On 25 Aug 2011, at 5:19pm, François wrote: > 3) So SQLite correctly deals with crashes (this is amazing!) But when > app crashes, database connection may be not closed. Could it be a > problem? For example, preventing from re-opening or reading database > later ?
When you use sqlite3_open() a database file that routine checks for many signs that the database wasn't closed properly -- in other words that an app which previously had it open crashed rather than using sqlite3_close(). It then uses all the clues available in the database file and any journal files to restore the database to a 'safe' situation. If you were part way through a transaction, then none of the operations in that transaction will be present in the 'safe' version. > 4) As far as I understand, data integrity is handled by database > transactions, not by database close: I mean that data are flushed into > database file when transactions are committed, not when database is > closed. Is this true ? If yes, what is the exact role of the close ? It is unfortunate that the way computers work seems to tie these things together but conceptually they are completely different. And I'm sorry but for brevity's sake the following leaves out some picky details about how SQLite actually works. Transactions are about programming and connections between data. For instance, you might want to INSERT an invoice and UPDATE that client's balance and either both of those things must be done or neither. Transactions are about making sure that related changes to your data happen 'at the same time' even though, because SQL has no commands which change more than one TABLE, you cannot issue one command that makes both changes. You can use transactions in your software: you can start a transaction, raise an invoice, update a client's balance, then read the new balance and notice it's less than zero, and cancel all those changes by issuing ROLLBACK instead of COMMIT. Everything inside SQLite happens in a transaction. If you fail to do BEGIN yourself before doing UPDATE, SQLite actually does BEGIN; UPDATE; COMMIT or nothing could be done. Closing the database connection is about files on disk. While your app has a connection to a database the files on the disk may not clearly reflect the true state of your data. Various changes may be partly made. Two or more different programs may have transactions open. You may be using some form of caching to delay changes so you can make them all at once. So if you have a SQLite application running while you take a complete backup of your hard disk, the data copied with the file may be weird in all sorts of ways. The sqlite3_close() command says "Save all this to disk, so I can forget it for the night and it'll all be there for the morning.". It makes sure that all your change are neatly expressed in files on disk, instead of them being partly on disk and partly in the memory of some apps you're running. iPhones make this all very confusing, because working memory isn't very different to 'disk' memory, and once apps are started they normally continue running until the device shuts down because the battery runs low. But these things were designed when things were different. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
