comparison cake/libs/model/datasources/dbo/dbo_postgres.php @ 0:261e66bd5a0c

hg init
author Shoshi TAMAKI <shoshi@cr.ie.u-ryukyu.ac.jp>
date Sun, 24 Jul 2011 21:08:31 +0900
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:261e66bd5a0c
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-2010, 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-2010, 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 (parent::truncate($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 *
496 * @param string matched string
497 * @return string quoted strig
498 * @access private
499 */
500 function __quoteFunctionField($match) {
501 $prepend = '';
502 if (strpos($match[1], 'DISTINCT') !== false) {
503 $prepend = 'DISTINCT ';
504 $match[1] = trim(str_replace('DISTINCT', '', $match[1]));
505 }
506 if (strpos($match[1], '.') === false) {
507 $match[1] = $this->name($match[1]);
508 } else {
509 $parts = explode('.', $match[1]);
510 if (!Set::numeric($parts)) {
511 $match[1] = $this->name($match[1]);
512 }
513 }
514 return '(' . $prepend .$match[1] . ')';
515 }
516
517 /**
518 * Returns an array of the indexes in given datasource name.
519 *
520 * @param string $model Name of model to inspect
521 * @return array Fields in table. Keys are column and unique
522 */
523 function index($model) {
524 $index = array();
525 $table = $this->fullTableName($model, false);
526 if ($table) {
527 $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
528 FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
529 WHERE c.oid = (
530 SELECT c.oid
531 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
532 WHERE c.relname ~ '^(" . $table . ")$'
533 AND pg_catalog.pg_table_is_visible(c.oid)
534 AND n.nspname ~ '^(" . $this->config['schema'] . ")$'
535 )
536 AND c.oid = i.indrelid AND i.indexrelid = c2.oid
537 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname", false);
538 foreach ($indexes as $i => $info) {
539 $key = array_pop($info);
540 if ($key['indisprimary']) {
541 $key['relname'] = 'PRIMARY';
542 }
543 $col = array();
544 preg_match('/\(([^\)]+)\)/', $key['statement'], $indexColumns);
545 $parsedColumn = $indexColumns[1];
546 if (strpos($indexColumns[1], ',') !== false) {
547 $parsedColumn = explode(', ', $indexColumns[1]);
548 }
549 $index[$key['relname']]['unique'] = $key['indisunique'];
550 $index[$key['relname']]['column'] = $parsedColumn;
551 }
552 }
553 return $index;
554 }
555
556 /**
557 * Alter the Schema of a table.
558 *
559 * @param array $compare Results of CakeSchema::compare()
560 * @param string $table name of the table
561 * @access public
562 * @return array
563 */
564 function alterSchema($compare, $table = null) {
565 if (!is_array($compare)) {
566 return false;
567 }
568 $out = '';
569 $colList = array();
570 foreach ($compare as $curTable => $types) {
571 $indexes = $colList = array();
572 if (!$table || $table == $curTable) {
573 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
574 foreach ($types as $type => $column) {
575 if (isset($column['indexes'])) {
576 $indexes[$type] = $column['indexes'];
577 unset($column['indexes']);
578 }
579 switch ($type) {
580 case 'add':
581 foreach ($column as $field => $col) {
582 $col['name'] = $field;
583 $alter = 'ADD COLUMN '.$this->buildColumn($col);
584 if (isset($col['after'])) {
585 $alter .= ' AFTER '. $this->name($col['after']);
586 }
587 $colList[] = $alter;
588 }
589 break;
590 case 'drop':
591 foreach ($column as $field => $col) {
592 $col['name'] = $field;
593 $colList[] = 'DROP COLUMN '.$this->name($field);
594 }
595 break;
596 case 'change':
597 foreach ($column as $field => $col) {
598 if (!isset($col['name'])) {
599 $col['name'] = $field;
600 }
601 $fieldName = $this->name($field);
602
603 $default = isset($col['default']) ? $col['default'] : null;
604 $nullable = isset($col['null']) ? $col['null'] : null;
605 unset($col['default'], $col['null']);
606 $colList[] = 'ALTER COLUMN '. $fieldName .' TYPE ' . str_replace($fieldName, '', $this->buildColumn($col));
607
608 if (isset($nullable)) {
609 $nullable = ($nullable) ? 'DROP NOT NULL' : 'SET NOT NULL';
610 $colList[] = 'ALTER COLUMN '. $fieldName .' ' . $nullable;
611 }
612
613 if (isset($default)) {
614 $colList[] = 'ALTER COLUMN '. $fieldName .' SET DEFAULT ' . $this->value($default, $col['type']);
615 } else {
616 $colList[] = 'ALTER COLUMN '. $fieldName .' DROP DEFAULT';
617 }
618
619 }
620 break;
621 }
622 }
623 if (isset($indexes['drop']['PRIMARY'])) {
624 $colList[] = 'DROP CONSTRAINT ' . $curTable . '_pkey';
625 }
626 if (isset($indexes['add']['PRIMARY'])) {
627 $cols = $indexes['add']['PRIMARY']['column'];
628 if (is_array($cols)) {
629 $cols = implode(', ', $cols);
630 }
631 $colList[] = 'ADD PRIMARY KEY (' . $cols . ')';
632 }
633
634 if (!empty($colList)) {
635 $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
636 } else {
637 $out = '';
638 }
639 $out .= implode(";\n\t", $this->_alterIndexes($curTable, $indexes));
640 }
641 }
642 return $out;
643 }
644
645 /**
646 * Generate PostgreSQL index alteration statements for a table.
647 *
648 * @param string $table Table to alter indexes for
649 * @param array $new Indexes to add and drop
650 * @return array Index alteration statements
651 */
652 function _alterIndexes($table, $indexes) {
653 $alter = array();
654 if (isset($indexes['drop'])) {
655 foreach($indexes['drop'] as $name => $value) {
656 $out = 'DROP ';
657 if ($name == 'PRIMARY') {
658 continue;
659 } else {
660 $out .= 'INDEX ' . $name;
661 }
662 $alter[] = $out;
663 }
664 }
665 if (isset($indexes['add'])) {
666 foreach ($indexes['add'] as $name => $value) {
667 $out = 'CREATE ';
668 if ($name == 'PRIMARY') {
669 continue;
670 } else {
671 if (!empty($value['unique'])) {
672 $out .= 'UNIQUE ';
673 }
674 $out .= 'INDEX ';
675 }
676 if (is_array($value['column'])) {
677 $out .= $name . ' ON ' . $table . ' (' . implode(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
678 } else {
679 $out .= $name . ' ON ' . $table . ' (' . $this->name($value['column']) . ')';
680 }
681 $alter[] = $out;
682 }
683 }
684 return $alter;
685 }
686
687 /**
688 * Returns a limit statement in the correct format for the particular database.
689 *
690 * @param integer $limit Limit of results returned
691 * @param integer $offset Offset from which to start results
692 * @return string SQL limit/offset statement
693 */
694 function limit($limit, $offset = null) {
695 if ($limit) {
696 $rt = '';
697 if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
698 $rt = ' LIMIT';
699 }
700
701 $rt .= ' ' . $limit;
702 if ($offset) {
703 $rt .= ' OFFSET ' . $offset;
704 }
705
706 return $rt;
707 }
708 return null;
709 }
710
711 /**
712 * Converts database-layer column types to basic types
713 *
714 * @param string $real Real database-layer column type (i.e. "varchar(255)")
715 * @return string Abstract column type (i.e. "string")
716 */
717 function column($real) {
718 if (is_array($real)) {
719 $col = $real['name'];
720 if (isset($real['limit'])) {
721 $col .= '(' . $real['limit'] . ')';
722 }
723 return $col;
724 }
725
726 $col = str_replace(')', '', $real);
727 $limit = null;
728
729 if (strpos($col, '(') !== false) {
730 list($col, $limit) = explode('(', $col);
731 }
732
733 $floats = array(
734 'float', 'float4', 'float8', 'double', 'double precision', 'decimal', 'real', 'numeric'
735 );
736
737 switch (true) {
738 case (in_array($col, array('date', 'time', 'inet', 'boolean'))):
739 return $col;
740 case (strpos($col, 'timestamp') !== false):
741 return 'datetime';
742 case (strpos($col, 'time') === 0):
743 return 'time';
744 case (strpos($col, 'int') !== false && $col != 'interval'):
745 return 'integer';
746 case (strpos($col, 'char') !== false || $col == 'uuid'):
747 return 'string';
748 case (strpos($col, 'text') !== false):
749 return 'text';
750 case (strpos($col, 'bytea') !== false):
751 return 'binary';
752 case (in_array($col, $floats)):
753 return 'float';
754 default:
755 return 'text';
756 break;
757 }
758 }
759
760 /**
761 * Gets the length of a database-native column description, or null if no length
762 *
763 * @param string $real Real database-layer column type (i.e. "varchar(255)")
764 * @return int An integer representing the length of the column
765 */
766 function length($real) {
767 $col = str_replace(array(')', 'unsigned'), '', $real);
768 $limit = null;
769
770 if (strpos($col, '(') !== false) {
771 list($col, $limit) = explode('(', $col);
772 }
773 if ($col == 'uuid') {
774 return 36;
775 }
776 if ($limit != null) {
777 return intval($limit);
778 }
779 return null;
780 }
781
782 /**
783 * Enter description here...
784 *
785 * @param unknown_type $results
786 */
787 function resultSet(&$results) {
788 $this->results =& $results;
789 $this->map = array();
790 $num_fields = pg_num_fields($results);
791 $index = 0;
792 $j = 0;
793
794 while ($j < $num_fields) {
795 $columnName = pg_field_name($results, $j);
796
797 if (strpos($columnName, '__')) {
798 $parts = explode('__', $columnName);
799 $this->map[$index++] = array($parts[0], $parts[1]);
800 } else {
801 $this->map[$index++] = array(0, $columnName);
802 }
803 $j++;
804 }
805 }
806
807 /**
808 * Fetches the next row from the current result set
809 *
810 * @return unknown
811 */
812 function fetchResult() {
813 if ($row = pg_fetch_row($this->results)) {
814 $resultRow = array();
815
816 foreach ($row as $index => $field) {
817 list($table, $column) = $this->map[$index];
818 $type = pg_field_type($this->results, $index);
819
820 switch ($type) {
821 case 'bool':
822 $resultRow[$table][$column] = $this->boolean($row[$index], false);
823 break;
824 case 'binary':
825 case 'bytea':
826 $resultRow[$table][$column] = pg_unescape_bytea($row[$index]);
827 break;
828 default:
829 $resultRow[$table][$column] = $row[$index];
830 break;
831 }
832 }
833 return $resultRow;
834 } else {
835 return false;
836 }
837 }
838
839 /**
840 * Translates between PHP boolean values and PostgreSQL boolean values
841 *
842 * @param mixed $data Value to be translated
843 * @param boolean $quote True to quote value, false otherwise
844 * @return mixed Converted boolean value
845 */
846 function boolean($data, $quote = true) {
847 switch (true) {
848 case ($data === true || $data === false):
849 return $data;
850 case ($data === 't' || $data === 'f'):
851 return ($data === 't');
852 case ($data === 'true' || $data === 'false'):
853 return ($data === 'true');
854 case ($data === 'TRUE' || $data === 'FALSE'):
855 return ($data === 'TRUE');
856 default:
857 return (bool)$data;
858 break;
859 }
860 }
861
862 /**
863 * Sets the database encoding
864 *
865 * @param mixed $enc Database encoding
866 * @return boolean True on success, false on failure
867 */
868 function setEncoding($enc) {
869 return pg_set_client_encoding($this->connection, $enc) == 0;
870 }
871
872 /**
873 * Gets the database encoding
874 *
875 * @return string The database encoding
876 */
877 function getEncoding() {
878 return pg_client_encoding($this->connection);
879 }
880
881 /**
882 * Generate a Postgres-native column schema string
883 *
884 * @param array $column An array structured like the following:
885 * array('name'=>'value', 'type'=>'value'[, options]),
886 * where options can be 'default', 'length', or 'key'.
887 * @return string
888 */
889 function buildColumn($column) {
890 $col = $this->columns[$column['type']];
891 if (!isset($col['length']) && !isset($col['limit'])) {
892 unset($column['length']);
893 }
894 $out = preg_replace('/integer\([0-9]+\)/', 'integer', parent::buildColumn($column));
895 $out = str_replace('integer serial', 'serial', $out);
896 if (strpos($out, 'timestamp DEFAULT')) {
897 if (isset($column['null']) && $column['null']) {
898 $out = str_replace('DEFAULT NULL', '', $out);
899 } else {
900 $out = str_replace('DEFAULT NOT NULL', '', $out);
901 }
902 }
903 if (strpos($out, 'DEFAULT DEFAULT')) {
904 if (isset($column['null']) && $column['null']) {
905 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT NULL', $out);
906 } elseif (in_array($column['type'], array('integer', 'float'))) {
907 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT 0', $out);
908 } elseif ($column['type'] == 'boolean') {
909 $out = str_replace('DEFAULT DEFAULT', 'DEFAULT FALSE', $out);
910 }
911 }
912 return $out;
913 }
914
915 /**
916 * Format indexes for create table
917 *
918 * @param array $indexes
919 * @param string $table
920 * @return string
921 */
922 function buildIndex($indexes, $table = null) {
923 $join = array();
924 if (!is_array($indexes)) {
925 return array();
926 }
927 foreach ($indexes as $name => $value) {
928 if ($name == 'PRIMARY') {
929 $out = 'PRIMARY KEY (' . $this->name($value['column']) . ')';
930 } else {
931 $out = 'CREATE ';
932 if (!empty($value['unique'])) {
933 $out .= 'UNIQUE ';
934 }
935 if (is_array($value['column'])) {
936 $value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column']));
937 } else {
938 $value['column'] = $this->name($value['column']);
939 }
940 $out .= "INDEX {$name} ON {$table}({$value['column']});";
941 }
942 $join[] = $out;
943 }
944 return $join;
945 }
946
947 /**
948 * Overrides DboSource::renderStatement to handle schema generation with Postgres-style indexes
949 *
950 * @param string $type
951 * @param array $data
952 * @return string
953 */
954 function renderStatement($type, $data) {
955 switch (strtolower($type)) {
956 case 'schema':
957 extract($data);
958
959 foreach ($indexes as $i => $index) {
960 if (preg_match('/PRIMARY KEY/', $index)) {
961 unset($indexes[$i]);
962 $columns[] = $index;
963 break;
964 }
965 }
966 $join = array('columns' => ",\n\t", 'indexes' => "\n");
967
968 foreach (array('columns', 'indexes') as $var) {
969 if (is_array(${$var})) {
970 ${$var} = implode($join[$var], array_filter(${$var}));
971 }
972 }
973 return "CREATE TABLE {$table} (\n\t{$columns}\n);\n{$indexes}";
974 break;
975 default:
976 return parent::renderStatement($type, $data);
977 break;
978 }
979 }
980 }