· 9 years ago · May 05, 2017, 04:14 PM
1/*
2
3CREATE TABLE sf_player(
4id INT NOT NULL AUTO_INCREMENT,
5PRIMARY KEY( id ),
6steamid VARCHAR( 30 ),
7name VARCHAR( 255 ),
8flags VARCHAR( 30 ),
9title VARCHAR( 50 ),
10lastip VARCHAR( 30 ),
11lastlogin DATETIME,
12lastname VARCHAR( 100 ) );
13
14CREATE TABLE sf_bans(
15id INT NOT NULL AUTO_INCREMENT,
16PRIMARY KEY( id ),
17steamid VARCHAR( 30 ),
18name VARCHAR( 255 ),
19lastip VARCHAR( 30 ),
20adname VARCHAR( 255 ),
21adsteamid VARCHAR( 50 ),
22adip VARCHAR( 30 ),
23reason VARCHAR( 255 ),
24bandate VARCHAR( 50 ),
25time VARCHAR( 50 ),
26ostime VARCHAR( 50 ) );
27
28CREATE TABLE sf_logs(
29id INT NOT NULL AUTO_INCREMENT,
30PRIMARY KEY( id ),
31steamid VARCHAR( 30 ),
32type INTEGER,
33name VARCHAR( 255 ),
34command VARCHAR( 255 ),
35arguments VARCHAR( 255 ),
36date DATETIME );
37
38CREATE TABLE sf_time(
39id INT NOT NULL AUTO_INCREMENT,
40PRIMARY KEY( id ),
41steamid VARCHAR( 30 ),
42name VARCHAR( 255 ),
43time TIME );
44
45"type" field
461 - command log
472 - bans log
483 - debug log
49
50*/
51/*
52
53 SparkFlare
54 MySQL.
55
56 Creates MySQL functions and such.
57
58*/
59
60// Means MySQL isn't loaded.
61SF.MySQL.Load = false;
62
63/*
64function SF.MySQL.ImportData()
65
66 sql.Query( "DROP TABLE db_details;" );
67 sql.Query( "CREATE TABLE IF NOT EXISTS db_details( 'id' INTEGER NOT NULL, 'addr' TEXT NOT NULL, 'data' TEXT NOT NULL, 'user' TEXT NOT NULL, 'pass' TEXT NOT NULL, PRIMARY KEY( 'id' ) );" );
68
69 if ( !sql.TableExists( "db_details" ) ) then
70
71 SF.Utility.Debug( "Table doesn't exist!" );
72 return;
73
74 end
75
76 SF.Utility.Debug( "Created table." );
77
78 //sql.Query( "INSERT INTO db_details ( `id`, `addr`, `data`, `user`, `pass` ) VALUES ( '1', 'hostname', 'database', 'username', 'password' );" );
79
80 SF.Utility.Debug( "Inserted details." );
81
82end
83SF.MySQL.ImportData();*/
84
85function SF.MySQL.Loadup()
86
87 // Try to select the data.
88 local Addr = sql.QueryValue( "SELECT addr FROM db_details WHERE id = 1;" );
89 local Data = sql.QueryValue( "SELECT data FROM db_details WHERE id = 1;" );
90 local User = sql.QueryValue( "SELECT user FROM db_details WHERE id = 1;" );
91 local Pass = sql.QueryValue( "SELECT pass FROM db_details WHERE id = 1;" );
92
93 // Check the data.
94 if ( !Data || !User || !Pass || !Addr ) then
95
96 SF.Utility.Debug( "Warning! Corrupt MySQL data!", "1" );
97 return false;
98
99 end
100
101 // Set the address, data, user and password.
102 SF.MySQL.Addr = Addr;
103 SF.MySQL.Data = Data;
104 SF.MySQL.User = User;
105 SF.MySQL.Pass = Pass;
106
107 // Set the mysql to loaded.
108 SF.MySQL.Load = true;
109
110 return true;
111
112end
113
114// Connect to SQL.
115function SF.MySQL.Connect()
116
117 // Check that we're loaded.
118 if ( !SF.MySQL.Load ) then
119
120 SF.Utility.Debug( "MySQL isn't loaded. Loading." );
121
122 // Attempt to load the data.
123 local Load = SF.MySQL.Loadup();
124 if ( !Load ) then
125
126 SF.Utility.Debug( "Couldn't load MySQL.", "1" );
127 return;
128
129 end
130
131 end
132
133 // Debug these details.
134 //SF.Utility.Debug( "ADDR: " .. SF.MySQL.Addr .. " || USER: " .. SF.MySQL.User .. " || PASS: " .. SF.MySQL.Pass .. " || DATA: " .. SF.MySQL.Data .. "!" );
135
136 // Connect.
137 SF.Connection, SF.Error = mysql.connect( SF.MySQL.Addr, SF.MySQL.User, SF.MySQL.Pass, SF.MySQL.Data );
138
139 // Check that we're connected.
140 if ( SF.Connection == 0 ) then
141
142 // Debug it erroring.
143 SF.Utility.Debug( "64: MYSQL ERROR: " .. SF.Error, "1" );
144 return;
145
146 end
147
148 // Debug loading correctly.
149 SF.Utility.Debug( "MySQL has connected successfully!" );
150
151end
152
153// Disconnect from MySQL.
154function SF.MySQL.Disconnect()
155
156 // IF we're connected, disconnect.
157 if ( SF.Connection ) then
158
159 SF.Disconnect, Error = mysql.disconnect( SF.Connection );
160
161 // Check that disconnection was a success.
162 if ( !SF.Disconnect ) then
163
164 // If it wasn't then debug it.
165 SF.Utility.Debug( "87: MYSQL ERROR: " .. Error, "1" );
166
167 end
168
169 // Clear the connect function.
170 SF.Connection = nil;
171
172 end
173
174end
175
176// Refresh the MySQL connection.
177function SF.MySQL.Refresh()
178
179 // Disconnect.
180 SF.MySQL.Disconnect();
181
182 // Connect.
183 SF.MySQL.Connect();
184
185end
186
187// Connect.
188SF.MySQL.Connect();
189
190// Create a timer to refresh the connection every two minutes.
191timer.Create( "SF.MySQL.Refresh", 120, 0, SF.MySQL.Refresh );
192
193/*
194
195 Player MySQL.
196
197*/
198
199// New player!
200function SF.MySQL.Player.New( pl )
201
202 // Check that we're connected.
203 if ( SF.Connection ) then
204
205 // : 1000-01-01 00:00:00
206 local Date = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );
207
208 // Clear the values.
209 local New;
210 local Success;
211 local Error;
212
213 // Create the new player.
214 New, Success, Error = mysql.query( SF.Connection, "INSERT INTO sf_player ( `steamid`, `name`, `flags`, `title`, `lastip`, `lastlogin`, `lastname` ) VALUES( '" .. pl:SteamID() .. "', '" .. mysql.escape( SF.Connection, pl:Name() ) .. "', 'A', 'Newbie', '" .. pl:IPAddress() .. "', '" .. Date .. "', '" .. mysql.escape( SF.Connection, pl:Name() ) .. "' )" );
215
216 // Check that it was a success.
217 if ( !Success ) then
218
219 // IF it wasn't display failed and return.
220 SF.Utility.Debug( "73: MYSQL ERROR: " .. Error, "1" );
221 return;
222
223 end
224
225 if ( New ) then
226
227 // Create the player (debug it, at least).
228 SF.Utility.Debug( "Created player: " .. pl:SteamID() .. "!" );
229
230 end
231
232 // Load the player.
233 SF.MySQL.Player.Load( pl );
234
235 end
236
237end
238
239// Load a player.
240function SF.MySQL.Player.Load( pl )
241
242 // If we're not connected then connect; if we're still not connected then return.
243 if ( !SF.Connection ) then
244
245 // Refresh.
246 SF.MySQL.Refresh();
247
248 // Recheck connection.
249 if ( !SF.Connection ) then
250
251 // Return if can't connect.
252 return;
253
254 end
255
256 end
257
258 // Clear MyData, success, and error.
259 local MyData;
260 local Success;
261 local Error;
262
263 // Get the players data.
264 MyData, Success, Error = mysql.query( SF.Connection, "SELECT * FROM sf_player WHERE steamid = '" .. pl:SteamID() .. "'" );
265
266 // Check that it was a success.
267 if ( !Success ) then
268
269 // IF it wasn't display failed and return.
270 SF.Utility.Debug( "1: MYSQL ERROR: " .. Error, "1" );
271 return;
272
273 end
274
275 // Count the amount of tables in the data.
276 if ( table.Count( MyData ) > 0 ) then
277
278 // Set the date.
279 // YYYY:MM:DD HH:MM:SS
280 local Date = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );
281
282 // If there's already tables.
283 NewTime, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET lastlogin = '" .. Date .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );
284
285 // Check that it was a success.
286 if ( !Success ) then
287
288 // IF it wasn't display failed and return.
289 SF.Utility.Debug( "2: MYSQL ERROR: " .. Error, "1" );
290 return;
291
292 end
293
294 NewName, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET name = '" .. mysql.escape( SF.Connection, pl:Name() ) .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );
295
296 // Check that it was a success.
297 if ( !Success ) then
298
299 // IF it wasn't display failed and return.
300 SF.Utility.Debug( "3: MYSQL ERROR: " .. Error, "1" );
301 return;
302
303 end
304
305 NewAddr, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET lastip = '" .. pl:IPAddress() .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );
306
307 // Check that it was a success.
308 if ( !Success ) then
309
310 // IF it wasn't display failed and return.
311 SF.Utility.Debug( "4: MYSQL ERROR: " .. Error, "1" );
312 return;
313
314 end
315
316 // Check the existing name.
317 LastName, Success, Error = mysql.query( SF.Connection, "SELECT lastname FROM sf_player WHERE steamid = '" .. pl:SteamID() .. "'" );
318
319 if ( !Success ) then
320
321 // Display error.
322 SF.Utility.Debug( "24924: MYQL ERROR: " .. Error, "1" );
323 return;
324
325 end
326
327 // If the player has a new name then update the last name.
328 if ( LastName ~= pl:Name() ) then
329
330 // Update the last name.
331 UpdtName, Success, Error = mysql.query( SF.Connection, "UPDATE sf_player SET lastname = '" .. mysql.escape( SF.Connection, pl:Name() ) .. "' WHERE steamid = '" .. pl:SteamID() .. "'" );
332
333 // Check for success.
334 if ( !Success ) then
335
336 // Error.
337 SF.Utility.Debug( "49239: MYSQL ERROR: " .. Error, "1" );
338 return;
339
340 end
341
342 end
343
344 /*
345
346 Check to see if the blasted user is banned!
347 Then check to see if his ban has run out.
348 Oh god, this is getting complicated.
349
350 */
351
352 // Get data fpr the user.
353 UserBan, Success, Error = mysql.query( SF.Connection, "SELECT * FROM sf_bans WHERE steamid = '" .. pl:SteamID() .. "'" );
354
355 // Search for mysql errors.
356 if ( !Success ) then
357
358 // Debug.
359 SF.Utility.Debug( "49244: MYSQL ERROR: " .. Error, "1" );
360 return;
361
362 end
363
364 // Make sure they actually have a ban section.
365 if ( table.Count( UserBan ) > 0 ) then
366
367 // Get the ostime they were banned.
368 local OSTime = UserBan[ 1 ][ 11 ];
369 // Time they were banned for.
370 local BanTime = UserBan[ 1 ][ 10 ];
371
372 // See if BanTime is empty.
373 if ( !BanTime || BanTime == "" || BanTime == nil ) then
374
375 // Set OSTime to 0 to symbolize that the user isn't banned because he has no ban time.
376 OSTime = 0;
377
378 elseif ( BanTime ~= 0 ) then
379
380 // Times it by 60 to convert the minutes to seconds.
381 BanTime = BanTime * 60;
382
383 end
384
385 // If it's 0 then it'll do nothing to it.
386
387 // Get the current os time.
388 local NowTime = os.time();
389
390 // Add the times.
391 local Added = OSTime + BanTime;
392 // So if it was 123456 when you were banned and you were banned for 50.. hmm, hang on a minute this needs to be done in seconds.
393 // Ok how do you make minutes into seconds, as ban time is minutes.
394 // Right.. so 60 seconds in a minute.
395 // So if you had 5 * 60 = 300. So 300 seconds. So we want to x the minutes by 60.
396
397 // Check that we're now ahead of that time.
398 if ( ( NowTime < Added && OSTime ~= 0 ) || ( BanTime == 0 ) ) then
399
400 // Get the reason for the ban.
401 local Reason = UserBan[ 1 ][ 8 ];
402
403 // Debug.
404 SF.Utility.Debug( "Prevented " .. pl:SteamID() .. " (" .. pl:Name() .. ") from playing (" .. Reason .. ").", "1" );
405
406 // Kick the mingebag.
407 RunConsoleCommand( "kickid", pl:UserID(), Reason );
408
409 return;
410
411 end
412
413 end
414
415 // I scrapped the below.
416 /*
417 // Check the ban field.
418 MyBanned, Success, Error = mysql.query( SF.Connection, "SELECT * FROM sf_bans WHERE steamid = '" .. pl:SteamID() .. "'" );
419
420 // Check that it was a success.
421 if ( !Success ) then
422
423 // IF it wasn't display failed and return.
424 SF.Utility.Debug( "5: MYSQL ERROR: " .. Error, "1" );
425 return;
426
427 end
428
429 //local Date = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );
430
431 // Get data from the query.
432 local BanDate = MyBanned[ 1 ][ 9 ];
433 local BanLength = MyBanned[ 1 ][ 10 ];
434
435 // Get the current date.
436 local CurrentDate = SF.Utility.Year .. os.date( "-%m-%d %H:%M:%S" );
437
438 // minge 1 is banned on 2010-02-15 13:37:15
439 // minge 1 is banned for 1440 minutes
440 // to work this out we need to.. do some complicated mathematics ^^
441 // ok first things first, let's convert these minutes to hours. 60 minutes in 1 hour > minutes /60 = hours.
442
443 local Hours = BanLength / 60; // Amount of hours the minge is banned for.
444
445 // Let's attempt to add these hours onto the existing hours.
446 local BannedHours = string.Explode( BanDate, " " )[ 2 ]; // This should get the HH:MM:SS bit.
447 BannedHours = string.Explode( BannedHours[, ":" )[ 1 ]; // This should get the HH bit.
448
449 local CalculateHours = BannedHours + Hours; // The minge will be unbanned in this many hours.
450
451 // Idea scrapped, would of been too inaccurate. We haz to do this through minutes.
452 // BanLength = minutes.
453
454 // BanDate = YYYY-MM-DD HH:MM:SS
455 local BannedMinutes = string.Explode( BanDate, " " )[ 2 ]; // HH:MM:SS
456 BannedMinutes = string.Explode( BannedMinutes, ":" )[ 2 ]; // MM
457
458 // Add the minutes on...
459 //local CalculateMinutes = BannedMinutes + BanLength;
460 // Wait; no. Let's make it all into seconds, then add those seconds, then from the final seconds work out hh:mm:ss on mm:dd.
461 // Gah, I'm confused. Let's go visit FacePunch.
462 */
463
464 /*
465
466 End the holy ban section.
467 Jesus.. that took some work (and FacePunch's help - kudos to all!)
468
469 */
470
471 // If we don't need to ban them then we can set up data for the user.
472 SF.Flags.Storage[ pl:SteamID() ] = MyData[ 1 ][ 4 ];
473 SF.Time.Start( pl );
474
475 // Display the users connection.
476 SF.Utility.ChatAll( pl:Name() .. " has connected. Last known name: " .. MyData[ 1 ][ 8 ] .. "." );
477
478 else
479
480 // Debug it.
481 SF.Utility.Debug( pl:Name() .. " doesn't exist! Writing new data." );
482 SF.MySQL.Player.New( pl );
483
484 // Return false.
485 return false;
486
487 end
488
489end
490
491// Create a timer to load the player on initial spawn.
492hook.Add( "PlayerInitialSpawn", "SF.MySQL.Player.Load", SF.MySQL.Player.Load );