1: <?php
2: /**
3: * Class for PostgreSQL-specific managing of database schemes and handling of
4: * SQL dialects and quoting.
5: *
6: * Copyright 2007 Maintainable Software, LLC
7: * Copyright 2008-2012 Horde LLC (http://www.horde.org/)
8: *
9: * @author Mike Naberezny <mike@maintainable.com>
10: * @author Derek DeVries <derek@maintainable.com>
11: * @author Chuck Hagenbuch <chuck@horde.org>
12: * @author Jan Schneider <jan@horde.org>
13: * @license http://www.horde.org/licenses/bsd
14: * @category Horde
15: * @package Db
16: * @subpackage Adapter
17: */
18:
19: /**
20: * @author Mike Naberezny <mike@maintainable.com>
21: * @author Derek DeVries <derek@maintainable.com>
22: * @author Chuck Hagenbuch <chuck@horde.org>
23: * @author Jan Schneider <jan@horde.org>
24: * @license http://www.horde.org/licenses/bsd
25: * @category Horde
26: * @package Db
27: * @subpackage Adapter
28: */
29: class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Base_Schema
30: {
31: /**
32: * The active schema search path.
33: *
34: * @var string
35: */
36: protected $_schemaSearchPath = '';
37:
38: /**
39: * Cached version.
40: *
41: * @var integer
42: */
43: protected $_version;
44:
45:
46: /*##########################################################################
47: # Object factories
48: ##########################################################################*/
49:
50: /**
51: * Factory for Column objects.
52: *
53: * @param string $name The column's name, such as "supplier_id" in
54: * "supplier_id int(11)".
55: * @param string $default The type-casted default value, such as "new" in
56: * "sales_stage varchar(20) default 'new'".
57: * @param string $sqlType Used to extract the column's type, length and
58: * signed status, if necessary. For example
59: * "varchar" and "60" in "company_name varchar(60)"
60: * or "unsigned => true" in "int(10) UNSIGNED".
61: * @param boolean $null Whether this column allows NULL values.
62: *
63: * @return Horde_Db_Adapter_Postgresql_Column A column object.
64: */
65: public function makeColumn($name, $default, $sqlType = null, $null = true)
66: {
67: return new Horde_Db_Adapter_Postgresql_Column($name, $default, $sqlType, $null);
68: }
69:
70:
71: /*##########################################################################
72: # Quoting
73: ##########################################################################*/
74:
75: /**
76: * Quotes the column value to help prevent SQL injection attacks.
77: *
78: * This method makes educated guesses on the scalar type based on the
79: * passed value. Make sure to correctly cast the value and/or pass the
80: * $column parameter to get the best results.
81: *
82: * @param mixed $value The scalar value to quote, a Horde_Db_Value,
83: * Horde_Date, or DateTime instance, or an object
84: * implementing quotedId().
85: * @param object $column An object implementing getType().
86: *
87: * @return string The correctly quoted value.
88: */
89: public function quote($value, $column = null)
90: {
91: if (!$column) {
92: return parent::quote($value, $column);
93: }
94:
95: if (is_string($value) &&
96: $column->getType() == 'binary') {
97: return $this->quoteBinary($value);
98: }
99: if (is_string($value) && $column->getSqlType() == 'xml') {
100: return "xml '" . $this->quoteString($value) . "'";
101: }
102: if (is_numeric($value) && $column->getSqlType() == 'money') {
103: // Not truly string input, so doesn't require (or allow) escape
104: // string syntax.
105: return "'" . $value . "'";
106: }
107: if (is_string($value) && substr($column->getSqlType(), 0, 3) == 'bit') {
108: if (preg_match('/^[01]*$/', $value)) {
109: // Bit-string notation
110: return "B'" . $value . "'";
111: }
112: if (preg_match('/^[0-9A-F]*$/i')) {
113: // Hexadecimal notation
114: return "X'" . $value . "'";
115: }
116: }
117:
118: return parent::quote($value, $column);
119: }
120:
121: /**
122: * Returns a quoted form of the column name.
123: *
124: * @param string $name A column name.
125: *
126: * @return string The quoted column name.
127: */
128: public function quoteColumnName($name)
129: {
130: return '"' . str_replace('"', '""', $name) . '"';
131: }
132:
133: /**
134: * Returns a quoted sequence name.
135: *
136: * PostgreSQL specific method.
137: *
138: * @param string $name A sequence name.
139: *
140: * @return string The quoted sequence name.
141: */
142: public function quoteSequenceName($name)
143: {
144: return '\'' . str_replace('"', '""', $name) . '\'';
145: }
146:
147: /**
148: * Returns a quoted binary value.
149: *
150: * @param mixed A binary value.
151: *
152: * @return string The quoted binary value.
153: */
154: public function quoteBinary($value)
155: {
156: if ($this->postgresqlVersion() >= 90000) {
157: return "E'\\\\x" . bin2hex($value) . "'";
158: }
159:
160: /* MUST escape zero octet(0), single quote (39), and backslash (92).
161: * MAY escape non-printable octets, but they are required in some
162: * instances so it is best to escape all. */
163: return "E'" . preg_replace_callback("/[\\x00-\\x1f\\x27\\x5c\\x7f-\\xff]/", array($this, '_quoteBinaryCallback'), $value) . "'";
164: }
165:
166: /**
167: * Callback function for quoteBinary().
168: *
169: * @param array $matches Matches from preg_replace().
170: *
171: * @return string Escaped/encoded binary value.
172: */
173: protected function _quoteBinaryCallback($matches)
174: {
175: return sprintf('\\\\%03.o', ord($matches[0]));
176: }
177:
178: /*##########################################################################
179: # Schema Statements
180: ##########################################################################*/
181:
182: /**
183: * Returns a hash of mappings from the abstract data types to the native
184: * database types.
185: *
186: * See TableDefinition::column() for details on the recognized abstract
187: * data types.
188: *
189: * @see TableDefinition::column()
190: *
191: * @return array A database type map.
192: */
193: public function nativeDatabaseTypes()
194: {
195: return array(
196: 'autoincrementKey' => 'serial primary key',
197: 'string' => array('name' => 'character varying',
198: 'limit' => 255),
199: 'text' => array('name' => 'text',
200: 'limit' => null),
201: 'mediumtext' => array('name' => 'text',
202: 'limit' => null),
203: 'longtext' => array('name' => 'text',
204: 'limit' => null),
205: 'integer' => array('name' => 'integer',
206: 'limit' => null),
207: 'float' => array('name' => 'float',
208: 'limit' => null),
209: 'decimal' => array('name' => 'decimal',
210: 'limit' => null),
211: 'datetime' => array('name' => 'timestamp',
212: 'limit' => null),
213: 'timestamp' => array('name' => 'timestamp',
214: 'limit' => null),
215: 'time' => array('name' => 'time',
216: 'limit' => null),
217: 'date' => array('name' => 'date',
218: 'limit' => null),
219: 'binary' => array('name' => 'bytea',
220: 'limit' => null),
221: 'boolean' => array('name' => 'boolean',
222: 'limit' => null),
223: );
224: }
225:
226: /**
227: * Returns the maximum length a table alias can have.
228: *
229: * Returns the configured supported identifier length supported by
230: * PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
231: *
232: * @return integer The maximum table alias length.
233: */
234: public function tableAliasLength()
235: {
236: if ($this->postgresqlVersion() >= 80000) {
237: return (int)$this->selectValue('SHOW max_identifier_length');
238: }
239: return 63;
240: }
241:
242: /**
243: * Returns a list of all tables in the schema search path.
244: *
245: * @return array A table list.
246: */
247: public function tables()
248: {
249: $schemas = array();
250: foreach (explode(',', $this->getSchemaSearchPath()) as $p) {
251: $schemas[] = $this->quote($p);
252: }
253:
254: return $this->selectValues('SELECT tablename FROM pg_tables WHERE schemaname IN (' . implode(',', $schemas) . ')');
255: }
256:
257: /**
258: * Returns a table's primary key.
259: *
260: * @param string $tableName A table name.
261: * @param string $name (can be removed?)
262: *
263: * @return Horde_Db_Adapter_Base_Index The primary key index object.
264: */
265: public function primaryKey($tableName, $name = null)
266: {
267: $sql = '
268: SELECT column_name
269: FROM information_schema.constraint_column_usage
270: WHERE table_name = ?
271: AND constraint_name = (SELECT constraint_name
272: FROM information_schema.table_constraints
273: WHERE table_name = ?
274: AND constraint_type = ?)';
275: $pk = $this->selectValues($sql,
276: array($tableName, $tableName, 'PRIMARY KEY'),
277: $name);
278:
279: return $this->makeIndex($tableName, 'PRIMARY', true, true, $pk);
280: }
281:
282: /**
283: * Returns a list of tables indexes.
284: *
285: * @param string $tableName A table name.
286: * @param string $name (can be removed?)
287: *
288: * @return array A list of Horde_Db_Adapter_Base_Index objects.
289: */
290: public function indexes($tableName, $name = null)
291: {
292: $indexes = @unserialize($this->_cache->get("tables/indexes/$tableName"));
293:
294: if (!$indexes) {
295: $schemas = array();
296: foreach (explode(',', $this->getSchemaSearchPath()) as $p) {
297: $schemas[] = $this->quote($p);
298: }
299:
300: $sql = "
301: SELECT distinct i.relname, d.indisunique, a.attname
302: FROM pg_class t, pg_class i, pg_index d, pg_attribute a
303: WHERE i.relkind = 'i'
304: AND d.indexrelid = i.oid
305: AND d.indisprimary = 'f'
306: AND t.oid = d.indrelid
307: AND t.relname = " . $this->quote($tableName) . "
308: AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (" . implode(',', $schemas) . ") )
309: AND a.attrelid = t.oid
310: AND (d.indkey[0] = a.attnum OR d.indkey[1] = a.attnum
311: OR d.indkey[2] = a.attnum OR d.indkey[3] = a.attnum
312: OR d.indkey[4] = a.attnum OR d.indkey[5] = a.attnum
313: OR d.indkey[6] = a.attnum OR d.indkey[7] = a.attnum
314: OR d.indkey[8] = a.attnum OR d.indkey[9] = a.attnum)
315: ORDER BY i.relname";
316:
317: $result = $this->select($sql, $name);
318:
319: $currentIndex = null;
320: $indexes = array();
321:
322: foreach ($result as $row) {
323: if ($currentIndex != $row['relname']) {
324: $currentIndex = $row['relname'];
325: $indexes[] = $this->makeIndex(
326: $tableName, $row['relname'], false, $row['indisunique'] == 't', array());
327: }
328: $indexes[count($indexes) - 1]->columns[] = $row['attname'];
329: }
330:
331: $this->_cache->set("tables/indexes/$tableName", serialize($indexes));
332: }
333:
334: return $indexes;
335: }
336:
337: /**
338: * Returns a list of table columns.
339: *
340: * @param string $tableName A table name.
341: * @param string $name (can be removed?)
342: *
343: * @return array A list of Horde_Db_Adapter_Base_Column objects.
344: */
345: public function columns($tableName, $name = null)
346: {
347: $rows = @unserialize($this->_cache->get("tables/columns/$tableName"));
348:
349: if (!$rows) {
350: $rows = $this->_columnDefinitions($tableName, $name);
351:
352: $this->_cache->set("tables/columns/$tableName", serialize($rows));
353: }
354:
355: // Create columns from rows.
356: $columns = array();
357: foreach ($rows as $row) {
358: $columns[$row['attname']] = $this->makeColumn(
359: $row['attname'], $row['adsrc'], $row['format_type'], !(boolean)$row['attnotnull']);
360: }
361: return $columns;
362: }
363:
364: /**
365: * Returns the list of a table's column names, data types, and default
366: * values.
367: *
368: * The underlying query is roughly:
369: * SELECT column.name, column.type, default.value
370: * FROM column LEFT JOIN default
371: * ON column.table_id = default.table_id
372: * AND column.num = default.column_num
373: * WHERE column.table_id = get_table_id('table_name')
374: * AND column.num > 0
375: * AND NOT column.is_dropped
376: * ORDER BY column.num
377: *
378: * If the table name is not prefixed with a schema, the database will take
379: * the first match from the schema search path.
380: *
381: * Query implementation notes:
382: * - format_type includes the column size constraint, e.g. varchar(50)
383: * - ::regclass is a function that gives the id for a table name
384: */
385: protected function _columnDefinitions($tableName, $name = null)
386: {
387: /* @todo See if we can get this from information_schema instead */
388: return $this->selectAll('
389: SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
390: FROM pg_attribute a LEFT JOIN pg_attrdef d
391: ON a.attrelid = d.adrelid AND a.attnum = d.adnum
392: WHERE a.attrelid = ' . $this->quote($tableName) . '::regclass
393: AND a.attnum > 0 AND NOT a.attisdropped
394: ORDER BY a.attnum', $name);
395: }
396:
397: /**
398: * Renames a table.
399: *
400: * @param string $name A table name.
401: * @param string $newName The new table name.
402: */
403: public function renameTable($name, $newName)
404: {
405: $this->_clearTableCache($name);
406:
407: return $this->execute(sprintf('ALTER TABLE %s RENAME TO %s', $this->quoteTableName($name), $this->quoteTableName($newName)));
408: }
409:
410: /**
411: * Adds a new column to a table.
412: *
413: * @param string $tableName A table name.
414: * @param string $columnName A column name.
415: * @param string $type A data type.
416: * @param array $options Column options. See
417: * Horde_Db_Adapter_Base_TableDefinition#column()
418: * for details.
419: */
420: public function addColumn($tableName, $columnName, $type,
421: $options = array())
422: {
423: $this->_clearTableCache($tableName);
424:
425: $options = array_merge(
426: array('autoincrement' => null,
427: 'limit' => null,
428: 'precision' => null,
429: 'scale' => null),
430: $options);
431:
432: $sqltype = $this->typeToSql($type, $options['limit'],
433: $options['precision'], $options['scale']);
434:
435: /* Convert to SERIAL type if needed. */
436: if ($options['autoincrement']) {
437: switch ($sqltype) {
438: case 'bigint':
439: $sqltype = 'BIGSERIAL';
440: break;
441:
442: case 'integer':
443: default:
444: $sqltype = 'SERIAL';
445: break;
446: }
447: }
448:
449: // Add the column.
450: $sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s',
451: $this->quoteTableName($tableName),
452: $this->quoteColumnName($columnName),
453: $sqltype);
454: $this->execute($sql);
455:
456: if (array_key_exists('default', $options)) {
457: $this->changeColumnDefault($tableName, $columnName,
458: $options['default']);
459: }
460:
461: if (isset($options['null']) && $options['null'] === false) {
462: $this->changeColumnNull(
463: $tableName, $columnName, false,
464: isset($options['default']) ? $options['default'] : null);
465: }
466: }
467:
468: /**
469: * Changes an existing column's definition.
470: *
471: * @param string $tableName A table name.
472: * @param string $columnName A column name.
473: * @param string $type A data type.
474: * @param array $options Column options. See
475: * Horde_Db_Adapter_Base_TableDefinition#column()
476: * for details.
477: */
478: public function changeColumn($tableName, $columnName, $type,
479: $options = array())
480: {
481: $this->_clearTableCache($tableName);
482:
483: $options = array_merge(
484: array('autoincrement' => null,
485: 'limit' => null,
486: 'precision' => null,
487: 'scale' => null),
488: $options);
489:
490: $quotedTableName = $this->quoteTableName($tableName);
491:
492: $primaryKey = $type == 'autoincrementKey';
493: if ($primaryKey) {
494: $type = 'integer';
495: $options['autoincrement'] = true;
496: $options['limit'] = $options['precision'] = $options['scale'] = null;
497: try {
498: $this->removePrimaryKey($tableName);
499: } catch (Horde_Db_Exception $e) {
500: }
501: }
502:
503: $sql = sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
504: $quotedTableName,
505: $this->quoteColumnName($columnName),
506: $this->typeToSql($type,
507: $options['limit'],
508: $options['precision'],
509: $options['scale']));
510: try {
511: $this->execute($sql);
512: } catch (Horde_Db_Exception $e) {
513: // This is PostgreSQL 7.x, or the old type could not be coerced to
514: // the new type, so we have to use a more arcane way of doing it.
515: try {
516: // Booleans can't always be cast to other data types; do extra
517: // work to handle them.
518: $oldType = null;
519: $columns = $this->columns($tableName);
520: foreach ($this->columns($tableName) as $column) {
521: if ($column->getName() == $columnName) {
522: $oldType = $column->getType();
523: break;
524: }
525: }
526: if ($oldType === null) {
527: throw new Horde_Db_Exception("$tableName does not have a column '$columnName'");
528: }
529:
530: $this->beginDbTransaction();
531:
532: $tmpColumnName = $columnName.'_change_tmp';
533: $this->addColumn($tableName, $tmpColumnName, $type, $options);
534:
535: if ($oldType == 'boolean') {
536: $sql = sprintf('UPDATE %s SET %s = CAST(CASE WHEN %s IS TRUE THEN 1 ELSE 0 END AS %s)',
537: $quotedTableName,
538: $this->quoteColumnName($tmpColumnName),
539: $this->quoteColumnName($columnName),
540: $this->typeToSql($type,
541: $options['limit'],
542: $options['precision'],
543: $options['scale']));
544: } else {
545: $sql = sprintf('UPDATE %s SET %s = CAST(%s AS %s)',
546: $quotedTableName,
547: $this->quoteColumnName($tmpColumnName),
548: $this->quoteColumnName($columnName),
549: $this->typeToSql($type,
550: $options['limit'],
551: $options['precision'],
552: $options['scale']));
553: }
554: $this->execute($sql);
555: $this->removeColumn($tableName, $columnName);
556: $this->renameColumn($tableName, $tmpColumnName, $columnName);
557:
558: $this->commitDbTransaction();
559: } catch (Horde_Db_Exception $e) {
560: $this->rollbackDbTransaction();
561: throw $e;
562: }
563: }
564:
565: if ($options['autoincrement']) {
566: $seq_name = $this->defaultSequenceName($tableName, $columnName);
567: try {
568: $this->execute('DROP SEQUENCE ' . $seq_name . ' CASCADE');
569: } catch (Horde_Db_Exception $e) {}
570: $this->execute('CREATE SEQUENCE ' . $seq_name);
571: $this->resetPkSequence($tableName, $columnName, $seq_name);
572:
573: /* Can't use changeColumnDefault() since it quotes the
574: * default value (NEXTVAL is a postgres keyword, not a text
575: * value). */
576: $this->_clearTableCache($tableName);
577: $sql = sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL(%s)',
578: $this->quoteTableName($tableName),
579: $this->quoteColumnName($columnName),
580: $this->quoteSequenceName($seq_name));
581: $this->execute($sql);
582: if ($this->postgresqlVersion() >= 80200) {
583: $sql = sprintf('ALTER SEQUENCE %s OWNED BY %s.%s',
584: $seq_name,
585: $this->quoteTableName($tableName),
586: $this->quoteColumnName($columnName));
587: $this->execute($sql);
588: }
589: } elseif (array_key_exists('default', $options)) {
590: $this->changeColumnDefault($tableName, $columnName,
591: $options['default']);
592: }
593:
594: if ($primaryKey) {
595: $this->addPrimaryKey($tableName, $columnName);
596: }
597:
598: if (array_key_exists('null', $options)) {
599: $this->changeColumnNull(
600: $tableName, $columnName, $options['null'],
601: isset($options['default']) ? $options['default'] : null);
602: }
603: }
604:
605: /**
606: * Sets a new default value for a column.
607: *
608: * If you want to set the default value to NULL, you are out of luck. You
609: * need to execute the apppropriate SQL statement yourself.
610: *
611: * @param string $tableName A table name.
612: * @param string $columnName A column name.
613: * @param mixed $default The new default value.
614: */
615: public function changeColumnDefault($tableName, $columnName, $default)
616: {
617: $this->_clearTableCache($tableName);
618: $sql = sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
619: $this->quoteTableName($tableName),
620: $this->quoteColumnName($columnName),
621: $this->quote($default));
622: return $this->execute($sql);
623: }
624:
625: /**
626: * Sets whether a column allows NULL values.
627: *
628: * @param string $tableName A table name.
629: * @param string $columnName A column name.
630: * @param boolean $null Whether NULL values are allowed.
631: * @param mixed $default The new default value.
632: */
633: public function changeColumnNull($tableName, $columnName, $null,
634: $default = null)
635: {
636: $this->_clearTableCache($tableName);
637: if (!$null && !is_null($default)) {
638: $sql = sprintf('UPDATE %s SET %s = %s WHERE %s IS NULL',
639: $this->quoteTableName($tableName),
640: $this->quoteColumnName($columnName),
641: $this->quote($default),
642: $this->quoteColumnName($columnName));
643: $this->execute($sql);
644: }
645: $sql = sprintf('ALTER TABLE %s ALTER %s %s NOT NULL',
646: $this->quoteTableName($tableName),
647: $this->quoteColumnName($columnName),
648: $null ? 'DROP' : 'SET');
649: return $this->execute($sql);
650: }
651:
652: /**
653: * Renames a column.
654: *
655: * @param string $tableName A table name.
656: * @param string $columnName A column name.
657: * @param string $newColumnName The new column name.
658: */
659: public function renameColumn($tableName, $columnName, $newColumnName)
660: {
661: $this->_clearTableCache($tableName);
662: $sql = sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
663: $this->quoteTableName($tableName),
664: $this->quoteColumnName($columnName),
665: $this->quoteColumnName($newColumnName));
666: return $this->execute($sql);
667: }
668:
669: /**
670: * Removes a primary key from a table.
671: *
672: * @since Horde_Db 1.1.0
673: *
674: * @param string $tableName A table name.
675: *
676: * @throws Horde_Db_Exception
677: */
678: public function removePrimaryKey($tableName)
679: {
680: $this->_clearTableCache($tableName);
681: $keyName = $this->selectValue(
682: 'SELECT constraint_name
683: FROM information_schema.table_constraints
684: WHERE table_name = ?
685: AND constraint_type = ?',
686: array($tableName, 'PRIMARY KEY'));
687: if ($keyName) {
688: $sql = sprintf('ALTER TABLE %s DROP CONSTRAINT %s CASCADE',
689: $this->quoteTableName($tableName),
690: $this->quoteColumnName($keyName));
691: return $this->execute($sql);
692: }
693: }
694:
695: /**
696: * Removes an index from a table.
697: *
698: * See parent class for examples.
699: *
700: * @param string $tableName A table name.
701: * @param string|array $options Either a column name or index options:
702: * - name: (string) the index name.
703: * - column: (string|array) column name(s).
704: */
705: public function removeIndex($tableName, $options = array())
706: {
707: $this->_clearTableCache($tableName);
708: return $this->execute('DROP INDEX ' . $this->indexName($tableName, $options));
709: }
710:
711: /**
712: * Creates a database.
713: *
714: * @param string $name A database name.
715: * @param array $options Database options: owner, template, charset,
716: * tablespace, and connection_limit.
717: */
718: public function createDatabase($name, $options = array())
719: {
720: $options = array_merge(array('charset' => 'utf8'), $options);
721:
722: $optionString = '';
723: foreach ($options as $key => $value) {
724: switch ($key) {
725: case 'owner':
726: $optionString .= " OWNER = '$value'";
727: break;
728: case 'template':
729: $optionString .= " TEMPLATE = $value";
730: break;
731: case 'charset':
732: $optionString .= " ENCODING = '$value'";
733: break;
734: case 'tablespace':
735: $optionString .= " TABLESPACE = $value";
736: break;
737: case 'connection_limit':
738: $optionString .= " CONNECTION LIMIT = $value";
739: }
740: }
741:
742: return $this->execute('CREATE DATABASE ' . $this->quoteTableName($name) . $optionString);
743: }
744:
745: /**
746: * Drops a database.
747: *
748: * @param string $name A database name.
749: */
750: public function dropDatabase($name)
751: {
752: if ($this->postgresqlVersion() >= 80200) {
753: return $this->execute('DROP DATABASE IF EXISTS ' . $this->quoteTableName($name));
754: }
755: try {
756: return $this->execute('DROP DATABASE ' . $this->quoteTableName($name));
757: } catch (Horde_Db_Exception $e) {
758: if ($this->_logger) {
759: $this->_logger->warn("$name database doesn't exist");
760: }
761: }
762: }
763:
764: /**
765: * Returns the name of the currently selected database.
766: *
767: * @return string The database name.
768: */
769: public function currentDatabase()
770: {
771: return $this->selectValue('SELECT current_database()');
772: }
773:
774: /**
775: * Generates the SQL definition for a column type.
776: *
777: * @param string $type A column type.
778: * @param integer $limit Maximum column length (non decimal type only)
779: * @param integer $precision The number precision (decimal type only).
780: * @param integer $scale The number scaling (decimal columns only).
781: * @param boolean $unsigned Whether the column is an unsigned number
782: * (non decimal columns only).
783: *
784: * @return string The SQL definition. If $type is not one of the
785: * internally supported types, $type is returned unchanged.
786: */
787: public function typeToSql($type, $limit = null, $precision = null,
788: $scale = null, $unsigned = null)
789: {
790: if ($type != 'integer') {
791: return parent::typeToSql($type, $limit, $precision, $scale);
792: }
793:
794: switch ($limit) {
795: case 1:
796: case 2:
797: return 'smallint';
798:
799: case 3:
800: case 4:
801: case null:
802: return 'integer';
803:
804: case 5:
805: case 6:
806: case 7:
807: case 8:
808: return 'bigint';
809: }
810:
811: throw new Horde_Db_Exception("No integer type has byte size $limit. Use a numeric with precision 0 instead.");
812: }
813:
814: /**
815: * Generates a DISTINCT clause for SELECT queries.
816: *
817: * PostgreSQL requires the ORDER BY columns in the SELECT list for distinct
818: * queries, and requires that the ORDER BY include the DISTINCT column.
819: *
820: * <code>
821: * $connection->distinct('posts.id', 'posts.created_at DESC')
822: * </code>
823: *
824: * @param string $columns A column list.
825: * @param string $orderBy An ORDER clause.
826: *
827: * @return string The generated DISTINCT clause.
828: */
829: public function distinct($columns, $orderBy = null)
830: {
831: if (empty($orderBy)) {
832: return 'DISTINCT ' . $columns;
833: }
834:
835: // Construct a clean list of column names from the ORDER BY clause,
836: // removing any ASC/DESC modifiers.
837: $orderColumns = array();
838: foreach (preg_split('/\s*,\s*/', $orderBy, -1, PREG_SPLIT_NO_EMPTY) as $orderByClause) {
839: $orderColumns[] = current(preg_split('/\s+/', $orderByClause, -1, PREG_SPLIT_NO_EMPTY)) . ' AS alias_' . count($orderColumns);
840: }
841:
842: // Return a DISTINCT ON() clause that's distinct on the columns we want
843: // but includes all the required columns for the ORDER BY to work
844: // properly.
845: return sprintf('DISTINCT ON (%s) %s, %s',
846: $columns, $columns, implode(', ', $orderColumns));
847: }
848:
849: /**
850: * Adds an ORDER BY clause to an existing query.
851: *
852: * PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so
853: * we work around this by wrapping the $sql string as a sub-select and
854: * ordering in that query.
855: *
856: * @param string $sql An SQL query to manipulate.
857: * @param array $options Options:
858: * - order: Order column an direction.
859: *
860: * @return string The manipulated SQL query.
861: */
862: public function addOrderByForAssociationLimiting($sql, $options)
863: {
864: if (empty($options['order'])) {
865: return $sql;
866: }
867:
868: $order = array();
869: foreach (preg_split('/\s*,\s*/', $options['order'], -1, PREG_SPLIT_NO_EMPTY) as $s) {
870: if (preg_match('/\bdesc$/i', $s)) {
871: $s = 'DESC';
872: }
873: $order[] = 'id_list.alias_' . count($order) . ' ' . $s;
874: }
875: $order = implode(', ', $order);
876:
877: return sprintf('SELECT * FROM (%s) AS id_list ORDER BY %s',
878: $sql, $order);
879: }
880:
881: /**
882: * Generates an INTERVAL clause for SELECT queries.
883: *
884: * @param string $interval The interval.
885: * @param string $precision The precision.
886: *
887: * @return string The generated INTERVAL clause.
888: */
889: public function interval($interval, $precision)
890: {
891: return 'INTERVAL \'' . $interval . ' ' . $precision . '\'';
892: }
893:
894: /**
895: * Generates a modified date for SELECT queries.
896: *
897: * @since Horde_Db 1.2.0
898: *
899: * @param string $reference The reference date - this is a column
900: * referenced in the SELECT.
901: * @param string $operator Add or subtract time? (+/-)
902: * @param integer $amount The shift amount (number of days if $interval
903: * is DAY, etc).
904: * @param string $interval The interval (SECOND, MINUTE, HOUR, DAY,
905: * MONTH, YEAR).
906: *
907: * @return string The generated INTERVAL clause.
908: */
909: public function modifyDate($reference, $operator, $amount, $interval)
910: {
911: if (!is_int($amount)) {
912: throw new InvalidArgumentException('$amount parameter must be an integer');
913: }
914: return sprintf('%s %s INTERVAL \'%s %s\'',
915: $reference,
916: $operator,
917: $amount,
918: $interval);
919: }
920:
921: /**
922: * Returns an expression using the specified operator.
923: *
924: * @param string $lhs The column or expression to test.
925: * @param string $op The operator.
926: * @param string $rhs The comparison value.
927: * @param boolean $bind If true, the method returns the query and a list
928: * of values suitable for binding as an array.
929: * @param array $params Any additional parameters for the operator.
930: *
931: * @return string|array The SQL test fragment, or an array containing the
932: * query and a list of values if $bind is true.
933: */
934: public function buildClause($lhs, $op, $rhs, $bind = false,
935: $params = array())
936: {
937: $lhs = $this->_escapePrepare($lhs);
938: switch ($op) {
939: case '|':
940: case '&':
941: /* Only PgSQL 7.3+ understands SQL99 'SIMILAR TO'; use ~ for
942: * greater backwards compatibility. */
943: $query = 'CASE WHEN CAST(%s AS VARCHAR) ~ \'^-?[0-9]+$\' THEN (CAST(%s AS INTEGER) %s %s) <> 0 ELSE FALSE END';
944: if ($bind) {
945: return array(sprintf($query, $lhs, $lhs, $op, '?'),
946: array((int)$rhs));
947: } else {
948: return sprintf($query, $lhs, $lhs, $op, (int)$rhs);
949: }
950:
951: case 'LIKE':
952: $query = '%s ILIKE %s';
953: if ($bind) {
954: if (empty($params['begin'])) {
955: return array(sprintf($query, $lhs, '?'),
956: array('%' . $rhs . '%'));
957: }
958: return array(sprintf('(' . $query . ' OR ' . $query . ')',
959: $lhs, '?', $lhs, '?'),
960: array($rhs . '%', '% ' . $rhs . '%'));
961: }
962: if (empty($params['begin'])) {
963: return sprintf($query,
964: $lhs,
965: $this->_escapePrepare($this->quote('%' . $rhs . '%')));
966: }
967: return sprintf('(' . $query . ' OR ' . $query . ')',
968: $lhs,
969: $this->_escapePrepare($this->quote($rhs . '%')),
970: $lhs,
971: $this->_escapePrepare($this->quote('% ' . $rhs . '%')));
972: }
973:
974: return parent::buildClause($lhs, $op, $rhs, $bind, $params);
975: }
976:
977:
978: /*##########################################################################
979: # PostgreSQL specific methods
980: ##########################################################################*/
981:
982: /**
983: * Returns the current database's encoding format.
984: *
985: * @return string The current database's encoding format.
986: */
987: public function encoding()
988: {
989: return $this->selectValue(
990: 'SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database
991: WHERE pg_database.datname LIKE ' . $this->quote($this->currentDatabase()));
992: }
993:
994: /**
995: * Sets the schema search path to a string of comma-separated schema names.
996: *
997: * Names beginning with $ have to be quoted (e.g. $user => '$user'). See:
998: * http://www.postgresql.org/docs/current/static/ddl-schemas.html
999: *
1000: * @param string $schemaCsv A comma-separated schema name list.
1001: */
1002: public function setSchemaSearchPath($schemaCsv)
1003: {
1004: if ($schemaCsv) {
1005: $this->execute('SET search_path TO ' . $schemaCsv);
1006: $this->_schemaSearchPath = $schemaCsv;
1007: }
1008: }
1009:
1010: /**
1011: * Returns the active schema search path.
1012: *
1013: * @return string The active schema search path.
1014: */
1015: public function getSchemaSearchPath()
1016: {
1017: if (!$this->_schemaSearchPath) {
1018: $this->_schemaSearchPath = $this->selectValue('SHOW search_path');
1019: }
1020: return $this->_schemaSearchPath;
1021: }
1022:
1023: /**
1024: * Returns the current client log message level.
1025: *
1026: * @return string The current client log message level.
1027: */
1028: public function getClientMinMessages()
1029: {
1030: return $this->selectValue('SHOW client_min_messages');
1031: }
1032:
1033: /**
1034: * Sets the client log message level.
1035: *
1036: * @param string $level The client log message level. One of DEBUG5,
1037: * DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE,
1038: * WARNING, ERROR, FATAL, or PANIC.
1039: */
1040: public function setClientMinMessages($level)
1041: {
1042: return $this->execute('SET client_min_messages TO ' . $this->quote($level));
1043: }
1044:
1045: /**
1046: * Returns the sequence name for a table's primary key or some other
1047: * specified key.
1048: *
1049: * If a sequence name doesn't exist, it is built from the table and primary
1050: * key name.
1051: *
1052: * @param string $tableName A table name.
1053: * @param string $pk A primary key name. Overrides the existing key
1054: * name when building a new sequence name.
1055: *
1056: * @return string The key's sequence name.
1057: */
1058: public function defaultSequenceName($tableName, $pk = null)
1059: {
1060: list($defaultPk, $defaultSeq) = $this->pkAndSequenceFor($tableName);
1061: if (!$defaultSeq) {
1062: $defaultSeq = $tableName . '_' . ($pk ? $pk : ($defaultPk ? $defaultPk : 'id')) . '_seq';
1063: }
1064: return $defaultSeq;
1065: }
1066:
1067: /**
1068: * Resets the sequence of a table's primary key to the maximum value.
1069: *
1070: * @param string $tableName A table name.
1071: * @param string $pk A primary key name. Defaults to the existing
1072: * primary key.
1073: * @param string $sequence A sequence name. Defaults to the sequence name
1074: * of the existing primary key.
1075: *
1076: * @return integer The (next) sequence value if a primary key and a
1077: * sequence exist.
1078: */
1079: public function resetPkSequence($table, $pk = null, $sequence = null)
1080: {
1081: if (!$pk || !$sequence) {
1082: list($defaultPk, $defaultSequence) = $this->pkAndSequenceFor($table);
1083: if (!$pk) {
1084: $pk = $defaultPk;
1085: }
1086: if (!$sequence) {
1087: $sequence = $defaultSequence;
1088: }
1089: }
1090:
1091: if ($pk) {
1092: if ($sequence) {
1093: $quotedSequence = $this->quoteSequenceName($sequence);
1094: $quotedTable = $this->quoteTableName($table);
1095: $quotedPk = $this->quoteColumnName($pk);
1096:
1097: $sql = sprintf('SELECT setval(%s, (SELECT COALESCE(MAX(%s) + (SELECT increment_by FROM %s), (SELECT min_value FROM %s)) FROM %s), false)',
1098: $quotedSequence,
1099: $quotedPk,
1100: $sequence,
1101: $sequence,
1102: $quotedTable);
1103: $this->selectValue($sql, 'Reset sequence');
1104: } else {
1105: if ($this->_logger) {
1106: $this->_logger->warn(sprintf('%s has primary key %s with no default sequence', $table, $pk));
1107: }
1108: }
1109: }
1110: }
1111:
1112: /**
1113: * Returns a table's primary key and the key's sequence.
1114: *
1115: * @param string $tableName A table name.
1116: *
1117: * @return array Array with two values: the primary key name and the key's
1118: * sequence name.
1119: */
1120: public function pkAndSequenceFor($table)
1121: {
1122: // First try looking for a sequence with a dependency on the
1123: // given table's primary key.
1124: $sql = "
1125: SELECT attr.attname, seq.relname
1126: FROM pg_class seq,
1127: pg_attribute attr,
1128: pg_depend dep,
1129: pg_namespace name,
1130: pg_constraint cons
1131: WHERE seq.oid = dep.objid
1132: AND seq.relkind = 'S'
1133: AND attr.attrelid = dep.refobjid
1134: AND attr.attnum = dep.refobjsubid
1135: AND attr.attrelid = cons.conrelid
1136: AND attr.attnum = cons.conkey[1]
1137: AND cons.contype = 'p'
1138: AND dep.refobjid = '$table'::regclass";
1139: $result = $this->selectOne($sql, 'PK and serial sequence');
1140:
1141: if (!$result) {
1142: // If that fails, try parsing the primary key's default value.
1143: // Support the 7.x and 8.0 nextval('foo'::text) as well as
1144: // the 8.1+ nextval('foo'::regclass).
1145: $sql = "
1146: SELECT attr.attname,
1147: CASE
1148: WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN
1149: substr(split_part(def.adsrc, '''', 2),
1150: strpos(split_part(def.adsrc, '''', 2), '.')+1)
1151: ELSE split_part(def.adsrc, '''', 2)
1152: END AS relname
1153: FROM pg_class t
1154: JOIN pg_attribute attr ON (t.oid = attrelid)
1155: JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum)
1156: JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1])
1157: WHERE t.oid = '$table'::regclass
1158: AND cons.contype = 'p'
1159: AND def.adsrc ~* 'nextval'";
1160:
1161: $result = $this->selectOne($sql, 'PK and custom sequence');
1162: }
1163:
1164: // [primary_key, sequence]
1165: return array($result['attname'], $result['relname']);
1166: }
1167:
1168: /**
1169: * Returns the version of the connected PostgreSQL server.
1170: *
1171: * @return integer Zero padded PostgreSQL version, e.g. 80108 for 8.1.8.
1172: */
1173: public function postgresqlVersion()
1174: {
1175: if (!$this->_version) {
1176: try {
1177: $version = $this->selectValue('SELECT version()');
1178: if (preg_match('/PostgreSQL (\d+)\.(\d+)\.(\d+)/', $version, $matches))
1179: $this->_version = ($matches[1] * 10000) + ($matches[2] * 100) + $matches[3];
1180: } catch (Exception $e) {
1181: return 0;
1182: }
1183: }
1184:
1185: return $this->_version;
1186: }
1187: }
1188: