Loading and saving in-memory SQLITE databases

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:

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 schema
        SKGTRACEL(20) << "Coping the schema..." << endl;
        SKGStringListList listSqlOrder;
        err=SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? 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((iFromFileToMemory ? 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(iMemoryDb,
                            "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 ?

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.

Quote:

---

Originally Posted by 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?

---

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

Quote:

---

Originally Posted by waynew

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.

Quote:

---

Originally Posted by waynew

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 ?

Quote:

---

Originally Posted by miraks

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 ?

Quote:

---

Originally Posted by Lesiok

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.

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

Quote:

---

Originally Posted by miraks

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.

Quote:

---

Originally Posted by Lesiok

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 system.
But, how to use it with QT ?

Do you have an answer ?

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 :

Code:

---

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

---

And 2 :

seeing your code, youhave the following statement :

Code:

---

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 ?

Code:

---

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

---

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:

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((iFromFileToMemory ? 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((iFromFileToMemory ? 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((iFromFileToMemory ? 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((iFromFileToMemory ? iMemoryDb: iFileDb), val);
            }
        }
 
        // Copy the indexes
        if (err.isSucceeded())
        {
            SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPYINDEX", err);
            SKGStringListList listSqlOrder;
            err=SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? 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((iFromFileToMemory ? iMemoryDb: iFileDb), val);
            }
        }
 
        // Copy the views
        if (err.isSucceeded())
        {
            SKGTRACEINRC(10, "SKGServices::copySqliteDatabase-COPYVIEW", err);
            SKGStringListList listSqlOrder;
            err=SKGServices::executeSelectSqliteOrder((iFromFileToMemory ? 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((iFromFileToMemory ? 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.