Multiple database connections

[tbscope]

You can indeed use the attach command. Did you already try it?
http://www.sqlite.org/lang_attach.html

Now that I now a bit more about your program, why don't you display the data directly to the experimenter without going through the database first? Or did you already do that? At least, this should eliminate any lag from the database. The database can then be used as a "tape recorder".

Edit: ohh and sorry for the indirect questions to your question. I'm trying to understand what you want to do and why.

[Matt31415]

I'm actually using the addDatabase command since it is just as easy, and works the same way. At least according to this:

http://www.qtcentre.org/threads/2527...TTACH-database

Because it's possible that multiple experimenters will be viewing the experiment, I need to route everything through the server. I could use some sort of simple data structure to store it all, but since I was already using the database, it made sense to store everything there (also this means that if I decide to change the schema, I can simply change the tables in the database).

If it turns out that there is no way to do this with a single SQL statement, I can read everything out and write it back by hand, but this will likely be slower.

[tbscope]

With the attach command, you can use one query. I didn't know about that.
You do need to use the actual database name, as in filename, when using the attach command. The connection name is a way for the Qt classes to make a difference between multiple databases.

Here's what I would do though (see attachment)
There's a direct connection between the main experimenter (experimenter 1) and the subject. Whatever the subject does with his mouse, the data is transfered and displayed in real time on the computer of experimenter 1. The server (running on the computer of experimenter 1 for example, but can be stand alone too) directly sends the data to the connected clients (as in a proxy) in real time. The other experimenters see the data in real time too (well, at least with a very minor lag, there's always some overhead). As a last step, the data is cached for a second or two and then written to the database in the background. The database is only used to record everything, not to display (unless at a later time).

[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