· 8 years ago · Apr 19, 2018, 12:46 AM
1//
2// MYSQL Create database
3//
4
5void OW_MySQL_CreateTable ( void ) {
6
7 Com_Printf( "------ Creating MySQL Tables ------\n" );
8
9 // If it is connected to database
10 if( connection ) {
11
12 //
13 // Userinfo table structure
14 // Player relevate structure
15 //
16
17 // create table user_info and fill table userinfo with some values
18 mysql_query(connection, "CREATE TABLE IF NOT EXISTS user_info (\
19 user_id mediumint(8) unsigned NOT NULL auto_increment, \
20 clan_id mediumint(8) unsigned NOT NULL default '3', \
21 user_permissions mediumtext collate utf8_bin NOT NULL, \
22 user_perm_from mediumint(8) unsigned NOT NULL default '0', \
23 user_ip varchar(40) collate utf8_bin NOT NULL default '', \
24 user_guid varchar(33) collate utf8_bin NOT NULL default '', \
25 user_regdate int(11) unsigned NOT NULL default '0', \
26 username varchar(255) collate utf8_bin NOT NULL default '', \
27 username_clean varchar(255) collate utf8_bin NOT NULL default '', \
28 user_password varchar(40) collate utf8_bin NOT NULL default '', \
29 user_passchg int(11) unsigned NOT NULL default '0', \
30 user_pass_convert tinyint(1) unsigned NOT NULL default '0', \
31 user_newpasswd varchar(40) collate utf8_bin NOT NULL default '', \
32 user_email varchar(100) collate utf8_bin NOT NULL default '', \
33 user_email_hash bigint(20) NOT NULL default '0', \
34 user_birthday varchar(10) collate utf8_bin NOT NULL default '', \
35 user_lastvisit int(11) unsigned NOT NULL default '0', \
36 user_warnings tinyint(4) NOT NULL default '0', \
37 user_last_warning int(11) unsigned NOT NULL default '0', \
38 user_login_attempts tinyint(4) NOT NULL default '0', \
39 user_inactive_reason tinyint(2) NOT NULL default '0', \
40 user_inactive_time int(11) unsigned NOT NULL default '0', \
41 user_timezone decimal(5,2) NOT NULL default '0.00', \
42 user_dst tinyint(1) unsigned NOT NULL default '0', \
43 user_dateformat varchar(30) collate utf8_bin NOT NULL default 'd M Y H:i', \
44 user_rank mediumint(8) unsigned NOT NULL default '0', \
45 user_new_privmsg int(4) NOT NULL default '0', \
46 user_unread_privmsg int(4) NOT NULL default '0', \
47 user_last_privmsg int(11) unsigned NOT NULL default '0', \
48 user_message_rules tinyint(1) unsigned NOT NULL default '0', \
49 user_full_folder int(11) NOT NULL default '-3', \
50 user_emailtime int(11) unsigned NOT NULL default '0', \
51 user_avatar varchar(255) collate utf8_bin NOT NULL default '', \
52 user_avatar_type tinyint(2) NOT NULL default '0', \
53 user_avatar_width smallint(4) unsigned NOT NULL default '0', \
54 user_avatar_height smallint(4) unsigned NOT NULL default '0', \
55 user_from varchar(100) collate utf8_bin NOT NULL default '', \
56 user_yim varchar(255) collate utf8_bin NOT NULL default '', \
57 user_msnm varchar(255) collate utf8_bin NOT NULL default '', \
58 user_website varchar(200) collate utf8_bin NOT NULL default '', \
59 user_interests text collate utf8_bin NOT NULL, \
60 PRIMARY KEY (user_id), \
61 UNIQUE KEY username_clean (username_clean), \
62 KEY user_birthday (user_birthday), \
63 KEY user_email_hash (user_email_hash))");
64
65 //
66 // Ban table structure
67 //
68
69 mysql_query(connection, "CREATE TABLE IF NOT EXISTS user_banlist (\
70 ban_id mediumint(8) unsigned NOT NULL auto_increment, \
71 ban_userid mediumint(8) unsigned NOT NULL default '0', \
72 ban_ip varchar(40) collate utf8_bin NOT NULL default '', \
73 ban_guid varchar(33) collate utf8_bin NOT NULL default '', \
74 ban_email varchar(100) collate utf8_bin NOT NULL default '', \
75 ban_start int(11) unsigned NOT NULL default '0', \
76 ban_end int(11) unsigned NOT NULL default '0', \
77 ban_exclude tinyint(1) unsigned NOT NULL default '0', \
78 ban_reason varchar(255) collate utf8_bin NOT NULL default '', \
79 ban_give_reason varchar(255) collate utf8_bin NOT NULL default '', \
80 PRIMARY KEY (ban_id), \
81 KEY ban_end (ban_end), \
82 KEY ban_user (ban_userid,ban_exclude), \
83 KEY ban_email (ban_email,ban_exclude), \
84 KEY ban_ip (ban_ip,ban_exclude))");
85
86 //
87 // Mute table structure
88 // This is copy from ban table
89 //
90
91 mysql_query(connection, "CREATE TABLE IF NOT EXISTS user_mutelist (\
92 mute_id mediumint(8) unsigned NOT NULL auto_increment, \
93 mute_userid mediumint(8) unsigned NOT NULL default '0', \
94 mute_ip varchar(40) collate utf8_bin NOT NULL default '', \
95 mute_guid varchar(33) collate utf8_bin NOT NULL default '', \
96 mute_start int(11) unsigned NOT NULL default '0', \
97 mute_end int(11) unsigned NOT NULL default '0', \
98 mute_exclude tinyint(1) unsigned NOT NULL default '0', \
99 mute_reason varchar(255) collate utf8_bin NOT NULL default '', \
100 PRIMARY KEY (mute_id), \
101 KEY mute_end (mute_end), \
102 KEY mute_user (mute_userid,mute_exclude), \
103 KEY mute_ip (mute_ip,mute_exclude))");
104
105
106 // ========================================================================== //
107
108 //
109 // Create server structure
110 // Server/Mod relevate structure
111 //
112
113 // create table players
114 mysql_query(connection, "CREATE TABLE `user_players` ( \
115 `user_guid` char(36) COLLATE utf8_bin NOT NULL, \
116 `user_permissions` mediumtext COLLATE utf8_bin, \
117 `user_ip4` varchar(15) COLLATE utf8_bin DEFAULT NULL, \
118 `user_ip6` varchar(39) COLLATE utf8_bin DEFAULT NULL, \
119 `user_xp` int(11) DEFAULT '0', \
120 `user_skill` int(11) DEFAULT '0', \
121 `username` varchar(50) COLLATE utf8_bin DEFAULT NULL, \
122 `clanname` varchar(50) COLLATE utf8_bin DEFAULT NULL, \
123 `user_password` varchar(50) COLLATE utf8_bin DEFAULT NULL, \
124 `user_email` varchar(255) COLLATE utf8_bin DEFAULT NULL, \
125 PRIMARY KEY (`user_guid`))");
126
127
128 // ========================================================================== //
129
130 //
131 // Player Stats structure
132 //
133
134 // NOTE : used information from VSP Stats
135
136 // create table player_stats
137 mysql_query(connection, "CREATE TABLE IF NOT EXISTS player_stats (\
138 playerID varchar(100) BINARY NOT NULL default '', \
139 playerName varchar(255) NOT NULL default '', \
140 skill int(10) unsigned default '0', \
141 kills int(11) default '0', \
142 deaths int(11) default '0', \
143 kill_streak int(11) default '0', \
144 death_streak int(11) default '0', \
145 games int(10) unsigned default '0', \
146 PRIMARY KEY (`playerID`))");
147
148 //
149 // OW HUB structure
150 //
151
152#ifdef USE_HUB_SERVER
153
154 // actual players from game servers we run and trust
155 mysql_query(connection, "CREATE TABLE IF NOT EXISTS OW_HUB_Players (\
156 user_ip varchar(40) collate utf8_bin NOT NULL default '', \
157 user_guid varchar(33) collate utf8_bin NOT NULL default '', \
158 username varchar(255) collate utf8_bin NOT NULL default '', \
159 server int(11) unsigned NOT NULL default '0', \
160 server_port int(4) unsigned NOT NULL default '0', \
161 first TIMESTAMP NOT NULL, \
162 last TIMESTAMP NOT NULL, \
163 PRIMARY KEY (`user_ip`), \
164 UNIQUE KEY name (username), \
165 KEY server (server), \
166 KEY server_port (server_port))");
167
168
169 // gossip from other hubs we keep only for reference
170 mysql_query(connection, "CREATE TABLE IF NOT EXISTS OW_HUB_Gossips (\
171 user_ip varchar(40) collate utf8_bin NOT NULL default '', \
172 user_guid varchar(33) collate utf8_bin NOT NULL default '', \
173 username varchar(255) collate utf8_bin NOT NULL default '', \
174 server int(11) unsigned NOT NULL default '0', \
175 server_port int(4) unsigned NOT NULL default '0', \
176 origin VARCHAR(255) NOT NULL, \
177 count INTEGER(8) DEFAULT NULL, \
178 first TIMESTAMP NOT NULL, \
179 last TIMESTAMP NOT NULL, \
180 PRIMARY KEY (`user_ip`), \
181 UNIQUE KEY username (username), \
182 UNIQUE KEY user_guid (user_guid), \
183 KEY server (server), \
184 KEY server_port (server_port), \
185 KEY origin (origin))");
186
187 // actual packets from game servers we run and trust
188 mysql_query(connection, "CREATE TABLE IF NOT EXISTS OW_HUB_FailOver ( \
189 server int(11) unsigned NOT NULL default '0', \
190 server_port int(4) unsigned NOT NULL default '0', \
191 packet int(10) unsigned NOT NULL default '0', \
192 time TIMESTAMP NULL)");
193#endif
194 }
195
196 Com_Printf( "-----------------------------------\n" );
197}