Location: PHPKode > projects > HypatiaDB > hypatiadb/run-import.php
<?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();
?>
Return current item: HypatiaDB