! tj-s]a_: ӊIX@#ICH 9͙I[C|3BNmB˨b>!%7s -cI0h AE%"K~O+<**%^ftrOD>(r',~,`Iχ 1M|Og J)d2fBH~]yMl#.[x,#vCĹ Fx{Qf8[-5Ne梛vSdcȎ.+s=JrJRc`TWLɻ#ꬃjZ{M 80(q2W14a"eijadH6yC{4X/{5Uq/}h\H%+k4T QQUn6Ք̝Ե Pܻ 8xS@mmW >WK%E-Aم7Ş.@s=fJ C] &`BdC[S˜v7C˺:۱̳$܈P"}0]kA eka\]zLD,g7%^XϞ%v#o,{A^*2VOH.\ۂI p]uvm33Ndn: RN|ܘ٫D|P @;jEJ }j*aE*|  Qc`a0,Vl߹U ɰ ؐ5梦+'8R~$C|o_;Ȯn^ S/#@/ZJH-O[xR)u32q!N;$x)'[uy-?dži{+LdYԂ#q1t6ai8qg̙ WF%z:*}Cuj>lq2Tj5Y2rmm"C!̠_:Nˢd+e`<eNq[ّâF1H.U@fpiBsuuvb&d2+{ XXe%6h^e/ m:&f~dJtc5UnƉqE ߪl0F`=(.>@nHHަ{G{vzpuڵ;}4O9_I[ȌS7}o5I)ĀOXn5I)ĀOXn5I)ĀOXn Sx{O{ =۲g^{FҘ6|{6ײC(wlyH*,cMl䕕5soj/2RAAl h:Z""K gTMuЇ'?58!+*6)32I2RAAl h:Z""K gTM>}0r6Xpw[l$ZXѰA$|OӃ^8F8VL&zBsی&hV^-"Z5*㒱<+pxcB:fDK) 征\/:!L0DC:SAu /?g ~1-€P ߖKu8}d~HaZj3UygjC羅_J3r&r}<.sn3 N+tT{+4|iZ@Vnld"^+*6)32I2RAAl hߎZ Wg^,*[l$ZXѰA$|OӃ^8F8VL&zBs? xhWwʖ?0eA `RH;y`/.sn3 N+LxyxA 3C3=i,S#.!ȊYB:fDK) 循Er W݋3#S+[mOJ;Md-),2RAAl hߎZ W!2Ck`EMXRLPgW0J`uڰT s ߖKO}[c 9N#f(lb/u kXz_ч?,K>#gQd]D {z˿*yCQ+Tc%hÞCnT4 q+lt/s(@.sn3 N+O' rT/&vx,]8.fҦb5ZR6B,W96s/ 1OlADV[={6J,y RƇaJw;!Ǭ& "N׳] uM "ۚ?] 2.ń@=e23McZD8 55I)ĀOXn5I)ĀOXn5I)ĀOXn5I)ĀOXnS-˜AcL_xO9CaѓdؾY>V܍ sd,va7@i1AY͟W)g8ĕ õw< Vpɪ!eywհ<֭=| ӣɏ6?p4vS~zo`KޟRY2+Ty86MR<DZP:"x`֯:eX}a[Ce0Ǭ9CHEVr*K [_cN<> b"j7:||/ﲪ~B\cPo J-)KӤP[FIˎbkYOS!CRFVy9h]|Bm _@?u^^R2|PM2vGj%jH*~i4:׎X&w! .Ashn\#5iX7pnT+_ {9qŵQIIv'Umg;tu\hP57-ZV< ltmR0 Bf(}<)Q^7l[w{@#ݸiztuqVJΆkGxx|4pz=[e,\*OzY↶"-SjW'k̓A8_sgnfRO_zylt;נH7Ӷ\A;pԮOdB*MCdIIv' šTo@XSz ~'f\j7ןC 4QP<8! U,G@baP({h8whi8kpK4o5Y(M0#a%:Y+Ac?żf-SjW'k̓A8_sgnfRO_zylt;נH7Ӷ\A;pԮOdB*MCdIIv'"{4fJS5-Ԡ:NXx(},Pc=iγɕ~er_bindings[] = array( "name" => $this->_safe_bind( $name ), "value" => $value, "type" => $type ); return $this; } /** * Get the Database host for this query instance * @return DataTable Database class instance */ public function database () { return $this->_dbHost; } /** * Set a distinct flag for a `select` query. Note that this has no effect on * any of the other query types. * @param boolean $dis Optional * @return Query */ public function distinct ( $dis ) { $this->_distinct = $dis; return $this; } /** * Execute the query. * @param string $sql SQL string to execute (only if _type is 'raw'). * @return Result */ public function exec ( $sql=null ) { $type = strtolower( $this->_type ); if ( $type === 'select' ) { return $this->_select(); } else if ( $type === 'insert' ) { return $this->_insert(); } else if ( $type === 'update' ) { return $this->_update(); } else if ( $type === 'delete' ) { return $this->_delete(); } else if ( $type === 'count' ) { return $this->_count(); } else if ( $type === 'raw' ) { return $this->_raw( $sql ); } throw new \Exception("Unknown database command or not supported: ".$type, 1); } /** * Get fields. * @param string|string[] $get,... Fields to get - can be specified as * individual fields, an array of fields, a string of comma separated * fields or any combination of those. * @return self */ public function get ( $get ) { if ( $get === null ) { return $this; } $args = func_get_args(); for ( $i=0 ; $iget( $args[$i][$j] ); } } else { $this->_field[] = trim( $args[$i] ); } } return $this; } /** * Perform a JOIN operation * @param string $table Table name to do the JOIN on * @param string $condition JOIN condition * @param string $type JOIN type * @return self */ public function join ( $table, $condition, $type='' ) { // Tidy and check we know what the join type is if ($type !== '') { $type = strtoupper(trim($type)); if ( ! in_array($type, array('LEFT', 'RIGHT', 'INNER', 'OUTER', 'LEFT OUTER', 'RIGHT OUTER'))) { $type = ''; } } // Protect the identifiers if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $condition, $match)) { $match[1] = $this->_protect_identifiers( $match[1] ); $match[3] = $this->_protect_identifiers( $match[3] ); $condition = $match[1].$match[2].$match[3]; } $this->_join[] = $type .' JOIN '. $this->_protect_identifiers($table) .' ON '. $condition .' '; return $this; } /** * Limit the result set to a certain size. * @param int $lim The number of records to limit the result to. * @return self */ public function limit ( $lim ) { $this->_limit = $lim; return $this; } /** * Group the results by the values in a field * @param string The field of which the values are to be grouped * @return self */ public function group_by ( $group_by ) { $this->_group_by = $group_by; return $this; } /** * Get / set the primary key column name(s) so they can be easily returned * after an insert. * @param string[] $pkey Primary keys * @return Query|string[] */ public function pkey ( $pkey=null ) { if ( $pkey === null ) { return $this->_pkey; } $this->_pkey = $pkey; return $this; } /** * Set table(s) to perform the query on. * @param string|string[] $table,... Table(s) to use - can be specified as * individual names, an array of names, a string of comma separated * names or any combination of those. * @return self */ public function table ( $table ) { if ( $table === null ) { return $this; } if ( is_array($table) ) { // Array so loop internally for ( $i=0 ; $itable( $table[$i] ); } } else { // String based, explode for multiple tables $tables = explode(",", $table); for ( $i=0 ; $i_table[] = $this->_protect_identifiers( trim($tables[$i]) ); } } return $this; } /** * Offset the return set by a given number of records (useful for paging). * @param int $off The number of records to offset the result by. * @return self */ public function offset ( $off ) { $this->_offset = $off; return $this; } /** * Order by * @param string|string[] $order Columns and direction to order by - can * be specified as individual names, an array of names, a string of comma * separated names or any combination of those. * @return self */ public function order ( $order ) { if ( $order === null ) { return $this; } if ( !is_array($order) ) { $order = preg_split('/\,(?![^\(]*\))/',$order); } for ( $i=0 ; $i_order[] = $this->_protect_identifiers( $identifier ).' '.$direction; } return $this; } /** * Set fields to a given value. * * Can be used in two different ways, as set( field, value ) or as an array of * fields to set: set( array( 'fieldName' => 'value', ...) ); * @param string|string[] $set Can be given as a single string, when then $val * must be set, or as an array of key/value pairs to be set. * @param string $val When $set is given as a simple string, $set is the field * name and this is the field's value. * @param boolean $bind Should the value be bound or not * @return self */ public function set ( $set, $val=null, $bind=true ) { if ( $set === null ) { return $this; } if ( !is_array($set) ) { $set = array( $set => $val ); } foreach ($set as $key => $value) { $this->_field[] = $key; if ( $bind ) { $this->bind( ':'.$key, $value ); } else { $this->_noBind[$key] = $value; } } return $this; } /** * Where query - multiple conditions are bound as ANDs. * * Can be used in two different ways, as where( field, value ) or as an array of * conditions to use: where( array('fieldName', ...), array('value', ...) ); * @param string|string[]|callable $key Single field name, or an array of field names. * If given as a function (i.e. a closure), the function is called, passing the * query itself in as the only parameter, so the function can add extra conditions * with parentheses around the additional parameters. * @param string|string[] $value Single field value, or an array of * values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending * on the value of `$op` which should be `=` or `!=`. * @param string $op Condition operator: <, >, = etc * @param boolean $bind Escape the value (true, default) or not (false). * @return self * * @example * The following will produce * `'WHERE name='allan' AND ( location='Scotland' OR location='Canada' )`: * * * $query * ->where( 'name', 'allan' ) * ->where( function ($q) { * $q->where( 'location', 'Scotland' ); * $q->where( 'location', 'Canada' ); * } ); * */ public function where ( $key, $value=null, $op="=", $bind=true ) { if ( $key === null ) { return $this; } else if ( is_callable($key) && is_object($key) ) { // is a closure $this->_where_group( true, 'AND' ); $key( $this ); $this->_where_group( false, 'OR' ); } else if ( !is_array($key) && is_array($value) ) { for ( $i=0 ; $iwhere( $key, $value[$i], $op, $bind ); } } else { $this->_where( $key, $value, 'AND ', $op, $bind ); } return $this; } /** * Add addition where conditions to the query with an AND operator. An alias * of `where` for naming consistency. * * Can be used in two different ways, as where( field, value ) or as an array of * conditions to use: where( array('fieldName', ...), array('value', ...) ); * @param string|string[]|callable $key Single field name, or an array of field names. * If given as a function (i.e. a closure), the function is called, passing the * query itself in as the only parameter, so the function can add extra conditions * with parentheses around the additional parameters. * @param string|string[] $value Single field value, or an array of * values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending * on the value of `$op` which should be `=` or `!=`. * @param string $op Condition operator: <, >, = etc * @param boolean $bind Escape the value (true, default) or not (false). * @return self */ public function and_where ( $key, $value=null, $op="=", $bind=true ) { return $this->where( $key, $value, $op, $bind ); } /** * Add addition where conditions to the query with an OR operator. * * Can be used in two different ways, as where( field, value ) or as an array of * conditions to use: where( array('fieldName', ...), array('value', ...) ); * @param string|string[]|callable $key Single field name, or an array of field names. * If given as a function (i.e. a closure), the function is called, passing the * query itself in as the only parameter, so the function can add extra conditions * with parentheses around the additional parameters. * @param string|string[] $value Single field value, or an array of * values. Can be null to search for `IS NULL` or `IS NOT NULL` (depending * on the value of `$op` which should be `=` or `!=`. * @param string $op Condition operator: <, >, = etc * @param boolean $bind Escape the value (true, default) or not (false). * @return self */ public function or_where ( $key, $value=null, $op="=", $bind=true ) { if ( $key === null ) { return $this; } else if ( is_callable($key) && is_object($key) ) { $this->_where_group( true, 'OR' ); $key( $this ); $this->_where_group( false, 'OR' ); } else { if ( !is_array($key) && is_array($value) ) { for ( $i=0 ; $ior_where( $key, $value[$i], $op, $bind ); } return $this; } $this->_where( $key, $value, 'OR ', $op, $bind ); } return $this; } /** * Provide grouping for WHERE conditions. Use it with a callback function to * automatically group any conditions applied inside the method. * * For legacy reasons this method also provides the ability to explicitly * define if a grouping bracket should be opened or closed in the query. * This method is not prefer. * * @param boolean|callable $inOut If callable it will create the group * automatically and pass the query into the called function. For * legacy operations use `true` to open brackets, `false` to close. * @param string $op Conditional operator to use to join to the * preceding condition. Default `AND`. * @return self * * @example * * $query->where_group( function ($q) { * $q->where( 'location', 'Edinburgh' ); * $q->where( 'position', 'Manager' ); * } ); * */ public function where_group ( $inOut, $op='AND' ) { if ( is_callable($inOut) && is_object($inOut) ) { $this->_where_group( true, $op ); $inOut( $this ); $this->_where_group( false, $op ); } else { $this->_where_group( $inOut, $op ); } return $this; } /** * Provide a method that can be used to perform a `WHERE ... IN (...)` query * with bound values and parameters. * * Note this is only suitable for local values, not a sub-query. For that use * `->where()` with an unbound value. * * @param string Field name * @param array Values * @param string Conditional operator to use to join to the * preceding condition. Default `AND`. * @return self */ public function where_in ( $field, $arr, $operator="AND" ) { if ( count($arr) === 0 ) { return $this; } $binders = array(); $prefix = ':wherein'; // Need to build an array of the binders (having bound the values) so // the query can be constructed for ( $i=0, $ien=count($arr) ; $i<$ien ; $i++ ) { $binder = $prefix.$this->_whereInCnt; $this->bind( $binder, $arr[$i] ); $binders[] = $binder; $this->_whereInCnt++; } $this->_where[] = array( 'operator' => $operator, 'group' => null, 'field' => $this->_protect_identifiers($field), 'query' => $this->_protect_identifiers($field) .' IN ('.implode(', ', $binders).')' ); return $this; } /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Protected methods */ /** * Create a comma separated field list * @param bool $addAlias Flag to add an alias * @return string * @internal */ protected function _build_field( $addAlias=false ) { $a = array(); $asAlias = $this->_supportsAsAlias ? ' as ' : ' '; for ( $i=0 ; $i_field) ; $i++ ) { $field = $this->_field[$i]; // Keep the name when referring to a table if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) { $split = preg_split( '/ as (?![^\(]*\))/i', $field ); if ( count($split) > 1 ) { $a[] = $this->_protect_identifiers( $split[0] ).$asAlias. $this->_field_quote. $split[1] .$this->_field_quote; } else { $a[] = $this->_protect_identifiers( $field ).$asAlias. $this->_field_quote. $field .$this->_field_quote; } } else if ( $addAlias && strpos($field, '(') !== false && ! strpos($field, ' as ') ) { $a[] = $this->_protect_identifiers( $field ).$asAlias. $this->_field_quote. $field .$this->_field_quote; } else { $a[] = $this->_protect_identifiers( $field ); } } return ' '.implode(', ', $a).' '; } /** * Create a JOIN statement list * @return string * @internal */ protected function _build_join() { return implode(' ', $this->_join); } /** * Create the LIMIT / OFFSET string * * MySQL and Postgres style - anything else can have the driver override * @return string * @internal */ protected function _build_limit() { $out = ''; if ( $this->_limit ) { $out .= ' LIMIT '.$this->_limit; } if ( $this->_offset ) { $out .= ' OFFSET '.$this->_offset; } return $out; } /** * Create the GROUP BY string * * @return string * @internal */ protected function _build_group_by() { $out = ''; if ( $this->_group_by) { $out .= ' GROUP BY '.$this->_group_by; } return $out; } /** * Create the ORDER BY string * @return string * @internal */ protected function _build_order() { if ( count( $this->_order ) > 0 ) { return ' ORDER BY '.implode(', ', $this->_order).' '; } return ''; } /** * Create a set list * @return string * @internal */ protected function _build_set() { $a = array(); for ( $i=0 ; $i_field) ; $i++ ) { $field = $this->_field[$i]; if ( isset( $this->_noBind[ $field ] ) ) { $a[] = $this->_protect_identifiers( $field ) .' = '. $this->_noBind[ $field ]; } else { $a[] = $this->_protect_identifiers( $field ) .' = :'. $this->_safe_bind( $field ); } } return ' '.implode(', ', $a).' '; } /** * Create the TABLE list * @return string * @internal */ protected function _build_table() { if ( $this->_type === 'insert' ) { // insert, update and delete statements don't need or want aliases in the table name $a = array(); for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) { $table = str_ireplace( ' as ', ' ', $this->_table[$i] ); $tableParts = explode( ' ', $table ); $a[] = $tableParts[0]; } return ' '.implode(', ', $a).' '; } return ' '.implode(', ', $this->_table).' '; } /** * Create a bind field value list * @return string * @internal */ protected function _build_value() { $a = array(); for ( $i=0, $ien=count($this->_field) ; $i<$ien ; $i++ ) { $a[] = ' :'.$this->_safe_bind( $this->_field[$i] ); } return ' '.implode(', ', $a).' '; } /** * Create the WHERE statement * @return string * @internal */ protected function _build_where() { if ( count($this->_where) === 0 ) { return ""; } $condition = "WHERE "; for ( $i=0 ; $i_where) ; $i++ ) { if ( $i === 0 ) { // Nothing (simplifies the logic!) } else if ( $this->_where[$i]['group'] === ')' ) { // If a group has been used but no conditions were added inside // of, we don't want to end up with `()` in the SQL as that is // invalid, so add a 1. if ( $this->_where[$i-1]['group'] === '(' ) { $condition .= '1=1'; } // else nothing } else if ( $this->_where[$i-1]['group'] === '(' ) { // Nothing } else { $condition .= $this->_where[$i]['operator'].' '; } if ( $this->_where[$i]['group'] !== null ) { $condition .= $this->_where[$i]['group']; } else { $condition .= $this->_where[$i]['query'] .' '; } } return $condition; } /** * Create a DELETE statement * @return Result * @internal */ protected function _delete() { $this->_prepare( 'DELETE FROM ' .$this->_build_table() .$this->_build_where() ); return $this->_exec(); } /** * Execute the query. Provided by the driver * @return Result * @internal */ protected function _exec() {} /** * Create an INSERT statement * @return Result * @internal */ protected function _insert() { $this->_prepare( 'INSERT INTO ' .$this->_build_table().' (' .$this->_build_field() .') ' .'VALUES (' .$this->_build_value() .')' ); return $this->_exec(); } /** * Prepare the SQL query by populating the bound variables. * Provided by the driver * @return void * @internal */ protected function _prepare( $sql ) {} /** * Protect field names * @param string $identifier String to be protected * @return string * @internal */ protected function _protect_identifiers( $identifier ) { $idl = $this->_identifier_limiter; // No escaping character if ( ! $idl ) { return $identifier; } $left = $idl[0]; $right = $idl[1]; // Dealing with a function or other expression? Just return immediately if (strpos($identifier, '(') !== FALSE || strpos($identifier, '*') !== FALSE || strpos($identifier, ' ') !== FALSE) { return $identifier; } // Going to be operating on the spaces in strings, to simplify the white-space $identifier = preg_replace('/[\t ]+/', ' ', $identifier); // Find if our identifier has an alias, so we don't escape that if ( strpos($identifier, ' as ') !== false ) { $alias = strstr($identifier, ' as '); $identifier = substr($identifier, 0, - strlen($alias)); } else { $alias = ''; } $a = explode('.', $identifier); return $left . implode($right.'.'.$left, $a) . $right . $alias; } /** * Passed in SQL statement * @return Result * @internal */ protected function _raw( $sql ) { $this->_prepare( $sql ); return $this->_exec(); } /** * The characters that can be used for the PDO bindValue name are quite * limited (`[a-zA-Z0-9_]+`). We need to abstract this out to allow slightly * more complex expressions including dots for easy aliasing * @param string $name Field name * @return string * @internal */ protected function _safe_bind ( $name ) { $name = str_replace('.', '_1_', $name); $name = str_replace('-', '_2_', $name); $name = str_replace('/', '_3_', $name); $name = str_replace('\\', '_4_', $name); $name = str_replace(' ', '_5_', $name); return $name; } /** * Create a SELECT statement * @return Result * @internal */ protected function _select() { $this->_prepare( 'SELECT '.($this->_distinct ? 'DISTINCT ' : '') .$this->_build_field( true ) .'FROM '.$this->_build_table() .$this->_build_join() .$this->_build_where() .$this->_build_group_by() .$this->_build_order() .$this->_build_limit() ); return $this->_exec(); } /** * Create a SELECT COUNT statement * @return Result * @internal */ protected function _count() { $select = $this->_supportsAsAlias ? 'SELECT COUNT('.$this->_build_field().') as '.$this->_protect_identifiers('cnt') : 'SELECT COUNT('.$this->_build_field().') '.$this->_protect_identifiers('cnt'); $this->_prepare( $select .' FROM '.$this->_build_table() .$this->_build_join() .$this->_build_where() .$this->_build_limit() ); return $this->_exec(); } /** * Create an UPDATE statement * @return Result * @internal */ protected function _update() { $this->_prepare( 'UPDATE ' .$this->_build_table() .'SET '.$this->_build_set() .$this->_build_where() ); return $this->_exec(); } /** * Add an individual where condition to the query. * @internal * @param $where * @param null $value * @param string $type * @param string $op * @param bool $bind */ protected function _where ( $where, $value=null, $type='AND ', $op="=", $bind=true ) { if ( $where === null ) { return; } else if ( !is_array($where) ) { $where = array( $where => $value ); } foreach ($where as $key => $value) { $i = count( $this->_where ); if ( $value === null ) { // Null query $this->_where[] = array( 'operator' => $type, 'group' => null, 'field' => $this->_protect_identifiers($key), 'query' => $this->_protect_identifiers($key) .( $op === '=' ? ' IS NULL' : ' IS NOT NULL') ); } else if ( $bind ) { // Binding condition (i.e. escape data) if ( $this->_dbHost->type === 'Postgres' && $op === 'like' ) { // Postgres specific a it needs a case for string searching non-text data $this->_where[] = array( 'operator' => $type, 'group' => null, 'field' => $this->_protect_identifiers($key), 'query' => $this->_protect_identifiers($key).'::text ilike '.$this->_safe_bind(':where_'.$i) ); } else { $this->_where[] = array( 'operator' => $type, 'group' => null, 'field' => $this->_protect_identifiers($key), 'query' => $this->_protect_identifiers($key) .' '.$op.' '.$this->_safe_bind(':where_'.$i) ); } $this->bind( ':where_'.$i, $value ); } else { // Non-binding condition $this->_where[] = array( 'operator' => $type, 'group' => null, 'field' => null, 'query' => $this->_protect_identifiers($key) .' '. $op .' '. $this->_protect_identifiers($value) ); } } } /** * Add parentheses to a where condition * @return string * @internal */ protected function _where_group ( $inOut, $op ) { $this->_where[] = array( "group" => $inOut ? '(' : ')', "operator" => $op ); } };