Class Database_Query_Builder_Insert

Database_Query_Builder_Insert

extends Kohana_Database_Query_Builder_Insert
extends Database_Query_Builder
extends Kohana_Database_Query_Builder
extends Database_Query
extends Kohana_Database_Query

Database query builder for INSERT statements. See Query Builder for usage and examples.

package
Kohana/Database
category
Query
author
Kohana Team
copyright
(c) Kohana Team
license
https://koseven.ga/LICENSE.md


Information

This class is a transparent base class for Kohana_Database_Query_Builder_Insert

Constants

  • None

Properties

Methods

Properties

protected $_as_object

Default value: 
bool FALSE

protected $_columns

Default value: 
array(0)

protected $_force_execute

Default value: 
bool FALSE

protected $_lifetime

Default value: 
NULL

protected $_object_params

Default value: 
array(0)

protected $_parameters

Default value: 
array(0)

protected $_sql

Default value: 
NULL

protected $_table

Default value: 
NULL

protected $_type

Default value: 
NULL

protected $_values

Default value: 
array(0)

Methods

public __construct([ mixed $table = NULL , array $columns = NULL ] ) (defined in Kohana_Database_Query_Builder_Insert)

Set the table and columns for an insert.

Parameters

  • mixed $table = NULL - Table name or array($table, $alias) or object
  • array $columns = NULL - Column names

Return Values

  • void

Source Code

public function __construct($table = NULL, array $columns = NULL)
{
	if ($table)
	{
		// Set the inital table name
		$this->table($table);
	}

	if ($columns)
	{
		// Set the column names
		$this->_columns = $columns;
	}

	// Start the query with no SQL
	return parent::__construct(Database::INSERT, '');
}

public columns(array $columns ) (defined in Kohana_Database_Query_Builder_Insert)

Set the columns that will be inserted.

Parameters

  • array $columns required - Column names

Return Values

  • $this

Source Code

public function columns(array $columns)
{
	$this->_columns = $columns;

	return $this;
}

public compile([ mixed $db = NULL ] ) (defined in Kohana_Database_Query_Builder_Insert)

Compile the SQL query and return it.

Parameters

  • mixed $db = NULL - Database instance or name of instance

Return Values

  • string

Source Code

public function compile($db = NULL)
{
	if ( ! is_object($db))
	{
		// Get the database instance
		$db = Database::instance($db);
	}

	// Start an insertion query
	$query = 'INSERT INTO '.$db->quote_table($this->_table);

	// Add the column names
	$query .= ' ('.implode(', ', array_map([$db, 'quote_column'], $this->_columns)).') ';

	if (is_array($this->_values))
	{
		// Callback for quoting values

		$groups = [];
		foreach ($this->_values as $group)
		{
			foreach ($group as $offset => $value)
			{
				if ((is_string($value) AND array_key_exists($value, $this->_parameters)) === FALSE)
				{
					// Quote the value, it is not a parameter
					$group[$offset] = $db->quote($value);
				}
			}

			$groups[] = '('.implode(', ', $group).')';
		}

		// Add the values
		$query .= 'VALUES '.implode(', ', $groups);
	}
	else
	{
		// Add the sub-query
		$query .= (string) $this->_values;
	}

	$this->_sql = $query;

	return parent::compile($db);
}

public reset() (defined in Kohana_Database_Query_Builder_Insert)

Reset the current builder status.

Return Values

  • $this

Source Code

public function reset()
{
	$this->_table = NULL;

	$this->_columns =
	$this->_values  = [];

	$this->_parameters = [];

	$this->_sql = NULL;

	return $this;
}

public select(object $query ) (defined in Kohana_Database_Query_Builder_Insert)

Use a sub-query to for the inserted values.

Parameters

  • object $query required - Database_Query of SELECT type

Return Values

  • $this

Source Code

public function select(Database_Query $query)
{
	if ($query->type() !== Database::SELECT)
	{
		throw new Kohana_Exception('Only SELECT queries can be combined with INSERT queries');
	}

	$this->_values = $query;

	return $this;
}

public table(string $table ) (defined in Kohana_Database_Query_Builder_Insert)

Sets the table to insert into.

Parameters

  • string $table required - Table name

Return Values

  • $this

Source Code

public function table($table)
{
	if ( ! is_string($table))
		throw new Kohana_Exception('INSERT INTO syntax does not allow table aliasing');

	$this->_table = $table;

	return $this;
}

public values(array $values ) (defined in Kohana_Database_Query_Builder_Insert)

Adds or overwrites values. Multiple value sets can be added.

Parameters

  • array $values required - Values list

Return Values

  • $this

Source Code

public function values(array $values)
{
	if ( ! is_array($this->_values))
	{
		throw new Kohana_Exception('INSERT INTO ... SELECT statements cannot be combined with INSERT INTO ... VALUES');
	}

	// Get all of the passed values
	$values = func_get_args();
	
	foreach ($values as $value)
	{
		$this->_values[] = $value;
	}

	return $this;
}

public __toString() (defined in Kohana_Database_Query)

Return the SQL query string.

Return Values

  • string

Source Code

public function __toString()
{
	try
	{
		// Return the SQL string
		return $this->compile(Database::instance());
	}
	catch (Exception $e)
	{
		return Kohana_Exception::text($e);
	}
}

public as_assoc() (defined in Kohana_Database_Query)

Returns results as associative arrays

Return Values

  • $this

Source Code

public function as_assoc()
{
	$this->_as_object = FALSE;

	$this->_object_params = [];

	return $this;
}

public as_object([ string $class = bool TRUE , array $params = NULL ] ) (defined in Kohana_Database_Query)

Returns results as objects

Parameters

  • string $class = bool TRUE - Classname or TRUE for stdClass
  • array $params = NULL - $params

Return Values

  • $this

Source Code

public function as_object($class = TRUE, array $params = NULL)
{
	$this->_as_object = $class;

	if ($params)
	{
		// Add object parameters
		$this->_object_params = $params;
	}

	return $this;
}

public bind(string $param , mixed & $var ) (defined in Kohana_Database_Query)

Bind a variable to a parameter in the query.

Parameters

  • string $param required - Parameter key to replace
  • byref mixed $var required - Variable to use

Return Values

  • $this

Source Code

public function bind($param, & $var)
{
	// Bind a value to a variable
	$this->_parameters[$param] =& $var;

	return $this;
}

public cached([ integer $lifetime = NULL , boolean $force = bool FALSE ] ) (defined in Kohana_Database_Query)

Enables the query to be cached for a specified amount of time.

Parameters

  • integer $lifetime = NULL - Number of seconds to cache, 0 deletes it from the cache
  • boolean $force = bool FALSE - Whether or not to execute the query during a cache hit

Tags

Return Values

  • $this

Source Code

public function cached($lifetime = NULL, $force = FALSE)
{
	if ($lifetime === NULL)
	{
		// Use the global setting
		$lifetime = Kohana::$cache_life;
	}

	$this->_force_execute = $force;
	$this->_lifetime = $lifetime;

	return $this;
}

public execute([ mixed $db = NULL , string $as_object = NULL , array $object_params = NULL ] ) (defined in Kohana_Database_Query)

Execute the current query on the given database.

Parameters

  • mixed $db = NULL - Database instance or name of instance
  • string $as_object = NULL - Result object classname, TRUE for stdClass or FALSE for array
  • array $object_params = NULL - Result object constructor arguments

Return Values

  • object - Database_Result for SELECT queries
  • mixed - The insert id for INSERT queries
  • integer - Number of affected rows for all other queries

Source Code

public function execute($db = NULL, $as_object = NULL, $object_params = NULL)
{
	if ( ! is_object($db))
	{
		// Get the database instance
		$db = Database::instance($db);
	}

	if ($as_object === NULL)
	{
		$as_object = $this->_as_object;
	}

	if ($object_params === NULL)
	{
		$object_params = $this->_object_params;
	}

	// Compile the SQL query
	$sql = $this->compile($db);

	if ($this->_lifetime !== NULL AND $this->_type === Database::SELECT)
	{
		// Set the cache key based on the database instance name and SQL
		$cache_key = 'Database::query("'.$db.'", "'.$sql.'")';

		// Read the cache first to delete a possible hit with lifetime <= 0
		if (($result = Kohana::cache($cache_key, NULL, $this->_lifetime)) !== NULL
			AND ! $this->_force_execute)
		{
			// Return a cached result
			return new Database_Result_Cached($result, $sql, $as_object, $object_params);
		}
	}

	// Execute the query
	$result = $db->query($this->_type, $sql, $as_object, $object_params);

	if (isset($cache_key) AND $this->_lifetime > 0)
	{
		// Cache the result array
		Kohana::cache($cache_key, $result->as_array(), $this->_lifetime);
	}

	return $result;
}

public param(string $param , mixed $value ) (defined in Kohana_Database_Query)

Set the value of a parameter in the query.

Parameters

  • string $param required - Parameter key to replace
  • mixed $value required - Value to use

Return Values

  • $this

Source Code

public function param($param, $value)
{
	// Add or overload a new parameter
	$this->_parameters[$param] = $value;

	return $this;
}

public parameters(array $params ) (defined in Kohana_Database_Query)

Add multiple parameters to the query.

Parameters

  • array $params required - List of parameters

Return Values

  • $this

Source Code

public function parameters(array $params)
{
	// Merge the new parameters in
	$this->_parameters = $params + $this->_parameters;

	return $this;
}

public type() (defined in Kohana_Database_Query)

Get the type of the query.

Return Values

  • integer

Source Code

public function type()
{
	return $this->_type;
}

protected _compile_conditions(object $db , array $conditions ) (defined in Kohana_Database_Query_Builder)

Compiles an array of conditions into an SQL partial. Used for WHERE and HAVING.

Parameters

  • object $db required - Database instance
  • array $conditions required - Condition statements

Return Values

  • string

Source Code

protected function _compile_conditions(Database $db, array $conditions)
{
	$last_condition = NULL;

	$sql = '';
	foreach ($conditions as $group)
	{
		// Process groups of conditions
		foreach ($group as $logic => $condition)
		{
			if ($condition === '(')
			{
				if ( ! empty($sql) AND $last_condition !== '(')
				{
					// Include logic operator
					$sql .= ' '.$logic.' ';
				}

				$sql .= '(';
			}
			elseif ($condition === ')')
			{
				$sql .= ')';
			}
			else
			{
				if ( ! empty($sql) AND $last_condition !== '(')
				{
					// Add the logic operator
					$sql .= ' '.$logic.' ';
				}

				// Split the condition
				list($column, $op, $value) = $condition;

				if ($value === NULL)
				{
					if ($op === '=')
					{
						// Convert "val = NULL" to "val IS NULL"
						$op = 'IS';
					}
					elseif ($op === '!=' OR $op === '<>')
					{
						// Convert "val != NULL" to "valu IS NOT NULL"
						$op = 'IS NOT';
					}
				}

				// Database operators are always uppercase
				$op = strtoupper($op);

				if ($op === 'BETWEEN' AND is_array($value))
				{
					// BETWEEN always has exactly two arguments
					list($min, $max) = $value;

					if ((is_string($min) AND array_key_exists($min, $this->_parameters)) === FALSE)
					{
						// Quote the value, it is not a parameter
						$min = $db->quote($min);
					}

					if ((is_string($max) AND array_key_exists($max, $this->_parameters)) === FALSE)
					{
						// Quote the value, it is not a parameter
						$max = $db->quote($max);
					}

					// Quote the min and max value
					$value = $min.' AND '.$max;
				}
				elseif ($op === 'IN' AND is_array($value) AND count($value) === 0)
				{
					$value = '(NULL)';
				}
				elseif ((is_string($value) AND array_key_exists($value, $this->_parameters)) === FALSE)
				{
					// Quote the value, it is not a parameter
					$value = $db->quote($value);
				}

				if ($column)
				{
					if (is_array($column))
					{
						// Use the column name
						$column = $db->quote_identifier(reset($column));
					}
					else
					{
						// Apply proper quoting to the column
						$column = $db->quote_column($column);
					}
				}

				// Append the statement to the query
				$sql .= trim($column.' '.$op.' '.$value);
			}

			$last_condition = $condition;
		}
	}

	return $sql;
}

protected _compile_group_by(object $db , array $columns ) (defined in Kohana_Database_Query_Builder)

Compiles an array of GROUP BY columns into an SQL partial.

Parameters

  • object $db required - Database instance
  • array $columns required - $columns

Return Values

  • string

Source Code

protected function _compile_group_by(Database $db, array $columns)
{
	$group = [];

	foreach ($columns as $column)
	{
		if (is_array($column))
		{
			// Use the column alias
			$column = $db->quote_identifier(end($column));
		}
		else
		{
			// Apply proper quoting to the column
			$column = $db->quote_column($column);
		}

		$group[] = $column;
	}

	return 'GROUP BY '.implode(', ', $group);
}

protected _compile_join(object $db , array $joins ) (defined in Kohana_Database_Query_Builder)

Compiles an array of JOIN statements into an SQL partial.

Parameters

  • object $db required - Database instance
  • array $joins required - Join statements

Return Values

  • string

Source Code

protected function _compile_join(Database $db, array $joins)
{
	$statements = [];

	foreach ($joins as $join)
	{
		// Compile each of the join statements
		$statements[] = $join->compile($db);
	}

	return implode(' ', $statements);
}

protected _compile_order_by(Database $db , array $columns ) (defined in Kohana_Database_Query_Builder)

Compiles an array of ORDER BY statements into an SQL partial.

Parameters

  • Database $db required - Database instance
  • array $columns required - Sorting columns

Tags

Return Values

  • string

Source Code

protected function _compile_order_by(Database $db, array $columns)
{
	$sort = [];
	foreach ($columns as $group)
	{
		list ($column, $direction) = $group;

		if (is_array($column))
		{
			// Use the column alias
			$column = $db->quote_identifier(end($column));
		}
		else
		{
			// Apply proper quoting to the column
			$column = $db->quote_column($column);
		}

		if ($direction)
		{
			// Make the direction uppercase
			$direction = ' '.strtoupper($direction);

			// Make sure direction is either ASC or DESC to prevent injections
			if ( ! in_array($direction, [' ASC', ' DESC'])) {
				throw new Database_Exception('Invalid sorting direction: ' . $direction);
			}
		}

		$sort[] = $column.$direction;
	}

	return 'ORDER BY '.implode(', ', $sort);
}

protected _compile_set(object $db , array $values ) (defined in Kohana_Database_Query_Builder)

Compiles an array of set values into an SQL partial. Used for UPDATE.

Parameters

  • object $db required - Database instance
  • array $values required - Updated values

Return Values

  • string

Source Code

protected function _compile_set(Database $db, array $values)
{
	$set = [];
	foreach ($values as $group)
	{
		// Split the set
		list ($column, $value) = $group;

		// Quote the column name
		$column = $db->quote_column($column);

		if ((is_string($value) AND array_key_exists($value, $this->_parameters)) === FALSE)
		{
			// Quote the value, it is not a parameter
			$value = $db->quote($value);
		}

		$set[$column] = $column.' = '.$value;
	}

	return implode(', ', $set);
}

Modules

Do you want to contribute to Koseven?

Koseven Logo

We need YOUR help!

This project is open source. What does this mean? YOU can help:
  • Found a bug? Report it on Github
  • Need a feature? Add it Here
  • Want to help? Join the Forum
Go to Github