<?php
// +----------------------------------------------------------------+
// | txtSQL                                            Version 4.0a |
// +----------------------------------------------------------------+
// | This program is free software; you can redistribute it and/or  |
// | modify it under the terms of the GNU General Public License    |
// | as published by the Free Software Foundation; either version 2 |
// | of the License, or (at your option) any later version.         |
// |                                                                |
// | This program is distributed in the hope that it will be useful |
// | but WITHOUT ANY WARRANTY; without even the implied warranty of |
// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the  |
// | GNU General Public License for more details.                   |
// |                                                                |
// | You should have received a copy of the GNU General Public      |
// | License along with this program; if not, write to the Free     |
// | Software                                                       |
// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA      |
// | 02111-1307                                                     |
// | USA.                                                           |
// +----------------------------------------------------------------+
// | @author Faraz Ali <FarazAli at gmail dot com>                  |
// | @tab-indent 4 spaces                                           |
// | @font-size fixed width                                         |
// +----------------------------------------------------------------+

// Load our lexer which allows us to parse SQL queries
require_once ( TXTSQL_LEXER_PATH . '/txtSQL.lexer.php' );

/**
 * The core of the txtSQL engine; a significant amount of the work
 * happens within this class
 *
 * @package txtSQL_Core::txtSQL_Lexer
 * @author Faraz Ali <FarazAli at gmail dot com>
 * @version 4.0.0 alpha
 * @access public
 */
class txtSQL_Core extends txtSQL_Lexer
{
	/**
	 * Selects the rows from a table that fit certain credentials
	 *
	 * @param mixed $arg An array holding the arguments
	 * @return mixed $results The selected rows
	 * @access private
	 */
	function _select ( $arg )
	{
		// Initialize some variables
		$results     = array();
		$tables      = array();
		$defs        = array();
		$found       = 0;
		$added       = 0;
		$filename    = '';
		$exec_tail   = '';
		$exec        = '';
		$conditional = true;

		// Check for a valid tables
		foreach ( $arg['tables'] as $table => $alias )
		{
			// Look for a database/table name
			if ( strpos($table, '.') )
			{
				list ( $db, $table, ) = explode ( '.', $table );

				if ( $this->select_db ( $db ) === false )
				{
					return false;
				}
			}

			// See if a database is specified
			if ( $this->_selected_db == '' )
			{
				return $this->_raise_error('No database selected', E_USER_NOTICE);
			}

			// Read in the table
			$filename        = implode('/', array( $this->_data_path, $this->_selected_db, $table ));
			$defs [ $table ] = $this->_read_file($filename . '.FRM');
			$rows [ $table ] = $this->_read_file($filename . '.MYD');

			if ( ( $defs[ $table ] === false ) || ( $rows[ $table ] === false ) )
			{
				return $this->_raise_error('Error reading table `' . $this->_selected_db . '.' . $table . '` in', E_USER_WARNING);
			}
		}

		// Keep track of multiple tables
		$indices_rev = array_keys($defs);
		$indices     = array_flip($indices_rev);

		// Compile the WHERE clause (if it is specified)
		if ( isset($arg['where']) )
		{
			if ( ( $conditional = $this->_compile($arg['where'], $defs, $indices) ) === false )
			{
				return false;
			}
		}

		// Implement Inner-Join
		if ( !isset($arg['join']) || strtolower($arg['join']) == 'inner' )
		{
			if ( $this->_compile_inner_join($exec, $exec_tail, $conditional, $arg, $defs, $indices_rev) === false )
			{
				return false;
			}
		}

		// Implement Outer-Joins
		else if ( in_array( $arg['join'], array('left', 'right') ) )
		{
			if ( $this->_compile_outer_join ( $arg['join'], &$exec, &$exec_tail, $conditional, $arg, $defs, $indices_rev ) === false )
			{
				return false;
			}
		}

		// Natural Joins
		// Cross Joins
		die($exec);

		return ( $this->_results = $results );
	}

	/**
	 * Inserts (a) row(s) of data into a table
	 *
	 * @param mixed $arg An array holding the arguments
	 * @return int $inserted How many rows were successfully inserted
	 * @access private
	 */
	function _insert ( $arg )
	{
		// Check for a database
		if ( isset($arg['db']) )
		{
			// Select it
			if ( !$this->select_db($arg['db']) )
			{
				return false;
			}
		}

		// Check for a selected database
		if ( trim($this->_selected_db) == '' )
		{
			return $this->_raise_error('No database selected', E_USER_NOTICE);
		}

		// Check for a valid table
		if ( !$this->table_exists($arg['table']) )
		{
			return $this->_raise_error('Table `' . $arg['table'] . '` doesn\'t exist', E_USER_NOTICE);
		}

		// Read in the table
		$filename = implode( '/', array ( $this->_data_path, $this->_selected_db, $arg['table'] ) );
		$defs     = $this->_read_file ( $filename . '.FRM' );
		$rows     = $this->_read_file ( $filename . '.MYD' );

		// Check for any errors while reading in tables
		if ( ( $defs === false ) || ( $rows === false ) )
		{
			return $this->_raise_error('Error reading table `' . $this->_selected_db . '.' . $arg['table'] . '` in', E_USER_NOTICE);
		}

		// Create a blank template of the row ( a model row of the table )
		$model    = array();
		$auto     = array();
		$sentinel = 0;

		foreach ( $defs as $key => $value )
		{
			$val =  '';

			if ( !empty($value['default']) )
			{
				$val = @$value['default'];
			}

			if ( $key == 'primary' )
			{
				continue;
			}

			// Look for auto_increment columns and create lookup table
			if ( $value['auto_increment'] === true )
			{
				$val = $auto[$key] = $value['autocount'];
			}

			// Check for 'date' columns
			if ( $value['type'] == 'date' )
			{
				$val = time();
			}

			$model [ $key ] = $val;
		}

		// Run through each row of data
		foreach ( $arg['values'] as $key => $entry )
		{
			// Initialize some vars
			$template = $model;
			$counter  = 0;

			// Increment all the auto fields
			foreach ( $auto as $field => $index )
			{
				$counter++;

				$template [ $field ]              = $index;     // Increment this field's value
				$defs     [ $field ]['autocount'] = $index + 1; // Update table's auto count
				$auto     [ $field ]              = $index + 1; // Update temp. count
			}

			// Check for invalid types
			foreach ( $entry as $col => $value )
			{
				switch ( $defs[$col]['type'] )
				{
					// Check value against possible ENUM values
					case 'enum' :
					{
						// Grab possible enum values and check against them
						if ( !in_array($value, $defs[ $col ][ 'enum_val' ]) )
						{
							$value = $defs[ $col ][ 'default' ];
						}
					}

					// String and text fields
					case 'string' :
					case 'text'   :
					{
						$template [ $col ] = $value;

						break;
					}

					// Integers
					case 'int' :
					{
						$template [ $col ] = ( int ) $value;

						break;
					}

					// Floats
					case 'float' :
					{
						$template [ $col ] = ( float ) $value;

						break;
					}
				}
			}

			$rows[] = array_values($template);
			$sentinel++;
		}

		// We will return the number of rows added on success
		$this->_results = $sentinel;

		// Write files to disk
		if ( $this->_write_file( $filename . '.FRM', 'w', serialize($defs) ) !== false )
		{
			if ( $this->_write_file( $filename . '.MYD', 'w', serialize($rows) ) !== false )
			{
				// Save the table information into the cache
				$this->_cache [ $filename . '.FRM' ] = $defs;
				$this->_cache [ $filename . '.MYD' ] = $rows;

				return $this->_results;
			}
		}

		return false;
	}

	/**
	 * Deletes rows from a table that fit a certain credential
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return int $deleted How many rows were successfully deleted
	 * @access private
	 */
	function _delete ( $arg )
	{
		$this->_results = true;
	}

	/**
	 * Updates rows in a table with new values
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return int $updated How many rows were successfully updated
	 * @access private
	 */
	function _update ( $arg )
	{
		$this->_results = true;
	}

	/**
	 * Creates a new database
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return bool
	 * @access private
	 */
	function _create_database ( $arg )
	{
		// Check for a valid database name
		if ( !isset($arg['db']) )
		{
			return $this->_raise_error('Invalid db name', E_USER_NOTICE);
		}

		// Make sure database doesn't already exist
		if ( $this->db_exists($arg['db']) )
		{
			return $this->_raise_error('Database `' . $arg['db'] . '` already exists', E_USER_NOTICE);
		}

		// Create the database folder and return
		$filename = implode ( '/', array($this->_data_path, $arg['db']) );

		if ( @mkdir($filename) === true )
		{
			$this->_results = true;

			return true;
		}

		// Something went wrong
		return $this->_raise_error('Error creating database `' . $arg['db'] . '`', E_USER_NOTICE);
	}

	/**
	 * Creates a new table
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return bool
	 * @access private
	 */
	function _create_table ( $arg )
	{
		// Check to see if a database was specified by the user
		if ( isset($arg['db']) )
		{
			// Select the database
			if ( $this->select_db($arg['db']) === false )
			{
				return false;
			}
		}

		// Check for a selected database
		if ( trim(@$this->_selected_db) == '' )
		{
			return $this->_raise_error('No database selected', E_USER_NOTICE);
		}

		// Make sure that the databse isn't locked
		if ( $this->db_is_locked(@$this->_selected_db) === true )
		{
			return $this->_raise_error('Database `' . $this->_selected_db . '` is locked', E_USER_NOTICE);
		}

		// Check that table doesn't already exist
		if ( $this->table_exists(@$arg['table']) )
		{
			return $this->_raise_error('Table `' . implode ( '.', array($this->_selected_db, $arg['table']) ) . '` already exists', E_USER_NOTICE);
		}

		// Generate table schema and serialize the table
		if ( ( $arg['cols'] = $this->_generate_schema($arg['cols']) ) === false )
		{
			return false;
		}

		$definitions = serialize($arg['cols']);
		$records     = serialize(array());
		$filename    = implode( '/', array( $this->_data_path, $this->_selected_db, $arg['table'] ) );

		// Create the definition file and the records file
		if ( $this->_write_file ( $filename . ".FRM", 'w', $definitions ) === true )
		{
			if ( $this->_write_file ( $filename . ".MYD", 'w', $records ) === true )
			{
				// Save files to cache
				$this->_cache [ $filename . '.FRM' ] = $arg['cols'];
				$this->_cache [ $filename . '.MYD' ] = array();
				$this->_results                      = true;

				return true;
			}

			// Remove the definitions file because the .MYD data file wasn't created
			@unlink ( $filename . '.FRM.' );
		}

		return $this->_raise_error('Error when trying to create table `' . implode ( '.', array($this->_selected_db, $arg['table']) ) . '`', E_USER_NOTICE);
	}

	/**
	 * Drops a database permanently
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return bool
	 * @access private
	 */
	function _drop_database ( $arg )
	{
		// Check to see if a database was specified by the user
		if ( isset($arg['db']) )
		{
			// Select the database
			if ( $this->select_db($arg['db']) === false )
			{
				return false;
			}
		}

		// Check for a selected database
		if ( trim(@$this->_selected_db) == '' )
		{
			return $this->_raise_error('No database selected', E_USER_NOTICE);
		}

		// Make sure that the databse isn't locked
		if ( $this->db_is_locked(@$this->_selected_db) === true )
		{
			return $this->_raise_error('Database `' . $this->_selected_db . '` is locked', E_USER_NOTICE);
		}

		// Go through each table inside of the database folder
		$filename = implode ( '/', array($this->_data_path, $arg['db']) );

		// Try to obtain a file pointer for the db folder
		if ( ( $fp = @opendir($filename) ) !== false )
		{
			// Loop through all of the files in the db folder
			while ( ( $file = @readdir($fp) ) !== false )
			{
				if ( ( $file != "." ) && ( $file != ".." ) )
				{
					// We can only delete files
					if ( is_dir( $filename . '/' . $file ) || !@unlink( $filename . '/'. $file ) )
					{
						return $this->_raise_error('Coulds not delete database `' . $arg['db'] . '`', E_USER_NOTICE);
					}
				}
			}

			// Close the file pointer
			@closedir($fp);

			// Now we have to delete the actual database folder
			if ( @rmdir ( $filename ) === true )
			{
				$this->_results = true;

				return true;
			}
		}

		return $this->_raise_error('Could not delete database `' . $arg['db'] . '`', E_USER_NOTICE);
	}

	/**
	 * Drops a table permanently
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return bool
	 * @access private
	 */
	function _drop_table ( $arg )
	{
		// Check to see if a database was specified by the user
		if ( isset($arg['db']) )
		{
			// Select the database
			if ( $this->select_db($arg['db']) === false )
			{
				return false;
			}
		}

		// Check for a selected database
		if ( trim(@$this->_selected_db) == '' )
		{
			return $this->_raise_error('No database selected', E_USER_NOTICE);
		}

		// Make sure that the databse isn't locked
		if ( $this->db_is_locked(@$this->_selected_db) === true )
		{
			return $this->_raise_error('Database `' . $this->_selected_db . '` is locked', E_USER_NOTICE);
		}

		// Check that the table exist
		if ( $this->table_exists(@$arg['table']) === false )
		{
			return $this->_raise_error('Table `' . implode ( '.', array($this->_selected_db, $arg['table']) ) . '` doesn\'t exist', E_USER_NOTICE);
		}

		// Drop the table from the filesystem
		$filename = implode ( '/', array($this->_data_path, $this->_selected_db, $arg['table']) );

		if ( @unlink($filename . '.FRM') === true )
		{
			if ( @unlink($filename . '.MYD') === true )
			{
				$this->_results = true;

				return true;
			}
		}

		return $this->_raise_error('Error when trying to remove table `' . implode ( '.', array($this->_selected_db, $arg['table']) ) . '` from filesystem', E_USER_NOTICE);
	}

	/**
	 * Alters a table's field-definitions
	 *
	 * @param mixed $arg An array containing the arguments
	 * @return bool
	 * @access private
	 */
	function _alter_table ( $arg )
	{
		// Check to see if a database was specified by the user
		if ( isset($arg['db']) )
		{
			// Select the database
			if ( $this->select_db($arg['db']) === false )
			{
				return false;
			}
		}

		// Check for a selected database
		if ( trim(@$this->_selected_db) == '' )
		{
			return $this->_raise_error('No database selected', E_USER_NOTICE);
		}

		// Make sure that the databse isn't locked
		if ( $this->db_is_locked(@$this->_selected_db) === true )
		{
			return $this->_raise_error('Database `' . $this->_selected_db . '` is locked', E_USER_NOTICE);
		}

		// Check that the table exist
		if ( $this->table_exists(@$arg['table']) === false )
		{
			return $this->_raise_error('Table `' . implode ( '.', array($this->_selected_db, $arg['table']) ) . '` doesn\'t exist', E_USER_NOTICE);
		}

		// Is the table locked?
		if ( $this->table_is_locked($arg['table']) === true )
		{
			return $this->_raise_error('Table `' . implode( '.', array($this->_selected_db, $arg['table']) ) . '` is locked', E_USER_NOTICE);
		}

		// Read in the definition files
		$filename    = implode ( '/', array($this->_data_path, $this->_selected_db, $arg['table']) );
		$definitions = $this->_read_file ( $filename . '.FRM' );

		// Check for a valid command
		if ( !in_array( strtolower(@$arg['action']), array( 'add', 'modify', 'move', 'rename' ) ) )
		{
			return $this->_raise_error('Invalid command, found `' . @$arg['action'] . '`', E_USER_NOTICE);
		}

		// Check that the column exists
		if ( ( strtolower(@$arg['action']) != 'add' ) && !isset($definitions[ $arg['col'] ]) )
		{
			return $this->_raise_error('Field, `' . @$arg['col'] . '`, does not exist', E_USER_NOTICE);
		}

		// Create an array of indices for locating field positions
		$indices = array_flip(array_keys($definitions));

		// Perform the corressponding command
		switch ( strtolower($arg['action']) )
		{
			// Add a field into a table
			case 'add' :
			{
				// Check if there was a position specified
				if ( isset($arg['where']) )
				{
					switch ( strtolower($arg['where']) )
					{
						// Add the field before or after a certain field
						case 'after'     :
						case 'before'    :
						{
							if ( !isset($definitions[ $arg['position'] ]) )
							{
								return $this->_raise_error('Field `' . $arg['position'] . '` doesn\'t exist', E_USER_NOTICE);
							}

							// Initialize some variables
							$count   = 0;
							$index   = $indices[ $arg['position'] ];
							$rows    = $this->_read_file ( $filename . '.MYD' );
							$new_def = array();

							if ( $arg['where'] == 'after' )
							{
								$index += 1;
							}

							// Loop through the definitions
							$tmp = array();

							foreach ( $definitions as $key => $value )
							{
								if ( $count == $index )
								{
									$new_def [ $arg['col'] ] = $arg['new_col'][ $arg['col'] ];
								}

								$new_def [ $key ] = $definitions [ $key ];

								$count++;
							}

							// Now add the new field to the rows
							$tmp = array();

							foreach ( $rows as $key => $value )
							{
								$tmp1 = array();

								foreach ( $value as $key1 => $value1 )
								{
									if ( $key1 == ( ( strtolower($arg['where']) == 'before' ) ? $index + 1: $index ) )
									{
										$tmp1[] = $arg['new_col'][ $arg['col'] ]['default'];
									}

									$tmp1[] = $value1;
									
								}

								$tmp[] = $tmp1;
 							}

							$rows = $tmp;

							break;
						}

						// Add to the end
						case 'end'       :
						{
							$rows    = $this->_read_file ( $filename . '.MYD' );
							$new_def = $definitions + $arg [ 'new_col' ];

							// Now rearrange the fields in the data file
							// Now add the new field to the rows
							foreach ( $rows as $key => $value )
							{
								$rows[ $key ][] = $arg['new_col'][ $arg['col'] ]['default'];
 							}

							break;
						}

						// Add to the beginning
						case 'beginning' :
						{
							$rows    = $this->_read_file ( $filename . '.MYD' );
							$new_def = $arg [ 'new_col' ] + $definitions;

							// Now rearrange the fields in the data file
							$tmp = array();

							foreach ( $rows as $key => $value )
							{
								$tmp1 = array();

								foreach ( $value as $key1 => $value1 )
								{
									if ( $key1 == 0 )
									{
										$tmp1[] = $arg[ 'new_col' ][ $arg['col'] ]['default'];
									}

									$tmp1[] = $value1;
								}

								$tmp[] = $tmp1;
							}

							$rows = $tmp;

							break;
						}
					}
				}

				break;
			}

			// Edit an already existing field
			case 'modify' :
			{
				// Add in the new definitions
				$new_def                = $definitions;
				$new_def[ $arg['col'] ] = $arg['new_col'][ $arg['col'] ];

				break;
			}

			// Move a field's index up or down
			case 'move'   :
			{
				// Check if the field exists
				if ( !isset($definitions[ $arg['col'] ]) )
				{
					return $this->_raise_error('Field `' . $arg['col'] . '` doesn\'t exist', E_USER_NOTICE);
				}

				// Check for a valid position
				if ( !isset($arg['where']) )
				{
					return $this->_raise_error('No position specified to move column, `' . $arg['col'] . '`, to', E_USER_NOTICE);
				}

				else if ( !isset($definitions[ $arg['position'] ]) )
				{
					return $this->_raise_error('Column, `' . $arg['position'] . '` doesn\'t exist');
				}

				switch ( strtolower($arg['where']) )
				{
					// Move the field to the end
					case 'end' :
					{
						// Create a backup of the field
						$field = $definitions[ $arg['col'] ];

						// Remove the field and add it to the end
						unset($definitions[ $arg['col'] ]);

						$definitions[ $arg['col'] ] = $field;

						// Now move the field for the records
						$rows = $this->_read_file ( $filename . '.MYD' );

						foreach ( $rows as $key => $value )
						{
							$val            = $value[ $indices[ $arg['col'] ] ];

							unset($rows[ $key ][ $indices[ $arg['col'] ] ] );

							$rows[ $key ][] = $val;
						}

						$new_def = $definitions;

						break;
					}

					// Move the field to the beginning
					case 'beginning' :
					{
						// Create a backup of the field
						$field       = $definitions[ $arg['col'] ];

						// Remove the field and add it to the beginning
						unset ( $definitions[ $arg['col'] ] );

						$new_def  = array( $arg['col'] => $field ) + $definitions;

						// Now move the field for the all of the records
						$rows     = $this->_read_file ( $filename . '.MYD' );

						foreach ( $rows as $key => $value )
						{
							$tmp =  array();
							$val =  $rows[ $key ][ $indices[ $arg['col'] ] ];
							unset ( $rows[ $key ][ $indices[ $arg['col'] ] ] );

							// Rewrite the row's information
							foreach ( $value as $key1 => $value1 )
							{
								// If this is the first element,
								// move the $arg['col'] to this index
								if ( $key1 == 0 )
								{
									$tmp[] = $val;
								}

								// As long as $key1 doesn't corresspond to the element
								// that we wish to move, keep aggregating the array
								if ( $key1 != $indices [ $arg['col'] ] )
								{
									$tmp[] = $value1;
								}
							}

							$rows [ $key ] = $tmp;

							unset($tmp);
						}

						break;
					}

					// Move the field to before or after a certain field
					case 'after'  :
					case 'before' :
					{
						// Determine the new array indices for the fields
						$count     = 0;
						$tmp       = array();
						$old_index = $indices     [ $arg['col'] ];
						$new_index = $indices     [ $arg['position'] ];
						$field     = $definitions [ $arg['col'] ];
						$rows      = $this->_read_file ( $filename . '.MYD' );

						if ( strtolower($arg['where']) == 'after' )
						{
							$new_index += 1;
						}

						// Rearrange the definitions
						foreach ( $definitions as $key => $value )
						{
							// If this is not the field we wish to move
							if ( $count != $old_index )
							{
								// If this index corresponds to the new position,
								// then add the field here
								if ( $count == $new_index )
								{
									$altered            = true;
									$tmp[ $arg['col'] ] = $field;
								}

								$tmp [ $key ] = $value;
							}

							$count++;
						}

						if ( !isset($altered) )
						{
							$tmp[ $arg['col'] ] = $field;
						}

						// blah...
						$new_def = $tmp;
						$tmp     = array();

						// Now rearrage the fields in the data files
						foreach ( $rows as $count => $row )
						{
							$replacer = $rows[$count][$old_index];
							$replaced = $rows[$count][$new_index];

							$rows[$count][$old_index] = $replaced;
							$rows[$count][$new_index] = $replacer;
						}

						break;
					}
				}

				break;
			}

			// Renames a column
			case 'rename' :
			{
				// Check if the field exists
				if ( !isset($definitions[ $arg['col'] ]) )
				{
					return $this->_raise_error('Field `' . $arg['col'] . '` doesn\'t exist', E_USER_NOTICE);
				}

				// Check that new one field doesn't exist
				if ( isset($definitions[ $arg['new_col'] ]) )
				{
					return $this->_raise_error('Field `' . $arg['col'] . '` already exists', E_USER_NOTICE);
				}

				// Perform the rename
				$tmp     = $definitions;
				$new_def = array();

				foreach ( $tmp as $key => $value )
				{
					if ( $key == $arg['col'] )
					{
						$key = $arg['new_col'];
					}

					$tmp[ $key ] = $value;
				}

				$new_def = $tmp;

				break;
			}

			// Oops.... @_o it's not a valid command
			default :
			{
				return $this->_raise_error('Invalid action, `' . $arg['action'] . '`', E_USER_NOTICE);
			}
		}

		// Save the changes to the files
		if ( $this->_write_file($filename . '.FRM', 'w', serialize($new_def)) !== false )
		{
			if ( isset($rows) )
			{
				if ( $this->_write_file($filename . '.MYD', 'w', serialize($rows)) === false )
				{
					return false;
				}
			}

			return true;
		}

		return false;
	}
}

?>