[SOLVED]What would be the best way to have database support in my application.



  • I am creating a small application mainly to test deploying for Android. So I am wondering what would be the best way for having a database to save various data. I used postgresql until now, and every computer that would have to run my app needed to have a postgresql server.

    I have also seen the examples from Qt where they use an in-memory sqlite database but I am not sure how can I use it to persistently save data. I mainly want my application to be self contained and not have to require a Mysql or some other server.

    Does anybody have any suggestions on this?



  • I'd recommend SQLite. When launching you're app, copy your embedded database to a writable location, so you can store data persistently.

    QString dbPath = QDir(QStandardPaths::writableLocation(QStandardPaths::AppDataLocation)).filePath("database.db");
    
    QFile::copy(":/database.db", dbPath);
    
    ...
    
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    
    db.setDatabaseName(dbPath);


  • @Leonardo hi and thanks for the answer. I am not sure why do I need this line

    QDir(QStandardPaths::writableLocation(QStandardPaths::AppDataLocation)).filePath("database.db");

    This would only be useful if I already have a database file.

    In my case where the app creates its own database if it does not exist, then simply

    db.setDatabaseName("./database.db");

    would suffice, right?

    Also from your experience how does sqlite perform? It could theoretically scale up to quite a few hundred thousand records.


  • Lifetime Qt Champion

    Hi,

    You need this line to ensure that your database file is in a known writable location. Opening it in the same folder as your executable might work when developing but as soon as you'll install your application it will likely be in a read-only folder.

    Also, if your application is meant to be used by several users you'll have a security and privacy problem. One user will see the data of another or overwrite it which will be bad.

    With SQLite, if the file given doesn't exist, it will be created to store the database.



  • Hi SGaist, thanks for the comment, as per usual it's right to the point.
    I will implement it this way.



  • You don't need to create your database programmatically. You could create an empty database with all your tables and relationships and store it as a resource in your app. Then you would copy this resource to a writable location if it didn't exist yet. That's what I suggested. It would be like a template database.



  • Indeed, it should be faster too.



  • The location created from this command

        db_path = QDir(QStandardPaths::writableLocation(QStandardPaths::AppDataLocation)).filePath("database.db");
    

    does not seem to actually exist in my system, moreover the copy command fails.

    Is it normal that db_path returns a location that does not exist?


  • Lifetime Qt Champion

    What value are you getting ?



  • db_path has a value of "C:/Users/eali/AppData/Roaming/ealione/v002/database.db"


  • Lifetime Qt Champion

    Looks good

    What is your problem with it ?



  • Maybe you think the folder doesn't exist because the "AppData" folder is hidden by default.



  • Looks correct indeed, the thing is that it does not exist (not simply hidden), and the copy command:

    QFile::copy(":assets/database/database.db", db_path returns false.



  • Is ":assets" Android specific? Also should it be "assets:/datatbase...."



  • QStandardPaths returns a writable path for you. It doesn't guarantee you that it exists. If if doesn't, create it using QDir.mkpath()

    http://doc.qt.io/qt-5/qdir.html#mkpath

    As you know it's a writable location, it won't be a problem.

    =========

    @rturrentine is right also. ":assets" is for android. For Qt resources, you should use ":/database/database.db".




Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.