duB2qLbc

1sqlite>         DROP TABLE log;
2sqlite>         DROP TABLE packages;
3sqlite>         DROP TABLE servers;
4sqlite>         CREATE TABLE IF NOT EXISTS packages (
5   ...>             id INTEGER PRIMARY KEY AUTOINCREMENT,
6   ...>             name TEXT NOT NULL UNIQUE
7   ...>         );
8sqlite>         CREATE TABLE IF NOT EXISTS servers (
9   ...>             id INTEGER PRIMARY KEY AUTOINCREMENT,
10   ...>             name TEXT NOT NULL UNIQUE
11   ...>         );
12sqlite>         CREATE TABLE IF NOT EXISTS log (
13   ...>             s_id INT8 NOT NULL,
14   ...>             p_id INT8 NOT NULL,
15   ...>             date INT8 NOT NULL,
16   ...>             PRIMARY KEY (s_id, p_id),
17   ...>             FOREIGN KEY (s_id) REFERENCES servers (id) ON DELETE CASCADE ON UPDATE CASCADE,
18   ...>             FOREIGN KEY (p_id) REFERENCES packages (id) ON DELETE CASCADE ON UPDATE CASCADE
19   ...>         );
20sqlite>         
21sqlite> 
22sqlite>             INSERT OR REPLACE INTO servers (id, name) VALUES (
23   ...>                 (SELECT id FROM servers WHERE name="etl2.gce-be"),
24   ...>                 "etl2.gce-be"
25   ...>             );
26sqlite>         
27sqlite> 
28sqlite>             INSERT OR REPLACE INTO packages (id, name) VALUES (
29   ...>                 (SELECT id FROM packages WHERE name="broken_shit"),
30   ...>                 "broken_shit"
31   ...>             );
32sqlite> 
33sqlite>             INSERT OR REPLACE INTO log (s_id, p_id, date) VALUES (
34   ...>                 (SELECT id FROM servers WHERE name="etl2.gce-be"),
35   ...>                 (SELECT id FROM packages WHERE name="broken_shit"),
36   ...>                 "1515608769"
37   ...>             );
38sqlite> 
39sqlite>             INSERT OR REPLACE INTO packages (id, name) VALUES (
40   ...>                 (SELECT id FROM packages WHERE name="another_one"),
41   ...>                 "another_one"
42   ...>             );
43sqlite> 
44sqlite>             INSERT OR REPLACE INTO log (s_id, p_id, date) VALUES (
45   ...>                 (SELECT id FROM servers WHERE name="etl2.gce-be"),
46   ...>                 (SELECT id FROM packages WHERE name="another_one"),
47   ...>                 "1515608769"
48   ...>             );
49sqlite> 
50sqlite>         SELECT log.date, servers.name, packages.name FROM log INNER JOIN servers
51   ...>             ON servers.id  = log.s_id INNER JOIN packages
52   ...>             ON packages.id = log.p_id
53   ...>         WHERE date>=1515601569;
541515608769|etl2.gce-be|broken_shit
551515608769|etl2.gce-be|another_one
56sqlite> SELECT * FROM log;
571|1|1515608769
581|2|1515608769
59sqlite> SELECT * FROM servers;
601|etl2.gce-be
61sqlite> SELECT * FROM packages;
621|broken_shit
632|another_one
64sqlite>