pastebin - collaborative debugging tool
eathena.kpaste.net RSS


[eathena SQL] Database Cleaner
Posted by Anonymous on Fri 11th Feb 2011 00:15
raw | new post

  1. <?
  2. // your database server login
  3. $db_server = "localhost";
  4. $db_username = "";
  5. $db_password = "";
  6. $ragnarok_db = "ragnarok";
  7.  
  8. // comment this line after you made a backup of your database
  9. die("*WARNING* Script will alter your database !!WITHOUT!! confirmation. Please make backup of your game database! Then, comment this line in script and run again.");
  10.  
  11. // YOU SHOULD **NOT** MODIFY LINES BELOW
  12.  
  13. set_time_limit(0);
  14.  
  15. function get_time()
  16. {
  17.         $a = explode (' ',microtime());
  18.         return(double) $a[0] + $a[1];
  19. }
  20.  
  21. function tolog($str,$terminate=false) {
  22.         print(date("d.m.y H:i:s",time())." : $str\n");
  23.         if ($terminate) die();
  24. }
  25.  
  26. function sql($query) {
  27.         $q = @mysql_query($query);
  28.         if (!$q) tolog("DB error: ".mysql_error(),true);
  29.         return $q;
  30. }
  31.  
  32. function remove_invalid_data() {
  33.         $q = sql("DELETE FROM `char` WHERE account_id NOT IN (SELECT account_id FROM `login`);");
  34.         $count = mysql_affected_rows();
  35.         tolog("Removed {$count} characters without their accounts.");
  36.        
  37.         $q = sql("DELETE FROM `cart_inventory` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  38.         $count = mysql_affected_rows();
  39.         tolog("Removed {$count} items in carts which does not belong to anyone.");
  40.        
  41.         $q  = sql("DELETE FROM `friends` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  42.         $count = mysql_affected_rows();
  43.         $q1 = sql("DELETE FROM `friends` WHERE friend_id NOT IN (SELECT char_id FROM `char`);");
  44.         $count += mysql_affected_rows();
  45.         tolog("Removed {$count} invalid friends (non-existent character ID).");
  46.        
  47.         $q = sql("DELETE FROM `global_reg_value` WHERE char_id <>'' AND char_id NOT IN (SELECT char_id FROM `char`);");
  48.         $count = mysql_affected_rows();
  49.         tolog("Removed {$count} character variables which does not belong to anyone.");
  50.        
  51.         $q = sql("DELETE FROM `global_reg_value` WHERE account_id <> '' AND account_id NOT IN (SELECT account_id FROM `login`);");
  52.         $count = mysql_affected_rows();
  53.         tolog("Removed {$count} account variables which does not belong to anyone.");
  54.        
  55.         $q = sql("DELETE FROM `guild` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  56.         $count = mysql_affected_rows();
  57.         tolog("Removed {$count} guilds without valid guildmaster.");
  58.        
  59.         $q = sql("DELETE FROM `guild_alliance` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
  60.         $count = mysql_affected_rows();
  61.         tolog("Removed {$count} guild alliances (non-existent guild ID).");
  62.        
  63.         $q = sql("DELETE FROM `guild_expulsion` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
  64.         $count = mysql_affected_rows();
  65.         tolog("Removed {$count} guild expulsion references (non-existent guild ID).");
  66.        
  67.         $q = sql("DELETE FROM `guild_member` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
  68.         $count = mysql_affected_rows();
  69.         tolog("Removed {$count} guild member references (non-existent guild ID).");
  70.        
  71.         $q = sql("DELETE FROM `guild_member` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  72.         $count = mysql_affected_rows();
  73.         tolog("Removed {$count} guild member references (non-existent character ID).");
  74.        
  75.         $q = sql("DELETE FROM `guild_member` WHERE account_id NOT IN (SELECT account_id FROM `login`);");
  76.         $count = mysql_affected_rows();
  77.         tolog("Removed {$count} guild member references (non-existent login ID).");
  78.        
  79.         $q = sql("DELETE FROM `guild_position` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
  80.         $count = mysql_affected_rows();
  81.         tolog("Removed {$count} guild position references (non-existent guild ID).");
  82.        
  83.         $q = sql("DELETE FROM `guild_skill` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
  84.         $count = mysql_affected_rows();
  85.         tolog("Removed {$count} guild skill references (non-existent guild ID).");
  86.        
  87.         $q = sql("DELETE FROM `guild_storage` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
  88.         $count = mysql_affected_rows();
  89.         tolog("Removed {$count} items from guild storages (non-existent guild ID).");
  90.        
  91.         $q = sql("DELETE FROM `homunculus` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  92.         $count = mysql_affected_rows();
  93.         tolog("Removed {$count} homonculuses which does not belong to anyone.");
  94.        
  95.         $q = sql("DELETE FROM `skill_homunculus` WHERE homun_id NOT IN (SELECT homun_id FROM `homunculus`);");
  96.         $count = mysql_affected_rows();
  97.         tolog("Removed {$count} homunculus skill references (non-existent homunculus ID).");
  98.        
  99.         $q = sql("DELETE FROM `hotkey` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  100.         $count = mysql_affected_rows();
  101.         tolog("Removed {$count} hotkey references (non-existent character ID).");
  102.        
  103.         $q = sql("DELETE FROM `inventory` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  104.         $count = mysql_affected_rows();
  105.         tolog("Removed {$count} items from players inventories which does not belong to anyone.");
  106.        
  107.         $q = sql("DELETE FROM `mail` WHERE dest_id NOT IN (SELECT char_id FROM `char`);");
  108.         $count = mysql_affected_rows();
  109.         tolog("Removed {$count} mails with invalid recipient (non-existent character ID).");
  110.        
  111.         $q = sql("DELETE FROM `memo` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  112.         $count = mysql_affected_rows();
  113.         tolog("Removed {$count} warp portal memo's (non-existent character ID).");
  114.        
  115.         $q = sql("DELETE FROM `mercenary` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  116.         $count = mysql_affected_rows();
  117.         tolog("Removed {$count} mercenaries which does not belong to anyone.");
  118.        
  119.         $q = sql("DELETE FROM `mercenary_owner` WHERE merc_id NOT IN (SELECT mer_id FROM `mercenary`);");
  120.         $count = mysql_affected_rows();
  121.         tolog("Removed {$count} mercenary references (non-existent mercenary ID).");
  122.        
  123.         $q = sql("DELETE FROM `mercenary_owner` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  124.         $count = mysql_affected_rows();
  125.         tolog("Removed {$count} mercenary references (non-existent character ID).");
  126.        
  127.         $q = sql("DELETE FROM `party` WHERE leader_char NOT IN (SELECT char_id FROM `char`);");
  128.         $count = mysql_affected_rows();
  129.         tolog("Removed {$count} parties. (non-existent party-leader ID).");
  130.        
  131.         $q = sql("DELETE FROM `pet` WHERE char_id<>'' AND account_id<>'' AND char_id NOT IN (SELECT char_id FROM `char`);");
  132.         $count = mysql_affected_rows();
  133.         tolog("Removed {$count} pets which does not belong to anyone.");
  134.        
  135.         $q = sql("DELETE FROM `quest` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  136.         $count = mysql_affected_rows();
  137.         tolog("Removed {$count} quest references (non-existent character ID).");
  138.        
  139.         $q = sql("DELETE FROM `sc_data` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  140.         $count = mysql_affected_rows();
  141.         tolog("Removed {$count} status change values (non-existent character ID).");
  142.        
  143.         $q = sql("DELETE FROM `skill` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
  144.         $count = mysql_affected_rows();
  145.         tolog("Removed {$count} player skill references (non-existent character ID).");
  146.        
  147.         $q = sql("DELETE FROM `storage` WHERE account_id NOT IN (SELECT account_id FROM `login`);");
  148.         $count = mysql_affected_rows();
  149.         tolog("Removed {$count} items from player storages. (non-existent account ID).");
  150. }
  151.  
  152. function prune_logs() {
  153.         //todo :)
  154. }
  155.  
  156. $start = get_time();
  157. tolog("Starting...");
  158. if (!@mysql_connect($db_server,$db_username,$db_password)) tolog("Error! Can't connect to database server: ".mysql_error(),true);
  159. if (!@mysql_select_db($ragnarok_db)) tolog("Error: Can't switch to database '{$ragnarok_db}': ".mysql_error(),true);
  160.  
  161. remove_invalid_data();
  162. prune_logs();
  163.  
  164. $sec = number_format((get_time() - $start),3);
  165. tolog("Finished at {$sec} seconds.");
  166. ?>

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:

To highlight particular lines, prefix each line with {%HIGHLIGHT}




All content is user-submitted.
The administrators of this site (kpaste.net) are not responsible for their content.
Abuse reports should be emailed to us at