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