PHP:DumpSQL

From EVEDev
Jump to: navigation, search

Copied from EDK Devtools and installer

Export

 
<?php
mysql_connect('localhost', 'user', 'pass');
mysql_select_db('db');
set_time_limit(0);
 
function dmptblstruct($table)
{
    $result = mysql_query('show create table '.$table);
    $row = mysql_fetch_row($result);
    $fp = fopen('sql_tblstrct_'.str_replace('kb3_', '', $table).'.sql', 'w');
    $text = substr($row[1], 0, strpos($row[1], 'ENGINE=MyISAM'));
    $text = str_replace("\n", "\r\n", $text).'TYPE=MyISAM;';
    fwrite($fp, $text);
    fclose($fp);
}
 
function dmptbldata($table)
{
    global $opt, $nodump, $dropdata;
    if (in_array($table, $nodump))
    {
        return;
    }
 
    $result = mysql_query('show columns from '.$table);
    while ($row = mysql_fetch_assoc($result))
    {
        $fields[] = $row['Field'];
    }
 
    $name = str_replace('kb3_', '', $table);
    if (in_array($table, $opt))
    {
        $base = 'sql_tbldata_opt_'.$name;
    }
    else
    {
        $base = 'sql_tbldata_'.$name;
    }
 
    $part = 1;
    $fp = fopen($base.'.sql', 'w');
    $result = mysql_query('select * from '.$table);
    while ($row = mysql_fetch_row($result))
    {
        $row_count++;
        $rows = array();
        foreach ($row as $field)
        {
            $rows[] = addslashes(stripslashes($field));
        }
 
        // ('.join(',', $fields).')
        $line = 'INSERT IGNORE INTO `'.$table.'` VALUES (\''.join("','", $rows).'\');'."\r\n";
        $len += strlen($line);
        if ($len >= 500*1024)
        {
            $len = strlen($line);
            fclose($fp);
            $part++;
            $fp = fopen($base.'_p'.$part.'.sql', 'w');
        }
        fwrite($fp, $line);
    }
    if (in_array($table, $dropdata))
    {
        $fp = fopen('tbl_check.txt', 'a');
        fwrite($fp, $table.chr(9).$row_count."\r\n");
        fclose($fp);
    }
}
$result = mysql_query('show tables');
while ($line = mysql_fetch_row($result))
{
    dmptblstruct($table);
    dmptbldata($table);
}
?>

Import

 
<?php
 
$struct = $opt = $data = array();
$dir = opendir('./sql');
while ($file = readdir($dir))
{
    if (strpos($file, 'sql_') !== false)
    {
        $table = str_replace(array('sql_tblstrct_', 'sql_tbldata_opt_', 'sql_tbldata_', '.sql'), '', $file);
        $table = preg_replace('/(_p\d{1,4})/', '', $table);
        if (strpos($file, 'tblstrct'))
        {
            $structc++;
            $struct[$table][] = $file;
        }
        elseif (strpos($file, '_opt_'))
        {
            $dcnt++;
            $optcnt++;
            $opt[$table][] = $file;
            asort($opt[$table]);
        }
        else
        {
            $dcnt++;
            $datacnt++;
            $data[$table][] = $file;
            asort($data[$table]);
        }
    }
}
 
$db = mysql_pconnect($_SESSION['sql']['host'], $_SESSION['sql']['user'], $_SESSION['sql']['pass']);
mysql_select_db($_SESSION['sql']['db']);
$result = mysql_query('show tables');
while ($row = mysql_fetch_row($result))
{
    $table = $row[0];
    unset($struct[$table]);
}
 
if ($_REQUEST['sub'] == 'struct')
{
    foreach ($struct as $table => $files)
    {
        foreach ($files as $file)
        {
            echo 'Creating table '.$table.' from file '.$file.'...';
            $query = file_get_contents('./sql/'.$file);
            $id = mysql_query($query);
            if ($id)
            {
                echo 'done<br/>';
            }
            else
            {
                echo 'error: '.mysql_error().'<br/>';
            }
        }
        unset($struct[$table]);
    }
}
 
if ($_REQUEST['sub'] == 'data')
{
    if (!isset($_SESSION['sqlinsert']))
    {
        $_SESSION['sqlinsert'] = 1;
        if (isset($_POST['opt']))
        {
            $_SESSION['useopt'] = array();
            foreach ($_POST['opt'] as $table => $value)
            {
                $_SESSION['useopt'][] = $table;
            }
        }
    }
 
    $i = 0;
    $did = false;
    $errors = false;
 
        foreach ($data as $table => $files)
        {
            foreach ($files as $file)
            {
                $i++;
                if ($_SESSION['sqlinsert'] > $i)
                {
                    continue;
                }
                echo 'Inserting data ('.$i.'/'.$datacnt.') into '.$table.' from file '.$file.'...<br/>';
 
                $error = '';
                $querys = file('./sql/'.$file);
                $lines = count($querys);
                $errors = 0;
                foreach ($querys as $query)
                {
                    if (trim($query))
                    {
                        $query = trim($query);
                        if (substr($query, -1, 1) == ';')
                        {
                            $query = substr($query, 0, -1);
                        }
                        $query_count++;
                        $id = mysql_query($query);
                        if (!$id)
                        {
                            $error .= 'error: '.mysql_error().'<br/>';
                            $errors++;
                        }
                    }
                }
                echo 'File '.$file.' had '.$lines.' lines with '.$query_count.' querys. '.$errors.' Querys failed.<br/>';
                if (!$error)
                {
                    echo 'done<br/>';
                    echo '<meta http-equiv="refresh" content="1; URL=?step=4&sub=data" />';
                    echo 'Automatic reload in 1s for next chunk. <a href="?step=4&sub=data">Manual Link</a><br/>';
                }
                else
                {
                    echo $error;
                    echo '<meta http-equiv="refresh" content="20; URL=?step=4&sub=data" />';
                    echo 'Automatic reload in 20s for next chunk because of the error occured. <a href="?step=4&sub=data">Manual Link</a><br/>';
                }
                $_SESSION['sqlinsert']++;
 
                $did = true;
                break 2;
            }
    }
}
 
Personal tools