Of course one problem remains, and that is detecting whether some other user has changed the record since you saved it. To achieve this, you define another integer column and store another value in that field, but this value is updated each time the record is saved. This can be a simple sequential value that rolls around to zero (or one) again after reaching some maximum value. The name of the field should reflect its purpose, so you could name it something like "EditVersion" or "ChangeFlag". The client does not need to access this field, or the primary key field, but they can be used to ensure that the record being updated matches the current record (you could read the current record first, or use the fields to validate the update).
For example, to continue your example, the SELECT statement would be modified to include the "CustomerID" and "ChangeFlag" columns, and the RecordSet.Update() method would be modified to issue a SQL statement like "UPDATE Customers SET Name = xxx, Age = yyy WHERE CustomerID = cccc AND ChangeFlag = fff", and the code would check to ensure that exactly one record was modified.
-ken
On 17-May-05, at 4:46 PM, Will Leshner wrote:
I develop a database wrapper for SQLite and I have an interesting problem that I'm curious how other people solve. Basically, the users of my wrapper have the option of editing database records indirectly through the wrapper, rather than directly, using UPDATE. The database wrapper is a set of classes and one of those classes is a RecordSet. So, when the user asks for a RecordSet, with the intention of editing one or more records, she might do this (where rs is a RecordSet):
rs = db.SQLSelect("SELECT name, age FROM customers")
Now, the user can edit a record in the RecordSet like this:
rs.Edit rs.Field("name") = "Frank" rs.Field("age") = 10 rs.Update
What the wrapper does, when it sees the Update, is create SQL and feed it to SQLite:
UPDATE cusomers SET name='Frank', age=10 WHERE name=<old-name-value> AND age=<old-age-value>;
The problem is that the 'name' and 'age' fields are not sufficiently unique to identify the very row the user wanted to update. Instead every row that has matching names and ages are going to be updated.
To solve this problem, I've been telling users to explicitly add 'rowid' as one of their columns when they issue SQL to select records to edit:
rs = db.SQLSelect("SELECT rowid, name, age FROM customers")
This works ok, but I'd really like to get rid of this limitation. I've considered ways of possibly inserting 'rowid' manually to the user's SQL, but the idea of modifying the user's SQL kind of leaves a bad taste in my mouth.
I know that PHP also uses SQLite and I'm wondering if it has the same problem and how it may have solved it.
Thanks for any help.
