-
Notifications
You must be signed in to change notification settings - Fork 0
/
initialize-database.sql
113 lines (84 loc) · 3.96 KB
/
initialize-database.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
-- Doubloons initial schema script
-- Create tables
CREATE TABLE doubloons.categories (
category_id integer NOT NULL,
category_name text NOT NULL,
category_description text NOT NULL
);
CREATE TABLE doubloons.comments (
month_and_year character varying(7) NOT NULL,
given_to text NOT NULL,
given_by text NOT NULL,
comment text NOT NULL
);
CREATE TABLE doubloons.doubloons (
doubloon_id bigint NOT NULL,
category_id integer NOT NULL,
given_to text NOT NULL,
given_by text NOT NULL,
amount integer NOT NULL,
month_and_year character varying(7) NOT NULL
);
ALTER TABLE doubloons.doubloons ALTER COLUMN doubloon_id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME doubloons.doubloons_doubloon_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
CREATE TABLE doubloons.team_leaders (
leader_id text NOT NULL,
team_id text NOT NULL
);
CREATE TABLE doubloons.teams (
team_id text NOT NULL,
team_name text NOT NULL,
team_description text
);
CREATE TABLE doubloons.users (
email text NOT NULL,
team_id text,
first_name text NOT NULL,
last_name text NOT NULL,
password text NOT NULL,
avatar text
);
-- Initialize categories
INSERT INTO doubloons.categories (category_id, category_name, category_description) VALUES (5, 'craftsmanship', 'to-update');
INSERT INTO doubloons.categories (category_id, category_name, category_description) VALUES (4, 'learn fast', 'to-update');
INSERT INTO doubloons.categories (category_id, category_name, category_description) VALUES (3, 'autonomy', 'to-update');
INSERT INTO doubloons.categories (category_id, category_name, category_description) VALUES (2, 'trust', 'to-update');
INSERT INTO doubloons.categories (category_id, category_name, category_description) VALUES (1, 'collaboration', 'to-update');
-- Make the default user the owner of the tables
ALTER TABLE doubloons.categories OWNER TO doubloons;
ALTER TABLE doubloons.comments OWNER TO doubloons;
ALTER TABLE doubloons.doubloons OWNER TO doubloons;
ALTER TABLE doubloons.team_leaders OWNER TO doubloons;
ALTER TABLE doubloons.teams OWNER TO doubloons;
ALTER TABLE doubloons.users OWNER TO doubloons;
-- Add constraints
ALTER TABLE ONLY doubloons.doubloons ADD CONSTRAINT doubloons_pkey PRIMARY KEY (doubloon_id);
ALTER TABLE ONLY doubloons.users ADD CONSTRAINT users_pkey PRIMARY KEY (email);
ALTER TABLE ONLY doubloons.categories ADD CONSTRAINT categories_pkey PRIMARY KEY (category_id);
ALTER TABLE ONLY doubloons.comments ADD CONSTRAINT comments_pkey PRIMARY KEY (month_and_year, given_to, given_by);
ALTER TABLE ONLY doubloons.teams
ADD CONSTRAINT teams_pkey PRIMARY KEY (team_id);
ALTER TABLE ONLY doubloons.comments
ADD CONSTRAINT comments_given_by_fkey FOREIGN KEY (given_by) REFERENCES doubloons.users(email);
ALTER TABLE ONLY doubloons.comments
ADD CONSTRAINT comments_given_to_fkey FOREIGN KEY (given_to) REFERENCES doubloons.users(email);
ALTER TABLE ONLY doubloons.doubloons
ADD CONSTRAINT doubloons_category_fkey FOREIGN KEY (category_id) REFERENCES doubloons.categories(category_id) NOT VALID;
ALTER TABLE ONLY doubloons.doubloons
ADD CONSTRAINT doubloons_given_by_fkey FOREIGN KEY (given_by) REFERENCES doubloons.users(email) NOT VALID;
ALTER TABLE ONLY doubloons.doubloons
ADD CONSTRAINT doubloons_given_to_fkey FOREIGN KEY (given_to) REFERENCES doubloons.users(email) NOT VALID;
ALTER TABLE ONLY doubloons.team_leaders
ADD CONSTRAINT team_leaders_leader_fkey FOREIGN KEY (leader_id) REFERENCES doubloons.users(email);
ALTER TABLE ONLY doubloons.team_leaders
ADD CONSTRAINT team_leaders_team_fkey FOREIGN KEY (team_id) REFERENCES doubloons.teams(team_id) NOT VALID;
ALTER TABLE ONLY doubloons.users
ADD CONSTRAINT users_team_fkey FOREIGN KEY (team_id) REFERENCES doubloons.teams(team_id) NOT VALID;
-- Add stallions team
INSERT INTO doubloons.teams (team_id, team_name, team_description) VALUES ('1', 'Stallions', 'OSP Now Business Processes');