<?php
$sql = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
mysql_select_db($a_db, $sql);
$m = new MongoClient($dbName);
$db = $m->$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;
}
