Does QxOrm support in-memory db?

[hcaihao]

Just like http://www.sqlite.org/backup.html

I wanna load db into memory, all operations will be done in memory, and save memory db to disk before application exit.

QxOrm is too slow to open & close db every time from disk when I call qx::dao::XXX.

Help pls. Thank you!



-------------------------


I tried this code, but failed to copy data to memory.

try
{
// Parameters to connect to database
qx::QxSqlDatabase::getSingleton()->setDriverName("QSQLITE");
qx::QxSqlDatabase::getSingleton()->setDatabaseName(":memory:");
qx::QxSqlDatabase::getSingleton()->setHostName("localhost");
qx::QxSqlDatabase::getSingleton()->setUserName("root");
qx::QxSqlDatabase::getSingleton()->setPassword("");

// Disable debug output
//qx::QxSqlDatabase::getSingleton()->setTraceSqlQuery(false);
}
catch(...)
{
return false;
}


// File 2 memory
QSqlDatabase db = qx::QxSqlDatabase::getSingleton()->getDatabase();
QVariant v = db.driver()->handle();
if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0)
{
// v.data() returns a pointer to the handle
sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
if (handle != 0)
{
// check that it is not NULL
int result = loadOrSaveDb(handle, path.toStdString().c_str(), false);
if(result != SQLITE_OK)
{
throw std::exception("File 2 memory error.");
}
}
}

[qxorm]

Hi,

QxOrm is too slow to open & close db every time from disk when I call qx::dao::XXX

Slow ? You are the first to told me that QxOrm library is slow
The database connexion is opened only once (per thread) and re-opnened when it's necessary : there is no open-close operations for each call of qx::dao::XXX function.
Moreover, if you want to manage yourself your connexion (or a pool of connexions for example), you can use the extra-parameter in each qx::dao::XXX function : QSqlDatabase * pDatabase => if NULL (default value), then QxOrm library will manage for you the connection; if not NULL, this is your own database connection.

I wanna load db into memory, all operations will be done in memory, and save memory db to disk before application exit.

QxOrm library support SQLite in memory database.
Then to synchronise an in-memory database into a persistent file database, this is not a built-in feature : this is more a SQLite feature than a QxOrm library feature.
So you should ask in another forum (a SQLite forum for example) : there is no reason that QxOrm library doesn't support it.
Maybe a link which could help you : http://qt-project.org/forums/viewthread/9090

[hcaihao]

The database connexion is opened only once (per thread) and re-opnened when it's necessary : there is no open-close operations for each call of qx::dao::XXX function.

I mean sqlite will access disk when I call qx::dao every time, it's too slow if I call it 10 more times per second.

QxOrm library support SQLite in memory database.

I found the issue, the memory db can only accessed by the created thread. But the disk db can access by all thread, why?

qx::QxSqlDatabase::getSingleton()->setDatabaseName("d:\\test.db"); // Can access by qx::dao from other thread

qx::QxSqlDatabase::getSingleton()->setDatabaseName(":memory:"); // Can not access by qx::dao from other thread

[qxorm]

the memory db can only accessed by the created thread. But the disk db can access by all thread, why?
qx::QxSqlDatabase::getSingleton()->setDatabaseName("d:\\test.db"); // Can access by qx::dao from other thread
qx::QxSqlDatabase::getSingleton()->setDatabaseName(":memory:"); // Can not access by qx::dao from other thread

This is interesting !
QSqlDatabase class from Qt library must be used with a connection per thread : http://qt-project.org/doc/qt-5.0/qtcore ... sql-module
To manage it automatically, QxOrm library creates automatically a new connection when you are working in a different thread (so a connection per thread).

When you are working with in memory databases, maybe each thread creates its own in memory database ?
You could try to get the pointer of the QSqlDatabase connection from the main thread and then dispatch it to all your threads (the Qt doc says it will not work but you can try it).

[hcaihao]

the memory db can only accessed by the created thread. But the disk db can access by all thread, why?
qx::QxSqlDatabase::getSingleton()->setDatabaseName("d:\\test.db"); // Can access by qx::dao from other thread
qx::QxSqlDatabase::getSingleton()->setDatabaseName(":memory:"); // Can not access by qx::dao from other thread

This is interesting !
QSqlDatabase class from Qt library must be used with a connection per thread : http://qt-project.org/doc/qt-5.0/qtcore ... sql-module
To manage it automatically, QxOrm library creates automatically a new connection when you are working in a different thread (so a connection per thread).

When you are working with in memory databases, maybe each thread creates its own in memory database ?
You could try to get the pointer of the QSqlDatabase connection from the main thread and then dispatch it to all your threads (the Qt doc says it will not work but you can try it).

If I must add lock myself when using qxorm in muti-thread? Is there any sample for this case?

[qxorm]

What do you mean exactly by "lock myself when using qxorm in muti-thread" ?

[hcaihao]

What do you mean exactly by "lock myself when using qxorm in muti-thread" ?

Sorry for my poor English, I mean If I need add lock/mutex when I using qxorm in muti-threaded envirment.


Another question: When my app is running, a journal file to be created many times, does it necessary? how to prevent it?

[qxorm]

I need add lock/mutex when I using qxorm in muti-threaded envirment.

I was wrong : trying to share the same connection over multiple threads is a bad idea. As it is written in the Qt doc, this is definitively not an option for you !
So it seems to be a limitation of Qt Sql module with SQLite : using the same in-memory database in multiple threads is not possible without modifying the QSQLite driver.

If you look at this forum, maybe you will have the solution : http://www.qtcentre.org/threads/28780-Q ... y-database
Hi everybdy.
We did it.
We've made our own driver to SQLite. In fact we've used sqlite3_open_v2 methd instead original sqlite3_open_v16 that is implemented in Qt default driver with flag set to NO MUTEXES. At run time we've not serialized acces to in memory database.
If fact the most mportat thing is to spawn only one connection per thread, but it's wrking in paralel.

You could try to contact this person to know how exactly they did it.

Another possibility (but I don't know if it's a good idea) : you could use a dedicated thread for all operations with your in-memory database, and then execute your requests from other threads using SIGNAL/SLOT mechanism.

In all cases, this is not a limitation of QxOrm library, but a normal behaviour of Qt Sql module with in-memory SQLite databases and multi-threads !

Another question: When my app is running, a journal file to be created many times, does it necessary? how to prevent it?

Please, next time, create a new topic on this forum for each new question...

QxOrm library doesn't create any journal file !
If you speak about logs, you can disable it using : qx::QxSqlDatabase::getSingleton()->setTraceSqlQuery(false); (in release mode, logs are not displayed by default).
If you really have a journal file, it's generated by your database, so you have to disable it in your SGBD, not in QxOrm library.