forked from Tech4covid19/trackovid19-backend
-
Notifications
You must be signed in to change notification settings - Fork 1
/
schema.sql
133 lines (120 loc) · 4.97 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
--Current DB Tables:
-- users : list of all users and their basic info
-- history : all historic of registered user health status
-- network : all user networks with other users
--Current DB Views:
-- latest_status : easily access any user current status
-- network_status : easily access current status of users in your network
--Useful Commands:
--\list : list all databases
--\d : list all tables & views
--\dt : list all tables
--\dv : list all views
--\d <table> : get <table> info
--\d <view> : get <view> info
--Possible Status For A Given User:
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statustype') THEN
CREATE TYPE statustype as ENUM ('infected', 'recovered', 'normal', 'quarentine');
ALTER TYPE statustype ADD VALUE 'self quarentine';
END IF;
END$$;
--DROP TYPE IF EXISTS statustype CASCADE;
--Table Users With All User Info:
CREATE TABLE IF NOT EXISTS users (
id varchar(16) primary key,
hash bytea,
age integer,
city varchar(20),
ip varchar(20),
info varchar(500),
timestamp timestamp without time zone default (now() at time zone 'utc'),
unix_ts int default extract(epoch from now())
);
CREATE INDEX IF NOT EXISTS users_id0 ON users (id);
--DROP TABLE IF EXISTS users CASCADE;
--Table History With The Whole User Health History:
CREATE TABLE IF NOT EXISTS history (
id serial,
user_id varchar(16) references users(id),
status statustype,
symptoms bool,
timestamp timestamp without time zone default (now() at time zone 'utc'),
unix_ts int default extract(epoch from now())
);
CREATE INDEX IF NOT EXISTS history_id0 ON history (user_id, timestamp);
--DROP TABLE IF EXISTS history CASCADE;
--Table Network With The Whole Users Network/Connections:
CREATE TABLE IF NOT EXISTS network (
id serial,
user_id varchar(16) references users(id),
met_with varchar(16) references users(id),
timestamp timestamp without time zone default (now() at time zone 'utc'),
unix_ts int default extract(epoch from now())
);
CREATE INDEX IF NOT EXISTS network_id0 ON network (user_id);
--DROP TABLE IF EXISTS network CASCADE;
--View To Easily Get Latest User Health Status For Each User:
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'latest_status') THEN
CREATE VIEW latest_status AS
SELECT a.user_id, a.status, a.symptoms, a.timestamp
FROM history a WHERE NOT EXISTS (
SELECT 1 FROM history b
WHERE a.user_id = b.user_id
AND a.timestamp < b.timestamp);
END IF;
END$$;
--DROP VIEW latest_status;
--View To Easily Get Latest Network Health Status For Each User:
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'network_status') THEN
CREATE VIEW network_status AS
SELECT b.user_id, b.met_with, c.status, c.symptoms, c.timestamp
FROM users a
INNER JOIN network b ON a.id = b.user_id
INNER JOIN latest_status c ON b.met_with = c.user_id;
END IF;
END$$;
--DROP VIEW network_status;
--Dummy Data For Test Proposes
DELETE FROM users where id in ('1','2','3');
DELETE FROM history where user_id in ('1','2','3');
DELETE FROM network where user_id in ('1','2','3');
INSERT INTO users(id, hash, age, city, ip, info) values('1', '1', 1, 'Lisboa', null, null);
INSERT INTO users(id, hash, age, city, ip, info) values('2', '2', 1, 'Lisboa', null, null);
INSERT INTO users(id, hash, age, city, ip, info) values('3', '3', 1, 'Lisboa', null, null);
INSERT INTO history(user_id, status, symptoms) values('1', 'normal', False);
INSERT INTO history(user_id, status, symptoms) values('1', 'infected', True);
INSERT INTO history(user_id, status, symptoms) values('1', 'recovered', False);
INSERT INTO history(user_id, status, symptoms) values('2', 'normal', False);
INSERT INTO history(user_id, status, symptoms) values('2', 'infected', True);
INSERT INTO history(user_id, status, symptoms) values('3', 'normal', False);
INSERT INTO network(user_id, met_with) values('1', '2');
INSERT INTO network(user_id, met_with) values('1', '3');
INSERT INTO network(user_id, met_with) values('2', '1');
INSERT INTO network(user_id, met_with) values('2', '3');
INSERT INTO network(user_id, met_with) values('3', '1');
INSERT INTO network(user_id, met_with) values('3', '2');
--Get Current Status Count:
SELECT status, count(1) FROM latest_status GROUP BY status;
--Get Current Network Status Count For User '1':
SELECT status, count(1) FROM network_status WHERE user_id='1' GROUP BY status;
--Dummy Table With Column Type Examples
CREATE TABLE IF NOT EXISTS example (
user_id varchar(16) references users(id),
inteiro integer,
numerico numeric,
bytes bytea not null,
status statustype,
texto varchar(16),
list_of_3_ints integer[3],
list_of_ints integer[],
authorized bool default False,
timestamp timestamp without time zone default (now() at time zone 'utc'),
unix_timestamp int default extract(epoch from now())
);
--DROP TABLE IF EXISTS examples CASCADE;