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