paulgorman.org/technical

SQLite

SQLite provides self-contained, light-weight, serverless, transactional file-based storage. It’s suitable for low-volume websites and application storage (no more roll-your-own file storage); see Appropriate Uses for SQLLite.

See the documentation and the FAQ.

On Debian, apt-get install sqlite3. Other packages of interest: php-sqlite3 ruby-sqlite3 sqlite3-pcre.

Create a database from a SQL file: sqlite3 test.db < test.sql, where test.sql is something like:

CREATE TABLE notes(
    id INTEGER PRIMARY KEY,
    note TEXT,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE tags(
    tag TEXT,
    note INTEGER,
    FOREIGN KEY(note) REFERENCES notes(id)
);
INSERT INTO notes VALUES(1, 'My first note!', '2015-07-20 16:26:16');
INSERT INTO notes VALUES(2, 'My second note.', '2015-07-21 12:53:19');
INSERT INTO notes VALUES(3, 'Yet another note. :\', '2015-07-23 18:13:42');
INSERT INTO tags VALUES('firsts', 1);
INSERT INTO tags VALUES('excited', 1);
INSERT INTO tags VALUES('remember', 1);
INSERT INTO tags VALUES('excited', 2);
INSERT INTO tags VALUES('bored', 3);

Connect to the database CLI: sqlite3 test.db.

Back up the database: sqlite3 test.db .dump > test.db.backup.

Shrink a database: sqlite3 test.db VACUUM. It creates a temporary file to do the shrink, so it will use more disk space until the VACUUM process completes.

Quick command pipeline use:

$ sqlite3 test.db 'SELECT * FROM notes LIMIT 1;' | awk -F'|' '{printf "%s\n%s\n", $3, $2}'
2015-07-20 16:26:16
My first note!

CLI dot functions

The CLI has a number of useful dot functions. See https://www.sqlite.org/cli.html.

sqlite> .databases
sqlite> .tables
sqlite> .schema
sqlite> .schema _tablename_
sqlite> 
sqlite> 

Dump the database to a text file:

$ echo '.dump' | sqlite3 mydb | gzip -c > mydb.dump.gz

Export a table to CSV:

sqlite> .header on
sqlite> .mode csv
sqlite> .once /tmp/foo.csv
sqlite> SELECT * FROM mytable;
sqlite> .system /tmp/foo.csv

SQL in SQLite

Data types: INTEGER, REAL, TEXT, BLOB, or NULL. Any other times (like DATETIME) are an alias to one of these basic types.

https://www.sqlite.org/datatype3.html

Columns declared as PRIMARY KEY will auto-increment.

SQLite supports C-style multiline comments (/* Comment */) and SQL-style single-line comments (-- A comment.).

SQLite understandsa a variety of date-time formats (stored in TEXT fields), including “YYYY-MM-DD HH:MM:SS”.

See SQL As Understood By SQLite.

Customize the Client

Add options in your ~/.sqliterc file.

.headers on
.mode column
.nullvalue ¤
.prompt "> "
.timer on

Import CSV Data

SQLite has a mode for importing CSV data.

$  sqlite3 airports.db
sqlite> .mode csv
sqlite> .separator ","
sqlite> .import airports.csv airports
sqlite> .schema

SQLite assumes the first line of input contains column headers, which it uses to name table columns for the database schema.

Attach Multiple Databases at the Same Time

$  sqlite3
sqlite> ATTACH 'airports.db' AS airport;
sqlite> ATTACH 'urls.db' AS urls;
sqlite> .databases
sqlite> SELECT COUNT(*) FROM urls.urls;
🐚 $ sqlite3 test-ticket.db
sqlite> .tables
comments  tickets 
sqlite> .schema comments
CREATE TABLE `comments` (
  `id` int(10) NOT NULL PRIMARY KEY,
  `posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `author` varchar(50) DEFAULT NULL,
  `message` longtext,
  `ticket_id` int(10) NOT NULL DEFAULT '0',
  `pin` tinyint(1) NOT NULL DEFAULT '0'
);
sqlite> .schema tickets
CREATE TABLE `tickets` (
  `id` int(10) NOT NULL PRIMARY KEY,
  `status` INTEGER DEFAULT NULL,
  `opened` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `severity` tinyint(4) NOT NULL DEFAULT '3',
  `owner` varchar(50) DEFAULT NULL,
  `headline` varchar(255)  DEFAULT NULL
);
sqlite> CREATE VIRTUAL TABLE fts USING fts5(ticket, headline, message);
sqlite> INSERT INTO fts SELECT tickets.id, tickets.headline, comments.message FROM tickets LEFT JOIN comments ON tickets.id = comments.ticket_id;
sqlite> SELECT * FROM fts WHERE fts MATCH 'spinning' ORDER BY rank;
5656|Upgrade older workstation spinning hard drives to SSD|Maybe more than half the main office staff still have workstations with spinning disks.\r\nSome of the workstations are getting a little old, and SSD's might extend their productive lives significantly.\r\n\r\n(I started looking at this because Sally complained that her computer is slow. I could hear her drive struggling.)
5656|Upgrade older workstation spinning hard drives to SSD|Replaced Nancy's drive.
5656|Upgrade older workstation spinning hard drives to SSD|Replaced Beth's drive with an SSD.
[…]

Populating the FTS5 table as seen above can significantly increase the database size. External or contentless tables are an alternative. See 4.4. External Content and Contentless Tables in https://www.sqlite.org/fts5.html.

Using a view for the search content is often best:

sqlite> CREATE VIEW IF NOT EXISTS ftsview (rowid, ticket, headline, message) AS SELECT comments.rowid, tickets.id, tickets.headline, comments.message FROM tickets LEFT JOIN comments ON tickets.id = comments.ticket_id;
sqlite> CREATE VIRTUAL TABLE fts USING fts5(ticket, headline, message, content='ftsview', content_rowid=rowid);
sqlite> INSERT INTO fts(fts) VALUES('rebuild');

(SQLite returned no results until the rebuild. Why?)

(And, after the rebuild, this does not seem to actually save any disk space in this case. Hmph.)