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