How to set Local Storage path for Android for Sqlite

[edip]

Hi. I try to embed the sqlite file generated by QML's Local Storage for Android. The code below works for desktop when I specify a path in decktop. Although there are sqlite and ini files in Android's assets folder, the app doesn't see the database file as default. How can I get it work?

The code in main.cpp:

QString filePath = QStandardPaths::writableLocation( QStandardPaths::StandardLocation::AppLocalDataLocation );
    QDir dir;
    if(dir.mkpath(QString(customPath))){
            qDebug() << "Default storage path >> "+engine.offlineStoragePath();
            engine.setOfflineStoragePath(QString(filePath+"/qml/OfflineStorage"));
            qDebug() << "New storage path >> "+engine.offlineStoragePath();
        }
    engine.clearComponentCache();

[Tom_H]

@edip The database doesn't exist at that location. You have to create it. If you want to distribute a database with your app, copy it from your app's resource location into the new OfflineStoragePath.

[edip]

@Tom_H I have an existing sqlite database in asset folder of Android. In project path it looks like this:

android
|
 assets
 └── qml
    └── OfflineStorage
        └── Databases
            ├── e04d88072f90f86a07481418b8ff4b6b.ini
            └── e04d88072f90f86a07481418b8ff4b6b.sqlite

I added sqlite and ini files to qrc. After compiling the app, I extracted apk file, and there are sqlite file in asset folder. I think the only problem is setting assets folder as default path in c++.
On the other hand I am able to set asset path in QML. For example in the project, I have WebView.qml page that html and css files are in assets folder in project. When I set assets path as:

WebView {
            id: webViewUrl
            url: "file:///android_asset/html/index.html" 
            width: parent.width
            height: parent.height
        }

it is working on Android. But when I try the same path in c++ it doesn't recognize assets folder:

QString filePath = "file:///android_asset/qml/OfflineStorage";
        QDir dir;
        if(dir.mkpath(QString(filePath))){
                qDebug() << "Default storage path >> "+engine.offlineStoragePath(); 
// Default storage path >> /data/user/0/org.project.surveyingcalculator/files/QML/OfflineStorage"
                engine.setOfflineStoragePath(QString(filePath));
                qDebug() << "New storage path >> "+engine.offlineStoragePath();
// New storage path file:///android_asset/qml/OfflineStorage
            }
        engine.clearComponentCache();

Is there any way to set assets folder as default storage path in c++?
Thank you.
Edit: The sqlite works with my app on desktop with this code in main.cpp:

// Desktop storage path
    QString filePath = "./android/assets/qml/OfflineStorage";
        QDir dir;
        if(dir.mkpath(QString(filePath))){
                engine.setOfflineStoragePath(QString(filePath));
            }
        engine.clearComponentCache();

[Tom_H]

@edip I think it has to be a valid writable location on Android. I suggest putting your database in a qrc file, then copying it to a writable location on app startup. I use this technique for certain files. I also call setOfflineStoragePath, but I specify a writable location. Try something like this:

QString appdir()
{
    QStringList dirs = QStandardPaths::standardLocations(QStandardPaths::AppDataLocation);
    if (dirs.length() >= 2)
        return dirs[1];
    if (dirs.length() == 1)
        return dirs[0];
    return "";
}

QString dbpath = appdir() + "/my.db";
QFile::copy(":/res/databases/my.db", dbpath);

[edip]

Thank you. I try now.

[edip]

@Tom_H Your technique worked on AppLocalDataLocation and AppDataLocation paths. ini and sqlite files are copied in the folder but sqlite file was empty database. *.ini file was copied properly. I deleted the empty sqlite file and I did copy-paste the original database , the app worked. But why is it copied empty sqlite file when running the app first time?

My code in main.cpp:

QString appdir()
{
    QStringList dirs = QStandardPaths::standardLocations(QStandardPaths::AppDataLocation);
    if (dirs.length() >= 2)
        return dirs[1];
    if (dirs.length() == 1)
        return dirs[0];
    return "";
}
QString dbpath = appdir() + "/QML/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.sqlite";
    QString dbpath_ini = appdir() + "/QML/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.ini";
    QString offline_path = appdir() + "/QML/OfflineStorage";
    engine.setOfflineStoragePath(QString(offline_path));
    qDebug() << "New storage path >> "+engine.offlineStoragePath();

    QDir dir(appdir() + "/QML/OfflineStorage/Databases");
    if (!dir.exists()){
      dir.mkdir(".");
    }
    qDebug() << "dbpath: >> " + dbpath;
    QFile::setPermissions(dbpath ,QFile::WriteOwner | QFile::ReadOwner);
    QFile::setPermissions(dbpath_ini ,QFile::WriteOwner | QFile::ReadOwner);

    if (QFile::exists(dbpath))
    {
        QFile::remove(dbpath);
    }
    QFile::copy(":android/assets/qml/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.sqlite", dbpath);
    QFile::copy(":android/assets/qml/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.ini", dbpath_ini);

Edit: When the app runs for the first time, the database isn't active. When the app runs second time the database works and sqlite file is created properly. copy process of sqlite file doesn't initialize by main.cpp

[Tom_H]

@edip I don't know, but does your initial database contain data or is it just empty structure? If it's empty structure, then I recommend creating it on the fly, which is what I do. Then you don't have to copy anything. Something like this:

[db.js]

.pragma library
.import QtQuick.LocalStorage 2.12 as Db

function getdb()
{
    return Db.LocalStorage.openDatabaseSync("MyDb", "1.0", "My Database", 1000000)
}

function init()
{
    try {
        var db = getdb()
        db.transaction(function (tx) {
            tx.executeSql('create table if not exists ...')
        })
    }
    catch (err) {
        console.log('Error creating database: ' + err)
    }
}

[edip]

@Tom_H Unfortunately I have many data in sqlite which I collected them from json data. I should use existing database. If I knew c++ I would do it from QSQLITE driver but I don't know c++. That's why I try to use Local Storage's sqlite file on Android.

[Tom_H]

@edip Are you copying the database before loading the main QML file?

[edip]

@Tom_H Yes like this:

...
    QFile::copy(":android/assets/qml/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.sqlite", dbpath);
    QFile::copy(":android/assets/qml/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.ini", dbpath_ini);
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbpath);
    db.open();
    engine.load(QUrl(QStringLiteral("qrc:/main.qml"))
....

[edip]

@Tom_H Here is a sample app that is unable to copy files in Android's local folder.

[Tom_H]

@edip I don't think the C++ QSqlDatabase has any affect on QML LocalStorage, but I don't know. I'd have to look at the Qt source code. The question boils down to "How do we tell LocalStorage to use an existing database?". Surely there must be a way. If not, you'll either have to do your database access in C++, or import your data in LocalStorage (you can export the required DDL and DML from either sqlite command line or sqlitebrowser). Or, since it works on the 2nd startup, find a graceful way to do that.

But before doing that, try to get in touch with a Qt developer on IRC or the mailing list. I'm out of ideas. Good luck. Please post the solution here when you get a chance.

[edip]

Thank you @Tom_H . I think when running Local Storage db it first look for its database, when it doesn't find any database, it creates an empty database. In github sample I just tried to copy two files to Android's path, it didn't work. Actually the question is how to copy a file to Android's path? I will research about it and I will write the solution here.
This record shows the problem :
https://streamable.com/vkhy0

[edip]

This code in main.cpp worked. I know there should be better way, sorry I don't know c++ :) I will make it a class later. The code sets default storage path and it copies ini and sqlite files to Android App Data location.
@Tom_H thanks for solutions!

// set default Local Storage path:
qDebug() << "Default storage path >> "+engine.offlineStoragePath();
    QString localPath = QStandardPaths::writableLocation( QStandardPaths::AppDataLocation );
    QString dbpath = appdir() + "/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.sqlite";
    QString offline_path = appdir() + "/OfflineStorage";
    engine.setOfflineStoragePath(QString(offline_path));
    qDebug() << "New storage path >> "+engine.offlineStoragePath();
    
    // Copy sqlite file
    QString dbName = "e04d88072f90f86a07481418b8ff4b6b.sqlite";
    // determine source path
    QString dbSourcePath = ":android/assets/qml/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.sqlite";
    QFileInfo dbSourceInfo(dbSourcePath);

    // determine destination path
    QDir writableLocation(appdir() + "/OfflineStorage/Databases/");
    if (!writableLocation.exists()) {
        writableLocation.mkpath(".");
    }
    QString dbDestPath = writableLocation.filePath(dbName);
    QFileInfo dbDestInfo(dbDestPath);

    // determine if the source db has changed
    bool dbSourceUpdated = dbSourceInfo.lastModified() > dbDestInfo.lastModified();

    // copy or replace db if needed
    if ((!dbDestInfo.exists()) || dbSourceUpdated) {
        QFile::remove(dbDestPath);
        if (!QFile::copy(dbSourcePath, dbDestPath)) {
            qCritical() << "ERROR: source db " << dbSourcePath << " not copied to "<< dbDestPath;
            //return false;
        } else {
            qDebug() << "db successfully copied or replaced to " << dbDestPath;
        }

    } else {
        qDebug() << "dest db " << dbDestPath << " already exists";
    }

    // make db writable
    QFile::setPermissions(dbDestPath, QFile::WriteOwner | QFile::ReadOwner);
    qDebug() << "current db: " << dbDestPath;

    // Copy ini file
    QString dbName_ini = "e04d88072f90f86a07481418b8ff4b6b.ini";
    QString dbSourcePath_ini = ":android/assets/qml/OfflineStorage/Databases/e04d88072f90f86a07481418b8ff4b6b.ini";
    QFileInfo dbSourceInfo_ini(dbSourcePath_ini);
    QDir writableLocation_ini(appdir() + "/OfflineStorage/Databases/");
    if (!writableLocation_ini.exists()) {
        writableLocation_ini.mkpath(".");
    }
    QString dbDestPath_ini = writableLocation_ini.filePath(dbName_ini);
    QFileInfo dbDestInfo_ini(dbDestPath_ini);
    bool dbSourceUpdated_ini = dbSourceInfo_ini.lastModified() > dbDestInfo_ini.lastModified();
    if ((!dbDestInfo_ini.exists()) || dbSourceUpdated_ini) {
        QFile::remove(dbDestPath_ini);
        if (!QFile::copy(dbSourcePath_ini, dbDestPath_ini)) {
            qCritical() << "ERROR: source db " << dbSourcePath_ini << " not copied to "<< dbDestPath_ini;
            //return false;
        } else {
            qDebug() << "db successfully copied or replaced to " << dbDestPath_ini;
        }
    } else {
        qDebug() << "dest db " << dbDestPath_ini << " already exists";
    }
    QFile::setPermissions(dbDestPath_ini, QFile::WriteOwner | QFile::ReadOwner);
    qDebug() << "current db: " << dbDestPath_ini;