SQLite management of multiple databases

Binary91

Hi again,

I'm very new to SQL programming and I'd like to store my data in local databases.

My concept is like this:
There should be a general database "config.sqlite" containing multiple tables like a user table, settings table and more... This database should be connected to at the very beginning of the program and is needed through the whole runtime.
Then, there should exist user-specific databases (username = database name) with multiple tables containing user-specific data.

Well, only one user uses the software. I read about that, with SQLite, it was not possible to get multi-access to a database at one time. I think that means that I can only have one connection to one database at the same time, right?

So, how would I set up this best? I'm not quite sure about the difference of "QSqlDatabase::addDatabase()" and QSqlDatabase::open().

Would it be best to initialize two connection objects, one for the config database and one for the user specific database and hold the connection object through the whole runtime and close it by calling QSqlDatabase::removeDatabase() at the end of the main function? Or would it be better to have only one connection and, each time I need access to either the main database or the specific one, I close the database, change the connection object bei db.setDatabaseName(...) and open the new one, all with only one connection object?

What would be the best way for that? Thank you very much in anticipation!

HoMa

Well, only one user uses the software. I read about that, with SQLite, it was not possible to get multi-access to a database at one time. I think that means that I can only have one connection to one database at the same time, right?
The "mulit-access" restriction of SQLite is: You can have multiple processes to read from one SQLite DB at a time - but only one can write. You can have any number of connections.

So, how would I set up this best? I'm not quite sure about the difference of "QSqlDatabase::addDatabase()" and QSqlDatabase::open().
This is a bit tricky. addDatabase makes sure the drive for the database type is loaded. open establishes the actual connection and for many databases you provide authentication information with open.
You also have to consider setDatabaseName. This connects the actual .sqlite file to your connection.

So - what is important for your case -opening multiple database files? QSqlDatabase maintains one "default" Database using the default connection name. If you do not give a connection name in related function calls, this default connection is used. And you can access the respective database object with QSqlDatabase::database().

For all other databases you need to specify a connection name to "addDatabase" and always use the returned QSqlDatabase object, to access the data. With QSqlDatabase::database(connectionname) you can always retrieve a QSqlDatabase object to a given connection.

;) I hope this helps.

Binary91

@HoMa Hey, first of all, thank you very much for your detailed, fast support. That helps a lot!

So, just to understand it clearly: * You can have multiple processes to read from one SQLite DB at a time - but only one can write*
You mean at one time, right? Also multiple processes can write to the DB, but in a queue right?

You mean that one connection always refers to one DB, like a constant pair that should or even can't be changed, right? So when using 2 DB files, I always have to create two connection objects instead of switching between them, right? And to identify them, I give those connections names. And because the QSqlDatabase is a "value class", the ::database()-function always gives me the corresponding connection object, not a copy of it, right?

In case I understood things right, I need to know how to handle closing / removing those objects. Should I hold the connections through the whole runtime or should I instantiate a new connection each time I switch to another "part" of the program, like changing a scope or doing other stuff? Should I close a connection with db.close() or should I use removeDatabase() instead or both?

HoMa

@Binary91
You mean that one connection always refers to one DB, like a constant pair that should or even can't be changed, right? So when using 2 DB files, I always have to create two connection objects instead of switching between them, right? And to identify them, I give those connections names.
Yes, that sounds correct. In the end, what you want to do (mostly) is using QSqlQuery objects. If you create one without db -> you use the DB that was opened to the default connection. If you want to use a different DB, you have to give it a QSqlDatabase object, and you get if (after opening it etc) from QSqlDatabase::database(connectionname).

In case I understood things right, I need to know how to handle closing / removing those objects.
This can - in deed - be tricky. If - for example - you have QSqlQuery objects or QSqlDatabase object in the scope, closing can fail. Or at least you get an error that tells you, that connections got truncated.
Aside from this you just close the database and remove the connection - just reverse to how you opened the stuff.

Binary91

Ok, I see there exists more then one way to fit my issues.

So, I'll create two connections, one for the main DB and one for the user-specific DB.
Now I only need to know if I should create and open those connections at the beginning of the program start and close&remove it prior to exit of the program or should I add the connection at the beginning and open/close it each time I need read/write access to it or should I even add/remove the whole connection each time I need access to the DB? What is best performant?

HoMa

@Binary91 Opening and closing connections definitly takes time. If you do not need to copy or move the database files during runtime I see no issue with leaving the connections open until you definitly do not need them any longer.

Binary91

@HoMa Thank you, I'll try it that way.

Great real-time support, thank's a lot!

A.A.SEZEN

I think SQLite is pretty fast in opening and closing database connection as it is in other subjects. Again, I think it should be opened every time it is needed and closed when the job is done. As the program you're working on gets deeper and better, you don't have to worry about the status of SQLite connections and conflicts. Perhaps you would prefer to have the details of the on and off in separate functions as a cleaner and more readable coding.

Binary91

@A-A-SEZEN Hi,
thank you for your post.

Well, I already started putting the mechanism into separate methods of my self-made config class. So, you think I should open/close the connection each time I need to read/write from/to the db? Ok. When you say open/close, do you mean the whole connection object, I mean creating it with QSqlDatabase::addDatabase(...) / removeDatabase(...) ? Or should this be done only once at runtime and just the db.open() / db.close() functions should be called each time I need it?

A.A.SEZEN

I have it when I use a separate QSqlDatabase variable and connection name in each class, I also manage it jointly in a separate database class. After all, it makes sense when you use open() . It's just a first definition. In scope, the same connection can be used with open close. removeDatabase can be used in the class destructor or in a separate function.

void removeConnection(const QString& sConnectionName) {
    QStringList sl = QSqlDatabase::connectionNames();
    for (int a = 0; a < sl.size(); a++) 
        if (sl[a] == sConnectionName)
            QSqlDatabase::removeDatabase(sConnectionName);
}
//or
void removeConnection(const QString& sConnectionName) {
    for (auto &a: QSqlDatabase::connectionNames())
        if (a == sConnectionName)
            QSqlDatabase::removeDatabase(sConnectionName);
}
removeConnection("name");
conn = QSqlDatabase::addDatabase("QSQLITE", "name");
conn.setDatabaseName(DB_PATH_NAME);

HoMa

@A-A-SEZEN What is the reason one would enumerate the connections for closing one of them? From the documentation of addDatabase one can read, that there can only be one connection with a specific name. Re-adding will close the first one. Removing a non-existing connection will do no harm. So you can omit the loop.
Btw: The closing of a connection, if it already exists and gets re-opened will invalidate any QSqlDatabase and QSqlQuery object to this connection. These are exactly the issues, why I would avoid all the closing and opening of connections. If you get a deeper and deeper call stack this will become more and more difficult to maintain. One can create RAII objects for this - but it will gain only some overhead.

(talking about all this: I found the same loop in my own code - I think I should clean this up ;) )
Best regards
Holger

A.A.SEZEN

@HoMa It was enough for me that in the QtCreator Application output window, the second time the same connection was used, the information was "duplicated, so the old connection removed". In my opinion, this is a snag. When I was done, I started deleting it myself.

HoMa

@A-A-SEZEN I understand your concerns. Resource management is key in programming and especially in C++. You might investigate, when exactly the error popped up. You write the second time the same connection was used - if with "using" you mean "addDatabase" this is exactly the problem I wanted to make you aware of. Instead of re-adding you should just access a connection with QSqlDatabase::database( connectionname)
I recommend reading the details of the documentation of QSqlDatabase::addDatabase, database(), ::open and ::removeDatabase. There some really helpful glues are given.
Best regards
Holger

PS: if the flow through your code does not ensure, that a connection is open, you can always check a connection with the isValid() function.

A.A.SEZEN

@A-A-SEZEN said in SQLite management of multiple databases:

I have it when I use a separate QSqlDatabase variable and connection name in each class, I also manage it jointly in a separate database class.

I commend your effort. Thank you.
I thought I made it clear above. It means it didn't happen. "Google Translate" english... excuse me.
However, in practice, we encounter different scenarios. When building a class, I prefer to say "delete if there is already a connection with the same name" instead of a "use if already open connection" check. I can also do this in destructor. However, in terms of legibility in coding, I adopt the combination of related processes if possible.
For example, there is this situation in the constructor of the "Sansur" class below.

    connect(ui->pushSansur, &QPushButton::clicked, this, [&]() {
        auto * form = new Sansur();
        form->setModal(true);
        form->exec();
        delete form;
    });

Sansur::Sansur(QWidget *parent) : QDialog(parent),
    ui(new Ui::Sansur)
{
    ui->setupUi(this);
    this->setWindowIcon(QIcon("://imaj/warning.png"));
    this->setWindowTitle("Sansür Çalışması");

    QString sBaglantiAdi = "sansur";
    for (auto &a: QSqlDatabase::connectionNames()) {
        if (a == sBaglantiAdi)
            QSqlDatabase::removeDatabase(a);
    }
    QString sYol = QString("%1/Asoza/Sozluk").arg(QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation).at(0));
    vt = QSqlDatabase::addDatabase("QSQLITE", sBaglantiAdi);
    vt.setDatabaseName(QString("%1/sozluk.db3").arg(sYol));
    vt.open();
    vt.exec("PRAGMA journal_mode=MEMORY");
    vt.exec("PRAGMA CacheSize=10240");    
.....

jsulm

@A-A-SEZEN said in SQLite management of multiple databases:

vt = QSqlDatabase::addDatabase("QSQLITE", sBaglantiAdi);

Do NOT store QSqlDatabase instances as member variables!
This is explained in red color in the documentation (https://doc.qt.io/qt-6/qsqldatabase.html):
"Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior."

jsulm

@A-A-SEZEN said in SQLite management of multiple databases:

QString sBaglantiAdi = "sansur";
for (auto &a: QSqlDatabase::connectionNames()) {
if (a == sBaglantiAdi)
QSqlDatabase::removeDatabase(a);
}

Simply call

QSqlDatabase::removeDatabase(sBaglantiAdi);

A.A.SEZEN

@jsulm Thanks for your contribution.

Binary91

Hi together,

sorry for the belated reply.

Well, I see that I am not the only one who discusses about the right way to deal with that.

I decided now to have a class called "configMain" and in that class, by calling the constructor while instantiating, I create a connection (addDatabase) and in the destructor I remove the connection (removeDatabase). So the connection stays as long as the mainConfig object is alive (the class that handles the database).

In the program code, I open and close this connection each time I need access to the database (x.open(), x.close()). I get access to the database connection with the static method QSqlDatabase::database(identifier) everywhere global in the code, what is really well so I dont need (and, as jsulm correctly mentioned) also shouldnt create a member variable of the QSqldatabase object.

Well, what do you think about that way to deal with it? Good or bad?

jsulm

@Binary91 said in SQLite management of multiple databases:

Well, what do you think about that way to deal with it? Good or bad?

Good, just not sure about this part: "In the program code, I open and close this connection each time I need access to the database". You wrote that you open the connection once in the constructtor of configMain and close it again in the destructor, so how does this match with what you wrote in the cited text?

Binary91

@jsulm Hi,

what I meant was the differentiation of "creating" the connection with QSqlDatabase::addDatabase(xxx) in the constructor (and so removeDatabase(xxx) in the destructor) and opening / closing it with the database object's methods db.open() and db.close() each time I need the connection for access to the database... And I would do it that way for any database I have. Creating the connections early and hold it to the end, but the opening/closing of these connections is done repetitively while runtime each time I need access to the database.

Is that the correct way of use?