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
31: * {@link \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 required
35: * for individual database types. Individual drives could add additional methods,
36: * but this is discouraged to ensure that the API is the same for all database types.
37: */
38: class Query {
39: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
40: * Constructor
41: */
42:
43: /**
44: * Query instance constructor.
45: *
46: * Note that typically instances of this class will be automatically created
47: * through the {@link \DataTables\Database::query} method.
48: * @param Database $db Database instance
49: * @param string $type Query type - 'select', 'insert', 'update' or 'delete'
50: * @param string|string[] $table Tables to operate on - see {@link table}.
51: */
52: public function __construct( $db, $type, $table=null )
53: {
54: $this->_dbcon = $db;
55: $this->_type = $type;
56: $this->table( $table );
57: }
58:
59:
60: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
61: * Private properties
62: */
63:
64: /**
65: * @var string Driver to use
66: * @internal
67: */
68: protected $_type = "";
69:
70: /**
71: * @var resource Database connection
72: * @internal
73: */
74: protected $_dbcon = null;
75:
76: /**
77: * @var array
78: * @internal
79: */
80: protected $_table = array();
81:
82: /**
83: * @var array
84: * @internal
85: */
86: protected $_field = array();
87:
88: /**
89: * @var array
90: * @internal
91: */
92: protected $_value = array();
93:
94: /**
95: * @var array
96: * @internal
97: */
98: protected $_where = array();
99:
100: /**
101: * @var array
102: * @internal
103: */
104: protected $_join = array();
105:
106: /**
107: * @var array
108: * @internal
109: */
110: protected $_order = array();
111:
112: /**
113: * @var int
114: * @internal
115: */
116: protected $_limit = null;
117:
118: /**
119: * @var int
120: * @internal
121: */
122: protected $_offset = null;
123:
124: /**
125: * @var string
126: * @internal
127: */
128: protected $_identifier_limiter = '`';
129:
130:
131:
132: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
133: * Static methods
134: */
135:
136: /**
137: * Commit a transaction.
138: * @param * $dbh The Database handle (typically a PDO object, but not always).
139: */
140: public static function commit ( $dbh )
141: {
142: $dbh->commit();
143: }
144:
145: /**
146: * Database connection - override by the database driver.
147: * @param string $user User name
148: * @param string $pass Password
149: * @param string $host Host name
150: * @param string $db Database name
151: * @return Query
152: */
153: public static function connect ( $user, $pass, $host, $port, $db )
154: {
155: return false;
156: }
157:
158:
159: /**
160: * Start a database transaction
161: * @param * $dbh The Database handle (typically a PDO object, but not always).
162: */
163: public static function transaction ( $dbh )
164: {
165: $dbh->beginTransaction();
166: }
167:
168:
169: /**
170: * Rollback the database state to the start of the transaction.
171: * @param * $dbh The Database handle (typically a PDO object, but not always).
172: */
173: public static function rollback ( $dbh )
174: {
175: $dbh->rollBack();
176: }
177:
178:
179:
180: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
181: * Public properties
182: */
183:
184: /**
185: * Execute the query.
186: * @param string $sql SQL string to execute (only if _type is 'raw').
187: * @return Result
188: */
189: public function exec ( $sql=null )
190: {
191: $type = strtolower( $this->_type );
192:
193: if ( $type === 'select' ) {
194: return $this->_select();
195: }
196: else if ( $type === 'insert' ) {
197: return $this->_insert();
198: }
199: else if ( $type === 'update' ) {
200: return $this->_update();
201: }
202: else if ( $type === 'delete' ) {
203: return $this->_delete();
204: }
205: else if ( $type === 'raw' ) {
206: return $this->_raw( $sql );
207: }
208:
209: throw new \Exception("Unknown database command or not supported: ".$type, 1);
210: }
211:
212:
213: /**
214: * Get fields.
215: * @param string|string[] $get,... Fields to get - can be specified as
216: * individual fields, an array of fields, a string of comma separated
217: * fields or any combination of those.
218: * @return self
219: */
220: public function get ( $get )
221: {
222: if ( $get === null ) {
223: return $this;
224: }
225:
226: $args = func_get_args();
227:
228: for ( $i=0 ; $i<count($args) ; $i++ ) {
229: if ( !is_array($args[$i]) ) {
230: $get = explode(", ", $args[$i]);
231: }
232:
233: $this->_field = array_merge( $this->_field, $get );
234: }
235:
236: return $this;
237: }
238:
239:
240: /**
241: * Perform a JOIN operation
242: * @param sting $table Table name to do the JOIN on
243: * @param string $condition JOIN condition
244: * @param string $type JOIN type
245: * @return self
246: */
247: public function join ( $table, $condition, $type='' )
248: {
249: if ($type !== '') {
250: $type = strtoupper(trim($type));
251:
252: if ( ! in_array($type, array('LEFT', 'RIGHT', 'INNER', 'OUTER', 'LEFT OUTER', 'RIGHT OUTER'))) {
253: $type = '';
254: }
255: else {
256: $type .= ' ';
257: }
258: }
259:
260: // Protect the identifiers
261: if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $condition, $match))
262: {
263: $match[1] = $this->_protect_identifiers( $match[1] );
264: $match[3] = $this->_protect_identifiers( $match[3] );
265:
266: $condition = $match[1].$match[2].$match[3];
267: }
268:
269: $this->_join[] = $type .'JOIN '. $this->_protect_identifiers($table) .' ON '. $condition .' ';
270:
271: return $this;
272: }
273:
274:
275: /**
276: * Limit the result set to a certain size.
277: * @param int $lim The number of records to limit the result to.
278: * @return self
279: */
280: public function limit ( $lim )
281: {
282: $this->_limit = $lim;
283:
284: return $this;
285: }
286:
287:
288: /**
289: * Set table(s) to perform the query on.
290: * @param string|string[] $table,... Table(s) to use - can be specified as
291: * individual names, an array of names, a string of comma separated
292: * names or any combination of those.
293: * @return self
294: */
295: public function table ( $table )
296: {
297: if ( $table === null ) {
298: return $this;
299: }
300:
301: if ( !is_array($table) ) {
302: $table = explode(", ", $table);
303: }
304:
305: for ( $i=0 ; $i<count($table) ; $i++ ) {
306: $this->_table[] = $this->_protect_identifiers( $table[$i] );
307: }
308:
309: return $this;
310: }
311:
312:
313: /**
314: * Offset the return set by a given number of records (useful for paging).
315: * @param int $off The number of records to offset the result by.
316: * @return self
317: */
318: public function offset ( $off )
319: {
320: $this->_offset = $off;
321:
322: return $this;
323: }
324:
325:
326: /**
327: * Order by
328: * @param string|string[] $order Columns and direction to order by - can
329: * be specified as individual names, an array of names, a string of comma
330: * separated names or any combination of those.
331: * @return self
332: */
333: public function order ( $order )
334: {
335: if ( $order === null ) {
336: return $this;
337: }
338:
339: if ( !is_array($order) ) {
340: $order = explode(", ", $order);
341: }
342:
343: for ( $i=0 ; $i<count($order) ; $i++ ) {
344: // Simplify the white-space
345: $order[$i] = preg_replace('/[\t ]+/', ' ', $order[$i]);
346:
347: // Find the identifier so we don't escape that
348: if ( strpos($order[$i], ' ') !== false ) {
349: $direction = strstr($order[$i], ' ');
350: $identifier = substr($order[$i], 0, - strlen($direction));
351: }
352: else {
353: $direction = '';
354: }
355:
356: $this->_order[] = $this->_protect_identifiers( $identifier ).' '.$direction;
357: }
358:
359: return $this;
360: }
361:
362:
363: /**
364: * Set fields to a given value.
365: *
366: * Can be used in two different ways, as set( field, value ) or as an array of
367: * fields to set: set( array( 'fieldName' => 'value', ...) );
368: * @param string|string[] $set Can be given as a single string, when then $val
369: * must be set, or as an array of key/value pairs to be set.
370: * @param string $val When $set is given as a simple string, $set is the field
371: * name and this is the field's value.
372: * @return self
373: */
374: public function set ( $set, $val=null )
375: {
376: if ( $set === null ) {
377: return $this;
378: }
379:
380: if ( !is_array($set) ) {
381: $set = array( $set => $val );
382: }
383:
384: foreach ($set as $key => $value) {
385: $this->_field[] = $key;
386: $this->_value[] = $value;
387: }
388:
389: return $this;
390: }
391:
392:
393: /**
394: * Where query - multiple conditions are bound as ANDs.
395: *
396: * Can be used in two different ways, as where( field, value ) or as an array of
397: * conditions to use: where( array('fieldName', ...), array('value', ...) );
398: * @param string|string[]|function $key Single field name, or an array of field names.
399: * If given as a function (i.e. a closure), the function is called, passing the
400: * query itself in as the only parameter, so the function can add extra conditions
401: * with parentheses around the additional parameters.
402: * @param string|string[] $value Single field value, or an array of values.
403: * @param string $op Condition operator: <, >, = etc
404: * @param boolean $bind Escape the value (true, default) or not (false).
405: * @return self
406: *
407: * @example
408: * The following will produce
409: * `'WHERE name='allan' AND ( location='Scotland' OR location='Canada' )`:
410: *
411: * <code>
412: * $query
413: * ->where( 'name', 'allan' )
414: * ->where( function ($q) {
415: * $q->where( 'location', 'Scotland' );
416: * $q->where( 'location', 'Canada' );
417: * } );
418: * </code>
419: */
420: public function where ( $key, $value=null, $op="=", $bind=true )
421: {
422: if ( $key === null ) {
423: return $this;
424: }
425: else if ( is_callable($key) ) {
426: $this->_where_group( true, 'AND' );
427: $key( $this );
428: $this->_where_group( false, 'OR' );
429: }
430: else {
431: if ( !is_array($key) && is_array($value) ) {
432: for ( $i=0 ; $i<count($value) ; $i++ ) {
433: $this->where( $key, $value[$i], $op, $bind );
434: }
435: return $this;
436: }
437:
438: $this->_where( $key, $value, 'AND ', $op, $bind );
439: }
440:
441: return $this;
442: }
443:
444:
445: /**
446: * Add addition where conditions to the query with an AND operator. An alias
447: * of `where` for naming consistency.
448: *
449: * Can be used in two different ways, as where( field, value ) or as an array of
450: * conditions to use: where( array('fieldName', ...), array('value', ...) );
451: * @param string|string[]|function $key Single field name, or an array of field names.
452: * If given as a function (i.e. a closure), the function is called, passing the
453: * query itself in as the only parameter, so the function can add extra conditions
454: * with parentheses around the additional parameters.
455: * @param string|string[] $value Single field value, or an array of values.
456: * @param string $op Condition operator: <, >, = etc
457: * @param boolean $bind Escape the value (true, default) or not (false).
458: * @return self
459: */
460: public function and_where ( $key, $value=null, $op="=", $bind=true )
461: {
462: return $this->where( $key, $value, $op, $bind );
463: }
464:
465:
466: /**
467: * Add addition where conditions to the query with an OR operator.
468: *
469: * Can be used in two different ways, as where( field, value ) or as an array of
470: * conditions to use: where( array('fieldName', ...), array('value', ...) );
471: * @param string|string[]|function $key Single field name, or an array of field names.
472: * If given as a function (i.e. a closure), the function is called, passing the
473: * query itself in as the only parameter, so the function can add extra conditions
474: * with parentheses around the additional parameters.
475: * @param string|string[] $value Single field value, or an array of values.
476: * @param string $op Condition operator: <, >, = etc
477: * @param boolean $bind Escape the value (true, default) or not (false).
478: * @return self
479: */
480: public function or_where ( $key, $value=null, $op="=", $bind=true )
481: {
482: if ( $key === null ) {
483: return $this;
484: }
485: else if ( is_callable($key) ) {
486: $this->_where_group( true, 'OR' );
487: $key( $this );
488: $this->_where_group( false, 'OR' );
489: }
490: else {
491: if ( !is_array($key) && is_array($value) ) {
492: for ( $i=0 ; $i<count($value) ; $i++ ) {
493: $this->or_where( $key, $value[$i], $op, $bind );
494: }
495: return $this;
496: }
497:
498: $this->_where( $key, $value, 'OR ', $op, $bind );
499: }
500:
501: return $this;
502: }
503:
504:
505: /**
506: * Provide grouping for WHERE conditions. Calling this function with `true`
507: * as the first parameter will open a bracket, and `false` will then close
508: * it.
509: *
510: * @param boolean $inOut `true` to open brackets, `false` to close
511: * @param string $op Conditional operator to use to join to the
512: * preceding condition. Default `AND`.
513: * @return self
514: */
515: public function where_group ( $inOut, $op='AND' )
516: {
517: $this->_where_group( $inOut, $op );
518:
519: return $this;
520: }
521:
522:
523:
524: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
525: * Private properties
526: */
527:
528: /*
529: * SQL string builders
530: */
531:
532: /**
533: * Create a SELECT statement
534: * @return Result
535: * @internal
536: */
537: protected function _select()
538: {
539: $this->_prepare(
540: 'SELECT '.$this->_build_field()
541: .'FROM '.$this->_build_table()
542: .$this->_build_join()
543: .$this->_build_where()
544: .$this->_build_order()
545: .$this->_build_limit()
546: );
547:
548: return $this->_exec();
549: }
550:
551:
552: /**
553: * Create an INSERT statement
554: * @return Result
555: * @internal
556: */
557: protected function _insert()
558: {
559: $this->_prepare(
560: 'INSERT INTO '
561: .$this->_build_table().' ('
562: .$this->_build_field()
563: .') '
564: .'VALUES ('
565: .$this->_build_value()
566: .')'
567: );
568:
569: return $this->_exec();
570: }
571:
572:
573: /**
574: * Create an UPDATE statement
575: * @return Result
576: * @internal
577: */
578: protected function _update()
579: {
580: $this->_prepare(
581: 'UPDATE '
582: .$this->_build_table()
583: .'SET '.$this->_build_set()
584: .$this->_build_where()
585: );
586:
587: return $this->_exec();
588: }
589:
590:
591: /**
592: * Create a DELETE statement
593: * @return Result
594: * @internal
595: */
596: protected function _delete()
597: {
598: $this->_prepare(
599: 'DELETE FROM '
600: .$this->_build_table()
601: .$this->_build_where()
602: );
603:
604: return $this->_exec();
605: }
606:
607: /**
608: * Passed in SQL statement
609: * @return Result
610: * @internal
611: */
612: protected function _raw( $sql )
613: {
614: $this->_prepare( $sql );
615:
616: return $this->_exec();
617: }
618:
619:
620: /*
621: * SQL component specific string builders
622: */
623:
624: /**
625: * Create the TABLE list
626: * @return string
627: * @internal
628: */
629: protected function _build_table()
630: {
631: return ' '.implode(', ', $this->_table).' ';
632: }
633:
634:
635: /**
636: * Create a comma separated field list
637: * @return string
638: * @internal
639: */
640: protected function _build_field()
641: {
642: $a = array();
643:
644: for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
645: $a[] = $this->_protect_identifiers( $this->_field[$i] );
646: }
647:
648: return ' '.implode(', ', $a).' ';
649: }
650:
651:
652: /**
653: * Create the ORDER BY string
654: * @return string
655: * @internal
656: */
657: protected function _build_order()
658: {
659: if ( count( $this->_order ) > 0 ) {
660: return ' ORDER BY '.implode(', ', $this->_order).' ';
661: }
662: return '';
663: }
664:
665:
666: /**
667: * Create a bind field value list
668: * @return string
669: * @internal
670: */
671: protected function _build_value()
672: {
673: return ' :'.implode(', :', $this->_field).' ';
674: }
675:
676:
677: /**
678: * Create a JOIN statement list
679: * @return string
680: * @internal
681: */
682: protected function _build_join()
683: {
684: return implode(' ', $this->_join);
685: }
686:
687:
688: /**
689: * Create a set list
690: * @return string
691: * @internal
692: */
693: protected function _build_set()
694: {
695: $a = array();
696:
697: for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
698: $a[] = $this->_protect_identifiers( $this->_field[$i] ) .' = :'. $this->_field[$i];
699: }
700:
701: return ' '.implode(', ', $a).' ';
702: }
703:
704:
705: /**
706: * Create the WHERE statement
707: * @return string
708: * @internal
709: */
710: protected function _build_where()
711: {
712: if ( count($this->_where) === 0 ) {
713: return "";
714: }
715:
716: $where = "WHERE ";
717:
718: for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
719: if ( $i === 0 ) {
720: // Nothing (simplifies the logic!)
721: }
722: else if ( $this->_where[$i]['group'] === ')' ) {
723: // Nothing
724: }
725: else if ( $this->_where[$i-1]['group'] === '(' ) {
726: // Nothing
727: }
728: else {
729: $where .= $this->_where[$i]['operator'];
730: }
731:
732: if ( $this->_where[$i]['group'] !== null ) {
733: $where .= $this->_where[$i]['group'];
734: }
735: else {
736: $where .= $this->_where[$i]['query'] .' ';
737: }
738: }
739:
740: return $where;
741: }
742:
743:
744: /**
745: * Create the LIMIT / OFFSET string
746: *
747: * MySQL and Postgres stylee - anything else can have the driver override
748: * @return string
749: * @internal
750: */
751: protected function _build_limit()
752: {
753: $out = '';
754:
755: if ( $this->_limit ) {
756: $out .= ' LIMIT '.$this->_limit;
757: }
758:
759: if ( $this->_offset ) {
760: $out .= ' OFFSET '.$this->_offset;
761: }
762:
763: return $out;
764: }
765:
766:
767: /**
768: * Add an individual where condition to the query.
769: * @internal
770: */
771: protected function _where ( $where, $value=null, $type='AND ', $op="=", $bind=true )
772: {
773: $idl = $this->_identifier_limiter;
774:
775: if ( $where === null ) {
776: return;
777: }
778: else if ( !is_array($where) ) {
779: $where = array( $where => $value );
780: }
781:
782: foreach ($where as $key => $value) {
783: $i = count( $this->_where );
784:
785: if ( $bind ) {
786: $this->_where[] = array(
787: 'operator' => $type,
788: 'group' => null,
789: 'field' => $this->_protect_identifiers($key),
790: 'value' => $value,
791: 'binder' => ':where_'. str_replace('.', '_', $i),
792: 'query' => $this->_protect_identifiers($key) .' '.$op.' :where_'. str_replace('.', '_', $i)
793: );
794: }
795: else {
796: $this->_where[] = array(
797: 'operator' => $type,
798: 'group' => null,
799: 'field' => null,
800: 'value' => null,
801: 'binder' => null,
802: 'query' => $this->_protect_identifiers($key) .' '. $op .' '. $this->_protect_identifiers($value)
803: );
804: }
805: }
806: }
807:
808:
809: /**
810: * Add parentheses to a where condition
811: * @return string
812: * @internal
813: */
814: protected function _where_group ( $inOut, $op )
815: {
816: $this->_where[] = array(
817: "group" => $inOut ? '(' : ')',
818: "operator" => $op,
819: 'binder' => null
820: );
821: }
822:
823:
824: /**
825: * Protect field names
826: * @return string
827: * @internal
828: */
829: protected function _protect_identifiers( $identifier )
830: {
831: $idl = $this->_identifier_limiter;
832:
833: // No escaping character
834: if ( $idl === '' ) {
835: return $identifier;
836: }
837:
838: // Dealing with a function? Just return immediately
839: // This is good enough for most cases, but not all (todo)
840: if (strpos($identifier, '(') !== FALSE || strpos($identifier, '*') !== FALSE)
841: {
842: return $identifier;
843: }
844:
845: // Going to be operating on the spaces in strings, to simplify the white-space
846: $identifier = preg_replace('/[\t ]+/', ' ', $identifier);
847:
848: // Find if our identifier has an alias, so we don't escape that
849: if ( strpos($identifier, ' as ') !== false ) {
850: $alias = strstr($identifier, ' as ');
851: $identifier = substr($identifier, 0, - strlen($alias));
852: }
853: else {
854: $alias = '';
855: }
856:
857: $a = explode('.', $identifier);
858: return $idl . implode($idl.'.'.$idl, $a) . $idl . $alias;
859: }
860: };
861:
862:
863: