"Lists Hammersley" <[EMAIL PROTECTED]> wrote: > > We have an existing application which stores data out to flat files. > We would like to use SQLite as the storage mechanism for column-like > data, other datatypes would remain in flat-files. However, there are a > few use cases which make it difficult to keep the flat files in sync > with the SQLite database. This is because the project is maintained in > memory until the user does a save, whereupon the in-memory > representations are written out to the flat-files. > > For example, we store a list of table names in our flat files. If the > user should import an additional table, the in-memory list of table > names will be updated and a new table will be written out to the > SQLite database. If the user should now quit the application without > saving, there will be a table in the SQLite database which is not > linked to in our flat files. > > The neatest solution would probably be to use nested transactions. To > keep one long transaction which is committed when the user does a > save. Nested transactions are then used when making modifications to > the database which may fail and need a partial rollback. > Unfortunately, SQLite does not support this. > > This seems to leave the approach of copying the SQLite database file > when a write to the database is begun. Then on a save to copy the > SQLite database back. > > Are there neater solutions to this problem? >
If you want to preserve the traditional File/Open + File/Save semantics, you could create an in-memory SQLite database then copy the disk database to the in-memory database on File/Open, and copy the other way on File/Save. But I have found that the traditional File/Open + File/Save paradigm leaves a lot to be desired. From a user interface perspective File/Open+File/Close is not good design. I know that most applications use that design, but they do so only due to technological limitations - limitations that are relaxed through the use of SQLite as your application file format. I find it better to directly update the database on disk. In my latest programs, I have removed File/Save. Every on-screen change is immediately written out to the application file, so there is never any reason to save. I still have File/Save_As, which makes a copy of the database and begins using the copy. And File/Open closes the current database and opens a new one. But File/Save is missing from the File menu all together. Think of it as an automatic save. You never again will lose work due to a power loss or other system crash. The one thing you lose when you remove File/Save is the ability to deliberately abandon your work - to quit without saving. But that is easily remedied with an unlimited depth undo/redo mechanism. You can implement an undo/redo using triggers. See for example http://www.sqlite.org/cvstrac/wiki?p=UndoRedo -- D. Richard Hipp <[EMAIL PROTECTED]>
