/** * 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(); }