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 === 'raw' ) {
296: return $this->_raw( $sql );
297: }
298:
299: throw new \Exception("Unknown database command or not supported: ".$type, 1);
300: }
301:
302:
303: /**
304: * Get fields.
305: * @param string|string[] $get,... Fields to get - can be specified as
306: * individual fields, an array of fields, a string of comma separated
307: * fields or any combination of those.
308: * @return self
309: */
310: public function get ( $get )
311: {
312: if ( $get === null ) {
313: return $this;
314: }
315:
316: $args = func_get_args();
317:
318: for ( $i=0 ; $i<count($args) ; $i++ ) {
319: // If argument is an array then we loop over and add each using a
320: // recursive call
321: if ( is_array( $args[$i] ) ) {
322: for ( $j=0 ; $j<count($args[$i]) ; $j++ ) {
323: $this->get( $args[$i][$j] );
324: }
325: }
326: else {
327: $this->_field[] = trim( $get );
328: }
329: }
330:
331: return $this;
332: }
333:
334:
335: /**
336: * Perform a JOIN operation
337: * @param string $table Table name to do the JOIN on
338: * @param string $condition JOIN condition
339: * @param string $type JOIN type
340: * @return self
341: */
342: public function join ( $table, $condition, $type='' )
343: {
344: // Tidy and check we know what the join type is
345: if ($type !== '') {
346: $type = strtoupper(trim($type));
347:
348: if ( ! in_array($type, array('LEFT', 'RIGHT', 'INNER', 'OUTER', 'LEFT OUTER', 'RIGHT OUTER'))) {
349: $type = '';
350: }
351: }
352:
353: // Protect the identifiers
354: if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $condition, $match))
355: {
356: $match[1] = $this->_protect_identifiers( $match[1] );
357: $match[3] = $this->_protect_identifiers( $match[3] );
358:
359: $condition = $match[1].$match[2].$match[3];
360: }
361:
362: $this->_join[] = $type .' JOIN '. $this->_protect_identifiers($table) .' ON '. $condition .' ';
363:
364: return $this;
365: }
366:
367:
368: /**
369: * Limit the result set to a certain size.
370: * @param int $lim The number of records to limit the result to.
371: * @return self
372: */
373: public function limit ( $lim )
374: {
375: $this->_limit = $lim;
376:
377: return $this;
378: }
379:
380:
381: /**
382: * Get / set the primary key column name(s) so they can be easily returned
383: * after an insert.
384: * @param string[] $pkey Primary keys
385: * @return Query|string[]
386: */
387: public function pkey ( $pkey=null )
388: {
389: if ( $pkey === null ) {
390: return $this->_pkey;
391: }
392:
393: $this->_pkey = $pkey;
394:
395: return $this;
396: }
397:
398:
399: /**
400: * Set table(s) to perform the query on.
401: * @param string|string[] $table,... Table(s) to use - can be specified as
402: * individual names, an array of names, a string of comma separated
403: * names or any combination of those.
404: * @return self
405: */
406: public function table ( $table )
407: {
408: if ( $table === null ) {
409: return $this;
410: }
411:
412: if ( is_array($table) ) {
413: // Array so loop internally
414: for ( $i=0 ; $i<count($table) ; $i++ ) {
415: $this->table( $table[$i] );
416: }
417: }
418: else {
419: // String based, explode for multiple tables
420: $tables = explode(",", $table);
421:
422: for ( $i=0 ; $i<count($tables) ; $i++ ) {
423: $this->_table[] = $this->_protect_identifiers( trim($tables[$i]) );
424: }
425: }
426:
427: return $this;
428: }
429:
430:
431: /**
432: * Offset the return set by a given number of records (useful for paging).
433: * @param int $off The number of records to offset the result by.
434: * @return self
435: */
436: public function offset ( $off )
437: {
438: $this->_offset = $off;
439:
440: return $this;
441: }
442:
443:
444: /**
445: * Order by
446: * @param string|string[] $order Columns and direction to order by - can
447: * be specified as individual names, an array of names, a string of comma
448: * separated names or any combination of those.
449: * @return self
450: */
451: public function order ( $order )
452: {
453: if ( $order === null ) {
454: return $this;
455: }
456:
457: if ( !is_array($order) ) {
458: $order = explode(",", $order);
459: }
460:
461: for ( $i=0 ; $i<count($order) ; $i++ ) {
462: // Simplify the white-space
463: $order[$i] = trim( preg_replace('/[\t ]+/', ' ', $order[$i]) );
464:
465: // Find the identifier so we don't escape that
466: if ( strpos($order[$i], ' ') !== false ) {
467: $direction = strstr($order[$i], ' ');
468: $identifier = substr($order[$i], 0, - strlen($direction));
469: }
470: else {
471: $direction = '';
472: $identifier = $order[$i];
473: }
474:
475: $this->_order[] = $this->_protect_identifiers( $identifier ).' '.$direction;
476: }
477:
478: return $this;
479: }
480:
481:
482: /**
483: * Set fields to a given value.
484: *
485: * Can be used in two different ways, as set( field, value ) or as an array of
486: * fields to set: set( array( 'fieldName' => 'value', ...) );
487: * @param string|string[] $set Can be given as a single string, when then $val
488: * must be set, or as an array of key/value pairs to be set.
489: * @param string $val When $set is given as a simple string, $set is the field
490: * name and this is the field's value.
491: * @param boolean $bind Should the value be bound or not
492: * @return self
493: */
494: public function set ( $set, $val=null, $bind=true )
495: {
496: if ( $set === null ) {
497: return $this;
498: }
499:
500: if ( !is_array($set) ) {
501: $set = array( $set => $val );
502: }
503:
504: foreach ($set as $key => $value) {
505: $this->_field[] = $key;
506:
507: if ( $bind ) {
508: $this->bind( ':'.$key, $value );
509: }
510: else {
511: $this->_noBind[$key] = $value;
512: }
513: }
514:
515: return $this;
516: }
517:
518:
519: /**
520: * Where query - multiple conditions are bound as ANDs.
521: *
522: * Can be used in two different ways, as where( field, value ) or as an array of
523: * conditions to use: where( array('fieldName', ...), array('value', ...) );
524: * @param string|string[]|callable $key Single field name, or an array of field names.
525: * If given as a function (i.e. a closure), the function is called, passing the
526: * query itself in as the only parameter, so the function can add extra conditions
527: * with parentheses around the additional parameters.
528: * @param string|string[] $value Single field value, or an array of
529: * values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending
530: * on the value of `$op` which should be `=` or `!=`.
531: * @param string $op Condition operator: <, >, = etc
532: * @param boolean $bind Escape the value (true, default) or not (false).
533: * @return self
534: *
535: * @example
536: * The following will produce
537: * `'WHERE name='allan' AND ( location='Scotland' OR location='Canada' )`:
538: *
539: * <code>
540: * $query
541: * ->where( 'name', 'allan' )
542: * ->where( function ($q) {
543: * $q->where( 'location', 'Scotland' );
544: * $q->where( 'location', 'Canada' );
545: * } );
546: * </code>
547: */
548: public function where ( $key, $value=null, $op="=", $bind=true )
549: {
550: if ( $key === null ) {
551: return $this;
552: }
553: else if ( is_callable($key) && is_object($key) ) { // is a closure
554: $this->_where_group( true, 'AND' );
555: $key( $this );
556: $this->_where_group( false, 'OR' );
557: }
558: else if ( !is_array($key) && is_array($value) ) {
559: for ( $i=0 ; $i<count($value) ; $i++ ) {
560: $this->where( $key, $value[$i], $op, $bind );
561: }
562: }
563: else {
564: $this->_where( $key, $value, 'AND ', $op, $bind );
565: }
566:
567: return $this;
568: }
569:
570:
571: /**
572: * Add addition where conditions to the query with an AND operator. An alias
573: * of `where` for naming consistency.
574: *
575: * Can be used in two different ways, as where( field, value ) or as an array of
576: * conditions to use: where( array('fieldName', ...), array('value', ...) );
577: * @param string|string[]|callable $key Single field name, or an array of field names.
578: * If given as a function (i.e. a closure), the function is called, passing the
579: * query itself in as the only parameter, so the function can add extra conditions
580: * with parentheses around the additional parameters.
581: * @param string|string[] $value Single field value, or an array of
582: * values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending
583: * on the value of `$op` which should be `=` or `!=`.
584: * @param string $op Condition operator: <, >, = etc
585: * @param boolean $bind Escape the value (true, default) or not (false).
586: * @return self
587: */
588: public function and_where ( $key, $value=null, $op="=", $bind=true )
589: {
590: return $this->where( $key, $value, $op, $bind );
591: }
592:
593:
594: /**
595: * Add addition where conditions to the query with an OR operator.
596: *
597: * Can be used in two different ways, as where( field, value ) or as an array of
598: * conditions to use: where( array('fieldName', ...), array('value', ...) );
599: * @param string|string[]|callable $key Single field name, or an array of field names.
600: * If given as a function (i.e. a closure), the function is called, passing the
601: * query itself in as the only parameter, so the function can add extra conditions
602: * with parentheses around the additional parameters.
603: * @param string|string[] $value Single field value, or an array of
604: * values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending
605: * on the value of `$op` which should be `=` or `!=`.
606: * @param string $op Condition operator: <, >, = etc
607: * @param boolean $bind Escape the value (true, default) or not (false).
608: * @return self
609: */
610: public function or_where ( $key, $value=null, $op="=", $bind=true )
611: {
612: if ( $key === null ) {
613: return $this;
614: }
615: else if ( is_callable($key) && is_object($key) ) {
616: $this->_where_group( true, 'OR' );
617: $key( $this );
618: $this->_where_group( false, 'OR' );
619: }
620: else {
621: if ( !is_array($key) && is_array($value) ) {
622: for ( $i=0 ; $i<count($value) ; $i++ ) {
623: $this->or_where( $key, $value[$i], $op, $bind );
624: }
625: return $this;
626: }
627:
628: $this->_where( $key, $value, 'OR ', $op, $bind );
629: }
630:
631: return $this;
632: }
633:
634:
635: /**
636: * Provide grouping for WHERE conditions. Use it with a callback function to
637: * automatically group any conditions applied inside the method.
638: *
639: * For legacy reasons this method also provides the ability to explicitly
640: * define if a grouping bracket should be opened or closed in the query.
641: * This method is not prefer.
642: *
643: * @param boolean|callable $inOut If callable it will create the group
644: * automatically and pass the query into the called function. For
645: * legacy operations use `true` to open brackets, `false` to close.
646: * @param string $op Conditional operator to use to join to the
647: * preceding condition. Default `AND`.
648: * @return self
649: *
650: * @example
651: * <code>
652: * $query->where_group( function ($q) {
653: * $q->where( 'location', 'Edinburgh' );
654: * $q->where( 'position', 'Manager' );
655: * } );
656: * </code>
657: */
658: public function where_group ( $inOut, $op='AND' )
659: {
660: if ( is_callable($inOut) && is_object($inOut) ) {
661: $this->_where_group( true, $op );
662: $inOut( $this );
663: $this->_where_group( false, $op );
664: }
665: else {
666: $this->_where_group( $inOut, $op );
667: }
668:
669: return $this;
670: }
671:
672:
673: /**
674: * Provide a method that can be used to perform a `WHERE ... IN (...)` query
675: * with bound values and parameters.
676: *
677: * Note this is only suitable for local values, not a sub-query. For that use
678: * `->where()` with an unbound value.
679: *
680: * @param string Field name
681: * @param array Values
682: * @param string Conditional operator to use to join to the
683: * preceding condition. Default `AND`.
684: * @return self
685: */
686: public function where_in ( $field, $arr, $operator="AND" )
687: {
688: if ( count($arr) === 0 ) {
689: return $this;
690: }
691:
692: $binders = array();
693: $prefix = ':wherein';
694:
695: // Need to build an array of the binders (having bound the values) so
696: // the query can be constructed
697: for ( $i=0, $ien=count($arr) ; $i<$ien ; $i++ ) {
698: $binder = $prefix.$this->_whereInCnt;
699:
700: $this->bind( $binder, $arr[$i] );
701:
702: $binders[] = $binder;
703: $this->_whereInCnt++;
704: }
705:
706: $this->_where[] = array(
707: 'operator' => $operator,
708: 'group' => null,
709: 'field' => $this->_protect_identifiers($field),
710: 'query' => $this->_protect_identifiers($field) .' IN ('.implode(', ', $binders).')'
711: );
712:
713: return $this;
714: }
715:
716:
717:
718: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
719: * Protected methods
720: */
721:
722: /**
723: * Create a comma separated field list
724: * @param bool $addAlias Flag to add an alias
725: * @return string
726: * @internal
727: */
728: protected function _build_field( $addAlias=false )
729: {
730: $a = array();
731: $asAlias = $this->_supportsAsAlias ?
732: ' as ' :
733: ' ';
734:
735: for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
736: $field = $this->_field[$i];
737:
738: // Keep the name when referring to a table
739: if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {
740: $split = preg_split( '/ as (?![^\(]*\))/i', $field );
741:
742: if ( count($split) > 1 ) {
743: $a[] = $this->_protect_identifiers( $split[0] ).$asAlias.
744: $this->_field_quote. $split[1] .$this->_field_quote;
745: }
746: else {
747: $a[] = $this->_protect_identifiers( $field ).$asAlias.
748: $this->_field_quote. $field .$this->_field_quote;
749: }
750: }
751: else if ( $addAlias && strpos($field, '(') !== false && ! strpos($field, ' as ') ) {
752: $a[] = $this->_protect_identifiers( $field ).$asAlias.
753: $this->_field_quote. $field .$this->_field_quote;
754: }
755: else {
756: $a[] = $this->_protect_identifiers( $field );
757: }
758: }
759:
760: return ' '.implode(', ', $a).' ';
761: }
762:
763: /**
764: * Create a JOIN statement list
765: * @return string
766: * @internal
767: */
768: protected function _build_join()
769: {
770: return implode(' ', $this->_join);
771: }
772:
773: /**
774: * Create the LIMIT / OFFSET string
775: *
776: * MySQL and Postgres stylee - anything else can have the driver override
777: * @return string
778: * @internal
779: */
780: protected function _build_limit()
781: {
782: $out = '';
783:
784: if ( $this->_limit ) {
785: $out .= ' LIMIT '.$this->_limit;
786: }
787:
788: if ( $this->_offset ) {
789: $out .= ' OFFSET '.$this->_offset;
790: }
791:
792: return $out;
793: }
794:
795: /**
796: * Create the ORDER BY string
797: * @return string
798: * @internal
799: */
800: protected function _build_order()
801: {
802: if ( count( $this->_order ) > 0 ) {
803: return ' ORDER BY '.implode(', ', $this->_order).' ';
804: }
805: return '';
806: }
807:
808: /**
809: * Create a set list
810: * @return string
811: * @internal
812: */
813: protected function _build_set()
814: {
815: $a = array();
816:
817: for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
818: $field = $this->_field[$i];
819:
820: if ( isset( $this->_noBind[ $field ] ) ) {
821: $a[] = $this->_protect_identifiers( $field ) .' = '. $this->_noBind[ $field ];
822: }
823: else {
824: $a[] = $this->_protect_identifiers( $field ) .' = :'. $this->_safe_bind( $field );
825: }
826: }
827:
828: return ' '.implode(', ', $a).' ';
829: }
830:
831: /**
832: * Create the TABLE list
833: * @return string
834: * @internal
835: */
836: protected function _build_table()
837: {
838: if ( $this->_type === 'insert' ) {
839: // insert, update and delete statements don't need or want aliases in the table name
840: $a = array();
841:
842: for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) {
843: $table = str_ireplace( ' as ', ' ', $this->_table[$i] );
844: $tableParts = explode( ' ', $table );
845:
846: $a[] = $tableParts[0];
847: }
848:
849: return ' '.implode(', ', $a).' ';
850: }
851:
852: return ' '.implode(', ', $this->_table).' ';
853: }
854:
855: /**
856: * Create a bind field value list
857: * @return string
858: * @internal
859: */
860: protected function _build_value()
861: {
862: $a = array();
863:
864: for ( $i=0, $ien=count($this->_field) ; $i<$ien ; $i++ ) {
865: $a[] = ' :'.$this->_safe_bind( $this->_field[$i] );
866: }
867:
868: return ' '.implode(', ', $a).' ';
869: }
870:
871: /**
872: * Create the WHERE statement
873: * @return string
874: * @internal
875: */
876: protected function _build_where()
877: {
878: if ( count($this->_where) === 0 ) {
879: return "";
880: }
881:
882: $condition = "WHERE ";
883:
884: for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
885: if ( $i === 0 ) {
886: // Nothing (simplifies the logic!)
887: }
888: else if ( $this->_where[$i]['group'] === ')' ) {
889: // If a group has been used but no conditions were added inside
890: // of, we don't want to end up with `()` in the SQL as that is
891: // invalid, so add a 1.
892: if ( $this->_where[$i-1]['group'] === '(' ) {
893: $condition .= '1=1';
894: }
895: // else nothing
896: }
897: else if ( $this->_where[$i-1]['group'] === '(' ) {
898: // Nothing
899: }
900: else {
901: $condition .= $this->_where[$i]['operator'];
902: }
903:
904: if ( $this->_where[$i]['group'] !== null ) {
905: $condition .= $this->_where[$i]['group'];
906: }
907: else {
908: $condition .= $this->_where[$i]['query'] .' ';
909: }
910: }
911:
912: return $condition;
913: }
914:
915: /**
916: * Create a DELETE statement
917: * @return Result
918: * @internal
919: */
920: protected function _delete()
921: {
922: $this->_prepare(
923: 'DELETE FROM '
924: .$this->_build_table()
925: .$this->_build_where()
926: );
927:
928: return $this->_exec();
929: }
930:
931: /**
932: * Execute the query. Provided by the driver
933: * @return Result
934: * @internal
935: */
936: protected function _exec()
937: {}
938:
939: /**
940: * Create an INSERT statement
941: * @return Result
942: * @internal
943: */
944: protected function _insert()
945: {
946: $this->_prepare(
947: 'INSERT INTO '
948: .$this->_build_table().' ('
949: .$this->_build_field()
950: .') '
951: .'VALUES ('
952: .$this->_build_value()
953: .')'
954: );
955:
956: return $this->_exec();
957: }
958:
959: /**
960: * Prepare the SQL query by populating the bound variables.
961: * Provided by the driver
962: * @return void
963: * @internal
964: */
965: protected function _prepare( $sql )
966: {}
967:
968: /**
969: * Protect field names
970: * @param string $identifier String to be protected
971: * @return string
972: * @internal
973: */
974: protected function _protect_identifiers( $identifier )
975: {
976: $idl = $this->_identifier_limiter;
977:
978: // No escaping character
979: if ( ! $idl ) {
980: return $identifier;
981: }
982:
983: $left = $idl[0];
984: $right = $idl[1];
985:
986: // Dealing with a function or other expression? Just return immediately
987: if (strpos($identifier, '(') !== FALSE || strpos($identifier, '*') !== FALSE || strpos($identifier, ' ') !== FALSE)
988: {
989: return $identifier;
990: }
991:
992: // Going to be operating on the spaces in strings, to simplify the white-space
993: $identifier = preg_replace('/[\t ]+/', ' ', $identifier);
994:
995: // Find if our identifier has an alias, so we don't escape that
996: if ( strpos($identifier, ' as ') !== false ) {
997: $alias = strstr($identifier, ' as ');
998: $identifier = substr($identifier, 0, - strlen($alias));
999: }
1000: else {
1001: $alias = '';
1002: }
1003:
1004: $a = explode('.', $identifier);
1005: return $left . implode($right.'.'.$left, $a) . $right . $alias;
1006: }
1007:
1008: /**
1009: * Passed in SQL statement
1010: * @return Result
1011: * @internal
1012: */
1013: protected function _raw( $sql )
1014: {
1015: $this->_prepare( $sql );
1016:
1017: return $this->_exec();
1018: }
1019:
1020: /**
1021: * The characters that can be used for the PDO bindValue name are quite
1022: * limited (`[a-zA-Z0-9_]+`). We need to abstract this out to allow slightly
1023: * more complex expressions including dots for easy aliasing
1024: * @param string $name Field name
1025: * @return string
1026: * @internal
1027: */
1028: protected function _safe_bind ( $name )
1029: {
1030: $name = str_replace('.', '_1_', $name);
1031: $name = str_replace('-', '_2_', $name);
1032: $name = str_replace('/', '_3_', $name);
1033: $name = str_replace('\\', '_4_', $name);
1034: $name = str_replace(' ', '_5_', $name);
1035:
1036: return $name;
1037: }
1038:
1039: /**
1040: * Create a SELECT statement
1041: * @return Result
1042: * @internal
1043: */
1044: protected function _select()
1045: {
1046: $this->_prepare(
1047: 'SELECT '.($this->_distinct ? 'DISTINCT ' : '')
1048: .$this->_build_field( true )
1049: .'FROM '.$this->_build_table()
1050: .$this->_build_join()
1051: .$this->_build_where()
1052: .$this->_build_order()
1053: .$this->_build_limit()
1054: );
1055:
1056: return $this->_exec();
1057: }
1058:
1059: /**
1060: * Create an UPDATE statement
1061: * @return Result
1062: * @internal
1063: */
1064: protected function _update()
1065: {
1066: $this->_prepare(
1067: 'UPDATE '
1068: .$this->_build_table()
1069: .'SET '.$this->_build_set()
1070: .$this->_build_where()
1071: );
1072:
1073: return $this->_exec();
1074: }
1075:
1076: /**
1077: * Add an individual where condition to the query.
1078: * @internal
1079: * @param $where
1080: * @param null $value
1081: * @param string $type
1082: * @param string $op
1083: * @param bool $bind
1084: */
1085: protected function _where ( $where, $value=null, $type='AND ', $op="=", $bind=true )
1086: {
1087: if ( $where === null ) {
1088: return;
1089: }
1090: else if ( !is_array($where) ) {
1091: $where = array( $where => $value );
1092: }
1093:
1094: foreach ($where as $key => $value) {
1095: $i = count( $this->_where );
1096:
1097: if ( $value === null ) {
1098: // Null query
1099: $this->_where[] = array(
1100: 'operator' => $type,
1101: 'group' => null,
1102: 'field' => $this->_protect_identifiers($key),
1103: 'query' => $this->_protect_identifiers($key) .( $op === '=' ?
1104: ' IS NULL' :
1105: ' IS NOT NULL')
1106: );
1107: }
1108: else if ( $bind ) {
1109: // Binding condition (i.e. escape data)
1110: $this->_where[] = array(
1111: 'operator' => $type,
1112: 'group' => null,
1113: 'field' => $this->_protect_identifiers($key),
1114: 'query' => $this->_protect_identifiers($key) .' '.$op.' '.$this->_safe_bind(':where_'.$i)
1115: );
1116: $this->bind( ':where_'.$i, $value );
1117: }
1118: else {
1119: // Non-binding condition
1120: $this->_where[] = array(
1121: 'operator' => $type,
1122: 'group' => null,
1123: 'field' => null,
1124: 'query' => $this->_protect_identifiers($key) .' '. $op .' '. $this->_protect_identifiers($value)
1125: );
1126: }
1127: }
1128: }
1129:
1130: /**
1131: * Add parentheses to a where condition
1132: * @return string
1133: * @internal
1134: */
1135: protected function _where_group ( $inOut, $op )
1136: {
1137: $this->_where[] = array(
1138: "group" => $inOut ? '(' : ')',
1139: "operator" => $op
1140: );
1141: }
1142: };
1143:
1144:
1145: