working with SQLite

[bhs-ittech]

I'm wondering if you can take some SQLite databases setup like this:

QSqlDatabase sql = QSqlDatabase::addDatabase("QSQLITE","pva");
sql.setDatabaseName("pva.db");
 
QSqlDatabase sql2 = QSqlDatabase::addDatabase("QSQLITE","pva-cache");
sql2.setDatabaseName(":memory:");

QSqlDatabase sql = QSqlDatabase::addDatabase("QSQLITE","pva");
sql.setDatabaseName("pva.db");

QSqlDatabase sql2 = QSqlDatabase::addDatabase("QSQLITE","pva-cache");
sql2.setDatabaseName(":memory:");

To copy to clipboard, switch view to plain text mode 

and then "copy" the one to the other?

and is there a way to store a ":memory:" database
as a file when done working with it?

[mikolaj]

In fact database in SQLite is a file! If you copy e file pva.db you have a new copy of database.

[miraks]

I'm wondering if you can take some SQLite databases setup like this:

QSqlDatabase sql = QSqlDatabase::addDatabase("QSQLITE","pva");
sql.setDatabaseName("pva.db");
 
QSqlDatabase sql2 = QSqlDatabase::addDatabase("QSQLITE","pva-cache");
sql2.setDatabaseName(":memory:");

QSqlDatabase sql = QSqlDatabase::addDatabase("QSQLITE","pva");
sql.setDatabaseName("pva.db");

QSqlDatabase sql2 = QSqlDatabase::addDatabase("QSQLITE","pva-cache");
sql2.setDatabaseName(":memory:");

To copy to clipboard, switch view to plain text mode 

and then "copy" the one to the other?

and is there a way to store a ":memory:" database
as a file when done working with it?

You can do it by youself. Here is an example of code to do that:

SKGError SKGServices::copySqliteDatabase(const QString& iFileDbFile, QSqlDatabase* iFileDb, QSqlDatabase* iMemoryDb, bool iFromFileToMemory)
{
        SKGError err;
        SKGTRACEINRC(10, "SKGServices::copySqliteDatabase", err);
        SKGTRACEL(10) << "Input parameter [iFileDbFile]=[" << iFileDbFile << "]" << endl;
        SKGTRACEL(10) << "Input parameter [iFromFileToMemory]=[" << (iFromFileToMemory ? "FILE->MEMORY" : "MEMORY->FILE") << "]" << endl;
 
        // Copy the schema
        SKGTRACEL(20) << "Coping the schema..." << endl;
        //TODO: How to copy the statistics too
        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);
        SKGStringListListIterator it = listSqlOrder.begin();
        ++it; //To Forget the header
        for (; err.isSucceeded() &&it != listSqlOrder.end(); ++it) {
                err=SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb: iFileDb), *(it->begin()));
        }
 
        // Attach the file iFileDbFile to the target
        SKGTRACEL(20) << "Attaching the file..." << endl;
        if (err.isSucceeded()) {
                err = SKGServices::executeSqliteOrder(iMemoryDb, "ATTACH DATABASE '" + iFileDbFile + "' 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);
                                SKGStringListListIterator it = listSqlOrder.begin();
                                ++it; //To Forget the header
                                for (; err.isSucceeded() &&it != listSqlOrder.end(); ++it) {
                                        QString val=*(it->begin());
                                        if (iFromFileToMemory) {
                                                err=SKGServices::executeSqliteOrder(iMemoryDb,
                                                                                    "insert into main."+val+" select * from source."+val);
                                        } else {
                                                QString val=*(it->begin());
                                                err=SKGServices::executeSqliteOrder(iMemoryDb,
                                                                                    "insert into source."+val+" select * from main."+val);
                                        }
                                }
 
                        }
                        if (err.isSucceeded()) {
                                err = SKGServices::executeSqliteOrder(iMemoryDb, "COMMIT");
                        }
 
                        //Detach
                        SKGServices::executeSqliteOrder(iMemoryDb, "DETACH DATABASE source");
                }
        }
        if (err.isFailed()) err.addError(SQLLITEERROR + ERR_FAIL, tr("copySqliteDatabase::copySqliteDatabase() failed"));
        return err;
}

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

        // Copy the schema
        SKGTRACEL(20) << "Coping the schema..." << endl;
        //TODO: How to copy the statistics too
        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);
        SKGStringListListIterator it = listSqlOrder.begin();
        ++it; //To Forget the header
        for (; err.isSucceeded() &&it != listSqlOrder.end(); ++it) {
                err=SKGServices::executeSqliteOrder((iFromFileToMemory ? iMemoryDb: iFileDb), *(it->begin()));
        }

        // Attach the file iFileDbFile to the target
        SKGTRACEL(20) << "Attaching the file..." << endl;
        if (err.isSucceeded()) {
                err = SKGServices::executeSqliteOrder(iMemoryDb, "ATTACH DATABASE '" + iFileDbFile + "' 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);
                                SKGStringListListIterator it = listSqlOrder.begin();
                                ++it; //To Forget the header
                                for (; err.isSucceeded() &&it != listSqlOrder.end(); ++it) {
                                        QString val=*(it->begin());
                                        if (iFromFileToMemory) {
                                                err=SKGServices::executeSqliteOrder(iMemoryDb,
                                                                                    "insert into main."+val+" select * from source."+val);
                                        } else {
                                                QString val=*(it->begin());
                                                err=SKGServices::executeSqliteOrder(iMemoryDb,
                                                                                    "insert into source."+val+" select * from main."+val);
                                        }
                                }

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

                        //Detach
                        SKGServices::executeSqliteOrder(iMemoryDb, "DETACH DATABASE source");
                }
        }
        if (err.isFailed()) err.addError(SQLLITEERROR + ERR_FAIL, tr("copySqliteDatabase::copySqliteDatabase() failed"));
        return err;
}

To copy to clipboard, switch view to plain text mode 

[bhs-ittech]

Thanks miraks,
this looks simple indeed, however what is a SKGServices object?

My guess is it's part of 'SKG' API but what's that, and how/where do I get it?

if possible is there a way do this using only Qt?

Problem is the database is very big, and takes ages if I have to add row by row
of the database if the database is a file. However if the database is in memory
that happens alot faster. ( the dataset it 1 table with 32000+ rows)