<?php

/**

* CodeIgniter

*

* An open source application development framework for PHP

*

* This content is released under the MIT License (MIT)

*

* Copyright (c) 2014 - 2015, British Columbia Institute of Technology

*

* Permission is hereby granted, free of charge, to any person obtaining a copy

* of this software and associated documentation files (the "Software"), to deal

* in the Software without restriction, including without limitation the rights

* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

* copies of the Software, and to permit persons to whom the Software is

* furnished to do so, subject to the following conditions:

*

* The above copyright notice and this permission notice shall be included in

* all copies or substantial portions of the Software.

*

* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN

* THE SOFTWARE.

*

* @package CodeIgniter

* @author EllisLab Dev Team

* @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (http://ellislab.com/)

* @copyright Copyright (c) 2014 - 2015, British Columbia Institute of Technology (http://bcit.ca/)

* @license http://opensource.org/licenses/MIT MIT License

* @link http://codeigniter.com

* @since Version 1.0.0

* @filesource

*/

defined('BASEPATH') OR exit('No direct script access allowed');

/**

* Query Builder Class

*

* This is the platform-independent base Query Builder implementation class.

*

* @package CodeIgniter

* @subpackage Drivers

* @category Database

* @author EllisLab Dev Team

* @link http://codeigniter.com/user_guide/database/

*/

abstract class CI_DB_query_builder extends CI_DB_driver {

/**

* Return DELETE SQL flag

*

* @var bool

*/

protected $return_delete_sql = FALSE;

/**

* Reset DELETE data flag

*

* @var bool

*/

protected $reset_delete_data = FALSE;

/**

* QB SELECT data

*

* @var array

*/

protected $qb_select = array();

/**

* QB DISTINCT flag

*

* @var bool

*/

protected $qb_distinct = FALSE;

/**

* QB FROM data

*

* @var array

*/

protected $qb_from = array();

/**

* QB JOIN data

*

* @var array

*/

protected $qb_join = array();

/**

* QB WHERE data

*

* @var array

*/

protected $qb_where = array();

/**

* QB GROUP BY data

*

* @var array

*/

protected $qb_groupby = array();

/**

* QB HAVING data

*

* @var array

*/

protected $qb_having = array();

/**

* QB keys

*

* @var array

*/

protected $qb_keys = array();

/**

* QB LIMIT data

*

* @var int

*/

protected $qb_limit = FALSE;

/**

* QB OFFSET data

*

* @var int

*/

protected $qb_offset = FALSE;

/**

* QB ORDER BY data

*

* @var array

*/

protected $qb_orderby = array();

/**

* QB data sets

*

* @var array

*/

protected $qb_set = array();

/**

* QB aliased tables list

*

* @var array

*/

protected $qb_aliased_tables = array();

/**

* QB WHERE group started flag

*

* @var bool

*/

protected $qb_where_group_started = FALSE;

/**

* QB WHERE group count

*

* @var int

*/

protected $qb_where_group_count = 0;

// Query Builder Caching variables

/**

* QB Caching flag

*

* @var bool

*/

protected $qb_caching = FALSE;

/**

* QB Cache exists list

*

* @var array

*/

protected $qb_cache_exists = array();

/**

* QB Cache SELECT data

*

* @var array

*/

protected $qb_cache_select = array();

/**

* QB Cache FROM data

*

* @var array

*/

protected $qb_cache_from = array();

/**

* QB Cache JOIN data

*

* @var array

*/

protected $qb_cache_join = array();

/**

* QB Cache WHERE data

*

* @var array

*/

protected $qb_cache_where = array();

/**

* QB Cache GROUP BY data

*

* @var array

*/

protected $qb_cache_groupby = array();

/**

* QB Cache HAVING data

*

* @var array

*/

protected $qb_cache_having = array();

/**

* QB Cache ORDER BY data

*

* @var array

*/

protected $qb_cache_orderby = array();

/**

* QB Cache data sets

*

* @var array

*/

protected $qb_cache_set = array();

/**

* QB No Escape data

*

* @var array

*/

protected $qb_no_escape = array();

/**

* QB Cache No Escape data

*

* @var array

*/

protected $qb_cache_no_escape = array();

// --------------------------------------------------------------------

/**

* Select

*

* Generates the SELECT portion of the query

*

* @param string

* @param mixed

* @return CI_DB_query_builder

*/

public function select($select = '*', $escape = NULL)

{

if (is_string($select))

{

$select = explode(',', $select);

}

// If the escape value was not set, we will base it on the global setting

is_bool($escape) OR $escape = $this->_protect_identifiers;

foreach ($select as $val)

{

$val = trim($val);

if ($val !== '')

{

$this->qb_select[] = $val;

$this->qb_no_escape[] = $escape;

if ($this->qb_caching === TRUE)

{

$this->qb_cache_select[] = $val;

$this->qb_cache_exists[] = 'select';

$this->qb_cache_no_escape[] = $escape;

}

}

}

return $this;

}

// --------------------------------------------------------------------

/**

* Select Max

*

* Generates a SELECT MAX(field) portion of a query

*

* @param string the field

* @param string an alias

* @return CI_DB_query_builder

*/

public function select_max($select = '', $alias = '')

{

return $this->_max_min_avg_sum($select, $alias, 'MAX');

}

// --------------------------------------------------------------------

/**

* Select Min

*

* Generates a SELECT MIN(field) portion of a query

*

* @param string the field

* @param string an alias

* @return CI_DB_query_builder

*/

public function select_min($select = '', $alias = '')

{

return $this->_max_min_avg_sum($select, $alias, 'MIN');

}

// --------------------------------------------------------------------

/**

* Select Average

*

* Generates a SELECT AVG(field) portion of a query

*

* @param string the field

* @param string an alias

* @return CI_DB_query_builder

*/

public function select_avg($select = '', $alias = '')

{

return $this->_max_min_avg_sum($select, $alias, 'AVG');

}

// --------------------------------------------------------------------

/**

* Select Sum

*

* Generates a SELECT SUM(field) portion of a query

*

* @param string the field

* @param string an alias

* @return CI_DB_query_builder

*/

public function select_sum($select = '', $alias = '')

{

return $this->_max_min_avg_sum($select, $alias, 'SUM');

}

// --------------------------------------------------------------------

/**

* SELECT [MAX|MIN|AVG|SUM]()

*

* @used-by select_max()

* @used-by select_min()

* @used-by select_avg()

* @used-by select_sum()

*

* @param string $select Field name

* @param string $alias

* @param string $type

* @return CI_DB_query_builder

*/

protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX')

{

if ( ! is_string($select) OR $select === '')

{

$this->display_error('db_invalid_query');

}

$type = strtoupper($type);

if ( ! in_array($type, array('MAX', 'MIN', 'AVG', 'SUM')))

{

show_error('Invalid function type: '.$type);

}

if ($alias === '')

{

$alias = $this->_create_alias_from_table(trim($select));

}

$sql = $type.'('.$this->protect_identifiers(trim($select)).') AS '.$this->escape_identifiers(trim($alias));

$this->qb_select[] = $sql;

$this->qb_no_escape[] = NULL;

if ($this->qb_caching === TRUE)

{

$this->qb_cache_select[] = $sql;

$this->qb_cache_exists[] = 'select';

}

return $this;

}

// --------------------------------------------------------------------

/**

* Determines the alias name based on the table

*

* @param string $item

* @return string

*/

protected function _create_alias_from_table($item)

{

if (strpos($item, '.') !== FALSE)

{

$item = explode('.', $item);

return end($item);

}

return $item;

}

// --------------------------------------------------------------------

/**

* DISTINCT

*

* Sets a flag which tells the query string compiler to add DISTINCT

*

* @param bool $val

* @return CI_DB_query_builder

*/

public function distinct($val = TRUE)

{

$this->qb_distinct = is_bool($val) ? $val : TRUE;

return $this;

}

// --------------------------------------------------------------------

/**

* From

*

* Generates the FROM portion of the query

*

* @param mixed $from can be a string or array

* @return CI_DB_query_builder

*/

public function from($from)

{

foreach ((array) $from as $val)

{

if (strpos($val, ',') !== FALSE)

{

foreach (explode(',', $val) as $v)

{

$v = trim($v);

$this->_track_aliases($v);

$this->qb_from[] = $v = $this->protect_identifiers($v, TRUE, NULL, FALSE);

if ($this->qb_caching === TRUE)

{

$this->qb_cache_from[] = $v;

$this->qb_cache_exists[] = 'from';

}

}

}

else

{

$val = trim($val);

// Extract any aliases that might exist. We use this information

// in the protect_identifiers to know whether to add a table prefix

$this->_track_aliases($val);

$this->qb_from[] = $val = $this->protect_identifiers($val, TRUE, NULL, FALSE);

if ($this->qb_caching === TRUE)

{

$this->qb_cache_from[] = $val;

$this->qb_cache_exists[] = 'from';

}

}

}

return $this;

}

// --------------------------------------------------------------------

/**

* JOIN

*

* Generates the JOIN portion of the query

*

* @param string

* @param string the join condition

* @param string the type of join

* @param string whether not to try to escape identifiers

* @return CI_DB_query_builder

*/

public function join($table, $cond, $type = '', $escape = NULL)

{

if ($type !== '')

{

$type = strtoupper(trim($type));

if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE))

{

$type = '';

}

else

{

$type .= ' ';

}

}

// Extract any aliases that might exist. We use this information

// in the protect_identifiers to know whether to add a table prefix

$this->_track_aliases($table);

is_bool($escape) OR $escape = $this->_protect_identifiers;

// Split multiple conditions

if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_OFFSET_CAPTURE))

{

$newcond = '';

$m[0][] = array('', strlen($cond));

for ($i = 0, $c = count($m[0]), $s = 0;

$i < $c;

$s = $m[0][$i][1] + strlen($m[0][$i][0]), $i++)

{

$temp = substr($cond, $s, ($m[0][$i][1] - $s));

$newcond .= preg_match("/([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $temp, $match)

? $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3])

: $temp;

$newcond .= $m[0][$i][0];

}

$cond = ' ON '.$newcond;

}

// Split apart the condition and protect the identifiers

elseif ($escape === TRUE && preg_match("/([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $cond, $match))

{

$cond = ' ON '.$this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]);

}

elseif ( ! $this->_has_operator($cond))

{

$cond = ' USING ('.($escape ? $this->escape_identifiers($cond) : $cond).')';

}

else

{

$cond = ' ON '.$cond;

}

// Do we want to escape the table name?

if ($escape === TRUE)

{

$table = $this->protect_identifiers($table, TRUE, NULL, FALSE);

}

// Assemble the JOIN statement

$this->qb_join[] = $join = $type.'JOIN '.$table.$cond;

if ($this->qb_caching === TRUE)

{

$this->qb_cache_join[] = $join;

$this->qb_cache_exists[] = 'join';

}

return $this;

}

// --------------------------------------------------------------------

/**

* WHERE

*

* Generates the WHERE portion of the query.

* Separates multiple calls with 'AND'.

*

* @param mixed

* @param mixed

* @param bool

* @return CI_DB_query_builder

*/

public function where($key, $value = NULL, $escape = NULL)

{

return $this->_wh('qb_where', $key, $value, 'AND ', $escape);

}

// --------------------------------------------------------------------

/**

* OR WHERE

*

* Generates the WHERE portion of the query.

* Separates multiple calls with 'OR'.

*

* @param mixed

* @param mixed

* @param bool

* @return CI_DB_query_builder

*/

public function or_where($key, $value = NULL, $escape = NULL)

{

return $this->_wh('qb_where', $key, $value, 'OR ', $escape);

}

// --------------------------------------------------------------------

/**

* WHERE, HAVING

*

* @used-by where()

* @used-by or_where()

* @used-by having()

* @used-by or_having()

*

* @param string $qb_key 'qb_where' or 'qb_having'

* @param mixed $key

* @param mixed $value

* @param string $type

* @param bool $escape

* @return CI_DB_query_builder

*/

protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL)

{

$qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where';

if ( ! is_array($key))

{

$key = array($key => $value);

}

// If the escape value was not set will base it on the global setting

is_bool($escape) OR $escape = $this->_protect_identifiers;

foreach ($key as $k => $v)

{

$prefix = (count($this->$qb_key) === 0 && count($this->$qb_cache_key) === 0)

? $this->_group_get_type('')

: $this->_group_get_type($type);

if ($v !== NULL)

{

if ($escape === TRUE)

{

$v = ' '.$this->escape($v);

}

if ( ! $this->_has_operator($k))

{

$k .= ' = ';

}

}

elseif ( ! $this->_has_operator($k))

{

// value appears not to have been set, assign the test to IS NULL

$k .= ' IS NULL';

}

elseif (preg_match('/\s*(!?=|<>|IS(?:\s+NOT)?)\s*$/i', $k, $match, PREG_OFFSET_CAPTURE))

{

$k = substr($k, 0, $match[0][1]).($match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL');

}

$this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape);

if ($this->qb_caching === TRUE)

{

$this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape);

$this->qb_cache_exists[] = substr($qb_key, 3);

}

}

return $this;

}

// --------------------------------------------------------------------

/**

* WHERE IN

*

* Generates a WHERE field IN('item', 'item') SQL query,

* joined with 'AND' if appropriate.

*

* @param string $key The field to search

* @param array $values The values searched on

* @param bool $escape

* @return CI_DB_query_builder

*/

public function where_in($key = NULL, $values = NULL, $escape = NULL)

{

return $this->_where_in($key, $values, FALSE, 'AND ', $escape);

}

// --------------------------------------------------------------------

/**

* OR WHERE IN

*

* Generates a WHERE field IN('item', 'item') SQL query,

* joined with 'OR' if appropriate.

*

* @param string $key The field to search

* @param array $values The values searched on

* @param bool $escape

* @return CI_DB_query_builder

*/

public function or_where_in($key = NULL, $values = NULL, $escape = NULL)

{

return $this->_where_in($key, $values, FALSE, 'OR ', $escape);

}

// --------------------------------------------------------------------

/**

* WHERE NOT IN

*

* Generates a WHERE field NOT IN('item', 'item') SQL query,

* joined with 'AND' if appropriate.

*

* @param string $key The field to search

* @param array $values The values searched on

* @param bool $escape

* @return CI_DB_query_builder

*/

public function where_not_in($key = NULL, $values = NULL, $escape = NULL)

{

return $this->_where_in($key, $values, TRUE, 'AND ', $escape);

}

// --------------------------------------------------------------------

/**

* OR WHERE NOT IN

*

* Generates a WHERE field NOT IN('item', 'item') SQL query,

* joined with 'OR' if appropriate.

*

* @param string $key The field to search

* @param array $values The values searched on

* @param bool $escape

* @return CI_DB_query_builder

*/

public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL)

{

return $this->_where_in($key, $values, TRUE, 'OR ', $escape);

}

// --------------------------------------------------------------------

/**

* Internal WHERE IN

*

* @used-by where_in()

* @used-by or_where_in()

* @used-by where_not_in()

* @used-by or_where_not_in()

*

* @param string $key The field to search

* @param array $values The values searched on

* @param bool $not If the statement would be IN or NOT IN

* @param string $type

* @param bool $escape

* @return CI_DB_query_builder

*/

protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL)

{

if ($key === NULL OR $values === NULL)

{

return $this;

}

if ( ! is_array($values))

{

$values = array($values);

}

is_bool($escape) OR $escape = $this->_protect_identifiers;

$not = ($not) ? ' NOT' : '';

$where_in = array();

foreach ($values as $value)

{

$where_in[] = $this->escape($value);

}

$prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type);

$where_in = array(

'condition' => $prefix.$key.$not.' IN('.implode(', ', $where_in).')',

'escape' => $escape

);

$this->qb_where[] = $where_in;

if ($this->qb_caching === TRUE)

{

$this->qb_cache_where[] = $where_in;

$this->qb_cache_exists[] = 'where';

}

return $this;

}

// --------------------------------------------------------------------

/**

* LIKE

*

* Generates a %LIKE% portion of the query.

* Separates multiple calls with 'AND'.

*

* @param mixed $field

* @param string $match

* @param string $side

* @param bool $escape

* @return CI_DB_query_builder

*/

public function like($field, $match = '', $side = 'both', $escape = NULL)

{

return $this->_like($field, $match, 'AND ', $side, '', $escape);

}

// --------------------------------------------------------------------

/**

* NOT LIKE

*

* Generates a NOT LIKE portion of the query.

* Separates multiple calls with 'AND'.

*

* @param mixed $field

* @param string $match

* @param string $side

* @param bool $escape

* @return CI_DB_query_builder

*/

public function not_like($field, $match = '', $side = 'both', $escape = NULL)

{

return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape);

}

// --------------------------------------------------------------------

/**

* OR LIKE

*

* Generates a %LIKE% portion of the query.

* Separates multiple calls with 'OR'.

*

* @param mixed $field

* @param string $match

* @param string $side

* @param bool $escape

* @return CI_DB_query_builder

*/

public function or_like($field, $match = '', $side = 'both', $escape = NULL)

{

return $this->_like($field, $match, 'OR ', $side, '', $escape);

}

// --------------------------------------------------------------------

/**

* OR NOT LIKE

*

* Generates a NOT LIKE portion of the query.

* Separates multiple calls with 'OR'.

*

* @param mixed $field

* @param string $match

* @param string $side

* @param bool $escape

* @return CI_DB_query_builder

*/

public function or_not_like($field, $match = '', $side = 'both', $escape = NULL)

{

return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape);

}

// --------------------------------------------------------------------

/**

* Internal LIKE

*

* @used-by like()

* @used-by or_like()

* @used-by not_like()

* @used-by or_not_like()

*

* @param mixed $field

* @param string $match

* @param string $type

* @param string $side

* @param string $not

* @param bool $escape

* @return CI_DB_query_builder

*/

protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '', $escape = NULL)

{

if ( ! is_array($field))

{

$field = array($field => $match);

}

is_bool($escape) OR $escape = $this->_protect_identifiers;

foreach ($field as $k => $v)

{

$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0)

? $this->_group_get_type('') : $this->_group_get_type($type);

$v = $this->escape_like_str($v);

if ($side === 'none')

{

$like_statement = "{$prefix} {$k} {$not} LIKE '{$v}'";

}

elseif ($side === 'before')

{

$like_statement = "{$prefix} {$k} {$not} LIKE '%{$v}'";

}

elseif ($side === 'after')

{

$like_statement = "{$prefix} {$k} {$not} LIKE '{$v}%'";

}

else

{

$like_statement = "{$prefix} {$k} {$not} LIKE '%{$v}%'";

}

// some platforms require an escape sequence definition for LIKE wildcards

if ($this->_like_escape_str !== '')

{

$like_statement .= sprintf($this->_like_escape_str, $this->_like_escape_chr);

}

$this->qb_where[] = array('condition' => $like_statement, 'escape' => $escape);

if ($this->qb_caching === TRUE)

{

$this->qb_cache_where[] = array('condition' => $like_statement, 'escape' => $escape);

$this->qb_cache_exists[] = 'where';

}

}

return $this;

}

// --------------------------------------------------------------------

/**

* Starts a query group.

*

* @param string $not (Internal use only)

* @param string $type (Internal use only)

* @return CI_DB_query_builder

*/

public function group_start($not = '', $type = 'AND ')

{

$type = $this->_group_get_type($type);

$this->qb_where_group_started = TRUE;

$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type;

$where = array(

'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (',

'escape' => FALSE

);

$this->qb_where[] = $where;

if ($this->qb_caching)

{

$this->qb_cache_where[] = $where;

}

return $this;

}

// --------------------------------------------------------------------

/**

* Starts a query group, but ORs the group

*

* @return CI_DB_query_builder

*/

public function or_group_start()

{