OK thank you for these new explanations. I just have a last question: you are telling that opening connection at app launch and close it only at app exit (scenario A) may be less secure than open/close database at each transaction (scenario B), on a data integrity point of view. But you gave the contrary advice at the beginning of our exchanges. Why?
Let's consider a simple example of a database used by only one iOS app, which performs many INSERT or UPDATE during a user session. Therefore it would be better to minimize data loss risk. Best Regards, François On 25 août, 23:36, Simon Slavin <slav...@bigfraud.org> wrote: > 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-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
