48 lines
2.1 KiB
Plaintext
48 lines
2.1 KiB
Plaintext
--
|
|
-- Version 1 sqlite3 database schema
|
|
--
|
|
|
|
-- this is the magic that makes it not dog slow
|
|
-- https://www.sqlite.org/wal.html
|
|
PRAGMA journal_mode = WAL;
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
-- info table
|
|
CREATE TABLE info ("key" TEXT PRIMARY KEY NOT NULL, "value" TEXT NOT NULL);
|
|
INSERT INTO info VALUES('version', '1');
|
|
INSERT INTO info VALUES('created', unixepoch("now"));
|
|
INSERT INTO info VALUES('upgraded', unixepoch("now"));
|
|
|
|
-- lists table
|
|
-- dots in a table name is really bad
|
|
-- there must be a way to escape it but let's just map to an internal name
|
|
CREATE TABLE lists ("list" TEXT PRIMARY KEY NOT NULL, "internal" TEXT NOT NULL);
|
|
INSERT INTO lists VALUES('privatehosts.txt', 'privatehoststxt');
|
|
INSERT INTO lists VALUES('userhosts.txt', 'userhoststxt');
|
|
INSERT INTO lists VALUES('hosts.txt', 'hoststxt');
|
|
|
|
-- list tables
|
|
-- hostname to primary entry (preferred, usually non-DSA) and optional alt (DSA) entry numbers
|
|
CREATE TABLE "privatehoststxt" ("hostname" TEXT PRIMARY KEY NOT NULL, "entry" INTEGER NOT NULL, "alt" INTEGER);
|
|
CREATE TABLE "userhoststxt" ("hostname" TEXT PRIMARY KEY NOT NULL, "entry" INTEGER NOT NULL, "alt" INTEGER);
|
|
CREATE TABLE "hoststxt" ("hostname" TEXT PRIMARY KEY NOT NULL, "entry" INTEGER NOT NULL, "alt" INTEGER);
|
|
|
|
-- entry table
|
|
-- id to dest, added time, modified time, notes, source, verified
|
|
-- Don't use AUTOINCREMENT
|
|
-- https://www.sqlite.org/autoinc.html
|
|
-- CREATE TABLE entries ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "dest" BLOB NOT NULL, "a" DATETIME NOT NULL, "m" DATETIME, "notes" TEXT, "s" TEXT, "v" BOOLEAN NOT NULL);
|
|
CREATE TABLE entries ("id" INTEGER PRIMARY KEY NOT NULL, "dest" BLOB NOT NULL, "a" DATETIME NOT NULL, "m" DATETIME, "notes" TEXT, "s" TEXT, "v" BOOLEAN NOT NULL);
|
|
|
|
-- reverse lookup table
|
|
-- first 8 bytes of hash to comma separated list of hostnames
|
|
CREATE TABLE "reverse" ("hash" INTEGER PRIMARY KEY NOT NULL, "hostnames" TEXT NOT NULL);
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
-- https://www.sqlite.org/pragma.html#synchronous
|
|
PRAGMA main.synchronous = NORMAL;
|
|
-- https://www.sqlite.org/pragma.html#pragma_wal_checkpoint
|
|
PRAGMA main.wal_checkpoint(FULL);
|