How can I overwrite the contents of an SQLite file

Question

I've just started using SQLite and I want to write all my application data to a file, not knowing if the file exists already; with 'normal' files, this is straightforward, but with SQLite I can't create a table if it already exists, and I can't insert a row if the primary key already exists.

I basically want to do something like "CREATE TABLE IF NOT EXISTS table....else...DELETE FROM table". There must be a way to do it, I suspect that there are some ways that are more efficient than others. You'd think, for example, that it would be better to use an existing table rather than deleting and recreating, but that depends on what's involved in checking if it exists and deleting its contents.

Alternatively, is there any way to write a database to memory (sqlite3_open(":memory:",db)), but then get hold of its contents - as a byte array or something - to write to a file?

Answer 1

For all database systems it will almost always be more efficient to DROP the table first and then re CREATE it. Using DELETE will require index updates etc, whereas a simple DROP removes the indexes too, and will not involve making transaction log entries For SQLite, you can do a DROP IF EXISTS to drop the table conditionaly.

Answer 2

What about having an empty, fully designed database file ready somewhere. Then, if the intended app data database does not exist, create it by copying the empty db file?

Answer 3

If you're going to read the application data in at some point before writing it back out - user settings for instance - you'll know whether the data exists or not. Try calls to tables wrapped in exception handling and you'll know if they exist - alternatively use the database tables to tell you what exists:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

If you only every want to overwrite an existing db file you can easily just delete the db file from the disk and then run your creation code - simples - no messing around with drop calls anywhere. No worries about having changes in the db between versions.