[Easymod-cvs] easymod2/dev mysql.php,NONE,1.1 sql_template_parser.php,NONE,1.1

[Eric F. <wg...@us...>]

Update of /cvsroot/easymod/easymod2/dev
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv31862/dev

Added Files:
	mysql.php sql_template_parser.php 
Log Message:
Mock-up of using a template for sql parsing

--- NEW FILE: mysql.php ---
<?php

$tpl['create'] = 'CREATE TABLE {NAME} ( {COLUMNS} );';
$tpl['create_columns'] = '{NAME} {TYPE} ({LENGTH}) {SIGNED} {NULL} {DEFAULT} {INCREMENT}';
$tpl['create_key'] = 'KEY {COLUMN1} ({COLUMN2})';
$tpl['create_primary_key'] = 'PRIMARY KEY ({COLUMN})';


//
// array(
//		'mysql_type' => 'db_type');
//
$types = array(
	'varchar'	=> 'varchar',
	'int'		=> 'int',
	'text'		=> 'text');

?>
--- NEW FILE: sql_template_parser.php ---
<?php

if ( $_POST['submit'] )
{
	$sql = new em_sql_parser;
	$sql->load_sql_template();
	$query = $sql->parse_sql($_POST['query']);

	echo 'The Query: ';
	vd($query);
}
else
{
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<textarea name="query"><?php echo $_POST['query']; ?></textarea>
<input type="submit" name="submit" value="Go" />
</form>
<?php
}

class em_sql_parser
{
	var $sql_tpls = array();
	var $sql_types = array();

	function load_sql_template()
	{
		global $phpbb_root_path, $phpEx;
		//$tpl_filename = $phpbb_root_path . 'admin/em_includes/schema/' . SQL_LAYER . '.' . $phpEx;
		$tpl_filename = 'mysql.php';
		include($tpl_filename);

		$this->sql_tpls = $tpl;
		$this->sql_types = $types;

		return;
	}

/*

CREATE TABLE mytable ( 
  FirstName varchar(40) NOT NULL DEFAULT '', 
  `LastName` varchar(40), 
  DateOfBirth int(11) NULL, 
  `SSN` int(11) UNSIGNED DEFAULT '0' auto_increment, 
  KEY DateOfBirth (DateOfBirth), 
  PRIMARY KEY (SSN)
);

ALTER TABLE `mytable` ADD
  nut_test int(8) NULL DEFAULT '0', 
  abool tinyint(1) ;

ALTER TABLE mytable ADD nut_test2 int(8) ; 

ALTER TABLE mytable MODIFY 
  nut_test varchar(10) NULL DEFAULT '' auto_increment;

ALTER TABLE mytable DROP nut_test ;

DROP TABLE mytable;
*/

//
// REQUIREMENTS
//
// - Each query end in ;
// - Queries start out for MySQL
//

	function parse_sql($query)
	{
		// explode the query so we can see whether it is CREATE, UPDATE, ALTER, etc
		$temp = explode(' ', $query);
		$type = $temp[0];
		unset($temp);

		$parsed_query = '';
		switch($type)
		{
			case 'CREATE':
				$parsed_query = $this->parse_sql_create($query);
				break;
			case 'ALTER':
				$parsed_query = $this->parse_sql_alter($query);
				break;
			// be careful with this
			case 'DROP':
				$parsed_query = $this->parse_sql_drop($query);
				break;
			// do these really need to be translated to other DB types?
			// maybe a separate case for DELETE so we can add extra precautions for it
			case 'INSERT':
			case 'UPDATE':
			case 'DELETE':
				$parsed_query = $query;
				break;
		}

		return $parsed_query;
	}

	function parse_sql_create($query)
	{
		preg_match('#CREATE TABLE (.*?) \((.*?)\);#s', $query, $matches);
		
		$parsed_query = str_replace('{NAME}', $matches[1], $this->sql_tpls['create']);
		
		$columns_array = explode(',', trim($matches[2]));
		$parsed_columns = array();
		foreach($columns_array as $column)
		{
			//$column = str_replace(',', '', $column);
			$temp = explode(' ', trim($column));

			// normal key
			if ( $temp[0] == 'KEY' )
			{
				$temp[2] = str_replace('(', '', str_replace(')', '', $temp[2]));
				$parsed_columns[] = str_replace('{COLUMN1}', $temp[1], str_replace('{COLUMN2}', $temp[2], $this->sql_tpls['create_key']));
			}
			// primary key
			else if ( $temp[0] == 'PRIMARY' )
			{
				$temp[2] = str_replace('(', '', str_replace(')', '', $temp[2]));
				$parsed_columns[] = str_replace('{COLUMN}', $temp[2], $this->sql_tpls['create_primary_key']);
			}
			// normal column to create
			else
			{
				// this will require some work in order to figure out what is what on each line
				$parsed_columns[] = str_replace('`', '', stripslashes(trim($column)));
			}
		}

		$parsed_query = str_replace('{COLUMNS}', implode(",\n", $parsed_columns), $parsed_query);

		return $parsed_query;
	}

	function parse_sql_alter($query)
	{

	}

	function parse_sql_drop($query)
	{

	}
}



function vd($data)
{
	echo '<pre>';
	var_dump($data);
	echo '</pre>';
}

?>