<?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 );
}
}
?>