account-dump

#! /usr/bin/php
<?php

// Change your database settings here:
$db = mysql_connect("localhost", "planeshift", "planeshift") or die ("Unable to connect!"); // host, user, pass
mysql_select_db("planeshift", $db) or die("Could not select database");

/* helper functions */

/** Executes an sql statement and returns it's return.
 *  
 *  @param query Sql query to execute
 *  @return the result of the executed sql query
 */
function execute_sql($query)
{
    $result = mysql_query($query) or die("Query error:" . mysql_error() . "\n");
    return $result;
}

/**
 * Dumps the sql of a row taking all entries.
 * It works generically with all the possible rows.
 *
 * @param tableArray An array containing the table row to parse.
 * @param tableName The name of the table containing this array, used
 *                   for preparing the string.
 * @param dumpFirstColumn If False will substituite the first column value with 0.
 *                         Usually used for id auto increment use with mysql.
 * @return a string with a single insert statement
 */
function dump_sql($tableArray, $tableName, $dumpFirstColumn = TRUE)
{
    $outputQuery = "INSERT INTO $tableName VALUES (";
    //this is the id we use a default auto increment one if dumpFirstColumn is false.
    if($dumpFirstColumn == FALSE)
        $outputQuery .= "0,";
    //creates the ...x,y,z... part of the statement, wrap all in '' and escape the value
    foreach($dumpFirstColumn == TRUE ? $tableArray : array_slice($tableArray,1) as $value)
    {
        $tmp = mysql_real_escape_string($value);
        $outputQuery .= "'$tmp',";
    }
    return substr($outputQuery,0,strlen($outputQuery)-1) . ");"; //we have to remove trailing ,
}

/**
 * Dumps the sql of a query.
 * It works generically with all the possible queries.
 *
 * @param query The query to execute.
 * @param tableName The name of the table used in this query, used
 *                  for preparing the string.
 * @param dumpFirstColumn If False will substituite the first column value with 0.
 *                        Usually used for id auto increment use with mysql.
 *                        This is passed to dump_sql.
 * @return a string with all the insert statement generated by the query.
 */

function dump_sql_rows($query, $tableName, $dumpFirstColumn = TRUE)
{
    $queryOutput = "";
    $queryRows = execute_sql($query);
    //processes all the rows returned by the query.
    while($queryRow = mysql_fetch_row($queryRows))
    {
        $queryOutput .= dump_sql($queryRow, $tableName, $dumpFirstColumn) . "\n";
    }
    return $queryOutput;
}

/** Gets the id of the account starting from the username.
 *
 *  @param The username which we are searching.
 *  @return The ID of the found account if found.
 */

function getAccountIDFromName($username)
{
    //search by username
    $accountID_query = execute_sql("SELECT id FROM accounts WHERE username = '$username'");

    //if we got zero rows it means the account wasn't found. Drop out.
    if(mysql_num_rows($accountID_query) == 0)
        die("Account ".$username." not Found!\n");

    return mysql_result($accountID_query, 0, "id");
}

/*main function*/

$accountID = 0;

if($argc < 2) //not enough arguments. syntax: account-dump <account ID|account Username>
{
    die("You need to specify an account id or username (usually the email)\n");
}

if(!is_numeric($argv[1])) //if the argument isn't a number search by name
{
    $accountID = getAccountIDFromName($argv[1]);
}
else
{
    $accountID = $argv[1];
}


//dump the account statement
$account_query = execute_sql("SELECT * FROM accounts WHERE id = '$accountID'");

if(mysql_num_rows($account_query) == 0)
    die("Account ".$argv[1]." not Found!\n");

printf(dump_sql(mysql_fetch_row($account_query), "accounts") . "\n\n");

//dump the characters
$charactersRows = execute_sql("SELECT * FROM characters WHERE account_id = '$accountID'");

//we found no characters in this account? empty account? no need to go over.
if(mysql_num_rows($charactersRows) == 0)
    die("Account ".$argv[1]." has no characters!\n");

//dump each character with all it's sub data.
while($characterRow = mysql_fetch_row($charactersRows))
{
    $characterID = $characterRow[0]; //copy the character id for later use.
    printf("\n\n" . dump_sql($characterRow, "characters") . "\n\n");

    //dump character quests
    printf(dump_sql_rows("SELECT * FROM character_quests where player_id = '$characterID'", "character_quests"));
    //dump character skills
    printf(dump_sql_rows("SELECT * FROM character_skills where character_id = '$characterID'", "character_skills"));
    //dump character traits
    printf(dump_sql_rows("SELECT * FROM character_traits where character_id = '$characterID'", "character_traits"));
    //dump player spells
    printf(dump_sql_rows("SELECT * FROM player_spells where player_id = '$characterID'", "player_spells"));
    //dump items
    printf(dump_sql_rows("SELECT * FROM item_instances where char_id_owner = '$characterID'", "item_instances", FALSE));

    //TODO: Complete npc dumping
    //these will give results only with npc.
    //dump merchant_item_categories
    printf(dump_sql_rows("SELECT * FROM merchant_item_categories where player_id = '$characterID'", "merchant_item_categories"));

}

?>