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