Working with SQLite

INTRO

Most, if not all, Android devices have an sqlite program in there somewhere,  Android uses sqlite databases as a part of its infrastructure. The good news is the program is available to us in AppInventor, and we can use it to manage large datasets and relational databases in our apps.

There have been several paid and free extensions written by developers for use in AppInventor. One of these, "aix-SQLite" written by Tom Bennedum, is open sourced. I have forked this code, and stripped it back to make a barebones or expert sqlite extension, far fewer blocks, but retaining all the functionality. I have named this SimpleSqlite and will use this extension to demonstrate how to use the sqlite program and databases.

SimpleSqlite Extension features:

• a default database file called: SimpleSqlite.db, stored in the private area of the app

• ability to load a database using an absolute path to a readable/writable database file

• ability to import and export the current database from/to a readable/writable location

• ability to attach/detach additional databases to the current database, so they can be worked on at the same time

• ability to import databases from the app assets or from the ASD - the default path

• provides for the use of SELECT, ATTACH/DETACH, CREATE (TABLE), INSERT, UPDATE and DELETE   SQLite queries and functions

• requires a database to be "OPEN" in order to work with the data therein, and for the database to be "CLOSED" to carry out file management activities

• It is not possible to use SQLite "dot" commands (e.g. ".tables"), although some of these are available using SELECT

• It is not possible to use PRAGMA commands, although a small few can be run using SELECT

• Each app has its own SQLite database, these are not, by default, shared across the android file system for access by other apps

• returns AI2 list of lists in the correct format with ListFixer block

• ability to export databases to Shared Storage, namely Download or Documents.

• ability to set DBName property to a database in Shared Storage, to then be able to work directly with that database, without having to import to the default database.

See here for the best tutorial resource on SQLite functions

https://www.sqlitetutorial.net

You will need to interpret some of the guidance there in order to apply it, or disregard it!

HOW DOES IT WORK ?

• At the very beginning, when you add the extension, and load the app for the first time, in companion, SQLite uses the default database; SimpleSqlite.db. This is empty to begin with, no tables or data. You can if you wish, just start using it; open the database, then: creating tables, inserting data, making queries. When you restart your app, it will again load SimpleSqlite.db and all the data you created will still be there. So far, so good. However, you may want to export all your hard work to another database file, on a regular basis, because, as I have said, the default database "always" loads when you start the app, you do not want to overwrite all your hard work if importing another database!
  
  

• This is where it can be a little confusing. In other content programs, the idea of importing a file would mean your original file would still be there, and you would have another file, the one you imported. This is not the case with SQLite. If you import a database to the current database, any data in the current database is removed/lost, and this is replaced with all the data from the imported database. Care therefore needs to be taken when importing, and you should have checks in place to ensure that your current database data is exported/backed-up, before importing. The default database is always read/write, so you can import a read only database from assets, and edit the imported database with impunity, this will not affect the original database file in the assets (which is read only). As I said, a little confusing, but I will hopefully demonstrate below the methods I use to make this all work for me.
  
  

• The SimpleSqlite extension also allows for a different database to be set as the default, by providing an absolute path to the corresponding block. This is fine for the current session, but as I have previously indicated, an app restart will begin with the default database SimpleSQLite.db again, so blocks will be needed at initialisation to overcome this.
  
  

• It is also possible to ATTACH another or several database/s to the current database, SQLite will allow you to work with several databases at once, they can either be treated separately, or integrated to work together.
  
  

• Please ensure that you have "ShowListsAsJson" checked in Screen1.Properties

DATABASE FILE MANAGEMENT OPERATIONS

The Default Database

Let us find out where our default database is located, and what its name is:

See that I have to use OPEN DATABASE in order to run the SELECT command

It may be useful to save this path to a variable/tinydb, so that you can return to the default database SimpleSQLite.db without having to restart the app.

(Note you can also use the DbName property block, or the getDatabasePath method block to return the database name)

Set a Different Database as Default

I have another database called "peopleCodes.db" I will set this as default on initialisation:

Notice that for above and below, the index/id for both is "0", and the "name" is "main" (default)

Start with the Default Database, and IMPORT another database from the assets

In this instance, I have a database file in the assets called empty.db

See that I have to CLOSE DATABASE in order to carry out the IMPORT function

The extension returns "true" for a successful import

Any data that was in the default database is gone! I can now work with the data imported, but this is now in the default database....

IMPORT a Database from Assets, then EXPORT it to a File

I have peopleCodes.db in the assets, this is a quick way of moving it to my ASD, so that it is writable.

I must remember that if I work on the default database now, with the peopleCodes data, I will need to EXPORT it again
in order to update the peopleCodes.db. Alternatively, set the DBName to peopleCodes.db and OPEN DATABASE before working on the data

ATTACH a Database to the Default Database

Here I will ATTACH peopleCodes.db in the ASD to SimpleSqlite.db 

Label 1 now shows the "main" database and "peopleCodes" database

Notice how I had to use single quotes around the file path

Non-SELECT functions return "completed"

The Attachment does not survive an app restart

To DETACH a Database

EXPORT a Database

This exports the current database to a database file in the ASD with the set name

EXPORT a Database to Shared Directories

This exports the current database to a database file in the /Download directory with the set name. (You can also use the /Documents directory in the same way)

Set the DBName Property to a Database in Shared Directories

This sets the sqlite database to the db file in the /Download directory. You must use an absolute path as shown. You can then work directly with this database in that location. Note that a journal file is created in the same directory when you work with the database

DATABASE SQL

Some useful commands you can run

This will show you the program version of sqlite on your device

e.g. 3.22

SELECT sqlite_version()

This will show you the names of the tables in the current database

e.g. ["data", "android_metadata"]
You can ignore the android_metadata table, this is for system use only

SELECT name FROM sqlite_master WHERE type='table'

This will show you the structure or schema of the tables in your database

e.g. [[0, id, INTEGER, 1, null, 1], [1, full_name, TEXT, 0, null, 0], [2, code, TEXT, 0, null, 0]]
As you can see there are three columns...each column entry displays: 

• the index of the column (indexing starts at 0 with sqlite)

• the name of the column

• the data type

• whether the cell entry can be empty or not (null=0/not null=1)

• whether there is a default value

• whether the column is the primary key or not (yes = 1, no = 0)

You use the above settings when creating a table and its columns

SELECT * FROM pragma_table_info('table_name')

Just return the column names in a table

e.g. [id, full_name, code]

SELECT name FROM pragma_table_info('table_name')

CRUD

Create a Table in a Database

Let us say we want a table to record some simple contact info. We will need an id, a name, and a location. Make sure you OPEN the database. We will run the following all as one command:

CREATE TABLE IF NOT EXISTS "contacts" ("id" INTEGER NOT NULL, "name" TEXT, "location" TEXT, PRIMARY KEY("id"))

You can usually get away with not using all the double quotes

Insert a record to our new table

INSERT INTO contacts(name,location) VALUES('Bob','London')

Notice I did not have to provide an entry for the id, this is automatically handled for me, because of the settings I provided.

Also notice the need for single quotes around each entry in VALUES

Update a record in our table

UPDATE contacts SET location = 'Glasgow' WHERE id = 1

This will replace London with Glasgow in the record where the id = 1 (Bob's record)

Delete a record in our table

DELETE FROM contacts WHERE id = 1

The record with id = 1 will be removed

Query Data in our database

SELECT * FROM data WHERE name LIKE 'B%' LIMIT 10

As an example, this query will return up to 10 records from the "data" table, where the name starts with "B"

Once you become familiar with the query language, you will be doing it just for fun ;)

In practice I have found a quirk with the return of data from a database using SELECT. This also exists in the original extension by Tom Bennedum.

If you run a SELECT query and only seek to return values from a single column in a table, all is well, and a working AI2 list is returned.

e.g. SELECT animals FROM data ... 

["dog", "cat", "hen"]

However, if you run a SELECT query  and seek to return two or more columns (or all columns[*], or a more complex SELECT] then the extension appears to return a valid list of lists. This is true for the outer list, but the inner lists are returned as some kind of JSON/List object that AI2 list blocks do not understand.

e.g. SELECT * FROM data ... 

[[dog,cat,hen],[lion,tiger,puma],[sheep,horse,cow]]

Use the ListFixer block to return a working AI2 list of lists. 

This will then return data in the correct format:

e.g. [["dog","cat,"hen"],["lion","tiger","puma"],["sheep","horse","cow"]]

COLUMN HEADERS

With regard to column headers, the output is pretty ugly, because it returns the headers for each item in the SELECT output (rather than as another single list item). You need to set the column Headers property before running the SQL:

and without

Hence it is probably better to just fetch the column names....

and then insert this list to any SELECT output list, e.g.

MILLIS

Another quirk with SimpleSqlite that has popped up is how sqlite seems to handle millis when returning the data to AI2. If you use INTEGER as the column field, then sqlite thinks you are using a date/time value, and converts it to a different (sometimes negative) value. The way to overcome this is to store millis as TEXT, then all is well. AI2 is quite happy with handling numbers as strings.