[Solved]Saving and restoring an in-memory SQLite database

[mrplainswalker]

I'm trying to figure out if there is a way to synchronize an in-memory sqlite database with a file and vice versa. I know that I could just dump everything in my database into a binary file, but I was hoping for something actually supported by Qt's QSqlDatabase. I also know sqlite has backup functions for this very thing, but I can't access the sqlite object directly when it's down in the bowels of QSqlDatabase and the sqlite drivers.

[lgeyer]

Attach a file based database and copy the data over using INSERT INTO ... SELECT ... statements.

The better solution would be using a file based database in the first place and setting up appropriate cache and page sizes and moving the temp store to memory. This will make your file-based database (nearly) as fast as a memory based one (of course depending on the use case).

@
QSqlQuery("PRAGMA page_size = 4096");
QSqlQuery("PRAGMA cache_size = 16384");
QSqlQuery("PRAGMA temp_store = MEMORY");
@

There are other pragmas that influence performance as well.

@
QSqlQuery("PRAGMA journal_mode = OFF");
QSqlQuery("PRAGMA locking_mode = EXCLUSIVE");
QSqlQuery("PRAGMA synchronous = OFF");
@

[mrplainswalker]

I suppose I could just attach to another Sqlite database and use a file instead of memory and then copy over as you say. Of course, that's not much better than a straight binary dump. I was hoping for a more intelligent persistence system than that.

By the way, the reason I'm not using a file database to begin with is that I'm aiming for a more typical new/open/save/save as type interface for the application. If I use a file based database from the beginning, then the user will have to choose a file name and location whenever they select "new" instead of whenever they save.

[goetz]

Hi,

you can try using "ATTACH DATABASE". The following snippet works for me:

@
// open the in memory database
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
qDebug() << "db open" << db.open();

// create a table in the memory DB
QSqlQuery q_create = db.exec("CREATE TABLE qdn (id int, name varchar(50))");
qDebug() << "create: " << q_create.lastError();

// populate with some data
QSqlQuery q_insert(db);
q_insert.prepare("INSERT INTO qdn (id, name) VALUES (:id, :name)");
q_insert.bindValue(":id", QVariant::fromValue(1));
q_insert.bindValue(":name", "Volker");
qDebug() << "insert volker: " << q_insert.exec();

q_insert.bindValue(":id", QVariant::fromValue(2));
q_insert.bindValue(":name", "Root");
qDebug() << "insert root: " << q_insert.exec();

// create a new file based database and attach it
// to the existing Qt DB handle
QString sqlAttach = QString("ATTACH DATABASE '%1' AS fileDB")
.arg(QDir::toNativeSeparators("/tmp/qdn.db"));
QSqlQuery q_attach = db.exec(sqlAttach);
qDebug() << "attach: " << q_attach.lastError();

// create the table in the file based DB as a copy
// of the in memory DB
QSqlQuery q_create_fdb = db.exec("CREATE TABLE fileDB.qdn AS SELECT * from qdn");
qDebug() << "create select: " << q_create_fdb.lastError();
@

Please note, that this does only create the tables. You will have to add the indices and probably other stuff manually afterwards.

[lgeyer]

[quote author="mrplainswalker" date="1314136697"]By the way, the reason I'm not using a file database to begin with is that I'm aiming for a more typical new/open/save/save as type interface for the application. If I use a file based database from the beginning, then the user will have to choose a file name and location whenever they select "new" instead of whenever they save.[/quote]

Not necessarily. Just create a database at a temporary location ("QDir::tempPath()":http://doc.qt.nokia.com/latest/qdir.html#tempPath) and rename / move it afterwards.

If you choose the attached database approach I should like to point out the sqlite_master table which automatically exists in every SQLite database and contains all existing database objects (tables, indices, ...).
@
ATTACH file AS fileDatabase

BEGIN

SELECT sql FROM sqlite_master; // 'sql' contains CREATE statements for each object
foreach(row)
{
execute(row) on fileDatabase;
}

SELECT name FROM sqlite_master WHERE type = 'table';
foreach(row)
{
INSERT INTO fileDatabase.name SELECT * FROM name;
}

COMMIT

DETACH
@
Pseudocode. From brain to terminal. Not tested.

However, from a users perspective I would have no problem in choosing a filename on creation.

[andre]

[quote author="Lukas Geyer" date="1314168594"][quote author="mrplainswalker" date="1314136697"]By the way, the reason I'm not using a file database to begin with is that I'm aiming for a more typical new/open/save/save as type interface for the application. If I use a file based database from the beginning, then the user will have to choose a file name and location whenever they select "new" instead of whenever they save.[/quote]

Not necessarily. Just create a database at a temporary location ("QDir::tempPath()":http://doc.qt.nokia.com/latest/qdir.html#tempPath) and rename / move it afterwards.
[/quote]

Not quite. That would mean that from the moment of the first save, all changes you make in the program are saved automatically. That may not be what you want, if you are looking for an explicit save/save as function. What would save mean, then?

I think that with this aproach (good idea, in itself!), saving would actually be copying the database from the temporary location to the indicated location, but continuing to work with the database in the temporary location. Then, a next save would simply mean a new copy to the same location, and a save as would mean a new copy to a new location.

[lgeyer]

[quote author="Andre" date="1314173001"]I think that with this aproach (good idea, in itself!), saving would actually be copying the database from the temporary location to the indicated location, but continuing to work with the database in the temporary location. Then, a next save would simply mean a new copy to the same location, and a save as would mean a new copy to a new location. [/quote]

I think the elementary problem is that the known work - save - work pattern and databases do not fit very well for a simple reason: it's quite delicate to copy the whole database every time the user request a save. This might work for a small dataset but becomes quite unusable as the database grows. It is still quite bad having to do this once - but that's the price to pay if you do not know the final filename.

If you want to map the work - save - work pattern I would still prefer the temporary file solution. On first save rename / move the database to the proper location. If you cross filesystem boundaries (so you acutally have to copy the data) create an online backup to the proper location, so the user can continue working on the database.

Once done, create a "savepoint":http://www.sqlite.org/lang_savepoint.html each time the user presses the save button. This allows for discarding any changes made to the database at any time if the user decides to do so and if the user decides to save just release the savepoint and create a new one. As savepoints can be nested this even allows for a simple undo function.

This way only the data changed since the last release / commit has to be copied. With proper cache settings there is almost no disk-to-disk transfer as transactions can be kept in memory.

[andre]

Interesting, I did not know about savepoints in sqlite. I guess it also really depends on how big you expect your dataset to become. You are right that the work - save - work pattern does not fit real databases very well, but not all applications that use an sqlite file for data storage can, IMHO, be qualified as real database applications.

Reading the document you link to, it seems that you would have to create a savepoint immediately, and then on each save command, do a release (so that the changes made between the savepoint and the release are committed), and then create a new savepoint. Interesting stuff, thanks for pointing me to this!

[mrplainswalker]

Thanks for all the input. Very informative. I think I'm going to just have the user choose a file location on "new" instead of on "save". It's an extremely minor inconvenience for a significant performance boost. I also like the idea of using savepoints and then committing changes on save or rolling back if they choose to discard changes, since commits are fast. Again, thanks for the help.