On 25 août, 19:29, Simon Slavin <slav...@bigfraud.org> wrote: > 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.
So we just have to close database in applicationWillterminate and applicationWillResignActive methods because SQLite handles crashes itself. This is amazing! > If you were part way through a transaction, then none of the operations in >that transaction will be present in the 'safe' version. OK. This is quite obvious for me since crash occurs before transaction commit. > > 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. > Yes, it a quite traditional transaction definition. > 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. Wow it confuses me. In 3) you told that when app crashes outside a transaction and before database close, there is no data loss. And now you tell that until database is closed, data are not flushed to the disk. I was thinking that flush to disk was performed at transaction commit because for example, performing N INSERT requests is much faster within a transaction than within N implicit ones. Please excuse me but can you explain this to me ? I am quite confused now :-) > 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. Are you telling that when you commit a transaction, data are "flushed" into a "in-memory cache" version of the database, and when database is closed then this cache is flushed to the database file ? And that on iPhone/iPad, the "memory cache" is in fact the disk ? Thank you, Best Regards, > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users