On Oct 5, 2009, at 9:59 AM, Reusche, Andrew wrote: > Thanks. We are beyond the point of recovering data at this point, we > will just start over with a fresh db. Two questions pertaining to > what > may have caused this: > > 1) We are trying to programmatically update the primary keys on this > table, in order to sync it up with a remote database. In the event > that > this needs to occur, we typically need to take a chunk of records in > this table, and increment all the "sales_id"'s - integer, primary > key - > to a higher number. Do you anticipate that this could cause a > problem?
If the "from" range and the "to" range of the primary key overlap, you may bump uniqueness constraints. If you are asking if this can cause database corruption, the answer is "no". It is not possible to cause database corruption using SQL (valid or invalid) as far as we know, and this is very carefully tested. Please see http://www.sqlite.org/atomiccommit.html and http://www.sqlite.org/testing.html for further information. > > 2) In the routine mentioned above in 1), and in a number of various > inserts and updates, I found out that we were not calling "begin" or > "commit." From the documentation on > http://www.sqlite.org/lang_transaction.html it looks like a > transaction > is automatically started, so this should not be a problem. Do you > recommend that I call begin and commit anyway, or do you think that > will > not provide any additional benefit? BEGIN...COMMIT will make your code run much faster, but it will not do anything to prevent database corruption. See the reference above. If you are already doing hundreds or thousands of updates without BEGIN...COMMIT and you haven't already complained about the speed, this suggests that your database is running asynchronously (i.e. with PRAGMA synchronous=OFF or on a system that does not support fsync() or the equivalent). In that case, a simple power failure is all that it takes to corrupt the database file. D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
