function pg2mysql(&$input, $header = true)
{
global $config;
if (is_array($input)) {
$lines = $input;
} else {
$lines = split("\n", $input);
}
if ($header) {
$output = "# Converted with " . PRODUCT . "-" . VERSION . "\n";
$output .= "# Converted on " . date("r") . "\n";
$output .= "# " . COPYRIGHT . "\n\n";
$output .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\nSET time_zone=\"+00:00\";\n\n";
} else {
$output = "";
}
$in_create_table = $in_insert = FALSE;
$linenumber = 0;
$tbl_extra = "";
while (isset($lines[$linenumber])) {
$line = $lines[$linenumber];
if (substr($line, 0, 12) == "CREATE TABLE") {
$in_create_table = true;
$line = str_replace("\"", "`", $line);
$output .= $line;
$linenumber++;
continue;
}
if (substr($line, 0, 2) == ");" && $in_create_table) {
$in_create_table = false;
$line = ") ENGINE={$config['engine']};\n\n";
$output .= $tbl_extra;
$output .= $line;
$linenumber++;
$tbl_extra = "";
continue;
}
if ($in_create_table) {
$line = str_replace("\"", "`", $line);
$line = str_replace(" integer", " int(11)", $line);
$line = str_replace(" int_unsigned", " int(11) UNSIGNED", $line);
$line = str_replace(" smallint_unsigned", " smallint UNSIGNED", $line);
$line = str_replace(" bigint_unsigned", " bigint UNSIGNED", $line);
$line = str_replace(" serial ", " int(11) auto_increment ", $line);
$line = str_replace(" bytea", " BLOB", $line);
$line = str_replace(" boolean", " bool", $line);
$line = str_replace(" bool DEFAULT true", " bool DEFAULT 1", $line);
$line = str_replace(" bool DEFAULT false", " bool DEFAULT 0", $line);
$line = str_replace("` `text`", "` text", $line);
// fix because pg_dump quotes text type for some reason
if (ereg(" character varying\\(([0-9]*)\\)", $line, $regs)) {
$num = $regs[1];
if ($num <= 255) {
$line = ereg_replace(" character varying\\([0-9]*\\)", " varchar({$num})", $line);
} else {
$line = ereg_replace(" character varying\\([0-9]*\\)", " text", $line);
}
}
//character varying with no size, we will default to varchar(255)
if (ereg(" character varying", $line)) {
$line = ereg_replace(" character varying", " varchar(255)", $line);
}
if (ereg("DEFAULT \\('([0-9]*)'::int", $line, $regs) || ereg("DEFAULT \\('([0-9]*)'::smallint", $line, $regs) || ereg("DEFAULT \\('([0-9]*)'::bigint", $line, $regs)) {
$num = $regs[1];
$line = ereg_replace(" DEFAULT \\('([0-9]*)'[^ ,]*", " DEFAULT {$num} ", $line);
}
if (ereg("DEFAULT \\(([0-9\\-]*)\\)", $line, $regs)) {
$num = $regs[1];
$line = ereg_replace(" DEFAULT \\(([0-9\\-]*)\\)", " DEFAULT {$num} ", $line);
}
$line = ereg_replace(" DEFAULT nextval\\(.*\\) ", " auto_increment ", $line);
$line = ereg_replace("::.*,", ",", $line);
$line = ereg_replace("::.*\$", "\n", $line);
if (ereg("character\\(([0-9]*)\\)", $line, $regs)) {
$num = $regs[1];
if ($num <= 255) {
$line = ereg_replace(" character\\([0-9]*\\)", " varchar({$num})", $line);
} else {
$line = ereg_replace(" character\\([0-9]*\\)", " text", $line);
}
}
//timestamps
$line = str_replace(" timestamp with time zone", " timestamp", $line);
$line = str_replace(" timestamp without time zone", " timestamp", $line);
//time
$line = str_replace(" time with time zone", " time", $line);
$line = str_replace(" time without time zone", " time", $line);
$line = str_replace(" timestamp DEFAULT now()", " timestamp DEFAULT CURRENT_TIMESTAMP", $line);
$line = preg_replace("/ timestamp( NOT NULL)?(,|\$)/", ' timestamp DEFAULT 0${1}${2}', $line);
if (strstr($line, "auto_increment")) {
$field = getfieldname($line);
$tbl_extra .= ", PRIMARY KEY(`{$field}`)\n";
}
$specialfields = array("repeat", "status", "type", "call", "key", "regexp");
$field = getfieldname($line);
if (in_array($field, $specialfields)) {
$line = str_replace("{$field} ", "`{$field}` ", $line);
}
//text/blob fields are not allowed to have a default, so if we find a text DEFAULT, change it to varchar(255) DEFAULT
if (strstr($line, "text DEFAULT")) {
$line = str_replace(" text DEFAULT ", " varchar(255) DEFAULT ", $line);
}
//just skip a CONSTRAINT line
if (strstr($line, " CONSTRAINT ")) {
$line = "";
//and if the previous output ended with a , remove the ,
$lastchr = substr($output, -2, 1);
// echo "lastchr=$lastchr";
if ($lastchr == ",") {
$output = substr($output, 0, -2) . "\n";
}
}
$output .= $line;
}
if (substr($line, 0, 11) == "INSERT INTO") {
//this insert spans multiple lines, so keep dumping the lines until we reach a line
//that ends with ");"
list($before, $after) = explode("VALUES", $line, 2);
//we only replace the " with ` in what comes BEFORE the VALUES
//(ie, field names, like INSERT INTO table ("bla","bla2") VALUES ('s:4:"test"','bladata2');
//should convert to INSERT INTO table (`bla`,`bla2`) VALUES ('s:4:"test"','bladata2');
$before = str_replace("\"", "`", $before);
//in after, we need to watch out for escape format strings, ie (E'escaped \r in a string'), and ('bla',E'escaped \r in a string')
//ugh i guess its possible these strings could exist IN the data as well, but the only way to solve that is to process these lines one character
//at a time, and thats just stupid, so lets just hope this doesnt appear anywhere in the actual data
//also there is a situation where string ends with \ (backslash). For example, 'C:\' and it's valid for pg, but not for mysql.
//the regexp looks odd, the preblem is that in PHP regexps we have to use 4 (four!) backslashes to represend one real!
//here is the regexp without escaping: (([^\]|^)(\\)*\)'
$after = preg_replace(array("/(, | \\()E'/", "/(([^\\\\]|^)(\\\\\\\\)*\\\\)'/"), array('\\1\'', '\\1\\\''), $after);
$c = substr_count($line, "'");
//we have an odd number of ' marks
if ($c % 2 != 0) {
$inquotes = true;
} else {
$inquotes = false;
}
$output .= $before . "VALUES" . $after;
while (substr($lines[$linenumber], -3, -1) != ");" || $inquotes) {
$linenumber++;
$line = $lines[$linenumber];
//in after, we need to watch out for escape format strings, ie (E'escaped \r in a string'), and ('bla',E'escaped \r in a string')
//ugh i guess its possible these strings could exist IN the data as well, but the only way to solve that is to process these lines one character
//at a time, and thats just stupid, so lets just hope this doesnt appear anywhere in the actual data
//also there is a situation where string ends with \ (backslash). For example, 'C:\' and it's valid for pg, but not for mysql.
//the regexp looks odd, the preblem is that in PHP regexps we have to use 4 (four!) backslashes to represend one real!
//here is the regexp without escaping: (([^\]|^)(\\)*\)'
$line = preg_replace(array("/, E'/", "/(([^\\\\]|^)(\\\\\\\\)*\\\\)'/"), array(", '", '\\1\\\''), $line);
$output .= $line;
// printf("inquotes: %d linenumber: %4d line: %s\n",$inquotes,$linenumber,$lines[$linenumber]);
$c = substr_count($line, "'");
//we have an odd number of ' marks
if ($c % 2 != 0) {
if ($inquotes) {
$inquotes = false;
} else {
$inquotes = true;
}
// echo "inquotes=$inquotes\n";
}
}
}
if (substr($line, 0, 16) == "ALTER TABLE ONLY") {
$line = preg_replace('/ ONLY/', '', $line);
$line = str_replace("\"", "`", $line);
$pkey = $line;
$linenumber++;
if (isset($lines[$linenumber])) {
$line = $lines[$linenumber];
if (strstr($line, " PRIMARY KEY ") && substr($line, -3, -1) == ");") {
//looks like we have a single line PRIMARY KEY definition, lets go ahead and add it
$output .= $pkey;
//MySQL and Postgres syntax are similar here, minus quoting differences
$output .= str_replace("\"", "`", $line);
}
}
}
//while we're here, we might as well catch CREATE INDEX as well
if (substr($line, 0, 12) == "CREATE INDEX") {
preg_match('/CREATE INDEX "?([a-zA-Z0-9_]*)"? ON "?([a-zA-Z0-9_]*)"? USING btree \\((.*)\\);/', $line, $matches);
if (isset($matches[1]) && isset($matches[2]) && isset($matches[3])) {
$indexname = $matches[1];
$tablename = $matches[2];
$columns = str_replace("\"", "`", $matches[3]);
$output .= "ALTER TABLE `{$tablename}` ADD INDEX ( {$columns} ) ;\n";
}
}
if (substr($line, 0, 13) == 'DROP DATABASE') {
$output .= $line;
}
if (substr($line, 0, 15) == 'CREATE DATABASE') {
preg_match('/CREATE DATABASE ([a-zA-Z0-9_]*) .* ENCODING = \'(.*)\'/', $line, $matches);
$output .= "CREATE DATABASE `{$matches['1']}` DEFAULT CHARACTER SET {$matches['2']};\n\n";
}
if (substr($line, 0, 8) == '\\connect') {
preg_match('/connect ([a-zA-Z0-9_]*)/', $line, $matches);
$output .= "USE `{$matches['1']}`;\n\n";
}
if (substr($line, 0, 5) == 'COPY ') {
preg_match('/COPY (.*) FROM stdin/', $line, $matches);
$heads = str_replace('"', "`", $matches[1]);
$values = array();
$in_insert = TRUE;
} elseif ($in_insert) {
if ($line == "\\.\n") {
$in_insert = FALSE;
if ($values) {
$output .= "INSERT INTO {$heads} VALUES\n" . implode(",\n", $values) . ";\n\n";
}
} else {
$vals = explode(' ', $line);
foreach ($vals as $i => $val) {
$val = trim($val);
switch ($val) {
case '\\N':
$vals[$i] = 'NULL';
break;
case 't':
$vals[$i] = 'true';
break;
case 'f':
$vals[$i] = 'false';
break;
default:
$vals[$i] = "'" . str_replace("'", "\\'", trim($val)) . "'";
}
}
$values[] = '(' . implode(',', $vals) . ')';
if (count($values) >= 1000) {
$output .= "INSERT INTO {$heads} VALUES\n" . implode(",\n", $values) . ";\n";
$values = array();
}
}
}
$linenumber++;
}
return $output;
}
function pg2mysql($input)
{
$lines=explode("\n",$input);
$output="";
if(substr($lines[0],-1)=="\r")
{
// Lines have \n\r termination - from web
$ssoff = -3;
$sslen = -1;
}
else
{
// Lines have no \r - file input
$ssoff = -2;
$sslen = 2;
}
$in_create_table=false;
$linenumber=0;
$tbl_extra="";
while(isset($lines[$linenumber]))
{
$line=$lines[$linenumber];
if(substr($line,0,12)=="CREATE TABLE")
{
$in_create_table=true;
$line=str_replace("\"","`",$line);
$output.=$line;
$linenumber++;
continue;
}
if(substr($line,0,2)==");" && $in_create_table)
{
$in_create_table=false;
$line=") TYPE=MyISAM;\n\n";
$output.=$tbl_extra;
$output.=$line;
$linenumber++;
$tbl_extra="";
continue;
}
if($in_create_table)
{
$line=str_replace("\"","`",$line);
$line=str_replace(" integer"," int(11)",$line);
$line=str_replace(" serial"," int(11) auto_increment",$line);
$line=str_replace(" bytea"," BLOB",$line);
$line=str_replace(" boolean"," bool",$line);
$line=str_replace(" bool DEFAULT true"," bool DEFAULT 1",$line);
$line=str_replace(" bool DEFAULT false"," bool DEFAULT 0",$line);
if(ereg(" character varying\(([0-9]*)\)",$line,$regs))
{
$num=$regs[1];
if($num<=255)
$line=ereg_replace(" character varying\([0-9]*\)"," varchar($num)",$line);
else
$line=ereg_replace(" character varying\([0-9]*\)"," text",$line);
}
//character varying with no size, we will default to varchar(255)
if(ereg(" character varying",$line))
{
$line=ereg_replace(" character varying"," varchar(255)",$line);
}
$line=ereg_replace(" DEFAULT nextval\(.*\) "," auto_increment ",$line);
$line=ereg_replace("::.*,",",",$line);
$line=ereg_replace("::.*$","\n",$line);
if(ereg("character\(([0-9]*)\)",$line,$regs))
{
$num=$regs[1];
if($num<=255)
$line=ereg_replace(" character\([0-9]*\)"," varchar($num)",$line);
else
$line=ereg_replace(" character\([0-9]*\)"," text",$line);
}
//timestamps
$line=str_replace(" timestamp with time zone"," timestamp",$line);
$line=str_replace(" timestamp without time zone"," timestamp",$line);
//time
$line=str_replace(" time with time zone"," time",$line);
$line=str_replace(" time without time zone"," time",$line);
$line=str_replace(" timestamp DEFAULT now()"," timestamp DEFAULT CURRENT_TIMESTAMP",$line);
if(strstr($line,"auto_increment"))
{
$field=getfieldname($line);
$tbl_extra.=", PRIMARY KEY(`$field`)\n";
}
$output.=$line;
}
if(substr($line,0,11)=="INSERT INTO")
{
if(substr($line,$ssoff,$sslen)==");")
{
//we have a complete insert on one line
$line=str_replace("\"","`",$line);
$output.=$line;
$linenumber++;
continue;
}
else
{
//this insert spans multiple lines, so keep dumping the lines until we reach a line
//that ends with ");"
$line=str_replace("\"","`",$line);
$output.=$line;
do{
$linenumber++;
$output.=$lines[$linenumber];
} while(substr($lines[$linenumber],$ssoff,$sslen)!=");" && isset($lines[$linenumber]));
}
}
$linenumber++;
}
return $output;
}
