Impossibility to execute VACUUM query

Question

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?

JorgeFrade_Martínez · Answer

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.

StephenQuan1 · Answer

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"}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

José_LuisGomez · Answer

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.