May 21, 2013 at 6:01 pm

Simple utility for copying data from MongoDB to MySql This is a simple PHP program I'm using in order to automatically migrate data from My precious MongoDB to MySQL it: 1. Introspetcts the MongoDB collections and creates the MySQL schema. 2. Migrates the collections into MySql The code: $a_mongo_db; $cols = $db->getCollectionNames(); // WATCH OUT!!!! Drops existing tables from MySql, foreach ($cols as $k => $v) { $drop = "DROP TABLE IF EXISTS $v"; $reply = mysql_query($drop, $sql); } foreach ($cols as $k => $v) { $c = $db->$v; $r = $c->find(); $fields = array(); $fieldTypes = array(); foreach ($r as $rec) { foreach ($rec as $fname => $field) { $ftype = tgetType($field); if ($fname == "synonyms") { } else if ($ftype == "array") { foreach ($field as $subName => $sub_val) if ($sub_val != null) { if ($subName == 'date') { $f2type = 'DATE'; } else $f2type = tgetType($sub_val); if ($f2type == 'DATE') $fieldTypes[$fname . '_' . $subName] = 'DATETIME'; else if ($f2type == "OBJECT") $fieldTypes[$fname . '_' . $subName] = 'VARCHAR(255)'; else if ($f2type != "array") $fieldTypes[$fname . '_' . $subName] = $f2type; } } else if ($ftype == "OBJECT") { $fieldTypes[$fname] = 'VARCHAR(255)'; $fields[$fname] = true; } else if ($field != null) { $fieldTypes[$fname] = $ftype; $fields[$fname] = true; } } } // create MySql tables $createCmd = "create table $v ("; foreach ($fieldTypes as $name => $type) { $createCmd.="$name $type"; if ($name == '_id') $createCmd.=" PRIMARY KEY,"; else $createCmd.=','; } $createCmd = trim($createCmd, ','); $createCmd.=")"; $out = mysql_query($createCmd); if ($out == false) echo $createCmd . "\n"; // inserting data $r = $c->find(); foreach ($r as $rec) { $cmd = "insert into $v set "; foreach ($rec as $fname => $field) { $ftype = tgetType($field); if ($fname == "synonyms") { } else if ($ftype == "array") { foreach ($field as $subName => $sub_val) if ($sub_val != null) { if ($subName == 'date') $f2type = 'DATE'; else $f2type = tgetType($sub_val); if ($f2type == "DATE") { $cmd.=$fname . "_$subName='$sub_val' "; $cmd.=','; } else if ($f2type == "OBJECT") { $val = $sub_val->__toString(); $cmd.=$fname . "_$subName='$val' "; $cmd.=','; } else if ($f2type != "array") { if ($f2type == "BIT" || $f2type == "INT") $cmd.=$fname . "_$subName=$sub_val "; else { $sub_val = remLetters($sub_val); $cmd.=$fname . "_$subName='$sub_val' "; } $cmd.=','; } } } else if ($ftype == "OBJECT") { $val = $field->__toString(); $cmd.="$fname='$val' "; $cmd.=','; } else if ($field != null) { if ($ftype == "BIT" || $ftype == "INT") $cmd.="$fname=$field "; else { $field = remLetters($field); $cmd.="$fname='$field' "; } $cmd.=','; } } $cmd = trim($cmd, ','); $out = mysql_query($cmd); if ($out == false) echo 'SQL error:' . $cmd . "\n"; } } // Mapping from Mongo types to MySql types, feel free to change function tgetType($field) { if (is_string($field)) return "TEXT"; else if (is_object($field)) return "OBJECT"; else if (is_bool($field)) return "BIT"; else if (is_int($field)) return "INT"; else if (is_object($field)) return "VARCHAR(255)"; else if (is_array($field)) { return "array"; } } // this is done to avoid SQL errors, but it changes the strings, removing quotes and double quotes function remLetters($s) { $remove[] = "'"; $remove[] = '"'; $out = str_replace($remove, " ", $s); return $out; } ?>