Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

888 lines (821 sloc) 24.675 kb
<?php
/**
* PostgreSQL layer for DBO.
*
* PHP 5
*
* CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
* Copyright 2005-2011, Cake Software Foundation, Inc. (http://cakefoundation.org)
*
* Licensed under The MIT License
* Redistributions of files must retain the above copyright notice.
*
* @copyright Copyright 2005-2011, Cake Software Foundation, Inc. (http://cakefoundation.org)
* @link http://cakephp.org CakePHP(tm) Project
* @package cake.libs.model.datasources.dbo
* @since CakePHP(tm) v 0.9.1.114
* @license MIT License (http://www.opensource.org/licenses/mit-license.php)
*/
App::uses('DboSource', 'Model/Datasource');
/**
* PostgreSQL layer for DBO.
*
* Long description for class
*
* @package cake.libs.model.datasources.dbo
*/
class Postgres extends DboSource {
/**
* Driver description
*
* @var string
* @access public
*/
public $description = "PostgreSQL DBO Driver";
/**
* Index of basic SQL commands
*
* @var array
* @access protected
*/
protected $_commands = array(
'begin' => 'BEGIN',
'commit' => 'COMMIT',
'rollback' => 'ROLLBACK'
);
/**
* Base driver configuration settings. Merged with user settings.
*
* @var array
* @access protected
*/
protected $_baseConfig = array(
'persistent' => true,
'host' => 'localhost',
'login' => 'root',
'password' => '',
'database' => 'cake',
'schema' => 'public',
'port' => 5432,
'encoding' => ''
);
public $columns = array(
'primary_key' => array('name' => 'serial NOT NULL'),
'string' => array('name' => 'varchar', 'limit' => '255'),
'text' => array('name' => 'text'),
'integer' => array('name' => 'integer', 'formatter' => 'intval'),
'float' => array('name' => 'float', 'formatter' => 'floatval'),
'datetime' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
'binary' => array('name' => 'bytea'),
'boolean' => array('name' => 'boolean'),
'number' => array('name' => 'numeric'),
'inet' => array('name' => 'inet')
);
/**
* Starting Quote
*
* @var string
* @access public
*/
public $startQuote = '"';
/**
* Ending Quote
*
* @var string
* @access public
*/
public $endQuote = '"';
/**
* Contains mappings of custom auto-increment sequences, if a table uses a sequence name
* other than what is dictated by convention.
*
* @var array
*/
protected $_sequenceMap = array();
/**
* Connects to the database using options in the given configuration array.
*
* @return True if successfully connected.
*/
public function connect() {
$config = $this->config;
$this->connected = false;
try {
$flags = array(
PDO::ATTR_PERSISTENT => $config['persistent']
);
$this->_connection = new PDO(
"pgsql:host={$config['host']};port={$config['port']};dbname={$config['database']}",
$config['login'],
$config['password'],
$flags
);
$this->connected = true;
if (!empty($config['encoding'])) {
$this->setEncoding($config['encoding']);
}
if (!empty($config['schema'])) {
$this->_execute('SET search_path TO ' . $config['schema']);
}
} catch (PDOException $e) {
throw new MissingConnectionException(array('class' => $e->getMessage()));
}
return $this->connected;
}
/**
* Check if PostgreSQL is enabled/loaded
*
* @return boolean
*/
public function enabled() {
return in_array('pgsql', PDO::getAvailableDrivers());
}
/**
* Returns an array of tables in the database. If there are no tables, an error is raised and the application exits.
*
* @return array Array of tablenames in the database
*/
public function listSources($data = null) {
$cache = parent::listSources();
if ($cache != null) {
return $cache;
}
$schema = $this->config['schema'];
$sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE table_schema = ?";
$result = $this->_execute($sql, array($schema));
if (!$result) {
return array();
} else {
$tables = array();
foreach ($result as $item) {
$tables[] = $item->name;
}
$result->closeCursor();
parent::listSources($tables);
return $tables;
}
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
public function describe($model) {
$fields = parent::describe($model);
$table = $this->fullTableName($model, false);
$this->_sequenceMap[$table] = array();
$cols = null;
if ($fields === null) {
$cols = $this->_execute(
"SELECT DISTINCT column_name AS name, data_type AS type, is_nullable AS null,
column_default AS default, ordinal_position AS position, character_maximum_length AS char_length,
character_octet_length AS oct_length FROM information_schema.columns
WHERE table_name = ? AND table_schema = ? ORDER BY position",
array($table, $this->config['schema'])
);
foreach ($cols as $c) {
$type = $c->type;
if (!empty($c->oct_length) && $c->char_length === null) {
if ($c->type == 'character varying') {
$length = null;
$type = 'text';
} else {
$length = intval($c->oct_length);
}
} elseif (!empty($c->char_length)) {
$length = intval($c->char_length);
} else {
$length = $this->length($c->type);
}
if (empty($length)) {
$length = null;
}
$fields[$c->name] = array(
'type' => $this->column($type),
'null' => ($c->null == 'NO' ? false : true),
'default' => preg_replace(
"/^'(.*)'$/",
"$1",
preg_replace('/::.*/', '', $c->default)
),
'length' => $length
);
if ($model instanceof Model) {
if ($c->name == $model->primaryKey) {
$fields[$c->name]['key'] = 'primary';
if ($fields[$c->name]['type'] !== 'string') {
$fields[$c->name]['length'] = 11;
}
}
}
if (
$fields[$c->name]['default'] == 'NULL' ||
preg_match('/nextval\([\'"]?([\w.]+)/', $c->default, $seq)
) {
$fields[$c->name]['default'] = null;
if (!empty($seq) && isset($seq[1])) {
$this->_sequenceMap[$table][$c->default] = $seq[1];
}
}
if ($fields[$c->name]['type'] == 'boolean' && !empty($fields[$c->name]['default'])) {
$fields[$c->name]['default'] = constant($fields[$c->name]['default']);
}
}
$this->__cacheDescription($table, $fields);
}
if (isset($model->sequence)) {
$this->_sequenceMap[$table][$model->primaryKey] = $model->sequence;
}
if ($cols) {
$cols->closeCursor();
}
return $fields;
}
/**
* Returns the ID generated from the previous INSERT operation.
*
* @param string $source Name of the database table
* @param string $field Name of the ID database field. Defaults to "id"
* @return integer
*/
public function lastInsertId($source, $field = 'id') {
$seq = $this->getSequence($source, $field);
return $this->_connection->lastInsertId($seq);
}
/**
* Gets the associated sequence for the given table/field
*
* @param mixed $table Either a full table name (with prefix) as a string, or a model object
* @param string $field Name of the ID database field. Defaults to "id"
* @return string The associated sequence name from the sequence map, defaults to "{$table}_{$field}_seq"
*/
public function getSequence($table, $field = 'id') {
if (is_object($table)) {
$table = $this->fullTableName($table, false);
}
if (isset($this->_sequenceMap[$table]) && isset($this->_sequenceMap[$table][$field])) {
return $this->_sequenceMap[$table][$field];
} else {
return "{$table}_{$field}_seq";
}
}
/**
* Deletes all the records in a table and drops all associated auto-increment sequences
*
* @param mixed $table A string or model class representing the table to be truncated
* @param boolean $reset true for resseting the sequence, false to leave it as is.
* and if 1, sequences are not modified
* @return boolean SQL TRUNCATE TABLE statement, false if not applicable.
*/
public function truncate($table, $reset = true) {
$table = $this->fullTableName($table, false);
if (!isset($this->_sequenceMap[$table])) {
$cache = $this->cacheSources;
$this->cacheSources = false;
$this->describe($table);
$this->cacheSources = $cache;
}
if (parent::truncate($table)) {
if (isset($this->_sequenceMap[$table]) && $reset) {
foreach ($this->_sequenceMap[$table] as $field => $sequence) {
$this->_execute("ALTER SEQUENCE \"{$sequence}\" RESTART WITH 1");
}
}
return true;
}
return false;
}
/**
* Prepares field names to be quoted by parent
*
* @param string $data
* @return string SQL field
*/
public function name($data) {
if (is_string($data)) {
$data = str_replace('"__"', '__', $data);
}
return parent::name($data);
}
/**
* Generates the fields list of an SQL query.
*
* @param Model $model
* @param string $alias Alias tablename
* @param mixed $fields
* @return array
*/
public function fields($model, $alias = null, $fields = array(), $quote = true) {
if (empty($alias)) {
$alias = $model->alias;
}
$fields = parent::fields($model, $alias, $fields, false);
if (!$quote) {
return $fields;
}
$count = count($fields);
if ($count >= 1 && !preg_match('/^\s*COUNT\(\*/', $fields[0])) {
$result = array();
for ($i = 0; $i < $count; $i++) {
if (!preg_match('/^.+\\(.*\\)/', $fields[$i]) && !preg_match('/\s+AS\s+/', $fields[$i])) {
if (substr($fields[$i], -1) == '*') {
if (strpos($fields[$i], '.') !== false && $fields[$i] != $alias . '.*') {
$build = explode('.', $fields[$i]);
$AssociatedModel = $model->{$build[0]};
} else {
$AssociatedModel = $model;
}
$_fields = $this->fields($AssociatedModel, $AssociatedModel->alias, array_keys($AssociatedModel->schema()));
$result = array_merge($result, $_fields);
continue;
}
$prepend = '';
if (strpos($fields[$i], 'DISTINCT') !== false) {
$prepend = 'DISTINCT ';
$fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
}
if (strrpos($fields[$i], '.') === false) {
$fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
} else {
$build = explode('.', $fields[$i]);
$fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
}
} else {
$fields[$i] = preg_replace_callback('/\(([\s\.\w]+)\)/', array(&$this, '__quoteFunctionField'), $fields[$i]);
}
$result[] = $fields[$i];
}
return $result;
}
return $fields;
}
/**
* Auxiliary function to quote matched `(Model.fields)` from a preg_replace_callback call
*
* @param string matched string
* @return string quoted strig
* @access private
*/
private function __quoteFunctionField($match) {
$prepend = '';
if (strpos($match[1], 'DISTINCT') !== false) {
$prepend = 'DISTINCT ';
$match[1] = trim(str_replace('DISTINCT', '', $match[1]));
}
if (strpos($match[1], '.') === false) {
$match[1] = $this->name($match[1]);
} else {
$parts = explode('.', $match[1]);
if (!Set::numeric($parts)) {
$match[1] = $this->name($match[1]);
}
}
return '(' . $prepend .$match[1] . ')';
}
/**
* Returns an array of the indexes in given datasource name.
*
* @param string $model Name of model to inspect
* @return array Fields in table. Keys are column and unique
*/
public function index($model) {
$index = array();
$table = $this->fullTableName($model, false);
if ($table) {
$indexes = $this->query("SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as statement, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = (
SELECT c.oid
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(" . $table . ")$'
AND pg_catalog.pg_table_is_visible(c.oid)
AND n.nspname ~ '^(" . $this->config['schema'] . ")$'
)
AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname", false);
foreach ($indexes as $i => $info) {
$key = array_pop($info);
if ($key['indisprimary']) {
$key['relname'] = 'PRIMARY';
}
$col = array();
preg_match('/\(([^\)]+)\)/', $key['statement'], $indexColumns);
$parsedColumn = $indexColumns[1];
if (strpos($indexColumns[1], ',') !== false) {
$parsedColumn = explode(', ', $indexColumns[1]);
}
$index[$key['relname']]['unique'] = $key['indisunique'];
$index[$key['relname']]['column'] = $parsedColumn;
}
}
return $index;
}
/**
* Alter the Schema of a table.
*
* @param array $compare Results of CakeSchema::compare()
* @param string $table name of the table
* @access public
* @return array
*/
public function alterSchema($compare, $table = null) {
if (!is_array($compare)) {
return false;
}
$out = '';
$colList = array();
foreach ($compare as $curTable => $types) {
$indexes = $colList = array();
if (!$table || $table == $curTable) {
$out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
foreach ($types as $type => $column) {
if (isset($column['indexes'])) {
$indexes[$type] = $column['indexes'];
unset($column['indexes']);
}
switch ($type) {
case 'add':
foreach ($column as $field => $col) {
$col['name'] = $field;
$alter = 'ADD COLUMN '.$this->buildColumn($col);
if (isset($col['after'])) {
$alter .= ' AFTER '. $this->name($col['after']);
}
$colList[] = $alter;
}
break;
case 'drop':
foreach ($column as $field => $col) {
$col['name'] = $field;
$colList[] = 'DROP COLUMN '.$this->name($field);
}
break;
case 'change':
foreach ($column as $field => $col) {
if (!isset($col['name'])) {
$col['name'] = $field;
}
$fieldName = $this->name($field);
$default = isset($col['default']) ? $col['default'] : null;
$nullable = isset($col['null']) ? $col['null'] : null;
unset($col['default'], $col['null']);
$colList[] = 'ALTER COLUMN '. $fieldName .' TYPE ' . str_replace($fieldName, '', $this->buildColumn($col));
if (isset($nullable)) {
$nullable = ($nullable) ? 'DROP NOT NULL' : 'SET NOT NULL';
$colList[] = 'ALTER COLUMN '. $fieldName .' ' . $nullable;
}
if (isset($default)) {
$colList[] = 'ALTER COLUMN '. $fieldName .' SET DEFAULT ' . $this->value($default, $col['type']);
} else {
$colList[] = 'ALTER COLUMN '. $fieldName .' DROP DEFAULT';
}
}
break;
}
}
if (isset($indexes['drop']['PRIMARY'])) {
$colList[] = 'DROP CONSTRAINT ' . $curTable . '_pkey';
}
if (isset($indexes['add']['PRIMARY'])) {
$cols = $indexes['add']['PRIMARY']['column'];
if (is_array($cols)) {
$cols = implode(', ', $cols);
}
$colList[] = 'ADD PRIMARY KEY (' . $cols . ')';
}
if (!empty($colList)) {
$out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
} else {
$out = '';
}
$out .= implode(";\n\t", $this->_alterIndexes($curTable, $indexes));
}
}
return $out;
}
/**
* Generate PostgreSQL index alteration statements for a table.
*
* @param string $table Table to alter indexes for
* @param array $new Indexes to add and drop
* @return array Index alteration statements
*/
protected function _alterIndexes($table, $indexes) {
$alter = array();
if (isset($indexes['drop'])) {
foreach($indexes['drop'] as $name => $value) {
$out = 'DROP ';
if ($name == 'PRIMARY') {
continue;
} else {
$out .= 'INDEX ' . $name;
}
$alter[] = $out;
}
}
if (isset($indexes['add'])) {
foreach ($indexes['add'] as $name => $value) {
$out = 'CREATE ';
if ($name == 'PRIMARY') {
continue;
} else {
if (!empty($value['unique'])) {
$out .= 'UNIQUE ';
}
$out .= 'INDEX ';
}
if (is_array($value['column'])) {
$out .= $name . ' ON ' . $table . ' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
} else {
$out .= $name . ' ON ' . $table . ' (' . $this->name($value['column']) . ')';
}
$alter[] = $out;
}
}
return $alter;
}
/**
* Returns a limit statement in the correct format for the particular database.
*
* @param integer $limit Limit of results returned
* @param integer $offset Offset from which to start results
* @return string SQL limit/offset statement
*/
public function limit($limit, $offset = null) {
if ($limit) {
$rt = '';
if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
$rt = ' LIMIT';
}
$rt .= ' ' . $limit;
if ($offset) {
$rt .= ' OFFSET ' . $offset;
}
return $rt;
}
return null;
}
/**
* Converts database-layer column types to basic types
*
* @param string $real Real database-layer column type (i.e. "varchar(255)")
* @return string Abstract column type (i.e. "string")
*/
public function column($real) {
if (is_array($real)) {
$col = $real['name'];
if (isset($real['limit'])) {
$col .= '(' . $real['limit'] . ')';
}
return $col;
}
$col = str_replace(')', '', $real);
$limit = null;
if (strpos($col, '(') !== false) {
list($col, $limit) = explode('(', $col);
}
$floats = array(
'float', 'float4', 'float8', 'double', 'double precision', 'decimal', 'real', 'numeric'
);
switch (true) {
case (in_array($col, array('date', 'time', 'inet', 'boolean'))):
return $col;
case (strpos($col, 'timestamp') !== false):
return 'datetime';
case (strpos($col, 'time') === 0):
return 'time';
case (strpos($col, 'int') !== false && $col != 'interval'):
return 'integer';
case (strpos($col, 'char') !== false || $col == 'uuid'):
return 'string';
case (strpos($col, 'text') !== false):
return 'text';
case (strpos($col, 'bytea') !== false):
return 'binary';
case (in_array($col, $floats)):
return 'float';
default:
return 'text';
break;
}
}
/**
* Gets the length of a database-native column description, or null if no length
*
* @param string $real Real database-layer column type (i.e. "varchar(255)")
* @return int An integer representing the length of the column
*/
public function length($real) {
$col = str_replace(array(')', 'unsigned'), '', $real);
$limit = null;
if (strpos($col, '(') !== false) {
list($col, $limit) = explode('(', $col);
}
if ($col == 'uuid') {
return 36;
}
if ($limit != null) {
return intval($limit);
}
return null;
}
/**
* Enter description here...
*
* @param unknown_type $results
*/
public function resultSet(&$results) {
$this->map = array();
$numFields = $results->columnCount();
$index = 0;
$j = 0;
while ($j < $numFields) {
$column = $results->getColumnMeta($j);
if (strpos($column['name'], '__')) {
list($table, $name) = explode('__', $column['name']);
$this->map[$index++] = array($table, $name, $column['native_type']);
} else {
$this->map[$index++] = array(0, $column['name'], $column['native_type']);
}
$j++;
}
}
/**
* Fetches the next row from the current result set
*
* @return unknown
*/
public function fetchResult() {
if ($row = $this->_result->fetch()) {
$resultRow = array();
foreach ($this->map as $index => $meta) {
list($table, $column, $type) = $meta;
switch ($type) {
case 'bool':
$resultRow[$table][$column] = is_null($row[$index]) ? null : $this->boolean($row[$index]);
break;
case 'binary':
case 'bytea':
$resultRow[$table][$column] = stream_get_contents($row[$index]);
break;
default:
$resultRow[$table][$column] = $row[$index];
break;
}
}
return $resultRow;
} else {
$this->_result->closeCursor();
return false;
}
}
/**
* Translates between PHP boolean values and PostgreSQL boolean values
*
* @param mixed $data Value to be translated
* @param boolean $quote true to quote a boolean to be used in a query, flase to return the boolean value
* @return boolean Converted boolean value
*/
public function boolean($data, $quote = false) {
switch (true) {
case ($data === true || $data === false):
$result = $data;
break;
case ($data === 't' || $data === 'f'):
$result = ($data === 't');
break;
case ($data === 'true' || $data === 'false'):
$result = ($data === 'true');
break;
case ($data === 'TRUE' || $data === 'FALSE'):
$result = ($data === 'TRUE');
break;
default:
$result = (bool) $data;
break;
}
if ($quote) {
return ($result) ? 'TRUE' : 'FALSE';
}
return (bool) $result;
}
/**
* Sets the database encoding
*
* @param mixed $enc Database encoding
* @return boolean True on success, false on failure
*/
public function setEncoding($enc) {
if ($this->_execute('SET NAMES ?', array($enc))) {
return true;
}
return false;
}
/**
* Gets the database encoding
*
* @return string The database encoding
*/
public function getEncoding() {
$cosa = $this->_execute('SHOW client_encoding')->fetch();
}
/**
* Generate a Postgres-native column schema string
*
* @param array $column An array structured like the following:
* array('name'=>'value', 'type'=>'value'[, options]),
* where options can be 'default', 'length', or 'key'.
* @return string
*/
public function buildColumn($column) {
$col = $this->columns[$column['type']];
if (!isset($col['length']) && !isset($col['limit'])) {
unset($column['length']);
}
$out = preg_replace('/integer\([0-9]+\)/', 'integer', parent::buildColumn($column));
$out = str_replace('integer serial', 'serial', $out);
if (strpos($out, 'timestamp DEFAULT')) {
if (isset($column['null']) && $column['null']) {
$out = str_replace('DEFAULT NULL', '', $out);
} else {
$out = str_replace('DEFAULT NOT NULL', '', $out);
}
}
if (strpos($out, 'DEFAULT DEFAULT')) {
if (isset($column['null']) && $column['null']) {
$out = str_replace('DEFAULT DEFAULT', 'DEFAULT NULL', $out);
} elseif (in_array($column['type'], array('integer', 'float'))) {
$out = str_replace('DEFAULT DEFAULT', 'DEFAULT 0', $out);
} elseif ($column['type'] == 'boolean') {
$out = str_replace('DEFAULT DEFAULT', 'DEFAULT FALSE', $out);
}
}
return $out;
}
/**
* Format indexes for create table
*
* @param array $indexes
* @param string $table
* @return string
*/
public function buildIndex($indexes, $table = null) {
$join = array();
if (!is_array($indexes)) {
return array();
}
foreach ($indexes as $name => $value) {
if ($name == 'PRIMARY') {
$out = 'PRIMARY KEY (' . $this->name($value['column']) . ')';
} else {
$out = 'CREATE ';
if (!empty($value['unique'])) {
$out .= 'UNIQUE ';
}
if (is_array($value['column'])) {
$value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column']));
} else {
$value['column'] = $this->name($value['column']);
}
$out .= "INDEX {$name} ON {$table}({$value['column']});";
}
$join[] = $out;
}
return $join;
}
/**
* Overrides DboSource::renderStatement to handle schema generation with Postgres-style indexes
*
* @param string $type
* @param array $data
* @return string
*/
public function renderStatement($type, $data) {
switch (strtolower($type)) {
case 'schema':
extract($data);
foreach ($indexes as $i => $index) {
if (preg_match('/PRIMARY KEY/', $index)) {
unset($indexes[$i]);
$columns[] = $index;
break;
}
}
$join = array('columns' => ",\n\t", 'indexes' => "\n");
foreach (array('columns', 'indexes') as $var) {
if (is_array(${$var})) {
${$var} = implode($join[$var], array_filter(${$var}));
}
}
return "CREATE TABLE {$table} (\n\t{$columns}\n);\n{$indexes}";
break;
default:
return parent::renderStatement($type, $data);
break;
}
}
}
Jump to Line
Something went wrong with that request. Please try again.