Hi, Could anyone advise on best practices for using SQLite as storage when used in an existing application which uses file-based project storage?
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? Much thanks and thanks for listening, Richard
