BaseBuilder.php

<?php

/**
 * This file is part of the CodeIgniter 4 framework.
 *
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CodeIgniter\Database;

use Closure;
use CodeIgniter\Database\Exceptions\DatabaseException;
use CodeIgniter\Database\Exceptions\DataException;
use InvalidArgumentException;

/**
 * Class BaseBuilder
 *
 * Provides the core Query Builder methods.
 * Database-specific Builders might need to override
 * certain methods to make them work.
 *
 * @mixin \CodeIgniter\Model
 */
class BaseBuilder
{
	/**
	 * Reset DELETE data flag
	 *
	 * @var boolean
	 */
	protected $resetDeleteData = false;

	/**
	 * QB SELECT data
	 *
	 * @var array
	 */
	protected $QBSelect = [];

	/**
	 * QB DISTINCT flag
	 *
	 * @var boolean
	 */
	protected $QBDistinct = false;

	/**
	 * QB FROM data
	 *
	 * @var array
	 */
	protected $QBFrom = [];

	/**
	 * QB JOIN data
	 *
	 * @var array
	 */
	protected $QBJoin = [];

	/**
	 * QB WHERE data
	 *
	 * @var array
	 */
	protected $QBWhere = [];

	/**
	 * QB GROUP BY data
	 *
	 * @var array
	 */
	public $QBGroupBy = [];

	/**
	 * QB HAVING data
	 *
	 * @var array
	 */
	protected $QBHaving = [];

	/**
	 * QB keys
	 *
	 * @var array
	 */
	protected $QBKeys = [];

	/**
	 * QB LIMIT data
	 *
	 * @var integer|boolean
	 */
	protected $QBLimit = false;

	/**
	 * QB OFFSET data
	 *
	 * @var integer|boolean
	 */
	protected $QBOffset = false;

	/**
	 * QB ORDER BY data
	 *
	 * @var array|null|string
	 */
	public $QBOrderBy = [];

	/**
	 * QB NO ESCAPE data
	 *
	 * @var array
	 */
	public $QBNoEscape = [];

	/**
	 * QB data sets
	 *
	 * @var array
	 */
	protected $QBSet = [];

	/**
	 * QB WHERE group started flag
	 *
	 * @var boolean
	 */
	protected $QBWhereGroupStarted = false;

	/**
	 * QB WHERE group count
	 *
	 * @var integer
	 */
	protected $QBWhereGroupCount = 0;

	/**
	 * Ignore data that cause certain
	 * exceptions, for example in case of
	 * duplicate keys.
	 *
	 * @var boolean
	 */
	protected $QBIgnore = false;

	/**
	 * A reference to the database connection.
	 *
	 * @var ConnectionInterface
	 */
	protected $db;

	/**
	 * Name of the primary table for this instance.
	 * Tracked separately because $QBFrom gets escaped
	 * and prefixed.
	 *
	 * @var string
	 */
	protected $tableName;

	/**
	 * ORDER BY random keyword
	 *
	 * @var array
	 */
	protected $randomKeyword = [
		'RAND()',
		'RAND(%d)',
	];

	/**
	 * COUNT string
	 *
	 * @used-by CI_DB_driver::count_all()
	 * @used-by BaseBuilder::count_all_results()
	 *
	 * @var string
	 */
	protected $countString = 'SELECT COUNT(*) AS ';

	/**
	 * Collects the named parameters and
	 * their values for later binding
	 * in the Query object.
	 *
	 * @var array
	 */
	protected $binds = [];

	/**
	 * Collects the key count for named parameters
	 * in the Query object.
	 *
	 * @var array
	 */
	protected $bindsKeyCount = [];

	/**
	 * Some databases, like SQLite, do not by default
	 * allow limiting of delete clauses.
	 *
	 * @var boolean
	 */
	protected $canLimitDeletes = true;

	/**
	 * Some databases do not by default
	 * allow limit update queries with WHERE.
	 *
	 * @var boolean
	 */
	protected $canLimitWhereUpdates = true;

	/**
	 * Specifies which sql statements
	 * support the ignore option.
	 *
	 * @var array
	 */
	protected $supportedIgnoreStatements = [];

	/**
	 * Builder testing mode status.
	 *
	 * @var boolean
	 */
	protected $testMode = false;

	/**
	 * Tables relation types
	 *
	 * @var array
	 */
	protected $joinTypes = [
		'LEFT',
		'RIGHT',
		'OUTER',
		'INNER',
		'LEFT OUTER',
		'RIGHT OUTER',
	];

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

	/**
	 * Constructor
	 *
	 * @param  string|array        $tableName
	 * @param  ConnectionInterface $db
	 * @param  array               $options
	 * @throws DatabaseException
	 */
	public function __construct($tableName, ConnectionInterface &$db, array $options = null)
	{
		if (empty($tableName))
		{
			throw new DatabaseException('A table must be specified when creating a new Query Builder.');
		}

		$this->db = $db;

		$this->tableName = $tableName;
		$this->from($tableName);

		if (! empty($options))
		{
			foreach ($options as $key => $value)
			{
				if (property_exists($this, $key))
				{
					$this->$key = $value;
				}
			}
		}
	}

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

	/**
	 * Returns the current database connection
	 *
	 * @return ConnectionInterface
	 */
	public function db(): ConnectionInterface
	{
		return $this->db;
	}

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

	/**
	 * Sets a test mode status.
	 *
	 * @param boolean $mode Mode to set
	 *
	 * @return $this
	 */
	public function testMode(bool $mode = true)
	{
		$this->testMode = $mode;

		return $this;
	}

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

	/**
	 * Gets the name of the primary table.
	 *
	 * @return string
	 */
	public function getTable(): string
	{
		return $this->tableName;
	}

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

	/**
	 * Returns an array of bind values and their
	 * named parameters for binding in the Query object later.
	 *
	 * @return array
	 */
	public function getBinds(): array
	{
		return $this->binds;
	}

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

	/**
	 * Ignore
	 *
	 * Set ignore Flag for next insert,
	 * update or delete query.
	 *
	 * @param boolean $ignore
	 *
	 * @return $this
	 */
	public function ignore(bool $ignore = true)
	{
		$this->QBIgnore = $ignore;

		return $this;
	}

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

	/**
	 * Select
	 *
	 * Generates the SELECT portion of the query
	 *
	 * @param string|array $select
	 * @param boolean      $escape
	 *
	 * @return $this
	 */
	public function select($select = '*', bool $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) || $escape = $this->db->protectIdentifiers;

		foreach ($select as $val)
		{
			$val = trim($val);

			if ($val !== '')
			{
				$this->QBSelect[] = $val;

				/*
				 * When doing 'SELECT NULL as field_alias FROM table'
				 * null gets taken as a field, and therefore escaped
				 * with backticks.
				 * This prevents NULL being escaped
				 * @see https://github.com/codeigniter4/CodeIgniter4/issues/1169
				 */
				if (mb_stripos(trim($val), 'NULL') === 0)
				{
					$escape = false;
				}

				$this->QBNoEscape[] = $escape;
			}
		}

		return $this;
	}

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

	/**
	 * Select Max
	 *
	 * Generates a SELECT MAX(field) portion of a query
	 *
	 * @param string $select The field
	 * @param string $alias  An alias
	 *
	 * @return $this
	 */
	public function selectMax(string $select = '', string $alias = '')
	{
		return $this->maxMinAvgSum($select, $alias);
	}

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

	/**
	 * Select Min
	 *
	 * Generates a SELECT MIN(field) portion of a query
	 *
	 * @param string $select The field
	 * @param string $alias  An alias
	 *
	 * @return $this
	 */
	public function selectMin(string $select = '', string $alias = '')
	{
		return $this->maxMinAvgSum($select, $alias, 'MIN');
	}

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

	/**
	 * Select Average
	 *
	 * Generates a SELECT AVG(field) portion of a query
	 *
	 * @param string $select The field
	 * @param string $alias  An alias
	 *
	 * @return $this
	 */
	public function selectAvg(string $select = '', string $alias = '')
	{
		return $this->maxMinAvgSum($select, $alias, 'AVG');
	}

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

	/**
	 * Select Sum
	 *
	 * Generates a SELECT SUM(field) portion of a query
	 *
	 * @param string $select The field
	 * @param string $alias  An alias
	 *
	 * @return $this
	 */
	public function selectSum(string $select = '', string $alias = '')
	{
		return $this->maxMinAvgSum($select, $alias, 'SUM');
	}

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

	/**
	 * Select Count
	 *
	 * Generates a SELECT COUNT(field) portion of a query
	 *
	 * @param string $select The field
	 * @param string $alias  An alias
	 *
	 * @return $this
	 */
	public function selectCount(string $select = '', string $alias = '')
	{
		return $this->maxMinAvgSum($select, $alias, 'COUNT');
	}

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

	/**
	 * SELECT [MAX|MIN|AVG|SUM|COUNT]()
	 *
	 * @used-by selectMax()
	 * @used-by selectMin()
	 * @used-by selectAvg()
	 * @used-by selectSum()
	 *
	 * @param string $select Field name
	 * @param string $alias
	 * @param string $type
	 *
	 * @return $this
	 * @throws DataException
	 * @throws DatabaseException
	 */
	protected function maxMinAvgSum(string $select = '', string $alias = '', string $type = 'MAX')
	{
		if ($select === '')
		{
			throw DataException::forEmptyInputGiven('Select');
		}

		if (strpos($select, ',') !== false)
		{
			throw DataException::forInvalidArgument('column name not separated by comma');
		}

		$type = strtoupper($type);

		if (! in_array($type, ['MAX', 'MIN', 'AVG', 'SUM', 'COUNT'], true))
		{
			throw new DatabaseException('Invalid function type: ' . $type);
		}

		if ($alias === '')
		{
			$alias = $this->createAliasFromTable(trim($select));
		}

		$sql = $type . '(' . $this->db->protectIdentifiers(trim($select)) . ') AS ' . $this->db->escapeIdentifiers(trim($alias));

		$this->QBSelect[]   = $sql;
		$this->QBNoEscape[] = null;

		return $this;
	}

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

	/**
	 * Determines the alias name based on the table
	 *
	 * @param string $item
	 *
	 * @return string
	 */
	protected function createAliasFromTable(string $item): string
	{
		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 boolean $val
	 *
	 * @return $this
	 */
	public function distinct(bool $val = true)
	{
		$this->QBDistinct = $val;

		return $this;
	}

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

	/**
	 * From
	 *
	 * Generates the FROM portion of the query
	 *
	 * @param mixed   $from      can be a string or array
	 * @param boolean $overwrite Should we remove the first table existing?
	 *
	 * @return $this
	 */
	public function from($from, bool $overwrite = false)
	{
		if ($overwrite === true)
		{
			$this->QBFrom = [];
			$this->db->setAliasedTables([]);
		}

		foreach ((array) $from as $val)
		{
			if (strpos($val, ',') !== false)
			{
				foreach (explode(',', $val) as $v)
				{
					$v = trim($v);
					$this->trackAliases($v);

					$this->QBFrom[] = $v = $this->db->protectIdentifiers($v, true, null, false);
				}
			}
			else
			{
				$val = trim($val);

				// Extract any aliases that might exist. We use this information
				// in the protectIdentifiers to know whether to add a table prefix
				$this->trackAliases($val);

				$this->QBFrom[] = $this->db->protectIdentifiers($val, true, null, false);
			}
		}

		return $this;
	}

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

	/**
	 * JOIN
	 *
	 * Generates the JOIN portion of the query
	 *
	 * @param string  $table
	 * @param string  $cond   The join condition
	 * @param string  $type   The type of join
	 * @param boolean $escape Whether not to try to escape identifiers
	 *
	 * @return $this
	 */
	public function join(string $table, string $cond, string $type = '', bool $escape = null)
	{
		if ($type !== '')
		{
			$type = strtoupper(trim($type));

			if (! in_array($type, $this->joinTypes, true))
			{
				$type = '';
			}
			else
			{
				$type .= ' ';
			}
		}

		// Extract any aliases that might exist. We use this information
		// in the protectIdentifiers to know whether to add a table prefix
		$this->trackAliases($table);

		is_bool($escape) || $escape = $this->db->protectIdentifiers;

		if (! $this->hasOperator($cond))
		{
			$cond = ' USING (' . ($escape ? $this->db->escapeIdentifiers($cond) : $cond) . ')';
		}
		elseif ($escape === false)
		{
			$cond = ' ON ' . $cond;
		}
		else
		{
			// Split multiple conditions
			if (preg_match_all('/\sAND\s|\sOR\s/i', $cond, $joints, PREG_OFFSET_CAPTURE))
			{
				$conditions = [];
				$joints     = $joints[0];
				array_unshift($joints, ['', 0]);

				for ($i = count($joints) - 1, $pos = strlen($cond); $i >= 0; $i --)
				{
					$joints[$i][1] += strlen($joints[$i][0]); // offset
					$conditions[$i] = substr($cond, $joints[$i][1], $pos - $joints[$i][1]);
					$pos            = $joints[$i][1] - strlen($joints[$i][0]);
					$joints[$i]     = $joints[$i][0];
				}
				ksort($conditions);
			}
			else
			{
				$conditions = [$cond];
				$joints     = [''];
			}

			$cond = ' ON ';
			foreach ($conditions as $i => $condition)
			{
				$operator = $this->getOperator($condition);

				$cond .= $joints[$i];
				$cond .= preg_match("/(\(*)?([\[\]\w\.'-]+)" . preg_quote($operator) . '(.*)/i', $condition, $match) ? $match[1] . $this->db->protectIdentifiers($match[2]) . $operator . $this->db->protectIdentifiers($match[3]) : $condition;
			}
		}

		// Do we want to escape the table name?
		if ($escape === true)
		{
			$table = $this->db->protectIdentifiers($table, true, null, false);
		}

		// Assemble the JOIN statement
		$this->QBJoin[] = $join = $type . 'JOIN ' . $table . $cond;

		return $this;
	}

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

	/**
	 * WHERE
	 *
	 * Generates the WHERE portion of the query.
	 * Separates multiple calls with 'AND'.
	 *
	 * @param mixed   $key
	 * @param mixed   $value
	 * @param boolean $escape
	 *
	 * @return $this
	 */
	public function where($key, $value = null, bool $escape = null)
	{
		return $this->whereHaving('QBWhere', $key, $value, 'AND ', $escape);
	}

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

	/**
	 * OR WHERE
	 *
	 * Generates the WHERE portion of the query.
	 * Separates multiple calls with 'OR'.
	 *
	 * @param mixed   $key
	 * @param mixed   $value
	 * @param boolean $escape
	 *
	 * @return $this
	 */
	public function orWhere($key, $value = null, bool $escape = null)
	{
		return $this->whereHaving('QBWhere', $key, $value, 'OR ', $escape);
	}

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

	/**
	 * WHERE, HAVING
	 *
	 * @used-by where()
	 * @used-by orWhere()
	 * @used-by having()
	 * @used-by orHaving()
	 *
	 * @param string  $qbKey  'QBWhere' or 'QBHaving'
	 * @param mixed   $key
	 * @param mixed   $value
	 * @param string  $type
	 * @param boolean $escape
	 *
	 * @return $this
	 */
	protected function whereHaving(string $qbKey, $key, $value = null, string $type = 'AND ', bool $escape = null)
	{
		if (! is_array($key))
		{
			$key = [$key => $value];
		}

		// If the escape value was not set will base it on the global setting
		is_bool($escape) || $escape = $this->db->protectIdentifiers;

		foreach ($key as $k => $v)
		{
			$prefix = empty($this->$qbKey) ? $this->groupGetType('') : $this->groupGetType($type);

			if ($v !== null)
			{
				$op = $this->getOperator($k, true);

				if (! empty($op))
				{
					$k = trim($k);

					end($op);

					$op = trim(current($op));

					if (substr($k, -1 * strlen($op)) === $op)
					{
						$k = rtrim(strrev(preg_replace(strrev('/' . $op . '/'), strrev(''), strrev($k), 1)));
					}
				}

				$bind = $this->setBind($k, $v, $escape);

				if (empty($op))
				{
					$k .= ' =';
				}
				else
				{
					$k .= " $op";
				}

				if ($v instanceof Closure)
				{
					$builder = $this->cleanClone();
					$v       = '(' . str_replace("\n", ' ', $v($builder)->getCompiledSelect()) . ')';
				}
				else
				{
					$v = " :$bind:";
				}
			}
			elseif (! $this->hasOperator($k) && $qbKey !== 'QBHaving')
			{
				// 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->{$qbKey}[] = [
				'condition' => $prefix . $k . $v,
				'escape'    => $escape,
			];
		}

		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|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function whereIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($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|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function orWhereIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($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|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function whereNotIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($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|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function orWhereNotIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($key, $values, true, 'OR ', $escape);
	}

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

	/**
	 * HAVING IN
	 *
	 * Generates a HAVING field IN('item', 'item') SQL query,
	 * joined with 'AND' if appropriate.
	 *
	 * @param string               $key    The field to search
	 * @param array|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function havingIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($key, $values, false, 'AND ', $escape, 'QBHaving');
	}

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

	/**
	 * OR HAVING IN
	 *
	 * Generates a HAVING field IN('item', 'item') SQL query,
	 * joined with 'OR' if appropriate.
	 *
	 * @param string               $key    The field to search
	 * @param array|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function orHavingIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($key, $values, false, 'OR ', $escape, 'QBHaving');
	}

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

	/**
	 * HAVING NOT IN
	 *
	 * Generates a HAVING field NOT IN('item', 'item') SQL query,
	 * joined with 'AND' if appropriate.
	 *
	 * @param string               $key    The field to search
	 * @param array|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function havingNotIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($key, $values, true, 'AND ', $escape, 'QBHaving');
	}

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

	/**
	 * OR HAVING NOT IN
	 *
	 * Generates a HAVING field NOT IN('item', 'item') SQL query,
	 * joined with 'OR' if appropriate.
	 *
	 * @param string               $key    The field to search
	 * @param array|string|Closure $values The values searched on, or anonymous function with subquery
	 * @param boolean              $escape
	 *
	 * @return $this
	 */
	public function orHavingNotIn(string $key = null, $values = null, bool $escape = null)
	{
		return $this->_whereIn($key, $values, true, 'OR ', $escape, 'QBHaving');
	}

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

	/**
	 * Internal WHERE IN
	 *
	 * @used-by WhereIn()