database/db.sql at master · LifeTrackr/database · GitHub

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
CREATE EXTENSION citext;

CREATEDOMAINemailAS citext CHECK (value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$');

CREATETABLEIF NOT EXISTS "User" (
 user_id serialCONSTRAINT"User_pk"PRIMARY KEY,
 username email NOT NULL UNIQUE,
 first_name varchar(15) NOT NULL,
 last_name varchar(25),
 hashed_password varchar(64) NOT NULL,
 is_active boolean DEFAULT TRUE NOT NULL
);

CREATETABLEIF NOT EXISTS "Event_Logs" (
 id serialCONSTRAINT"Event_Logs_pk"PRIMARY KEY,
 event_id serial,
 user_id serialCONSTRAINT"Event_Logs_fk1"REFERENCES"User",
 completed_at timestamp(1) WITH time zoneNOT NULL
);

CREATETABLEIF NOT EXISTS "Companion_Types" (
 id serialPRIMARY KEY,
 type_name textNOT NULL
);

INSERT INTO"Companion_Types" (type_name)
VALUES ('dog'), ('cat'), ('reptile'), ('plant'), ('bird');

CREATETABLEIF NOT EXISTS "Companion" (
 companion serialCONSTRAINT"Companion_pk"PRIMARY KEY,
 name varchar(10) NOT NULL,
 companion_type integerREFERENCES"Companion_Types"(id),
 notes varchar(255) NOT NULL,
 image varchar(255) UNIQUE,
 user_id serialCONSTRAINT"Companion_fk0"REFERENCES"User"ON DELETE CASCADE
);

CREATETABLEIF NOT EXISTS "Event" (
 event_id serialCONSTRAINT"Event_pk"PRIMARY KEY,
 name varchar(10) NOT NULL,
 qr_code integerNOT NULL,
 notes varchar(255) NOT NULL,
 priority varchar(1) NOT NULL,
 frequency interval(1) NOT NULL,
 last_trigger timestamp(1) WITH time zone,
 next_trigger timestamp(1) WITH time zone,
 action varchar(10) NOT NULL,
 companion_id serialCONSTRAINT"Event_fk1"REFERENCES"Companion"ON DELETE CASCADE,
 user_id serialCONSTRAINT"Event_fk0"REFERENCES"User"ON DELETE CASCADE,
UPDATEboolean DEFAULT FALSE NOT NULL
);

CREATETABLEIF NOT EXISTS "QR_Range" (
 id serialPRIMARY KEY,
 assigned_range integerNOT NULL,
 user_id serialCONSTRAINT"QR_Range_fk0"REFERENCES"User"ON DELETE CASCADE
);

CREATETABLEIF NOT EXISTS "QR_Codes" (
 id serialPRIMARY KEY,
 value integerNOT NULL,
 qr_id serialCONSTRAINT"QR_Codes_fk0"REFERENCES"QR_Range"ON DELETE CASCADE,
 event_id serialCONSTRAINT"QR_Codes_fk1"REFERENCES"Event"ON DELETE CASCADE
);

CREATEFUNCTIONupdate_next_trigger ()
 RETURNS TRIGGER
 LANGUAGE plpgsql
AS $$
BEGIN
 IF NEW.update!= FALSE THEN
UPDATE
"Event"
SET
 next_trigger = frequency + now(),
 last_trigger = now(),
UPDATE
= FALSE
WHERE
UPDATE
= TRUE;
 END IF;
 RETURN NEW;
END;
$$;

CREATEFUNCTIONevent_completed_log ()
 RETURNS TRIGGER
 LANGUAGE plpgsql
AS $$
BEGIN
 IF NEW.update!= FALSE THEN
INSERT INTO"Event_Logs" ("event_id", "user_id", "completed_at")
VALUES (NEW.event_id, NEW.user_id, now());
 END IF;
 RETURN NEW;
END;
$$;

CREATETRIGGERupdate_triggers
 AFTER UPDATEON"Event" FOR EACH ROW
 EXECUTE PROCEDURE update_next_trigger ();

CREATETRIGGERcreate_trigger
 AFTER INSERT ON"Event" FOR EACH ROW
 EXECUTE PROCEDURE update_next_trigger ();

CREATETRIGGERevent_completed_log
 BEFORE UPDATEON"Event" FOR EACH ROW
 EXECUTE PROCEDURE event_completed_log ();