On 25 Aug 2011, at 9:54pm, François wrote: > On 25 août, 19:29, Simon Slavin <slav...@bigfraud.org> wrote: >> 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 your application crashes you can't make it do anything else. So you have no other option. Just think of it as if there's a 'rescue utility' built into sqlite3_open(). It's not a perfect utility, and can't rescue from all possible kinds of corruption, but it will handle the results of most kinds of crashes and leave you with a usable database. > Wow it confuses me. In 3) you told that when app crashes outside a > transaction and before database close, there is no data loss. No. The recovery after a database has crashed does not make sure there is no data loss. It tries to make sure the database is restored to a COMMIT point, not half way through a transaction, or in a corrupt state where, for instance, a row has been added to a table but the table's indexes haven't been updated. The recovery process could, for instance, lose an entire transaction if you were unlucky with your crash's timing. > 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 :-) The only time you can be sure that the SQLite database file on disk perfectly reflects the state of your database is when you don't have any connections open, and have given caches time to empty, and have given your disk hardware time to do what it promised the motherboard it would do. SQLite may or may not choose to update the database file on disk at any time while you have the connection open. That's not your problem. If SQLite waited for a full and proper update of the disk file every time you closed a transaction it would work very slowly indeed. Nothing actually works like that these days: we're used to computers that tell the user "I did it" but then do a lot of the work afterwards. >> 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 ? No. Sorry but this is more complicated than you have to care about at first glance. Please think of transactions and connections are two completely different things. You can write your program, for example, that until you've committed a transaction, /nothing/ is written to disk. Perhaps all the INSERT and UPDATE commands are just held as text in memory until a SELECT or COMMIT, with no data generated at all. Certainly if you have two different apps with connections to the same database, app1 could open a transaction, do a thousand INSERTs, then wait for user input for three hours, and app2 won't see any changes until app1 has done a COMMIT. That's how SQL is designed. If you want to make sure a SQLite database on disk reflects the changes you've made to it, close all your database connections. Until then your changes may be in journal files or in the memory associated with the app that made the change. If you write your software the way the documentation says, it'll work fine. If you want to learn more about the deep considerations behind your questions, read <http://en.wikipedia.org/wiki/ACID> > And that on > iPhone/iPad, the "memory cache" is in fact the disk ? It's just that there is no hard disk in an iPhone, just dynamic RAM and Flash RAM. So really everything's in RAM. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
