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

ealione

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?

Leonardo

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);

ealione

@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.

SGaist

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.

ealione

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

Leonardo

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.

ealione

Indeed, it should be faster too.

ealione

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?

SGaist

What value are you getting ?

ealione

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

SGaist

Looks good

What is your problem with it ?

rturrentine

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

ealione

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.

rturrentine

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

Leonardo

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".

rturrentine

BTW, here is a link to a related post:

http://forum.qt.io/topic/29289/solved-qt5-1-android-how-to-bundle-and-use-sqlite-database-in-application