How to use relative path on SQLite .db file on iOS

[Kiovtorov]

I tried in a test app that I can not copy a file from a resource file (.qrc). I am now searching for another way

[JonB]

@Kiovtorov
First try again on QFile::copy() but remove the qrc, keep the :, so QFile resourceFile(":/resources/Finbank.db");. https://stackoverflow.com/questions/10724899/how-to-copy-qrc-resource-file-into-filesystem
https://forum.qt.io/topic/13131/how-to-extract-a-resource-from-qrc-solved
Hopefully that does work? I think it's supposed to.

If it really does not work, you would have to do the copy yourself via QFile::open(), read content, write to extracted file location.

[SGaist]

@JonB is correct, I missed that in your code. qrc:/ is used in QML because it uses URI however, in the C++ world, it's :/ that is used to signify that you want to access a file in the resource system.

[Kiovtorov]

Hi @JonB

Here is the corrected path but it doesn't work

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Determine the writable location for the database
    QString writablePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QString databasePath = writablePath + "/Finbank.db";

    // Ensure the writable directory exists
    QDir dir(writablePath);
    if (!dir.exists()) {
        dir.mkpath(writablePath);
    }

    if (!QFile::exists(databasePath)) {
        QFile resourceFile(":/resources/Finbank.db");
        if (resourceFile.exists()) {
            if (resourceFile.open(QIODevice::ReadOnly)) {
                QFile destinationFile(databasePath);
                if (destinationFile.open(QIODevice::WriteOnly)) {
                    destinationFile.write(resourceFile.readAll());
                    resourceFile.close();
                    destinationFile.close();
                    qDebug() << "Database successfully copied to writable location.";
                } else {
                    qDebug() << "Failed to open destination file:" << destinationFile.errorString();
                }
            } else {
                qDebug() << "Failed to open resource file:" << resourceFile.errorString();
            }
        } else {
            qDebug() << "Database resource file not found.";
        }
    }

    // Set the SQLite database file location
    m_db.setDatabaseName(databasePath);

    // Open the database connection and handle success or failure
    if (m_db.open() && m_db.isOpen()) {
        qDebug() << "Database opened successfully at" << databasePath;
    } else {
        // Handle connection error
        qDebug() << "Database failed to open";
        qDebug() << m_db.lastError().text();
    }
}

I get an output of

Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"

I was unable to access that directory to check the file but nevertheless I think it is generated .db file which is 0 bytes and not the db file from the resource file.
I am currently looking at a stackoverflow case - https://stackoverflow.com/questions/50828041/how-to-open-database-sqlite-file-on-iphone-real-device

But i am getting no success so far because I do not have Application Support folder like him

[Kiovtorov]

@JonB AI thought of copying the database from the CMakeFIle.txt which worked

cmake_minimum_required(VERSION 3.16)

project(sqlite VERSION 0.1 LANGUAGES CXX)

set(CMAKE_CXX_STANDARD_REQUIRED ON)

find_package(Qt6 6.5 REQUIRED COMPONENTS Quick Sql)

qt_standard_project_setup(REQUIRES 6.5)

qt_add_executable(appsqlite
    main.cpp
)

qt_add_qml_module(appsqlite
    URI sqlite
    VERSION 1.0
    QML_FILES
        Main.qml
        SOURCES maketransactions.hpp maketransactions.cpp
        RESOURCES resources.qrc
)

# Qt for iOS sets MACOSX_BUNDLE_GUI_IDENTIFIER automatically since Qt 6.1.
# If you are developing for iOS or macOS you should consider setting an
# explicit, fixed bundle identifier manually though.
set_target_properties(appsqlite PROPERTIES
#    MACOSX_BUNDLE_GUI_IDENTIFIER com.example.appsqlite
    MACOSX_BUNDLE_BUNDLE_VERSION ${PROJECT_VERSION}
    MACOSX_BUNDLE_SHORT_VERSION_STRING ${PROJECT_VERSION_MAJOR}.${PROJECT_VERSION_MINOR}
    MACOSX_BUNDLE TRUE
    WIN32_EXECUTABLE TRUE
)

target_link_libraries(appsqlite
    PRIVATE Qt6::Quick Qt6::Sql
)

include(GNUInstallDirs)
install(TARGETS appsqlite
    BUNDLE DESTINATION .
    LIBRARY DESTINATION ${CMAKE_INSTALL_LIBDIR}
    RUNTIME DESTINATION ${CMAKE_INSTALL_BINDIR}
)


# Add the database file to the bundle
set(APP_RESOURCES Finbank.db)

# Copy resources to the macOS/iOS bundle or build directory
foreach(resource ${APP_RESOURCES})
    configure_file(${resource} ${resource} COPYONLY)
endforeach()

# Ensure it is included in the app bundle
if(APPLE)
    set_source_files_properties(${APP_RESOURCES} PROPERTIES MACOSX_PACKAGE_LOCATION "Resources")
endif()

# Include it in the target
target_sources(appsqlite PRIVATE ${APP_RESOURCES})

The problem there is that for different OSs the path to this db file is different. It generates it at

/Users/boyankiovtorov/Documents/sqlite/build/Qt_6_8_0_for_macOS-Debug/Finbank.db

Do you think it is a good idea?

[jsulm]

@Kiovtorov But do you see any of the errors you're printing (like "Failed to open resource file:"")?

[Kiovtorov]

@jsulm
No, I am getting

Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"

but it is just because it automatically generates it.

[jsulm]

@Kiovtorov said in How to use relative path on SQLite .db file on iOS:

but it is just because it automatically generates it.

If it automatically generates it the copying failed.
Run through debugger step by step to see what happens.

[Kiovtorov]

I just tested a new method which works (kind of)

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get a writable location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    qDebug() << "Database path:" << fullDbPath;

    // Check if the database file exists
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist, creating a new one.";
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully";

        if (!QFile::exists(fullDbPath)) {
            // If the database is newly created, initialize tables
            QSqlQuery query(m_db);
            query.prepare("SELECT * FROM users WHERE username = :username");
            query.bindValue(":username", "test");

            if (!query.exec()) {
                qDebug() << "Error creating settings table:" << query.lastError().text();
            } else {
                qDebug() << "Settings table created successfully.";
                qDebug() << "lite: " << query.value("username");
            }
        }
    } else {
        // Handle connection error
        qDebug() << "Database failed to open";
        qDebug() << m_db.lastError().text();
    }
}

it creates a database in my Documents folder or the ios devide documents folder. I tested if it works with creating a new table

QSqlQuery query(db);
            query.prepare("CREATE TABLE settings ("
                          "`id` INTEGER PRIMARY KEY AUTOINCREMENT, "
                          "`username` TEXT, "
                          "`password` TEXT, "
                          "`remember_login` TEXT);");
            if (!query.exec())
            {
                qDebug() << "ERROR: FAILED TO CREATE TABLE -" << query.lastError().text();
            }

            query.prepare("INSERT INTO settings (username, password, remember_login) VALUES ('aaa', 'bbb', '1')");
            query.exec();

            query.prepare("SELECT * FROM settings");
            query.exec();

and it works on macos and ios. Before I couldn't even find the file so I can now at least debug more. I will now try to copy the data from my original sqlite file to that one in the documents

[Kiovtorov]

FINALLY!
I don't know what is the difference between a db file and sqllite file but with .sqlite file the copying from .qrc file works
Here is my code for everyone with the same problem

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get a writable location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    qDebug() << "Database path:" << fullDbPath;

    // Check if the database file exists in the writable location
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist in Documents, copying from resources.";

        // Path to the resource database file
        QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file

        // Attempt to copy the database from resources to the writable location
        if (QFile::copy(resourceDbPath, fullDbPath)) {
            qDebug() << "Database copied successfully to Documents.";
            // Set file permissions to writable (necessary for some platforms)
            QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
        } else {
            qDebug() << "Failed to copy database from resources.";
        }
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully.";

        // Sample query to verify the database contents
        QSqlQuery query(m_db);
        query.prepare("SELECT * FROM users WHERE username = :username");
        query.bindValue(":username", "test");

        if (!query.exec()) {
            qDebug() << "Error executing query:" << query.lastError().text();
        } else {
            while (query.next()) {
                qDebug() << "Username:" << query.value("username").toString();
            }
        }
    } else {
        // Handle connection error
        qDebug() << "Database failed to open.";
        qDebug() << m_db.lastError().text();
    }
}

[Kiovtorov]

Here is the same code using appDataLocation

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get the application data location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    QDir dbDir(dbLocation);
    if (!dbDir.exists()) {
        if (dbDir.mkpath(dbLocation)) {
            qDebug() << "Created application data directory:" << dbLocation;
        } else {
            qDebug() << "Failed to create application data directory:" << dbLocation;
            return; // Exit if the directory cannot be created
        }
    }

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    // Check if the database file exists in the application data location
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist in AppDataLocation, copying from resources.";

        // Path to the resource database file
        QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file

        // Attempt to copy the database from resources to the writable location
        if (QFile::copy(resourceDbPath, fullDbPath)) {
            qDebug() << "Database copied successfully to AppDataLocation.";
            // Set file permissions to writable (necessary for some platforms)
            QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
        } else {
            qDebug() << "Failed to copy database from resources.";
            return; // Exit if the database cannot be copied
        }
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully.";

        // Sample query to verify the database contents
    } else {
        // Handle connection error
        qDebug() << "Database failed to open.";
        qDebug() << m_db.lastError().text();
    }
}