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