|
Baz has asked for the wisdom of the Perl Monks concerning the following question:
Hi all,
I have a website which shows the distribution of surnames for various nations. Here's an example -
http://baz.perlmonk.org/surnames.cgi
The distributions are stored in a mysql table, and the maps are generated on the fly when a name is selected.
I was thinking about adding a form to this page to allow clients to add surnames which are not already included. To to do this, I could supply a text box form, and the user could enter their surname via this. The form would at this point hand over to a script, lets call it add_surname.cgi?surname=Griffin
Lets say this form has 2 parts. The first part acquires the data pertaining to this surname from an external source (for example, a CD containing electoral registrations), and fills a hash with this data, something like -
Name - Area - Number
Griffin - Dublin - 101
Griffin - Cork - 51
Griffin - Kerry - 514
Griffin - Clare - 445
Griffin - Derry - 0 (no Griffins in Derry, therefore no entry added to database for Griffin - Derry)
The second part then adds the data stored in the hash to the mysql database. So for the case above - Dublin, Cork, Kerry, Clare are added, and the number associated with them. As you can see, I therefore need to ensure that once the script is executed, it executes to completion, otherwise some of the entries might not be added to the database. How might I do this? Any other pitfalls you can think off? Two people trying to add the same surname simultaneously is already handled because add_surname.cgi first checks if an entry already exists for the surname in question. If so, add_surname.cgi terminates without adding anything.
Thanks,
Barry.
|
Re: Executing a Script to Completion
by exussum0 (Vicar) on Jan 02, 2004 at 04:33 UTC
|
You need database transactions. I am NOT speaking for mysql, since I am not a fan of it, so YMMV.
Every DBD has a way of turning transactions on if the db supports it and the dbd supports it. A transaction is simply a bunch of statements, language related to the db (sql,pgsql, pl/sql, transact sql), which are treated like an atomic unit. Either it all pushes through or the state before the transaction even started is restored. They only get pushed through when a commit (a "hey, i'm done now" occurrs).
At any rate, open your connection, do your thousands of statements and issue a $dbh->commit().
If your DBD doesn't support it, a lot of databases allow you to start a transaction arbitrarly via SQL. YMMV on databases.. oracle supports it as a BEGIN and END statement in a single statement handle, so ->commit is kinda pointless, at least from what i've seen. PostgreSQL allows you to span your transaction over multiple statement handles as transactions are connection oriented, so ->commit or "BEGIN ... COMMIT" works over multiple statement handles.
Notes..
Do this when your DB has a small load if you can. And make sure that you spend the least time between your first statement and your commit's. The more time you spend, the more other queries can get queued up due to locking. When a transaction is occuring with a write, other transactions wait for the writes to end.
Transactions incur a NASTY cost on your db depending on how your db is implemented. For instance, older versions of mysql didn't have row level locking.. so writing to a particular row would lock the entire table and thus prevent others from accessing it until the write was done. Oracle is usually nice about locking, though it can't get around people doing long winded updates, like UPDATE TABLE SET COLUMN = NULL.
Play that funky music white boy..
| [reply]
[d/l] |
Re: Executing a Script to Completion
by graff (Chancellor) on Jan 02, 2004 at 03:22 UTC
|
So, if there is a form that allows a user to fill in any number of rows (location, number) for some given surname, and that form contains a "Submit" button, the result of hitting that button could be to save the form contents to a local file on the server (possibly after doing some rudimentary sanity checks on field values), making sure the format of this file is suitbla for use with the "mysqlimport" utility (which does the same thing as doing "load data local infile" within a mysql session); once the file is successfully closed, just run mysqlimport via "system()", and check the results to see if that worked. The next form simply reports the result (success or failure, possibly with details).
Naturally, be mindful of the information in the mysql docs regarding security issues and access control with respect to the use of the "load data infile"/"mysqlimport" mechanism. | [reply] |
Re: Executing a Script to Completion
by sgifford (Prior) on Jan 02, 2004 at 10:01 UTC
|
Two people trying to add the same surname simultaneously is already handled because add_surname.cgi first checks if an entry already exists for the surname in question.
This makes two people adding the same surname less likely, but not impossible. Imagine your code is like this:
1. Check if surname exists
2. Add surname
and you have two clients, Alice and Bob, both adding the same surname. If things happen to be ordered like this:
Alice: Check if surname exists (it doesn't)
Bob: Check if surname exists (it doesn't)
Alice: Add surname
Bob: Add surname
you'll have the same problem. One solution is to enforce only a single surname in the database, with UNIQUE constraints on columns.
| [reply] |
|
|
| [reply] |
|
|
