/**
* Compile the SQL partial for a JOIN statement and return it.
*
* @param mixed $db Alt_Database instance or name of instance
* @return string
*/
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get the Alt_Database instance
$db = Alt_Database::instance($db);
}
if ($this->_type) {
$sql = strtoupper($this->_type) . ' JOIN';
} else {
$sql = 'JOIN';
}
// Quote the table name that is being joined
$sql .= ' ' . $db->quote_table($this->_table);
if (!empty($this->_using)) {
// Quote and concat the columns
$sql .= ' USING (' . implode(', ', array_map(array($db, 'quote_column'), $this->_using)) . ')';
} else {
$conditions = array();
foreach ($this->_on as $condition) {
// Split the condition
list($c1, $op, $c2) = $condition;
if ($op) {
// Make the operator uppercase and spaced
$op = ' ' . strtoupper($op);
}
// Quote each of the columns used for the condition
$conditions[] = $db->quote_column($c1) . $op . ' ' . $db->quote_column($c2);
}
// Concat the conditions "... AND ..."
$sql .= ' ON (' . implode(' AND ', $conditions) . ')';
}
return $sql;
}
/**
* Compile the SQL query and return it.
*
* @param mixed $db Alt_Database instance or name of instance
* @return string
*/
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get the Alt_Database instance
$db = Alt_Database::instance($db);
}
// Start an update query
$query = 'UPDATE ' . $db->quote_table($this->_table);
// Add the columns to update
$query .= ' SET ' . $this->_compile_set($db, $this->_set);
if (!empty($this->_where)) {
// Add selection conditions
$query .= ' WHERE ' . $this->_compile_conditions($db, $this->_where);
}
if (!empty($this->_order_by)) {
// Add sorting
$query .= ' ' . $this->_compile_order_by($db, $this->_order_by);
}
if ($this->_limit !== NULL) {
// Add limiting
$query .= ' LIMIT ' . $this->_limit;
}
$this->_sql = $query;
return parent::compile($db);
}
/**
* Compile the SQL query and return it.
*
* @param mixed $db Alt_Database instance or name of instance
* @return string
*/
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get the Alt_Database instance
$db = Alt_Database::instance($db);
}
// Start a deletion query
$query = 'DELETE FROM ' . $db->quote_table($this->_table);
if (!empty($this->_where)) {
// Add deletion conditions
$query .= ' WHERE ' . $this->_compile_conditions($db, $this->_where);
}
if (!empty($this->_order_by)) {
// Add sorting
$query .= ' ' . $this->_compile_order_by($db, $this->_order_by);
}
if ($this->_limit !== NULL) {
// Add limiting
$query .= ' LIMIT ' . $this->_limit;
}
$this->_sql = $query;
return parent::compile($db);
}
public function __construct($name = NULL, $config = NULL)
{
$this->db = Alt_Database::instance($name, $config);
}
/**
* Compiles an array of ORDER BY statements into an SQL partial.
*
* @param object $db Alt_Database instance
* @param array $columns sorting columns
* @return string
*/
protected function _compile_order_by(Alt_Database $db, array $columns)
{
$sort = array();
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);
}
$sort[] = $column . $direction;
}
return 'ORDER BY ' . implode(', ', $sort);
}
/**
* Execute the current query on the given Alt_Database.
*
* @param mixed $db Alt_Database instance or name of instance
* @param string result object classname, TRUE for stdClass or FALSE for array
* @param array result object constructor arguments
* @return object Alt_Database_Result for SELECT queries
* @return mixed the insert id for INSERT queries
* @return integer number of affected rows for all other queries
*/
public function execute($db = NULL, $as_object = NULL, $object_params = NULL)
{
if (!is_object($db)) {
// Get the Alt_Database instance
$db = Alt_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 === Alt_Database::SELECT) {
// Set the cache key based on the Alt_Database instance name and SQL
$cache_key = 'Alt_Database::query("' . $db . '", "' . $sql . '")';
// Read the cache first to delete a possible hit with lifetime <= 0
if (($result = Alt::cache($cache_key, NULL, $this->_lifetime)) !== NULL and !$this->_force_execute) {
// Return a cached result
return new Alt_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
Alt::cache($cache_key, $result->as_array(), $this->_lifetime);
}
return $result;
}
/**
* Compile the SQL query and return it.
*
* @param mixed $db Alt_Database instance or name of instance
* @return string
*/
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get the Alt_Database instance
$db = Alt_Database::instance($db);
}
// Callback to quote columns
$quote_column = array($db, 'quote_column');
// Callback to quote tables
$quote_table = array($db, 'quote_table');
// Start a selection query
$query = 'SELECT ';
if ($this->_distinct === TRUE) {
// Select only unique results
$query .= 'DISTINCT ';
}
if (empty($this->_select)) {
// Select all columns
$query .= '*';
} else {
// Select all columns
$query .= implode(', ', array_unique(array_map($quote_column, $this->_select)));
}
if (!empty($this->_from)) {
// Set tables to select from
$query .= ' FROM ' . implode(', ', array_unique(array_map($quote_table, $this->_from)));
}
if (!empty($this->_join)) {
// Add tables to join
$query .= ' ' . $this->_compile_join($db, $this->_join);
}
if (!empty($this->_where)) {
// Add selection conditions
$query .= ' WHERE ' . $this->_compile_conditions($db, $this->_where);
}
if (!empty($this->_group_by)) {
// Add grouping
$query .= ' ' . $this->_compile_group_by($db, $this->_group_by);
}
if (!empty($this->_having)) {
// Add filtering conditions
$query .= ' HAVING ' . $this->_compile_conditions($db, $this->_having);
}
if (!empty($this->_order_by)) {
// Add sorting
$query .= ' ' . $this->_compile_order_by($db, $this->_order_by);
}
if ($this->_limit !== NULL) {
// Add limiting
$query .= ' LIMIT ' . $this->_limit;
}
if ($this->_offset !== NULL) {
// Add offsets
$query .= ' OFFSET ' . $this->_offset;
}
if (!empty($this->_union)) {
foreach ($this->_union as $u) {
$query .= ' UNION ';
if ($u['all'] === TRUE) {
$query .= 'ALL ';
}
$query .= $u['select']->compile($db);
}
}
$this->_sql = $query;
return parent::compile($db);
}
/**
* Compile the SQL query and return it.
*
* @param mixed $db Alt_Database instance or name of instance
* @return string
*/
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get the Alt_Database instance
$db = Alt_Database::instance($db);
}
// Start an insertion query
$query = 'INSERT INTO ' . $db->quote_table($this->_table);
// Add the column names
$query .= ' (' . implode(', ', array_map(array($db, 'quote_column'), $this->_columns)) . ') ';
if (is_array($this->_values)) {
// Callback for quoting values
$quote = array($db, 'quote');
$groups = array();
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 function datatype($type)
{
static $types = array('blob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '65535'), 'bool' => array('type' => 'bool'), 'bigint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '18446744073709551615'), 'datetime' => array('type' => 'string'), 'decimal unsigned' => array('type' => 'float', 'exact' => TRUE, 'min' => '0'), 'double' => array('type' => 'float'), 'double precision unsigned' => array('type' => 'float', 'min' => '0'), 'double unsigned' => array('type' => 'float', 'min' => '0'), 'enum' => array('type' => 'string'), 'fixed' => array('type' => 'float', 'exact' => TRUE), 'fixed unsigned' => array('type' => 'float', 'exact' => TRUE, 'min' => '0'), 'float unsigned' => array('type' => 'float', 'min' => '0'), 'geometry' => array('type' => 'string', 'binary' => TRUE), 'int unsigned' => array('type' => 'int', 'min' => '0', 'max' => '4294967295'), 'integer unsigned' => array('type' => 'int', 'min' => '0', 'max' => '4294967295'), 'longblob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '4294967295'), 'longtext' => array('type' => 'string', 'character_maximum_length' => '4294967295'), 'mediumblob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '16777215'), 'mediumint' => array('type' => 'int', 'min' => '-8388608', 'max' => '8388607'), 'mediumint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '16777215'), 'mediumtext' => array('type' => 'string', 'character_maximum_length' => '16777215'), 'national varchar' => array('type' => 'string'), 'numeric unsigned' => array('type' => 'float', 'exact' => TRUE, 'min' => '0'), 'nvarchar' => array('type' => 'string'), 'point' => array('type' => 'string', 'binary' => TRUE), 'real unsigned' => array('type' => 'float', 'min' => '0'), 'set' => array('type' => 'string'), 'smallint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '65535'), 'text' => array('type' => 'string', 'character_maximum_length' => '65535'), 'tinyblob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '255'), 'tinyint' => array('type' => 'int', 'min' => '-128', 'max' => '127'), 'tinyint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '255'), 'tinytext' => array('type' => 'string', 'character_maximum_length' => '255'), 'year' => array('type' => 'string'));
$type = str_replace(' zerofill', '', $type);
if (isset($types[$type])) {
return $types[$type];
}
return parent::datatype($type);
}
public function disconnect()
{
// Destroy the PDO object
$this->_connection = NULL;
return parent::disconnect();
}
