php mysql/file backend, thanks to Kabal458

--HG--
extra : convert_revision : svn%3Ab267cdba-c1da-11dd-874b-8bacd04a0a74/trunk%4048

3 files changed, 240 insertions, 0 deletions

diff --git a/backend/php-mysql+file/data/default b/backend/php-mysql+file/data/default
new file mode 100644
index 0000000..a6de8b9
--- /dev/null
+++ b/backend/php-mysql+file/data/default
@@ -0,0 +1,30 @@
+<?xml version="1.0" encoding="utf-8" ?><sql><datatypes db="mysql">
+	<group label="Numeric" color="rgb(238,238,170)">
+		<type label="Integer" length="0" sql="INTEGER" re="INT" quote=""/>
+		<type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote=""/>
+		<type label="Single precision" length="0" sql="FLOAT" quote=""/>
+		<type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote=""/>
+	</group>
+
+	<group label="Character" color="rgb(255,200,200)">
+		<type label="Char" length="1" sql="CHAR" quote="'"/>
+		<type label="Varchar" length="1" sql="VARCHAR" quote="'"/>
+		<type label="Text" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>
+		<type label="Binary" length="1" sql="BINARY" quote="'"/>
+		<type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>
+		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
+	</group>
+
+	<group label="Date &amp; Time" color="rgb(200,255,200)">
+		<type label="Date" length="0" sql="DATE" quote="'"/>
+		<type label="Time" length="0" sql="TIME" quote="'"/>
+		<type label="Datetime" length="0" sql="DATETIME" quote="'"/>
+		<type label="Year" length="0" sql="YEAR" quote=""/>
+		<type label="Timestamp" length="0" sql="TIMESTAMP" quote="'"/>
+	</group>
+	
+	<group label="Miscellaneous" color="rgb(200,200,255)">
+		<type label="ENUM" length="1" sql="ENUM" quote=""/>
+		<type label="SET" length="1" sql="SET" quote=""/>
+	</group>
+</datatypes><table x="50" y="50" name="Producer"><row name="id" null="0" autoincrement="1"><datatype>INTEGER</datatype></row><row name="name" null="1" autoincrement="0"><datatype>VARCHAR(100)</datatype><default>NULL</default></row><key type="PRIMARY" name=""><part>id</part></key></table><table x="574" y="66" name="Consumer"><row name="id" null="0" autoincrement="1"><datatype>INTEGER</datatype></row><row name="name" null="1" autoincrement="0"><datatype>VARCHAR(100)</datatype><default>NULL</default></row><key type="PRIMARY" name=""><part>id</part></key></table><table x="195" y="333" name="Product"><row name="id" null="0" autoincrement="1"><datatype>INTEGER</datatype></row><row name="id_Producer" null="0" autoincrement="1"><datatype>INTEGER</datatype><relation table="Producer" row="id" /></row><row name="name" null="1" autoincrement="0"><datatype>VARCHAR(100)</datatype></row><key type="PRIMARY" name=""><part>id</part></key></table><table x="383" y="227" name="Garbage"><row name="id" null="0" autoincrement="1"><datatype>INTEGER</datatype></row><row name="id_Product" null="0" autoincrement="1"><datatype>INTEGER</datatype><relation table="Product" row="id" /></row><row name="id_Consumer" null="0" autoincrement="1"><datatype>INTEGER</datatype><relation table="Consumer" row="id" /></row><row name="consumed" null="0" autoincrement="0"><datatype>TIMESTAMP</datatype></row><key type="PRIMARY" name=""><part>id</part></key></table></sql>
\ No newline at end of file
diff --git a/backend/php-mysql+file/index.php b/backend/php-mysql+file/index.php
new file mode 100644
index 0000000..4528c22
--- /dev/null
+++ b/backend/php-mysql+file/index.php
@@ -0,0 +1,115 @@
+<?php
+	/**
+	 * This file contains the plugin to allow importing from a MySQL database
+	 * but instead of saving data to that database it will write it instead to
+	 * a static XML file.  The load function will echo out the contents of the
+	 * file.
+	 * 
+	 * When saving/loading the script will only accept alphanumeric w/ underscore
+	 * filenames between 1 and 100 characters (yes, its arbitrary, change it if
+	 * you don't like it :p).
+	 * 
+	 * No validation is performed on the XML data saved to file.  A malicious user
+	 * could potentially upload a HUGE file so be aware of this when you configure
+	 * your server.  No user authentication is included.
+	 * 
+	 * Please note that this is not all my work.  Large portions of it have been
+	 * copied from the php-mysql and php-file plugins.
+	 * 
+	 * @author 'Kabal458'	<Kabal458@gmail.com>
+	 * @since  10-July-2009
+	 */
+
+	// Define these constants for importing from your MySQL database. MySQL 
+	// usually stores your metadata inside the `information_schema` database
+	// on the `TABLES` table
+	define('HOST',	'localhost');
+	define('USER',	'root');
+	define('PASS',	'password');
+	define('DB',	'information_schema');
+	
+	// This constant is the regular expression used to validate the filename
+	define('FILENAME_REGEX',   '^[A-Za-z0-9_]{1,100}$');
+	
+	// Pull the action variable from the GET and validate it
+	$action = isset($_GET['action']) ? $_GET['action'] : '';
+	
+	if(!in_array($action, array('list','save','load','import'))) {
+		header('HTTP/1.0 501 Not Implemented');
+		exit;
+	}
+	
+	// Save and Load actions must have the 'keyword' GET variable to work,  
+	// I've used a regular expression to keep filenames simple and valid 
+	// so that they don't do anything unintended.
+	if($action == 'save' || $action == 'load') {
+		if(!isset($_GET['keyword']) || !ereg(FILENAME_REGEX, $_GET['keyword'])) {
+			header('HTTP/1.0 400 Bad Request');
+			exit;
+		}
+	}
+	
+	// The import action requires the database variable to be set
+	if($action == 'import' && !isset($_GET['database'])) {
+		header('HTTP/1.0 400 Bad Request');
+		exit;	
+	}
+	
+	// Based on the action variable return appropriate data
+	switch($action) {
+		case 'list':
+			// List all files in the data directory
+			foreach (glob('data/*') as $file)
+				echo basename($file)."\n";
+			break;
+			
+		case 'save':
+			// Open the file for writing
+			$f = fopen('data/'.$_GET['keyword'], 'w');
+			
+			// Read in the contents of the XML file from input
+			$data = file_get_contents('php://input');
+			if (get_magic_quotes_gpc() || get_magic_quotes_runtime()) {
+			   $data = stripslashes($data);
+			}
+			
+			// Write the data to the file and close the file
+			fwrite($f, $data);
+			fclose($f);
+			
+			// Write the response HTTP code
+			header('HTTP/1.0 201 Created');
+			break;
+			
+		case 'load':
+			$keyword = 'data/' . $_GET['keyword'];
+			if (!file_exists($keyword)) {
+				header('HTTP/1.0 404 Not Found');
+			} else {
+				header('Content-type: text/xml');
+				echo file_get_contents($keyword);
+			}
+			break;
+			
+		case 'import':
+			// The import command may take some time, so make sure that PHP won't timeout
+			set_time_limit(0);
+			
+			// I've included the mysql_import function from the php_mysql plugin as its
+			// own file for future compatibility and to take so much code out of this one
+			// file.
+			require_once 'mysql_import.php';
+			
+			// Connect to the MySQL Database, short circuiting ensures that this code will 
+			// execute without error
+			if (!mysql_connect(HOST,USER,PASS) || !mysql_select_db(DB)) {
+				header("HTTP/1.0 503 Service Unavailable");
+				exit;
+			}
+			
+			header("Content-type: text/xml");
+			echo import();
+			
+			break;
+	}	
+?>
diff --git a/backend/php-mysql+file/mysql_import.php b/backend/php-mysql+file/mysql_import.php
new file mode 100644
index 0000000..8d399e0
--- /dev/null
+++ b/backend/php-mysql+file/mysql_import.php
@@ -0,0 +1,95 @@
+<?php 
+	function import() {
+		$db = (isset($_GET["database"]) ? $_GET["database"] : "information_schema");
+		$db = mysql_real_escape_string($db);
+		$xml = "";
+
+		$arr = array();
+		@ $datatypes = file("../../db/mysql/datatypes.xml");
+		$arr[] = $datatypes[0];
+		$arr[] = '<sql db="mysql">';
+		for ($i=1;$i<count($datatypes);$i++) {
+			$arr[] = $datatypes[$i];
+		}
+
+		$result = mysql_query("SELECT * FROM TABLES WHERE TABLE_SCHEMA = '".$db."'");
+		while ($row = mysql_fetch_array($result)) {
+			$table = $row["TABLE_NAME"];
+			$xml .= '<table name="'.$table.'">';
+			$comment = (isset($row["TABLE_COMMENT"]) ? $row["TABLE_COMMENT"] : "");
+			if ($comment) { $xml .= '<comment>'.$comment.'</comment>'; }
+
+			$q = "SELECT * FROM COLUMNS WHERE TABLE_NAME = '".$table."' AND TABLE_SCHEMA = '".$db."'";
+			$result2 = mysql_query($q);
+			while ($row = mysql_fetch_array($result2)) {
+				$name  = $row["COLUMN_NAME"];
+				$type  = $row["COLUMN_TYPE"];
+				$comment = (isset($row["COLUMN_COMMENT"]) ? $row["COLUMN_COMMENT"] : "");
+				$null = ($row["IS_NULLABLE"] == "YES" ? "0" : "1");
+				$def = $row["COLUMN_DEFAULT"];
+				$ai = (preg_match("/auto_increment/i",$row["EXTRA"]) ? "1" : "0");
+				if ($def == "NULL") { $def = ""; }
+				$xml .= '<row name="'.$name.'" null="'.$null.'" autoincrement="'.$ai.'">';
+				$xml .= '<datatype>'.strtoupper($type).'</datatype>';
+				$xml .= '<default>'.$def.'</default>';
+				if ($comment) { $xml .= '<comment>'.$comment.'</comment>'; }
+
+				/* fk constraints */
+				$q = "SELECT
+					REFERENCED_TABLE_NAME AS 'table', REFERENCED_COLUMN_NAME AS 'column'
+					FROM KEY_COLUMN_USAGE k
+					LEFT JOIN TABLE_CONSTRAINTS c
+					ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME
+					WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
+					AND c.TABLE_SCHEMA = '".$db."' AND c.TABLE_NAME = '".$table."'
+					AND k.COLUMN_NAME = '".$name."'";
+				$result3 = mysql_query($q);
+
+				while ($row = mysql_fetch_array($result3)) {
+					$xml .= '<relation table="'.$row["table"].'" row="'.$row["column"].'" />';
+				}
+
+				$xml .= '</row>';
+			}
+
+			/* keys */
+			$q = "SELECT * FROM STATISTICS WHERE TABLE_NAME = '".$table."' AND TABLE_SCHEMA = '".$db."' ORDER BY SEQ_IN_INDEX ASC";
+			$result2 = mysql_query($q);
+			$idx = array();
+
+			while ($row = mysql_fetch_array($result2)) {
+				$name = $row["INDEX_NAME"];
+				if (array_key_exists($name, $idx)) {
+					$obj = $idx[$name];
+				} else {
+					$type = $row["INDEX_TYPE"];
+					$t = "INDEX";
+					if ($type == "FULLTEXT") { $t = $type; }
+					if ($row["NON_UNIQUE"] == "0") { $t = "UNIQUE"; }
+					if ($name == "PRIMARY") { $t = "PRIMARY"; }
+
+					$obj = array(
+						"columns" => array(),
+						"type" => $t
+					);
+				}
+
+				$obj["columns"][] = $row["COLUMN_NAME"];
+				$idx[$name] = $obj;
+			}
+
+			foreach ($idx as $name=>$obj) {
+				$xml .= '<key name="'.$name.'" type="'.$obj["type"].'">';
+				for ($i=0;$i<count($obj["columns"]);$i++) {
+					$col = $obj["columns"][$i];
+					$xml .= '<part>'.$col.'</part>';
+				}
+				$xml .= '</key>';
+			}
+			$xml .= "</table>";
+		}
+		$arr[] = $xml;
+		$arr[] = '</sql>';
+		return implode("\n",$arr);
+	}
+?>
\ No newline at end of file