2 database's mergen

jitka-[CZ] · Bericht door **jitka-[CZ]** » 04 okt 2003, 23:27

Weet iemand hoe ik 2 database's met elkaar
moet mergen??

De perfixs is bij bijde het zelfde "phpbb_"

Leipo · Bericht door **Leipo** » 05 okt 2003, 10:11

Uuhmm wat is mergen

MeDieViL · Bericht door **MeDieViL** » 05 okt 2003, 10:27

Leipo schreef:Uuhmm wat is mergen

umm mengen

jitka-[CZ] · Bericht door **jitka-[CZ]** » 05 okt 2003, 10:54

Twee DB's met elkaar samen voegen.

Doormiddel van dit script bijvoorbeeld

Code: Selecteer alles

<?

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

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


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

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

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"),
			array("posts", "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))
	{
		print " OK<br />\n";
	}
	else
	{
		print " FAILED!<br />\n";
	}
}

print "Merging users (username and (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;
	
	$user_deps = $shift_tables['users'][1];
	
	foreach($user_deps as $key => $value)
	{
		$d_table = $value[0];
		$d_column = $value[1];
		$sql = "UPDATE $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 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 merge_tables($table)
{
	global $db;
	global $dbname1, $table_prefix1, $dbname2, $table_prefix2;
	
	$sql = "SHOW FIELDS FROM $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, ', ');
	
	$sql = "INSERT INTO $dbname1.$table_prefix1".$table." ($fieldlist) SELECT $fieldlist from $dbname2.$table_prefix2".$table;
	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);

	if($max2 == FALSE || $max2 == 0)
	{
		// The table in DB2 was probably empty, no need to shift IDs
		return $offset;
	}
	
	
	// 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;
}

?>

Leipo · Bericht door **Leipo** » 05 okt 2003, 11:19

Beetje raar.. je geeft zelf oplossing al..
Zo niet moet je ze samen mengen in PHPMyAdmin

jitka-[CZ] · Bericht door **jitka-[CZ]** » 05 okt 2003, 12:14

Ja, die heb ik vannacht gevonden, dus zo raar
is dat dan niet.

Maar hoe doe je dat dan in PHPMyAdmin ??

Leipo · Bericht door **Leipo** » 05 okt 2003, 14:06

Uuhm.. tabel bekijken.. kopieeren.. plakken volgens mij..

jitka-[CZ] · Bericht door **jitka-[CZ]** » 05 okt 2003, 17:30

Dat werkt zo dus niet.
Als je dat zo doet, dan is je DB verziekt

Leipo · Bericht door **Leipo** » 05 okt 2003, 17:40

Je moet stuk bij stuk doen..
Bijv. niet alles eronder kopieen maar de members onder de members bij het ene db'tje doen..

Badja · Bericht door **Badja** » 07 nov 2003, 00:57

jitka-[CZ] schreef:Twee DB's met elkaar samen voegen.

Doormiddel van dit script bijvoorbeeld

Code: Selecteer alles

<?

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

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


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

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

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"),
			array("posts", "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))
	{
		print " OK<br />\n";
	}
	else
	{
		print " FAILED!<br />\n";
	}
}

print "Merging users (username and (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;
	
	$user_deps = $shift_tables['users'][1];
	
	foreach($user_deps as $key => $value)
	{
		$d_table = $value[0];
		$d_column = $value[1];
		$sql = "UPDATE $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 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 merge_tables($table)
{
	global $db;
	global $dbname1, $table_prefix1, $dbname2, $table_prefix2;
	
	$sql = "SHOW FIELDS FROM $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, ', ');
	
	$sql = "INSERT INTO $dbname1.$table_prefix1".$table." ($fieldlist) SELECT $fieldlist from $dbname2.$table_prefix2".$table;
	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);

	if($max2 == FALSE || $max2 == 0)
	{
		// The table in DB2 was probably empty, no need to shift IDs
		return $offset;
	}
	
	
	// 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;
}

?>

Wat moet je met dit code doen?

Podium4 · Bericht door **Podium4** » 07 nov 2003, 08:20

maak er een php bestandje van bijvoorbeeld merge.php zet het in de root en navigeer er heen.....

w0lfie.be · Bericht door **w0lfie.be** » 07 nov 2003, 11:24

@Badja:
Hoe zinloos om heel dat script te quoten, je verneukt hier de topic door die extra lang te maken.

Wat heeft dat mengen als voordeel? Welke twee databases meng je? Zijn dat phpbb databases? Dan zal hoogstwaarschijnlijk de uiteindelijke tabel langs geen kanten werken volgens mij

SPyKoN · Bericht door **SPyKoN** » 07 nov 2003, 16:32

w0lfie.be schreef:@Badja:
Hoe zinloos om heel dat script te quoten, je verneukt hier de topic door die extra lang te maken.

Wat heeft dat mengen als voordeel? Welke twee databases meng je? Zijn dat phpbb databases? Dan zal hoogstwaarschijnlijk de uiteindelijke tabel langs geen kanten werken volgens mij

Jah dit vind ik nou zeer kinderachtig. Je verpest gewoon je eigen topic. Ik geef je geen support aangezien je zo kinderachtig doet. Ga dan ergens anders

w0lfie.be · Bericht door **w0lfie.be** » 07 nov 2003, 17:09

SPyKoN schreef:Jah dit vind ik nou zeer kinderachtig.

Kidnerachtig? Wat?

SPyKoN schreef:Je verpest gewoon je eigen topic.

Eigen topic? Ik heb die hier niet opgestart

SPyKoN schreef:Ik geef je geen support aangezien je zo kinderachtig doet. Ga dan ergens anders

Wtf is uw probleem manneke? Puber geworden en 't is naar je hoofd gestegen ofzo?

SPyKoN · Bericht door **SPyKoN** » 07 nov 2003, 17:37

w0lfie.be schreef:
SPyKoN schreef:Jah dit vind ik nou zeer kinderachtig.
Kidnerachtig? Wat?

SPyKoN schreef:Je verpest gewoon je eigen topic.
Eigen topic? Ik heb die hier niet opgestart

SPyKoN schreef:Ik geef je geen support aangezien je zo kinderachtig doet. Ga dan ergens anders
Wtf is uw probleem manneke? Puber geworden en 't is naar je hoofd gestegen ofzo?

How sorry hoor dat was wel niet tegen jou bedoeld. Lees eerst eens goed mijn antwoord. Sorry als je me verkeerd begrepen zou hebben!