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