Multiple database connections

[Matt31415]

So, I don't really want to rearrange the entire system to make this happen.

My current solution is simply to use 2 seperate queries. I SELECTed all of the relevant records from the memory-backed cacheDb_, and then in a single transaction, I am INSERTing them into the table in the disk-backed sessionDb_. I am assuming that this is slower than doing the whole thing in a single SQL command, but it is working for the moment.

However, I would still be interested in figuring out how to move data between two databases without having to do it one record at a time.

[numbat]

I still don't understand why you can't use transactions. A SELECT statement will still return up-to-date data, even in the middle of a transaction. A disk database with transactions will easily support 10,000 simple inserts a second, so you could skip the in-memory database. Anyway, here is a sample of using ATTACH. I doubt it's much faster than doing it in Qt, but it is a bit more elegant.

void setupDiskDb()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "disk");
    db.setDatabaseName("CatchyName");
    db.open();
 
    QSqlQuery query(db);
    query.exec("CREATE TABLE T1(F1, F2, F3)");
}
 
void doSqLiteTest2()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "mem");
    db.setDatabaseName(":memory:");
    db.open();
 
    QSqlQuery query(db);
    query.exec("CREATE TABLE T1(F1, F2, F3)");
 
    query.prepare("INSERT INTO T1 (F1, F2, F3) "
                   "VALUES (:1, :2, :3)");
 
    for (int i = 0; i < 50000; i++)
    {
        query.bindValue(":1", i + 0);
        query.bindValue(":2", i + 1);
        query.bindValue(":3", i + 2);
        query.exec();
    }
 
    qDebug() << query.exec("ATTACH DATABASE 'CatchyName' AS Catchy");
    qDebug() << query.exec("INSERT INTO Catchy.T1(F1, F2, F3) SELECT F1, F2, F3 FROM T1");
 }
 
int main(int argc, char * argv[])
{
    QApplication a(argc, argv);
 
    setupDiskDb();
    doSqLiteTest2();
 
    return a.exec();
}

void setupDiskDb()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "disk");
    db.setDatabaseName("CatchyName");
    db.open();

    QSqlQuery query(db);
    query.exec("CREATE TABLE T1(F1, F2, F3)");
}

void doSqLiteTest2()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "mem");
    db.setDatabaseName(":memory:");
    db.open();

    QSqlQuery query(db);
    query.exec("CREATE TABLE T1(F1, F2, F3)");

    query.prepare("INSERT INTO T1 (F1, F2, F3) "
                   "VALUES (:1, :2, :3)");

    for (int i = 0; i < 50000; i++)
    {
        query.bindValue(":1", i + 0);
        query.bindValue(":2", i + 1);
        query.bindValue(":3", i + 2);
        query.exec();
    }

    qDebug() << query.exec("ATTACH DATABASE 'CatchyName' AS Catchy");
    qDebug() << query.exec("INSERT INTO Catchy.T1(F1, F2, F3) SELECT F1, F2, F3 FROM T1");
 }

int main(int argc, char * argv[])
{
    QApplication a(argc, argv);

    setupDiskDb();
    doSqLiteTest2();

    return a.exec();
}

To copy to clipboard, switch view to plain text mode