<?php
include("common_db.php");
include("functions.php");
dbconnect($host, $username, $password); //from common_db.php
$strings = loadStrings($lang, 'RIMPORT');
headers();
html();
head("Importing data");
menu();
navpane();
?>
<div id="mainpane">
<?php
//check that the file got here successfully, no errors, et cetera
if($_FILES['file']['error']!=UPLOAD_ERR_OK) {
//an error occurred, so die appropriately
switch($_FILES['file']['error']) {
case UPLOAD_ERR_INI_SIZE:
echo("<p class=\"error\">$strings[IMPORT_ERR_SIZE]</p>");
break;
case UPLOAD_ERR_FORM_SIZE:
echo("<p class=\"error\">$strings[IMPORT_ERR_SIZE]</p>");
break;
case UPLOAD_ERR_PARTIAL:
echo("<p class=\"error\">$strings[IMPORT_ERR_UFAILED]</p>");
break;
case UPLOAD_ERR_NO_FILE:
echo("<p class=\"error\">$strings[IMPORT_ERR_UFAILED]</p>");
break;
case UPLOAD_ERR_NO_TMP_DIR:
echo("<p class=\"error\">$strings[IMPORT_ERR_SERVERFAILED]</p>");
break;
case UPLOAD_ERR_CANT_WRITE:
echo("<p class=\"error\">$strings[IMPORT_ERR_SERVERFAILED]</p>");
break;
}
} else if (!is_uploaded_file($_FILES['file']['tmp_name'])) { //this check is, apparently, needed
//to prevent the script pointing to,
//say, /etc/shadow or wherever (acc-
//ording to the php docs)
echo("<p class=\"error\">$strings[IMPORT_ERR_SERVERFAILED]</p>");
} else {
//good to go. Try to figure out what type of file it is.
$type = $_REQUEST['type'];
if($type == "auto") {
//we'll just guess from the extension.
switch(substr($_FILES['file']['name'], -3)) {
case 'sql':
$type="mysql";
break;
case 'mdb':
$type="mdb";
break;
case 'csv':
$type="csv";
break;
default:
echo("<p class=\"error\">$strings[IMPORT_ERR_UNKNOWNTYPE]</p>");
break;
}
}
switch($type) {
case 'mysql':
//We basically need to cat the contents of the file into an instance of mysql
//Since SELinux and PHP can be a pain, we need to read the contents of the file
//then spit it out somewhere else before we can *use* it
if(is_uploaded_file($_FILES['file']['tmp_name'])) {
echo('<pre>');
//get the appropriate filename and path
$fname = dirname(__FILE__) . '/' . basename($_FILES['file']['tmp_name']); //TODO:Windows compatibility?
//resave the file
file_put_contents($fname, file_get_contents($_FILES['file']['tmp_name']));
//the following builds the command
$mysql = "mysql -h $host -u $username";
if($password != "")
$mysql .= " -p $password";
$command = $mysql . ' < ' . $fname;
$command .= ' 2>&1'; //redirect stderr to stdout
//Finally, execute the command
passthru($command);
//Say how pleased we are that it worked (or possibly didn't)
echo("</pre><p>$strings[IMPORT_SQLEXEC]</p>");
//and remove our temporary file
unlink($fname);
} else { //from is_uploaded_file
echo("<p class=\"error\">$strings[IMPORT_ERR_SERVERFAILED]</p>");
}
break;
case 'mdb':
//$mdbtools_location = "/home/patrick/mdbtools-cvs/inst/bin/"; //where is mdbtools?
$mdbtools_location = "/usr/local/bin/";
//Lots of fun to be had here (note that we've dropped down a few levels of indentation
//for readability).
//First we need to create the database:
$new_db = $_REQUEST['msadb'];
$query = "CREATE DATABASE IF NOT EXISTS " . $new_db;
mysql_query($query) or die($strings['IMPORT_ERR_CREATEDB']);
//Depending on security settings, PHP can be a sufficient pain that we need to copy the
//file somewhere else.
//TODO: make more efficient
if(is_uploaded_file($_FILES['file']['tmp_name'])) {
//get the appropriate filename and path
$fname = dirname(__FILE__) . '/' . basename($_FILES['file']['tmp_name']); //TODO:Windows compatibility?
//resave the file
file_put_contents($fname, file_get_contents($_FILES['file']['tmp_name']));
//Now we need to generate a schema DDL
//We can use the program mdb-schema to do this (assuming it's in the $PATH, which we
//do). Caveat: mdbtools 0.5 and below does not support creating schemas for mysql.
//mdbtools 0.6pre (from CVS as of writing) does, however.
//get the required parameters for MySQL
$mysql = "mysql -h $host -u $username";
if($password != "")
$mysql .= " -p $password";
$mysql .= " $new_db";
//For the uninitiated, the for mdb-schema is
// mdb-schema <file-name> [<schema-type>]
//This isn't particularly well documented. (Other schema types: oracle, sybase, access,
//postgres)
$scname = $fname . '-schema';
$command = "${mdbtools_location}mdb-schema -S $fname mysql > $scname"; //TODO: Windows compat?
// ( -S -- sanitize)
passthru($command);
//Now, ideally, we could have piped the output of mdb-schema directy to mysql. However,
//mdb-schema generates a set of "DROP TABLE" commands. These need to be removed.
//(In some future version, those statements may be come DROP TABLE IF EXISTS, but they're
//not as of 0.6pre)
//We also need to escape some of the MySQL keywords, if they have tables or columns named
//after them
$patterns[0] = "/DROP TABLE/";
$patterns[1] = "/\sOrder\s/i";
$replacements[0] = "--";
$replacements[1] = "_order";
ksort($patterns);
ksort($replacements);
file_put_contents($scname, preg_replace($patterns, $replacements, file_get_contents($scname)));
//And now, we can finally stuff that into MySQL
printf("<p>$strings[IMPORT_MDB_SCHEMA]</p>",$table);
$command = "$mysql < $scname";
//passthru for debugging
passthru($command);
// //We need to get a list of tables in the database
// //We can use mdb-tables to achieve this, which returns a space-separated list
// //Note that this only gets user tables. To get system tables (those beginning
// //with 'MSys'), add a '-S' to the line.
// $command = "${mdbtools_location}mdb-tables $fname";
// echo('...........' . shell_exec($command) . '.............');
// $tables = explode(' ', trim(shell_exec($command)));
mysql_select_db($new_db);
//Now we need to use mdb-export to get a CSV file of the output.
// foreach($tables as $table) {
//Ideally, we'd do what's said above and get all the tables,
//but mdb-tables appear to have a bug whereby it reports non-existant databases
//So we SHOW TABLES from the MySQL database
$query = "SHOW TABLES";
$results = mysql_query($query);
while($r = mysql_fetch_array($results)) {
$table = $r[0];
if($table == "Order") {
$table = "_Order";
}
$tfname = $fname . '-table';
$command = "${mdbtools_location}mdb-export -D\"%F %T\" -H $fname $table > $tfname"; //the '>' should overwrite the file
//( -H == suppress header row)
//( -D == date, see strftime(3) )
//Incidentally, there is a -I option for mdb-export that will produce
//INSERT ... statements, but said statements appear to occassionally
//be bad SQL.
passthru($command); //passthru for debugging
//Now read that into the database
printf("<p>$strings[IMPORT_MDB_ADDTABLE]</p>",$table);
$query = 'LOAD DATA LOCAL INFILE "' . $tfname . '" INTO TABLE ' . $table . ' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" LINES TERMINATED BY "\\n"';
mysql_query($query) or print(mysql_error()); //echo for debugging
}
//TODO: When mdb-tools supports it, add support for reports, macros, whatever
unlink($fname);
unlink($tfname);
unlink($scname);
echo("<p>$strings[IMPORT_MDBDONE]</p>");
} else { //from is_uploaded_file
echo("<p class=\"error\">$strings[IMPORT_ERR_SERVERFAILED]</p>");
}
break;
case 'csv':
//TODO: more options
//Possibly, some compatibility types, for example
//Microsoft Excel has it's own tricks with csv s
//LOAD DATA INFILE "filename.csv" INTO TABLE your_table FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\\r\\n";
//mysql has a dinky little command ^^ above that will
//load most csv s.
$fname = mysql_escape_string($_FILES['file']['tmp_name']);
if(isset($_REQUEST['db']) && isset($_REQUEST['table'])) {
//in the off chance the user has managed to not select a line ending
//type, guess "windows"
//TODO: smarter autodetect
if(!isset($_REQUEST['linendings']))
$lineendings = 'win';
else
$lineendings = $_REQUEST['lineendings'];
$db = $_REQUEST['db'];
$table = mysql_escape_string($_REQUEST['table']);
mysql_select_db($db);
//build the query
$query = 'LOAD DATA LOCAL INFILE "' . $fname . '" INTO TABLE ' . $table . ' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" LINES TERMINATED BY "' . ($lineendings == 'nix' ? '\\n' : '\\r\\n') . '"';
if(!mysql_query($query)) {
//an error.
printf("<p class=\"error=\">$strings[IMPORT_ERR_MYSQL]</p>", mysql_error());
} else {
echo('<p>' . $strings['IMPORT_SUCCESS'] . '</p>');
}
} else { //the user didn't select a database/table
echo("<p class=\"error\">$strings[IMPORT_ERR_BADDBTABLE]</p>");
}
break;
//if it isn't one of the above, presumably it's a type that wasn't
//auto-detected
}
}
?>
</div>
<?php
endhtml();
?>