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-2011, 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-2011, 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:         $fields = parent::describe($model);
190:         $table = $this->fullTableName($model, false);
191:         $this->_sequenceMap[$table] = array();
192:         $cols = null;
193: 
194:         if ($fields === null) {
195:             $cols = $this->_execute(
196:                 "SELECT DISTINCT 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:             foreach ($cols as $c) {
204:                 $type = $c->type;
205:                 if (!empty($c->oct_length) && $c->char_length === null) {
206:                     if ($c->type == 'character varying') {
207:                         $length = null;
208:                         $type = 'text';
209:                     } else if ($c->type == 'uuid') {
210:                         $length = 36;
211:                     } else {
212:                         $length = intval($c->oct_length);
213:                     }
214:                 } elseif (!empty($c->char_length)) {
215:                     $length = intval($c->char_length);
216:                 } else {
217:                     $length = $this->length($c->type);
218:                 }
219:                 if (empty($length)) {
220:                     $length = null;
221:                 }
222:                 $fields[$c->name] = array(
223:                     'type' => $this->column($type),
224:                     'null' => ($c->null == 'NO' ? false : true),
225:                     'default' => preg_replace(
226:                         "/^'(.*)'$/",
227:                         "$1",
228:                         preg_replace('/::.*/', '', $c->default)
229:                     ),
230:                     'length' => $length
231:                 );
232:                 if ($model instanceof Model) {
233:                     if ($c->name == $model->primaryKey) {
234:                         $fields[$c->name]['key'] = 'primary';
235:                         if ($fields[$c->name]['type'] !== 'string') {
236:                             $fields[$c->name]['length'] = 11;
237:                         }
238:                     }
239:                 }
240:                 if (
241:                     $fields[$c->name]['default'] == 'NULL' ||
242:                     preg_match('/nextval\([\'"]?([\w.]+)/', $c->default, $seq)
243:                 ) {
244:                     $fields[$c->name]['default'] = null;
245:                     if (!empty($seq) && isset($seq[1])) {
246:                         $this->_sequenceMap[$table][$c->default] = $seq[1];
247:                     }
248:                 }
249:                 if ($fields[$c->name]['type'] == 'boolean' && !empty($fields[$c->name]['default'])) {
250:                     $fields[$c->name]['default'] = constant($fields[$c->name]['default']);
251:                 }
252:             }
253:             $this->_cacheDescription($table, $fields);
254:         }
255:         if (isset($model->sequence)) {
256:             $this->_sequenceMap[$table][$model->primaryKey] = $model->sequence;
257:         }
258: 
259:         if ($cols) {
260:             $cols->closeCursor();
261:         }
262:         return $fields;
263:     }
264: 
265: /**
266:  * Returns the ID generated from the previous INSERT operation.
267:  *
268:  * @param string $source Name of the database table
269:  * @param string $field Name of the ID database field. Defaults to "id"
270:  * @return integer
271:  */
272:     public function lastInsertId($source = null, $field = 'id') {
273:         $seq = $this->getSequence($source, $field);
274:         return $this->_connection->lastInsertId($seq);
275:     }
276: 
277: /**
278:  * Gets the associated sequence for the given table/field
279:  *
280:  * @param mixed $table Either a full table name (with prefix) as a string, or a model object
281:  * @param string $field Name of the ID database field. Defaults to "id"
282:  * @return string The associated sequence name from the sequence map, defaults to "{$table}_{$field}_seq"
283:  */
284:     public function getSequence($table, $field = 'id') {
285:         if (is_object($table)) {
286:             $table = $this->fullTableName($table, false);
287:         }
288:         if (isset($this->_sequenceMap[$table]) && isset($this->_sequenceMap[$table][$field])) {
289:             return $this->_sequenceMap[$table][$field];
290:         } else {
291:             return "{$table}_{$field}_seq";
292:         }
293:     }
294: 
295: /**
296:  * Deletes all the records in a table and drops all associated auto-increment sequences
297:  *
298:  * @param mixed $table A string or model class representing the table to be truncated
299:  * @param boolean $reset true for resetting the sequence, false to leave it as is.
300:  *    and if 1, sequences are not modified
301:  * @return boolean  SQL TRUNCATE TABLE statement, false if not applicable.
302:  */
303:     public function truncate($table, $reset = false) {
304:         $fullTable = $this->fullTableName($table, false);
305:         if (!isset($this->_sequenceMap[$fullTable])) {
306:             $cache = $this->cacheSources;
307:             $this->cacheSources = false;
308:             $this->describe($table);
309:             $this->cacheSources = $cache;
310:         }
311:         if ($this->execute('DELETE FROM ' . $this->fullTableName($table))) {
312:             if (isset($this->_sequenceMap[$fullTable]) && $reset != true) {
313:                 foreach ($this->_sequenceMap[$fullTable] as $field => $sequence) {
314:                     $this->_execute("ALTER SEQUENCE \"{$sequence}\" RESTART WITH 1");
315:                 }
316:             }
317:             return true;
318:         }
319:         return false;
320:     }
321: 
322: /**
323:  * Prepares field names to be quoted by parent
324:  *
325:  * @param string $data
326:  * @return string SQL field
327:  */
328:     public function name($data) {
329:         if (is_string($data)) {
330:             $data = str_replace('"__"', '__', $data);
331:         }
332:         return parent::name($data);
333:     }
334: 
335: /**
336:  * Generates the fields list of an SQL query.
337:  *
338:  * @param Model $model
339:  * @param string $alias Alias table name
340:  * @param mixed $fields
341:  * @param boolean $quote
342:  * @return array
343:  */
344:     public function fields($model, $alias = null, $fields = array(), $quote = true) {
345:         if (empty($alias)) {
346:             $alias = $model->alias;
347:         }
348:         $fields = parent::fields($model, $alias, $fields, false);
349: 
350:         if (!$quote) {
351:             return $fields;
352:         }
353:         $count = count($fields);
354: 
355:         if ($count >= 1 && !preg_match('/^\s*COUNT\(\*/', $fields[0])) {
356:             $result = array();
357:             for ($i = 0; $i < $count; $i++) {
358:                 if (!preg_match('/^.+\\(.*\\)/', $fields[$i]) && !preg_match('/\s+AS\s+/', $fields[$i])) {
359:                     if (substr($fields[$i], -1) == '*') {
360:                         if (strpos($fields[$i], '.') !== false && $fields[$i] != $alias . '.*') {
361:                             $build = explode('.', $fields[$i]);
362:                             $AssociatedModel = $model->{$build[0]};
363:                         } else {
364:                             $AssociatedModel = $model;
365:                         }
366: 
367:                         $_fields = $this->fields($AssociatedModel, $AssociatedModel->alias, array_keys($AssociatedModel->schema()));
368:                         $result = array_merge($result, $_fields);
369:                         continue;
370:                     }
371: 
372:                     $prepend = '';
373:                     if (strpos($fields[$i], 'DISTINCT') !== false) {
374:                         $prepend = 'DISTINCT ';
375:                         $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
376:                     }
377: 
378:                     if (strrpos($fields[$i], '.') === false) {
379:                         $fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
380:                     } else {
381:                         $build = explode('.', $fields[$i]);
382:                         $fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
383:                     }
384:                 } else {
385:                     $fields[$i] = preg_replace_callback('/\(([\s\.\w]+)\)/',  array(&$this, '_quoteFunctionField'), $fields[$i]);
386:                 }
387:                 $result[] = $fields[$i];
388:             }
389:             return $result;
390:         }
391:         return $fields;
392:     }
393: 
394: /**
395:  * Auxiliary function to quote matched `(Model.fields)` from a preg_replace_callback call
396:  * Quotes the fields in a function call.
397:  *
398:  * @param string $match matched string
399:  * @return string quoted string
400:  */
401:     protected function _quoteFunctionField($match) {
402:         $prepend = '';
403:         if (strpos($match[1], 'DISTINCT') !== false) {
404:             $prepend = 'DISTINCT ';
405:             $match[1] = trim(str_replace('DISTINCT', '', $match[1]));
406:         }
407:         $constant = preg_match('/^\d+|NULL|FALSE|TRUE$/i', $match[1]);
408: 
409:         if (!$constant && strpos($match[1], '.') === false) {
410:             $match[1] = $this->name($match[1]);
411:         } elseif (!$constant) {
412:             $parts = explode('.', $match[1]);
413:             if (!Set::numeric($parts)) {
414:                 $match[1] = $this->name($match[1]);
415:             }
416:         }
417:         return '(' . $prepend . $match[1] . ')';
418:     }
419: 
420: /**
421:  * Returns an array of the indexes in given datasource name.
422:  *
423:  * @param string $model Name of model to inspect
424:  * @return array Fields in table. Keys are column and unique
425:  */
426:     public function index($model) {
427:         $index = array();
428:         $table = $this->fullTableName($model, false);
429:         if ($table) {
430:             $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
431:             FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
432:             WHERE c.oid  = (
433:                 SELECT c.oid
434:                 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
435:                 WHERE c.relname ~ '^(" . $table . ")$'
436:                     AND pg_catalog.pg_table_is_visible(c.oid)
437:                     AND n.nspname ~ '^(" . $this->config['schema'] . ")$'
438:             )
439:             AND c.oid = i.indrelid AND i.indexrelid = c2.oid
440:             ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname", false);
441:             foreach ($indexes as $i => $info) {
442:                 $key = array_pop($info);
443:                 if ($key['indisprimary']) {
444:                     $key['relname'] = 'PRIMARY';
445:                 }
446:                 $col = array();
447:                 preg_match('/\(([^\)]+)\)/', $key['statement'], $indexColumns);
448:                 $parsedColumn = $indexColumns[1];
449:                 if (strpos($indexColumns[1], ',') !== false) {
450:                     $parsedColumn = explode(', ', $indexColumns[1]);
451:                 }
452:                 $index[$key['relname']]['unique'] = $key['indisunique'];
453:                 $index[$key['relname']]['column'] = $parsedColumn;
454:             }
455:         }
456:         return $index;
457:     }
458: 
459: /**
460:  * Alter the Schema of a table.
461:  *
462:  * @param array $compare Results of CakeSchema::compare()
463:  * @param string $table name of the table
464:  * @return array
465:  */
466:     public function alterSchema($compare, $table = null) {
467:         if (!is_array($compare)) {
468:             return false;
469:         }
470:         $out = '';
471:         $colList = array();
472:         foreach ($compare as $curTable => $types) {
473:             $indexes = $colList = array();
474:             if (!$table || $table == $curTable) {
475:                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
476:                 foreach ($types as $type => $column) {
477:                     if (isset($column['indexes'])) {
478:                         $indexes[$type] = $column['indexes'];
479:                         unset($column['indexes']);
480:                     }
481:                     switch ($type) {
482:                         case 'add':
483:                             foreach ($column as $field => $col) {
484:                                 $col['name'] = $field;
485:                                 $colList[] = 'ADD COLUMN ' . $this->buildColumn($col);
486:                             }
487:                         break;
488:                         case 'drop':
489:                             foreach ($column as $field => $col) {
490:                                 $col['name'] = $field;
491:                                 $colList[] = 'DROP COLUMN ' . $this->name($field);
492:                             }
493:                         break;
494:                         case 'change':
495:                             foreach ($column as $field => $col) {
496:                                 if (!isset($col['name'])) {
497:                                     $col['name'] = $field;
498:                                 }
499:                                 $fieldName = $this->name($field);
500: 
501:                                 $default = isset($col['default']) ? $col['default'] : null;
502:                                 $nullable = isset($col['null']) ? $col['null'] : null;
503:                                 unset($col['default'], $col['null']);
504:                                 $colList[] = 'ALTER COLUMN '. $fieldName .' TYPE ' . str_replace(array($fieldName, 'NOT NULL'), '', $this->buildColumn($col));
505:                                 if (isset($nullable)) {
506:                                     $nullable = ($nullable) ? 'DROP NOT NULL' : 'SET NOT NULL';
507:                                     $colList[] = 'ALTER COLUMN '. $fieldName .'  ' . $nullable;
508:                                 }
509: 
510:                                 if (isset($default)) {
511:                                     $colList[] = 'ALTER COLUMN '. $fieldName .'  SET DEFAULT ' . $this->value($default, $col['type']);
512:                                 } else {
513:                                     $colList[] = 'ALTER COLUMN '. $fieldName .'  DROP DEFAULT';
514:                                 }
515: 
516:                             }
517:                         break;
518:                     }
519:                 }
520:                 if (isset($indexes['drop']['PRIMARY'])) {
521:                     $colList[] = 'DROP CONSTRAINT ' . $curTable . '_pkey';
522:                 }
523:                 if (isset($indexes['add']['PRIMARY'])) {
524:                     $cols = $indexes['add']['PRIMARY']['column'];
525:                     if (is_array($cols)) {
526:                         $cols = implode(', ', $cols);
527:                     }
528:                     $colList[] = 'ADD PRIMARY KEY (' . $cols . ')';
529:                 }
530: 
531:                 if (!empty($colList)) {
532:                     $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
533:                 } else {
534:                     $out = '';
535:                 }
536:                 $out .= implode(";\n\t", $this->_alterIndexes($curTable, $indexes));
537:             }
538:         }
539:         return $out;
540:     }
541: 
542: /**
543:  * Generate PostgreSQL index alteration statements for a table.
544:  *
545:  * @param string $table Table to alter indexes for
546:  * @param array $indexes Indexes to add and drop
547:  * @return array Index alteration statements
548:  */
549:     protected function _alterIndexes($table, $indexes) {
550:         $alter = array();
551:         if (isset($indexes['drop'])) {
552:             foreach ($indexes['drop'] as $name => $value) {
553:                 $out = 'DROP ';
554:                 if ($name == 'PRIMARY') {
555:                     continue;
556:                 } else {
557:                     $out .= 'INDEX ' . $name;
558:                 }
559:                 $alter[] = $out;
560:             }
561:         }
562:         if (isset($indexes['add'])) {
563:             foreach ($indexes['add'] as $name => $value) {
564:                 $out = 'CREATE ';
565:                 if ($name == 'PRIMARY') {
566:                     continue;
567:                 } else {
568:                     if (!empty($value['unique'])) {
569:                         $out .= 'UNIQUE ';
570:                     }
571:                     $out .= 'INDEX ';
572:                 }
573:                 if (is_array($value['column'])) {
574:                     $out .= $name . ' ON ' . $table . ' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
575:                 } else {
576:                     $out .= $name . ' ON ' . $table . ' (' . $this->name($value['column']) . ')';
577:                 }
578:                 $alter[] = $out;
579:             }
580:         }
581:         return $alter;
582:     }
583: 
584: /**
585:  * Returns a limit statement in the correct format for the particular database.
586:  *
587:  * @param integer $limit Limit of results returned
588:  * @param integer $offset Offset from which to start results
589:  * @return string SQL limit/offset statement
590:  */
591:     public function limit($limit, $offset = null) {
592:         if ($limit) {
593:             $rt = '';
594:             if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
595:                 $rt = ' LIMIT';
596:             }
597: 
598:             $rt .= ' ' . $limit;
599:             if ($offset) {
600:                 $rt .= ' OFFSET ' . $offset;
601:             }
602: 
603:             return $rt;
604:         }
605:         return null;
606:     }
607: 
608: /**
609:  * Converts database-layer column types to basic types
610:  *
611:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
612:  * @return string Abstract column type (i.e. "string")
613:  */
614:     public function column($real) {
615:         if (is_array($real)) {
616:             $col = $real['name'];
617:             if (isset($real['limit'])) {
618:                 $col .= '(' . $real['limit'] . ')';
619:             }
620:             return $col;
621:         }
622: 
623:         $col = str_replace(')', '', $real);
624:         $limit = null;
625: 
626:         if (strpos($col, '(') !== false) {
627:             list($col, $limit) = explode('(', $col);
628:         }
629: 
630:         $floats = array(
631:             'float', 'float4', 'float8', 'double', 'double precision', 'decimal', 'real', 'numeric'
632:         );
633: 
634:         switch (true) {
635:             case (in_array($col, array('date', 'time', 'inet', 'boolean'))):
636:                 return $col;
637:             case (strpos($col, 'timestamp') !== false):
638:                 return 'datetime';
639:             case (strpos($col, 'time') === 0):
640:                 return 'time';
641:             case (strpos($col, 'int') !== false && $col != 'interval'):
642:                 return 'integer';
643:             case (strpos($col, 'char') !== false || $col == 'uuid'):
644:                 return 'string';
645:             case (strpos($col, 'text') !== false):
646:                 return 'text';
647:             case (strpos($col, 'bytea') !== false):
648:                 return 'binary';
649:             case (in_array($col, $floats)):
650:                 return 'float';
651:             default:
652:                 return 'text';
653:             break;
654:         }
655:     }
656: 
657: /**
658:  * Gets the length of a database-native column description, or null if no length
659:  *
660:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
661:  * @return integer An integer representing the length of the column
662:  */
663:     public function length($real) {
664:         $col = str_replace(array(')', 'unsigned'), '', $real);
665:         $limit = null;
666: 
667:         if (strpos($col, '(') !== false) {
668:             list($col, $limit) = explode('(', $col);
669:         }
670:         if ($col == 'uuid') {
671:             return 36;
672:         }
673:         if ($limit != null) {
674:             return intval($limit);
675:         }
676:         return null;
677:     }
678: 
679: /**
680:  * resultSet method
681:  *
682:  * @param array $results
683:  * @return void
684:  */
685:     public function resultSet(&$results) {
686:         $this->map = array();
687:         $numFields = $results->columnCount();
688:         $index = 0;
689:         $j = 0;
690: 
691:         while ($j < $numFields) {
692:             $column = $results->getColumnMeta($j);
693:             if (strpos($column['name'], '__')) {
694:                 list($table, $name) = explode('__', $column['name']);
695:                 $this->map[$index++] = array($table, $name, $column['native_type']);
696:             } else {
697:                 $this->map[$index++] = array(0, $column['name'], $column['native_type']);
698:             }
699:             $j++;
700:         }
701:     }
702: 
703: /**
704:  * Fetches the next row from the current result set
705:  *
706:  * @return array
707:  */
708:     public function fetchResult() {
709:         if ($row = $this->_result->fetch(PDO::FETCH_NUM)) {
710:             $resultRow = array();
711: 
712:             foreach ($this->map as $index => $meta) {
713:                 list($table, $column, $type) = $meta;
714: 
715:                 switch ($type) {
716:                     case 'bool':
717:                         $resultRow[$table][$column] = is_null($row[$index]) ? null : $this->boolean($row[$index]);
718:                     break;
719:                     case 'binary':
720:                     case 'bytea':
721:                         $resultRow[$table][$column] = is_null($row[$index]) ? null : stream_get_contents($row[$index]);
722:                     break;
723:                     default:
724:                         $resultRow[$table][$column] = $row[$index];
725:                     break;
726:                 }
727:             }
728:             return $resultRow;
729:         } else {
730:             $this->_result->closeCursor();
731:             return false;
732:         }
733:     }
734: 
735: /**
736:  * Translates between PHP boolean values and PostgreSQL boolean values
737:  *
738:  * @param mixed $data Value to be translated
739:  * @param boolean $quote true to quote a boolean to be used in a query, false to return the boolean value
740:  * @return boolean Converted boolean value
741:  */
742:     public function boolean($data, $quote = false) {
743:         switch (true) {
744:             case ($data === true || $data === false):
745:                 $result = $data;
746:                 break;
747:             case ($data === 't' || $data === 'f'):
748:                 $result = ($data === 't');
749:                 break;
750:             case ($data === 'true' || $data === 'false'):
751:                 $result = ($data === 'true');
752:                 break;
753:             case ($data === 'TRUE' || $data === 'FALSE'):
754:                 $result = ($data === 'TRUE');
755:                 break;
756:             default:
757:                 $result = (bool) $data;
758:             break;
759:         }
760: 
761:         if ($quote) {
762:             return ($result) ? 'TRUE' : 'FALSE';
763:         }
764:         return (bool) $result;
765:     }
766: 
767: /**
768:  * Sets the database encoding
769:  *
770:  * @param mixed $enc Database encoding
771:  * @return boolean True on success, false on failure
772:  */
773:     public function setEncoding($enc) {
774:         return $this->_execute('SET NAMES ' . $this->value($enc)) !== false;
775:     }
776: 
777: /**
778:  * Gets the database encoding
779:  *
780:  * @return string The database encoding
781:  */
782:     public function getEncoding() {
783:         $result = $this->_execute('SHOW client_encoding')->fetch();
784:         if ($result === false) {
785:             return false;
786:         }
787:         return (isset($result['client_encoding'])) ? $result['client_encoding'] : false;
788:     }
789: 
790: /**
791:  * Generate a Postgres-native column schema string
792:  *
793:  * @param array $column An array structured like the following:
794:  *                      array('name'=>'value', 'type'=>'value'[, options]),
795:  *                      where options can be 'default', 'length', or 'key'.
796:  * @return string
797:  */
798:     public function buildColumn($column) {
799:         $col = $this->columns[$column['type']];
800:         if (!isset($col['length']) && !isset($col['limit'])) {
801:             unset($column['length']);
802:         }
803:         $out = preg_replace('/integer\([0-9]+\)/', 'integer', parent::buildColumn($column));
804:         $out = str_replace('integer serial', 'serial', $out);
805:         if (strpos($out, 'timestamp DEFAULT')) {
806:             if (isset($column['null']) && $column['null']) {
807:                 $out = str_replace('DEFAULT NULL', '', $out);
808:             } else {
809:                 $out = str_replace('DEFAULT NOT NULL', '', $out);
810:             }
811:         }
812:         if (strpos($out, 'DEFAULT DEFAULT')) {
813:             if (isset($column['null']) && $column['null']) {
814:                 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT NULL', $out);
815:             } elseif (in_array($column['type'], array('integer', 'float'))) {
816:                 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT 0', $out);
817:             } elseif ($column['type'] == 'boolean') {
818:                 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT FALSE', $out);
819:             }
820:         }
821:         return $out;
822:     }
823: 
824: /**
825:  * Format indexes for create table
826:  *
827:  * @param array $indexes
828:  * @param string $table
829:  * @return string
830:  */
831:     public function buildIndex($indexes, $table = null) {
832:         $join = array();
833:         if (!is_array($indexes)) {
834:             return array();
835:         }
836:         foreach ($indexes as $name => $value) {
837:             if ($name == 'PRIMARY') {
838:                 $out = 'PRIMARY KEY  (' . $this->name($value['column']) . ')';
839:             } else {
840:                 $out = 'CREATE ';
841:                 if (!empty($value['unique'])) {
842:                     $out .= 'UNIQUE ';
843:                 }
844:                 if (is_array($value['column'])) {
845:                     $value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column']));
846:                 } else {
847:                     $value['column'] = $this->name($value['column']);
848:                 }
849:                 $out .= "INDEX {$name} ON {$table}({$value['column']});";
850:             }
851:             $join[] = $out;
852:         }
853:         return $join;
854:     }
855: 
856: /**
857:  * Overrides DboSource::renderStatement to handle schema generation with Postgres-style indexes
858:  *
859:  * @param string $type
860:  * @param array $data
861:  * @return string
862:  */
863:     public function renderStatement($type, $data) {
864:         switch (strtolower($type)) {
865:             case 'schema':
866:                 extract($data);
867: 
868:                 foreach ($indexes as $i => $index) {
869:                     if (preg_match('/PRIMARY KEY/', $index)) {
870:                         unset($indexes[$i]);
871:                         $columns[] = $index;
872:                         break;
873:                     }
874:                 }
875:                 $join = array('columns' => ",\n\t", 'indexes' => "\n");
876: 
877:                 foreach (array('columns', 'indexes') as $var) {
878:                     if (is_array(${$var})) {
879:                         ${$var} = implode($join[$var], array_filter(${$var}));
880:                     }
881:                 }
882:                 return "CREATE TABLE {$table} (\n\t{$columns}\n);\n{$indexes}";
883:             break;
884:             default:
885:                 return parent::renderStatement($type, $data);
886:             break;
887:         }
888:     }
889: }
890: