Loading and saving in-memory SQLITE databases

[miraks]

Hi,

My application is working with a document file implemented in a sqlite database.
So, the function "Open" is copying a file base sqlite database in memory (This is very fast).
The function "Save" is copying a memory database into a file (This is very slow due to file access).

For you information, here is the code to do this copy:

SKGError SKGServices::copySqliteDatabase(QSqlDatabase* iFileDb, QSqlDatabase* iMemoryDb, bool iFromFileToMemory)
{
SKGError err;
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase", err);
if (iFileDb && iMemoryDb) {
SKGTRACEL(20) << "Input parameter [iFileDb]=[" << iFileDb->databaseName() << ']' << endl;
SKGTRACEL(20) << "Input parameter [iMemoryDb]=[" << iMemoryDb->databaseName() << ']' << endl;
SKGTRACEL(10) << "Input parameter [iFromFileToMemory]=[" << (iFromFileToMemory ? "FILE->MEMORY" : "MEMORY->FILE") << ']' << endl;

QString dbFileName=iFileDb->databaseName();

// Copy the schema
SKGTRACEL(20) << "Coping the schema..." << endl;
SKGStringListList listSqlOrder;
err=SKGServices::executeSelectSqliteOrder((iFromFi leToMemory ? iFileDb: iMemoryDb),
"SELECT sql FROM sqlite_master WHERE sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')",
listSqlOrder);

int nb=listSqlOrder.count();
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listSqlOrder.at(i).at(0);
err=SKGServices::executeSqliteOrder((iFromFileToMe mory ? iMemoryDb: iFileDb), val);
}

// Attach the file dbFileName to the target
if (err.isSucceeded()) {
SKGTRACEL(20) << "Attaching the file..." << endl;
err = SKGServices::executeSqliteOrder(iMemoryDb, "ATTACH DATABASE '" + dbFileName + "' as source");
if (err.isSucceeded()) {
// Copy the DATA from the source to the target
SKGTRACEL(20) << "Coping data..." << endl;
err = SKGServices::executeSqliteOrder(iMemoryDb, "BEGIN");
if (err.isSucceeded()) {
SKGStringListList listSqlOrder;
err=SKGServices::executeSelectSqliteOrder(iMemoryD b,
"SELECT name FROM source.sqlite_master WHERE type='table' and name NOT IN ('sqlite_stat1', 'sqlite_sequence')",
listSqlOrder);
int nb=listSqlOrder.count();
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listSqlOrder.at(i).at(0);
if (iFromFileToMemory) err=SKGServices::executeSqliteOrder(iMemoryDb, "insert into main."+val+" select * from source."+val);
else err=SKGServices::executeSqliteOrder(iMemoryDb, "insert into source."+val+" select * from main."+val);
}

}
if (err.isSucceeded()) err = SKGServices::executeSqliteOrder(iMemoryDb, "COMMIT");

//Detach
SKGError err2=SKGServices::executeSqliteOrder(iMemoryDb, "DETACH DATABASE source");
if (err.isSucceeded() && err2.isFailed()) err=err2;

//Check if created file exists
if (err.isSucceeded() && !iFromFileToMemory && !QFile(dbFileName).exists()) {
//Set error message
err.setReturnCode(ERR_FAIL);
err.setMessage(i18nc("An error message", "Creation file [%1] failed",dbFileName));
}
}
}
if (err.isFailed()) err.addError(SQLLITEERROR + ERR_FAIL, i18nc("Error message", "%1 failed", QString("copySqliteDatabase::copySqliteDatabase()")));
}
return err;
}

I saw in this page http://www.sqlite.org/backup.html that new APIs are existing to do that.

Do you know how we can use it with QT ?

[waynew]

I don't know about using that with Qt. I suspect QtSql does not support it.
I'm wondering why your write the db to the file is so slow. I am doing that with a small database and it is not slow.
So my questions would be: how large is the database you are writing to a file?
And, what does your select statement for getting the records look like? Are you selecting all of the records, or just some of them?
If just some of them, what does your 'where' clause look like and do you have the proper columns indexed?
Your problem could be a slow query due to lack of indexes.

[miraks]

I don't know about using that with Qt. I suspect QtSql does not support it.
I'm wondering why your write the db to the file is so slow. I am doing that with a small database and it is not slow.
So my questions would be: how large is the database you are writing to a file?


The size of the file is around 4M.
Around 20 tables.
Most important table contains ~6000 records.



And, what does your select statement for getting the records look like?
Are you selecting all of the records, or just some of them?


As you can see in my code, I am duplicating all schema objects (tables, indexes, triggers) except 'sqlite_stat1' and 'sqlite_sequence'.
After that, I am duplicating all records of these tables.



If just some of them, what does your 'where' clause look like and do you have the proper columns indexed?
Your problem could be a slow query due to lack of indexes.

Indexes have been duplicated first.

One more remark:
The same function is used to duplicate from MEMORY to FILE and from FILE to MEMORY.
It's very fast from FILE to MEMORY (<500ms) but very slow (>20s) from MEMORY to FILE (My hard disc is working hard).

Why ?

[Lesiok]

One more remark:
The same function is used to duplicate from MEMORY to FILE and from FILE to MEMORY.
It's very fast from FILE to MEMORY (<500ms) but very slow (>20s) from MEMORY to FILE (My hard disc is working hard).

Why ?

Because write operations on disk are not cached ?

[miraks]

Because write operations on disk are not cached ?

How can I cached them ?
All sql orders launched are done between a BEGIN and a COMMIT.

[miraks]

Please ! Do you have an answer ? I am blocked.

[Lesiok]

How can I cached them ?
All sql orders launched are done between a BEGIN and a COMMIT.
This is a problem for OS and/or for DB engine.

[miraks]

This is a problem for OS and/or for DB engine.

As I said in my first topic, the solution is already existing in sqlite, this is the backup (http://www.sqlite.org/backup.html) system.
But, how to use it with QT ?

[miraks]

Do you have an answer ?

[jpujolf]

2 possible points of "stress" :

As I've found some time ago, don't ask me why, it's not the same for SQLite execute some statements between a BEGIN / COMMIT block than doing :


QSqlDatabase.transaction();
...
...<here ALL the stuff>
...
QSqlDatabase.commit();

And 2 :

seeing your code, youhave the following statement :


SKGServices::executeSqliteOrder(iMemoryDb, "BEGIN");
....
SKGServices::executeSqliteOrder(iMemoryDb, "COMMIT");

So, ONLY im-memory database has a begin / commit enclosure. The code has to be like this, or not ?


SKGServices::executeSqliteOrder(iFromFileToMemory ? iMemoryD : iFileDb, "BEGIN");
....
SKGServices::executeSqliteOrder(iFromFileToMemory ? iMemoryD : iFileDb, "COMMIT");

[miraks]

Hi jpujolf,

Thank you for you answer.
I think that BEGIN and COMMIT must be done on memory database because all sql orders are launched on this database or on a attached database. In any case, the main database is the memory one.

Here is my last version of code:

SKGError SKGServices::copySqliteDatabase(QSqlDatabase* iFileDb, QSqlDatabase* iMemoryDb, bool iFromFileToMemory)
{
SKGError err;
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase", err);
if (iFileDb && iMemoryDb) {
SKGTRACEL(20) << "Input parameter [iFileDb]=[" << iFileDb->databaseName() << ']' << endl;
SKGTRACEL(20) << "Input parameter [iMemoryDb]=[" << iMemoryDb->databaseName() << ']' << endl;
SKGTRACEL(10) << "Input parameter [iFromFileToMemory]=[" << (iFromFileToMemory ? "FILE->MEMORY" : "MEMORY->FILE") << ']' << endl;

QString dbFileName=iFileDb->databaseName();
// Copy the tables
SKGStringListList listTables;
int nb=0;
if (err.isSucceeded())
{
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPYTABLES", err);
err=SKGServices::executeSelectSqliteOrder((iFromFi leToMemory ? iFileDb: iMemoryDb),
"SELECT sql, tbl_name FROM sqlite_master WHERE type='table' AND sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')",
listTables);

nb=listTables.count();
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listTables.at(i).at(0);
err=SKGServices::executeSqliteOrder((iFromFileToMe mory ? iMemoryDb: iFileDb), val);
}
}
//Attach db
if (err.isSucceeded()) {
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-ATTACH", err);
err = SKGServices::executeSqliteOrder(iMemoryDb, "ATTACH DATABASE '" + dbFileName + "' as source");
}

//Copy records
if (err.isSucceeded()) {
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPY", err);
err = SKGServices::executeSqliteOrder(iMemoryDb, "BEGIN");
if (err.isSucceeded()) {
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listTables.at(i).at(1);
if (iFromFileToMemory) err=SKGServices::executeSqliteOrder(iMemoryDb, "insert into main."+val+" select * from source."+val);
else err=SKGServices::executeSqliteOrder(iMemoryDb, "insert into source."+val+" select * from main."+val);
}

}
SKGServices::executeSqliteOrder(iMemoryDb, "COMMIT");
}

//Detach
SKGError err2;
{
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-DETACH", err);
err2=SKGServices::executeSqliteOrder(iMemoryDb, "DETACH DATABASE source");
if (err.isSucceeded() && err2.isFailed()) err=err2;
}

//Optimisation
if (err.isSucceeded())
{

QString optimisation="PRAGMA case_sensitive_like=true;;"
"PRAGMA journal_mode=MEMORY;;"
"PRAGMA temp_store=MEMORY;;"
"PRAGMA locking_mode=EXCLUSIVE;;"
"PRAGMA synchronous = OFF;;"
;
err = SKGServices::executeSqliteOrder(iFromFileToMemory ? iMemoryDb: iFileDb, optimisation);
}

// Copy the triggers
if (err.isSucceeded())
{
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPYTRIGGER", err);
SKGStringListList listSqlOrder;
err=SKGServices::executeSelectSqliteOrder((iFromFi leToMemory ? iFileDb: iMemoryDb),
"SELECT sql FROM sqlite_master WHERE type='trigger' AND sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')",
listSqlOrder);

int nb=listSqlOrder.count();
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listSqlOrder.at(i).at(0);
err=SKGServices::executeSqliteOrder((iFromFileToMe mory ? iMemoryDb: iFileDb), val);
}
}

// Copy the indexes
if (err.isSucceeded())
{
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPYINDEX", err);
SKGStringListList listSqlOrder;
err=SKGServices::executeSelectSqliteOrder((iFromFi leToMemory ? iFileDb: iMemoryDb),
"SELECT sql FROM sqlite_master WHERE type='index' AND sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')",
listSqlOrder);

int nb=listSqlOrder.count();
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listSqlOrder.at(i).at(0);
err=SKGServices::executeSqliteOrder((iFromFileToMe mory ? iMemoryDb: iFileDb), val);
}
}

// Copy the views
if (err.isSucceeded())
{
SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPYVIEW", err);
SKGStringListList listSqlOrder;
err=SKGServices::executeSelectSqliteOrder((iFromFi leToMemory ? iFileDb: iMemoryDb),
"SELECT sql FROM sqlite_master WHERE type='view' AND sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')",
listSqlOrder);

int nb=listSqlOrder.count();
for (int i=1; err.isSucceeded() && i<nb; ++i) { //Forget header
QString val=listSqlOrder.at(i).at(0);
err=SKGServices::executeSqliteOrder((iFromFileToMe mory ? iMemoryDb: iFileDb), val);
}
}

//Check if created file exists
if (err.isSucceeded() && !iFromFileToMemory && !QFile(dbFileName).exists()) {
//Set error message
err.setReturnCode(ERR_FAIL);
err.setMessage(i18nc("An error message", "Creation file [%1] failed",dbFileName));
}
}
if (err.isFailed()) err.addError(SQLLITEERROR + ERR_FAIL, i18nc("Error message", "%1 failed", QString("copySqliteDatabase::copySqliteDatabase()")));
return err;
}

This version is faster due to PRAGMA settings.
In any case, it could be interesting to use backup sqlite API from QT.