-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmysqlprocs
131 lines (104 loc) · 3.55 KB
/
mysqlprocs
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
DELIMITER |
drop function SteamToInt|
create function SteamToInt(steamid varchar(64))
RETURNS bigint(64) DETERMINISTIC
BEGIN
declare authserver int;
declare authid int;
set authserver = cast(substr(steamid,9,1) as unsigned integer);
set authid = cast(substr(steamid,11) as unsigned integer);
return 76561197960265728+(authid*2)+authserver;
END |
drop function IntToSteam|
create function IntToSteam(communityid bigint(64))
RETURNS varchar(64) DETERMINISTIC
BEGIN
declare ret varchar(64);
declare authserver int;
declare authid bigint;
set communityid = communityid-76561197960265728;
set authserver = mod(communityid,2);
set communityid = communityid-authserver;
set authid = communityid/2;
set ret = concat("STEAM_0:",authserver,":",authid);
return ret;
END |
DELIMITER ;
DROP TABLE servers;
DROP TABLE players;
DROP TABLE player_weapons;
DROP TABLE player_events;
DROP TABLE player_names;
DROP TABLE player_targets;
DROP TABLE player_team;
DROP TABLE player_maps;
CREATE TABLE servers (
server_id int(11) NOT NULL auto_increment
,server_ip varchar(16) default NULL
,server_port int(11) default NULL
,PRIMARY KEY (server_id)
,UNIQUE KEY server_ip (server_ip,server_port)
) ENGINE=InnoDB;
CREATE TABLE players (
server_id int(11) NOT NULL default '0'
,player_id bigint(20) NOT NULL default '0'
,lastconnect datetime default NULL
,CONSTRAINT FOREIGN KEY (server_id) REFERENCES servers (server_id)
,PRIMARY KEY (server_id,player_id)
) ENGINE=InnoDB;
CREATE TABLE player_weapons (
server_id int(11) NOT NULL default '0'
,player_id bigint(20) NOT NULL default '0'
,weapon_name varchar(64) NOT NULL default ''
,kills int(11) default NULL
,headshots int(11) default NULL
,damage int(11) default NULL
,tks int(11) default NULL
,CONSTRAINT FOREIGN KEY (server_id,player_id) REFERENCES players (server_id,player_id)
,PRIMARY KEY (server_id,player_id,weapon_name)
) ENGINE=InnoDB;
CREATE TABLE player_events (
server_id int(11) NOT NULL default '0'
,player_id bigint(20) NOT NULL default '0'
,event_name varchar(64) NOT NULL default ''
,triggercount int(11) default NULL
,CONSTRAINT FOREIGN KEY (server_id,player_id) REFERENCES players (server_id,player_id)
,PRIMARY KEY (server_id,player_id,event_name)
) ENGINE=InnoDB;
CREATE TABLE player_names (
server_id int(11) NOT NULL
,player_id bigint(20) NOT NULL
,player_name varchar(128)
,lastuse datetime
,CONSTRAINT FOREIGN KEY (server_id,player_id) REFERENCES players (server_id,player_id)
,PRIMARY KEY (server_id,player_id,player_name)
) ENGINE=InnoDB;
CREATE TABLE player_team (
server_id int(11) NOT NULL
,player_id bigint(20) NOT NULL
,team_name varchar(32)
,join_count int(5)
,CONSTRAINT FOREIGN KEY (server_id,player_id) REFERENCES players (server_id,player_id)
,PRIMARY KEY (server_id,player_id,team_name)
) ENGINE=InnoDB;
CREATE TABLE player_maps (
server_id int(11) NOT NULL
,player_id bigint(20) NOT NULL
,map_name varchar(64)
,kills int(11)
,deaths int(11)
,headshots int(11)
,suicides int(11)
,CONSTRAINT FOREIGN KEY (server_id,player_id) REFERENCES players (server_id,player_id)
,PRIMARY KEY (server_id,player_id,map_name)
) ENGINE=InnoDB;
CREATE TABLE player_targets (
server_id int(11) NOT NULL
,player_id bigint(20) NOT NULL
,target_id bigint(20) NOT NULL
,kills int(11)
,headshots int(11)
,CONSTRAINT FOREIGN KEY (server_id,player_id) REFERENCES players (server_id,player_id)
,CONSTRAINT FOREIGN KEY (server_id,target_id) REFERENCES players (server_id,player_id)
,PRIMARY KEY (server_id,player_id,target_id)
) ENGINE=InnoDB;