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: