1: <?php
   2: /**
   3:  * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
   4:  * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
   5:  *
   6:  * Licensed under The MIT License
   7:  * For full copyright and license information, please see the LICENSE.txt
   8:  * Redistributions of files must retain the above copyright notice.
   9:  *
  10:  * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
  11:  * @link          https://cakephp.org CakePHP(tm) Project
  12:  * @package       Cake.Model.Datasource.Database
  13:  * @since         CakePHP(tm) v 0.9.1.114
  14:  * @license       https://opensource.org/licenses/mit-license.php MIT License
  15:  */
  16: 
  17: App::uses('DboSource', 'Model/Datasource');
  18: 
  19: /**
  20:  * PostgreSQL layer for DBO.
  21:  *
  22:  * @package       Cake.Model.Datasource.Database
  23:  */
  24: class Postgres extends DboSource {
  25: 
  26: /**
  27:  * Driver description
  28:  *
  29:  * @var string
  30:  */
  31:     public $description = "PostgreSQL DBO Driver";
  32: 
  33: /**
  34:  * Base driver configuration settings. Merged with user settings.
  35:  *
  36:  * @var array
  37:  */
  38:     protected $_baseConfig = array(
  39:         'persistent' => true,
  40:         'host' => 'localhost',
  41:         'login' => 'root',
  42:         'password' => '',
  43:         'database' => 'cake',
  44:         'schema' => 'public',
  45:         'port' => 5432,
  46:         'encoding' => '',
  47:         'sslmode' => 'allow',
  48:         'flags' => array()
  49:     );
  50: 
  51: /**
  52:  * Columns
  53:  *
  54:  * @var array
  55:  * @link https://www.postgresql.org/docs/9.6/static/datatype.html PostgreSQL Data Types
  56:  */
  57:     public $columns = array(
  58:         'primary_key' => array('name' => 'serial NOT NULL'),
  59:         'string' => array('name' => 'varchar', 'limit' => '255'),
  60:         'text' => array('name' => 'text'),
  61:         'integer' => array('name' => 'integer', 'formatter' => 'intval'),
  62:         'smallinteger' => array('name' => 'smallint', 'formatter' => 'intval'),
  63:         'tinyinteger' => array('name' => 'smallint', 'formatter' => 'intval'),
  64:         'biginteger' => array('name' => 'bigint', 'limit' => '20'),
  65:         'float' => array('name' => 'float', 'formatter' => 'floatval'),
  66:         'decimal' => array('name' => 'decimal', 'formatter' => 'floatval'),
  67:         'datetime' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
  68:         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
  69:         'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
  70:         'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
  71:         'binary' => array('name' => 'bytea'),
  72:         'boolean' => array('name' => 'boolean'),
  73:         'number' => array('name' => 'numeric'),
  74:         'inet' => array('name' => 'inet'),
  75:         'uuid' => array('name' => 'uuid')
  76:     );
  77: 
  78: /**
  79:  * Starting Quote
  80:  *
  81:  * @var string
  82:  */
  83:     public $startQuote = '"';
  84: 
  85: /**
  86:  * Ending Quote
  87:  *
  88:  * @var string
  89:  */
  90:     public $endQuote = '"';
  91: 
  92: /**
  93:  * Contains mappings of custom auto-increment sequences, if a table uses a sequence name
  94:  * other than what is dictated by convention.
  95:  *
  96:  * @var array
  97:  */
  98:     protected $_sequenceMap = array();
  99: 
 100: /**
 101:  * The set of valid SQL operations usable in a WHERE statement
 102:  *
 103:  * @var array
 104:  */
 105:     protected $_sqlOps = array('like', 'ilike', 'or', 'not', 'in', 'between', '~', '~\*', '\!~', '\!~\*', 'similar to');
 106: 
 107: /**
 108:  * Connects to the database using options in the given configuration array.
 109:  *
 110:  * @return bool True if successfully connected.
 111:  * @throws MissingConnectionException
 112:  */
 113:     public function connect() {
 114:         $config = $this->config;
 115:         $this->connected = false;
 116: 
 117:         $flags = $config['flags'] + array(
 118:             PDO::ATTR_PERSISTENT => $config['persistent'],
 119:             PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 120:         );
 121: 
 122:         try {
 123:             $this->_connection = new PDO(
 124:                 "pgsql:host={$config['host']};port={$config['port']};dbname={$config['database']};sslmode={$config['sslmode']}",
 125:                 $config['login'],
 126:                 $config['password'],
 127:                 $flags
 128:             );
 129: 
 130:             $this->connected = true;
 131:             if (!empty($config['encoding'])) {
 132:                 $this->setEncoding($config['encoding']);
 133:             }
 134:             if (!empty($config['schema'])) {
 135:                 $this->_execute('SET search_path TO "' . $config['schema'] . '"');
 136:             }
 137:             if (!empty($config['settings'])) {
 138:                 foreach ($config['settings'] as $key => $value) {
 139:                     $this->_execute("SET $key TO $value");
 140:                 }
 141:             }
 142:         } catch (PDOException $e) {
 143:             throw new MissingConnectionException(array(
 144:                 'class' => get_class($this),
 145:                 'message' => $e->getMessage()
 146:             ));
 147:         }
 148: 
 149:         return $this->connected;
 150:     }
 151: 
 152: /**
 153:  * Check if PostgreSQL is enabled/loaded
 154:  *
 155:  * @return bool
 156:  */
 157:     public function enabled() {
 158:         return in_array('pgsql', PDO::getAvailableDrivers());
 159:     }
 160: 
 161: /**
 162:  * Returns an array of tables in the database. If there are no tables, an error is raised and the application exits.
 163:  *
 164:  * @param mixed $data The sources to list.
 165:  * @return array Array of table names in the database
 166:  */
 167:     public function listSources($data = null) {
 168:         $cache = parent::listSources();
 169: 
 170:         if ($cache) {
 171:             return $cache;
 172:         }
 173: 
 174:         $schema = $this->config['schema'];
 175:         $sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE table_schema = ?";
 176:         $result = $this->_execute($sql, array($schema));
 177: 
 178:         if (!$result) {
 179:             return array();
 180:         }
 181: 
 182:         $tables = array();
 183: 
 184:         foreach ($result as $item) {
 185:             $tables[] = $item->name;
 186:         }
 187: 
 188:         $result->closeCursor();
 189:         parent::listSources($tables);
 190:         return $tables;
 191:     }
 192: 
 193: /**
 194:  * Returns an array of the fields in given table name.
 195:  *
 196:  * @param Model|string $model Name of database table to inspect
 197:  * @return array Fields in table. Keys are name and type
 198:  */
 199:     public function describe($model) {
 200:         $table = $this->fullTableName($model, false, false);
 201:         $fields = parent::describe($table);
 202:         $this->_sequenceMap[$table] = array();
 203:         $cols = null;
 204:         $hasPrimary = false;
 205: 
 206:         if ($fields === null) {
 207:             $cols = $this->_execute(
 208:                 'SELECT DISTINCT table_schema AS schema,
 209:                     column_name AS name,
 210:                     data_type AS type,
 211:                     is_nullable AS null,
 212:                     column_default AS default,
 213:                     ordinal_position AS position,
 214:                     character_maximum_length AS char_length,
 215:                     character_octet_length AS oct_length,
 216:                     pg_get_serial_sequence(attr.attrelid::regclass::text, attr.attname) IS NOT NULL AS has_serial
 217:                 FROM information_schema.columns c
 218:                 INNER JOIN pg_catalog.pg_namespace ns ON (ns.nspname = table_schema)
 219:                 INNER JOIN pg_catalog.pg_class cl ON (cl.relnamespace = ns.oid AND cl.relname = table_name)
 220:                 LEFT JOIN pg_catalog.pg_attribute attr ON (cl.oid = attr.attrelid AND column_name = attr.attname)
 221:                 WHERE table_name = ? AND table_schema = ? AND table_catalog = ?
 222:                 ORDER BY ordinal_position',
 223:                 array($table, $this->config['schema'], $this->config['database'])
 224:             );
 225: 
 226:             // @codingStandardsIgnoreStart
 227:             // Postgres columns don't match the coding standards.
 228:             foreach ($cols as $c) {
 229:                 $type = $c->type;
 230:                 if (!empty($c->oct_length) && $c->char_length === null) {
 231:                     if ($c->type === 'character varying') {
 232:                         $length = null;
 233:                         $type = 'text';
 234:                     } elseif ($c->type === 'uuid') {
 235:                         $type = 'uuid';
 236:                         $length = 36;
 237:                     } else {
 238:                         $length = (int)$c->oct_length;
 239:                     }
 240:                 } elseif (!empty($c->char_length)) {
 241:                     $length = (int)$c->char_length;
 242:                 } else {
 243:                     $length = $this->length($c->type);
 244:                 }
 245:                 if (empty($length)) {
 246:                     $length = null;
 247:                 }
 248:                 $fields[$c->name] = array(
 249:                     'type' => $this->column($type),
 250:                     'null' => ($c->null === 'NO' ? false : true),
 251:                     'default' => preg_replace(
 252:                         "/^'(.*)'$/",
 253:                         "$1",
 254:                         preg_replace('/::[\w\s]+/', '', $c->default)
 255:                     ),
 256:                     'length' => $length,
 257:                 );
 258: 
 259:                 // Serial columns are primary integer keys
 260:                 if ($c->has_serial) {
 261:                     $fields[$c->name]['key'] = 'primary';
 262:                     $fields[$c->name]['length'] = 11;
 263:                     $hasPrimary = true;
 264:                 }
 265:                 if ($hasPrimary === false &&
 266:                     $model instanceof Model &&
 267:                     $c->name === $model->primaryKey
 268:                 ) {
 269:                     $fields[$c->name]['key'] = 'primary';
 270:                     if (
 271:                         $fields[$c->name]['type'] !== 'string' &&
 272:                         $fields[$c->name]['type'] !== 'uuid'
 273:                     ) {
 274:                         $fields[$c->name]['length'] = 11;
 275:                     }
 276:                 }
 277:                 if (
 278:                     $fields[$c->name]['default'] === 'NULL' ||
 279:                     $c->default === null ||
 280:                     preg_match('/nextval\([\'"]?([\w.]+)/', $c->default, $seq)
 281:                 ) {
 282:                     $fields[$c->name]['default'] = null;
 283:                     if (!empty($seq) && isset($seq[1])) {
 284:                         if (strpos($seq[1], '.') === false) {
 285:                             $sequenceName = $c->schema . '.' . $seq[1];
 286:                         } else {
 287:                             $sequenceName = $seq[1];
 288:                         }
 289:                         $this->_sequenceMap[$table][$c->name] = $sequenceName;
 290:                     }
 291:                 }
 292:                 if ($fields[$c->name]['type'] === 'timestamp' && $fields[$c->name]['default'] === '') {
 293:                     $fields[$c->name]['default'] = null;
 294:                 }
 295:                 if ($fields[$c->name]['type'] === 'boolean' && !empty($fields[$c->name]['default'])) {
 296:                     $fields[$c->name]['default'] = constant($fields[$c->name]['default']);
 297:                 }
 298:             }
 299:             $this->_cacheDescription($table, $fields);
 300:         }
 301:         // @codingStandardsIgnoreEnd
 302: 
 303:         if (isset($model->sequence)) {
 304:             $this->_sequenceMap[$table][$model->primaryKey] = $model->sequence;
 305:         }
 306: 
 307:         if ($cols) {
 308:             $cols->closeCursor();
 309:         }
 310:         return $fields;
 311:     }
 312: 
 313: /**
 314:  * Returns the ID generated from the previous INSERT operation.
 315:  *
 316:  * @param string $source Name of the database table
 317:  * @param string $field Name of the ID database field. Defaults to "id"
 318:  * @return int
 319:  */
 320:     public function lastInsertId($source = null, $field = 'id') {
 321:         $seq = $this->getSequence($source, $field);
 322:         return $this->_connection->lastInsertId($seq);
 323:     }
 324: 
 325: /**
 326:  * Gets the associated sequence for the given table/field
 327:  *
 328:  * @param string|Model $table Either a full table name (with prefix) as a string, or a model object
 329:  * @param string $field Name of the ID database field. Defaults to "id"
 330:  * @return string The associated sequence name from the sequence map, defaults to "{$table}_{$field}_seq"
 331:  */
 332:     public function getSequence($table, $field = 'id') {
 333:         if (is_object($table)) {
 334:             $table = $this->fullTableName($table, false, false);
 335:         }
 336:         if (!isset($this->_sequenceMap[$table])) {
 337:             $this->describe($table);
 338:         }
 339:         if (isset($this->_sequenceMap[$table][$field])) {
 340:             return $this->_sequenceMap[$table][$field];
 341:         }
 342:         return "{$table}_{$field}_seq";
 343:     }
 344: 
 345: /**
 346:  * Reset a sequence based on the MAX() value of $column. Useful
 347:  * for resetting sequences after using insertMulti().
 348:  *
 349:  * @param string $table The name of the table to update.
 350:  * @param string $column The column to use when resetting the sequence value,
 351:  *   the sequence name will be fetched using Postgres::getSequence();
 352:  * @return bool success.
 353:  */
 354:     public function resetSequence($table, $column) {
 355:         $tableName = $this->fullTableName($table, false, false);
 356:         $fullTable = $this->fullTableName($table);
 357: 
 358:         $sequence = $this->value($this->getSequence($tableName, $column));
 359:         $column = $this->name($column);
 360:         $this->execute("SELECT setval($sequence, (SELECT MAX($column) FROM $fullTable))");
 361:         return true;
 362:     }
 363: 
 364: /**
 365:  * Deletes all the records in a table and drops all associated auto-increment sequences
 366:  *
 367:  * @param string|Model $table A string or model class representing the table to be truncated
 368:  * @param bool $reset true for resetting the sequence, false to leave it as is.
 369:  *    and if 1, sequences are not modified
 370:  * @return bool SQL TRUNCATE TABLE statement, false if not applicable.
 371:  */
 372:     public function truncate($table, $reset = false) {
 373:         $table = $this->fullTableName($table, false, false);
 374:         if (!isset($this->_sequenceMap[$table])) {
 375:             $cache = $this->cacheSources;
 376:             $this->cacheSources = false;
 377:             $this->describe($table);
 378:             $this->cacheSources = $cache;
 379:         }
 380:         if ($this->execute('DELETE FROM ' . $this->fullTableName($table))) {
 381:             if (isset($this->_sequenceMap[$table]) && $reset != true) {
 382:                 foreach ($this->_sequenceMap[$table] as $sequence) {
 383:                     $quoted = $this->name($sequence);
 384:                     $this->_execute("ALTER SEQUENCE {$quoted} RESTART WITH 1");
 385:                 }
 386:             }
 387:             return true;
 388:         }
 389:         return false;
 390:     }
 391: 
 392: /**
 393:  * Prepares field names to be quoted by parent
 394:  *
 395:  * @param string $data The name to format.
 396:  * @return string SQL field
 397:  */
 398:     public function name($data) {
 399:         if (is_string($data)) {
 400:             $data = str_replace('"__"', '__', $data);
 401:         }
 402:         return parent::name($data);
 403:     }
 404: 
 405: /**
 406:  * Generates the fields list of an SQL query.
 407:  *
 408:  * @param Model $model The model to get fields for.
 409:  * @param string $alias Alias table name.
 410:  * @param mixed $fields The list of fields to get.
 411:  * @param bool $quote Whether or not to quote identifiers.
 412:  * @return array
 413:  */
 414:     public function fields(Model $model, $alias = null, $fields = array(), $quote = true) {
 415:         if (empty($alias)) {
 416:             $alias = $model->alias;
 417:         }
 418:         $fields = parent::fields($model, $alias, $fields, false);
 419: 
 420:         if (!$quote) {
 421:             return $fields;
 422:         }
 423:         $count = count($fields);
 424: 
 425:         if ($count >= 1 && !preg_match('/^\s*COUNT\(\*/', $fields[0])) {
 426:             $result = array();
 427:             for ($i = 0; $i < $count; $i++) {
 428:                 if (!preg_match('/^.+\\(.*\\)/', $fields[$i]) && !preg_match('/\s+AS\s+/', $fields[$i])) {
 429:                     if (substr($fields[$i], -1) === '*') {
 430:                         if (strpos($fields[$i], '.') !== false && $fields[$i] != $alias . '.*') {
 431:                             $build = explode('.', $fields[$i]);
 432:                             $AssociatedModel = $model->{$build[0]};
 433:                         } else {
 434:                             $AssociatedModel = $model;
 435:                         }
 436: 
 437:                         $_fields = $this->fields($AssociatedModel, $AssociatedModel->alias, array_keys($AssociatedModel->schema()));
 438:                         $result = array_merge($result, $_fields);
 439:                         continue;
 440:                     }
 441: 
 442:                     $prepend = '';
 443:                     if (strpos($fields[$i], 'DISTINCT') !== false) {
 444:                         $prepend = 'DISTINCT ';
 445:                         $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
 446:                     }
 447: 
 448:                     if (strrpos($fields[$i], '.') === false) {
 449:                         $fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
 450:                     } else {
 451:                         $build = explode('.', $fields[$i]);
 452:                         $fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
 453:                     }
 454:                 } else {
 455:                     $fields[$i] = preg_replace_callback('/\(([\s\.\w]+)\)/', array(&$this, '_quoteFunctionField'), $fields[$i]);
 456:                 }
 457:                 $result[] = $fields[$i];
 458:             }
 459:             return $result;
 460:         }
 461:         return $fields;
 462:     }
 463: 
 464: /**
 465:  * Auxiliary function to quote matched `(Model.fields)` from a preg_replace_callback call
 466:  * Quotes the fields in a function call.
 467:  *
 468:  * @param string $match matched string
 469:  * @return string quoted string
 470:  */
 471:     protected function _quoteFunctionField($match) {
 472:         $prepend = '';
 473:         if (strpos($match[1], 'DISTINCT') !== false) {
 474:             $prepend = 'DISTINCT ';
 475:             $match[1] = trim(str_replace('DISTINCT', '', $match[1]));
 476:         }
 477:         $constant = preg_match('/^\d+|NULL|FALSE|TRUE$/i', $match[1]);
 478: 
 479:         if (!$constant && strpos($match[1], '.') === false) {
 480:             $match[1] = $this->name($match[1]);
 481:         } elseif (!$constant) {
 482:             $parts = explode('.', $match[1]);
 483:             if (!Hash::numeric($parts)) {
 484:                 $match[1] = $this->name($match[1]);
 485:             }
 486:         }
 487:         return '(' . $prepend . $match[1] . ')';
 488:     }
 489: 
 490: /**
 491:  * Returns an array of the indexes in given datasource name.
 492:  *
 493:  * @param string $model Name of model to inspect
 494:  * @return array Fields in table. Keys are column and unique
 495:  */
 496:     public function index($model) {
 497:         $index = array();
 498:         $table = $this->fullTableName($model, false, false);
 499:         if ($table) {
 500:             $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
 501:             FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
 502:             WHERE c.oid  = (
 503:                 SELECT c.oid
 504:                 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 505:                 WHERE c.relname ~ '^(" . $table . ")$'
 506:                     AND pg_catalog.pg_table_is_visible(c.oid)
 507:                     AND n.nspname ~ '^(" . $this->config['schema'] . ")$'
 508:             )
 509:             AND c.oid = i.indrelid AND i.indexrelid = c2.oid
 510:             ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname", false);
 511:             foreach ($indexes as $info) {
 512:                 $key = array_pop($info);
 513:                 if ($key['indisprimary']) {
 514:                     $key['relname'] = 'PRIMARY';
 515:                 }
 516:                 preg_match('/\(([^\)]+)\)/', $key['statement'], $indexColumns);
 517:                 $parsedColumn = $indexColumns[1];
 518:                 if (strpos($indexColumns[1], ',') !== false) {
 519:                     $parsedColumn = explode(', ', $indexColumns[1]);
 520:                 }
 521:                 $index[$key['relname']]['unique'] = $key['indisunique'];
 522:                 $index[$key['relname']]['column'] = $parsedColumn;
 523:             }
 524:         }
 525:         return $index;
 526:     }
 527: 
 528: /**
 529:  * Alter the Schema of a table.
 530:  *
 531:  * @param array $compare Results of CakeSchema::compare()
 532:  * @param string $table name of the table
 533:  * @return array
 534:  */
 535:     public function alterSchema($compare, $table = null) {
 536:         if (!is_array($compare)) {
 537:             return false;
 538:         }
 539:         $out = '';
 540:         $colList = array();
 541:         foreach ($compare as $curTable => $types) {
 542:             $indexes = $colList = array();
 543:             if (!$table || $table === $curTable) {
 544:                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
 545:                 foreach ($types as $type => $column) {
 546:                     if (isset($column['indexes'])) {
 547:                         $indexes[$type] = $column['indexes'];
 548:                         unset($column['indexes']);
 549:                     }
 550:                     switch ($type) {
 551:                         case 'add':
 552:                             foreach ($column as $field => $col) {
 553:                                 $col['name'] = $field;
 554:                                 $colList[] = 'ADD COLUMN ' . $this->buildColumn($col);
 555:                             }
 556:                             break;
 557:                         case 'drop':
 558:                             foreach ($column as $field => $col) {
 559:                                 $col['name'] = $field;
 560:                                 $colList[] = 'DROP COLUMN ' . $this->name($field);
 561:                             }
 562:                             break;
 563:                         case 'change':
 564:                             $schema = $this->describe($curTable);
 565:                             foreach ($column as $field => $col) {
 566:                                 if (!isset($col['name'])) {
 567:                                     $col['name'] = $field;
 568:                                 }
 569:                                 $original = $schema[$field];
 570:                                 $fieldName = $this->name($field);
 571: 
 572:                                 $default = isset($col['default']) ? $col['default'] : null;
 573:                                 $nullable = isset($col['null']) ? $col['null'] : null;
 574:                                 $boolToInt = $original['type'] === 'boolean' && $col['type'] === 'integer';
 575:                                 unset($col['default'], $col['null']);
 576:                                 if ($field !== $col['name']) {
 577:                                     $newName = $this->name($col['name']);
 578:                                     $out .= "\tRENAME {$fieldName} TO {$newName};\n";
 579:                                     $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
 580:                                     $fieldName = $newName;
 581:                                 }
 582: 
 583:                                 if ($boolToInt) {
 584:                                     $colList[] = 'ALTER COLUMN ' . $fieldName . '  SET DEFAULT NULL';
 585:                                     $colList[] = 'ALTER COLUMN ' . $fieldName . ' TYPE ' . str_replace(array($fieldName, 'NOT NULL'), '', $this->buildColumn($col)) . ' USING CASE WHEN TRUE THEN 1 ELSE 0 END';
 586:                                 } else {
 587:                                     if ($original['type'] === 'text' && $col['type'] === 'integer') {
 588:                                         $colList[] = 'ALTER COLUMN ' . $fieldName . ' TYPE ' . str_replace(array($fieldName, 'NOT NULL'), '', $this->buildColumn($col)) . " USING cast({$fieldName} as INTEGER)";
 589:                                     } else {
 590:                                         $colList[] = 'ALTER COLUMN ' . $fieldName . ' TYPE ' . str_replace(array($fieldName, 'NOT NULL'), '', $this->buildColumn($col));
 591:                                     }
 592:                                 }
 593: 
 594:                                 if (isset($nullable)) {
 595:                                     $nullable = ($nullable) ? 'DROP NOT NULL' : 'SET NOT NULL';
 596:                                     $colList[] = 'ALTER COLUMN ' . $fieldName . '  ' . $nullable;
 597:                                 }
 598: 
 599:                                 if (isset($default)) {
 600:                                     if (!$boolToInt) {
 601:                                         $colList[] = 'ALTER COLUMN ' . $fieldName . '  SET DEFAULT ' . $this->value($default, $col['type']);
 602:                                     }
 603:                                 } else {
 604:                                     $colList[] = 'ALTER COLUMN ' . $fieldName . '  DROP DEFAULT';
 605:                                 }
 606: 
 607:                             }
 608:                             break;
 609:                     }
 610:                 }
 611:                 if (isset($indexes['drop']['PRIMARY'])) {
 612:                     $colList[] = 'DROP CONSTRAINT ' . $curTable . '_pkey';
 613:                 }
 614:                 if (isset($indexes['add']['PRIMARY'])) {
 615:                     $cols = $indexes['add']['PRIMARY']['column'];
 616:                     if (is_array($cols)) {
 617:                         $cols = implode(', ', $cols);
 618:                     }
 619:                     $colList[] = 'ADD PRIMARY KEY (' . $cols . ')';
 620:                 }
 621: 
 622:                 if (!empty($colList)) {
 623:                     $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
 624:                 } else {
 625:                     $out = '';
 626:                 }
 627:                 $out .= implode(";\n\t", $this->_alterIndexes($curTable, $indexes));
 628:             }
 629:         }
 630:         return $out;
 631:     }
 632: 
 633: /**
 634:  * Generate PostgreSQL index alteration statements for a table.
 635:  *
 636:  * @param string $table Table to alter indexes for
 637:  * @param array $indexes Indexes to add and drop
 638:  * @return array Index alteration statements
 639:  */
 640:     protected function _alterIndexes($table, $indexes) {
 641:         $alter = array();
 642:         if (isset($indexes['drop'])) {
 643:             foreach ($indexes['drop'] as $name => $value) {
 644:                 $out = 'DROP ';
 645:                 if ($name === 'PRIMARY') {
 646:                     continue;
 647:                 } else {
 648:                     $out .= 'INDEX ' . $name;
 649:                 }
 650:                 $alter[] = $out;
 651:             }
 652:         }
 653:         if (isset($indexes['add'])) {
 654:             foreach ($indexes['add'] as $name => $value) {
 655:                 $out = 'CREATE ';
 656:                 if ($name === 'PRIMARY') {
 657:                     continue;
 658:                 } else {
 659:                     if (!empty($value['unique'])) {
 660:                         $out .= 'UNIQUE ';
 661:                     }
 662:                     $out .= 'INDEX ';
 663:                 }
 664:                 if (is_array($value['column'])) {
 665:                     $out .= $name . ' ON ' . $table . ' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
 666:                 } else {
 667:                     $out .= $name . ' ON ' . $table . ' (' . $this->name($value['column']) . ')';
 668:                 }
 669:                 $alter[] = $out;
 670:             }
 671:         }
 672:         return $alter;
 673:     }
 674: 
 675: /**
 676:  * Returns a limit statement in the correct format for the particular database.
 677:  *
 678:  * @param int $limit Limit of results returned
 679:  * @param int $offset Offset from which to start results
 680:  * @return string SQL limit/offset statement
 681:  */
 682:     public function limit($limit, $offset = null) {
 683:         if ($limit) {
 684:             $rt = sprintf(' LIMIT %u', $limit);
 685:             if ($offset) {
 686:                 $rt .= sprintf(' OFFSET %u', $offset);
 687:             }
 688:             return $rt;
 689:         }
 690:         return null;
 691:     }
 692: 
 693: /**
 694:  * Converts database-layer column types to basic types
 695:  *
 696:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
 697:  * @return string Abstract column type (i.e. "string")
 698:  */
 699:     public function column($real) {
 700:         if (is_array($real)) {
 701:             $col = $real['name'];
 702:             if (isset($real['limit'])) {
 703:                 $col .= '(' . $real['limit'] . ')';
 704:             }
 705:             return $col;
 706:         }
 707: 
 708:         $col = str_replace(')', '', $real);
 709: 
 710:         if (strpos($col, '(') !== false) {
 711:             list($col, $limit) = explode('(', $col);
 712:         }
 713: 
 714:         $floats = array(
 715:             'float', 'float4', 'float8', 'double', 'double precision', 'real'
 716:         );
 717: 
 718:         switch (true) {
 719:             case (in_array($col, array('date', 'time', 'inet', 'boolean'))):
 720:                 return $col;
 721:             case (strpos($col, 'timestamp') !== false):
 722:                 return 'datetime';
 723:             case (strpos($col, 'time') === 0):
 724:                 return 'time';
 725:             case ($col === 'bigint'):
 726:                 return 'biginteger';
 727:             case ($col === 'smallint'):
 728:                 return 'smallinteger';
 729:             case (strpos($col, 'int') !== false && $col !== 'interval'):
 730:                 return 'integer';
 731:             case (strpos($col, 'char') !== false):
 732:                 return 'string';
 733:             case (strpos($col, 'uuid') !== false):
 734:                 return 'uuid';
 735:             case (strpos($col, 'text') !== false):
 736:                 return 'text';
 737:             case (strpos($col, 'bytea') !== false):
 738:                 return 'binary';
 739:             case ($col === 'decimal' || $col === 'numeric'):
 740:                 return 'decimal';
 741:             case (in_array($col, $floats)):
 742:                 return 'float';
 743:             default:
 744:                 return 'text';
 745:         }
 746:     }
 747: 
 748: /**
 749:  * Gets the length of a database-native column description, or null if no length
 750:  *
 751:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
 752:  * @return int An integer representing the length of the column
 753:  */
 754:     public function length($real) {
 755:         $col = $real;
 756:         if (strpos($real, '(') !== false) {
 757:             list($col, $limit) = explode('(', $real);
 758:         }
 759:         if ($col === 'uuid') {
 760:             return 36;
 761:         }
 762:         return parent::length($real);
 763:     }
 764: 
 765: /**
 766:  * resultSet method
 767:  *
 768:  * @param PDOStatement $results The results
 769:  * @return void
 770:  */
 771:     public function resultSet($results) {
 772:         $this->map = array();
 773:         $numFields = $results->columnCount();
 774:         $index = 0;
 775:         $j = 0;
 776: 
 777:         while ($j < $numFields) {
 778:             $column = $results->getColumnMeta($j);
 779:             if (strpos($column['name'], '__')) {
 780:                 list($table, $name) = explode('__', $column['name']);
 781:                 $this->map[$index++] = array($table, $name, $column['native_type']);
 782:             } else {
 783:                 $this->map[$index++] = array(0, $column['name'], $column['native_type']);
 784:             }
 785:             $j++;
 786:         }
 787:     }
 788: 
 789: /**
 790:  * Fetches the next row from the current result set
 791:  *
 792:  * @return array
 793:  */
 794:     public function fetchResult() {
 795:         if ($row = $this->_result->fetch(PDO::FETCH_NUM)) {
 796:             $resultRow = array();
 797: 
 798:             foreach ($this->map as $index => $meta) {
 799:                 list($table, $column, $type) = $meta;
 800: 
 801:                 switch ($type) {
 802:                     case 'bool':
 803:                         $resultRow[$table][$column] = $row[$index] === null ? null : $this->boolean($row[$index]);
 804:                         break;
 805:                     case 'binary':
 806:                     case 'bytea':
 807:                         $resultRow[$table][$column] = $row[$index] === null ? null : stream_get_contents($row[$index]);
 808:                         break;
 809:                     default:
 810:                         $resultRow[$table][$column] = $row[$index];
 811:                 }
 812:             }
 813:             return $resultRow;
 814:         }
 815:         $this->_result->closeCursor();
 816:         return false;
 817:     }
 818: 
 819: /**
 820:  * Translates between PHP boolean values and PostgreSQL boolean values
 821:  *
 822:  * @param mixed $data Value to be translated
 823:  * @param bool $quote true to quote a boolean to be used in a query, false to return the boolean value
 824:  * @return bool Converted boolean value
 825:  */
 826:     public function boolean($data, $quote = false) {
 827:         switch (true) {
 828:             case ($data === true || $data === false):
 829:                 $result = $data;
 830:                 break;
 831:             case ($data === 't' || $data === 'f'):
 832:                 $result = ($data === 't');
 833:                 break;
 834:             case ($data === 'true' || $data === 'false'):
 835:                 $result = ($data === 'true');
 836:                 break;
 837:             case ($data === 'TRUE' || $data === 'FALSE'):
 838:                 $result = ($data === 'TRUE');
 839:                 break;
 840:             default:
 841:                 $result = (bool)$data;
 842:         }
 843: 
 844:         if ($quote) {
 845:             return ($result) ? 'TRUE' : 'FALSE';
 846:         }
 847:         return (bool)$result;
 848:     }
 849: 
 850: /**
 851:  * Sets the database encoding
 852:  *
 853:  * @param mixed $enc Database encoding
 854:  * @return bool True on success, false on failure
 855:  */
 856:     public function setEncoding($enc) {
 857:         return $this->_execute('SET NAMES ' . $this->value($enc)) !== false;
 858:     }
 859: 
 860: /**
 861:  * Gets the database encoding
 862:  *
 863:  * @return string The database encoding
 864:  */
 865:     public function getEncoding() {
 866:         $result = $this->_execute('SHOW client_encoding')->fetch();
 867:         if ($result === false) {
 868:             return false;
 869:         }
 870:         return (isset($result['client_encoding'])) ? $result['client_encoding'] : false;
 871:     }
 872: 
 873: /**
 874:  * Generate a Postgres-native column schema string
 875:  *
 876:  * @param array $column An array structured like the following:
 877:  *                      array('name'=>'value', 'type'=>'value'[, options]),
 878:  *                      where options can be 'default', 'length', or 'key'.
 879:  * @return string
 880:  */
 881:     public function buildColumn($column) {
 882:         $col = $this->columns[$column['type']];
 883:         if (!isset($col['length']) && !isset($col['limit'])) {
 884:             unset($column['length']);
 885:         }
 886:         $out = parent::buildColumn($column);
 887: 
 888:         $out = preg_replace(
 889:             '/integer\([0-9]+\)/',
 890:             'integer',
 891:             $out
 892:         );
 893:         $out = preg_replace(
 894:             '/bigint\([0-9]+\)/',
 895:             'bigint',
 896:             $out
 897:         );
 898: 
 899:         $out = str_replace('integer serial', 'serial', $out);
 900:         $out = str_replace('bigint serial', 'bigserial', $out);
 901:         if (strpos($out, 'timestamp DEFAULT')) {
 902:             if (isset($column['null']) && $column['null']) {
 903:                 $out = str_replace('DEFAULT NULL', '', $out);
 904:             } else {
 905:                 $out = str_replace('DEFAULT NOT NULL', '', $out);
 906:             }
 907:         }
 908:         if (strpos($out, 'DEFAULT DEFAULT')) {
 909:             if (isset($column['null']) && $column['null']) {
 910:                 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT NULL', $out);
 911:             } elseif (in_array($column['type'], array('integer', 'float'))) {
 912:                 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT 0', $out);
 913:             } elseif ($column['type'] === 'boolean') {
 914:                 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT FALSE', $out);
 915:             }
 916:         }
 917:         return $out;
 918:     }
 919: 
 920: /**
 921:  * Format indexes for create table
 922:  *
 923:  * @param array $indexes The index to build
 924:  * @param string $table The table name.
 925:  * @return string
 926:  */
 927:     public function buildIndex($indexes, $table = null) {
 928:         $join = array();
 929:         if (!is_array($indexes)) {
 930:             return array();
 931:         }
 932:         foreach ($indexes as $name => $value) {
 933:             if ($name === 'PRIMARY') {
 934:                 $out = 'PRIMARY KEY  (' . $this->name($value['column']) . ')';
 935:             } else {
 936:                 $out = 'CREATE ';
 937:                 if (!empty($value['unique'])) {
 938:                     $out .= 'UNIQUE ';
 939:                 }
 940:                 if (is_array($value['column'])) {
 941:                     $value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column']));
 942:                 } else {
 943:                     $value['column'] = $this->name($value['column']);
 944:                 }
 945:                 $out .= "INDEX {$name} ON {$table}({$value['column']});";
 946:             }
 947:             $join[] = $out;
 948:         }
 949:         return $join;
 950:     }
 951: 
 952: /**
 953:  * {@inheritDoc}
 954:  */
 955:     public function value($data, $column = null, $null = true) {
 956:         $value = parent::value($data, $column, $null);
 957:         if ($column === 'uuid' && is_scalar($data) && $data === '') {
 958:             return 'NULL';
 959:         }
 960:         return $value;
 961:     }
 962: 
 963: /**
 964:  * Overrides DboSource::renderStatement to handle schema generation with Postgres-style indexes
 965:  *
 966:  * @param string $type The query type.
 967:  * @param array $data The array of data to render.
 968:  * @return string
 969:  */
 970:     public function renderStatement($type, $data) {
 971:         switch (strtolower($type)) {
 972:             case 'schema':
 973:                 extract($data);
 974: 
 975:                 foreach ($indexes as $i => $index) {
 976:                     if (preg_match('/PRIMARY KEY/', $index)) {
 977:                         unset($indexes[$i]);
 978:                         $columns[] = $index;
 979:                         break;
 980:                     }
 981:                 }
 982:                 $join = array('columns' => ",\n\t", 'indexes' => "\n");
 983: 
 984:                 foreach (array('columns', 'indexes') as $var) {
 985:                     if (is_array(${$var})) {
 986:                         ${$var} = implode($join[$var], array_filter(${$var}));
 987:                     }
 988:                 }
 989:                 return "CREATE TABLE {$table} (\n\t{$columns}\n);\n{$indexes}";
 990:             default:
 991:                 return parent::renderStatement($type, $data);
 992:         }
 993:     }
 994: 
 995: /**
 996:  * Gets the schema name
 997:  *
 998:  * @return string The schema name
 999:  */
1000:     public function getSchemaName() {
1001:         return $this->config['schema'];
1002:     }
1003: 
1004: /**
1005:  * Check if the server support nested transactions
1006:  *
1007:  * @return bool
1008:  */
1009:     public function nestedTransactionSupported() {
1010:         return $this->useNestedTransactions && version_compare($this->getVersion(), '8.0', '>=');
1011:     }
1012: 
1013: }
1014: