PHP with SQLite using PDO

Had to throw together a fast and dirty database-backed system. Super easy with SQLite, which is built into most PHP builds. Not sure? Run this and look for --with-sqlite3 in the Configure Command section:

<?php
phpinfo();

Creating a database and setting up tables from the command line is easy:

$ sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
sqlite> .open /path/to/database.sqlite
sqlite> CREATE TABLE tag (uuid char(36) primary key, tag varchar(255));
sqlite> .tables
tag
sqlite> INSERT INTO tag (uuid, tag) VALUES('371A4A0D-C21E-42AD-854A-B48181D061AD', 'Litigation');
sqlite> SELECT * FROM tag;
371A4A0D-C21E-42AD-854A-B48181D061AD|Litigation
sqlite> create table article_tag( uuid text primary key, article_uuid text, tag_uuid text);
sqlite> create index atart on article_tag(article_uuid);
sqlite> create index attag on article_tag(tag_uuid);
sqlite> .quit

I realized after the fact that SQLite maps “regular” SQL data types (CHAR(36) etc) to its internal data types (TEXT); also, note you can specify the database you’re opening on the command line, instead of using .open:

$ sqlite3 ./database.sqlite

Accessing the database via PDO in PHP is simple; here’s a basic script that pulls from the database and lists the contents of a table:

<?php
require("UUID.php");

$db = new PDO('sqlite://path/to/database.sqlite');
$stmt = $db->prepare('SELECT uuid,tag FROM tag'); # WHERE meal LIKE ?');
$stmt->execute(); # (array($_POST['meal']));
$rows = $stmt->fetchAll();
if (count($rows) == 0) {
        print "No tags.\n";
} else {
        print "UUID                            \tTag\n";
        foreach ($rows as $row) {
                print $row[0] . "\t" . $row[1] . PHP_EOL;;
        }
}
?>

Alternately, to use the column names in the foreach loop:

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
...
print $row['uuid'] . "\t" . $row['tag'] . PHP_EOL;

To insert data into the database, use the PDO transaction support; reads won’t be impacted, but other transactions will block until a current transaction is committed or rolled back:

$sql = "INSERT INTO tag (uuid, tag) VALUES(:uuid, :tag)";
$stmt = $db->prepare($sql);
$ins_uuid = "5ab76a57-5e91-48a8-9392-0d32fb417c34";
$ins_tag = "New tag value";
$stmt->bindParam(":uuid", $ins_uuid, PDO::PARAM_STR); #
$stmt->bindParam(":tag", $ins_tag, PDO::PARAM_STR);
try {
        $db->beginTransaction();
        $stmt->execute();
        $db->commit();
} catch(\PDOException $e) {
        die($e->getMessage());
}

Easy. A few things (it’s been a while since I did much of anything with PHP, and PDO is new to me, so maybe these are old news to the rest of the world?):

• If you want to use, e.g., a string literal, use ->bindValue() instead of ->bindParam() (which passes the variable by reference).
• PDO::PARAM constants defined.

SQLite lacks a date type but supports storing dates in a couple of formats, including an integer count of seconds since epoch. PHP makes these easy to work with, too:

$date = '11/28/21';
$dt = new DateTime($date);
$epoch = $dt->getTimestamp();
$display = date("Y-m-d", $epoch);
print "$date = $epoch = $display" . PHP_EOL;

Importing tab-separated data into SQLite:

# head -n 3 /nas/www/dogbeach/Book1.txt
uuid    matter_type_uuid    name    sort_value    active
a8e314e4-ee2b-4108-affa-1e65d68c4e05    c787e2fd-7a9e-4fae-afdd-33a6fccd3490    Potential New Client    0    1
f0f2ef0b-3ffb-42ae-b2a2-3eee8f5c97f5    52ee97ec-f80d-4d48-8fe1-e4b03be59162    Open    100    1

sqlite> .mode tabs
sqlite> .import /nas/www/dogbeach/Book1.txt matter_status
sqlite> select * from matter_status
uuid    matter_type_uuid    name    sort_value    active
a8e314e4-ee2b-4108-affa-1e65d68c4e05    c787e2fd-7a9e-4fae-afdd-33a6fccd3490    Potential New Client    0    1
...

Same deal with comma separated values:

sqlite> .mode csv

Output to a CSV file:

sqlite> .mode csv
sqlite> .output /Users/flying-geek/Documents/Aircraft.csv
sqlite> SELECT ...;
sqlite> .output stdout