· 9 years ago · Jan 26, 2017, 06:24 PM
1CREATE TABLE USERS(
2 -- PROGRESSIVE ID COUNT. IN MYSQL IT'S POSSIBLE TO SET IT TO AUTOINCREMENT, BUT THEN YOU NEED TO FETCH IT NEVERTHELESS BECAUSE YOU CAN'T TELL WHAT IT WAS
3 ID INT NOT NULL PRIMARY KEY,
4 -- NOTE 0 IS NOT REGISTERED, 1 IS REGISTERED USER THE CHECKS AND THE TRIGGERS MAKE SURE THE CONSTRAINTS ARE RESPECTED
5 TYPE1 INT NOT NULL CHECK (TYPE1 IN(0, 1)),
6 PASSWORD VARCHAR(100),
7 -- IF THE ANONYMOUS ACCOUNT NEEDS A NAME CHANGE THIS SECTION TO NOT NULL, ADD A DEFAULT VALUE AND DELETE THE NAME FILED FROM THE CHECK
8 NAME VARCHAR(100),
9 CONSTRAINT CONSISTENCY CHECK ((TYPE1 = 0 AND PASSWORD IS NULL AND NAME IS NULL) OR (TYPE1 = 1 AND PASSWORD IS NOT NULL AND NAME IS NOT NULL))
10);
11
12CREATE TABLE ROLES(
13 -- ONLY ONE COMBINATION OF TAG AND USER IS POSSIBLE TO AVOID DUPLICATES (THIS IS GRANTED BY THE DOUBLE PRIMARY KEY)
14 ID INT NOT NULL REFERENCES USERS,
15 ROLE VARCHAR(3) NOT NULL,
16 -- ADD OR REMOVE HERE THE EVENTUAL NEW ROLES
17 CONSTRAINT CONSISTENCY CHECK (ROLE = 'DEV' OR ROLE = 'MOD' OR ROLE = 'ADM'),
18 PRIMARY KEY (ID, ROLE)
19);
20
21CREATE TABLE SETTINGS(
22 -- ONLY ONE SETTINGS PER USER
23 ID INT NOT NULL PRIMARY KEY REFERENCES USERS,
24 -- BOOLEAN IT'S A TYPE BUT WHO CARES
25 CYRILLIC INT NOT NULL CHECK( CYRILLIC IN(0, 1)),
26 -- THIS IS SMARTER TO DO WITH A VARCHAR BUT, HEY
27 DEFAULTSERVER INT NOT NULL,
28 SWEARS INT NOT NULL CHECK( SWEARS IN(0, 1))
29);
30
31CREATE TABLE SITES(
32 -- ONLY ONE SITE PER USER (TBH THE REAL ONE PERMIT MULTIPLE SITES, JUST REMOVE THE "PRIMARY KEY" PART FOR THAT. I'D SUGGEST TO MAKE SITE "UNIQUE" TO AVOID DOUBLE-SITE LINKING)
33 ID INT NOT NULL PRIMARY KEY REFERENCES USERS,
34 SITE VARCHAR(100) NOT NULL
35);
36
37CREATE TABLE PONIES(
38 ID INT NOT NULL REFERENCES USERS,
39 NAME VARCHAR(100) NOT NULL
40 -- OTHER INFOS HERE
41);
42
43-- TRIGGERS
44-- THESE GRANTS THE CONSISTENCY OF THE DATABASE ANF THE FACT THAT UNREGISTERED USERS CAN'T INSERT THINGS IN A DB
45
46-- IF IT'S PLANNED TO USE UPDATE OR DELETE QUERIES IT'S BETTER USING BEFORE UPDATE AND BEFORE DELETE TRIGGERS TOO
47-- TECHNICALLY HERE NEEDS TO CHANGE DELIMITER WITH //
48
49CREATE TRIGGER CONS1
50BEFORE INSERT ON SITES
51FOR EACH ROW
52BEGIN
53 IF EXISTS(
54 SELECT *
55 FROM USERS U
56 WHERE U.ID = NEW.ID
57 AND U.TYPE1 = 0
58 ) THEN SIGNAL SQLSTATE '70000';
59 END IF;
60END//
61
62CREATE TRIGGER CONS2
63BEFORE INSERT ON PONIES
64FOR EACH ROW
65BEGIN
66 IF EXISTS(
67 SELECT *
68 FROM USERS U
69 WHERE U.ID = NEW.ID
70 AND U.TYPE1 = 0
71 ) THEN SIGNAL SQLSTATE '70001';
72 END IF;
73END//
74
75CREATE TRIGGER CONS3
76BEFORE INSERT ON SETTINGS
77FOR EACH ROW
78BEGIN
79 IF EXISTS(
80 SELECT *
81 FROM USERS U
82 WHERE U.ID = NEW.ID
83 AND U.TYPE1 = 0
84 ) THEN SIGNAL SQLSTATE '70002';
85 END IF;
86END//
87
88CREATE TRIGGER CONS4
89BEFORE INSERT ON ROLES
90FOR EACH ROW
91BEGIN
92 IF EXISTS(
93 SELECT *
94 FROM USERS U
95 WHERE U.ID = NEW.ID
96 AND U.TYPE1 = 0
97 ) THEN SIGNAL SQLSTATE '70003';
98 END IF;
99END//
100
101-- TEST INSERTING
102
103INSERT INTO USERS (ID, TYPE1, PASSWORD, NAME)
104VALUES(1, 0, NULL, NULL),
105(2, 1, 'batman', 'Anderej G Faggotov'),
106(3, 1, 'password1', 'Niggest'),
107(4, 1, 'password2', 'RGB');
108
109INSERT INTO ROLES (ID, ROLE)
110VALUES(3, 'DEV'),
111(4, 'MOD'),
112(4, 'DEV'),
113(3, 'ADM'),
114(3, 'MOD');
115
116INSERT INTO SETTINGS (ID, CYRILLIC, DEFAULTSERVER, SWEARS)
117VALUES(2, 0, 0, 1),
118(3, 0, 1, 0),
119(4, 0, 1, 1);
120
121INSERT INTO SITES (ID, SITE)
122VALUES(4, 'https://google.de'),
123(3, 'https://4chan.org'),
124(2, 'https://rizon.net');
125
126INSERT INTO PONIES(ID, NAME)
127VALUES(2, 'Zarathustra'),
128(3, 'Niggest'),
129(4, 'RGB'),
130(2, 'Octavia');
131
132-- EXPLODING (THEY HAVE TO)
133-- this is an unregistered user trying to save a pone
134INSERT INTO PONIES(ID, NAME)
135VALUES(1, 'Fire');
136
137-- this is an unregistered user trying to save a site
138INSERT INTO SITES (ID, SITE)
139VALUES(1, 'https://google.com');
140
141-- this is an unregistered user trying to save settings
142INSERT INTO SETTINGS (ID, CYRILLIC, DEFAULTSERVER, SWEARS)
143VALUES(1, 0, 0, 1);
144
145-- this is an unregistered user trying to gain mod/dev
146INSERT INTO ROLES (ID, ROLE)
147VALUES(1, 'DEV');