diff options
| -rw-r--r-- | install/migrate-mypg.php | 306 | ||||
| -rw-r--r-- | install/migrate_mypg_fixseq.sql | 55 |
2 files changed, 361 insertions, 0 deletions
diff --git a/install/migrate-mypg.php b/install/migrate-mypg.php new file mode 100644 index 000000000..b7a7ced1b --- /dev/null +++ b/install/migrate-mypg.php @@ -0,0 +1,306 @@ +<?php +function ask_question($menu, $options, $default) { + $r=null; + while(!$r) { + echo $menu; + $str = substr(strtolower(fgets(STDIN)),0,-1); + if($str == '') + $r = $default; + else if(in_array($str, $options)) + $r = $str; + } + return $r; +} + +function get_data($prompt, $regex) { + do { + echo $prompt; + $r = substr(fgets(STDIN), 0, -1); + if(!preg_match($regex, $prompt)) + $r = ''; + } while($r === ''); + return $r; +} + +function parse_htconfig($file) { + if(!file_exists($file)) + return array(); + $conf = file_get_contents($file); + preg_match_all('/\$db\_(host|port|user|pass|data|type)\s*=\s*\'([[:print:]]+)\'/', $conf, $matches); + return array_combine($matches[1], $matches[2]); +} + +function get_configtype(array $data) { + if(!isset($data['host'], $data['user'], $data['pass'], $data['data'])) + return 'none'; + if($data['type'] == 1) + return 'pgsql'; + return 'mysql'; +} + +function phpquote($str) { + for($r = '', $x=0, $l=strlen($str); $x < $l; $x++) + if($str{$x} == '\'' || $str{$x} == '\\') + $r .= '\\' . $str{$x}; + else + $r .= $str{$x}; + return $r; +} + +function run_sql($file, $db, &$err, &$n) { + $sql = file_get_contents($file); + $sql = explode(';', $sql); + $err = 0; $n = 0; + $c = count($sql); + if(!$c) { + echo "Unknown error.\n"; + exit(); + } + foreach($sql as $stmt) { + if($stmt == '' || $stmt == "\n" || $stmt == "\n\n") + continue; + $r = $db->exec($stmt); + if($r===false) { + echo "\nError executing $stmt: ".var_export($db->errorInfo(), true)."\n"; + $err++; + } else { + $n++; + } + if($n % 5 == 0) + echo "\033[255DExecuting: $file, $n/$c\033[K"; + } + echo "\n"; +} + +$drivers=true; +if(!class_exists('PDO')) + $drivers=false; +if($drivers) { + $drivers = PDO::getAvailableDrivers(); + if(!in_array('pgsql', $drivers) || !in_array('mysql', $drivers)) + $drivers = false; +} +if(!$drivers) { + echo "Sorry. This migration tool requires both mysql and pgsql PDO drivers.\n"; + $r = ask_question("If you are on dreamhost you can enable them. This might work on other shared hosts too. Type 'n' to do it yourself.\nWould you like to try (Y/n)? ", array('y', 'n'), 'y'); + if($r=='y') { + $path = $_SERVER['HOME'] . '/.php/5.4'; + if(!file_exists($path)) + mkdir($path, 0770, true); + + $rcfile = $path . '/phprc'; + + $str = ''; + $mods = get_loaded_extensions(); + foreach(array('pdo_mysql','pdo_pgsql','pgsql') as $ext) + if(!in_array($ext, $mods)) + $str .= 'extension=' . $ext . ".so\n"; + + file_put_contents($rcfile, $str, FILE_APPEND ); + echo "drivers enabled.\nNow type: \033[1m/usr/local/bin/php-5.4 install/".basename($argv[0])."\033[0m\n"; + } + exit(); +} + +foreach(array('install','include','mod','view') as $dir) { + if(!file_exists($dir)) { + echo "You must execute from inside the webroot like the cron\n"; + exit(); + } +} + +$cfg = parse_htconfig('.htconfig.php'); +$type = get_configtype($cfg); +if($type != 'mysql') { + echo "Error. Must start with standard mysql installation in .htconfig.php.\n"; + exit(); +} + +if(!$cfg['port']) + $cfg['port'] = 3306; +try { + $mydb = new PDO("mysql:host={$cfg['host']};dbname={$cfg['data']};port={$cfg['port']}", $cfg['user'], $cfg['pass']); +} catch (PDOException $e) { + echo "Error connecting to mysql DB: " . $e->getMessage() . "\n"; + exit(); +} + +if(!file_exists('.htconfig.php-pgsql')) { + echo "Enter postgres server info:\n"; + $p['host'] = get_data("Hostname: ", '/[\w.]+/'); + $p['port'] = get_data("Enter port (0 for default): ", '/\d+/'); + $p['user'] = get_data("Username: ", '/\w+/'); + $p['pass'] = get_data("Password: ", '/[[:print:]]+/'); + $p['data'] = get_data("Database name: ", '/\w+/'); + $old = file_get_contents('.htconfig.php'); + $new = preg_replace( + array( + '/^(\$db_host\s*=\s*\')([\w.]+)(\';)$/m', + '/^(\$db_port\s*=\s*\')(\d+)(\';)$/m', + '/^(\$db_user\s*=\s*\')(\w+)(\';)$/m', + '/^(\$db_pass\s*=\s*\')([[:print:]]+)(\';)/m', + '/^(\$db_data\s*=\s*\')(\w+)(\';)$/m', + '/^(\$db_type\s*=\s*\')(\d)(\';)$/m' // in case they already have it + ), array( + "$1{$p['host']}$3", + "\${1}{$p['port']}$3", + "$1{$p['user']}$3", + "$1{$p['pass']}$3", + "$1{$p['data']}$3\n\$db_type = '1';\n", // they probably don't + "\${1}1$3" + ), + $old, + 1, + $repl + ); + if($new === false || $repl < 5) { + echo "Failed. Please make a postgres config file named .htconfig.php-pgsql - Be sure to add \"\$db_type = '1';\" to your config.\n"; + exit(); + } + file_put_contents('.htconfig.php-pgsql', $new); +} + +$pcfg = parse_htconfig('.htconfig.php-pgsql'); +$ptype = get_configtype($pcfg); +if($ptype != 'pgsql') { + echo "Error. Must have a valid pgsql config named .htconfig.php-pgsql. Be sure to add \"\$db_type = '1';\" to your config.\n"; + exit(); +} + +if(!$pcfg['port']) + $pcfg['port'] = 5432; +try { + $pgdb = new PDO("pgsql:host={$pcfg['host']};dbname={$pcfg['data']};port={$pcfg['port']}", $pcfg['user'], $pcfg['pass']); +} catch (PDOException $e) { + echo "Error connecting to pgsql DB: " . $e->getMessage() . "\n"; + echo "cfg string: " . "pgsql:host={$pcfg['host']};dbname={$pcfg['data']};port={$pcfg['port']}\n"; + exit(); +} +$B = "\033[0;34m"; +$H = "\033[0;35m"; +$W = "\033[1;37m"; +$M = "\033[1;31m"; +$N = "\033[0m"; +$r = ask_question("Ready to migrate {$W}Red{$M}(#){$W}Matrix$N from mysql db @$B{$cfg['host']}$N/$B{$cfg['data']}$N to postgres db @$B{$pcfg['host']}$N/$B{$pcfg['data']}$N. +The site will be disabled during the migration by moving the $H.htconfig.php$N file to $H.htconfig.php-mysql$N. +If for any reason the migration fails, you will need to move the config file back into place manually before trying again. + +Are you ready to begin (N/y)? ", array('y','n'), 'n' +); + +if($r == 'n') + exit(); + +rename('.htconfig.php', '.htconfig.php-mysql'); + +run_sql('install/schema_postgres.sql', $pgdb, $err, $n); +if($err) { + echo "There were $err errors creating the pgsql schema. Unable to continue.\n"; +/* $r = ask_question("There were $err errors (and $n ok) creating the schema. This shouldn't happen and continuing will probably fail. +Do you want to continue migration anyway (N/y)? ", array('y','n'), 'n' + ); + if($r == 'n')*/ + exit(); +} + +echo "pgsql schema created. $n queries executed successfully.\n"; + +$res = $pgdb->query("select relname, attname, pg_type.typname from ((pg_attribute inner join pg_class on attrelid=pg_class.oid) inner join pg_type on atttypid=pg_type.oid) inner join pg_namespace on relnamespace=pg_namespace.oid where nspname='public' and atttypid not in (26,27,28,29) and relkind='r' and attname <> 'item_search_vector';"); +if($res === false) { + echo "Error reading back schema. Unable to continue.\n"; + var_export($pgdb->errorInfo()); + exit(); +} +$schema = array(); +while(($row = $res->fetch()) !== false) + $schema[$row[0]][$row[1]] = $row[2]; + +$err = 0; $n = 0; +$reserved = array('ignore','key','with'); +foreach($schema as $table=>$fields) { + $fnames = array_keys($fields); + $pfnames = array_keys($fields); + + foreach($fnames as &$fname) + if(in_array($fname, $reserved)) + $fname = '`' . $fname . '`'; + $fstr = implode(',', $fnames); + + foreach($pfnames as &$pfname) + if(in_array($pfname, $reserved)) + $pfname = '"' . $pfname . '"'; + $pfstr = implode(',', $pfnames); + + $res = $mydb->query("SELECT count(*) FROM $table;"); + if($res === false) { + echo "Fatal error counting table $table: ".var_export($mydb->errorInfo(), true)."\n"; + exit(); + } + $nrows = $res->fetchColumn(0); + if(!$nrows) { + echo "\nTABLE $table has 0 rows in mysql db.\n"; + continue; + } + + $pstr = ''; + for($x=0, $c=count($fields); $x < $c; $x++) + $pstr .= ($x ? ',?' : '?'); + + $crow = 0; + echo "\033[255DTABLE: $table [$c fields] $crow/$nrows (".number_format(($crow/$nrows)*100,2)."%)\033[K"; + + $res = $mydb->query("SELECT $fstr FROM $table;"); + if($res === false) { + echo "Fatal Error importing table $table: ".var_export($mydb->errorInfo(), true)."\n"; + exit(); + } + + $istmt = $pgdb->prepare("INSERT INTO $table ($pfstr) VALUES ($pstr);"); + if($istmt === false) { + echo "Fatal error preparing query. Aborting.\n"; + var_export($pgdb->errorInfo()); + exit(); + } + + while(($row = $res->fetch(PDO::FETCH_NUM)) !== false) { + foreach($row as $idx => &$val) + if(array_slice(array_values($fields),$idx,1)[0] == 'timestamp' && $val == '0000-00-00 00:00:00') + $istmt->bindParam($idx+1, ($nulldate='0001-01-01 00:00:00')); + else if(array_slice(array_values($fields),$idx,1)[0] == 'bytea') + $istmt->bindParam($idx+1, $val, PDO::PARAM_LOB); + else + $istmt->bindParam($idx+1, $val); + $r = $istmt->execute(); + if($r === false) { + $err++; + echo "\nInsert error: ".var_export(array($pgdb->errorInfo(), $table, $fields, $row), true)."\n"; + //exit(); + } else + $n++; + $crow++; + if(($crow % 10) == 0 || $crow == $nrows) + echo "\033[255DTABLE: $table [$c fields] $crow/$nrows (".number_format(($crow/$nrows)*100,2)."%)\033[K"; + } +} + +echo "Done with $err errors and $n inserts.\n"; +if($err) { + echo "Migration had errors. Aborting.\n"; + exit(); +} + +run_sql('install/migrate_mypg_fixseq.sql', $pgdb, $err, $n); +echo "Sequences updated with $err errors and $n inserts.\n"; +if($err) + exit(); + +$r = ask_question("Everything successful. Once you connect up the pg database there is no going back. Do you want to make it live (N,y)?", array('y', 'n'), 'n'); +if($r == 'n') { + echo "You can make active by renaming .htconfig.php-pgsql to .htconfig.php, or start over by renaming .htconfig.php-mysql to .htconfig.php\n"; + exit(); +} + +rename('.htconfig.php-pgsql', '.htconfig.php'); +echo "Done. {$W}Red{$M}(#){$W}Matrix$N now running on postgres.\n"; + + diff --git a/install/migrate_mypg_fixseq.sql b/install/migrate_mypg_fixseq.sql new file mode 100644 index 000000000..806c5cf31 --- /dev/null +++ b/install/migrate_mypg_fixseq.sql @@ -0,0 +1,55 @@ + SELECT SETVAL('public.abook_abook_id_seq', COALESCE(MAX(abook_id), 1) ) FROM public.abook; + SELECT SETVAL('public.account_account_id_seq', COALESCE(MAX(account_id), 1) ) FROM public.account; + SELECT SETVAL('public.addon_id_seq', COALESCE(MAX(id), 1) ) FROM public.addon; + SELECT SETVAL('public.app_id_seq', COALESCE(MAX(id), 1) ) FROM public.app; + SELECT SETVAL('public.attach_id_seq', COALESCE(MAX(id), 1) ) FROM public.attach; + SELECT SETVAL('public.channel_channel_id_seq', COALESCE(MAX(channel_id), 1) ) FROM public.channel; + SELECT SETVAL('public.chat_chat_id_seq', COALESCE(MAX(chat_id), 1) ) FROM public.chat; + SELECT SETVAL('public.chatpresence_cp_id_seq', COALESCE(MAX(cp_id), 1) ) FROM public.chatpresence; + SELECT SETVAL('public.chatroom_cr_id_seq', COALESCE(MAX(cr_id), 1) ) FROM public.chatroom; + SELECT SETVAL('public.config_id_seq', COALESCE(MAX(id), 1) ) FROM public.config; + SELECT SETVAL('public.conv_id_seq', COALESCE(MAX(id), 1) ) FROM public.conv; + SELECT SETVAL('public.event_id_seq', COALESCE(MAX(id), 1) ) FROM public.event; + SELECT SETVAL('public.fcontact_id_seq', COALESCE(MAX(id), 1) ) FROM public.fcontact; + SELECT SETVAL('public.ffinder_id_seq', COALESCE(MAX(id), 1) ) FROM public.ffinder; + SELECT SETVAL('public.fserver_id_seq', COALESCE(MAX(id), 1) ) FROM public.fserver; + SELECT SETVAL('public.fsuggest_id_seq', COALESCE(MAX(id), 1) ) FROM public.fsuggest; + SELECT SETVAL('public.group_member_id_seq', COALESCE(MAX(id), 1) ) FROM public.group_member; + SELECT SETVAL('public.groups_id_seq', COALESCE(MAX(id), 1) ) FROM public.groups; + SELECT SETVAL('public.hook_id_seq', COALESCE(MAX(id), 1) ) FROM public.hook; + SELECT SETVAL('public.hubloc_hubloc_id_seq', COALESCE(MAX(hubloc_id), 1) ) FROM public.hubloc; + SELECT SETVAL('public.issue_issue_id_seq', COALESCE(MAX(issue_id), 1) ) FROM public.issue; + SELECT SETVAL('public.item_id_id_seq', COALESCE(MAX(id), 1) ) FROM public.item_id; + SELECT SETVAL('public.item_id_seq', COALESCE(MAX(id), 1) ) FROM public.item; + SELECT SETVAL('public.likes_id_seq', COALESCE(MAX(id), 1) ) FROM public.likes; + SELECT SETVAL('public.mail_id_seq', COALESCE(MAX(id), 1) ) FROM public.mail; + SELECT SETVAL('public.manage_id_seq', COALESCE(MAX(id), 1) ) FROM public.manage; + SELECT SETVAL('public.menu_item_mitem_id_seq', COALESCE(MAX(mitem_id), 1) ) FROM public.menu_item; + SELECT SETVAL('public.menu_menu_id_seq', COALESCE(MAX(menu_id), 1) ) FROM public.menu; + SELECT SETVAL('public.notify_id_seq', COALESCE(MAX(id), 1) ) FROM public.notify; + SELECT SETVAL('public.obj_obj_id_seq', COALESCE(MAX(obj_id), 1) ) FROM public.obj; + SELECT SETVAL('public.pconfig_id_seq', COALESCE(MAX(id), 1) ) FROM public.pconfig; + SELECT SETVAL('public.photo_id_seq', COALESCE(MAX(id), 1) ) FROM public.photo; + SELECT SETVAL('public.poll_elm_pelm_id_seq', COALESCE(MAX(pelm_id), 1) ) FROM public.poll_elm; + SELECT SETVAL('public.poll_poll_id_seq', COALESCE(MAX(poll_id), 1) ) FROM public.poll; + SELECT SETVAL('public.profdef_id_seq', COALESCE(MAX(id), 1) ) FROM public.profdef; + SELECT SETVAL('public.profext_id_seq', COALESCE(MAX(id), 1) ) FROM public.profext; + SELECT SETVAL('public.profile_check_id_seq', COALESCE(MAX(id), 1) ) FROM public.profile_check; + SELECT SETVAL('public.profile_id_seq', COALESCE(MAX(id), 1) ) FROM public.profile; + SELECT SETVAL('public.register_id_seq', COALESCE(MAX(id), 1) ) FROM public.register; + SELECT SETVAL('public.session_id_seq', COALESCE(MAX(id), 1) ) FROM public.session; + SELECT SETVAL('public.shares_share_id_seq', COALESCE(MAX(share_id), 1) ) FROM public.shares; + SELECT SETVAL('public.sign_id_seq', COALESCE(MAX(id), 1) ) FROM public.sign; + SELECT SETVAL('public.source_src_id_seq', COALESCE(MAX(src_id), 1) ) FROM public.source; + SELECT SETVAL('public.spam_id_seq', COALESCE(MAX(id), 1) ) FROM public.spam; + SELECT SETVAL('public.sys_perms_id_seq', COALESCE(MAX(id), 1) ) FROM public.sys_perms; + SELECT SETVAL('public.term_tid_seq', COALESCE(MAX(tid), 1) ) FROM public.term; + SELECT SETVAL('public.updates_ud_id_seq', COALESCE(MAX(ud_id), 1) ) FROM public.updates; + SELECT SETVAL('public.verify_id_seq', COALESCE(MAX(id), 1) ) FROM public.verify; + SELECT SETVAL('public.vote_vote_id_seq', COALESCE(MAX(vote_id), 1) ) FROM public.vote; + SELECT SETVAL('public.xchat_xchat_id_seq', COALESCE(MAX(xchat_id), 1) ) FROM public.xchat; + SELECT SETVAL('public.xconfig_id_seq', COALESCE(MAX(id), 1) ) FROM public.xconfig; + SELECT SETVAL('public.xign_id_seq', COALESCE(MAX(id), 1) ) FROM public.xign; + SELECT SETVAL('public.xlink_xlink_id_seq', COALESCE(MAX(xlink_id), 1) ) FROM public.xlink; + SELECT SETVAL('public.xtag_xtag_id_seq', COALESCE(MAX(xtag_id), 1) ) FROM public.xtag; + |