Database_MySQL
› Kohana_Database_MySQL
› Database
› Kohana_Database

Class Contents

Class declared in MODPATH/database/classes/database/mysql.php on line 3.

Properties

Methods

protected function _select_db($database)
{
	if ( ! mysql_select_db($database, $this->_connection))
	{
		// Unable to select database
		throw new Database_Exception(':error',
			array(':error' => mysql_error($this->_connection)),
			mysql_errno($this->_connection));
	}

	Database_MySQL::$_current_databases[$this->_connection_id] = $database;
}

public function connect()
{
	if ($this->_connection)
		return;

	if (Database_MySQL::$_set_names === NULL)
	{
		// Determine if we can use mysql_set_charset(), which is only
		// available on PHP 5.2.3+ when compiled against MySQL 5.0+
		Database_MySQL::$_set_names = ! function_exists('mysql_set_charset');
	}

	// Extract the connection parameters, adding required variabels
	extract($this->_config['connection'] + array(
		'database'   => '',
		'hostname'   => '',
		'username'   => '',
		'password'   => '',
		'persistent' => FALSE,
	));

	// Prevent this information from showing up in traces
	unset($this->_config['connection']['username'], $this->_config['connection']['password']);

	try
	{
		if ($persistent)
		{
			// Create a persistent connection
			$this->_connection = mysql_pconnect($hostname, $username, $password);
		}
		else
		{
			// Create a connection and force it to be a new link
			$this->_connection = mysql_connect($hostname, $username, $password, TRUE);
		}
	}
	catch (ErrorException $e)
	{
		// No connection exists
		$this->_connection = NULL;

		throw new Database_Exception(':error', array(
				':error' => mysql_error(),
			),
			mysql_errno());
	}

	// \xFF is a better delimiter, but the PHP driver uses underscore
	$this->_connection_id = sha1($hostname.'_'.$username.'_'.$password);

	$this->_select_db($database);

	if ( ! empty($this->_config['charset']))
	{
		// Set the character set
		$this->set_charset($this->_config['charset']);
	}
}

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'),
		'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()
{
	try
	{
		// Database is assumed disconnected
		$status = TRUE;

		if (is_resource($this->_connection))
		{
			if ($status = mysql_close($this->_connection))
			{
				// Clear the connection
				$this->_connection = NULL;
			}
		}
	}
	catch (Exception $e)
	{
		// Database is probably not disconnected
		$status = ! is_resource($this->_connection);
	}

	return $status;
}

public function escape($value)
{
	// Make sure the database is connected
	$this->_connection or $this->connect();

	if (($value = mysql_real_escape_string( (string) $value, $this->_connection)) === FALSE)
	{
		throw new Database_Exception(':error',
			array(':error' => mysql_errno($this->_connection)),
			mysql_error($this->_connection));
	}

	// SQL standard is to use single-quotes for all values
	return "'$value'";
}

public function list_columns($table, $like = NULL, $add_prefix = TRUE)
{
	// Quote the table name
	$table = ($add_prefix === TRUE) ? $this->quote_table($table) : $table;

	if (is_string($like))
	{
		// Search for column names
		$result = $this->query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$table.' LIKE '.$this->quote($like), FALSE);
	}
	else
	{
		// Find all column names
		$result = $this->query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$table, FALSE);
	}

	$count = 0;
	$columns = array();
	foreach ($result as $row)
	{
		list($type, $length) = $this->_parse_type($row['Type']);

		$column = $this->datatype($type);

		$column['column_name']      = $row['Field'];
		$column['column_default']   = $row['Default'];
		$column['data_type']        = $type;
		$column['is_nullable']      = ($row['Null'] == 'YES');
		$column['ordinal_position'] = ++$count;

		switch ($column['type'])
		{
			case 'float':
				if (isset($length))
				{
					list($column['numeric_precision'], $column['numeric_scale']) = explode(',', $length);
				}
			break;
			case 'int':
				if (isset($length))
				{
					// MySQL attribute
					$column['display'] = $length;
				}
			break;
			case 'string':
				switch ($column['data_type'])
				{
					case 'binary':
					case 'varbinary':
						$column['character_maximum_length'] = $length;
					break;
					case 'char':
					case 'varchar':
						$column['character_maximum_length'] = $length;
					case 'text':
					case 'tinytext':
					case 'mediumtext':
					case 'longtext':
						$column['collation_name'] = $row['Collation'];
					break;
					case 'enum':
					case 'set':
						$column['collation_name'] = $row['Collation'];
						$column['options'] = explode('\',\'', substr($length, 1, -1));
					break;
				}
			break;
		}

		// MySQL attributes
		$column['comment']      = $row['Comment'];
		$column['extra']        = $row['Extra'];
		$column['key']          = $row['Key'];
		$column['privileges']   = $row['Privileges'];

		$columns[$row['Field']] = $column;
	}

	return $columns;
}

public function list_tables($like = NULL)
{
	if (is_string($like))
	{
		// Search for table names
		$result = $this->query(Database::SELECT, 'SHOW TABLES LIKE '.$this->quote($like), FALSE);
	}
	else
	{
		// Find all table names
		$result = $this->query(Database::SELECT, 'SHOW TABLES', FALSE);
	}

	$tables = array();
	foreach ($result as $row)
	{
		$tables[] = reset($row);
	}

	return $tables;
}

public function query($type, $sql, $as_object = FALSE, array $params = NULL)
{
	// Make sure the database is connected
	$this->_connection or $this->connect();

	if ( ! empty($this->_config['profiling']))
	{
		// Benchmark this query for the current instance
		$benchmark = Profiler::start("Database ({$this->_instance})", $sql);
	}

	if ( ! empty($this->_config['connection']['persistent']) AND $this->_config['connection']['database'] !== Database_MySQL::$_current_databases[$this->_connection_id])
	{
		// Select database on persistent connections
		$this->_select_db($this->_config['connection']['database']);
	}

	// Execute the query
	if (($result = mysql_query($sql, $this->_connection)) === FALSE)
	{
		if (isset($benchmark))
		{
			// This benchmark is worthless
			Profiler::delete($benchmark);
		}

		throw new Database_Exception(':error [ :query ]',
			array(':error' => mysql_error($this->_connection), ':query' => $sql),
			mysql_errno($this->_connection));
	}

	if (isset($benchmark))
	{
		Profiler::stop($benchmark);
	}

	// Set the last query
	$this->last_query = $sql;

	if ($type === Database::SELECT)
	{
		// Return an iterator of results
		return new Database_MySQL_Result($result, $sql, $as_object, $params);
	}
	elseif ($type === Database::INSERT)
	{
		// Return a list of insert id and rows created
		return array(
			mysql_insert_id($this->_connection),
			mysql_affected_rows($this->_connection),
		);
	}
	else
	{
		// Return the number of rows affected
		return mysql_affected_rows($this->_connection);
	}
}

public function set_charset($charset)
{
	// Make sure the database is connected
	$this->_connection or $this->connect();

	if (Database_MySQL::$_set_names === TRUE)
	{
		// PHP is compiled against MySQL 4.x
		$status = (bool) mysql_query('SET NAMES '.$this->quote($charset), $this->_connection);
	}
	else
	{
		// PHP is compiled against MySQL 5.x
		$status = mysql_set_charset($charset, $this->_connection);
	}

	if ($status === FALSE)
	{
		throw new Database_Exception(':error',
			array(':error' => mysql_error($this->_connection)),
			mysql_errno($this->_connection));
	}
}

protected function __construct($name, array $config)
{
	// Set the instance name
	$this->_instance = $name;

	// Store the config locally
	$this->_config = $config;

	// Store the database instance
	Database::$instances[$name] = $this;
}

final public function __destruct()
{
	$this->disconnect();
}

final public function __toString()
{
	return $this->_instance;
}

protected function _parse_type($type)
{
	if (($open = strpos($type, '(')) === FALSE)
	{
		// No length specified
		return array($type, NULL);
	}

	// Closing parenthesis
	$close = strpos($type, ')', $open);

	// Length without parentheses
	$length = substr($type, $open + 1, $close - 1 - $open);

	// Type without the length
	$type = substr($type, 0, $open).substr($type, $close + 1);

	return array($type, $length);
}

public function count_last_query()
{
	if ($sql = $this->last_query)
	{
		$sql = trim($sql);
		if (stripos($sql, 'SELECT') !== 0)
		{
			return FALSE;
		}

		if (stripos($sql, 'LIMIT') !== FALSE)
		{
			// Remove LIMIT from the SQL
			$sql = preg_replace('/\sLIMIT\s+[^a-z]+/i', ' ', $sql);
		}

		if (stripos($sql, 'OFFSET') !== FALSE)
		{
			// Remove OFFSET from the SQL
			$sql = preg_replace('/\sOFFSET\s+\d+/i', '', $sql);
		}

		// Get the total rows from the last query executed
		$result = $this->query
		(
			Database::SELECT,
			'SELECT COUNT(*) AS '.$this->quote_identifier('total_rows').' '
			.'FROM ('.$sql.') AS '.$this->quote_table('counted_results'),
			TRUE
		);

		// Return the total number of rows from the query
		return (int) $result->current()->total_rows;
	}

	return FALSE;
}

public function count_records($table)
{
	// Quote the table name
	$table = $this->quote_identifier($table);

	return $this->query(Database::SELECT, 'SELECT COUNT(*) AS total_row_count FROM '.$table, FALSE)
		->get('total_row_count');
}

public static function instance($name = NULL, array $config = NULL)
{
	if ($name === NULL)
	{
		// Use the default instance name
		$name = Database::$default;
	}

	if ( ! isset(Database::$instances[$name]))
	{
		if ($config === NULL)
		{
			// Load the configuration for this database
			$config = Kohana::config('database')->$name;
		}

		if ( ! isset($config['type']))
		{
			throw new Kohana_Exception('Database type not defined in :name configuration',
				array(':name' => $name));
		}

		// Set the driver class name
		$driver = 'Database_'.ucfirst($config['type']);

		// Create the database connection instance
		new $driver($name, $config);
	}

	return Database::$instances[$name];
}

public function quote($value)
{
	if ($value === NULL)
	{
		return 'NULL';
	}
	elseif ($value === TRUE)
	{
		return "'1'";
	}
	elseif ($value === FALSE)
	{
		return "'0'";
	}
	elseif (is_object($value))
	{
		if ($value instanceof Database_Query)
		{
			// Create a sub-query
			return '('.$value->compile($this).')';
		}
		elseif ($value instanceof Database_Expression)
		{
			// Use a raw expression
			return $value->value();
		}
		else
		{
			// Convert the object to a string
			return $this->quote( (string) $value);
		}
	}
	elseif (is_array($value))
	{
		return '('.implode(', ', array_map(array($this, __FUNCTION__), $value)).')';
	}
	elseif (is_int($value))
	{
		return (int) $value;
	}
	elseif (is_float($value))
	{
		// Convert to non-locale aware float to prevent possible commas
		return sprintf('%F', $value);
	}

	return $this->escape($value);
}

public function quote_identifier($value)
{
	if ($value === '*')
	{
		return $value;
	}
	elseif (is_object($value))
	{
		if ($value instanceof Database_Query)
		{
			// Create a sub-query
			return '('.$value->compile($this).')';
		}
		elseif ($value instanceof Database_Expression)
		{
			// Use a raw expression
			return $value->value();
		}
		else
		{
			// Convert the object to a string
			return $this->quote_identifier( (string) $value);
		}
	}
	elseif (is_array($value))
	{
		// Separate the column and alias
		list ($value, $alias) = $value;

		return $this->quote_identifier($value).' AS '.$this->quote_identifier($alias);
	}

	if (strpos($value, '"') !== FALSE)
	{
		// Quote the column in FUNC("ident") identifiers
		return preg_replace('/"(.+?)"/e', '$this->quote_identifier("$1")', $value);
	}
	elseif (strpos($value, '.') !== FALSE)
	{
		// Split the identifier into the individual parts
		$parts = explode('.', $value);

		if ($prefix = $this->table_prefix())
		{
			// Get the offset of the table name, 2nd-to-last part
			// This works for databases that can have 3 identifiers (Postgre)
			$offset = count($parts) - 2;

			// Add the table prefix to the table name
			$parts[$offset] = $prefix.$parts[$offset];
		}

		// Quote each of the parts
		return implode('.', array_map(array($this, __FUNCTION__), $parts));
	}
	else
	{
		return $this->_identifier.$value.$this->_identifier;
	}
}

public function quote_table($value)
{
	// Assign the table by reference from the value
	if (is_array($value))
	{
		$table =& $value[0];

		// Attach table prefix to alias
		$value[1] = $this->table_prefix().$value[1];
	}
	else
	{
		$table =& $value;
	}

	if (is_string($table) AND strpos($table, '.') === FALSE)
	{
		// Add the table prefix for tables
		$table = $this->table_prefix().$table;
	}

	return $this->quote_identifier($value);
}

public function table_prefix()
{
	return $this->_config['table_prefix'];
}