Hi Richard, Many thanks for your input. My ideas about how sqlite works were indeed incorrect.
When I say "re-opening" the database, I mean opening it and doing a few SELECT to it (as described in a previous email). So I do read it (especially the sqlite_master table), and still the journal file stays there. Max suggested that it might be normal behaviour... Please also note that I now do a PRGMA to force the journal mode to DELETE (although I am pretty sure this is what it was by default...) Best regards, Fabrice -----Original Message----- From: Richard Hipp <d...@sqlite.org> Sender: drhsql...@gmail.com Date: Wed, 26 Feb 2014 05:40:27 To: <ftrib...@falcon-one.com>; General Discussion of SQLite Database<sqlite-users@sqlite.org> Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Wed, Feb 26, 2014 at 3:33 AM, Fabrice Triboix <ftrib...@falcon-one.com>wrote: > Hi Simon, > > If I run my program step-by-step, I can see that the sqlite database is > not modified when I do an INSERT. It is modified only when I do a COMMIT. > Correct. Changes are normally kept in memory (unless the change set grows too large) until you COMMIT. This allows other concurrent processes to continue reading the unmodified database file. > > I read the page on the sqlite website detailing how sqlite deals with > crashes and untimely power losses, and it says that the journal file is > created just before modifying the database and is a copy of the areas that > are to be modified. Once the journal file is committed to the disk, the > database is modified. > But this is not what I can see. What I can see is that the database file > is not modified when I do the INSERT, it is modified only when I do the > COMMIT. > Perhaps your mental model of INSERT and COMMIT is wrong. The abstraction that SQL (all SQL databases, not just SQLite) tries to present is this: INSERT does not modify an SQL database; it merely queues up changes to be made atomically when you COMMIT. > > And even then, that would not explain why the journal file lingers after > re-opening the database. > Simply calling sqlite3_open() does not "re-open" a database. It merely saves the filename so that the database can be opened later when needed. The "re-open" does not occur until you first try to read from the database file. The rollback journal is replayed and deleted at that point. > > Could anyone shed some light on the above? > > Thank you very much for any help! > > Best regards, > > Fabrice > > ------Original Message------ > From: Simon Slavin > To: Fabrice Triboix > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Question about how sqlite recovers after a power loss > Sent: 18 Feb 2014 08:57 > > > On 18 Feb 2014, at 8:13am, Fabrice Triboix <ftrib...@falcon-one.com> > wrote: > > > Actually, the journal file does not get deleted when I do a SELECT just > after opening the database. > > Depending on your journal mode (I think), the journal file should be > deleted, but then it will immediately be recreated. Are you sure that this > is not what's happening ? > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
