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
|
CREATE TABLE users(
id SERIAL NOT NULL,
email text NOT NULL,
password text NOT NULL,
name text NULL,
address text NULL,
notification_token text NULL,
is_admin boolean default false,
PRIMARY KEY (id)
);
CREATE TYPE moving_activity AS ENUM ('InVehicle', 'Running', 'Walking', 'Still');
CREATE TABLE positions(
id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
location GEOGRAPHY(Point, 4326) NOT NULL,
activity moving_activity NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_users_id
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE,
CONSTRAINT unique_user_position UNIQUE(user_id)
);
CREATE TYPE level_alert AS ENUM ('One', 'Two', 'Three');
CREATE TABLE alerts(
id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
area GEOMETRY(Polygon, 4326),
text1 text NOT NULL,
text2 text NOT NULL,
text3 text NOT NULL,
audio1 bytea NOT NULL,
audio2 bytea NOT NULL,
audio3 bytea NOT NULL,
reached_users INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_users_id
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE
);
CREATE TABLE notifications(
id SERIAL NOT NULL,
alert_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
location GEOGRAPHY(Point, 4326) NOT NULL,
activity moving_activity NOT NULL,
seen BOOLEAN DEFAULT false,
level level_alert NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY(id),
CONSTRAINT fk_alerts_id
FOREIGN KEY(alert_id) REFERENCES alerts(id)
ON DELETE CASCADE,
CONSTRAINT fk_users_id
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE
);
|
