FnFR0n0B

1$ sqlite3 < testdb.sql
2$ ls -Al testdb.db
3-rw-r--r-- 1 jwalton jwalton 0 Mar 25 04:02 testdb.db 
4     
5$ cat testdb.sql
6
7ATTACH DATABASE 'testdb.db' AS 'testdb';
8PRAGMA foreign_keys = ON;
9
10-- #####################################################
11
12CREATE TABLE IF NOT EXISTS name_number
13(
14        nn_id INTEGER PRIMARY KEY AUTOINCREMENT,
15        nn_name VARCHAR,
16        nn_number VARCHAR
17);
18
19CREATE INDEX IF NOT EXISTS number_index ON name_number(nn_number);
20
21-- #####################################################
22
23CREATE TABLE IF NOT EXISTS whitelist
24(
25        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
26        nn_id INT NOT NULL,
27        list_date DATETIME,
28        fk1_id INTEGER,
29        FOREIGN KEY(fk1_id) REFERENCES name_number(nn_id)
30);
31
32-- #####################################################
33
34CREATE TABLE IF NOT EXISTS blacklist
35(
36        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
37        nn_id INT NOT NULL,
38        list_date DATETIME,
39        fk2_id INTEGER,
40        FOREIGN KEY(fk2_id) REFERENCES name_number(nn_id)
41);
42
43-- #####################################################
44
45INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "4105551212");
46INSERT INTO whitelist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));
47
48INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "8005551212");
49INSERT INTO blacklist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));