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