Mercurial > hg > Members > shoshi > webvirt
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 } |