phpBB.de

Code: Alles auswählen

<?

$dbms = 'mysql';
$phpbb_root_path = '../phpBB/';

$dbhost = 'localhost';
$dbuser = 'user';
$dbpasswd = 'secret';


// Name and prefix for the database that should keep the original IDs
$dbname1 = 'database1';
$table_prefix1 = '';

// Name and prefix for the database that is going to be added
// to DB1.
$dbname2 = 'database2';
$table_prefix2 = 'phpbb_';

define('IN_PHPBB', true);
// Use DB1 for the initial config table etc.
$dbname = $dbname1;
$table_prefix = $table_prefix1;
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'includes/constants.'.$phpEx);
include($phpbb_root_path . 'includes/db.'.$phpEx);


// These tables can be dropped from DB2 (not used in this script)
$drop_tables = array(
    "config",
    "banlist", // This one could be converted
    "disallow",
    "search_results",
    "search_wordlist",
    "search_wordmatch",
    "sessions",
    "smilies",
    "themes",
    "themes_name",
    "words"
    );
    
// All tables in DB2 that should shift ID.
//  - table that needs shifting (categories)
//  - id in table (cat_id)
//    nested array:
//    - table that depends on id (forums)
//    - id that corresponds to id in original table (cat_id)
$shift_tables = array(
    "categories" => array(
        "cat_id", 
        array(
            array("forums", "cat_id")
        )
    ),
    "forums" => array(
        "forum_id",
        array(
            array("posts", "forum_id"),
            array("topics", "forum_id"),
            array("forum_prune", "forum_id"),
            array("auth_access", "forum_id")
        )
    ),
    "forum_prune" => array(
        "prune_id",
        array()
    ),
    "groups" => array(
        "group_id",
        array(
            array("user_group", "group_id"),
            array("auth_access", "group_id")
        )
    ),
    "posts" => array(
        "post_id",
        array(
            array("posts_text", "post_id"),
            array("topics", "topic_first_post_id"),
            array("topics", "topic_last_post_id")
        )
    ),
    "privmsgs" => array(
        "privmsgs_id",
        array(
            array("privmsgs_text", "privmsgs_text_id"),
            array("users", "user_last_privmsg")
        )
    ),
    "topics" => array(
        "topic_id",
        array(
            array("posts", "topic_id"),
            array("topics_watch", "topic_id"),
            array("vote_desc", "topic_id")
        )
    ),
    "users" => array(
        "user_id",
        array(
            array("user_group", "user_id"),
            array("groups", "group_moderator"),
            array("posts", "poster_id"),
            array("topics", "topic_poster"),
            array("privmsgs", "privmsgs_to_userid"),
            array("privmsgs", "privmsgs_from_userid"),
            array("topics_watch", "user_id"),
            array("vote_voters", "vote_user_id")
        )
    ),
    "ranks" => array(
        "rank_id",
        array(
            array("users", "user_rank")
        )
    ),
    "vote_desc" => array(
        "vote_id",
        array(
            array("vote_voters", "vote_id"),
            array("vote_results", "vote_id")
        )
    )
);

$bla_tables = array(
    "auth_access",
    "user_group",
    "posts_text",
    "privmsgs_text",
    "topics_watch",
    "vote_results",
    "vote_voters"
    );

// Traverse the shift_tables array
foreach($shift_tables as $key => $value)
{
    $table = $key;
    $merge_tables[$table] = 0; // keep an array with all tables that need merging
    $column = $value[0]; // Column with ID that needs to be shifted
    $ref = $value[1]; // Tables that are using the mentioned ID.
    print "Shifting IDs in table $table<br />\n";
    $max = shift_ids($table, $column);
    flush();
    // Do the dependent tables
    foreach($ref as $key => $value)
    {
        $d_table = $value[0];
        $merge_tables[$d_table] = 0;
        $d_column = $value[1];
        print "&nbsp; Altering dependent table: $d_table : $d_column (offset = $max)<br />\n";
        flush();
        shift_ids($d_table, $d_column, $max);
    }
    
    print "<br />\n";
    flush();
}

foreach($merge_tables as $table => $value)
{
    print "Merging $table table: ";
    if(merge_tables($table_prefix1 . $table))
    {
        print " OK<br />\n";
    }
    else
    {
        print " FAILED!<br />\n";
    }
}

print "Merging users (username and either password or email are the same).<br />";
$sql = "
    SELECT
        u1.user_id as id1, 
        u2.user_id as id2, 
        u1.username
    FROM
        " . USERS_TABLE . " u1,
        " . USERS_TABLE . " u2
    WHERE
        u1.username = u2.username
        && (u1.user_password = u2.user_password
            || u1.user_email = u2.user_email)
        && u1.user_id != u2.user_id
        && u1.user_id < u2.user_id";
if(!$result = $db->sql_query($sql))
{
     message_die(GENERAL_ERROR, 'Could not query for double user records.', '', __LINE__, __FILE__, $sql);
}

print "<table cellpadding='0' cellspacing='0'>";
while($row = $db->sql_fetchrow($result))
{
    print "<tr><td> ".$row['id1']."&nbsp;</td><td> ".$row['id2']."&nbsp;</td><td> ".$row['username']."&nbsp;</td><td> ";
    merge_users($row['id1'], $row['id2']);
    print " </td></tr>\n";
}
print "</table>";


function merge_users($user_id1, $user_id2)
{
    global $db;
    global $shift_tables;
    global $table_prefix1;
    
    $user_deps = $shift_tables['users'][1];

    // The users table should be skipped and the user_posts column should be updated.
    
    foreach($user_deps as $key => $value)
    {
        $d_table = $value[0];
        $d_column = $value[1];
        $sql = "UPDATE $table_prefix1$d_table SET $d_column = $user_id1 WHERE $d_column = $user_id2";
        if(!$result = $db->sql_query($sql))
        {
             message_die(GENERAL_ERROR, 'Could not update user_id.', '', __LINE__, __FILE__, $sql);
        }
    }
    
    $sql = "DELETE FROM " . $table_prefix1 . "users WHERE user_id = $user_id2";
    if(!$result = $db->sql_query($sql))
    {
         message_die(GENERAL_ERROR, 'Could not delete user2.', '', __LINE__, __FILE__, $sql);
    }

    print "OK";
    return;
}

function double_users()
{
    global $db;
    global $table_prefix1;

    $users_table = $table_prefix1 . "users";

    $sql = "SELECT user_id, ";
}

function merge_tables($table)
{
    global $db;
    global $dbname1, $table_prefix1, $dbname2, $table_prefix2;
    
    $sql = "SHOW FIELDS FROM $table_prefix$table";
    if(!$result = $db->sql_query($sql))
    {
         message_die(GENERAL_ERROR, 'Could not get field info from $table.', '', __LINE__, __FILE__, $sql);
    }
    
    $fields = array();
    while($row = $db->sql_fetchrow($result))
    {
        $fields[] = $row['Field'];
    }
    $fieldlist = implode($fields, ', ');
    
    if($table == 'users')
    {
        $where = " WHERE $dbname2.$table_prefix2" . $table . ".user_id > 0";
    }
    else
    {
        $where = '';
    }
    
    $sql = "INSERT INTO $dbname1.$table_prefix1".$table." ($fieldlist) SELECT $fieldlist from $dbname2.$table_prefix2" . $table . $where;
    if(!$db->sql_query($sql))
    {
         message_die(GENERAL_ERROR, 'Could not merge $table.', '', __LINE__, __FILE__, $sql);
    }

    return TRUE;
}


// Shift all ID's in column $id in table $table in 
// database 2 by MAX($id) or (if not 0) by $offset
function shift_ids($table, $id, $offset = 0)
{
    global $db;
    global $dbname1, $table_prefix1;
    global $dbname2, $table_prefix2;

    // Offset hasn't been given, we're going to figure it out ourselfs
    if($offset == 0)
    {
        if(!$offset = getmax($dbname1, $table_prefix1.$table, $id))
        {
            // Empty table, no need to shift IDs
            print "Empty table? Skipping...<br />\n";
            return;
        }
    }

    // What's the max_id in the current table?
    $max2 = getmax($dbname2, $table_prefix2.$table, $id);
    $max2 = intval($max2); // Make sure that max2 contains a number, make it 0 if this table is empty.
    
    
    // First we add the offset + the max of the current table
    // Treat values of 0 and lower as special values.
    $sql = "UPDATE $dbname2." . $table_prefix2 . $table . " SET $id = $id + $max2 + $offset WHERE $id > 0";
    print "$sql<br />\n";
    if(!$result = $db->sql_query($sql) )
    {
         message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
    }
    
    // Then we subtract the max of the current table again.
    // We do this to prevent problems with key constrains from happening
    // i.e. if we do id=id+20 on key 1 when key 21 already exists we would get an error
    $sql = "UPDATE $dbname2." . $table_prefix2 . $table . " SET $id = $id - $max2 WHERE $id > 0";
    print "$sql<br />\n";
    if(!$result = $db->sql_query($sql) )
    {
         message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
    }

    return $offset;
}

function getmax($dbname, $table, $id)
{
    global $db;
        $sql = "SELECT MAX($id) as max_id FROM $dbname." . $table;
        if(!$result = $db->sql_query($sql) )
        {
             message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
        }
        if($max = $db->sql_fetchrow($result))
        {
            return($max['max_id']);
        }
        else
        {
            // Probably no rows where returned.. Empty table.
            return FALSE;
        }
}


function message_die($msg_code, $msg_text = '', $msg_title = '', $err_line = '', $err_file = '', $sql = '')
{
    global $db, $template, $board_config, $theme, $lang, $phpEx, $phpbb_root_path, $nav_links, $gen_simple_header;
    global $userdata, $user_ip, $session_length;
    global $starttime;

    $sql_store = $sql;
    
    //
    // Get SQL error if we are debugging. Do this as soon as possible to prevent 
    // subsequent queries from overwriting the status of sql_error()
    //
    $sql_error = $db->sql_error();

    $debug_text = '';

    if ( $sql_error['message'] != '' )
    {
        $debug_text .= '<br /><br />SQL Error : ' . $sql_error['code'] . ' ' . $sql_error['message'];
    }

    if ( $sql_store != '' )
    {
        $debug_text .= "<br /><br />$sql_store";
    }

    if ( $err_line != '' && $err_file != '' )
    {
        $debug_text .= '</br /><br />Line : ' . $err_line . '<br />File : ' . $err_file;
    }

    print $debug_text;

    exit;
}

?>