terminal - sqlite3

INTRO

NOTE: The file access restrictions put in place on the latest android version (10/11/+) may prevent this from working, because the linux sub system is not given any file access permissions, from the testing I have done

I am going to start with some useful links, for those of you not up to speed on how to work with sqlite3:

SQLite Tutorial - An Easy Way to Master SQLite Fast

SQLite Tutorial - Tutlane

Command Line Shell For SQLite

Most android devices, but not all, come with a set of linux command line programs, including sqlite3, a self contained sql relational database program. Several extensions have been written to provide developers with access to this program, in order to utilise their own databases, or for users to create their own. With the help of the excellent terminal extension from Juan Antonio, I have worked up a basic demo app that allows a user to create empty databases, to select databases from local storage, and to send sql commands in order to load data into their databases and to query the database contents. My method also provides full access to the sqlite3 commands one would use in a computer environment, something that not all the extensions provide, although there is usually more than one way of doing things.

In a computer environment, one would normally open up a database in sqlite3, then work within the sqlite3 program to issue commands. it is possible though, to send commands directly from the command line, negating the need to work inside the program, and I use a method that allow for multiline commands.

Here is the basic structure for a command that I send to sqlite3, using the \n to create line returns:

 echo -e "command1\ncommand2\ncommand3;" | sqlite3 /path/to/the/database/file.db

I put this together with some help from StackOverFlow and StackExchange:

sqlite - How to automate a process with the sqlite3.exe command line tool? - Stack Overflow

Scripting SQLite with dot commands - Database Administrators Stack Exchange

There are many "dot" commands available for sqlite3, these must be on their own line and start at the beginning of the line (and should not be finished with a semi-colon). Note, in my investigations I have have found that, for whatever reason, some the listed dot commands do not return an output (e.g. .dbinfo).

Fortunately, a multiline textbox can be used to enter multiple commands, which makes data entry and the visual presentation that much more acceptable, so that a user can work on the app in much the same way as they would on a computer. In the demo, after each command/set of commands is sent I return the database file path, the commands sent, and the database output.

There is no big mystery about how sqlite3 works on Android, it is a program like any other program, and can easily work with databases stored on the device. The demo allows the user to create "empty" databases in local storage (in the application specific directory for Android + 10 devices, and in the root of the sdcard for devices before that), this has been kept simple for the purposes of this demo.

BLOCKS

In Screen1.Initialise, I first test to see if sqlite3 exists on the device, if it does then, I have to get read/write permission for the terminal extension, then select the file path to use based upon the android version of the device. Two buttons provide for the creation of empty databases and for the selection of databases in the local storage (as long as they have a .db extension). The third button submits the query entered in the textbox, and returns the output and other query information to the label.

VIDEO

AIA and FILES

terminal_sqlite3.aia

CREDITS

Juan Antonio for the terminal extension