[Easymod-cvs] sql_parser/root/includes/sql sql_builder.php,1.2,1.3 sql_builder_msaccess.php,1.2,1.3 sql_builder_mssql.php,1.2,1.3 sql_builder_mysql.php,1.2,1.3 sql_builder_postgresql.php,1.2,1.3 sql_parser.php,1.2,1.3 sql_reserved_keywords.php,1.2,1.3

[Markus P. <mar...@us...>]

Update of /cvsroot/easymod/sql_parser/root/includes/sql
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv11076

Added Files:
	sql_builder.php sql_builder_msaccess.php sql_builder_mssql.php 
	sql_builder_mysql.php sql_builder_postgresql.php 
	sql_parser.php sql_reserved_keywords.php 
Log Message:
Trying to cleanup stuff caused by wrong import.

--- NEW FILE: sql_builder.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_builder.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * SQL Builder Class
 *
 * This class is aimed to provide common services to all builder classes.
 */
class sql_builder
{
	/**
	 * A pointer to the main parser object.
	 *
	 * @access public
	 */
	var $parser;

	/**
	 * Common indentation string for some kind of generated statements.
	 *
	 * @access public
	 */
	var $indent = '    ';

	/**
	 * Constructor
	 */
	function sql_builder(&$parser)
	{
		$this->parser = &$parser;
	}

	/**
	 * Generate a safe SQL identifier.
	 *
	 * @see class sql_parser
	 *
	 * @access public
	 */
	function get_identifier($identifier, $suffix = '')
	{
		return $this->parser->get_identifier($identifier, $suffix);
	}
}

?>
--- NEW FILE: sql_builder_msaccess.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_builder_msaccess.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * SQL Builder Class for Microsoft Access
 *
 * Fundamental Microsoft Jet SQL for Access 2000:
 * http://msdn.microsoft.com/library/en-us/dnacc2k/html/acfundsql.asp
 * Intermediate Microsoft Jet SQL for Access 2000:
 * http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp
 * Advanced Microsoft Jet SQL for Access 2000:
 * http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp
 *
 * SQL Reference for MS-Access 97:
 * http://msdn.microsoft.com/archive/en-us/office97/html/output/F1/D2/S5A318.asp
 *
 * Microsoft Jet SQL Reference:
 * http://www.devguru.com/Technologies/jetsql/quickref/statements.html
 */
class sql_builder_msaccess extends sql_builder
{
	/**
	 * Build a Column Definition.
	 * @TODO: Column Definition for MSACCESS
	 */
	function build_column_definition($column_data)
	{
		$column_definition = $column_data['name'] . ' ' . $column_data['datatype'];
		return $column_definition;
	}

	/**
	 * Build a CREATE TABLE statement.
	 * @TODO: CREATE TABLE for MSACCESS
	 *
	 * Reference:
	 * http://www.devguru.com/Technologies/jetsql/quickref/create_table.html
	 * http://msdn.microsoft.com/archive/en-us/office97/html/output/F1/D2/S5A320.asp
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function create_table(&$sql_data, &$sql_output)
	{
		$sql_output[] = 'CREATE TABLE ' . $sql_data['table_name'];
	}

	/**
	 * Build an ALTER TABLE statement.
	 * @TODO: ALTER TABLE for MSACCESS
	 *
	 * Reference:
	 * http://www.devguru.com/Technologies/jetsql/quickref/alter_table.html
	 * http://msdn.microsoft.com/archive/en-us/office97/html/output/F1/D2/S5A31A.asp
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function alter_table(&$sql_data, &$sql_output)
	{
		$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'];
	}

	/**
	 * Build a DROP TABLE statement.
	 *
	 * Reference:
	 * http://www.devguru.com/Technologies/jetsql/quickref/drop.html
	 * http://msdn.microsoft.com/archive/en-us/office97/html/output/F1/D2/S5A323.asp
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function drop_table(&$sql_data, &$sql_output)
	{
		$sql_output[] = 'DROP TABLE ' . $sql_data['table_name'];
	}
}

?>
--- NEW FILE: sql_builder_mysql.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_builder_mysql.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * SQL Builder Class for MySQL
 *
 * References:
 * http://dev.mysql.com/doc/mysql/en/data-definition.html
 */
class sql_builder_mysql extends sql_builder
{
	/**
	 * Translate the data type
	 */
	function translate_datatype($column_data)
	{
		$datatype = $column_data['datatype_name'];

		// BOOLEANs were added in MySQL 4.1.0
		// http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html
		// We'll use the synonym here for compatibility with older versions.
		if( $datatype == 'BOOLEAN' )
		{
			$datatype = 'TINYINT(1)';
			$column_data['unsigned'] = true;
		}
		return $datatype;
	}

	/**
	 * Build a Column Definition.
	 */
	function build_column_definition($column_data)
	{
		$column_definition = $column_data['name'] . ' ' . $this->translate_datatype($column_data);

		if( isset($column_data['unsigned']) )
		{
			$column_definition .= ' UNSIGNED';
		}
		if( isset($column_data['binary']) )
		{
			$column_definition .= ' BINARY';
		}
		if( isset($column_data['zerofill']) )
		{
			$column_definition .= ' ZEROFILL';
		}
		if( !empty($column_data['null']) )
		{
			$column_definition .= ' ' . $column_data['null'];
		}
		if( isset($column_data['default']) )
		{
			$column_definition .= ' DEFAULT ' . $column_data['default'];
		}
		if( isset($column_data['auto_increment']) )
		{
			$column_definition .= ' AUTO_INCREMENT';
		}
		return $column_definition;
	}

	/**
	 * Build a Primary Key Definition.
	 */
	function build_primary_key(&$sql_data)
	{
		$keys = array();
		for( $i = 0; $i < count($sql_data['primary_keys']); $i++ )
		{
			$key_data = &$sql_data['primary_keys'][$i];
			$key_order = ( $key_data['order'] != 'ASC' ? ' DESC' : '' );
			$keys[] = $key_data['name'] . $key_order;
		}
		return 'PRIMARY KEY (' . implode(', ', $keys) . ')';
	}

	/**
	 * Build an Index Definition.
	 */
	function build_index(&$sql_data, $index_data)
	{
		$keys = array();
		for( $i = 0; $i < count($index_data['keys']); $i++ )
		{
			$key_data = &$index_data['keys'][$i];
			$key_length = ( $key_data['length'] > 0 ? ('('.$key_data['length'].')') : '' );
			$key_order = ( $key_data['order'] != 'ASC' ? ' DESC' : '' );
			$keys[] = $key_data['name'] . $key_length . $key_order;
		}
		return ( $index_data['unique'] ? 'UNIQUE' : 'INDEX' ) . ' ' . $index_data['name'] . ' (' . implode(', ', $keys) . ')';
	}

	/**
	 * Build a CREATE TABLE statement.
	 *
	 * References:
	 * http://dev.mysql.com/doc/mysql/en/create-table.html
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function create_table(&$sql_data, &$sql_output)
	{
		$lines = array();
		foreach( $sql_data['columns'] as $column_data )
		{
			$lines[] = $this->indent . $this->build_column_definition($column_data);
		}
		if( count($sql_data['primary_keys']) > 0 )
		{
			$lines[] = $this->indent . $this->build_primary_key($sql_data);
		}
		foreach( $sql_data['indexes'] as $index_data )
		{
			$lines[] = $this->indent . $this->build_index($sql_data, $index_data);
		}
		$sql_output[] = 'CREATE TABLE ' . $sql_data['table_name'] . " (\n" . implode(",\n", $lines) . "\n)";
	}

	/**
	 * Build an ALTER TABLE statement.
	 *
	 * References:
	 * http://dev.mysql.com/doc/mysql/en/alter-table.html
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function alter_table(&$sql_data, &$sql_output)
	{
		$line = 'ALTER TABLE ' . $sql_data['table_name'] . ' ' . $sql_data['action'];
		switch( $sql_data['action'] )
		{
			case 'ADD':
				if( count($sql_data['primary_keys']) > 0 )
				{
					$line .= ' ' . $this->build_primary_key($sql_data);
				}
				elseif( count($sql_data['indexes']) > 0 )
				{
					$line .= ' ' . $this->build_index($sql_data, array_shift($sql_data['indexes']));
				}
				elseif( count($sql_data['columns']) > 0 )
				{
					$line .= ' ' . $this->build_column_definition(array_shift($sql_data['columns']));
				}
				break;

			case 'ALTER':
				$line .= ' COLUMN ' . $sql_data['column'];
				if( $sql_data['subaction'] == 'set_default' )
				{
					$line .= ' SET DEFAULT ' . $sql_data['default'];
				}
				else
				{
					$line .= ' DROP DEFAULT';
				}
				break;

			case 'CHANGE':
				$line .= ' COLUMN ' . $sql_data['old_column'] . ' ' .$this->build_column_definition(array_shift($sql_data['columns']));
				break;

			case 'MODIFY':
				$line .= ' COLUMN ' . $this->build_column_definition(array_shift($sql_data['columns']));
				break;

			case 'DROP':
				if( isset($sql_data['primary_key']) )
				{
					$line .= ' PRIMARY KEY';
				}
				elseif( isset($sql_data['index']) )
				{
					$line .= ' INDEX ' . $sql_data['index'];
				}
				elseif( isset($sql_data['column']) )
				{
					$line .= ' COLUMN ' . $sql_data['column'];
				}
				break;
		}
		$sql_output[] = $line;
	}

	/**
	 * Build a DROP TABLE statement.
	 *
	 * References:
	 * http://dev.mysql.com/doc/mysql/en/drop-table.html
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function drop_table(&$sql_data, &$sql_output)
	{
		$sql_output[] = 'DROP TABLE ' . $sql_data['table_name'];
	}
}

?>
--- NEW FILE: sql_reserved_keywords.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_reserved_keywords.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * Reserved Keywords for SQL Parser
 *
 * - Reserved keyword lookups are made in the order of this table.
 * - There are many duplicates here, though, the loader takes care of them.
 */
$reserved_keywords = array(
	'SQL_99' => array(
[...1295 lines suppressed...]
VARIABLES
VARYING
VIEW
WARNINGS
WEEK
WHEN
WHERE
WHILE
WITH
WORK
WRITE
X509
XA
XOR
YEAR
YEAR_MONTH
ZEROFILL
SQL_PARSER_END_OF_DATA;

?>
--- NEW FILE: sql_parser.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_parser.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * SQL References...
 *
 * MOD Template SQL Command Standard (by Nuttzy):
 * http://area51.phpbb.com/phpBB/viewtopic.php?f=25&t=15390
 *
 * MySQL:
 * http://dev.mysql.com/doc/mysql/en/index.html
[...1905 lines suppressed...]
	 *
	 * This method is just provided for convenience. Despite input is a filename
	 * we still return an array, so the caller can do whatever...
	 *
	 * @access public
	 * @param string File name, contents of one or more SQL statements.
	 * @param string Table Prefix in the target DB (optional).
	 * @return integer Return code (@see constants SQL_PARSER_*).
	 */
	function parse_file($filename, $table_prefix = false)
	{
		if( !($sql_stream = $this->read_file($filename)) )
		{
			return SQL_PARSER_ERROR | (count($this->warnings) > 0 ? SQL_PARSER_WARNINGS : 0);
		}
		return $this->parse_stream($sql_stream, $table_prefix);
	}
}

?>
--- NEW FILE: sql_builder_postgresql.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_builder_postgresql.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * SQL Builder Class for PostgreSQL
 *
 * References:
 * http://www.postgresql.org/docs/8.0/interactive/index.html
 * http://www.postgresql.org/docs/8.0/interactive/sql-commands.html
 */
class sql_builder_postgresql extends sql_builder
{
	/**
	 * Translate the data type
	 */
	function translate_datatype($column_data)
	{
		$datatype = $column_data['datatype_name'];

		$datatype_map = array(
			'TINYINT'		=> 'SMALLINT',
			'MEDIUMINT'		=> 'INTEGER',
			'BINARY'		=> 'BYTEA',
			'VARBINARY'		=> 'BYTEA',
			'TINYBLOB'		=> 'BYTEA',
			'BLOB'			=> 'BYTEA',
			'MEDIUMBLOB'	=> 'BYTEA',
			'LONGBLOB'		=> 'BYTEA'
		);
		if( isset($datatype_map[$datatype]) )
		{
			$datatype = $datatype_map[$datatype];
		}
		if( isset($column_data['binary']) )
		{
			$datatype = 'BYTEA';
		}
		if( in_array($datatype, array('SMALLINT','INTEGER','BIGINT')) )
		{
			// - PostgreSQL does not support the UNSIGNED attribute, therefore small
			//   integers (we'll never promote integers to BIGINT, which is a really huge
			//   data type) should promoted in some cases:
			//   a) Of course, SMALLINT UNSIGNED was specified and
			//   b) It hasn't been already promoted by datatype_map and
			//   c) No display width was specified or it is greater than the type capability.
			if( $datatype == 'SMALLINT' && isset($column_data['unsigned']) && !isset($datatype_map[$column_data['datatype_name']]) )
			{
				if( $column_data['datatype_argc'] == 0 || $column_data['datatype_argc'] > 6 )
				{
					$datatype = 'INTEGER';
				}
			}
			if( isset($column_data['auto_increment']) )
			{
				// PostgreSQL (at least, since 7.2) creates the sequence automatically when using SERIAL
				// See chapter "Serial Types":
				// http://www.postgresql.org/docs/8.0/interactive/datatype.html
				// http://www.postgresql.org/docs/7.2/interactive/datatype.html
				$datatype = ( $datatype == 'BIGINT' ? 'BIGSERIAL' : 'SERIAL' );
			}
		}
		else
		{
			if( $column_data['datatype_argc'] > 0 && in_array($datatype, array('DECIMAL','CHAR','VARCHAR')) )
			{
				$datatype .= '(' . implode(', ', $column_data['datatype_argv']) . ')';
			}
		}
		return $datatype;
	}

	/**
	 * Build a Column Definition.
	 *
	 * References:
	 * http://www.postgresql.org/docs/8.0/interactive/datatype.html
	 */
	function build_column_definition($column_data)
	{
		$column_definition = $column_data['name'] . ' ' . $this->translate_datatype($column_data);

		if( !isset($column_data['auto_increment']) )
		{
			if( !empty($column_data['null']) )
			{
				$column_definition .= ' ' . $column_data['null'];
			}
			if( isset($column_data['default']) )
			{
				$column_definition .= ' DEFAULT ' . $column_data['default'];
			}
		}
		return $column_definition;
	}

	/**
	 * Build a Primary Key Definition.
	 */
	function build_primary_key(&$sql_data)
	{
		$keys = array();
		for( $i = 0; $i < count($sql_data['primary_keys']); $i++ )
		{
			$key_data = &$sql_data['primary_keys'][$i];
			$key_order = ( $key_data['order'] != 'ASC' ? ' DESC' : '' );
			$keys[] = $key_data['name'] . $key_order;
		}
		$contraint_name = $this->get_identifier($sql_data['table_name'], '_pk');
		return 'CONSTRAINT ' . $contraint_name . ' PRIMARY KEY (' . implode(', ', $keys) . ')';
	}

	/**
	 * Build an Index Definition.
	 */
	function build_index(&$sql_data, $index_data)
	{
		$table_name = $sql_data['table_name'];
		$index_name = $this->get_identifier($sql_data['table_name'] . '_' . $index_data['name']);
		$keys = array();
		for( $i = 0; $i < count($index_data['keys']); $i++ )
		{
			$key_data = &$index_data['keys'][$i];
			$key_order = ( $key_data['order'] != 'ASC' ? ' DESC' : '' );
			$keys[] = $key_data['name'] . $key_order;
		}
		return 'CREATE' . ( $index_data['unique'] ? ' UNIQUE' : '' ) . ' INDEX ' . $index_name . ' ON ' . $table_name . ' (' . implode(', ', $keys) . ')';
	}

	/**
	 * Build a CREATE TABLE statement.
	 *
	 * References:
	 * http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function create_table(&$sql_data, &$sql_output)
	{
		$lines = array();
		foreach( $sql_data['columns'] as $column_data )
		{
			$lines[] = $this->indent . $this->build_column_definition($column_data);
		}
		if( count($sql_data['primary_keys']) > 0 )
		{
			$lines[] = $this->indent . $this->build_primary_key($sql_data);
		}
		foreach( $sql_data['columns'] as $column_data )
		{
			if( isset($column_data['unsigned']) )
			{
				$lines[] = $this->indent . 'CHECK (' . $column_data['name'] . '>=0)';
			}
		}
		$sql_output[] = 'CREATE TABLE ' . $sql_data['table_name'] . " (\n" . implode(",\n", $lines) . "\n)";

		foreach( $sql_data['indexes'] as $index_data )
		{
			$sql_output[] = $this->build_index($sql_data, $index_data);
		}
	}

	/**
	 * Build an ALTER TABLE statement.
	 *
	 * References:
	 * http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html
	 * http://www.postgresql.org/docs/7.4/interactive/sql-altertable.html
	 * http://www.postgresql.org/docs/7.3/interactive/sql-altertable.html
	 * http://www.postgresql.org/docs/7.2/interactive/sql-altertable.html
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function alter_table(&$sql_data, &$sql_output)
	{
		switch( $sql_data['action'] )
		{
			case 'ADD':
				if( count($sql_data['primary_keys']) > 0 )
				{
					$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ADD ' . $this->build_primary_key($sql_data);
				}
				elseif( count($sql_data['indexes']) > 0 )
				{
					$sql_output[] = $this->build_index($sql_data, array_shift($sql_data['indexes']));
				}
				elseif( count($sql_data['columns']) > 0 )
				{
					// Note: DEFAULT and NOT NULL clausules in the same ALTER ADD COLUMN
					// statement were not supported until PostgreSQL 8.0
					$column_data = array_shift($sql_data['columns']);
					$column_name = $column_data['name'];
					if( isset($column_data['default']) )
					{
						$set_default = $column_data['default'];
						unset($column_data['default']);
					}
					if( isset($column_data['null']) )
					{
						if( $column_data['null'] == 'NOT NULL' )
						{
							$set_null = ' SET NOT NULL';
						}
						unset($column_data['null']);
					}
					$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ADD COLUMN ' . $this->build_column_definition($column_data);
					if( isset($set_default) )
					{
						$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $set_default;
					}
					if( isset($set_null) )
					{
						if( !isset($set_default) )
						{
							$set_default = ( strstr('BIFD', $column_data['constant_type']) ? 0 : "''" );
						}
						$sql_output[] = 'UPDATE TABLE ' . $sql_data['table_name'] . " SET $column_name = $set_default WHERE $column_name IS NULL";
						$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ALTER COLUMN ' . $column_name . $set_null;
					}

					if( isset($column_data['unsigned']) )
					{
						$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ADD CHECK (' . $column_data['name'] . '>=0)';
					}
				}
				break;

			case 'ALTER':
				$line = 'ALTER TABLE ' . $sql_data['table_name'] . ' ALTER COLUMN ' . $sql_data['column'];
				if( $sql_data['subaction'] == 'set_default' )
				{
					$line .= ' SET DEFAULT ' . $sql_data['default'];
				}
				else
				{
					$line .= ' DROP DEFAULT';
				}
				$sql_output[] = $line;
				break;

			case 'CHANGE':
			case 'MODIFY':
				$column_data = array_shift($sql_data['columns']);
				$column_name = $column_data['name'];
				$datatype = $this->translate_datatype($column_data);
				if( $sql_data['action'] == 'CHANGE' )
				{
					$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' RENAME COLUMN ' . $sql_data['old_column'] . ' TO ' . $column_name;
				}
				$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ALTER COLUMN ' . $column_name . ' TYPE ' . $datatype;
				$line = 'ALTER TABLE ' . $sql_data['table_name'] . ' ALTER COLUMN ' . $column_name;
				if( isset($column_data['default']) )
				{
					$line .= ' SET DEFAULT ' . $column_data['default'];
				}
				else
				{
					$line .= ' DROP DEFAULT';
				}
				$sql_output[] = $line;
				$set_drop = ( empty($column_data['null']) || $column_data['null'] == 'NULL' ? 'DROP' : 'SET' );
				if( $set_drop == 'SET' && isset($column_data['default']) )
				{
					$sql_output[] = 'UPDATE TABLE ' . $sql_data['table_name'] . " SET $column_name = " . $column_data['default'] . " WHERE $column_name IS NULL";
				}
				$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ALTER COLUMN ' . $column_name . ' ' . $set_drop . ' NOT NULL';

				if( isset($column_data['unsigned']) )
				{
					$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' ADD CHECK (' . $column_data['name'] . '>=0)';
				}
				break;

			case 'DROP':
				if( isset($sql_data['primary_key']) )
				{
					$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' DROP CONSTRAINT ' . $sql_data['table_name'] . '_pkey';
				}
				elseif( isset($sql_data['index']) )
				{
					$sql_output[] = 'DROP INDEX ' . $sql_data['index'];
				}
				elseif( isset($sql_data['column']) )
				{
					// Note: DROP COLUMN was added in 7.3, and I guess we can't easilly offer support for
					// previous (legacy :P ;) versions, where the way to deal with this kind of operations
					// would, more or less, look like this:
					// 1) CREATE TABLE temp AS SELECT * FROM target_table;
					// 2) DROP TABLE target_table;
					// 3) CREATE TABLE target_table (column_definition[,...]);
					// 4) INSERT INTO target_table SELECT * FROM temp;
					// 5) DROP TABLE temp;
					// The problem here is we don't have the required information to build 3rd step, and
					// the CREATE TABLE a LIKE b; statement was not implemented until version 7.4, so the
					// only possible automation here would be to read all table attributes from the server
					// and dynamically build the column definitions, which looks like a bit too heavy.
					$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'] . ' DROP COLUMN ' . $sql_data['column'] . ' CASCADE';
				}
				break;
		}
	}

	/**
	 * Build a DROP TABLE statement.
	 *
	 * References:
	 * http://www.postgresql.org/docs/8.0/interactive/sql-droptable.html
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function drop_table(&$sql_data, &$sql_output)
	{
		$sql_output[] = 'DROP TABLE ' . $sql_data['table_name'];
	}
}

?>
--- NEW FILE: sql_builder_mssql.php ---
<?php
/**
 *
 * @package SQL Parser
 * @version $Id: sql_builder_mssql.php,v 1.3 2005/09/26 09:59:13 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */


/**
 * SQL Builder Class for Microsoft SQL Server
 *
 * References:
 * http://msdn.microsoft.com/library/en-us/dnanchor/html/sqlserver2000.asp
 *
 * Known things that may need revision by the end-user:
 * - The [PRIMARY] "filegroup" is specified for all ON and TEXTIMAGE_ON clausules generated.
 *   The user might want to adapt this clause to its own particular needs (big database, etc).
 *   More information on "filegroups" can be found here:
 *   > Creating and Maintaining Databases -> Files and Filegroups:
 *     http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_6epf.asp
 *   > SQL Server Arquitecture -> Physical Database Files and Filegroups:
 *     http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_9sab.asp
 *   > Optimizing Database Performance -> Data Placement using Filegroups:
 *     http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_2upf.asp
 */
class sql_builder_mssql extends sql_builder
{
	/**
	 * Translate the data type
	 */
	function translate_datatype($column_data)
	{
		$datatype = $column_data['datatype_name'];

		$datatype_map = array(
			// http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_6r3g.asp
			'FLOAT'				=> 'REAL',
			'DOUBLE PRECISION'	=> 'FLOAT',
			// http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_3ss4.asp
			'MEDIUMINT'			=> 'INTEGER',
			// http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_9rfp.asp
			'TINYBLOB'			=> 'IMAGE',
			'BLOB'				=> 'IMAGE',
			'MEDIUMBLOB'		=> 'IMAGE',
			'LONGBLOB'			=> 'IMAGE'
		);
		if( isset($datatype_map[$datatype]) )
		{
			$datatype = $datatype_map[$datatype];
		}
		if( isset($column_data['binary']) )
		{
			$datatype = ( $datatype == 'CHAR' ? 'BINARY' : 'VARBINARY' );
		}
		if( in_array($datatype, array('TINYINT','SMALLINT','INTEGER','BIGINT')) )
		{
			// - MS-SQL Server does not support signed TINYINTs.
			// - MS-SQL Server does not support MEDIUMINTs.
			// - MS-SQL Server does not support the UNSIGNED attribute, therefore small
			//   integers (we'll never promote integers to BIGINT, which is a really huge
			//   data type) should be promoted in some cases:
			//   a) Of course, SMALLINT UNSIGNED was specified and
			//   b) No display width was specified or it is greater than the type capability.
			if( $datatype == 'TINYINT' && !isset($column_data['unsigned']) )
			{
				$datatype = 'SMALLINT';
			}
			elseif( $datatype == 'SMALLINT' && isset($column_data['unsigned']) && ( $column_data['datatype_argc'] == 0 || $column_data['datatype_argc'] > 6 ) )
			{
				$datatype = 'INTEGER';
			}
		}
		else
		{
			if( $column_data['datatype_argc'] > 0 && in_array($datatype, array('DECIMAL','CHAR','VARCHAR','BINARY','VARBINARY')) )
			{
				$datatype .= '(' . implode(', ', $column_data['datatype_argv']) . ')';
			}
		}
		return $datatype;
	}

	/**
	 * Build a Column Definition.
	 *
	 * References:
	 * http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp
	 */
	function build_column_definition($column_data)
	{
		$column_definition = $column_data['name'] . ' ' . $this->translate_datatype($column_data);

		if( !empty($column_data['null']) )
		{
			$column_definition .= ' ' . $column_data['null'];
		}
		if( isset($column_data['default']) )
		{
			$column_definition .= ' DEFAULT (' . $column_data['default'] . ')';
		}
		if( isset($column_data['auto_increment']) )
		{
			$column_definition .= ' IDENTITY(1, 1)';
		}
		return $column_definition;
	}

	/**
	 * Build a Primary Key Definition.
	 */
	function build_primary_key(&$sql_data)
	{
		$keys = array();
		for( $i = 0; $i < count($sql_data['primary_keys']); $i++ )
		{
			$key_data = &$sql_data['primary_keys'][$i];
			$key_order = ( $key_data['order'] != 'ASC' ? ' DESC' : '' );
			$keys[] = $key_data['name'] . $key_order;
		}
		$contraint_name = $this->get_identifier($sql_data['table_name'], '_pk');
		return 'CONSTRAINT ' . $contraint_name . ' PRIMARY KEY (' . implode(', ', $keys) . ') ON [PRIMARY]';
	}

	/**
	 * Build an Index Definition.
	 */
	function build_index(&$sql_data, $index_data)
	{
		$table_name = $sql_data['table_name'];
		$index_name = $this->get_identifier($sql_data['table_name'] . '_' . $index_data['name']);
		$keys = array();
		for( $i = 0; $i < count($index_data['keys']); $i++ )
		{
			$key_data = &$index_data['keys'][$i];
			$key_order = ( $key_data['order'] != 'ASC' ? ' DESC' : '' );
			$keys[] = $key_data['name'] . $key_order;
		}
		return 'CREATE' . ( $index_data['unique'] ? ' UNIQUE' : '' ) . ' INDEX ' . $index_name . ' ON ' . $table_name . ' (' . implode(', ', $keys) . ') ON [PRIMARY]';
	}

	/**
	 * Build a CREATE TABLE statement.
	 *
	 * References:
	 * http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function create_table(&$sql_data, &$sql_output)
	{
		$on_textimage = false;
		$lines = array();
		foreach( $sql_data['columns'] as $column_data )
		{
			if( in_array($column_data['datatype_name'], array('TEXT')) )
			{
				$on_textimage = true;
			}
			$lines[] = $this->indent . $this->build_column_definition($column_data);
		}
		if( count($sql_data['primary_keys']) > 0 )
		{
			$lines[] = $this->indent . $this->build_primary_key($sql_data);
		}
		foreach( $sql_data['columns'] as $column_data )
		{
			if( isset($column_data['unsigned']) )
			{
				$lines[] = $this->indent . 'CHECK (' . $column_data['name'] . '>=0)';
			}
		}
		$filegroups = ' ON [PRIMARY]' . ( $on_textimage ? ' TEXTIMAGE_ON [PRIMARY]' : '' );
		$sql_output[] = 'CREATE TABLE ' . $sql_data['table_name'] . " (\n" . implode(",\n", $lines) . "\n) $filegroups";

		foreach( $sql_data['indexes'] as $index_data )
		{
			$sql_output[] = $this->build_index($sql_data, $index_data);
		}
	}

	/**
	 * Build an ALTER TABLE statement.
	 *
	 * References:
	 * http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_3ied.asp
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function alter_table(&$sql_data, &$sql_output)
	{
		// @TODO: ALTER TABLE for MSSQL
//		$sql_output[] = 'ALTER TABLE ' . $sql_data['table_name'];
	}

	/**
	 * Build a DROP TABLE statement.
	 *
	 * References:
	 * http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_7uud.asp
	 *
	 * @access public
	 * @param array Specifications to build the statement.
	 * @param array One or more formatted SQL statements.
	 */
	function drop_table(&$sql_data, &$sql_output)
	{
		$sql_output[] = 'DROP TABLE ' . $sql_data['table_name'];
	}
}

?>