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