· 8 years ago · Jun 15, 2018, 03:50 AM
1\set ON_ERROR_STOP 1;
2
3DROP SCHEMA IF EXISTS public CASCADE;
4CREATE SCHEMA public;
5
6SET search_path TO public;
7
8--------------------------------------------------------------------------------
9
10CREATE TABLE admins (
11 -- keys
12 admin_id SERIAL NOT NULL PRIMARY KEY,
13
14 -- details
15 disabled_reason VARCHAR,
16 password VARCHAR NOT NULL,
17 username VARCHAR NOT NULL,
18
19 -- dates
20 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
21 date_disabled TIMESTAMP
22);
23
24CREATE UNIQUE INDEX admins_idx1 ON admins (LOWER(username));
25
26INSERT INTO admins (password, username) VALUES ('f40f58f6f3f7ba888ff533e6c5f777c739d6c923', 'Sean');
27INSERT INTO admins (password, username) VALUES ('fe282a083b3882bc823df9c38e82d6f37338fcfd', 'Marc');
28
29--------------------------------------------------------------------------------
30
31CREATE TABLE categories (
32 -- keys
33 category_id SERIAL NOT NULL PRIMARY KEY,
34
35 -- details
36 description VARCHAR,
37 name VARCHAR NOT NULL,
38
39 -- statuses
40 stat_joke BOOLEAN NOT NULL DEFAULT false,
41 stat_werd BOOLEAN NOT NULL DEFAULT false,
42
43 -- dates
44 date_created TIMESTAMP NOT NULL DEFAULT NOW()
45);
46
47--------------------------------------------------------------------------------
48
49CREATE TABLE tags (
50 -- keys
51 tag_id SERIAL NOT NULL PRIMARY KEY,
52
53 -- details
54 name VARCHAR NOT NULL,
55
56 -- dates
57 date_created TIMESTAMP NOT NULL DEFAULT NOW()
58);
59
60CREATE UNIQUE INDEX tags_idx1 ON tags (LOWER(name));
61
62--------------------------------------------------------------------------------
63
64CREATE TABLE users (
65 -- keys
66 user_id SERIAL NOT NULL PRIMARY KEY,
67
68 -- details
69 deleted_reason VARCHAR,
70 email VARCHAR NOT NULL,
71 location VARCHAR,
72 password VARCHAR NOT NULL,
73 quality_jokes NUMERIC(4,2) NOT NULL DEFAULT 0.00,
74 quality_one_liners NUMERIC(4,2) NOT NULL DEFAULT 0.00,
75 quality_riddles NUMERIC(4,2) NOT NULL DEFAULT 0.00,
76 quality_say_its NUMERIC(4,2) NOT NULL DEFAULT 0.00,
77 quality_total NUMERIC(4,2) NOT NULL DEFAULT 0.00,
78 quality_werds NUMERIC(4,2) NOT NULL DEFAULT 0.00,
79 username VARCHAR NOT NULL,
80
81 -- statuses
82 stat_active BOOLEAN NOT NULL DEFAULT false,
83 stat_comments BOOLEAN NOT NULL DEFAULT true,
84 stat_deleted BOOLEAN NOT NULL DEFAULT false,
85 stat_male BOOLEAN NOT NULL DEFAULT true,
86
87 -- dates
88 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
89 date_active TIMESTAMP,
90 date_deleted TIMESTAMP
91);
92
93CREATE UNIQUE INDEX users_idx1 ON users (LOWER(email));
94CREATE UNIQUE INDEX users_idx2 ON users (LOWER(username));
95
96--------------------------------------------------------------------------------
97
98CREATE TABLE werds (
99 -- keys
100 werd_id SERIAL NOT NULL PRIMARY KEY,
101 primary_category_id INT8 NOT NULL REFERENCES categories,
102 secondary_category_id INT8 REFERENCES categories,
103 user_id INT8 NOT NULL REFERENCES users,
104
105 -- details
106 definition VARCHAR,
107 example VARCHAR,
108 joke_body VARCHAR,
109 joke_title VARCHAR,
110 one_liner VARCHAR,
111 riddle_answer VARCHAR,
112 riddle_question VARCHAR,
113 used_when VARCHAR,
114 say_it VARCHAR,
115 werd VARCHAR,
116
117 -- statuses
118 stat_active BOOLEAN NOT NULL DEFAULT false,
119 stat_audio BOOLEAN NOT NULL DEFAULT false,
120
121 -- dates
122 date_audio_added TIMESTAMP,
123 date_created TIMESTAMP NOT NULL DEFAULT NOW()
124);
125
126CREATE UNIQUE INDEX werds_idx1 ON werds (LOWER(joke_title)) WHERE joke_title IS NOT NULL;
127CREATE UNIQUE INDEX werds_idx2 ON werds (LOWER(one_liner)) WHERE one_liner IS NOT NULL;
128CREATE UNIQUE INDEX werds_idx3 ON werds (LOWER(riddle_question)) WHERE riddle_question IS NOT NULL;
129CREATE UNIQUE INDEX werds_idx4 ON werds (LOWER(werd)) WHERE werd IS NOT NULL;
130
131--------------------------------------------------------------------------------
132
133CREATE TABLE comments (
134 -- keys
135 comment_id SERIAL NOT NULL PRIMARY KEY,
136 user_id INT8 NOT NULL REFERENCES users,
137 werd_id INT8 NOT NULL REFERENCES werds,
138
139 -- details
140 comment VARCHAR NOT NULL,
141
142 -- dates
143 date_created TIMESTAMP NOT NULL DEFAULT NOW()
144);
145
146--------------------------------------------------------------------------------
147
148CREATE TABLE newsletter_users (
149 -- keys
150 newsletter_user_id SERIAL NOT NULL PRIMARY KEY,
151 user_id INT8 NOT NULL REFERENCES users,
152
153 -- details
154 email VARCHAR NOT NULL,
155
156 -- dates
157 date_created TIMESTAMP NOT NULL DEFAULT NOW()
158);
159
160CREATE UNIQUE INDEX newsletter_users_idx1 ON newsletter_users (LOWER(email));
161
162--------------------------------------------------------------------------------
163
164CREATE TABLE say_its (
165 -- keys
166 say_it_id SERIAL NOT NULL PRIMARY KEY,
167 user_id INT8 NOT NULL REFERENCES users,
168 werd_id INT8 NOT NULL REFERENCES werds,
169
170 -- details
171 say_it VARCHAR NOT NULL,
172
173 -- dates
174 date_created TIMESTAMP NOT NULL DEFAULT NOW()
175);
176
177--------------------------------------------------------------------------------
178
179CREATE TABLE votes (
180 -- keys
181 vote_id SERIAL NOT NULL PRIMARY KEY,
182 werd_id INT8 NOT NULL REFERENCES werds,
183
184 -- details
185 http_user_agent VARCHAR NOT NULL,
186 ip_address INET NOT NULL,
187 quality NUMERIC(4,2) NOT NULL DEFAULT 0.00,
188
189 -- statuses
190 stat_good BOOLEAN NOT NULL DEFAULT false,
191
192 -- dates
193 date_created TIMESTAMP NOT NULL DEFAULT NOW()
194);
195
196--------------------------------------------------------------------------------
197
198CREATE TABLE werds_tags (
199 -- keys
200 tag_id INT8 NOT NULL REFERENCES tags,
201 werd_id INT8 NOT NULL REFERENCES werds,
202
203 -- constraints
204 PRIMARY KEY (tag_id, werd_id)
205);