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

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