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