Installing SQLite3

SQLite3 is a self-contained, easy-to-install, lightweight transactional database. The
website boasts that it is the most deployed database in the world. As we know from
Wikipedia, it is mostly deployed on embedded devices, but the most used database
is still MySQL. None of this really matters to us because both these databases are
very good at what they do, except that SQLite is a better choice if you are looking
for performance on the Pi.

sudo apt-get install sqlite3 php5-sqlite

We will be using phpliteadmin, a single-file administration utility made specifically
for SQLite Versions 2 and 3. Also, create a new subdirectory for phpliteadmin. You
should visit the website to get the latest version and download that file instead.
mkdir /var/www/phpliteadmin/web
cd /var/www/phpliteadmin/web
wget https://phpliteadmin.googlecode.com/files/phpliteAdmin_v1-9-4-1.zip
unzip phpliteAdmin_v1-9-4-1.zip
The easiest way to get phpliteadmin working locally is by running it on its own
dedicated port. Create a new virtual host file with the following contents:

server {
listen 81;
root /var/www/phpliteadmin/web/;
index phpliteadmin.php;
location ~ \.php$ {
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index phpliteadmin.php;
include fastcgi_params;
}
}

Reload nginx and navigate to your Pi site, http://pi:81. The default password is
admin. At this point, you will get an error message saying there are no databases
and the directory is not writeable. SQLite creates flat files that contain all the data,
and you can manage where you would like to keep these files. There is no server
managing these files and any SQLite-compatible client can connect to these files.
You can create a test database using the SQLite3 command line. In the web directory
of phpliteadmin, type in the following:

sqlite3 test.db
BEGIN;
CREATE TABLE temps (theDate DATE, name TEXT);
COMMIT;
.exit

Refresh the web page where you navigated to phpliteadmin. You will see the newly
created test.db file and the basic temps table displayed.