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