🟥 [FREE] SimpleSqlite - an extension for working with SQLite Databases

[TIMAI2]

This is a fork/rework/remix of Tom Bennedum's open-sourced aix-SQLite extension. (all Credits to Tom!)
I have stripped it back to a bare-bones/expert version, without (hopefully) affecting functionality.

The key to working with this extension/sqlite is:

• being clear about which database file and also which database you are working with
• remembering to OPEN the database to carry out data operations (SELECT etc)
• remembering to CLOSE the database to carry out file operations (IMPORT/EXPORT etc)
• the default file path for IMPORT/EXPORT is the ASD (or Assets if you use // on IMPORT)
• knowing that all SELECT queries are returned as AI2 lists
• Use the ListFixer block when an Sqlite query returns a list of lists

I have written a guide to complement this release:

Working with SQLite

.
.

• Latest Version: 4krelease (now provides for export/naming to/from shared directories - Download or Documents)
• Tested: This has been tested on App Inventor 2 using Android 9, 10 and 13 devices, companion 2.70, and compiled apk. It may work on other derivatives....e.g. Kodular/Niotron/etc.
• Released: 2022-05-18T23:00:00Z
• Last Updated: 2024-05-23T23:00:00Z
• Built: Using the Rush Extension Builder by @shreyash

BLOCKS & USAGE:

Usage covered in the guide above

Extension

SimpleSqliteV4

Also see:
Integrating SQLite with Google Sheets

Enjoy , and all feedback, comments and questions welcome.

Available for use in any MIT AppInventor projects or competitions

#####################################################################
This work by TIMAI2 is licensed under an
MIT Licence
#####################################################################

[SteveJG]

i use Tom Bennedum's original extension; it has suited my needs. Will give this remix a try and report.

Thanks.

[Juan_Antonio]

Works for me.

@TIMAI2 is it possible to ExportDatabase as /peopleCodes.db in ASD and then read it with the File.ReadFrom component?

[Don_Ammar]

Thanks for this great extensions as always you are awesome and genius

i was extracting one of .db files using this [ SQLiteStudio ] ( https://sqlitestudio.pl/ )

you can create them easily as well extract other apks db files

[TIMAI2]

@Juan_Antonio

is it possible to ExportDatabase as /peopleCodes.db in ASD and then read it with the File.ReadFrom component?

The sqlite db is not in a text format suitable for reading with file component.

It is possible to query the entire table then save the result to a csv file for future reading.

[TIMAI2]

@Juan_Antonio

Please read my guide again, which I have updated, I found a quirk with the return of list of lists....

[Juan_Antonio]

I was testing this @TIMAI2 extension and noticed that sometimes (like a daemon) when I deleted the aaaaaa.db file, .db-shm and .db-wal were created, here I found the explanation:

What are the .db-shm and .db-wal extensions in Sqlite databases?

This is just a test application:

Two database paths are established: ASD/peoplesCode.db and ASD/peoplesCode2.db.
It opens, creates, inserts and selects in peoplesCode.db.

Store this file to CloudDB (in Base64 - extension).

Get as peoplesCode2.db (from Base64 - extension).
Open and Select * of peoplesCode2.db

If you want to go back to the peoplesCode.db you must Click in OpenDatabase peoplesCode.db

Borrar_Sqlite.aia (54.8 KB)

[TIMAI2]

How are you deleting the database? This should only be possible using external file management, nothing to do with the extension. But best practice would be to ensure the database is closed and not the "main" database before deleting?

[Juan_Antonio]

My comment was only because I was unaware of the existence of the .db-wal and .db-shm files.

I have tested it on an Android 9, MIT Comp. and installed.

When I delete the ASD/peoplesCode.db file without closing it, the .db-wal and .db-shm files are created.

I delete it using the File component, File.Delete

Interestingly, if I now close the database and File.Delete: ASD/peoplesCode.db again, the .db-wal and .db-shm are deleted, they are shared memory files.

[josep_maria_pons_mor]

Hi team

, I need som help about SQLlite extension when I working with companion on the phone, I try to import the database from the phone, this is a empty database with all tables and I need to begin the app with this database, not to 'create' the tables on then phone. Well, with sqllitestudio I create this database and transfer it to phone but the "Import" method of this extension return all time 'error', pls, can someone tell me where I can put the empty database for the import to work. thans.

[Taifun]

From the documentation

@TIMAI2

the default file path for IMPORT/EXPORT is the ASD (or Assets if you use // on IMPORT)

The simplest way is to upload the database into the assets of your app, see also @TIMAI2's guide METRIC RAT AI2 - Working with SQLite

Else use the file component to copy it from shared storage /Download or /Documents to the ASD and import from there

Taifun

[TIMAI2]

Which extension are you using ? (SQLite1) ?

[aiotech]

Mmmm... may be OK set column headers to false using a clock started by Screen1.Initialize?

[TIMAI2]

@aiotech

Mmmm... may be OK set column headers to false using a clock started by Screen1.Initialize

Column Headers Bug now fixed, download latest version 3
Also ListFixer merged to main extension
SimpleSqliteV3

[Anke]

Android 8.1:

Android 12:

[TIMAI2]

For Android <10 there is a bug (typo ?) that carried over from the original extension

return getExternalStoragePath() + "/AppInventor/data/";

should read

return getExternalStoragePath() + "/AppInventor/assets/";

Should now be fixed

For Android 12, just use //myDatabase.sqlite, extension is looking for // for asset files

[Anke]

Android 8.1 - a relative path seems to work:

[Anke]

Android 11: (this path is redirected to the ASD)

[Anke]

... the same of course on Android 10+

[Anke]

On Android ≤ 9 a relative path must therefore be set (at least as long as this is not adjusted in the extension).