· 7 years ago · Sep 09, 2018, 08:26 PM
1/*
2* MYSQLX
3*/
4
5#define TOTAL_TABLES 7
6
7new const szTables[TOTAL_TABLES][] =
8{
9 "CREATE TABLE IF NOT EXISTS `wc3_player` ( `player_id` int(8) unsigned NOT NULL auto_increment, `player_steamid` varchar(25) NOT NULL default '', `player_ip` varchar(20) NOT NULL default '', `player_name` varchar(35) NOT NULL default '', `time` timestamp(14) NOT NULL, PRIMARY KEY (`player_id`), KEY `player_name` (`player_name`), KEY `player_ip` (`player_ip`), KEY `player_steamid` (`player_steamid`) ) TYPE=MyISAM;",
10 "CREATE TABLE IF NOT EXISTS `wc3_player_extra` ( `player_id` INT( 8 ) UNSIGNED NOT NULL , `player_steamid` VARCHAR( 25 ) NOT NULL , `player_ip` VARCHAR( 20 ) NOT NULL , `player_name` VARCHAR( 35 ) NOT NULL , PRIMARY KEY ( `player_id` )) TYPE=MyISAM ;",
11 "CREATE TABLE IF NOT EXISTS `wc3_player_race` ( `player_id` int(8) unsigned NOT NULL default '0', `race_id` tinyint(4) unsigned NOT NULL default '0', `race_xp` int(8) default NULL, PRIMARY KEY (`player_id`,`race_id`) ) TYPE=MyISAM;",
12 "CREATE TABLE IF NOT EXISTS `wc3_player_skill` ( `player_id` int(8) unsigned NOT NULL default '0', `skill_id` tinyint(4) unsigned NOT NULL default '0', `skill_level` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`player_id`,`skill_id`) ) TYPE=MyISAM;",
13 "CREATE TABLE IF NOT EXISTS `wc3_web_race` ( `race_id` tinyint(4) unsigned NOT NULL default '0', `race_lang` char(2) NOT NULL default '', `race_name` varchar(100) default NULL, `race_description` text NOT NULL, PRIMARY KEY (`race_id`,`race_lang`) ) TYPE=MyISAM;",
14 "CREATE TABLE IF NOT EXISTS `wc3_web_skill` ( `skill_id` tinyint(4) unsigned NOT NULL default '0', `skill_lang` char(2) NOT NULL default '', `skill_name
15` varchar(100) default NULL, `skill_description` text NOT NULL, `skill_type` tinyint(4) unsigned NOT NULL default '0', `skill_owner` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`skill_id`,`skill_lang`) ) TYPE=MyISAM;",
16 "CREATE TABLE IF NOT EXISTS `wc3_config` ( `config_id` varchar(50) NOT NULL, `config_value` varchar(255) NOT NULL, PRIMARY KEY (`config_id`) ) TYPE=MyISAM;"
17};
18
19// Initiate the connection to the MySQL database
20MYSQLX_Init()
21{
22 // Determine the database information
23 new szHost[64], szUser[32], szPass[32], szDB[128], szError[256], iErrNum;
24
25 get_pcvar_string( CVAR_wc3_sql_dbhost , szHost , 63 );
26 get_pcvar_string( CVAR_wc3_sql_dbuser , szUser , 31 );
27 get_pcvar_string( CVAR_wc3_sql_dbpass , szPass , 31 );
28 get_pcvar_string( CVAR_wc3_sql_dbname , szDB , 127 );
29
30 // Set up the tuple that will be used for threading
31 g_DBTuple = SQL_MakeDbTuple( szHost, szUser, szPass, szDB );
32
33 // Attempt to connect
34 g_DBConn = SQL_Connect( g_DBTuple, iErrNum, szError, 255 );
35
36 if ( !g_DBConn )
37 {
38 WC3_Log( true, "[MYSQLX] Database Connection Failed: [%d] %s", iErrNum, szError );
39
40 return;
41 }
42
43 server_print( "[WAR3FT] MySQL X database connection successful" );
44
45 bDBAvailable = true;
46
47 // Create tables!
48 MYSQLX_CreateTables();
49
50 // Do we need to update the skills/races ?
51 MYSQLX_UpdateWebTable();
52
53 // Do we need to run a conversion?
54 MYSQLX_Convert();
55}
56
57// Verifies that the database connection is ok
58bool:MYSQLX_Connection_Available()
59{
60 if ( !bDBAvailable || !g_DBConn )
61 {
62 return false;
63 }
64
65 return true;
66}
67
68// Create all of our tables!
69MYSQLX_CreateTables()
70{
71 new Handle:query;
72
73 // Create the default tables if we need to
74 for ( new i = 0; i < TOTAL_TABLES; i++ )
75 {
76 query = SQL_PrepareQuery( g_DBConn, szTables[i] );
77
78 if ( !SQL_Execute( query ) )
79 {
80 MYSQLX_Error( query, szTables[i], 1 );
81
82 return;
83 }
84
85 SQL_FreeHandle( query );
86 }
87}
88
89MYSQLX_FetchUniqueID( id )
90{
91 // Make sure our connection is working
92 if ( !MYSQLX_Connection_Available() )
93 {
94 return;
95 }
96
97 // Remember how we got this ID
98 g_iDBPlayerSavedBy[id] = get_pcvar_num( CVAR_wc3_save_by );
99
100 new szKey[66], szKeyName[32];
101 DB_GetKey( id, szKey, 65 );
102 DB_GetKeyName( szKeyName, 31 );
103
104 new szQuery[512];
105 format( szQuery, 511, "SELECT `player_id` FROM `wc3_player` WHERE `%s` = '%s';", szKeyName, szKey );
106 new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
107
108 if ( !SQL_Execute( query ) )
109 {
110 MYSQLX_Error( query, szQuery, 2 );
111
112 return;
113 }
114
115 // If no rows we need to insert!
116 if ( SQL_NumResults( query ) == 0 )
117 {
118 // Free the last handle!
119 SQL_FreeHandle( query );
120
121 // Insert this player!
122 new szQuery[512];
123 format( szQuery, 511, "INSERT INTO `wc3_player` ( `player_id` , `%s` , `time` ) VALUES ( NULL , '%s', NOW() );", szKeyName, szKey );
124 new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
125
126 if ( !SQL_Execute( query ) )
127 {
128 MYSQLX_Error( query, szQuery, 3 );
129
130 return;
131 }
132
133 g_iDBPlayerUniqueID[id] = SQL_GetInsertId( query );
134
135 // Since we have the ID - lets insert extra data here...
136 // Basically insert whatever data we don't have yet on this player in the extra table
137 // (this will only be used for the webpage)
138 new szName[70], szSteamID[30], szIP[20];
139 get_user_name( id, szName, 69 );
140 DB_FormatString( szName, 69 );
141 get_user_ip( id, szIP, 19, 1 );
142 get_user_authid( id, szSteamID, 29 );
143
144 format( szQuery, 511, "INSERT INTO `wc3_player_extra` ( `player_id` , `player_steamid` , `player_ip` , `player_name` ) VALUES ( '%d', '%s', '%s', '%s' );", g_iDBPlayerUniqueID[id], szSteamID, szIP, szName );
145 query = SQL_PrepareQuery( g_DBConn, szQuery );
146
147 if ( !SQL_Execute( query ) )
148 {
149 MYSQLX_Error( query, szQuery, 20 );
150
151 return;
152 }
153 }
154
155 // They have been here before - store their ID
156 else
157 {
158 g_iDBPlayerUniqueID[id] = SQL_ReadResult( query, 0 );
159 }
160
161 // Free the last handle!
162 SQL_FreeHandle( query );
163}
164
165MYSQLX_Save( id )
166{
167 // Make sure our connection is working
168 if ( !MYSQLX_Connection_Available() )
169 {
170 return;
171 }
172
173 new iUniqueID = DB_GetUniqueID( id );
174
175 // Error checking when saving
176 if ( iUniqueID <= 0 )
177 {
178 new szName[128];
179 get_user_name( id, szName, 127 );
180
181 WC3_Log( true, "Unable to save XP for user '%s', unique ID: %d", szName, iUniqueID );
182
183 return;
184 }
185
186 // Save the user's XP!
187 new szQuery[512];
188 format( szQuery, 511, "REPLACE INTO `wc3_player_race` ( `player_id` , `race_id` , `race_xp` ) VALUES ( '%d', '%d', '%d');", iUniqueID, p_data[id][P_RACE], p_data[id][P_XP] );
189 new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
190
191 if ( !SQL_Execute( query ) )
192 {
193 client_print( id, print_chat, "%s Error, unable to save your XP, please contact a server administrator", g_MODclient );
194
195 MYSQLX_Error( query, szQuery, 4 );
196
197 return;
198 }
199
200 static iCurrentLevel;
201
202 // Only save skill levels if the user does NOT play chameleon
203 if ( p_data[id][P_RACE] != RACE_CHAMELEON )
204 {
205 // Now we need to save the skill levels!
206 for ( new iSkillID = 0; iSkillID < MAX_SKILLS; iSkillID++ )
207 {
208 if ( g_SkillType[iSkillID] != SKILL_TYPE_PASSIVE )
209 {
210 iCurrentLevel = SM_GetSkillLevel( id, iSkillID, 14 );
211
212 // Then we need to save this!
213 if ( iCurrentLevel >= 0 && g_iDBPlayerSkillStore[id][iSkillID] != iCurrentLevel )
214 {
215 g_iDBPlayerSkillStore[id][iSkillID] = iCurrentLevel;
216 format( szQuery, 511, "REPLACE INTO `wc3_player_skill` ( `player_id` , `skill_id` , `skill_level` ) VALUES ( '%d', '%d', '%d' );", iUniqueID, iSkillID, iCurrentLevel );
217 query = SQL_PrepareQuery( g_DBConn, szQuery );
218
219 if ( !SQL_Execute( query ) )
220 {
221 client_print( id, print_chat, "%s Error, unable to save your XP, please contact a server administrator", g_MODclient );
222
223 MYSQLX_Error( query, szQuery, 5 );
224
225 return;
226 }
227 }
228 }
229 }
230 }
231
232 return;
233}
234
235MYSQLX_Save_T( id )
236{
237 // Make sure our connection is working
238 if ( !MYSQLX_Connection_Available() )
239 {
240 return;
241 }
242
243 new iUniqueID = DB_GetUniqueID( id );
244
245 // Error checking when saving
246 if ( iUniqueID <= 0 )
247 {
248 new szName[128];
249 get_user_name( id, szName, 127 );
250
251 WC3_Log( true, "Unable to save XP for user '%s', unique ID: %d", szName, iUniqueID );
252
253 return;
254 }
255
256 // Save the user's XP!
257 new szQuery[512];
258 format( szQuery, 511, "REPLACE INTO `wc3_player_race` ( `player_id` , `race_id` , `race_xp` ) VALUES ( '%d', '%d', '%d');", iUniqueID, p_data[id][P_RACE], p_data[id][P_XP] );
259 SQL_ThreadQuery( g_DBTuple, "_MYSQLX_Save_T", szQuery );
260
261 static iCurrentLevel;
262
263 // Only save skill levels if the user does NOT play chameleon
264 if ( p_data[id][P_RACE] != RACE_CHAMELEON )
265 {
266 // Now we need to save the skill levels!
267 for ( new iSkillID = 0; iSkillID < MAX_SKILLS; iSkillID++ )
268 {
269 if ( g_SkillType[iSkillID] != SKILL_TYPE_PASSIVE )
270 {
271 iCurrentLevel = SM_GetSkillLevel( id, iSkillID, 15 );
272
273 // Then we need to save this!
274 if ( iCurrentLevel >= 0 && g_iDBPlayerSkillStore[id][iSkillID] != iCurrentLevel )
275 {
276 g_iDBPlayerSkillStore[id][iSkillID] = iCurrentLevel;
277 format( szQuery, 511, "REPLACE INTO `wc3_player_skill` ( `player_id` , `skill_id` , `skill_level` ) VALUES ( '%d', '%d', '%d' );", iUniqueID, iSkillID, iCurrentLevel );
278 SQL_ThreadQuery( g_DBTuple, "_MYSQLX_Save_T", szQuery );
279 }
280 }
281 }
282 }
283
284 return;
285}
286
287public _MYSQLX_Save_T( failstate, Handle:query, error[], errnum, data[], size )
288{
289
290 // Error during the query
291 if ( failstate )
292 {
293 new szQuery[256];
294 SQL_GetQueryString( query, szQuery, 255 );
295
296 MYSQLX_ThreadError( query, szQuery, error, errnum, failstate, 1 );
297 }
298}
299
300MYSQLX_GetAllXP( id )
301{
302 // Make sure our connection is working
303 if ( !MYSQLX_Connection_Available() )
304 {
305 return;
306 }
307
308 new iUniqueID = DB_GetUniqueID( id );
309
310 // Then we have a problem and cannot retreive the user's XP
311 if ( iUniqueID <= 0 )
312 {
313 client_print( id, print_chat, "%s Unable to retreive your XP from the database, please attempt to changerace later", g_MODclient );
314
315 WC3_Log( true, "[ERROR] Unable to retreive user's Unique ID" );
316
317 return;
318 }
319
320 new szQuery[256];
321 format(szQuery, 255, "SELECT `race_id`, `race_xp` FROM `wc3_player_race` WHERE ( `player_id` = '%d' );", iUniqueID );
322 new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
323
324 if ( !SQL_Execute( query ) )
325 {
326 client_print( id, print_chat, "%s Error, unable to retrieve XP, please contact a server administrator", g_MODclient );
327
328 MYSQLX_Error( query, szQuery, 6 );
329
330 return;
331 }
332
333 // Set last saved XP to 0
334 for ( new i = 0; i < MAX_RACES; i++ )
335 {
336 g_iDBPlayerXPInfoStore[id][i] = 0;
337 }
338
339 // Get the XP!
340 new iXP, iRace;
341
342 // Loop through all of the records to find the XP data
343 while ( SQL_MoreResults( query ) )
344 {
345 iRace = SQL_ReadResult( query, 0 );
346 iXP = SQL_ReadResult( query, 1 );
347
348 // Save the user's XP in an array
349 if ( iRace > 0 && iRace < MAX_RACES + 1 )
350 {
351 g_iDBPlayerXPInfoStore[id][iRace-1] = iXP;
352 }
353
354 SQL_NextRow( query );
355 }
356
357 // Free the handle
358 SQL_FreeHandle( query );
359
360 // Call the function that will display the "select a race" menu
361 WC3_ChangeRaceShowMenu( id, g_iDBPlayerXPInfoStore[id] );
362
363 return;
364}
365
366
367MYSQLX_SetDataForRace( id )
368{
369 // Make sure our connection is working
370 if ( !MYSQLX_Connection_Available() )
371 {
372 return;
373 }
374
375 new szQuery[256];
376 format( szQuery, 255, "SELECT `skill_id`, `skill_level` FROM `wc3_player_skill` WHERE `player_id` = '%d';", DB_GetUniqueID( id ) );
377 new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
378
379 if ( !SQL_Execute( query ) )
380 {
381 client_print( id, print_chat, "%s Error, unable to retrieve XP, please contact a server administrator", g_MODclient );
382
383 MYSQLX_Error( query, szQuery, 21 );
384
385 return;
386 }
387
388 // Set the user's XP!
389 p_data[id][P_XP] = g_iDBPlayerXPInfoStore[id][p_data[id][P_RACE]-1];
390
391 // Reset all skill data to 0!
392 for ( new iSkillID = 0; iSkillID < MAX_SKILLS; iSkillID++ )
393 {
394 if ( g_SkillType[iSkillID] != SKILL_TYPE_PASSIVE )
395 {
396 SM_SetSkillLevel( id, iSkillID, 0, 2 );
397 }
398 }
399
400 new iSkillID, iSkillLevel;
401 // While we have a result!
402 while ( SQL_MoreResults( query ) )
403 {
404 iSkillID = SQL_ReadResult( query, 0 );
405 iSkillLevel = SQL_ReadResult( query, 1 );
406 SM_SetSkillLevel( id, iSkillID, iSkillLevel, 3 );
407 g_iDBPlayerSkillStore[id][iSkillID] = iSkillLevel;
408
409 SQL_NextRow( query );
410 }
411
412 // Free the handle
413 SQL_FreeHandle( query );
414
415 // Set the race up
416 WC3_SetRaceUp( id );
417
418 // This user's XP has been set + retrieved! We can save now
419 bDBXPRetrieved[id] = true;
420
421
422 return;
423}
424
425MYSQLX_Close()
426{
427 if ( g_DBTuple )
428 {
429 SQL_FreeHandle( g_DBTuple );
430 }
431
432 if ( g_DBConn )
433 {
434 SQL_FreeHandle( g_DBConn );
435 }
436
437 bDBAvailable = false;
438}
439
440// The id should be a unique number, so we know what function called it (useful for debugging)
441MYSQLX_Error( Handle:query, szQuery[], id )
442{
443 new szError[256];
444 new iErrNum = SQL_QueryError( query, szError, 255 );
445
446 WC3_Log( true, "[MYSQLX] Error in querying database, location: %d", id );
447 WC3_Log( true, "[MYSQLX] Message: %s (%d)", szError, iErrNum );
448 WC3_Log( true, "[MYSQLX] Query statement: %s ", szQuery );
449
450 // Free the handle
451 SQL_FreeHandle( query );
452
453 // MySQL server has gone away (2006)
454 if ( iErrNum == 2006 )
455 {
456 WC3_Log( true, "[MYSQLX] Attempting to re-establish connection to MySQL server" );
457 // Close the connection
458 MYSQLX_Close();
459
460 // Re-open the connection
461 MYSQLX_Init();
462 }
463}
464
465MYSQLX_ThreadError( Handle:query, szQuery[], szError[], iErrNum, failstate, id )
466{
467 WC3_Log( true, "[MYSQLX] Threaded query error, location: %d", id );
468 WC3_Log( true, "[MYSQLX] Message: %s (%d)", szError, iErrNum );
469 WC3_Log( true, "[MYSQLX] Query statement: %s ", szQuery );
470
471 // Connection failed
472 if ( failstate == TQUERY_CONNECT_FAILED )
473 {
474 WC3_Log( true, "[MYSQLX] Fail state: Connection Failed" );
475 }
476
477 // Query failed
478 else if ( failstate == TQUERY_QUERY_FAILED )
479 {
480 WC3_Log( true, "[MYSQLX] Fail state: Query Failed" );
481 }
482
483 // Free the handle
484 SQL_FreeHandle( query );
485}
486
487MYSQLX_UpdateTimestamp( id )
488{
489 // Make sure our connection is working
490 if ( !MYSQLX_Connection_Available() )
491 {
492 return;
493 }
494
495 new szKey[66];
496 DB_GetKey( id, szKey, 65 );
497
498 new szQuery[256];
499 format( szQuery, 255, "UPDATE `wc3_player` SET time = NOW() WHERE ( `player_id` = '%d' );", DB_GetUniqueID( id ) );
500
501 SQL_ThreadQuery( g_DBTuple, "_MYSQLX_UpdateTimestamp", szQuery );
502}
503
504public _MYSQLX_UpdateTimestamp( failstate, Handle:query, error[], errnum, data[], size )
505{
506 // Error during the query
507 if ( failstate )
508 {
509 new szQuery[256];
510 SQL_GetQueryString( query, szQuery, 255 );
511
512 MYSQLX_ThreadError( query, szQuery, error, errnum, failstate, 4 );
513 }
514
515 // Query successful, we can do stuff!
516 else
517 {
518 // Free the handle
519 SQL_FreeHandle( query );
520 }
521
522 return;
523}
524
525MYSQLX_UpdateWebTable()
526{
527
528 // Make sure our connection is working
529 if ( !MYSQLX_Connection_Available() )
530 {
531 return;
532 }
533
534
535 new szQuery[1024], Handle:query;
536
537 // No matter what always update the XP levels
538 for ( new iLevel = 0; iLevel <= MAX_LEVELS; iLevel++ )
539 {
540 formatex( szQuery, 255, "REPLACE INTO `wc3_config` ( `config_id`, `config_value` ) VALUES ( 'level%d_xp', '%d' );", iLevel, XP_GetByLevel( iLevel ) );
541 query = SQL_PrepareQuery( g_DBConn, szQuery );
542
543 if ( !SQL_Execute( query ) )
544 {
545 MYSQLX_Error( query, szQuery, 7 );
546
547 return;
548 }
549 }
550
551
552
553 // Check to see if we even need an update!
554 formatex ( szQuery, 255, "SELECT `config_value` FROM `wc3_config` WHERE `config_id` = 'version' AND `config_value` = '%s';", WC3VERSION );
555 query = SQL_PrepareQuery( g_DBConn, szQuery );
556
557 if ( !SQL_Execute( query ) )
558 {
559 MYSQLX_Error( query, szQuery, 22 );
560
561 return;
562 }
563
564 // Then we don't need to update because we already have!
565 if ( SQL_NumResults( query ) == 1 )
566 {
567 SQL_FreeHandle( query );
568
569 return;
570 }
571
572 // We haven't update yet! Lets store the version
573 else
574 {
575 // Free the last handle
576 SQL_FreeHandle( query );
577
578 // Insert current version!
579 formatex ( szQuery, 1023, "REPLACE INTO `wc3_config` ( `config_id` , `config_value` ) VALUES ( 'version', '%s' );", WC3VERSION );
580 query = SQL_PrepareQuery( g_DBConn, szQuery );
581
582 if ( !SQL_Execute( query ) )
583 {
584 MYSQLX_Error( query, szQuery, 8 );
585
586 return;
587 }
588
589 // Now lets add the language information to the DB!
590 new iTotalLanguages = get_langsnum();
591 new lang[3], iLang, i;
592 new szName[64], szDescription[512];
593
594 // Loop through all languages
595 for ( iLang = 0; iLang < iTotalLanguages; iLang++ )
596 {
597 get_lang ( iLang, lang );
598
599 // We have a valid language
600 if ( lang_exists( lang ) )
601 {
602
603 // Check all races
604 for ( i = 1; i <= MAX_RACES; i++ )
605 {
606 lang_GetRaceName ( i, iLang, szName, 63 );
607
608 formatex( szQuery, 1023, "REPLACE INTO `wc3_web_race` ( `race_id` , `race_lang` , `race_name`, `race_description` ) VALUES ( '%d', '%s', '%s', '' );", i, lang, szName );
609
610 query = SQL_PrepareQuery( g_DBConn, szQuery );
611
612 if ( !SQL_Execute( query ) )
613 {
614 MYSQLX_Error( query, szQuery, 9 );
615
616 return;
617 }
618 }
619
620 // Check all skills
621 for ( i = 0; i < MAX_SKILLS; i++ )
622 {
623 LANG_GetSkillName ( i, iLang, szName, 63, 200 );
624 LANG_GetSkillInfo( i, iLang, szDescription, 255 ); // Intentionally only 255 - technically this could be all '
625 DB_FormatString( szDescription, 511 );
626
627 formatex( szQuery, 1023, "REPLACE INTO `wc3_web_skill` ( `skill_id` , `skill_lang` , `skill_name`, `skill_description`, `skill_type`, `skill_owner` ) VALUES ( '%d', '%s', '%s', '%s', '%d', '%d' );", i, lang, szName, szDescription, g_SkillType[i], g_SkillOwner[i] );
628
629 query = SQL_PrepareQuery( g_DBConn, szQuery );
630
631 if ( !SQL_Execute( query ) )
632 {
633 MYSQLX_Error( query, szQuery, 10 );
634
635 return;
636 }
637 }
638 }
639 }// End language loop
640 }
641}
642
643#define MYSQL_TOTAL_PRUNE_QUERY 2
644
645// Prune the MySQL database
646MYSQLX_Prune()
647{
648 // Make sure our connection is working
649 if ( !MYSQLX_Connection_Available() )
650 {
651 return;
652 }
653
654 new const szPruneQuery[MYSQL_TOTAL_PRUNE_QUERY][] =
655 {
656 "DELETE FROM wc3_player_race WHERE player_id IN ( SELECT `player_id` FROM `wc3_player` WHERE ( DATE_SUB(CURDATE(), INTERVAL %d DAY) > time ) )",
657 "DELETE FROM wc3_player_skill WHERE player_id IN ( SELECT `player_id` FROM `wc3_player` WHERE ( DATE_SUB(CURDATE(), INTERVAL %d DAY) > time ) );"
658 };
659
660
661 /*
662 CREATE TEMPORARY TABLE `wc3_expired` SELECT * FROM wc3_player WHERE ( DATE_SUB(CURDATE(), INTERVAL 10 DAY) > `time` );# Affected rows: 4179
663 DELETE `wc3_player_race` FROM `wc3_player_race` INNER JOIN wc3_expired ON wc3_player_race.player_id = wc3_expired.player_id;# MySQL returned an empty result set (i.e. zero rows).
664
665
666
667CREATE TEMPORARY TABLE tmptable
668SELECT A.* FROM table1 AS A, table1 AS B
669WHERE A.username LIKE '%2'
670AND A.ID = B.ID
671AND A.username <> B.username;
672
673DELETE table1 FROM table1
674INNER JOIN tmptable
675ON table1.username = tmptable.username;
676*/
677
678 //SELECT `wc3_player`.`player_id` FROM `wc3_player` WHERE ( DATE_SUB(CURDATE(), INTERVAL 14 DAY) > `wc3_player`.`time` )
679
680 new szQuery[256];
681
682 // Need to run all queries
683 for ( new i = 0; i < MYSQL_TOTAL_PRUNE_QUERY; i++ )
684 {
685 formatex( szQuery, 255, szPruneQuery[i], get_pcvar_num( CVAR_wc3_days_before_delete ) );
686
687 new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
688
689 WC3_Log( true, szQuery );
690
691 if ( !SQL_Execute( query ) )
692 {
693 MYSQLX_Error( query, szQuery, 11 );
694
695 return;
696 }
697 }
698}
699
700#define MYSQL_TOTAL_CONVERSION_QUERY 2
701MYSQLX_Convert()
702{
703 // Make sure our connection is working
704 if ( !MYSQLX_Connection_Available() )
705 {
706 return;
707 }
708
709 new const szConversionQuery[MYSQL_TOTAL_CONVERSION_QUERY][] =
710 {
711 "INSERT INTO wc3_player select '', playerid, playerip, playername, time FROM `war3users` GROUP BY playerid;",
712 "INSERT INTO wc3_player_race select wc3_player.player_id, war3users.race, war3users.xp FROM `wc3_player`, `war3users` WHERE wc3_player.player_steamid=war3users.playerid;"
713 };
714
715 new szQuery[256], Handle:query;
716
717
718 // Check to see if the table even exists!
719 new szDB[128];
720 get_pcvar_string( CVAR_wc3_sql_dbname , szDB , 127 );
721 formatex ( szQuery, 255, "SELECT count(*) FROM information_schema.tables WHERE table_schema = '%s' AND table_name = 'war3users';", szDB );
722 query = SQL_PrepareQuery( g_DBConn, szQuery );
723
724 if ( !SQL_Execute( query ) )
725 {
726 MYSQLX_Error( query, szQuery, 12 );
727
728 return;
729 }
730
731 // Weird this shouldn't occur as we should always have a count!
732 if ( SQL_NumResults( query ) <= 0 )
733 {
734 return;
735 }
736
737 // Find out if the count is 1, if it's not then we need to return
738 if ( SQL_ReadResult( query, 0 ) != 1 )
739 {
740 return;
741 }
742
743 // Now we need to check to see if the conversion has already been ran
744 formatex ( szQuery, 255, "SELECT `config_value` FROM `wc3_config` WHERE `config_id` = 'sql_conversion' AND `config_value` = '1';" );
745 query = SQL_PrepareQuery( g_DBConn, szQuery );
746
747 if ( !SQL_Execute( query ) )
748 {
749 MYSQLX_Error( query, szQuery, 13 );
750
751 return;
752 }
753
754 // Then we don't need to run the conversion because we already have!
755 if ( SQL_NumResults( query ) == 1 )
756 {
757 SQL_FreeHandle( query );
758
759 return;
760 }
761
762 // We haven't ran the conversion yet - run it!
763 else
764 {
765 // Free the last handle
766 SQL_FreeHandle( query );
767
768 // Insert that we ran the conversion!
769 formatex ( szQuery, 255, "REPLACE INTO `wc3_config` ( `config_id` , `config_value` ) VALUES ( 'sql_conversion', '1' );" );
770 query = SQL_PrepareQuery( g_DBConn, szQuery );
771
772 if ( !SQL_Execute( query ) )
773 {
774 MYSQLX_Error( query, szQuery, 14 );
775
776 return;
777 }
778
779 // Need to run all the queries
780 for ( new i = 0; i < MYSQL_TOTAL_CONVERSION_QUERY; i++ )
781 {
782 new Handle:query = SQL_PrepareQuery( g_DBConn, szConversionQuery[i] );
783
784 if ( !SQL_Execute( query ) )
785 {
786 MYSQLX_Error( query, szConversionQuery[i], 15 );
787
788 return;
789 }
790 }
791
792 WC3_Log( true, "[MYSQLX] MySQL Conversion to 3.0 DB Format ran successfully" );
793 }
794}