· 10 years ago · Aug 31, 2016, 07:22 PM
1/**
2 * -----------------------------------------------------
3 * File calladmin_mysql.sp
4 * Authors Impact, Popoklopsi
5 * License GPLv3
6 * Web http://gugyclan.eu, http://popoklopsi.de
7 * -----------------------------------------------------
8 *
9 * CallAdmin
10 * Copyright (C) 2013 Impact, Popoklopsi
11 *
12 * This program is free software: you can redistribute it and/or modify
13 * it under the terms of the GNU General Public License as published by
14 * the Free Software Foundation, either version 3 of the License, or
15 * any later version.
16 *
17 * This program is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
21 *
22 * You should have received a copy of the GNU General Public License
23 * along with this program. If not, see <http://www.gnu.org/licenses/>
24 */
25
26#include <sourcemod>
27#include <autoexecconfig>
28#include "calladmin"
29
30#undef REQUIRE_PLUGIN
31#include <updater>
32#pragma semicolon 1
33#pragma newdecls required
34
35
36// Global Stuff
37ConVar g_hEntryPruning;
38char g_iEntryPruning;
39
40ConVar g_hTableName;
41char g_sTableName[32];
42
43ConVar g_hServerKey;
44char g_sServerKey[32];
45
46ConVar g_hOhphanedEntryPruning;
47char g_iOhphanedEntryPruning;
48
49ConVar g_hVersion;
50
51bool g_bAllLoaded;
52bool g_bDbInitTriggered;
53
54char g_iHostPort;
55char g_sServerName[64];
56char g_sHostIP[16];
57
58
59#define PRUNE_TRACKERS_TIME 3
60char g_iCurrentTrackers;
61
62
63
64// Dbstuff
65Database g_hDbHandle;
66
67
68#define SQL_DB_CONF "CallAdmin"
69
70
71
72// Updater
73#define UPDATER_URL "http://plugins.gugyclan.eu/calladmin/calladmin_mysql.txt"
74
75
76public Plugin myinfo =
77{
78 name = "CallAdmin: Mysql module",
79 author = "Impact, Popoklopsi",
80 description = "The mysqlmodule for CallAdmin",
81 version = CALLADMIN_VERSION,
82 url = "http://gugyclan.eu"
83}
84
85
86
87public void OnConfigsExecuted()
88{
89 if (!g_bDbInitTriggered)
90 {
91 // This convar is the only one which isn't hooked, we only fetch its content once before the connection to the database is made
92 g_hTableName.GetString(g_sTableName, sizeof(g_sTableName));
93
94 InitDB();
95 g_bDbInitTriggered = true;
96 }
97}
98
99
100
101
102
103public void OnPluginStart()
104{
105 AutoExecConfig_SetFile("plugin.calladmin_mysql");
106
107 g_hVersion = AutoExecConfig_CreateConVar("sm_calladmin_mysql_version", CALLADMIN_VERSION, "Plugin version", FCVAR_NOTIFY|FCVAR_DONTRECORD);
108 g_hTableName = AutoExecConfig_CreateConVar("sm_calladmin_table_name", "CallAdmin", "Name of the CallAdmin table", FCVAR_PROTECTED);
109 g_hServerKey = AutoExecConfig_CreateConVar("sm_calladmin_server_key", "", "Server key to identify this server (Max. 64 allowed!)", FCVAR_PROTECTED);
110 g_hEntryPruning = AutoExecConfig_CreateConVar("sm_calladmin_entrypruning", "25", "Entries older than given minutes will be deleted, 0 deactivates the feature", FCVAR_NONE, true, 0.0);
111 g_hOhphanedEntryPruning = AutoExecConfig_CreateConVar("sm_calladmin_entrypruning_ohphaned", "4320", "Entries older than given minutes will be recognized as orphaned and will be deleted globally (serverIP and serverPort won't be checked)", FCVAR_NONE, true, 0.0);
112
113
114 AutoExecConfig(true, "plugin.calladmin_mysql");
115 AutoExecConfig_CleanFile();
116
117
118 LoadTranslations("calladmin.phrases");
119
120
121 g_hVersion.SetString(CALLADMIN_VERSION, false, false);
122 HookConVarChange(g_hVersion, OnCvarChanged);
123
124 g_iEntryPruning = g_hEntryPruning.IntValue;
125 HookConVarChange(g_hEntryPruning, OnCvarChanged);
126
127 g_hServerKey.GetString(g_sServerKey, sizeof(g_sServerKey));
128 HookConVarChange(g_hServerKey, OnCvarChanged);
129
130 g_iOhphanedEntryPruning = g_hOhphanedEntryPruning.IntValue;
131 HookConVarChange(g_hOhphanedEntryPruning, OnCvarChanged);
132
133 CreateTimer(600.0, Timer_PruneEntries, _, TIMER_REPEAT);
134 CreateTimer(20.0, Timer_UpdateTrackersCount, _, TIMER_REPEAT);
135}
136
137
138
139
140void InitDB()
141{
142 // Fallback for default if possible
143 if (!SQL_CheckConfig(SQL_DB_CONF) && !SQL_CheckConfig("default"))
144 {
145 CallAdmin_LogMessage("Couldn't find database config");
146 SetFailState("Couldn't find database config");
147 }
148
149 Database.Connect(SQLT_ConnectCallback, SQL_CheckConfig(SQL_DB_CONF) ? SQL_DB_CONF : "default");
150}
151
152
153
154
155
156public void OnAllPluginsLoaded()
157{
158 if (!LibraryExists("calladmin"))
159 {
160 SetFailState("CallAdmin not found");
161 }
162
163 if (LibraryExists("updater"))
164 {
165 Updater_AddPlugin(UPDATER_URL);
166 }
167
168 g_iHostPort = CallAdmin_GetHostPort();
169 CallAdmin_GetHostIP(g_sHostIP, sizeof(g_sHostIP));
170 CallAdmin_GetHostName(g_sServerName, sizeof(g_sServerName));
171}
172
173
174
175
176public void OnLibraryAdded(const char[] name)
177{
178 if (StrEqual(name, "updater"))
179 {
180 Updater_AddPlugin(UPDATER_URL);
181 }
182}
183
184
185
186
187public Action Timer_PruneEntries(Handle timer)
188{
189 // Prune old entries if enabled
190 if (g_iEntryPruning > 0)
191 {
192 PruneDatabase();
193 }
194
195 return Plugin_Continue;
196}
197
198
199
200
201// Pseudo forward
202public void CallAdmin_OnRequestTrackersCountRefresh(int &trackers)
203{
204 trackers = g_iCurrentTrackers;
205}
206
207
208
209
210public void CallAdmin_OnServerDataChanged(ConVar convar, ServerData type, const char[] oldVal, const char[] newVal)
211{
212 if (type == ServerData_HostIP)
213 {
214 CallAdmin_GetHostIP(g_sHostIP, sizeof(g_sHostIP));
215 }
216 else if (type == ServerData_HostName)
217 {
218 CallAdmin_GetHostName(g_sServerName, sizeof(g_sServerName));
219 }
220 else if (type == ServerData_HostPort)
221 {
222 g_iHostPort = CallAdmin_GetHostPort();
223 }
224}
225
226
227
228
229void PruneDatabase()
230{
231 if (g_hDbHandle != null && g_bAllLoaded)
232 {
233 char query[1024];
234 char sHostIP[16];
235 int iHostPort = CallAdmin_GetHostPort();
236 CallAdmin_GetHostIP(sHostIP, sizeof(sHostIP));
237
238 // Prune main table (this server)
239 Format(query, sizeof(query), "DELETE FROM `%s` WHERE serverIP = '%s' AND serverPort = %d AND TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(reportedAt), NOW()) > %d", g_sTableName, sHostIP, iHostPort, g_iEntryPruning);
240 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
241
242
243 // Prune trackers table (global)
244 Format(query, sizeof(query), "DELETE FROM `%s_Trackers` WHERE TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(lastView), NOW()) >= %d", g_sTableName, PRUNE_TRACKERS_TIME);
245 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
246
247
248 // Prune ohphaned entries (global)
249 Format(query, sizeof(query), "DELETE FROM `%s` WHERE TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(reportedAt), NOW()) > %d", g_sTableName, g_iOhphanedEntryPruning);
250 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
251 }
252}
253
254
255
256
257void UpdateServerData()
258{
259 if (g_hDbHandle != null && g_bAllLoaded)
260 {
261 char query[1024];
262
263 char sHostName[(sizeof(g_sServerName) + 1) * 2];
264 g_hDbHandle.Escape(g_sServerName, sHostName, sizeof(sHostName));
265
266 // Update the servername
267 Format(query, sizeof(query), "UPDATE IGNORE `%s` SET serverName = '%s', serverKey = '%s' WHERE serverIP = '%s' AND serverPort = %d", g_sTableName, sHostName, g_sServerKey, g_sHostIP, g_iHostPort);
268 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
269 }
270}
271
272
273
274
275public void OnCvarChanged(Handle cvar, const char[] oldValue, const char[] newValue)
276{
277 if (cvar == g_hEntryPruning)
278 {
279 g_iEntryPruning = g_hEntryPruning.IntValue;
280 }
281 else if (cvar == g_hServerKey)
282 {
283 g_hServerKey.GetString(g_sServerKey, sizeof(g_sServerKey));
284 }
285 else if (cvar == g_hOhphanedEntryPruning)
286 {
287 g_iOhphanedEntryPruning = g_hOhphanedEntryPruning.IntValue;
288 }
289 else if (cvar == g_hVersion)
290 {
291 g_hVersion.SetString(CALLADMIN_VERSION, false, false);
292 }
293
294 UpdateServerData();
295}
296
297
298
299
300public void CallAdmin_OnReportPost(int client, int target, const char[] reason)
301{
302 // We need all loaded
303 if (!g_bAllLoaded || g_hDbHandle == null)
304 {
305 return;
306 }
307
308
309 char clientNameBuf[MAX_NAME_LENGTH];
310 char clientName[(MAX_NAME_LENGTH + 1) * 2];
311 char clientAuth[21];
312
313 char targetNameBuf[MAX_NAME_LENGTH];
314 char targetName[(MAX_NAME_LENGTH + 1) * 2];
315 char targetAuth[21];
316
317 char sKey[(32 + 1) * 2];
318 g_hDbHandle.Escape(g_sServerKey, sKey, sizeof(sKey));
319
320 char sReason[(REASON_MAX_LENGTH + 1) * 2];
321 g_hDbHandle.Escape(reason, sReason, sizeof(sReason));
322
323
324 // Reporter wasn't a real client (initiated by a module)
325 if (client == REPORTER_CONSOLE)
326 {
327 strcopy(clientName, sizeof(clientName), "Server/Console");
328 strcopy(clientAuth, sizeof(clientAuth), "Server/Console");
329 }
330 else
331 {
332 GetClientName(client, clientNameBuf, sizeof(clientNameBuf));
333 g_hDbHandle.Escape(clientNameBuf, clientName, sizeof(clientName));
334 GetClientAuthId(client, AuthId_Steam2, clientAuth, sizeof(clientAuth));
335 }
336
337
338 GetClientName(target, targetNameBuf, sizeof(targetNameBuf));
339 g_hDbHandle.Escape(targetNameBuf, targetName, sizeof(targetName));
340 GetClientAuthId(target, AuthId_Steam2, targetAuth, sizeof(targetAuth));
341
342 char serverName[(sizeof(g_sServerName) + 1) * 2];
343 g_hDbHandle.Escape(g_sServerName, serverName, sizeof(serverName));
344
345 char query[1024];
346 Format(query, sizeof(query), "INSERT INTO `%s`\
347 (serverIP, serverPort, serverName, serverKey, targetName, targetID, targetReason, clientName, clientID, callHandled, reportedAt)\
348 VALUES\
349 ('%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', 0, UNIX_TIMESTAMP())",
350 g_sTableName, g_sHostIP, g_iHostPort, serverName, sKey, targetName, targetAuth, sReason, clientName, clientAuth);
351 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
352}
353
354
355
356
357public void SQLT_ConnectCallback(Database db, const char[] error, any data)
358{
359 if (db == null)
360 {
361 CallAdmin_LogMessage("ConErr: %s", error);
362 SetFailState("ConErr: %s", error);
363 }
364 else
365 {
366 g_hDbHandle = db;
367
368 // Set utf-8 encodings
369 g_hDbHandle.Query(SQLT_ErrorCheckCallback, "SET NAMES 'utf8'");
370
371 // Create main Table
372 char query[1024];
373 Format(query, sizeof(query), "CREATE TABLE IF NOT EXISTS `%s` (\
374 `callID` INT UNSIGNED NOT NULL AUTO_INCREMENT,\
375 `serverIP` VARCHAR(15) NOT NULL,\
376 `serverPort` SMALLINT UNSIGNED NOT NULL,\
377 `serverName` VARCHAR(64) NOT NULL,\
378 `serverKey` VARCHAR(32) NOT NULL,\
379 `targetName` VARCHAR(32) NOT NULL,\
380 `targetID` VARCHAR(21) NOT NULL,\
381 `targetReason` VARCHAR(%d) NOT NULL,\
382 `clientName` VARCHAR(32) NOT NULL,\
383 `clientID` VARCHAR(21) NOT NULL,\
384 `callHandled` TINYINT UNSIGNED NOT NULL,\
385 `reportedAt` INT UNSIGNED NOT NULL,\
386 INDEX `serverIP_serverPort` (`serverIP`, `serverPort`),\
387 INDEX `reportedAt` (`reportedAt`),\
388 INDEX `callHandled` (`callHandled`),\
389 INDEX `serverKey` (`serverKey`),\
390 PRIMARY KEY (`callID`))\
391 COLLATE='utf8_unicode_ci'\
392 ", g_sTableName, REASON_MAX_LENGTH);
393 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
394
395 // Create trackers Table
396 Format(query, sizeof(query), "CREATE TABLE IF NOT EXISTS `%s_Trackers` (\
397 `trackerIP` VARCHAR(15) NOT NULL,\
398 `trackerID` VARCHAR(21) NOT NULL,\
399 `lastView` INT UNSIGNED NOT NULL,\
400 `accessID` BIGINT UNSIGNED NOT NULL,\
401 INDEX `lastView` (`lastView`),\
402 UNIQUE INDEX `trackerIP` (`trackerIP`))\
403 COLLATE='utf8_unicode_ci'\
404 ", g_sTableName);
405 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
406
407 // Create Access Table
408 Format(query, sizeof(query), "CREATE TABLE IF NOT EXISTS `%s_Access` (\
409 `serverKey` VARCHAR(32) NOT NULL,\
410 `accessBit` BIGINT UNSIGNED NOT NULL,\
411 UNIQUE INDEX `serverKey` (`serverKey`))\
412 COLLATE='utf8_unicode_ci'\
413 ", g_sTableName);
414 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
415
416 // Create Version Table
417 Format(query, sizeof(query), "CREATE TABLE IF NOT EXISTS `%s_Settings` (\
418 `version` VARCHAR(12) NOT NULL)\
419 COLLATE='utf8_unicode_ci'\
420 ", g_sTableName);
421 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
422
423 // Get current version
424 Format(query, sizeof(query), "SELECT \
425 `version` \
426 FROM \
427 `%s_Settings` LIMIT 1", g_sTableName);
428 g_hDbHandle.Query(SQLT_CurrentVersion, query);
429 }
430}
431
432
433
434
435public void SQLT_ErrorCheckCallback(Database db, DBResultSet result, const char[] error, any data)
436{
437 if (result == null)
438 {
439 CallAdmin_LogMessage("QueryErr: %s", error);
440 SetFailState("QueryErr: %s", error);
441 }
442}
443
444
445
446
447public void SQLT_CurrentVersion(Database db, DBResultSet result, const char[] error, any data)
448{
449 char version[12];
450 char query[512];
451
452 if (result != null)
453 {
454 if (result.FetchRow())
455 {
456 result.FetchString(0, version, sizeof(version));
457
458 // Setup maybe new structure
459 ChangeDB(version);
460 }
461 else
462 {
463 // We have to check the real version
464 Format(query, sizeof(query), "SELECT \
465 `serverKey` \
466 FROM \
467 `%s` LIMIT 1", g_sTableName);
468
469 g_hDbHandle.Query(SQLT_GetRealVersion, query);
470
471 // Insert Version
472 Format(query, sizeof(query), "INSERT INTO `%s_Settings` \
473 (version) \
474 VALUES \
475 ('%s')", g_sTableName, CALLADMIN_VERSION);
476 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
477
478
479 return;
480 }
481 }
482 else
483 {
484 CallAdmin_LogMessage("VersionErr: %s", error);
485 SetFailState("VersionErr: %s", error);
486 }
487
488
489 // Update version
490 Format(query, sizeof(query), "UPDATE `%s_Settings` SET version = '%s'", g_sTableName, CALLADMIN_VERSION);
491 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
492}
493
494
495
496
497public void SQLT_GetRealVersion(Database db, DBResultSet result, const char[] error, any data)
498{
499 if (result == null)
500 {
501 // We have the old 0.1.2A
502 ChangeDB("0.1.2A");
503 }
504 else
505 {
506 // The version is the current version
507 OnAllLoaded();
508 }
509}
510
511
512
513
514void ChangeDB(const char[] version)
515{
516 char query[512];
517
518 // Check version < 0.1.3
519 if (!IsVersionNewerOrEqual(version, "0.1.3"))
520 {
521 // Update Table to current structure
522 Format(query, sizeof(query), "ALTER TABLE `%s` \
523 ADD COLUMN `serverKey` VARCHAR(32) NOT NULL AFTER `serverName`, \
524 CHANGE COLUMN `targetReason` `targetReason` VARCHAR(%d) NOT NULL AFTER `targetID`, \
525 ADD INDEX `serverKey` (`serverKey`) \
526 ", g_sTableName, REASON_MAX_LENGTH);
527 g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
528 }
529
530 // Now we are finished
531 OnAllLoaded();
532}
533
534
535
536
537
538bool IsVersionNewerOrEqual(const char[] currentVersion, const char[] versionCompare)
539{
540 // Check if currentVersion >= versionCompare
541 return (strcmp(versionCompare, currentVersion, false) <= 0);
542}
543
544
545
546
547public Action Timer_UpdateTrackersCount(Handle timer)
548{
549 // Get current trackers
550 GetCurrentTrackers();
551
552 return Plugin_Continue;
553}
554
555
556
557
558int GetCurrentTrackers()
559{
560 // We need all loaded
561 if (g_hDbHandle != null && g_bAllLoaded)
562 {
563 char query[1024];
564
565 char sKey[(32 + 1) * 2];
566 SQL_EscapeString(g_hDbHandle, g_sServerKey, sKey, sizeof(sKey));
567
568 // Get current trackers (last 2 minutes)
569 Format(query, sizeof(query), "SELECT \
570 COUNT(`trackerID`) as currentTrackers \
571 FROM \
572 `%s_Trackers` \
573 WHERE \
574 TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(lastView), NOW()) < 2 AND \
575 `accessID` & (SELECT `accessBit` FROM `%s_Access` WHERE `serverKey`='%s')", g_sTableName, g_sTableName, sKey);
576 g_hDbHandle.Query(SQLT_CurrentTrackersCallback, query);
577 }
578 else
579 {
580 // Set to zero
581 g_iCurrentTrackers = 0;
582 }
583}
584
585
586
587
588public void SQLT_CurrentTrackersCallback(Database db, DBResultSet result, const char[] error, any data)
589{
590 if (result == null)
591 {
592 CallAdmin_LogMessage("CurrentTrackersErr: %s", error);
593 SetFailState("CurrentTrackersErr: %s", error);
594 }
595 else
596 {
597 if (result.FetchRow())
598 {
599 g_iCurrentTrackers = result.FetchInt(0);
600 }
601 }
602}
603
604
605
606
607void OnAllLoaded()
608{
609 g_bAllLoaded = true;
610
611
612 // Prune old entries if enabled
613 if (g_iEntryPruning > 0)
614 {
615 PruneDatabase();
616 }
617
618 // Get Current trackers
619 GetCurrentTrackers();
620
621 // Update Serverdata
622 UpdateServerData();
623}