Orion PHP  0.11.12
The PHP5.3 framework
sql.php
Go to the documentation of this file.
00001 <?php
00002 
00003 namespace Orion\Core\Query;
00004 
00005 use \Orion\Core;
00006 
00007 
00008 /**
00009  * \Orion\Core\Query\Sql
00010  * 
00011  * Orion Query for SQL DB type.
00012  *
00013  * @author Thibaut Despoulain
00014  * @license BSD 4-clauses
00015  * @version 0.11.10
00016  */
00017 class Sql implements Base
00018 {
00019     /**
00020      * Slug used to alias joined fields in JOIN queries to ease object conversion in SELECT queries.<br />
00021      * ie: A valid alias should look like : L_FIELD_SLUG . $fieldlink . L_FIELD_SEP . $fieldname<br />
00022      * With $fieldlink, the name of the field usef for linkage and $fieldname the joined field.
00023      */
00024     const L_FIELD_SLUG = 'linkedfield_';
00025     /**
00026      * Alias separator for JOIN queries.
00027      * See L_FIELD_SLUG
00028      */
00029     const L_FIELD_SEP = '__';
00030 
00031     private static $COMPARATORS = array(
00032         Core\Query::EQUAL => ' = ',
00033         Core\Query::NEQUAL => ' != ',
00034         Core\Query::LIKE => ' LIKE ',
00035         Core\Query::NOT => ' NOT ',
00036         Core\Query::REGEX => ' REGEXP '
00037     );
00038     private static $ORDERS = array(
00039         Core\Query::ASCENDING => 'ASC',
00040         Core\Query::DESCENDING => 'DESC'
00041     );
00042 //------------------------------------------------------------------------------
00043 // Protected Dynamic
00044 //------------------------------------------------------------------------------
00045     /**
00046      * From table override
00047      * @var String
00048      */
00049     protected $_TABLE = null;
00050 
00051     /**
00052      * Columns placeholder for select, update queries.
00053      * @var array<string>
00054      */
00055     protected $_COLUMNS = array( );
00056 
00057     /**
00058      * Keys for save
00059      * @var array<string>
00060      */
00061     protected $_KEYS = null;
00062 
00063     /**
00064      * Values for save
00065      * @var array<string>
00066      */
00067     protected $_VALUES = null;
00068 
00069     /**
00070      * Array of key/values to update
00071      * @var String[String]
00072      */
00073     protected $_SETS = null;
00074 
00075     /**
00076      * Use distinct
00077      * @var Boolean 
00078      */
00079     protected $_DISTINCT = false;
00080     
00081     /**
00082      * Where clause placeholder
00083      * @var array<string>[3] Example: array('field', 'LIKE', '%token%');
00084      */
00085     protected $_WHERE = array( );
00086 
00087     /**
00088      * Manual where clause placeholder. Used for complex where clauses.
00089      * <p><b>Be careful when using manual where clause because the string is not parsed, nor escaped. It is put in the query 'AS IS'.</b></p>
00090      * @var string string the complete where clause, without the WHERE keyword
00091      */
00092     protected $_MWHERE = null;
00093 
00094     /**
00095      * AND where clause placeholder
00096      * @var string AND where clause
00097      */
00098     protected $_AWHERE = array( );
00099 
00100     /**
00101      * OR where clause placeholder
00102      * @var string OR where clause
00103      */
00104     protected $_OWHERE = array( );
00105 
00106     /**
00107      * Order clause placeholder
00108      * @var array[2]
00109      */
00110     protected $_ORDER = array( );
00111 
00112     /**
00113      * Limit clause placeholder
00114      * @var int
00115      */
00116     protected $_LIMIT = null;
00117     
00118     /**
00119      * Group by statement field holder
00120      * @var string
00121      */
00122     protected $_GROUPBY = null;
00123     
00124     /**
00125      * Offset clause placeholder
00126      * @var int
00127      */
00128     protected $_OFFSET = null;
00129 
00130     /**
00131      * The type of the query
00132      * @var string
00133      */
00134     protected $_TYPE = null;
00135 
00136     /**
00137      * Joined links
00138      * @var array<string> Array of linked fields' name
00139      */
00140     protected $_JOIN = array( );
00141 
00142     /**
00143      * Joined fields data. <br />
00144      * <b>The field alias must be internally defined using L_FIELD_SLUG and L_FIELD_SEP</b>
00145      * @var array<array[3]<string>> Each row correspond to a linked field, consisting in an array with [0] being the field name, [1] the field alias and [2] the linked object's class name for object convertion.
00146      */
00147     protected $_JOIN_COLUMNS = array( );
00148     
00149     /**
00150      * Attribute storing tables joined using joinTable().
00151      * @var String[][] Assoc array of [leftfield, rightfield, jointype] with tables as keys.
00152      */
00153     protected $_JOIN_TABLE = array();
00154     
00155     /**
00156      * PDO query result
00157      * @var PDOStatement
00158      */
00159     protected $_QUERY = null;
00160 
00161     /**
00162      * SQL query string for backtracing
00163      * @var string
00164      */
00165     protected $_QUERY_STRING = null;
00166 
00167     /**
00168      * Internal query result keeper
00169      * @var mixed
00170      */
00171     protected $_RESULT = null;
00172 
00173     /**
00174      * PDO instance from Sql class
00175      * @var PDO
00176      */
00177     protected $_PDO = null;
00178 
00179     /**
00180      * Last joined table name
00181      * @var string
00182      */
00183     protected $_LAST_JOINED_TABLE = null;
00184 
00185 //------------------------------------------------------------------------------
00186 // Public Dynamic Implementations
00187 //------------------------------------------------------------------------------
00188 
00189     public function __construct( $model )
00190     {
00191         if ( isset( $model ) && is_string( $model ) )
00192         {
00193             $this->model = $model;
00194             if ( is_string( $model::getTable() ) )
00195                 $this->_TABLE = $model::getTable();
00196         }
00197     }
00198 
00199     /**
00200      * Query chain element, defining AND where clause
00201      * @param string $field
00202      * @param string $comparator
00203      * @param mixed $value <b>If no wildcard should be used, pass this parameter using Tools::escapeSql($value).</b> Other standard quotes escapes are done internally.
00204      */
00205     public function &andWhere( $field, $comparator=null, $value=null )
00206     {
00207         if ( empty( $this->_WHERE ) )
00208             return $this->where( $field, $comparator, $value );
00209 
00210         if ( !is_string( $field ) || !is_string( $comparator ) || $value === null )
00211             throw new Core\Exception( 'Missing argument in AND where clause' );
00212 
00213         if ( !array_key_exists( $comparator, self::$COMPARATORS ) )
00214             throw new Core\Exception( 'Unsupported comparator. Please use manualWhere() to use custom comparators.' );
00215 
00216         $comparator = self::$COMPARATORS[ $comparator ];
00217 
00218         $model = $this->model;
00219         if ( $this->hasModel() && $model::hasField( $field ) )
00220             $this->_AWHERE[ ] = array( $this->tablePrefix( $field ), $comparator, $model::getField( $field )->prepare( $this->escape( $value ) ) );
00221         else
00222             $this->_AWHERE[ ] = array( $this->tablePrefix( $field ), $comparator, $this->quote( $this->escape( $value ) ) );
00223 
00224         return $this;
00225     }
00226 
00227     public function &delete()
00228     {
00229         return $this->execute( 'delete' );
00230     }
00231     
00232     /**
00233      * Ignore duplicates on provided field
00234      * @param String $field
00235      */
00236     public function &distinct()
00237     {
00238         $this->_DISTINCT = true;
00239         return $this;
00240     }
00241 
00242     /**
00243      * Ends a query chain and returns the resulting row
00244      * @return \Orion\Core\Model
00245      */
00246     public function fetch()
00247     {
00248         try
00249         {
00250             $this->_QUERY = Core\DB::getConnection()->query( $this->getQuery() );
00251 
00252             if ( $this->hasModel() )
00253                 $class = $this->model;
00254             else
00255                 $class = '\\Orion\\Core\\Object';
00256 
00257             $this->_RESULT = $this->_QUERY->fetchObject( $class );
00258 
00259             if ( $this->_RESULT !== false && !empty( $this->_JOIN ) )
00260                 $this->parseJoinFields( $this->_RESULT );
00261         }
00262         catch ( PDOException $e )
00263         {
00264             throw new Core\Exception( $e->getMessage(), $e->getCode() );
00265         }
00266 
00267         return $this->_RESULT;
00268     }
00269 
00270     /**
00271      * Ends a query chain and returns the resulting rows
00272      * @return \Orion\Core\Model[]
00273      */
00274     public function fetchAll()
00275     {
00276         try
00277         {
00278             $this->_QUERY = Core\DB::getConnection()->query( $this->getQuery() );
00279 
00280             if ( $this->hasModel() )
00281                 $class = $this->model;
00282             else
00283                 $class = '\\Orion\\Core\\Object';
00284 
00285             $this->_RESULT = $this->_QUERY->fetchAll( \PDO::FETCH_CLASS, $class );
00286 
00287             if ( $this->_RESULT !== false && !empty( $this->_JOIN ) )
00288                 $this->parseJoinFields( $this->_RESULT );
00289         }
00290         catch ( PDOException $e )
00291         {
00292             throw new Core\Exception( $e->getMessage(), $e->getCode() );
00293         }
00294 
00295         return $this->_RESULT;
00296     }
00297     
00298     /**
00299      * Add a GROUP BY statement on provided field
00300      * @param String $col
00301      */
00302     public function &groupBy($col)
00303     {
00304         $this->_GROUPBY = $this->tablePrefix($this->escape($col));
00305         
00306         return $this;
00307     }
00308     
00309     /**
00310      * This method is SQL-only.
00311      * Groups rows from x-to-many query by concatenating them in a sigle column.
00312      * @param String $field The joined column to contact
00313      * @param String $name The alias of the new column
00314      * @param String $separator The separator used for concatenation
00315      * @param String [$table] The table used to prefix the $field
00316      */
00317     public function &groupConcat( $field, $name, $separator, $table=null )
00318     {
00319         $this->_COLUMNS[] = 'GROUP_CONCAT(' . $this->tablePrefix( $field, false, $table ) . ' SEPARATOR ' . $this->quote( $this->escape( $separator ) ) . ') AS ' . $this->antiQuote( $name );
00320     
00321         return $this;
00322     }
00323 
00324     /**
00325      * Query chain element, joining provided $fields on $link.
00326      * This method is only usable with Query that have a model bound width linked fields.
00327      * For manual joints, use manualJoin().
00328      * @param string $link Either a linked field name if Query is bound to a Model or a table name otherwise.
00329      * @param string $fields The fields to join
00330      * @param string $type [LEFT|RIGHT|INNER|OUTER]
00331      */
00332     public function &join( $link, $fields, $type='LEFT' )
00333     {
00334         if ( $fields == null || !is_array( $fields ) )
00335             throw new Core\Exception( 'Missing array of joined fields while trying to join on [' . Core\Security::preventInjection( $link ) . '].' );
00336 
00337         if ( !Core\Tools::match( $type, '(left|right) ?(inner|outer)?', 'i' ) )
00338             throw new Core\Exception( 'Invalid join type while trying to join on [' . Core\Security::preventInjection( $link ) . '].' );
00339 
00340         if ( !$this->hasModel() )
00341             throw new Core\Exception( 'Cannot create join query, no model bound.' );
00342 
00343         $model = $this->model;
00344 
00345         if ( !$model::isLinked( $link ) )
00346             throw new Core\Exception( 'Field [' . Core\Security::preventInjection( $link ) . ' is not linked in model.' );
00347 
00348         $table = $model::getField( $link )->getLinkedTable();
00349         $this->_LAST_JOINED_TABLE = $table;
00350         $previousTable = $this->getTable();
00351         $this->setTable( $table );
00352 
00353         // build joined fields array with field aliases 
00354         foreach ( $fields as $field )
00355             $this->_JOIN_COLUMNS[ ] = array( $this->tablePrefix( $field ), $this->antiQuote( self::L_FIELD_SLUG . $link . self::L_FIELD_SEP . $this->escape( $field ) ) );
00356 
00357         $this->_JOIN[ $link ] = array( $table, $type );
00358 
00359         $this->setTable( $previousTable );
00360 
00361         return $this;
00362     }
00363     
00364     /**
00365      * /!\ This method is experimental and should be used only if you know what you are doing.
00366      * Query chain element, joining provided $table to the query.
00367      * This method does not require a bound model. 
00368      * But the downside is that you won't have any object formating or column aliasing, so be careful with overlaps.
00369      * @param string $link A table name.
00370      * @param string $leftfield The field from the current table
00371      * @param string $rightfield The field from the joined table
00372      * @param string $type [LEFT|RIGHT|INNER|OUTER]
00373      */
00374     public function &joinTable( $table, $leftfield, $rightfield, $type='LEFT' )
00375     {
00376         if ( empty($table) || empty($leftfield) || empty($rightfield) )
00377             throw new Core\Exception( 'Missing arguments while trying to join [' . Core\Security::preventInjection( $table ) . '].' );
00378 
00379         if ( !Core\Tools::match( $type, '(natural )?((inner|cross)|(left|right)( outer)?)?', 'i' ) )
00380             throw new Core\Exception( 'Invalid join type while trying to join [' . Core\Security::preventInjection( $table ) . '].' );
00381 
00382         $this->_JOIN_TABLE[ $table ] = array($leftfield, $rightfield, $type);
00383         
00384         return $this;
00385     }
00386 
00387     /**
00388      * Query chain element, limiting the query to a given $size
00389      * @param Integer $size
00390      */
00391     public function &limit( $size )
00392     {
00393         if ( !is_numeric( $size ) )
00394             throw new Core\Exception( 'Invalid limit argument.' );
00395 
00396         $this->_LIMIT = $size;
00397 
00398         return $this;
00399     }
00400 
00401     /**
00402      * Query chain element, defining where clause manualy. Used for complex where clauses
00403      * <p><b>Be careful when using manual where clause because the string is not parsed, nor escaped. It is used in the query 'AS IS'.</b></p>
00404      * @param string the complete where clause, without the WHERE keyword
00405      */
00406     public function &manualWhere( $clause )
00407     {
00408         if ( !is_string( $clause ) )
00409             throw new Core\Exception( 'Manual where argument is not a complete <string> where clause.' );
00410 
00411         $this->_MWHERE = $clause;
00412 
00413         return $this;
00414     }
00415 
00416     /**
00417      * Query chain element, setting the starting offset
00418      * @param int $start
00419      */
00420     public function &offset( $start )
00421     {
00422         if ( !is_numeric( $start ) )
00423             throw new Core\Exception( 'Invalid offset argument' );
00424 
00425         $this->_OFFSET = $start;
00426 
00427         return $this;
00428     }
00429 
00430     /**
00431      * Query chain element, setting ordering clause
00432      * @param mixed $fields either an array of fields or a single field
00433      * @param string $mode 'ASC'|'DESC' or an array of modes
00434      */
00435     public function &order( $fields, $mode )
00436     {
00437         if ( empty( $fields ) || empty( $mode ) )
00438             throw new Core\Exception( 'Missing parameter in order clause.' );
00439 
00440         $order = array( );
00441 
00442         if ( is_array( $fields ) && is_array( $mode ) )
00443         {
00444             $lastMode = self::$ORDERS[ Core\Query::ASCENDING ];
00445             $c = count( $fields );
00446             for ( $i = 0; $i < $c; $i++ )
00447             {
00448                 if ( !isset( $mode[ $i ] ) )
00449                     $order[ $this->tablePrefix( $this->escape( $fields[ $i ] ) ) ] = $lastMode;
00450                 elseif ( !array_key_exists( $mode[ $i ], self::$ORDERS ) )
00451                     throw new Core\Exception( 'Unsupported order statement.' );
00452                 else
00453                 {
00454                     $order[ $this->tablePrefix( $this->escape( $fields[ $i ] ) ) ] = $mode[ $i ];
00455                     $lastMode = $mode[ $i ];
00456                 }
00457             }
00458 
00459             $this->_ORDER = $order;
00460         }
00461         else
00462         {
00463             if ( !is_array( $fields ) )
00464                 $fields = array( $fields );
00465             if ( !is_array( $mode ) )
00466                 $mode = array( $mode );
00467 
00468             return $this->order( $fields, $mode );
00469         }
00470 
00471         return $this;
00472     }
00473 
00474     /**
00475      * Query chain element, defining OR where clause
00476      * @param string $field
00477      * @param string $comparator
00478      * @param mixed $value <b>If no wildcard should be used, pass this parameter using Tools::escapeSql($value).</b> Other standard quotes escapes are done internally.
00479      */
00480     public function &orWhere( $field, $comparator=null, $value=null )
00481     {
00482         if ( empty( $this->_WHERE ) )
00483             return $this->where( $field, $comparator, $value );
00484 
00485         if ( !is_string( $field ) || !is_string( $comparator ) || $value === null )
00486             throw new Core\Exception( 'Missing argument in OR where clause' );
00487 
00488         if ( !array_key_exists( $comparator, self::$COMPARATORS ) )
00489             throw new Core\Exception( 'Unsupported comparator. Please use manualWhere() to use custom comparators.' );
00490 
00491         $comparator = self::$COMPARATORS[ $comparator ];
00492 
00493         $model = $this->model;
00494         if ( $this->hasModel() && $model::hasField( $field ) )
00495             $this->_OWHERE[ ] = array( $this->tablePrefix( $field ), $comparator, $model::getField( $field )->prepare( $this->escape( $value ) ) );
00496         else
00497             $this->_OWHERE[ ] = array( $this->tablePrefix( $field ), $comparator, $this->quote( $this->escape( $value ) ) );
00498 
00499         return $this;
00500     }
00501 
00502     public function &save()
00503     {
00504         return $this->execute( 'insert' );
00505     }
00506 
00507     /**
00508      * Start a select query chain
00509      * @param mixed Either select('f1','f2', ...) or select(array('f1','f2',...))
00510      */
00511     public function &select( $data=null )
00512     {
00513         $this->_TYPE = 'select';
00514 
00515         if ( func_num_args() == 0 || $data == null )
00516             if ( $this->hasModel() )
00517             {
00518                 $model = $this->model;
00519                 $this->_COLUMNS = $this->tablePrefix( $this->escape( array_keys( $model::getFields() ) ) );
00520             }
00521             else
00522                 $this->_COLUMNS = array( '*' );
00523         else
00524         {
00525             if ( is_array( $data ) )
00526                 $cols = $data;
00527             else
00528                 $cols = func_get_args();
00529             $this->_COLUMNS = $this->tablePrefix( $this->escape( $cols ) );
00530         }
00531 
00532         return $this;
00533     }
00534 
00535     /**
00536      * Start a select query chain by selecting all fields of a model except those provided.
00537      * Can only be used on Query with a model bound.
00538      * @param mixed $data Either selectAllExcept('f1','f2', ...) or selectAllExcept(array('f1','f2',...))
00539      */
00540     public function &selectAllExcept( $data=null )
00541     {
00542         if ( !$this->hasModel() )
00543             throw new Core\Exception( 'selectAllExcept() can only be used with Query when a model is bound.' );
00544 
00545         $this->_TYPE = 'select';
00546 
00547         if ( func_num_args() == 0 || $data == null )
00548             throw new Core\Exception( 'SelectAllExcept() needs at least one field as argument.' );
00549 
00550         if ( is_array( $data ) )
00551             $exceptCols = $data;
00552         else
00553             $exceptCols = func_get_args();
00554 
00555         $cols = array( );
00556         $model = $this->model;
00557         foreach ( array_keys( $model::getFields() ) as $field )
00558             if ( !in_array( $field, $exceptCols ) )
00559                 $cols[ ] = $field;
00560 
00561         if ( empty( $cols ) )
00562             throw new Core\Exception( 'SelectAllExcept() Too many fields removed. Needs at least one field to select.' );
00563 
00564         $this->_COLUMNS = $this->tablePrefix( $this->escape( $cols ) );
00565 
00566         return $this;
00567     }
00568 
00569     /** 
00570      * Set columns values for insert and update queries
00571      * @param string $key
00572      * @param string $value
00573      * @return Sql 
00574      */
00575     public function &set( $key, $value )
00576     {
00577         if ( !is_string( $key ) )
00578             throw new Core\Exception( 'set() key must be a valid string.' );
00579 
00580         $model = $this->model;
00581         if ( $this->hasModel() && $model::hasField( $key ) )
00582             $this->_DATA [ $this->antiQuote( $this->escape( $key ) ) ] = $model::getField( $key )->prepare( $this->escape( $value ) );
00583         else
00584             $this->_DATA [ $this->antiQuote( $this->escape( $key ) ) ] = $this->quote( $this->escape( $value ) );
00585 
00586         return $this;
00587     }
00588 
00589     public function success()
00590     {
00591         return ($this->_RESULT != null && $this->_RESULT !== false);
00592     }
00593 
00594     public function &update()
00595     {
00596         return $this->execute( 'update' );
00597     }
00598 
00599     /**
00600      * Query chain element, defining where clause
00601      * @param string $field
00602      * @param string $comparator
00603      * @param mixed $value <b>If no wildcard should be used, pass this parameter using Tools::escapeSql($value).</b> Other standard quotes escapes are done internally.
00604      */
00605     public function &where( $field, $comparator=null, $value=null )
00606     {
00607         if ( !is_string( $field ) || !is_string( $comparator ) || $value === null )
00608             throw new Core\Exception( 'Missing argument in where clause' );
00609 
00610         if ( !array_key_exists( $comparator, self::$COMPARATORS ) )
00611             throw new Core\Exception( 'Unsupported comparator. Please use manualWhere() to use custom comparators.' );
00612 
00613         $comparator = self::$COMPARATORS[ $comparator ];
00614 
00615         $model = $this->model;
00616         if ( $this->hasModel() && $model::hasField( $field ) )
00617             $this->_WHERE = array( $this->tablePrefix( $field ), $comparator, $model::getField( $field )->prepare( $this->escape( $value ) ) );
00618         else
00619             $this->_WHERE = array( $this->tablePrefix( $field ), $comparator, $this->quote( $this->escape( $value ) ) );
00620 
00621         return $this;
00622     }
00623 
00624     /**
00625      * Add a table prefix to a field name or an array of field names if they do not already have a table prefix.
00626      * Also add surrounding antiquotes.
00627      * @param String|String[] $field field name
00628      * @param Boolean $force Force prefix, even if field is already prefixed
00629      * @param String $table Prefix with a custom table name instead of the current table.
00630      * @return String prefixed field name
00631      */
00632     public function tablePrefix( $fields, $force=false, $table=null )
00633     {
00634         if ( $table == null )
00635             $table = $this->_TABLE;
00636 
00637         if ( $table == null )
00638             return $fields;
00639 
00640         if ( is_array( $fields ) )
00641         {
00642             $result = array( );
00643 
00644             foreach ( $fields as $field )
00645                 $result[ ] = self::tablePrefix( $field, $force, $table );
00646 
00647             return $result;
00648         }
00649 
00650         if ( strpos( '.', $fields ) !== false )
00651         {
00652             if ( $force )
00653                 return $this->antiQuote( $table ) . '.' . $this->antiQuote( implode( '`.`', explode( '.', $fields ) ) );
00654             else
00655                 return $this->antiQuote( implode( '`.`', explode( '.', $fields ) ) );
00656         }
00657         else
00658         {
00659             return $this->antiQuote( $table ) . '.' . $this->antiQuote( $fields );
00660         }
00661     }
00662 
00663     /**
00664      * Surrround a string with antiquotes
00665      * @param String $data
00666      * @return String
00667      */
00668     public function antiQuote( $data )
00669     {
00670         if ( is_array( $data ) )
00671         {
00672             $c = count( $data );
00673             for ( $i = 0; $i < $c; $i++ )
00674                 $data[ $i ] = self::escape( $data[ $i ] );
00675 
00676             return $data;
00677         }
00678 
00679         if ( is_string( $data ) )
00680             return '`' . $data . '`';
00681         else
00682             throw new Core\Exception( 'antiquote() must be applyed to strings.' );
00683     }
00684 
00685     /**
00686      * Escapes string for mysql usage ('\\', "\0", "\n", "\r", "'", '"', "\x1a")
00687      * @param string $data
00688      * @param string[] $custom An array of custom elements to remove
00689      * @return string
00690      */
00691     public function escape( $data )
00692     {
00693         if ( is_array( $data ) )
00694         {
00695             $c = count( $data );
00696             for ( $i = 0; $i < $c; $i++ )
00697                 $data[ $i ] = self::escape( $data[ $i ] );
00698 
00699             return $data;
00700         }
00701 
00702         if ( is_string( $data ) )
00703             return str_replace( array( '\\', "\0", "\n", "\r", "'", '"', "\x1a" ), array( '\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z' ), $data );
00704         else
00705             return $data;
00706     }
00707 
00708     /**
00709      * Execute given query type using current query data
00710      * @param string $type Query type ('insert', 'delete', ..etc)
00711      * @return Sql 
00712      */
00713     public function &execute( $type=null )
00714     {
00715         if ( $type != null )
00716             $this->_TYPE = $type;
00717 
00718         $this->_RESULT = Core\DB::getConnection()->exec( $this->getQuery() );
00719 
00720         return $this;
00721     }
00722 
00723     /**
00724      * Adds single quotes to a string
00725      * @param stirng $string
00726      * @return string
00727      */
00728     public function quote( $string )
00729     {
00730         return "'" . $string . "'";
00731     }
00732 
00733     /**
00734      * Get current table in use
00735      * @return String
00736      */
00737     public function getTable()
00738     {
00739         return $this->_TABLE;
00740     }
00741 
00742     /**
00743      * Get last joined table's name
00744      * @return string
00745      */
00746     public function getLastJoinedTable()
00747     {
00748         return $this->_LAST_JOINED_TABLE;
00749     }
00750 
00751     /**
00752      * Overrides current table name.
00753      * @param String $table 
00754      */
00755     public function &setTable( $table )
00756     {
00757         if ( is_string( $table ) )
00758             $this->_TABLE = $this->escape( Core\Tools::removeString( ' ', $table ) );
00759         else
00760             throw new Core\Exception( 'setTable() method must be given a string.' );
00761 
00762         return $this;
00763     }
00764 
00765     /**
00766      * Revert table name back to model's table.
00767      * Usable only on Query with a bound Model.
00768      * @param Boolean $dontUseModel Set this to TRUE to skip revert and force table to none
00769      */
00770     public function &unsetTable( $dontUseModel=false )
00771     {
00772         if ( !$this->hasModel() )
00773             throw new Core\Exception( 'Unable to unset table on Query with no bound Model.' );
00774         else
00775         {
00776             $model = $this->model;
00777             if ( is_string( $model::getTable() ) )
00778                 $this->_TABLE = $model::getTable();
00779             else
00780                 throw new Core\Exception( 'Cannot revert table to model table, varable is not a string.' );
00781         }
00782 
00783         return $this;
00784     }
00785 
00786     /**
00787      * Is a model bound to Query object ?
00788      * @return Boolean
00789      */
00790     public function hasModel()
00791     {
00792         return ( is_string( $this->model ) );
00793     }
00794 
00795     /**
00796      * Get current query string
00797      * @return string current query string
00798      */
00799     public function getQuery()
00800     {
00801         if ( $this->_TABLE == null )
00802             throw new Core\Exception( 'Unable to perform query, no table provided.' );
00803 
00804         if ( $this->hasModel() )
00805             $this->unsetTable();
00806 
00807         if ( $this->_MWHERE != null )
00808             $where = $this->_MWHERE;
00809         else
00810         {
00811             if ( !empty( $this->_WHERE ) && is_array( $this->_WHERE ) )
00812             {
00813                 $where = implode( ' ', $this->_WHERE );
00814                 if ( !empty( $this->_AWHERE ) )
00815                 {
00816                     foreach ( $this->_AWHERE as $andClause )
00817                         $where .= ' AND ' . implode( ' ', $andClause );
00818                 }
00819                 if ( !empty( $this->_OWHERE ) )
00820                 {
00821                     foreach ( $this->_OWHERE as $orClause )
00822                         $where .= ' OR ' . implode( ' ', $orClause );
00823                 }
00824             }
00825             else
00826                 $where = null;
00827         }
00828 
00829         switch ( $this->_TYPE )
00830         {
00831             case 'select':
00832                 $query = "SELECT ";
00833                 if ($this->_DISTINCT)
00834                     $query .= 'DISTINCT ';
00835                             
00836                 $query .= implode( ', ', $this->_COLUMNS );
00837 
00838                 foreach ( $this->_JOIN_COLUMNS as $col )
00839                     $query .= ", " . implode( ' AS ', $col );
00840 
00841                 $query .= " FROM " . $this->antiQuote( $this->_TABLE );
00842 
00843                 if ( !empty( $this->_JOIN ) )
00844                     if ( !$this->hasModel() )
00845                         throw new Core\Exception( 'Trying to execute a join query with no model bound.' );
00846                     else
00847                         $model = $this->model;
00848 
00849                 foreach ( $this->_JOIN as $key => $data )
00850                     $query .= " " . strtoupper( $data[ 1 ] ) . " JOIN " . $this->antiQuote( $data[ 0 ] ) . " ON " . $this->tablePrefix( $model::getField( $key )->getBinding() ) . "=" . $this->tablePrefix( $model::getField( $key )->getRightfield(), false, $data[ 0 ] );
00851 
00852                 foreach ( $this->_JOIN_TABLE as $table => $data )
00853                     $query .= " " . strtoupper( $data[ 2 ] ) . " JOIN " . $this->antiQuote( $table ) . " ON " . $this->tablePrefix( $data[0] ) . "=" . $this->tablePrefix( $data[ 1 ], false, $table );
00854                 
00855                 if ( $where != null )
00856                     $query .= " WHERE " . $where;
00857                 
00858                 if( $this->_GROUPBY != null)
00859                     $query .= " GROUP BY ".$this->_GROUPBY;
00860                 
00861                 if ( !empty( $this->_ORDER ) )
00862                 {
00863                     $order = array( );
00864                     foreach ( $this->_ORDER as $field => $mode )
00865                         $order[ ] = $field . ' ' . $mode;
00866                     $query .= " ORDER BY " . implode( ', ', $order );
00867                 }
00868                 if ( $this->_LIMIT != null )
00869                     $query .= " LIMIT " . $this->_LIMIT;
00870                 if ( $this->_OFFSET != null )
00871                     $query .= " OFFSET " . $this->_OFFSET;
00872                 break;
00873 
00874             case 'insert':
00875                 if ( empty( $this->_DATA ) )
00876                     throw new Core\Exception( 'Missing row data in insert query.' );
00877 
00878                 $query = "INSERT INTO `" . $this->_TABLE . "`";
00879 
00880                 $keys = array_keys( $this->_DATA );
00881                 $values = array_values( $this->_DATA );
00882                 $query .= " (" . implode( ', ', $keys ) . ") VALUES (" . implode( ', ', $values ) . ")";
00883                 break;
00884 
00885             case 'update':
00886                 if ( empty( $this->_DATA ) )
00887                     throw new Core\Exception( 'Missing row data in update query.' );
00888 
00889                 $query = "UPDATE `" . $this->_TABLE . "`";
00890 
00891                 $sets = array( );
00892                 foreach ( $this->_DATA as $key => $value )
00893                     $sets[ ] = $key . '=' . $value;
00894 
00895                 $query .= " SET " . implode( ', ', $sets );
00896 
00897                 if ( $where == null )
00898                     throw new Core\Exception( 'Update query must have a where clause.' );
00899                 else
00900                     $query .= " WHERE " . $where;
00901 
00902                 if ( $this->_LIMIT != null )
00903                     $query .= " LIMIT " . $this->_LIMIT;
00904                 break;
00905 
00906             case 'delete':
00907                 $query = "DELETE FROM `" . $this->_TABLE . "`";
00908 
00909                 if ( $where == null )
00910                     throw new Core\Exception( 'Delete query must have a where clause.', E_USER_WARNING, get_class( $this ) );
00911                 else
00912                     $query .= " WHERE " . $where;
00913 
00914                 if ( $this->_LIMIT != null )
00915                     $query .= " LIMIT " . $this->_LIMIT;
00916                 break;
00917 
00918             default:
00919                 throw new Core\Exception( 'Unknown query type.', E_USER_WARNING, get_class( $this ) );
00920                 break;
00921         }
00922 
00923         $this->_QUERY_STRING = $query;
00924 
00925         if ( \Orion::isDebug() )
00926             echo 'SQL_QUERY: ' . $query . '<br />';
00927 
00928         return $query . ';';
00929     }
00930 
00931     /**
00932      * Parse query-resulting object to build linked objects from joined fields' syntax.<br />
00933      * In other words, convert "L_FIELD_SLUG.$linkedfield.L_FIELD_SEP.$fieldname" into linked Object.
00934      * @param \Orion\Core\Model|\Orion\Core\Object
00935      */
00936     protected function parseJoinFields( &$object )
00937     {
00938         if ( is_array( $object ) )
00939         {
00940             $c = count( $object );
00941             for ( $i = 0; $i < $c; $i++ )
00942                 self::parseJoinFields( $object[ $i ] );
00943 
00944             return;
00945         }
00946 
00947         $linkedFields = Core\Tools::extractArrayKeysStartingWith( get_object_vars( $object ), self::L_FIELD_SLUG );
00948         $l_field_len = strlen( self::L_FIELD_SLUG );
00949         foreach ( $linkedFields as $key => $value )
00950         {
00951             $tmp = explode( self::L_FIELD_SEP, substr( $key, $l_field_len ) );
00952             $field = $tmp[ 0 ];
00953             $subfield = $tmp[ 1 ];
00954 
00955             if ( !($object->{$field} instanceOf Core\Model) || !($object->{$field} instanceOf Core\Object) )
00956             {
00957                 if ( $this->hasModel( $field ) )
00958                 {
00959                     $model = $this->model;
00960                     if ( !$model::isLinked( $field ) )
00961                         $object->{$field} = new Object();
00962                     else
00963                     {
00964                         $linkedClass = $model::getField( $field )->getModel();
00965                         $object->{$field} = new $linkedClass();
00966                     }
00967                 }
00968                 else
00969                     $object->{$field} = new Object();
00970             }
00971             $object->{$field}->{$subfield} = $value;
00972             unset( $object->{$key} );
00973         }
00974     }
00975 
00976 }
00977 
00978 ?>