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:");


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:");


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((iFromFi leToMemory ? 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((iFromFileToMe mory ? 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(iMemoryD b,
"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;
}

[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)