topic Re: Impossibility to execute VACUUM query in ArcGIS AppStudio Questions

Impossibility to execute VACUUM query

José_LuisGomez — Mon, 28 Jan 2019 10:15:02 GMT

I need to perform a "VACUUM" on my app database using QtQuick.LocalStorage 2.0 or using the SqlDatabase Type but pointing the database from QtQuick.LocalStorage.

Must work on Android and iOS devices.
Any idea?

Re: Impossibility to execute VACUUM query

StephenQuan1 — Tue, 29 Jan 2019 01:48:27 GMT

José can you tell us what you have tried?

Re: Impossibility to execute VACUUM query

José_LuisGomez — Tue, 29 Jan 2019 09:30:35 GMT

I tried to execute a VACUUM using LocalStorage, but is not able to run it because it must be executed from outside a transaction.

My database must sometimes handle a large amount of data.
But even if you delete those rows or even delete the tables the space occupied by the app continues to increase.

Re: Impossibility to execute VACUUM query

StephenQuan1 — Sun, 12 Dec 2021 09:08:21 GMT

Hi José, in the following example, I'm able to use `VACUUM` on a `SqlDatabase`:

import QtQuick 2.7
import QtQuick.Controls 2.1

import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Controls 1.0
import ArcGIS.AppFramework.Sql 1.0

App {
    id: app
    width: 800
    height: 640

    property string dbFileName: "sample.sqlite"
    property string dbFilePath: sqlFolder.filePath(dbFileName)

    ListView {
        id: listView
        property ListModel logListModel: ListModel { }
        anchors.fill: parent
        anchors.margins: 10
        model: logListModel
        delegate: Text { text: logText }
        function log(logText) { logListModel.append( { logText: logText } ); }
    }

    FileFolder {
        id: sqlFolder
        path: "~/ArcGIS/Data/Sql"
    }

    SqlDatabase {
        id: db
        databaseName: dbFilePath
    }

    function db_exec_sql(sql, obj) {
        listView.log( "db_exec_sql.sql: %1".arg(sql) );
        if (obj != null) {
            listView.log( "db_exec_sql.obj: %1".arg(JSON.stringify(obj)) );
        }
        var query = obj != null ? db.query(sql, obj) : db.query(sql);
        if (query.error) {
            listView.log( "query.error: %1".arg(query.error) );
            return;
        }
        var ok = query.first();
        while (ok) {
            listView.log( "query.values: %1".arg(JSON.stringify(query.values)));
            ok = query.next();
        }
        query.finish();
    }

    Component.onCompleted: {
        listView.log( "dbFileName: %1".arg(dbFileName) );
        listView.log( "dbFilePath: %1".arg(dbFilePath) );

        sqlFolder.makeFolder();

        db.open();

        db_exec_sql( "DROP TABLE IF EXISTS people" );
        db_exec_sql( "CREATE TABLE people (NAME text)" );
        db_exec_sql( "INSERT INTO people VALUES (:name) ", { name: 'Bill Gates' } );
        db_exec_sql( "INSERT INTO people VALUES (:name) ", { name: 'Jack Dangermond' } );
        db_exec_sql( "SELECT * FROM people" );
        db_exec_sql( "VACUUM" );
        db_exec_sql( "SELECT * FROM people" );
    }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The program completes successfully, and, because I use `~/ArcGIS/Data/Sql`, the folder will exist on all platforms. The output I get when I run the above on Windows is:

dbFileName: sample.sqlite
dbFilePath: C:/Users/stephen/ArcGIS/Data/Sql/sample.sqlite
db_exec_sql.sql: DROP TABLE IF EXISTS people
db_exec_sql.sql: CREATE TABLE people (NAME text)
db_exec_sql.sql: INSERT INTO people VALUES (:name) 
db_exec_sql.obj: {"name":"Bill Gates"}
db_exec_sql.sql: INSERT INTO people VALUES (:name) 
db_exec_sql.obj: {"name":"Jack Dangermond"}
db_exec_sql.sql: SELECT * FROM people
query.values: {"NAME":"Bill Gates"}
query.values: {"NAME":"Jack Dangermond"}
db_exec_sql.sql: VACUUM
db_exec_sql.sql: SELECT * FROM people
query.values: {"NAME":"Bill Gates"}
query.values: {"NAME":"Jack Dangermond"}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Impossibility to execute VACUUM query

JorgeFrade_Martínez — Fri, 08 Feb 2019 09:05:39 GMT

Hi Stephen,

I´m a workmate of Jose, he has some trouble with his account and can´t answer and has told me that send this to you.

Yeah, i know.
But i need to do the vacuum to the QtQuick.LocalStorage 2.0 database.
Its mandatory to me, due to the needs of the project, use QtQuick.LocalStorage 2.0.