METRIC RAT AI2 - HOWTO: php SQLite3 with AI2

HOWTO: php SQLite3 with AI2

INTRO

Someone asked on the forum about the various options available for online databases to use with AI2. We ran through the usual suspects, and then I piped up with idea of using sqlite. For most of us, sqlite is a considered as a local database on an android device, but it can, with the correct configuration and php, be used on a standard linux server as an online database with AI2. Having proposed sqlite as a possible solution, I then realised I had made a rod for my own back, and needed to provide some proof that this would work! Here then, after some serious toiling at the browser and command line, is a demonstration of how to use sqlite on a remote server to create, read, update and delete data from AI2. I have also included methods for the creation of new databases, and administration of said databases. Most of the examples will provide developers with the basis for creating more complex methods using php and sqlite. I have purposely created a set of php files, as opposed to a single long and complicated php file, for (hopefully) ease of use and understanding. For the purposes of the demo I have made some compromises in the creation/addition of data to sqlite tables. All values will be sent as TEXT, but is it still possible to sort and organise text "numbers" lexicographically (aphabetically). This is taken care of in the blocks (by the addition of single quotes to each value). The php files only test for the provision of a database name in the url parameters, additional tests can be added, if desired.

There is a list of useful resources at the bottom of this page.

Please remember that this is just a demo to show the interaction between AI2 and an online sqlite3 database, there may be some rough edges here and there, and not all the user error checking has been built in.

Oh, no extensions used or harmed in the production of this demo!

SERVER

The online server used for this demo is a VPS running Ubuntu server 18.04, with several virtual hosts, using Apache2 version 2.4.29. By default the server is running sqlite3. I obviously have full control over this server, therefore can access all the necessary configuration files, you may be using a server on shared hosting, which may restrict your ability to set things up in the same way, but I will try to provide methods for both.

For security purposes (there is little or no security on sqlite3, given its principal intended use is not as a public facing database) we want to ensure that all files are owned by the Apache2 web user: www-data, that the database files are set to permission 0600 and that we have http rules in place (either placed in the config files or using an .htaccess file) to prevent download of the databases.

We login by FTP to our server, and cd to the root directory for the virtual host we are going to use: example.co.uk. On my system this is

/var/www/example.co.uk/public_html

depending on your setup your root directory may be /var/www/html

We create a new directory for our php and database files called sqlite

/var/www/example.co.uk/public_html/sqlite

Go into the folder sqlite and then upload all the php files (see FILES below)

Now we need to ssh into the server and cd to the root directory, then run the following commands in order to set the group and ownership of all the files to www-data, and the folder and file permissions

cd /var/www/example.co.uk/public_html

sudo chown www-data:www-data -R sqlite

sudo chmod 0775 sqlite

All new database files ( .db / .sql / .sqlite3 ) created by the demo files will be set with 0600 permissions. These settings may mean you cannot access all files via ftp in the future, you will need to change them back to 0775. (The alternative is to, on the sever, give the user www-data a password and then use www-data as an ftp account.)

Now open the conf file for the virtual host

sudo nano /etc/apache2/sites-enabled/example.co.uk.conf

and paste the following AFTER the </VIRTUAL HOST>:

Options Indexes FollowSymLinks

AllowOverride All

Require all granted

Require all denied

</FilesMatch>

</Directory>

This config prevents the download of database files with the suffix .db / .sql / sqlite3. You can add other file extensions if you wish. There may be a different settings requirement for apache versions before 2.4. If you do not have access to the config files for your host, then you will need to use an .htaccess file in your sqlite folder.

Your sqlite folder should now look something like this in your ftp program:

the -rwxrwxr-x shows the 0775 permission. The user www-data executes and runs the php files :)

That should be all that is needed on the server.

SETUP and RUN AI2

For the purposes of the demo, the AI2 app uses a simple menu system: a spinner provides a list of actions, and then opens the required "page" for that action and the data entry required. Everything happens on one screen, using vertical arrangements and their visibility. Response content is dependent on the action, either a simple message or data is returned. Where data is returned, it should be in the form of a json array (text).

The demo provides for:

Creation of new databases and tables
Creation, Reading (querying), Updating and Deleting of data in the database tables
Deletion, or Dropping, of database tables
Viewing of the Metadata for the databases created (list of databases, list of tables in a database, list of columns, with their affinity - e.g. TEXT/INTEGER, in a table in a database)
A separate section for the creation and use of a lists database for AI2 Lists
( I included some sample lists for demo purposes )

Unless you can remember everything, the workflow seems to be to:

run the Metadata to get the database name, tables names and column names
possibly run a query on that information to see what data exists
then perform your actual action.

You can query the lists database in the same way as any other database.

Data entered in any action view is retained until the app is reset or restarted. Example data is shown for each action where possible.

The serverURL block is set to:

https://example.co.uk/sqlite/

I have added a second screen to run a "lazy" reset procedure....

If you want to add images, then you can either:

Upload the image to a separate directory, and set the url to that image in the sqlite database
or
Convert the image to a base64 string in the AI2 app, and save that string to the database. (Sqlite "cells" can store 10^9 bytes). You will need to convert the base64 string back to a binary file when calling it back into AI2 for viewing

BLOCKS

all 534 of them!

PHP FILE CONSTRUCTION EXAMPLES

Create a new Database

<?php

// Example URL

// https://example.co.uk/sqlite/create_db.php?db=test1.db

// Test for db value

if (@$_REQUEST['db']) {

// Set db value to variable

$db = $_REQUEST['db'];

} else {

die('No db selected');

}

// Create or open (if exists) the database

$database = new SQLite3($db);

// Close the db connection

$database = null;

// Set permissions (read/write only for Apache user)

chmod($db,0600);

// Output

echo 'The sqlite database: '.$db.' has been created';

returns a message

Query Data

<?php

// Example url

// https://example.co.uk/sqlite/query_data.php?db=test1.db&qry=SELECT%20*%20FROM%20table1%20WHERE%20id=2

//Get database name and any other variables from url

if (@$_REQUEST['db']) {

//set db name to variable

$db = $_REQUEST['db'];

//add other variables here

$query = $_REQUEST['qry'];

} else {

//output if fails

die('No db selected');

}

// Connect to database

$database = new SQLite3($db);

// Make query

$result = $database->query("$query");

// Output

$output = array();

while ($row = $result->fetchArray(SQLITE3_NUM)) {

array_push($output,$row);

}

echo json_encode($output);

returns a json array