· 7 years ago · Sep 17, 2018, 01:52 AM
1DROP SCHEMA public CASCADE;
2CREATE SCHEMA public;
3
4CREATE EXTENSION IF NOT EXISTS plv8;
5CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
6
7
8-- Users
9
10CREATE TYPE UserClassEnum AS ENUM ('user', 'moderator', 'admin');
11
12
13CREATE TABLE users (
14 id bigint NOT NULL,
15 created timestamp with time zone DEFAULT now() NOT NULL,
16 username text NOT NULL,
17 email text,
18 password text NOT NULL,
19 mfa_secret text,
20 balance_satoshis bigint DEFAULT 0 NOT NULL,
21 gross_profit bigint DEFAULT 0 NOT NULL,
22 net_profit bigint DEFAULT 0 NOT NULL,
23 games_played bigint DEFAULT 0 NOT NULL,
24 userclass UserClassEnum DEFAULT 'user' NOT NULL,
25 CONSTRAINT users_balance_satoshis_check CHECK ((balance_satoshis >= 0))
26);
27
28ALTER TABLE ONLY users
29 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
30
31CREATE UNIQUE INDEX unique_username ON users USING btree (lower(username));
32CREATE INDEX users_email_idx ON users USING btree (lower(email));
33CREATE INDEX user_id_idx ON users USING btree (id);
34
35CREATE SEQUENCE users_id_seq
36 START WITH 1
37 INCREMENT BY 1
38 NO MINVALUE
39 NO MAXVALUE
40 CACHE 1;
41ALTER SEQUENCE users_id_seq OWNED BY users.id;
42
43ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);
44
45
46-- Failed logins
47
48CREATE TABLE failedlogins (
49 id bigserial NOT NULL PRIMARY KEY,
50 user_id bigint NOT NULL REFERENCES users(id),
51 --password text NOT NULL,
52 ip_address inet NOT NULL,
53 user_agent text,
54 fingerprint text,
55 created timestamp with time zone DEFAULT now() NOT NULL
56);
57
58CREATE INDEX failedlogins_user_id_idx ON failedlogins(user_id);
59
60-- CREATE INDEX failedlogins_fingerprint_idx ON failedlogins USING btree (fingerprint);
61
62
63-- Transfers (Tips)
64CREATE TABLE transfers (
65 id uuid NOT NULL PRIMARY KEY,
66 from_user_id bigint NOT NULL REFERENCES users(id),
67 to_user_id bigint NOT NULL REFERENCES users(id),
68 amount bigint NOT NULL,
69 created timestamp with time zone DEFAULT now() NOT NULL,
70 CONSTRAINT user_transfer_valid_amount CHECK(amount>0)
71);
72
73CREATE INDEX transfer_from_user_id_idx ON transfers USING btree (from_user_id, created);
74CREATE INDEX transfer_to_user_id_idx ON transfers USING btree (to_user_id, created);
75
76-- Blocks
77
78CREATE TABLE blocks (
79 height integer NOT NULL,
80 hash text NOT NULL
81);
82
83ALTER TABLE ONLY blocks
84 ADD CONSTRAINT bv_blocks_pkey PRIMARY KEY (height, hash);
85
86
87
88-- Fundings
89
90CREATE TABLE fundings (
91 id bigserial NOT NULL PRIMARY KEY,
92 user_id bigint NOT NULL REFERENCES users(id),
93 amount bigint NOT NULL,
94 bitcoin_withdrawal_txid text,
95 bitcoin_withdrawal_address text,
96 created timestamp with time zone DEFAULT now() NOT NULL,
97 description text,
98 bitcoin_deposit_txid text,
99 withdrawal_id UUID,
100 withdrawal_fp text,
101 CONSTRAINT fundings_withdrawal_id_key UNIQUE (withdrawal_id)
102);
103
104ALTER TABLE ONLY fundings
105 ADD CONSTRAINT fundings_user_id_bitcoin_deposit_txid_key UNIQUE (user_id, bitcoin_deposit_txid);
106
107CREATE INDEX fundings_user_id_idx ON fundings USING btree (user_id);
108
109
110
111-- Games
112
113CREATE TABLE games (
114 id bigint NOT NULL,
115 game_crash bigint NOT NULL,
116 created timestamp with time zone DEFAULT now() NOT NULL,
117 ended boolean DEFAULT false NOT NULL
118);
119
120ALTER TABLE ONLY games ADD CONSTRAINT games_pkey PRIMARY KEY (id);
121
122CREATE SEQUENCE games_id_seq
123 START WITH 1
124 INCREMENT BY 1
125 NO MINVALUE
126 NO MAXVALUE
127 CACHE 1;
128
129ALTER SEQUENCE games_id_seq OWNED BY games.id;
130
131ALTER TABLE ONLY games ALTER COLUMN id SET DEFAULT nextval('games_id_seq'::regclass);
132
133
134
135-- Giveaways
136
137CREATE TABLE giveaways (
138 amount bigint NOT NULL,
139 created timestamp with time zone DEFAULT now() NOT NULL,
140 user_id bigint NOT NULL,
141 id bigint NOT NULL,
142 ip_address inet NULL
143);
144
145CREATE INDEX giveaways_created ON giveaways(created);
146
147CREATE INDEX giveaways_user_id_idx ON giveaways USING btree (user_id);
148
149CREATE SEQUENCE giveaways_id_seq
150 START WITH 1
151 INCREMENT BY 1
152 NO MINVALUE
153 NO MAXVALUE
154 CACHE 1;
155
156ALTER SEQUENCE giveaways_id_seq OWNED BY giveaways.id;
157
158ALTER TABLE ONLY giveaways ALTER COLUMN id SET DEFAULT nextval('giveaways_id_seq'::regclass);
159
160ALTER TABLE ONLY giveaways ADD CONSTRAINT giveaways_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
161
162
163
164-- Plays
165
166CREATE TABLE plays (
167 id bigint NOT NULL,
168 user_id bigint NOT NULL,
169 cash_out bigint,
170 auto_cash_out bigint NOT NULL,
171 game_id bigint NOT NULL,
172 created timestamp with time zone DEFAULT now() NOT NULL,
173 bet bigint NOT NULL,
174 bonus bigint
175);
176
177ALTER TABLE ONLY plays ADD CONSTRAINT plays_pkey PRIMARY KEY (id);
178
179CREATE INDEX plays_game_id_idx ON plays USING btree (game_id);
180
181CREATE INDEX plays_user_id_idx ON plays USING btree (user_id, id DESC);
182
183ALTER TABLE ONLY plays ADD CONSTRAINT plays_game_id_fkey FOREIGN KEY (game_id) REFERENCES games(id) ON UPDATE CASCADE ON DELETE CASCADE;
184
185ALTER TABLE ONLY plays ADD CONSTRAINT plays_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
186
187CREATE SEQUENCE plays_id_seq
188 START WITH 1
189 INCREMENT BY 1
190 NO MINVALUE
191 NO MAXVALUE
192 CACHE 1;
193
194ALTER SEQUENCE plays_id_seq OWNED BY plays.id;
195
196ALTER TABLE ONLY plays ALTER COLUMN id SET DEFAULT nextval('plays_id_seq'::regclass);
197
198
199
200-- Recovery
201
202CREATE TABLE recovery (
203 id uuid NOT NULL PRIMARY KEY,
204 user_id bigint NOT NULL REFERENCES users(id),
205 ip inet NOT NULL,
206 created timestamp with time zone DEFAULT now(),
207 expired timestamp with time zone DEFAULT now() + interval '15 minutes',
208 used boolean NOT NULL DEFAULT false
209);
210CREATE INDEX fki_foreing_user_id ON recovery USING btree (user_id);
211
212
213
214-- Sessions:
215 -- Regular sessions for users and one time tokens for the cross origin connection to the game server
216 -- Ott allows to let the session is http only
217
218CREATE TABLE sessions (
219 id uuid NOT NULL,
220 user_id bigint NOT NULL,
221 ip_address inet NOT NULL,
222 user_agent text,
223 fingerprint text, -- The fingerprint of the browser that created this session
224 ott boolean DEFAULT false,
225 created timestamp with time zone NOT NULL DEFAULT now(),
226 expired timestamp with time zone NOT NULL DEFAULT now() + interval '21 days'
227);
228
229ALTER TABLE ONLY sessions
230 ADD CONSTRAINT unique_id PRIMARY KEY (id);
231
232CREATE INDEX sessions_user_id_idx ON sessions USING btree (user_id, expired);
233
234
235
236-- Users View
237
238CREATE VIEW users_view AS
239 SELECT u.id,
240 u.created,
241 u.username,
242 u.email,
243 u.password,
244 u.mfa_secret,
245 u.balance_satoshis,
246 u.games_played,
247 ( SELECT max(giveaways.created) AS max
248 FROM giveaways
249 WHERE (giveaways.user_id = u.id)) AS last_giveaway,
250 u.userclass
251 FROM users u;
252
253
254
255CREATE TABLE game_hashes
256(
257 game_id bigint NOT NULL,
258 hash text NOT NULL,
259 CONSTRAINT game_hashes_pkey PRIMARY KEY (game_id)
260);
261
262
263
264-- Leaderboard View
265
266CREATE MATERIALIZED VIEW leaderboard AS
267 SELECT id as user_id,
268 username,
269 gross_profit,
270 net_profit,
271 games_played,
272 rank() OVER (ORDER BY gross_profit DESC) AS rank
273 FROM users;
274
275CREATE UNIQUE INDEX leaderboard_user_id_idx
276 ON leaderboard
277 USING btree
278 (user_id);
279
280CREATE INDEX leaderboard_username_idx ON leaderboard USING btree (lower(username));
281
282CREATE INDEX leaderboard_gross_profit_idx ON leaderboard USING btree (gross_profit);
283
284CREATE INDEX leaderboard_net_profit_idx ON leaderboard USING btree (net_profit);
285
286
287
288-- Chat messages
289
290CREATE TABLE chat_messages
291(
292 id bigserial NOT NULL PRIMARY KEY,
293 user_id bigint NOT NULL REFERENCES users(id),
294 message text NOT NULL,
295 created timestamp with time zone DEFAULT now() NOT NULL,
296 is_bot boolean NOT NULL,
297 channel text NOT NULL
298);
299
300CREATE INDEX chat_messages_user_id_idx ON chat_messages USING btree(user_id);
301CREATE INDEX chat_messages_channel_id_idx ON chat_messages USING btree(channel, id);
302
303
304
305-- User stats
306
307CREATE OR REPLACE FUNCTION plays_users_stats_trigger()
308 RETURNS trigger AS $$
309
310 if (TG_OP === 'UPDATE' && OLD.user_id !== NEW.user_id)
311 throw new Error('Update of user_id not allowed');
312
313 var userId, gross = 0, net = 0, num = 0;
314 var bet, cashOut, bonus;
315
316 // Add new values.
317 if (NEW) {
318 userId = NEW.user_id;
319 bet = NEW.bet;
320 bonus = NEW.bonus || 0;
321 cashOut = NEW.cash_out || 0;
322
323 gross += Math.max(cashOut - bet, 0) + bonus;
324 net += (cashOut - bet) + bonus;
325 num += 1;
326 }
327
328 // Subtract old values
329 if (OLD) {
330 userId = OLD.user_id;
331 bet = OLD.bet;
332 bonus = OLD.bonus || 0;
333 cashOut = OLD.cash_out || 0;
334
335 gross -= Math.max(cashOut - bet, 0) + bonus;
336 net -= (cashOut - bet) + bonus;
337 num -= 1;
338 }
339
340 var sql =
341 'UPDATE users ' +
342 ' SET gross_profit = gross_profit + $1, ' +
343 ' net_profit = net_profit + $2, ' +
344 ' games_played = games_played + $3 ' +
345 ' WHERE id = $4';
346 var par = [gross,net,num,userId];
347 plv8.execute(sql,par);
348$$ LANGUAGE plv8;
349
350CREATE TRIGGER plays_users_stats_trigger
351AFTER INSERT OR UPDATE OR DELETE ON plays
352 FOR EACH ROW EXECUTE PROCEDURE plays_users_stats_trigger();
353
354
355CREATE FUNCTION ip_root(ip_address inet) RETURNS inet AS $$
356 BEGIN
357 RETURN host(network(set_masklen(ip_address, (CASE family(ip_address) WHEN 4 THEN 24 ELSE 48 END))));
358 END;
359$$ LANGUAGE plpgsql IMMUTABLE;
360
361
362CREATE TABLE deposit_addresses (
363 id bigserial PRIMARY KEY,
364 user_id bigint REFERENCES users(id),
365 address text NOT NULL,
366 created timestamptz NOT NULL DEFAULT NOW()
367);
368
369CREATE INDEX deposit_addresses_user_id_idx ON deposit_addresses (user_id);
370CREATE UNIQUE INDEX deposit_addresses_address_unique_idx ON deposit_addresses(address);
371
372CREATE TABLE deposits (
373 id bigserial NOT NULL,
374 deposit_addresses_id bigint NOT NULL REFERENCES deposit_addresses(id),
375 txid text NOT NULL,
376 vout bigint NOT NULL,
377 amount bigint NOT NULL,
378 fee bigint NOT NULL DEFAULT 0
379);