Platinum Partner

Simple utility for copying data from MongoDB to MySql

http://my.sociopal.com/sociopaltech/post?id=simple_utility_for_copying_data_from_mongodb_to_mysql_this_is_a_simple_php_program_im_using_in_o_61755

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;
}

{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}