Location: PHPKode > scripts > Sqlitei > sqlitei/Sqlitei.class.php
<?php //5

/**
 * This final class has implemented some mysqli functions such:
 *	- prepare ( to prepare queries with or without ? vars )
 *	- bind_param ( to automatically parse given ? variables )
 *	- bind_result ( to chose the name of recieved vars after query )
 *	- execute ( to execute prepared query / queries )
 *	- close ( to clear result or closedatabase )
 * With this class your strings will be correctly parsed, then don't
 * worry about slashes or sqlinjections problems, it should be blinded
 * if used correctly ( as mysqli should be too ) .
 * Finally you can use or call all valid OOP methods of an SQLiteDatabase
 * database class.
 * To know more about methods, look here:
 * 	http://it2.php.net/manual/en/ref.sqlite.php
 * ______________________________________________________________
 * EXAMPLE [ you can find a complete example on www.devpro.it ] :
 * try {
 *	$connect = &new Sqlitei( 'sqlite.database' );
 *	$stmt = $connect->prepare( "SELECT * FROM test LIMIT 0, ?" );
 *	try {
 *		$stmt->bind_param( 'i', 10 );
 *       	$stmt->bind_result( 'id', 'someField' );
 *       	$stmt->execute();
 *       	while( $stmt->fetch() ) {
 *			echo 'ID: '.$stmt->id.'<br />';
 *			echo "TEXT: {$stmt->someField}<br />";
 *			echo '<hr />';
 *		}
 *		$stmt->close();
 *	}
 *	catch( Exception $e ) {
 *		die( $e->getMessage() );
 *	}
 * }
 * catch( Exception $e ) {
 *	die( $e->getMessage() );
 * }
 * --------------------------------------------------------------
 * @Compatibility	>= PHP 5.0
 * @Requires		class Sqlitei_Procedures
 * @Author		Andrea Giammarchi
 * @Site		http://www.devpro.it/
 * @Mail		andreaAT3siteDOTit
 * @Date		08/04/2005
 * @LastModified	11/04/2005 16:30
 * @Version		0.1 [ tested, seems efficient ]
 */
final class Sqlitei {
	
	/**
	 * private variables
         * 	Resource		internal SQLiteDatabase database class
         *      String			internal Resource error handler
         *      Integer			total indexes for internal managment
         *      Array			prepared queries backup variable
         *      Array			all valid methods for an SQLiteDatabase Object
	 */
	private $__db;
	private $__error;
	private $__index = 0;
	private $__dbPrepare = Array();
	private $__sqliteMethods = Array();
	
	/**
	 * final constructor
         * 	Creates a new Sqlitei obect and checks if database file exists.
         *      Assigns all valid SQLiteDatabase methods to internal Array.
         *      	new Sqlitei( String $dbFileName [, Integer $mode=0666] )
         * @param	String		sqlite dtabase file, it must exists
         * @param	Integer		mode to open db file, default 0666
	 */
	final function __construct( $dbFile, $mode = 0666 ) {
		if( file_exists( $dbFile ) ) {
			$this->__db = new SQLiteDatabase( $dbFile, $mode, $this->__error );
			$this->sqliteMethods = get_class_methods( $this->__db );			
		}
		else {
			throw new Exception( 'Database file ['.$dbFile.'] doesn\'t exist' );
			$this->__destruct();
		}
	}
	
	/**
	 * final destructor
         * 	clears all internal variables
         *      	self->__destruct( void )
	 */
	final function __destruct() {
		unset( $this->__db );
		unset( $this->__error );
		unset( $this->__index );
		unset( $this->__dbPrepare );
	}
	
	/**
	 * final public 'magic' method
         * 	Automatically try to call request SQLiteDatabase method with its vars.
         *      Throwsa new error if method is not a valid SQLiteDatabase method.
         *      	Mixed self->__call( String $method [, Array $variables] )
         * @param	String		SQLiteDatabase method to call
         * @param	Array		anythin, one or more required variables for called method
         * @return	Mixed		everything that returns called method.
	 */
	final public function __call( $method, $variables ) {
		if( in_Array( $method, $this->sqliteMethods ) ) {
			$result = '$this->__db->'.$method.'(';
			$internal = '';
			for( $a = 0, $b = count( $variables ); $a < $b; $a++ ) {
				$internal .= '$variables['.$a.'],';
			}
			if( $internal != '' ) {
				$result .= substr( $internal, 0, -1 ).');';
			}
			else {
				$result .= ');';
			}
			eval( '$SqliteResult = &'.$result );
		}
		else {
			$SqliteResult = NULL;
			throw new Exception( 'Call to undefined method SQLiteDatabase::'.$method.'()' );
		}
		return $SqliteResult;
	}
	
	/**
	 * final public 'magic' method
         * 	Returns string object id for internal db connection.
         *      	String self->__toString( void )
         * @return	String		string object id
	 */
	final public function __toString() {
		return (string)$this->__db;
	}
	
	/**
	 * final public method
         * 	Searchs if prepared query was used, otherwise parse this new query to prepare
         *      and add to internal query Array
         *      	Sqlitei_Procedures self->prepare( String $queryString )
         * @param	String			a query string with ? and without ' or "
         * 					Example: select ? from table where id = ?
         *                                      automatically parsed on its bind_param method
         * @return	Sqlitei_Procedures	new Sqlitei_Procedures Object to do database operations
	 */
	final public function prepare( $str ) {
		$found = &$this->__usedPrepare( $str );
		if( $found[0] ) {
			$idx = $found[1];
		}
		else {
			$idx = $this->__index++;
			$many = 0;
			preg_replace( "/\?/e", '$many++', $str );
			$this->__dbPrepare[$idx] = Array( 'query'=>$str, 'fields'=>$many );
		}
		return new Sqlitei_Procedures( $this->__db, $this->__dbPrepare[$idx] );
	}
	
	/**
	 * final public method
         * 	Calls its destructor
         *      	self->close( void )
	 */
	final public function close() {
		$this->__destruct();
	}
	
	/**
	 * final private method
         * 	Searchs inside prepared queries, then use that and comunicate to
         *      don't parse this just parsed and used query
         *      	Array self->__usedPrepare( &String $queryString )
         * @param	String		a query string with ? and without ' or "
         * 				Example: select ? from table where id = ?
         *				automatically parsed on its bind_param method
         * @return	Array		2 keys Array, Boolena found and Int index value
	 */
	final private function __usedPrepare( &$str ) {
		$c = -1;
		$found = false;
		for( $a = 0, $b = count( $this->__dbPrepare ); $a < $b; $a++ ) {
			if( $this->__dbPrepare[$a]['query'] === $str ) {
				$c = $a;
				$a = $b;
				$found = true;
			}
		}
		return Array( $found, $c );
	}
}

/**
 * This final class allows object Sqlitei to return a new Object
 * that will simulate a mysqli returned connection.
 * Please don't use this class directly, it should be dangerous.
 * ______________________________________________________________
 * --------------------------------------------------------------
 * @Compatibility	>= PHP 5.0
 * @Author		Andrea Giammarchi
 * @Site		http://www.devpro.it/
 * @Mail		andreaAT3siteDOTit
 * @Date		08/04/2005
 * @LastModified	08/04/2005 10:40
 * @Version		0.1 [ tested, seems efficient ]
 */
final class Sqlitei_Procedures {
	
	/**
	 * private variables
         * 	Resource		internal SQLiteDatabase database class
         *      Array			query results manager
         *      Integer			query array index
         *      Array			required parameters to do the query
         *      Array			prepared query, from Sqlite
         *      Array			all query parsed results
         *      Array			all internal methods
         *      Array			all internal vars
	 */
	private $__db;
	private $__dbResult;
	private $__qIndex = 0;
	private $__dbParams = Array();
	private $__dbPrepare = Array();
	private $__qResult = Array();
	private $__methods = Array();
	private $__vars = Array();
	
	/**
	 * final constructor
         * 	Creates a new Sqlitei_Procedures obect.
         *      Assigns database, prepared string, checks for params to use,
         *      assigns internal methods and vars to ensure that user
         *      will not use internal variables while loop a query result.
         *      	new Sqlitei_Procedures( SQLiteDatabase $database, Array $preparedQuery)
         * @param	SQLiteDatabase		the db resource for this connection
         * @param	Array			Array with 2 keys:
         * 					['fields'] = integer, how many params to execute this query
         *                                      ['query'] = preformatted query, from Sqlitei class
	 */
	final function __construct( SQLiteDatabase &$__db, &$__dbPrepare ) {
		$this->__db = &$__db;
		$this->__dbPrepare = &$__dbPrepare;
		if( $this->__dbPrepare['fields'] === 0 ) {
			$this->__dbParams[0] = 0;
		}
		$this->__methods = get_class_methods( $this );
		$class_vars = get_class_vars( get_class( $this ) );
		foreach( $class_vars as $name => &$value ) {
			array_push( $this->__vars, $name );
		}
	}
	
	/**
	 * final destructor
         * 	remove internal database reference
         *      	self->__destruct( void )
	 */
	final function __destruct() {
		unset( $this->__db );
	}
	
	/**
	 * final public method
         * 	Unsets assigned parameters and queries results
         *      	self->close( void )
	 */
	final public function close() {
		unset( $this->__dbParams );
		unset( $this->__qResult );
	}
	
	/**
	 * final public method
         * 	Clear stored results and puts on new results.
         *      Assigns new variables to object to manage
         *      results inside while( self->fetch() ) loop
         *      	Void self->bind_result( [ String result1 [, String result2 [, String resultN ... ]] ] )
         * @param	String		any, one or more new variables name that will have the requested value
         * 				Example:
         *                              self = $sqlitei->prepare( 'SELECT name, surname FROM table' );
         *                              self->bind_result( 'name', 'surname' );
         *                              self->execute();
         *                              while( self->fetch() ) {
         *                              	echo "{self->name} , {self->surname}<br />";
         *                              }
         *                              self->close();
         *                              NOTE:
         *                              	names should be unused inside this class or will be
         *                                      throw a new Exception
	 */
	final public function bind_result() {
		$this->__dbResult = Array();
		$required = &func_get_args();
		for( $a = 0, $b = count( $required ); $a < $b; $a++ ) {
			if( in_array( $required[$a], $this->__methods ) || in_array( $required[$a], $this->__vars ) ) {
				throw new Exception(
					'"'.$required[$a].'" name is just used in this class, please change this result name.'
				);
				$a = $b;
			}
			else {
				array_push( $this->__dbResult, '$this->'.$required[$a] );
			}
		}
	}
	
	/**
	 * final public method
         * 	Checks if query needs parameters and parse those with declared types control.
         *      Throws new Exception if:
         *      	- query doesn't need parameter
         *              - params are more or less than required
         *              - param type doesn't match with param value
         *	If passes all these checks, puts on internal array all parameters parsed with
         *      sqlite_escape_string if are strings type.
         *      	Void self->bind_param( String $types, Array $values )
         * @param	String		a string that rappresents all parameters types.
         * 				Accepted values are: i, d, b, s
         *                              i = integer parameter
         *                              d = double, float parameter
         *                              b = boolean parameter
         *                              s = every kind of parameter ( strings or others )
         * 				Example:
         *                              	self = $sqlitei->prepare( 'UPDATE table SET text = ? WHERE id = ?' );
         *                              	self->bind_param( 'si', 'Hello World!', 10 );
         *                                      self->execute();
         * @param	Array		func_get_args() array with all values for parameters
	 */
	final public function bind_param( $types ) {
		if( isSet( $this->__dbParams[0] ) == false || $this->__dbParams[0] !== 0 ) {
			if( preg_match( "/^(i|d|b|s)+$/", $types ) ) {
				$required = func_get_args();
				$b = strlen( $types );
				$c = count( $required ) - 1;
				if( $b === $c ) {
					$params = Array();
					for( $a = 0; $a < $b; $a++ ) {
						$params[$a] = &$this->__parseParams( $types{$a}, $required[($a+1)] );
						if( $params[$a]['valid'] !== true ) {
							$c = $a;
							$a = $b;
							throw new Exception(
								'Error type "'.$params[$c]['type'].'" for value number '.( $c + 1 )
							);
						}
					}
					array_push( $this->__dbParams, $params );
				}
				else {
					throw new Exception( 'This query requires '.$b.' parameters, not '.$c );
				}
			}
			else {
				throw new Exception( 'Association types are only "i", "d", "b", "s" not "'.$types.'"' );
			}
		}
	}
	
	/**
	 * final public method
         * 	Checks if necessary parameters are setted or throw a new Exception.
         *      Then checks:
         *      	- fields number and param required
         *              - request type and real value
         *              - query failure
         *	If passes all these tests, executes the query or all the queries binded then
         *      allow user to loop over a while cicle if is necessary ( only with a SELECT ) .
         *      Throws a new Exception on query failure too.
         *      	Void self->execute( void )
	 */
	final public function execute() {
		if( isSet( $this->__dbParams[0] ) == false ) {
			throw new Exception( 'Undefined association between query and parameters' );
		}
		else {
			$a = $this->__dbPrepare['fields'];
			if( $a !== 0 ) {
				$query = Array();
				$c = count( $this->__dbParams );
				for( $b = 0; $b < $c; $b++ ) {
					if( $a !== count( $this->__dbParams[$b] ) ) {
						throw new Exception( 'fields number mismatch this query' );
					}
					else {
						$replace = Array();
						for( $d = 0; $d < $a; $d++ ) {
							if( $this->__dbParams[$b][$d]['type'] === 's' ) {
								$value = &$this->__dbParams[$b][$d]['value'];
								if( is_null( $value ) ) {
									$value = 'NULL';
								}
								elseif( is_string( $value ) ) {
									$value = "'".sqlite_escape_string( $value )."'";
								}
								array_push( $replace, $value );
							}
							else {
								array_push( $replace, $this->__dbParams[$b][$d]['value'] );
							}
						}
						$e = 0;
						array_push( $query, preg_replace( "/\?/e", '$replace[$e++]', $this->__dbPrepare['query'] ) );
					}
				}
			}
			else {
				$query = $this->__dbPrepare['query'];
			}
			if( is_array( $query ) ) {
				$error = false;
				$this->__db->unbufferedQuery( 'BEGIN TRANSACTION' );
				for( $b = 0, $c = count( $query ); $b < $c; $b++ ) {
					$result = @$this->__db->unbufferedQuery( $query[$b] );
					if( !$result ) {
						$b = $c;
						$error = true;
						throw new Exception( $query[$b].'<br />'.sqlite_error_string( $this->__db->lastError() ) );
					}
					else {
						$this->__loopOverQuery( $result );
					}
				}
				if( $error === true ) {
					$this->__db->unbufferedQuery( 'ROLLBACK' );
				}
				else {
					$this->__db->unbufferedQuery( 'COMMIT' );
					$this->__qIndex = 0;
				}
			}
			else {
				$result = @$this->__db->unbufferedQuery( $query );
				if( !$result ) {
					throw new Exception( $query.'<br />'.sqlite_error_string( $this->__db->lastError() ) );
				}
				else {
					$this->__loopOverQuery( $result );
					$this->__qIndex = 0;
				}
			}
		}
	}
	
	/**
	 * final public method
         * 	Try to evaluate the result and assigns them to prepared results.
         *      Increments internal index on each valid result
         *      	Boolean self->fetch( void )
         * @return	Boolean		true or false, used in a while loop
	 */
	final public function fetch() {
		$return = isSet( $this->__qResult[$this->__qIndex] );
		if( $return ) {
			for( $a = 0, $b = count( $this->__dbResult ); $a < $b; $a++ ) {
				if( isSet( $this->__qResult[$this->__qIndex][$a] ) ) {
					eval( $this->__dbResult[$a].' = $this->__qResult[$this->__qIndex][$a];' );
				}
				else {
					eval( $this->__dbResult[$a].' = NULL;' );
				}
			}
			$this->__qIndex++;
		}
		else {
			for( $a = 0, $b = count( $this->__dbResult ); $a < $b; $a++ ) {
				eval( 'unset( '.$this->__dbResult[$a].' );' );
			}
			$this->__qResult = Array();
		}
		return $return;
	}
	
	/**
	 * final private method
         * 	Loops over a query results then push each column of each row
         *      inside internal query array.
         *      	Void self->__loopOverQuery( Resource $queryResult )
         * @param	Resource		result of an unbuffered query
	 */
	final private function __loopOverQuery( &$result ) {
		while( $result->valid() ) {
			$row = Array();
			$data = &$result->current( SQLITE_NUM, true );
			for( $b = 0, $c = count( $data ); $b < $c; $b++ ) {
				array_push( $row, $data[$b] );
			}
			array_push( $this->__qResult, $row );
			$result->next();
		}
	}
	
	/**
	 * final private method
         * 	Checks if params used respect its type declaration.
         *      	Array self->__parseParams( Char $type, Mixed &$value )
         * @param	Char		the type of this value ( i, d, b, s )
         * @param	Mixed		the value for this type
         * @return	Array		3 keys array:
         * 				['type'] => type of this value
         *                              ['value'] => value of this value
         *                              ['valid'] => boolean, pass or not this check
	 */	
	final private function __parseParams( $type, &$value ) {
		$error = true;
		switch( $type ) {
			case 'i':
				if( !is_int($value) ) {
					$error = false;
				}
				break;
			case 'd':
				if( !is_double($value) ) {
					$error = false;
				}
				break;
			case 'b':
				if( !is_bool($value) ) {
					$error = false;
				}
				break;
			default:
				break;
		}
		return Array( 'type'=>$type, 'value'=>$value, 'valid'=>$error );
	}	
}
?>
Return current item: Sqlitei