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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
|
drop table scorched3d_main;
drop table scorched3d_prefixs;
drop table scorched3d_servers;
drop table scorched3d_series;
drop table scorched3d_players;
drop table scorched3d_stats;
drop table scorched3d_statssource;
drop table scorched3d_events;
drop table scorched3d_eventtypes;
drop table scorched3d_names;
drop table scorched3d_weapons;
drop table scorched3d_binary;
drop table scorched3d_ipaddress;
create table if not exists scorched3d_prefixs (
prefixid INTEGER auto_increment,
prefix varchar(64),
PRIMARY KEY (prefixid)
);
create table if not exists scorched3d_servers (
serverid INTEGER auto_increment,
name varchar(64),
published varchar(64),
displaystats INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (serverid),
UNIQUE(name)
);
create table if not exists scorched3d_eventtypes (
eventtype INTEGER,
name varchar(32),
PRIMARY KEY (eventtype)
);
create table if not exists scorched3d_series (
seriesid INTEGER auto_increment,
started DATETIME,
ended DATETIME,
name varchar(128),
games INTEGER NOT NULL DEFAULT 0,
rounds INTEGER NOT NULL DEFAULT 0,
type INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (seriesid)
);
create table if not exists scorched3d_players (
playerid INTEGER auto_increment,
uniqueid varchar(64),
name varchar(32),
ipaddress varchar(32),
osdesc varchar(32) NOT NULL DEFAULT '',
avatarid INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (playerid),
UNIQUE (uniqueid),
INDEX uniqueid_index (uniqueid),
INDEX avatarid_index (avatarid)
);
create table if not exists scorched3d_statssource (
serverid INTEGER,
prefixid INTEGER,
seriesid INTEGER,
PRIMARY KEY (serverid, prefixid, seriesid),
FOREIGN KEY (serverid) REFERENCES scorched3d_servers(serverid) on delete cascade,
FOREIGN KEY (prefixid) REFERENCES scorched3d_prefixs(prefixid) on delete cascade,
FOREIGN KEY (seriesid) REFERENCES scorched3d_series(seriesid) on delete cascade
);
create table if not exists scorched3d_stats (
playerid INTEGER,
prefixid INTEGER,
seriesid INTEGER,
connects INTEGER NOT NULL DEFAULT 0,
lastconnected DATETIME,
kills INTEGER NOT NULL DEFAULT 0,
deaths INTEGER NOT NULL DEFAULT 0,
selfkills INTEGER NOT NULL DEFAULT 0,
teamkills INTEGER NOT NULL DEFAULT 0,
shots INTEGER NOT NULL DEFAULT 0,
wins INTEGER NOT NULL DEFAULT 0,
overallwinner INTEGER NOT NULL DEFAULT 0,
resigns INTEGER NOT NULL DEFAULT 0,
gamesplayed INTEGER NOT NULL DEFAULT 0,
timeplayed INTEGER NOT NULL DEFAULT 0,
roundsplayed INTEGER NOT NULL DEFAULT 0,
moneyearned INTEGER NOT NULL DEFAULT 0,
scoreearned INTEGER NOT NULL DEFAULT 0,
skill INTEGER NOT NULL DEFAULT 1000,
rank INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (playerid, prefixid, seriesid),
FOREIGN KEY (playerid) REFERENCES scorched3d_players(playerid) on delete cascade,
FOREIGN KEY (prefixid) REFERENCES scorched3d_prefixs(prefixid) on delete cascade,
FOREIGN KEY (seriesid) REFERENCES scorched3d_series(seriesid) on delete cascade
);
create table if not exists scorched3d_weapons (
weaponid INTEGER auto_increment,
prefixid INTEGER,
seriesid INTEGER,
name varchar(64),
icon varchar(64),
description varchar(255) NOT NULL DEFAULT 'No Desc',
cost INTEGER NOT NULL DEFAULT 0,
bundlesize INTEGER NOT NULL DEFAULT 0,
armslevel INTEGER NOT NULL DEFAULT 0,
kills INTEGER NOT NULL DEFAULT 0,
shots INTEGER NOT NULL DEFAULT 0,
deathshots INTEGER NOT NULL DEFAULT 0,
deathkills INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (weaponid, prefixid, seriesid),
FOREIGN KEY (prefixid) REFERENCES scorched3d_prefixs(prefixid) on delete cascade,
FOREIGN KEY (seriesid) REFERENCES scorched3d_series(seriesid) on delete cascade
);
create table if not exists scorched3d_events (
eventid INTEGER auto_increment,
prefixid INTEGER,
seriesid INTEGER,
eventtype INTEGER,
playerid INTEGER,
otherplayerid INTEGER,
weaponid INTEGER,
eventtime DATETIME,
PRIMARY KEY (eventid),
FOREIGN KEY (playerid) REFERENCES scorched3d_players(playerid) on delete cascade,
FOREIGN KEY (eventtype) REFERENCES scorched3d_eventtypes(eventtype) on delete cascade,
FOREIGN KEY (prefixid) REFERENCES scorched3d_prefixs(prefixid) on delete cascade,
FOREIGN KEY (seriesid) REFERENCES scorched3d_series(seriesid) on delete cascade
);
create table if not exists scorched3d_names (
playerid INTEGER NOT NULL DEFAULT 0,
name varchar(32) BINARY NOT NULL DEFAULT "",
count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (playerid, name),
FOREIGN KEY (playerid) REFERENCES scorched3d_players(playerid) on delete cascade
);
create table if not exists scorched3d_ipaddress (
playerid INTEGER NOT NULL DEFAULT 0,
ipaddress varchar(32) BINARY NOT NULL DEFAULT "",
count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (playerid, ipaddress),
FOREIGN KEY (playerid) REFERENCES scorched3d_players(playerid) on delete cascade
);
create table if not exists scorched3d_binary (
binaryid INTEGER auto_increment,
name varchar(32) BINARY NOT NULL DEFAULT "",
crc INTEGER UNSIGNED NOT NULL DEFAULT 0,
length INTEGER UNSIGNED NOT NULL DEFAULT 0,
data BLOB,
PRIMARY KEY(binaryid)
);
create table if not exists scorched3d_achievement_names (
achievementid INTEGER auto_increment,
name varchar(64) BINARY NOT NULL DEFAULT "",
PRIMARY KEY(achievementid)
);
create table if not exists scorched3d_achievements (
achievementid INTEGER NOT NULL DEFAULT 0,
playerid INTEGER NOT NULL DEFAULT 0,
achievementrank INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(achievementid, playerid),
FOREIGN KEY (achievementid) REFERENCES scorched3d_achievement_names(achievementrank) on delete cascade,
FOREIGN KEY (playerid) REFERENCES scorched3d_players(playerid) on delete cascade
);
|
