       1: <?php
       2: /**
       3:  * DataTables PHP libraries.
       4:  *
       5:  * PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
       6:  *
       7:  *  @author    SpryMedia
       8:  *  @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
       9:  *  @license   http://editor.datatables.net/license DataTables Editor
      10:  *  @link      http://editor.datatables.net
      11:  */
      12:
      13: namespace DataTables\Database;
      14: if (!defined('DATATABLES')) exit();
      15:
      16: use
      17:     DataTables,
      18:     DataTables\Database,
      19:     DataTables\Database\Query,
      20:     DataTables\Database\Result;
      21:
      22:
      23: //
      24: // This is a stub class that a driver must extend and complete
      25: //
      26:
      27: /**
      28:  * Perform an individual query on the database.
      29:  *
      30:  * The typical pattern for using this class is through the {@link
      31:  * \DataTables\Database::query} method (and it's 'select', etc short-cuts).
      32:  * Typically it would not be initialised directly.
      33:  *
      34:  * Note that this is a stub class that a driver will extend and complete as
      35:  * required for individual database types. Individual drivers could add
      36:  * additional methods, but this is discouraged to ensure that the API is the
      37:  * same for all database types.
      38:  */
      39: class Query {
      40:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
      41:      * Constructor
      42:      */
      43:
      44:     /**
      45:      * Query instance constructor.
      46:      *
      47:      * Note that typically instances of this class will be automatically created
      48:      * through the {@link \DataTables\Database::query} method.
      49:      *  @param Database        $db    Database instance
      50:      *  @param string          $type  Query type - 'select', 'insert', 'update' or 'delete'
      51:      *  @param string|string[] $table Tables to operate on - see {@link table}.
      52:      */
      53:     public function __construct( $dbHost, $type, $table=null )
      54:     {
      55:         $this->_dbHost = $dbHost;
      56:         $this->_type = $type;
      57:         $this->table( $table );
      58:     }
      59:
      60:
      61:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
      62:      * Private properties
      63:      */
      64:
      65:     /**
      66:      * @var string Driver to use
      67:      * @internal
      68:      */
      69:     protected $_type = "";
      70:
      71:     /**
      72:      * @var array
      73:      * @internal
      74:      */
      75:     protected $_table = array();
      76:
      77:     /**
      78:      * @var array
      79:      * @internal
      80:      */
      81:     protected $_field = array();
      82:
      83:     /**
      84:      * @var array
      85:      * @internal
      86:      */
      87:     protected $_bindings = array();
      88:
      89:     /**
      90:      * @var array
      91:      * @internal
      92:      */
      93:     protected $_where = array();
      94:
      95:     /**
      96:      * @var array
      97:      * @internal
      98:      */
      99:     protected $_join = array();
     100:
     101:     /**
     102:      * @var array
     103:      * @internal
     104:      */
     105:     protected $_order = array();
     106:
     107:     /**
     108:      * @var array
     109:      * @internal
     110:      */
     111:     protected $_noBind = array();
     112:
     113:     /**
     114:      * @var int
     115:      * @internal
     116:      */
     117:     protected $_limit = null;
     118:
     119:     /**
     120:      * @var int
     121:      * @internal
     122:      */
     123:     protected $_offset = null;
     124:
     125:     /**
     126:      * @var string
     127:      * @internal
     128:      */
     129:     protected $_distinct = false;
     130:
     131:     /**
     132:      * @var string
     133:      * @internal
     134:      */
     135:     protected $_identifier_limiter = array( '`', '`' );
     136:
     137:     /**
     138:      * @var string
     139:      * @internal
     140:      */
     141:     protected $_field_quote = '\'';
     142:
     143:     /**
     144:      * @var array
     145:      * @internal
     146:      */
     147:     protected $_pkey = null;
     148:
     149:     protected $_supportsAsAlias = true;
     150:
     151:     protected $_whereInCnt = 1;
     152:
     153:
     154:
     155:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     156:      * Static methods
     157:      */
     158:
     159:     /**
     160:      * Commit a transaction.
     161:      *  @param \PDO $dbh The Database handle (typically a PDO object, but not always).
     162:      */
     163:     public static function commit ( $dbh )
     164:     {
     165:         $dbh->commit();
     166:     }
     167:
     168:     /**
     169:      * Database connection - override by the database driver.
     170:      *  @param string|array $user User name or all parameters in an array
     171:      *  @param string $pass Password
     172:      *  @param string $host Host name
     173:      *  @param string $db   Database name
     174:      *  @return Query
     175:      */
     176:     public static function connect ( $user, $pass='', $host='', $port='', $db='', $dsn='' )
     177:     {
     178:         return false;
     179:     }
     180:
     181:
     182:     /**
     183:      * Start a database transaction
     184:      *  @param \PDO $dbh The Database handle (typically a PDO object, but not always).
     185:      */
     186:     public static function transaction ( $dbh )
     187:     {
     188:         $dbh->beginTransaction();
     189:     }
     190:
     191:
     192:     /**
     193:      * Rollback the database state to the start of the transaction.
     194:      *  @param \PDO $dbh The Database handle (typically a PDO object, but not always).
     195:      */
     196:     public static function rollback ( $dbh )
     197:     {
     198:         $dbh->rollBack();
     199:     }
     200:
     201:
     202:     /**
     203:      * Common helper for the drivers to handle a PDO DSN postfix
     204:      *  @param string $dsn DSN postfix to use
     205:      *  @return Query
     206:      *  @internal
     207:      */
     208:     static function dsnPostfix ( $dsn )
     209:     {
     210:         if ( ! $dsn ) {
     211:             return '';
     212:         }
     213:
     214:         // Add a DSN field separator if not given
     215:         if ( strpos( $dsn, ';' ) !== 0 ) {
     216:             return ';'.$dsn;
     217:         }
     218:
     219:         return $dsn;
     220:     }
     221:
     222:
     223:
     224:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     225:      * Public methods
     226:      */
     227:
     228:     /**
     229:      * Safely bind an input value to a parameter. This is evaluated when the
     230:      * query is executed. This allows user input to be safely executed without
     231:      * risk of an SQL injection attack.
     232:      *
     233:      * @param  string $name  Parameter name. This should include a leading colon
     234:      * @param  string $value Value to bind
     235:      * @param  mixed $type  Data type. See the PHP PDO documentation:
     236:      *   http://php.net/manual/en/pdo.constants.php
     237:      * @return Query
     238:      */
     239:     public function bind ( $name, $value, $type=null )
     240:     {
     241:         $this->_bindings[] = array(
     242:             "name"  => $this->_safe_bind( $name ),
     243:             "value" => $value,
     244:             "type"  => $type
     245:         );
     246:
     247:         return $this;
     248:     }
     249:
     250:
     251:     /**
     252:      * Get the Database host for this query instance
     253:      * @return DataTable Database class instance
     254:      */
     255:     public function database ()
     256:     {
     257:         return $this->_dbHost;
     258:     }
     259:
     260:
     261:     /**
     262:      * Set a distinct flag for a `select` query. Note that this has no effect on
     263:      * any of the other query types.
     264:      *  @param boolean $dis Optional
     265:      *  @return Query
     266:      */
     267:     public function distinct ( $dis )
     268:     {
     269:         $this->_distinct = $dis;
     270:         return $this;
     271:     }
     272:
     273:
     274:     /**
     275:      * Execute the query.
     276:      *  @param string $sql SQL string to execute (only if _type is 'raw').
     277:      *  @return Result
     278:      */
     279:     public function exec ( $sql=null )
     280:     {
     281:         $type = strtolower( $this->_type );
     282:
     283:         if ( $type === 'select' ) {
     284:             return $this->_select();
     285:         }
     286:         else if ( $type === 'insert' ) {
     287:             return $this->_insert();
     288:         }
     289:         else if ( $type === 'update' ) {
     290:             return $this->_update();
     291:         }
     292:         else if ( $type === 'delete' ) {
     293:             return $this->_delete();
     294:         }
     295:         else if ( $type === 'count' ) {
     296:             return $this->_count();
     297:         }
     298:         else if ( $type === 'raw' ) {
     299:             return $this->_raw( $sql );
     300:         }
     301:
     302:         throw new \Exception("Unknown database command or not supported: ".$type, 1);
     303:     }
     304:
     305:
     306:     /**
     307:      * Get fields.
     308:      *  @param string|string[] $get,... Fields to get - can be specified as
     309:      *    individual fields, an array of fields, a string of comma separated
     310:      *    fields or any combination of those.
     311:      *  @return self
     312:      */
     313:     public function get ( $get )
     314:     {
     315:         if ( $get === null ) {
     316:             return $this;
     317:         }
     318:
     319:         $args = func_get_args();
     320:
     321:         for ( $i=0 ; $i<count($args) ; $i++ ) {
     322:             // If argument is an array then we loop over and add each using a
     323:             // recursive call
     324:             if ( is_array( $args[$i] ) ) {
     325:                 for ( $j=0 ; $j<count($args[$i]) ; $j++ ) {
     326:                     $this->get( $args[$i][$j] );
     327:                 }
     328:             }
     329:             else {
     330:                 $this->_field[] = trim( $args[$i] );
     331:             }
     332:         }
     333:
     334:         return $this;
     335:     }
     336:
     337:
     338:     /**
     339:      * Perform a JOIN operation
     340:      *  @param string $table     Table name to do the JOIN on
     341:      *  @param string $condition JOIN condition
     342:      *  @param string $type      JOIN type
     343:      *  @return self
     344:      */
     345:     public function join ( $table, $condition, $type='' )
     346:     {
     347:         // Tidy and check we know what the join type is
     348:         if ($type !== '') {
     349:             $type = strtoupper(trim($type));
     350:
     351:             if ( ! in_array($type, array('LEFT', 'RIGHT', 'INNER', 'OUTER', 'LEFT OUTER', 'RIGHT OUTER'))) {
     352:                 $type = '';
     353:             }
     354:         }
     355:
     356:         // Protect the identifiers
     357:         if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $condition, $match))
     358:         {
     359:             $match[1] = $this->_protect_identifiers( $match[1] );
     360:             $match[3] = $this->_protect_identifiers( $match[3] );
     361:
     362:             $condition = $match[1].$match[2].$match[3];
     363:         }
     364:
     365:         $this->_join[] = $type .' JOIN '. $this->_protect_identifiers($table) .' ON '. $condition .' ';
     366:
     367:         return $this;
     368:     }
     369:
     370:
     371:     /**
     372:      * Limit the result set to a certain size.
     373:      *  @param int $lim The number of records to limit the result to.
     374:      *  @return self
     375:      */
     376:     public function limit ( $lim )
     377:     {
     378:         $this->_limit = $lim;
     379:
     380:         return $this;
     381:     }
     382:
     383:
     384:     /**
     385:      * Get / set the primary key column name(s) so they can be easily returned
     386:      * after an insert.
     387:      * @param  string[] $pkey Primary keys
     388:      * @return Query|string[]
     389:      */
     390:     public function pkey ( $pkey=null )
     391:     {
     392:         if ( $pkey === null ) {
     393:             return $this->_pkey;
     394:         }
     395:
     396:         $this->_pkey = $pkey;
     397:
     398:         return $this;
     399:     }
     400:
     401:
     402:     /**
     403:      * Set table(s) to perform the query on.
     404:      *  @param string|string[] $table,... Table(s) to use - can be specified as
     405:      *    individual names, an array of names, a string of comma separated
     406:      *    names or any combination of those.
     407:      *  @return self
     408:      */
     409:     public function table ( $table )
     410:     {
     411:         if ( $table === null ) {
     412:             return $this;
     413:         }
     414:
     415:         if ( is_array($table) ) {
     416:             // Array so loop internally
     417:             for ( $i=0 ; $i<count($table) ; $i++ ) {
     418:                 $this->table( $table[$i] );
     419:             }
     420:         }
     421:         else {
     422:             // String based, explode for multiple tables
     423:             $tables = explode(",", $table);
     424:
     425:             for ( $i=0 ; $i<count($tables) ; $i++ ) {
     426:                 $this->_table[] = $this->_protect_identifiers( trim($tables[$i]) );
     427:             }
     428:         }
     429:
     430:         return $this;
     431:     }
     432:
     433:
     434:     /**
     435:      * Offset the return set by a given number of records (useful for paging).
     436:      *  @param int $off The number of records to offset the result by.
     437:      *  @return self
     438:      */
     439:     public function offset ( $off )
     440:     {
     441:         $this->_offset = $off;
     442:
     443:         return $this;
     444:     }
     445:
     446:
     447:     /**
     448:      * Order by
     449:      *  @param string|string[] $order Columns and direction to order by - can
     450:      *    be specified as individual names, an array of names, a string of comma
     451:      *    separated names or any combination of those.
     452:      *  @return self
     453:      */
     454:     public function order ( $order )
     455:     {
     456:         if ( $order === null ) {
     457:             return $this;
     458:         }
     459:
     460:         if ( !is_array($order) ) {
     461:             $order = preg_split('/\,(?![^\(]*\))/',$order);
     462:         }
     463:
     464:         for ( $i=0 ; $i<count($order) ; $i++ ) {
     465:             // Simplify the white-space
     466:             $order[$i] = trim( preg_replace('/[\t ]+/', ' ', $order[$i]) );
     467:
     468:             // Find the identifier so we don't escape that
     469:             if ( strpos($order[$i], ' ') !== false ) {
     470:                 $direction = strstr($order[$i], ' ');
     471:                 $identifier = substr($order[$i], 0, - strlen($direction));
     472:             }
     473:             else {
     474:                 $direction = '';
     475:                 $identifier = $order[$i];
     476:             }
     477:
     478:             $this->_order[] = $this->_protect_identifiers( $identifier ).' '.$direction;
     479:         }
     480:
     481:         return $this;
     482:     }
     483:
     484:
     485:     /**
     486:      * Set fields to a given value.
     487:      *
     488:      * Can be used in two different ways, as set( field, value ) or as an array of
     489:      * fields to set: set( array( 'fieldName' => 'value', ...) );
     490:      *  @param string|string[] $set Can be given as a single string, when then $val
     491:      *    must be set, or as an array of key/value pairs to be set.
     492:      *  @param string          $val When $set is given as a simple string, $set is the field
     493:      *    name and this is the field's value.
     494:      *  @param boolean         $bind Should the value be bound or not
     495:      *  @return self
     496:      */
     497:     public function set ( $set, $val=null, $bind=true )
     498:     {
     499:         if ( $set === null ) {
     500:             return $this;
     501:         }
     502:
     503:         if ( !is_array($set) ) {
     504:             $set = array( $set => $val );
     505:         }
     506:
     507:         foreach ($set as $key => $value) {
     508:             $this->_field[] = $key;
     509:
     510:             if ( $bind ) {
     511:                 $this->bind( ':'.$key, $value );
     512:             }
     513:             else {
     514:                 $this->_noBind[$key] = $value;
     515:             }
     516:         }
     517:
     518:         return $this;
     519:     }
     520:
     521:
     522:     /**
     523:      * Where query - multiple conditions are bound as ANDs.
     524:      *
     525:      * Can be used in two different ways, as where( field, value ) or as an array of
     526:      * conditions to use: where( array('fieldName', ...), array('value', ...) );
     527:      *  @param string|string[]|callable $key   Single field name, or an array of field names.
     528:      *    If given as a function (i.e. a closure), the function is called, passing the
     529:      *    query itself in as the only parameter, so the function can add extra conditions
     530:      *    with parentheses around the additional parameters.
     531:      *  @param string|string[]          $value Single field value, or an array of
     532:      *    values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending
     533:      *    on the value of `$op` which should be `=` or `!=`.
     534:      *  @param string                   $op    Condition operator: <, >, = etc
     535:      *  @param boolean                  $bind  Escape the value (true, default) or not (false).
     536:      *  @return self
     537:      *
     538:      *  @example
     539:      *     The following will produce
     540:      *     `'WHERE name='allan' AND ( location='Scotland' OR location='Canada' )`:
     541:      *
     542:      *     <code>
     543:      *       $query
     544:      *         ->where( 'name', 'allan' )
     545:      *         ->where( function ($q) {
     546:      *           $q->where( 'location', 'Scotland' );
     547:      *           $q->where( 'location', 'Canada' );
     548:      *         } );
     549:      *     </code>
     550:      */
     551:     public function where ( $key, $value=null, $op="=", $bind=true )
     552:     {
     553:         if ( $key === null ) {
     554:             return $this;
     555:         }
     556:         else if ( is_callable($key) && is_object($key) ) { // is a closure
     557:             $this->_where_group( true, 'AND' );
     558:             $key( $this );
     559:             $this->_where_group( false, 'OR' );
     560:         }
     561:         else if ( !is_array($key) && is_array($value) ) {
     562:             for ( $i=0 ; $i<count($value) ; $i++ ) {
     563:                 $this->where( $key, $value[$i], $op, $bind );
     564:             }
     565:         }
     566:         else {
     567:             $this->_where( $key, $value, 'AND ', $op, $bind );
     568:         }
     569:
     570:         return $this;
     571:     }
     572:
     573:
     574:     /**
     575:      * Add addition where conditions to the query with an AND operator. An alias
     576:      * of `where` for naming consistency.
     577:      *
     578:      * Can be used in two different ways, as where( field, value ) or as an array of
     579:      * conditions to use: where( array('fieldName', ...), array('value', ...) );
     580:      *  @param string|string[]|callable $key   Single field name, or an array of field names.
     581:      *    If given as a function (i.e. a closure), the function is called, passing the
     582:      *    query itself in as the only parameter, so the function can add extra conditions
     583:      *    with parentheses around the additional parameters.
     584:      *  @param string|string[]          $value Single field value, or an array of
     585:      *    values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending
     586:      *    on the value of `$op` which should be `=` or `!=`.
     587:      *  @param string                   $op    Condition operator: <, >, = etc
     588:      *  @param boolean                  $bind  Escape the value (true, default) or not (false).
     589:      *  @return self
     590:      */
     591:     public function and_where ( $key, $value=null, $op="=", $bind=true )
     592:     {
     593:         return $this->where( $key, $value, $op, $bind );
     594:     }
     595:
     596:
     597:     /**
     598:      * Add addition where conditions to the query with an OR operator.
     599:      *
     600:      * Can be used in two different ways, as where( field, value ) or as an array of
     601:      * conditions to use: where( array('fieldName', ...), array('value', ...) );
     602:      *  @param string|string[]|callable $key   Single field name, or an array of field names.
     603:      *    If given as a function (i.e. a closure), the function is called, passing the
     604:      *    query itself in as the only parameter, so the function can add extra conditions
     605:      *    with parentheses around the additional parameters.
     606:      *  @param string|string[]          $value Single field value, or an array of
     607:      *    values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending
     608:      *    on the value of `$op` which should be `=` or `!=`.
     609:      *  @param string                   $op    Condition operator: <, >, = etc
     610:      *  @param boolean                  $bind  Escape the value (true, default) or not (false).
     611:      *  @return self
     612:      */
     613:     public function or_where ( $key, $value=null, $op="=", $bind=true )
     614:     {
     615:         if ( $key === null ) {
     616:             return $this;
     617:         }
     618:         else if ( is_callable($key) && is_object($key) ) {
     619:             $this->_where_group( true, 'OR' );
     620:             $key( $this );
     621:             $this->_where_group( false, 'OR' );
     622:         }
     623:         else {
     624:             if ( !is_array($key) && is_array($value) ) {
     625:                 for ( $i=0 ; $i<count($value) ; $i++ ) {
     626:                     $this->or_where( $key, $value[$i], $op, $bind );
     627:                 }
     628:                 return $this;
     629:             }
     630:
     631:             $this->_where( $key, $value, 'OR ', $op, $bind );
     632:         }
     633:
     634:         return $this;
     635:     }
     636:
     637:
     638:     /**
     639:      * Provide grouping for WHERE conditions. Use it with a callback function to
     640:      * automatically group any conditions applied inside the method.
     641:      *
     642:      * For legacy reasons this method also provides the ability to explicitly
     643:      * define if a grouping bracket should be opened or closed in the query.
     644:      * This method is not prefer.
     645:      *
     646:      *  @param boolean|callable $inOut If callable it will create the group
     647:      *      automatically and pass the query into the called function. For
     648:      *      legacy operations use `true` to open brackets, `false` to close.
     649:      *  @param string  $op    Conditional operator to use to join to the
     650:      *      preceding condition. Default `AND`.
     651:      *  @return self
     652:      *
     653:      *  @example
     654:      *     <code>
     655:      *     $query->where_group( function ($q) {
     656:      *       $q->where( 'location', 'Edinburgh' );
     657:      *       $q->where( 'position', 'Manager' );
     658:      *     } );
     659:      *     </code>
     660:      */
     661:     public function where_group ( $inOut, $op='AND' )
     662:     {
     663:         if ( is_callable($inOut) && is_object($inOut) ) {
     664:             $this->_where_group( true, $op );
     665:             $inOut( $this );
     666:             $this->_where_group( false, $op );
     667:         }
     668:         else {
     669:             $this->_where_group( $inOut, $op );
     670:         }
     671:
     672:         return $this;
     673:     }
     674:
     675:
     676:     /**
     677:      * Provide a method that can be used to perform a `WHERE ... IN (...)` query
     678:      * with bound values and parameters.
     679:      *
     680:      * Note this is only suitable for local values, not a sub-query. For that use
     681:      * `->where()` with an unbound value.
     682:      *
     683:      *  @param string Field name
     684:      *  @param array Values
     685:      *  @param string Conditional operator to use to join to the
     686:      *      preceding condition. Default `AND`.
     687:      *  @return self
     688:      */
     689:     public function where_in ( $field, $arr, $operator="AND" )
     690:     {
     691:         if ( count($arr) === 0 ) {
     692:             return $this;
     693:         }
     694:
     695:         $binders = array();
     696:         $prefix = ':wherein';
     697:
     698:         // Need to build an array of the binders (having bound the values) so
     699:         // the query can be constructed
     700:         for ( $i=0, $ien=count($arr) ; $i<$ien ; $i++ ) {
     701:             $binder = $prefix.$this->_whereInCnt;
     702:
     703:             $this->bind( $binder, $arr[$i] );
     704:
     705:             $binders[] = $binder;
     706:             $this->_whereInCnt++;
     707:         }
     708:
     709:         $this->_where[] = array(
     710:             'operator' => $operator,
     711:             'group'    => null,
     712:             'field'    => $this->_protect_identifiers($field),
     713:             'query'    => $this->_protect_identifiers($field) .' IN ('.implode(', ', $binders).')'
     714:         );
     715:
     716:         return $this;
     717:     }
     718:
     719:
     720:
     721:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     722:      * Protected methods
     723:      */
     724:
     725:     /**
     726:      * Create a comma separated field list
     727:      * @param bool $addAlias Flag to add an alias
     728:      * @return string
     729:      * @internal
     730:      */
     731:     protected function _build_field( $addAlias=false )
     732:     {
     733:         $a = array();
     734:         $asAlias = $this->_supportsAsAlias ?
     735:             ' as ' :
     736:             ' ';
     737:
     738:         for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
     739:             $field = $this->_field[$i];
     740:
     741:             // Keep the name when referring to a table
     742:             if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {
     743:                 $split = preg_split( '/ as (?![^\(]*\))/i', $field );
     744:
     745:                 if ( count($split) > 1 ) {
     746:                     $a[] = $this->_protect_identifiers( $split[0] ).$asAlias.
     747:                         $this->_field_quote. $split[1] .$this->_field_quote;
     748:                 }
     749:                 else {
     750:                     $a[] = $this->_protect_identifiers( $field ).$asAlias.
     751:                         $this->_field_quote. $field .$this->_field_quote;
     752:                 }
     753:             }
     754:             else if ( $addAlias && strpos($field, '(') !== false && ! strpos($field, ' as ') ) {
     755:                 $a[] = $this->_protect_identifiers( $field ).$asAlias.
     756:                     $this->_field_quote. $field .$this->_field_quote;
     757:             }
     758:             else {
     759:                 $a[] = $this->_protect_identifiers( $field );
     760:             }
     761:         }
     762:
     763:         return ' '.implode(', ', $a).' ';
     764:     }
     765:
     766:     /**
     767:      * Create a JOIN statement list
     768:      *  @return string
     769:      *  @internal
     770:      */
     771:     protected function _build_join()
     772:     {
     773:         return implode(' ', $this->_join);
     774:     }
     775:
     776:     /**
     777:      * Create the LIMIT / OFFSET string
     778:      *
     779:      * MySQL and Postgres stylee - anything else can have the driver override
     780:      *  @return string
     781:      *  @internal
     782:      */
     783:     protected function _build_limit()
     784:     {
     785:         $out = '';
     786:
     787:         if ( $this->_limit ) {
     788:             $out .= ' LIMIT '.$this->_limit;
     789:         }
     790:
     791:         if ( $this->_offset ) {
     792:             $out .= ' OFFSET '.$this->_offset;
     793:         }
     794:
     795:         return $out;
     796:     }
     797:
     798:     /**
     799:      * Create the ORDER BY string
     800:      *  @return string
     801:      *  @internal
     802:      */
     803:     protected function _build_order()
     804:     {
     805:         if ( count( $this->_order ) > 0 ) {
     806:             return ' ORDER BY '.implode(', ', $this->_order).' ';
     807:         }
     808:         return '';
     809:     }
     810:
     811:     /**
     812:      * Create a set list
     813:      *  @return string
     814:      *  @internal
     815:      */
     816:     protected function _build_set()
     817:     {
     818:         $a = array();
     819:
     820:         for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
     821:             $field = $this->_field[$i];
     822:
     823:             if ( isset( $this->_noBind[ $field ] ) ) {
     824:                 $a[] = $this->_protect_identifiers( $field ) .' = '. $this->_noBind[ $field ];
     825:             }
     826:             else {
     827:                 $a[] = $this->_protect_identifiers( $field ) .' = :'. $this->_safe_bind( $field );
     828:             }
     829:         }
     830:
     831:         return ' '.implode(', ', $a).' ';
     832:     }
     833:
     834:     /**
     835:      * Create the TABLE list
     836:      *  @return string
     837:      *  @internal
     838:      */
     839:     protected function _build_table()
     840:     {
     841:         if ( $this->_type === 'insert' ) {
     842:             // insert, update and delete statements don't need or want aliases in the table name
     843:             $a = array();
     844:
     845:             for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) {
     846:                 $table = str_ireplace( ' as ', ' ', $this->_table[$i] );
     847:                 $tableParts = explode( ' ', $table );
     848:
     849:                 $a[] = $tableParts[0];
     850:             }
     851:
     852:             return ' '.implode(', ', $a).' ';
     853:         }
     854:
     855:         return ' '.implode(', ', $this->_table).' ';
     856:     }
     857:
     858:     /**
     859:      * Create a bind field value list
     860:      *  @return string
     861:      *  @internal
     862:      */
     863:     protected function _build_value()
     864:     {
     865:         $a = array();
     866:
     867:         for ( $i=0, $ien=count($this->_field) ; $i<$ien ; $i++ ) {
     868:             $a[] = ' :'.$this->_safe_bind( $this->_field[$i] );
     869:         }
     870:
     871:         return ' '.implode(', ', $a).' ';
     872:     }
     873:
     874:     /**
     875:      * Create the WHERE statement
     876:      *  @return string
     877:      *  @internal
     878:      */
     879:     protected function _build_where()
     880:     {
     881:         if ( count($this->_where) === 0 ) {
     882:             return "";
     883:         }
     884:
     885:         $condition = "WHERE ";
     886:
     887:         for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
     888:             if ( $i === 0 ) {
     889:                 // Nothing (simplifies the logic!)
     890:             }
     891:             else if ( $this->_where[$i]['group'] === ')' ) {
     892:                 // If a group has been used but no conditions were added inside
     893:                 // of, we don't want to end up with `()` in the SQL as that is
     894:                 // invalid, so add a 1.
     895:                 if ( $this->_where[$i-1]['group'] === '(' ) {
     896:                     $condition .= '1=1';
     897:                 }
     898:                 // else nothing
     899:             }
     900:             else if ( $this->_where[$i-1]['group'] === '(' ) {
     901:                 // Nothing
     902:             }
     903:             else {
     904:                 $condition .= $this->_where[$i]['operator'].' ';
     905:             }
     906:
     907:             if ( $this->_where[$i]['group'] !== null ) {
     908:                 $condition .= $this->_where[$i]['group'];
     909:             }
     910:             else {
     911:                 $condition .= $this->_where[$i]['query'] .' ';
     912:             }
     913:         }
     914:
     915:         return $condition;
     916:     }
     917:
     918:     /**
     919:      * Create a DELETE statement
     920:      *  @return Result
     921:      *  @internal
     922:      */
     923:     protected function _delete()
     924:     {
     925:         $this->_prepare(
     926:             'DELETE FROM '
     927:             .$this->_build_table()
     928:             .$this->_build_where()
     929:         );
     930:
     931:         return $this->_exec();
     932:     }
     933:
     934:     /**
     935:      * Execute the query. Provided by the driver
     936:      *  @return Result
     937:      *  @internal
     938:      */
     939:     protected function _exec()
     940:     {}
     941:
     942:     /**
     943:      * Create an INSERT statement
     944:      *  @return Result
     945:      *  @internal
     946:      */
     947:     protected function _insert()
     948:     {
     949:         $this->_prepare(
     950:             'INSERT INTO '
     951:                 .$this->_build_table().' ('
     952:                     .$this->_build_field()
     953:                 .') '
     954:             .'VALUES ('
     955:                 .$this->_build_value()
     956:             .')'
     957:         );
     958:
     959:         return $this->_exec();
     960:     }
     961:
     962:     /**
     963:      * Prepare the SQL query by populating the bound variables.
     964:      * Provided by the driver
     965:      *  @return void
     966:      *  @internal
     967:      */
     968:     protected function _prepare( $sql )
     969:     {}
     970:
     971:     /**
     972:      * Protect field names
     973:      * @param string $identifier String to be protected
     974:      * @return string
     975:      * @internal
     976:      */
     977:     protected function _protect_identifiers( $identifier )
     978:     {
     979:         $idl = $this->_identifier_limiter;
     980:
     981:         // No escaping character
     982:         if ( ! $idl ) {
     983:             return $identifier;
     984:         }
     985:
     986:         $left = $idl[0];
     987:         $right = $idl[1];
     988:
     989:         // Dealing with a function or other expression? Just return immediately
     990:         if (strpos($identifier, '(') !== FALSE || strpos($identifier, '*') !== FALSE || strpos($identifier, ' ') !== FALSE)
     991:         {
     992:             return $identifier;
     993:         }
     994:
     995:         // Going to be operating on the spaces in strings, to simplify the white-space
     996:         $identifier = preg_replace('/[\t ]+/', ' ', $identifier);
     997:
     998:         // Find if our identifier has an alias, so we don't escape that
     999:         if ( strpos($identifier, ' as ') !== false ) {
    1000:             $alias = strstr($identifier, ' as ');
    1001:             $identifier = substr($identifier, 0, - strlen($alias));
    1002:         }
    1003:         else {
    1004:             $alias = '';
    1005:         }
    1006:
    1007:         $a = explode('.', $identifier);
    1008:         return $left . implode($right.'.'.$left, $a) . $right . $alias;
    1009:     }
    1010:
    1011:     /**
    1012:      * Passed in SQL statement
    1013:      *  @return Result
    1014:      *  @internal
    1015:      */
    1016:     protected function _raw( $sql )
    1017:     {
    1018:         $this->_prepare( $sql );
    1019:
    1020:         return $this->_exec();
    1021:     }
    1022:
    1023:     /**
    1024:      * The characters that can be used for the PDO bindValue name are quite
    1025:      * limited (`[a-zA-Z0-9_]+`). We need to abstract this out to allow slightly
    1026:      * more complex expressions including dots for easy aliasing
    1027:      * @param string $name Field name
    1028:      * @return string
    1029:      * @internal
    1030:      */
    1031:     protected function _safe_bind ( $name )
    1032:     {
    1033:         $name = str_replace('.', '_1_', $name);
    1034:         $name = str_replace('-', '_2_', $name);
    1035:         $name = str_replace('/', '_3_', $name);
    1036:         $name = str_replace('\\', '_4_', $name);
    1037:         $name = str_replace(' ', '_5_', $name);
    1038:
    1039:         return $name;
    1040:     }
    1041:
    1042:     /**
    1043:      * Create a SELECT statement
    1044:      *  @return Result
    1045:      *  @internal
    1046:      */
    1047:     protected function _select()
    1048:     {
    1049:         $this->_prepare(
    1050:             'SELECT '.($this->_distinct ? 'DISTINCT ' : '')
    1051:             .$this->_build_field( true )
    1052:             .'FROM '.$this->_build_table()
    1053:             .$this->_build_join()
    1054:             .$this->_build_where()
    1055:             .$this->_build_order()
    1056:             .$this->_build_limit()
    1057:         );
    1058:
    1059:         return $this->_exec();
    1060:     }
    1061:
    1062:     /**
    1063:      * Create a SELECT COUNT statement
    1064:      *  @return Result
    1065:      *  @internal
    1066:      */
    1067:     protected function _count()
    1068:     {
    1069:         $select = $this->_supportsAsAlias ?
    1070:             'SELECT COUNT('.$this->_build_field().') as '.$this->_protect_identifiers('cnt') :
    1071:             'SELECT COUNT('.$this->_build_field().') '.$this->_protect_identifiers('cnt');
    1072:
    1073:         $this->_prepare(
    1074:             $select
    1075:             .' FROM '.$this->_build_table()
    1076:             .$this->_build_join()
    1077:             .$this->_build_where()
    1078:             .$this->_build_limit()
    1079:         );
    1080:
    1081:         return $this->_exec();
    1082:     }
    1083:
    1084:     /**
    1085:      * Create an UPDATE statement
    1086:      *  @return Result
    1087:      *  @internal
    1088:      */
    1089:     protected function _update()
    1090:     {
    1091:         $this->_prepare(
    1092:             'UPDATE '
    1093:             .$this->_build_table()
    1094:             .'SET '.$this->_build_set()
    1095:             .$this->_build_where()
    1096:         );
    1097:
    1098:         return $this->_exec();
    1099:     }
    1100:
    1101:     /**
    1102:      * Add an individual where condition to the query.
    1103:      * @internal
    1104:      * @param $where
    1105:      * @param null $value
    1106:      * @param string $type
    1107:      * @param string $op
    1108:      * @param bool $bind
    1109:      */
    1110:     protected function _where ( $where, $value=null, $type='AND ', $op="=", $bind=true )
    1111:     {
    1112:         if ( $where === null ) {
    1113:             return;
    1114:         }
    1115:         else if ( !is_array($where) ) {
    1116:             $where = array( $where => $value );
    1117:         }
    1118:
    1119:         foreach ($where as $key => $value) {
    1120:             $i = count( $this->_where );
    1121:
    1122:             if ( $value === null ) {
    1123:                 // Null query
    1124:                 $this->_where[] = array(
    1125:                     'operator' => $type,
    1126:                     'group'    => null,
    1127:                     'field'    => $this->_protect_identifiers($key),
    1128:                     'query'    => $this->_protect_identifiers($key) .( $op === '=' ?
    1129:                         ' IS NULL' :
    1130:                         ' IS NOT NULL')
    1131:                 );
    1132:             }
    1133:             else if ( $bind ) {
    1134:                 // Binding condition (i.e. escape data)
    1135:                 if ( $this->_dbHost->type === 'Postgres' && $op === 'like' ) {
    1136:                     // Postgres specific a it needs a case for string searching non-text data
    1137:                     $this->_where[] = array(
    1138:                         'operator' => $type,
    1139:                         'group'    => null,
    1140:                         'field'    => $this->_protect_identifiers($key),
    1141:                         'query'    => $this->_protect_identifiers($key).'::text ilike '.$this->_safe_bind(':where_'.$i)
    1142:                     );
    1143:                 }
    1144:                 else {
    1145:                     $this->_where[] = array(
    1146:                         'operator' => $type,
    1147:                         'group'    => null,
    1148:                         'field'    => $this->_protect_identifiers($key),
    1149:                         'query'    => $this->_protect_identifiers($key) .' '.$op.' '.$this->_safe_bind(':where_'.$i)
    1150:                     );
    1151:                 }
    1152:                 $this->bind( ':where_'.$i, $value );
    1153:             }
    1154:             else {
    1155:                 // Non-binding condition
    1156:                 $this->_where[] = array(
    1157:                     'operator' => $type,
    1158:                     'group'    => null,
    1159:                     'field'    => null,
    1160:                     'query'    => $this->_protect_identifiers($key) .' '. $op .' '. $this->_protect_identifiers($value)
    1161:                 );
    1162:             }
    1163:         }
    1164:     }
    1165:
    1166:     /**
    1167:      * Add parentheses to a where condition
    1168:      *  @return string
    1169:      *  @internal
    1170:      */
    1171:     protected function _where_group ( $inOut, $op )
    1172:     {
    1173:         $this->_where[] = array(
    1174:             "group"    => $inOut ? '(' : ')',
    1175:             "operator" => $op
    1176:         );
    1177:     }
    1178: };
    1179:
    1180:
    1181: