· 7 years ago · Oct 09, 2018, 06:02 PM
1DROP TABLE IF EXISTS abilities;
2
3CREATE TABLE abilities
4(
5 char_idx INTEGER,
6 aindex INTEGER,
7 level INT,
8 max_level INT,
9 hard_max INT,
10 modifier INT,
11 disable INT,
12 general_skill INT
13);
14
15DROP TABLE IF EXISTS character_data;
16
17CREATE TABLE character_data
18(
19 char_idx INTEGER PRIMARY KEY,
20 respawns INTEGER,
21 health INTEGER,
22 maxhealth INTEGER,
23 armour INTEGER,
24 maxarmour INTEGER,
25 nerfme INTEGER,
26 adminlevel INTEGER,
27 bosslevel INTEGER
28);
29
30DROP TABLE IF EXISTS ctf_stats;
31
32CREATE TABLE ctf_stats
33(
34 char_idx INTEGER,
35 flag_pickups INTEGER,
36 flag_captures INTEGER,
37 flag_returns INTEGER,
38 flag_kills INTEGER,
39 offense_kills INTEGER,
40 defense_kills INTEGER,
41 assists INTEGER
42);
43
44DROP TABLE IF EXISTS game_stats;
45
46CREATE TABLE game_stats
47(
48 char_idx INTEGER,
49 shots INTEGER,
50 shots_hit INTEGER,
51 frags INTEGER,
52 fragged INTEGER,
53 num_sprees INTEGER,
54 max_streak INTEGER,
55 spree_wars INTEGER,
56 broken_sprees INTEGER,
57 broken_spreewars INTEGER,
58 suicides INT,
59 teleports INTEGER,
60 num_2fers INTEGER
61);
62
63DROP TABLE IF EXISTS point_data;
64
65CREATE TABLE point_data
66(
67 char_idx INTEGER,
68 exp INTEGER,
69 exptnl INTEGER,
70 level INTEGER,
71 classnum INTEGER,
72 skillpoints INTEGER,
73 credits INTEGER,
74 weap_points INTEGER,
75 resp_weapon INTEGER,
76 tpoints INTEGER
77);
78
79DROP TABLE IF EXISTS runes_meta;
80
81CREATE TABLE runes_meta
82(
83 char_idx INTEGER,
84 rindex INTEGER,
85 itemtype INTEGER,
86 itemlevel INTEGER,
87 quantity INTEGER,
88 untradeable INTEGER,
89 id CHAR(16),
90 name CHAR(24),
91 nummods INTEGER,
92 setcode INTEGER,
93 classnum INTEGER
94);
95
96DROP TABLE IF EXISTS runes_mods;
97
98CREATE TABLE runes_mods
99(
100 char_idx INTEGER,
101 rune_index INTEGER,
102 rmod INTEGER,
103 type INTEGER,
104 mindex INTEGER,
105 value INTEGER,
106 rset INTEGER
107);
108
109DROP TABLE IF EXISTS talents;
110
111CREATE TABLE talents
112(
113 char_idx INTEGER,
114 id INTEGER,
115 upgrade_level INTEGER,
116 max_level INTEGER
117);
118
119DROP TABLE IF EXISTS userdata;
120
121CREATE TABLE userdata
122(
123 char_idx INTEGER,
124 title CHAR(24),
125 playername CHAR(64),
126 password CHAR(24),
127 email CHAR(64),
128 owner CHAR(24),
129 member_since CHAR(30),
130 last_played CHAR(30),
131 playtime_total INTEGER,
132 playingtime INTEGER,
133 isplaying INTEGER
134);
135
136DROP TABLE IF EXISTS weapon_meta;
137
138CREATE TABLE weapon_meta
139(
140 char_idx INTEGER,
141 windex INTEGER,
142 disable INTEGER
143);
144
145DROP TABLE IF EXISTS weapon_mods;
146
147CREATE TABLE weapon_mods
148(
149 char_idx INTEGER,
150 weapon_index INTEGER,
151 modindex INTEGER,
152 level INTEGER,
153 soft_max INTEGER,
154 hard_max INTEGER
155);
156
157-- Todo: Uniques list in da SQL!
158
159DELIMITER $$
160
161DROP PROCEDURE IF EXISTS FillNewChar; $$
162
163-- We create a new character with this function.
164CREATE PROCEDURE FillNewChar(IN charname VARCHAR(64))
165BEGIN
166
167 DECLARE chid INT DEFAULT 0;
168
169 START TRANSACTION;
170
171 -- Give us the character id so we generate this new character!
172 SELECT COUNT(char_idx) INTO chid FROM character_data WHERE char_idx IS NOT NULL;
173
174 INSERT INTO character_data (char_idx) VALUES(chid);
175
176 INSERT INTO userdata (char_idx, playername) VALUES (chid, charname);
177 INSERT INTO point_data (char_idx) VALUES (chid);
178 INSERT INTO game_stats (char_idx) VALUES (chid);
179 INSERT INTO ctf_stats (char_idx) VALUES (chid);
180
181 COMMIT;
182
183END $$
184
185DROP PROCEDURE IF EXISTS CharacterExists; $$
186
187CREATE PROCEDURE CharacterExists(IN pname varchar(64), OUT doesexist int)
188BEGIN
189
190 SELECT EXISTS (
191 SELECT (char_idx) FROM userdata
192 WHERE userdata.playername = pname
193 ) INTO doesexist;
194
195END $$
196
197DROP PROCEDURE IF EXISTS ResetTables; $$
198
199CREATE PROCEDURE ResetTables(IN pname varchar(64))
200BEGIN
201
202 DECLARE chid INT DEFAULT 0;
203
204 START TRANSACTION;
205
206 SELECT (char_idx) INTO chid FROM userdata WHERE playername=pname;
207
208 DELETE FROM abilities WHERE char_idx = chid;
209 DELETE FROM talents WHERE char_idx = chid;
210 DELETE FROM runes_meta WHERE char_idx = chid;
211 DELETE FROM runes_mods WHERE char_idx = chid;
212 DELETE FROM weapon_meta WHERE char_idx = chid;
213 DELETE FROM weapon_mods WHERE char_idx = chid;
214
215 COMMIT;
216
217END $$
218
219DROP PROCEDURE IF EXISTS GetCharID; $$
220
221CREATE PROCEDURE GetCharID(IN pname varchar(64), OUT chidx INT)
222BEGIN
223 SELECT (char_idx) INTO chidx FROM userdata WHERE playername = pname;
224END $$
225
226DROP PROCEDURE IF EXISTS CanPlay; $$
227
228CREATE PROCEDURE CanPlay(IN chidx INT, OUT canplay int)
229BEGIN
230
231 DECLARE lp DATE;
232 DECLARE str_lp VARCHAR (128);
233
234 SELECT (last_played) INTO str_lp FROM userdata WHERE char_idx = chidx;
235
236 if str_lp != "" THEN
237 SELECT DATE (str_lp) INTO lp
238 FROM userdata
239 WHERE char_idx = chidx;
240 END IF;
241
242 if lp = current_date AND (select (isplaying) FROM userdata WHERE char_idx = chidx) THEN
243 SET canplay = 0;
244 else
245 SET canplay = 1;
246 END IF;
247
248
249END
250-- Saving/Loading procedures are done in-dll, since the code for that is mostly already written. :D
251-- That DOES mean the dll MUST be updated before submitting stuff to the database. And the database as well.