From 6981db24390f8899ba6ec0c4eb5fe67f83c7099b Mon Sep 17 00:00:00 2001
From: William Carroll <wpcarro@gmail.com>
Date: Sun, 26 Jul 2020 12:21:12 +0100
Subject: Create //playbooks/sqlite3.md

Write a playbook for using SQLite to capture some trivia that I often forget in
between my ~infrequent uses of SQLite.
---
 playbooks/sqlite3.md | 115 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 115 insertions(+)
 create mode 100644 playbooks/sqlite3.md

(limited to 'playbooks/sqlite3.md')

diff --git a/playbooks/sqlite3.md b/playbooks/sqlite3.md
new file mode 100644
index 000000000000..aec87f0b59ee
--- /dev/null
+++ b/playbooks/sqlite3.md
@@ -0,0 +1,115 @@
+# SQLite3
+
+Creating a reference for SQLite that I can access when I'm offline
+(e.g. traveling in an airplane).
+
+## Benefits
+
+I enjoy using SQLite because it's lightweight and simple. Instead of networking
+microservices, I can oftentimes just create a simple `db.sqlite3` file and get
+significant mileage without much tooling overhead.
+
+## Limitations
+
+SQLite has some limitations; here are some of the limitations that I have encountered.
+
+- SQLite **disables** support for `FOREIGN KEY` by default. Enable it with:
+
+```
+sqlite> PRAGMA foreign_keys = ON;
+```
+
+- SQLite has no `BOOLEAN` type; it uses 0 and 1 instead.
+
+```
+sqlite> SELECT TRUE;
+TRUE
+----------
+1
+sqlite> SELECT FALSE;
+FALSE
+----------
+0
+```
+
+- SQLite has no `DATETIME` type; it uses `TEXT` instead.
+
+```
+sqlite> SELECT datetime('now');
+datetime('now')
+-------------------
+2020-07-26 09:52:32
+```
+
+## Reference
+
+The following should serve as a useful reference for working with SQLite.
+
+### Schema
+
+```sql
+CREATE TABLE IF NOT EXISTS Movies (
+  title TEXT NOT NULL,
+  year INTEGER,
+  PRIMARY KEY (title)
+);
+
+ALTER TABLE Movies ADD COLUMN rating DEFAULT 0.0;
+
+DROP TABLE Movies;
+```
+
+### Queries
+
+The following queries should come in handy as a reference:
+
+```
+sqlite> -- I'm using an intentionally incorrect date here for the subsequent UPDATE.
+sqlite> INSERT INTO Movies (title, year) VALUES ('Toy Story 3', 2100);
+sqlite> SELECT * FROM Movies WHERE year IS NULL;
+sqlite> UPDATE Movies SET year = 2010 WHERE title = 'Toy Story 3';
+sqlite> -- % is like .* in a regex
+sqlite> DELETE FROM Movies WHERE title LIKE 'Toy Story%';
+```
+
+## Command Line
+
+- Create a `~/.sqliterc` file with the following contents:
+
+```
+.mode column
+.headers on
+```
+
+- To start an interactive session:
+
+```shell
+$ sqlite3 db.sqlite3
+```
+
+- To create a SQLite database from a `.sql` file:
+
+```shell
+$ sqlite3 db.sqlite3 <db.sql
+```
+
+- To reload changes to a `.sql` file while in an interactive session:
+
+```
+sqlite> .read db.sql
+```
+
+## Miscellaneous
+
+- For a web-browser-based SQLite viewer, run the following:
+
+```shell
+$ sqlite_web db.sqlite3
+```
+
+- To import a CSV:
+
+```
+sqlite> .mode csv <table-name>
+sqlite> .import path/to/file.csv <table-name>
+```
-- 
cgit 1.4.1