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