sqlite3 Notes

2016-11-22 Tue (last modified 2016-12-15 Thu)

Remember:

sqlite> .open test.db
sqlite> CREATE TABLE Datums(
   ...> id INT,
   ...> name TEXT,
   ...> arbitraryNumber INT);
sqlite> .tables
Datums
sqlite> INSERT INTO Datums VALUES (1, 'foo', 17);
sqlite> INSERT INTO Datums VALUES (2, 'bar', 71);
sqlite> INSERT INTO Datums VALUES (3, 'baz', 17);
sqlite> SELECT * FROM Datums;
1|foo|17
2|bar|71
3|baz|17
sqlite> .mode columns
sqlite> .headers on
sqlite> SELECT * FROM Datums;
id          name        arbitraryNumber
----------  ----------  ---------------
1           foo         17
2           bar         71
3           baz         17
sqlite> SELECT *
   ...> FROM Datums
   ...> WHERE arbitraryNumber < 20
   ...> ORDER BY name;
name
----------
baz
foo
sqlite> .quit

Capture default config preferences with ~/.sqliterc:

.mode	columns
.headers on

Scripted table creation & population

A simple make_tables.sql file:

CREATE TABLE Things
(
	id INTEGER PRIMARY KEY,
	born TEXT NOT NULL,
	died TEXT,
	name TEXT NOT NULL UNIQUE,		-- There can be only one
	description TEXT NOT NULL DEFAULT ''
);

CREATE TABLE ThingTags
(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	thingId INT,
	FOREIGN KEY (thingId) REFERENCES Things(id),
	UNIQUE(name, thingId) ON CONFLICT REPLACE  -- No dupe tags on things
);

Add some test data:

INSERT INTO Things (eventStart, eventEnd, name, description)
VALUES ( "1998-08-20", NULL, "Guy", "That guy");

INSERT INTO Things (eventStart, eventEnd, name, description)
VALUES ("1847-07-18", "1847-07-19", "DNE", "");

Notes: