ILIAS  release_4-3 Revision
 All Data Structures Namespaces Files Functions Variables Groups Pages
pgsql.php
Go to the documentation of this file.
1 <?php
2 // +----------------------------------------------------------------------+
3 // | PHP versions 4 and 5 |
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) 1998-2006 Manuel Lemos, Tomas V.V.Cox, |
6 // | Stig. S. Bakken, Lukas Smith |
7 // | All rights reserved. |
8 // +----------------------------------------------------------------------+
9 // | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
10 // | API as well as database abstraction for PHP applications. |
11 // | This LICENSE is in the BSD license style. |
12 // | |
13 // | Redistribution and use in source and binary forms, with or without |
14 // | modification, are permitted provided that the following conditions |
15 // | are met: |
16 // | |
17 // | Redistributions of source code must retain the above copyright |
18 // | notice, this list of conditions and the following disclaimer. |
19 // | |
20 // | Redistributions in binary form must reproduce the above copyright |
21 // | notice, this list of conditions and the following disclaimer in the |
22 // | documentation and/or other materials provided with the distribution. |
23 // | |
24 // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
25 // | Lukas Smith nor the names of his contributors may be used to endorse |
26 // | or promote products derived from this software without specific prior|
27 // | written permission. |
28 // | |
29 // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
30 // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
31 // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
32 // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
33 // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
34 // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
35 // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
36 // | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
37 // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
38 // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
39 // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
40 // | POSSIBILITY OF SUCH DAMAGE. |
41 // +----------------------------------------------------------------------+
42 // | Author: Paul Cooper <pgc@ucecom.com> |
43 // +----------------------------------------------------------------------+
44 //
45 // $Id: pgsql.php,v 1.70 2007/04/10 20:35:49 nrf Exp $
46 
47 require_once 'MDB2/Driver/Manager/Common.php';
48 
56 class MDB2_Driver_Manager_pgsql extends MDB2_Driver_Manager_Common
57 {
58  // {{{ createDatabase()
59 
67  function createDatabase($name)
68  {
69  $db =& $this->getDBInstance();
70  if (PEAR::isError($db)) {
71  return $db;
72  }
73 
74  $name = $db->quoteIdentifier($name, true);
75  return $db->standaloneQuery("CREATE DATABASE $name", null, true);
76  }
77 
78  // }}}
79  // {{{ dropDatabase()
80 
88  function dropDatabase($name)
89  {
90  $db =& $this->getDBInstance();
91  if (PEAR::isError($db)) {
92  return $db;
93  }
94 
95  $name = $db->quoteIdentifier($name, true);
96  return $db->standaloneQuery("DROP DATABASE $name", null, true);
97  }
98 
99  // }}}
100  // {{{ alterTable()
101 
192  function alterTable($name, $changes, $check)
193  {
194  $db =& $this->getDBInstance();
195  if (PEAR::isError($db)) {
196  return $db;
197  }
198 
199  foreach ($changes as $change_name => $change) {
200  switch ($change_name) {
201  case 'add':
202  case 'remove':
203  case 'change':
204  case 'name':
205  case 'rename':
206  break;
207  default:
208  return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
209  'change type "'.$change_name.'\" not yet supported', __FUNCTION__);
210  }
211  }
212 
213  if ($check) {
214  return MDB2_OK;
215  }
216 
217  if (!empty($changes['add']) && is_array($changes['add'])) {
218  foreach ($changes['add'] as $field_name => $field) {
219  $query = 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);
220  $result = $db->exec("ALTER TABLE $name $query");
221  if (PEAR::isError($result)) {
222  return $result;
223  }
224  }
225  }
226 
227  if (!empty($changes['remove']) && is_array($changes['remove'])) {
228  foreach ($changes['remove'] as $field_name => $field) {
229  $field_name = $db->quoteIdentifier($field_name, true);
230  $query = 'DROP ' . $field_name;
231  $result = $db->exec("ALTER TABLE $name $query");
232  if (PEAR::isError($result)) {
233  return $result;
234  }
235  }
236  }
237 
238  if (!empty($changes['change']) && is_array($changes['change'])) {
239  foreach ($changes['change'] as $field_name => $field) {
240  $field_name = $db->quoteIdentifier($field_name, true);
241  if (!empty($field['type'])) {
242  $server_info = $db->getServerVersion();
243  if (PEAR::isError($server_info)) {
244  return $server_info;
245  }
246  if (is_array($server_info) && $server_info['major'] < 8) {
247  return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
248  'changing column type for "'.$change_name.'\" requires PostgreSQL 8.0 or above', __FUNCTION__);
249  }
250  $db->loadModule('Datatype', null, true);
251  $query = "ALTER $field_name TYPE ".$db->datatype->getTypeDeclaration($field['definition']);
252  $result = $db->exec("ALTER TABLE $name $query");
253  if (PEAR::isError($result)) {
254  return $result;
255  }
256  }
257  if (array_key_exists('default', $field)) {
258  $query = "ALTER $field_name SET DEFAULT ".$db->quote($field['definition']['default'], $field['definition']['type']);
259  $result = $db->exec("ALTER TABLE $name $query");
260  if (PEAR::isError($result)) {
261  return $result;
262  }
263  }
264  if (!empty($field['notnull'])) {
265  $query = "ALTER $field_name ".($field['definition']['notnull'] ? "SET" : "DROP").' NOT NULL';
266  $result = $db->exec("ALTER TABLE $name $query");
267  if (PEAR::isError($result)) {
268  return $result;
269  }
270  }
271  }
272  }
273 
274  if (!empty($changes['rename']) && is_array($changes['rename'])) {
275  foreach ($changes['rename'] as $field_name => $field) {
276  $field_name = $db->quoteIdentifier($field_name, true);
277  $result = $db->exec("ALTER TABLE $name RENAME COLUMN $field_name TO ".$db->quoteIdentifier($field['name'], true));
278  if (PEAR::isError($result)) {
279  return $result;
280  }
281  }
282  }
283 
284  $name = $db->quoteIdentifier($name, true);
285  if (!empty($changes['name'])) {
286  $change_name = $db->quoteIdentifier($changes['name'], true);
287  $result = $db->exec("ALTER TABLE $name RENAME TO ".$change_name);
288  if (PEAR::isError($result)) {
289  return $result;
290  }
291  }
292 
293  return MDB2_OK;
294  }
295 
296  // }}}
297  // {{{ listDatabases()
298 
305  function listDatabases()
306  {
307  $db =& $this->getDBInstance();
308  if (PEAR::isError($db)) {
309  return $db;
310  }
311 
312  $query = 'SELECT datname FROM pg_database';
313  $result2 = $db->standaloneQuery($query, array('text'), false);
314  if (!MDB2::isResultCommon($result2)) {
315  return $result2;
316  }
317 
318  $result = $result2->fetchCol();
319  $result2->free();
320  if (PEAR::isError($result)) {
321  return $result;
322  }
323  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
324  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
325  }
326  return $result;
327  }
328 
329  // }}}
330  // {{{ listUsers()
331 
338  function listUsers()
339  {
340  $db =& $this->getDBInstance();
341  if (PEAR::isError($db)) {
342  return $db;
343  }
344 
345  $query = 'SELECT usename FROM pg_user';
346  $result2 = $db->standaloneQuery($query, array('text'), false);
347  if (!MDB2::isResultCommon($result2)) {
348  return $result2;
349  }
350 
351  $result = $result2->fetchCol();
352  $result2->free();
353  return $result;
354  }
355 
356  // }}}
357  // {{{ listViews()
358 
365  function listViews()
366  {
367  $db =& $this->getDBInstance();
368  if (PEAR::isError($db)) {
369  return $db;
370  }
371 
372  $query = "SELECT viewname
373  FROM pg_views
374  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
375  AND viewname !~ '^pg_'";
376  $result = $db->queryCol($query);
377  if (PEAR::isError($result)) {
378  return $result;
379  }
380  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
381  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
382  }
383  return $result;
384  }
385 
386  // }}}
387  // {{{ listTableViews()
388 
396  function listTableViews($table)
397  {
398  $db =& $this->getDBInstance();
399  if (PEAR::isError($db)) {
400  return $db;
401  }
402 
403  $query = 'SELECT viewname FROM pg_views NATURAL JOIN pg_tables';
404  $query.= ' WHERE tablename ='.$db->quote($table, 'text');
405  $result = $db->queryCol($query);
406  if (PEAR::isError($result)) {
407  return $result;
408  }
409  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
410  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
411  }
412  return $result;
413  }
414 
415  // }}}
416  // {{{ listFunctions()
417 
424  function listFunctions()
425  {
426  $db =& $this->getDBInstance();
427  if (PEAR::isError($db)) {
428  return $db;
429  }
430 
431  $query = "
432  SELECT
433  proname
434  FROM
435  pg_proc pr,
436  pg_type tp
437  WHERE
438  tp.oid = pr.prorettype
439  AND pr.proisagg = FALSE
440  AND tp.typname <> 'trigger'
441  AND pr.pronamespace IN
442  (SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
443  $result = $db->queryCol($query);
444  if (PEAR::isError($result)) {
445  return $result;
446  }
447  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
448  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
449  }
450  return $result;
451  }
452 
453  // }}}
454  // {{{ listTableTriggers()
455 
463  function listTableTriggers($table = null)
464  {
465  $db =& $this->getDBInstance();
466  if (PEAR::isError($db)) {
467  return $db;
468  }
469 
470  $query = 'SELECT trg.tgname AS trigger_name
471  FROM pg_trigger trg,
472  pg_class tbl
473  WHERE trg.tgrelid = tbl.oid';
474  if (!is_null($table)) {
475  $table = $db->quote(strtoupper($table), 'text');
476  $query .= " AND tbl.relname = $table";
477  }
478  $result = $db->queryCol($query);
479  if (PEAR::isError($result)) {
480  return $result;
481  }
482  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
483  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
484  }
485  return $result;
486  }
487 
488  // }}}
489  // {{{ listTables()
490 
497  function listTables()
498  {
499  $db =& $this->getDBInstance();
500  if (PEAR::isError($db)) {
501  return $db;
502  }
503 
504  // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
505  $query = 'SELECT c.relname AS "Name"'
506  . ' FROM pg_class c, pg_user u'
507  . ' WHERE c.relowner = u.usesysid'
508  . " AND c.relkind = 'r'"
509  . ' AND NOT EXISTS'
510  . ' (SELECT 1 FROM pg_views'
511  . ' WHERE viewname = c.relname)'
512  . " AND c.relname !~ '^(pg_|sql_)'"
513  . ' UNION'
514  . ' SELECT c.relname AS "Name"'
515  . ' FROM pg_class c'
516  . " WHERE c.relkind = 'r'"
517  . ' AND NOT EXISTS'
518  . ' (SELECT 1 FROM pg_views'
519  . ' WHERE viewname = c.relname)'
520  . ' AND NOT EXISTS'
521  . ' (SELECT 1 FROM pg_user'
522  . ' WHERE usesysid = c.relowner)'
523  . " AND c.relname !~ '^pg_'";
524  $result = $db->queryCol($query);
525  if (PEAR::isError($result)) {
526  return $result;
527  }
528  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
529  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
530  }
531  return $result;
532  }
533 
534  // }}}
535  // {{{ listTableFields()
536 
544  function listTableFields($table)
545  {
546  $db =& $this->getDBInstance();
547  if (PEAR::isError($db)) {
548  return $db;
549  }
550 
551  $table = $db->quoteIdentifier($table, true);
552  $db->setLimit(1);
553  $result2 = $db->query("SELECT * FROM $table");
554  if (PEAR::isError($result2)) {
555  return $result2;
556  }
557  $result = $result2->getColumnNames();
558  $result2->free();
559  if (PEAR::isError($result)) {
560  return $result;
561  }
562  return array_flip($result);
563  }
564 
565  // }}}
566  // {{{ listTableIndexes()
567 
575  function listTableIndexes($table)
576  {
577  $db =& $this->getDBInstance();
578  if (PEAR::isError($db)) {
579  return $db;
580  }
581 
582  $table = $db->quote($table, 'text');
583  $subquery = "SELECT indexrelid FROM pg_index, pg_class";
584  $subquery.= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'";
585  $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
586  $indexes = $db->queryCol($query, 'text');
587  if (PEAR::isError($indexes)) {
588  return $indexes;
589  }
590 
591  $result = array();
592  foreach ($indexes as $index) {
593  $index = $this->_fixIndexName($index);
594  if (!empty($index)) {
595  $result[$index] = true;
596  }
597  }
598 
599  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
600  $result = array_change_key_case($result, $db->options['field_case']);
601  }
602  return array_keys($result);
603  }
604 
605  // }}}
606  // {{{ listTableConstraints()
607 
615  function listTableConstraints($table)
616  {
617  $db =& $this->getDBInstance();
618  if (PEAR::isError($db)) {
619  return $db;
620  }
621 
622  $table = $db->quote($table, 'text');
623  $subquery = "SELECT indexrelid FROM pg_index, pg_class";
624  $subquery.= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND (indisunique = 't' OR indisprimary = 't')";
625  $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
626  $constraints = $db->queryCol($query);
627  if (PEAR::isError($constraints)) {
628  return $constraints;
629  }
630 
631  $result = array();
632  foreach ($constraints as $constraint) {
633  $constraint = $this->_fixIndexName($constraint);
634  if (!empty($constraint)) {
635  $result[$constraint] = true;
636  }
637  }
638 
639  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
640  && $db->options['field_case'] == CASE_LOWER
641  ) {
642  $result = array_change_key_case($result, $db->options['field_case']);
643  }
644  return array_keys($result);
645  }
646 
647  // }}}
648  // {{{ createSequence()
649 
658  function createSequence($seq_name, $start = 1)
659  {
660  $db =& $this->getDBInstance();
661  if (PEAR::isError($db)) {
662  return $db;
663  }
664 
665  $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
666  return $db->exec("CREATE SEQUENCE $sequence_name INCREMENT 1".
667  ($start < 1 ? " MINVALUE $start" : '')." START $start");
668  }
669 
670  // }}}
671  // {{{ dropSequence()
672 
680  function dropSequence($seq_name)
681  {
682  $db =& $this->getDBInstance();
683  if (PEAR::isError($db)) {
684  return $db;
685  }
686 
687  $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
688  return $db->exec("DROP SEQUENCE $sequence_name");
689  }
690 
691  // }}}
692  // {{{ listSequences()
693 
700  function listSequences()
701  {
702  $db =& $this->getDBInstance();
703  if (PEAR::isError($db)) {
704  return $db;
705  }
706 
707  $query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
708  $query.= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
709  $table_names = $db->queryCol($query);
710  if (PEAR::isError($table_names)) {
711  return $table_names;
712  }
713  $result = array();
714  foreach ($table_names as $table_name) {
715  $result[] = $this->_fixSequenceName($table_name);
716  }
717  if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
718  $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
719  }
720  return $result;
721  }
722 }
723 ?>