ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
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
47require_once 'MDB2/Driver/Manager/Common.php';
48
57{
58 // {{{ createDatabase()
59
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
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_orig = $name;
285 $name = $db->quoteIdentifier($name, true);
286 if (!empty($changes['name'])) {
287 $indexes = $this->listTableIndexes($name_orig);
288 foreach ($indexes as $index) {
289 $new_name = str_replace($name_orig, $changes['name'], $index);
290 $result = $db->exec("ALTER INDEX {$index}_idx RENAME TO {$new_name}_idx");
291 if (PEAR::isError($result)) {
292// return $result;
293 }
294 }
295
296 $constraints = $this->listTableConstraints($name_orig);
297 foreach ($constraints as $constraint) {
298 $new_name = str_replace($name_orig, $changes['name'], $constraint);
299 $result = $db->exec("ALTER TABLE {$name_orig} RENAME CONSTRAINT {$constraint}_idx TO {$new_name}_idx;");
300 if (PEAR::isError($result)) {
301// return $result;
302 }
303 }
304
305 $change_name = $db->quoteIdentifier($changes['name'], true);
306 $result = $db->exec("ALTER TABLE $name RENAME TO ".$change_name);
307 if (PEAR::isError($result)) {
308 return $result;
309 }
310 }
311
312 return MDB2_OK;
313 }
314
315 // }}}
316 // {{{ listDatabases()
317
324 function listDatabases()
325 {
326 $db =& $this->getDBInstance();
327 if (PEAR::isError($db)) {
328 return $db;
329 }
330
331 $query = 'SELECT datname FROM pg_database';
332 $result2 = $db->standaloneQuery($query, array('text'), false);
333 if (!MDB2::isResultCommon($result2)) {
334 return $result2;
335 }
336
337 $result = $result2->fetchCol();
338 $result2->free();
339 if (PEAR::isError($result)) {
340 return $result;
341 }
342 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
343 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
344 }
345 return $result;
346 }
347
348 // }}}
349 // {{{ listUsers()
350
357 function listUsers()
358 {
359 $db =& $this->getDBInstance();
360 if (PEAR::isError($db)) {
361 return $db;
362 }
363
364 $query = 'SELECT usename FROM pg_user';
365 $result2 = $db->standaloneQuery($query, array('text'), false);
366 if (!MDB2::isResultCommon($result2)) {
367 return $result2;
368 }
369
370 $result = $result2->fetchCol();
371 $result2->free();
372 return $result;
373 }
374
375 // }}}
376 // {{{ listViews()
377
384 function listViews()
385 {
386 $db =& $this->getDBInstance();
387 if (PEAR::isError($db)) {
388 return $db;
389 }
390
391 $query = "SELECT viewname
392 FROM pg_views
393 WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
394 AND viewname !~ '^pg_'";
395 $result = $db->queryCol($query);
396 if (PEAR::isError($result)) {
397 return $result;
398 }
399 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
400 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
401 }
402 return $result;
403 }
404
405 // }}}
406 // {{{ listTableViews()
407
416 {
417 $db =& $this->getDBInstance();
418 if (PEAR::isError($db)) {
419 return $db;
420 }
421
422 $query = 'SELECT viewname FROM pg_views NATURAL JOIN pg_tables';
423 $query.= ' WHERE tablename ='.$db->quote($table, 'text');
424 $result = $db->queryCol($query);
425 if (PEAR::isError($result)) {
426 return $result;
427 }
428 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
429 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
430 }
431 return $result;
432 }
433
434 // }}}
435 // {{{ listFunctions()
436
443 function listFunctions()
444 {
445 $db =& $this->getDBInstance();
446 if (PEAR::isError($db)) {
447 return $db;
448 }
449
450 $query = "
451 SELECT
452 proname
453 FROM
454 pg_proc pr,
455 pg_type tp
456 WHERE
457 tp.oid = pr.prorettype
458 AND pr.proisagg = FALSE
459 AND tp.typname <> 'trigger'
460 AND pr.pronamespace IN
461 (SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
462 $result = $db->queryCol($query);
463 if (PEAR::isError($result)) {
464 return $result;
465 }
466 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
467 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
468 }
469 return $result;
470 }
471
472 // }}}
473 // {{{ listTableTriggers()
474
482 function listTableTriggers($table = null)
483 {
484 $db =& $this->getDBInstance();
485 if (PEAR::isError($db)) {
486 return $db;
487 }
488
489 $query = 'SELECT trg.tgname AS trigger_name
490 FROM pg_trigger trg,
491 pg_class tbl
492 WHERE trg.tgrelid = tbl.oid';
493 if (!is_null($table)) {
494 $table = $db->quote(strtoupper($table), 'text');
495 $query .= " AND tbl.relname = $table";
496 }
497 $result = $db->queryCol($query);
498 if (PEAR::isError($result)) {
499 return $result;
500 }
501 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
502 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
503 }
504 return $result;
505 }
506
507 // }}}
508 // {{{ listTables()
509
516 function listTables()
517 {
518 $db =& $this->getDBInstance();
519 if (PEAR::isError($db)) {
520 return $db;
521 }
522
523 // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
524 $query = 'SELECT c.relname AS "Name"'
525 . ' FROM pg_class c, pg_user u'
526 . ' WHERE c.relowner = u.usesysid'
527 . " AND c.relkind = 'r'"
528 . ' AND NOT EXISTS'
529 . ' (SELECT 1 FROM pg_views'
530 . ' WHERE viewname = c.relname)'
531 . " AND c.relname !~ '^(pg_|sql_)'"
532 . ' UNION'
533 . ' SELECT c.relname AS "Name"'
534 . ' FROM pg_class c'
535 . " WHERE c.relkind = 'r'"
536 . ' AND NOT EXISTS'
537 . ' (SELECT 1 FROM pg_views'
538 . ' WHERE viewname = c.relname)'
539 . ' AND NOT EXISTS'
540 . ' (SELECT 1 FROM pg_user'
541 . ' WHERE usesysid = c.relowner)'
542 . " AND c.relname !~ '^pg_'";
543 $result = $db->queryCol($query);
544 if (PEAR::isError($result)) {
545 return $result;
546 }
547 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
548 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
549 }
550 sort($result); // FSX Patch
551 return $result;
552 }
553
554 // }}}
555 // {{{ listTableFields()
556
565 {
566 $db =& $this->getDBInstance();
567 if (PEAR::isError($db)) {
568 return $db;
569 }
570
571 $table = $db->quoteIdentifier($table, true);
572 $db->setLimit(1);
573 $result2 = $db->query("SELECT * FROM $table");
574 if (PEAR::isError($result2)) {
575 return $result2;
576 }
577 $result = $result2->getColumnNames();
578 $result2->free();
579 if (PEAR::isError($result)) {
580 return $result;
581 }
582 return array_flip($result);
583 }
584
585 // }}}
586 // {{{ listTableIndexes()
587
596 {
597 $db =& $this->getDBInstance();
598 if (PEAR::isError($db)) {
599 return $db;
600 }
601
602 $table = $db->quote($table, 'text');
603 $subquery = "SELECT indexrelid FROM pg_index, pg_class";
604 $subquery.= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'";
605 $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
606 $indexes = $db->queryCol($query, 'text');
607 if (PEAR::isError($indexes)) {
608 return $indexes;
609 }
610
611 $result = array();
612 foreach ($indexes as $index) {
613 $index = $this->_fixIndexName($index);
614 if (!empty($index)) {
615 $result[$index] = true;
616 }
617 }
618
619 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
620 $result = array_change_key_case($result, $db->options['field_case']);
621 }
622 return array_keys($result);
623 }
624
625 // }}}
626 // {{{ listTableConstraints()
627
636 {
637 $db =& $this->getDBInstance();
638 if (PEAR::isError($db)) {
639 return $db;
640 }
641
642 $table = $db->quote($table, 'text');
643 $subquery = "SELECT indexrelid FROM pg_index, pg_class";
644 $subquery.= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND (indisunique = 't' OR indisprimary = 't')";
645 $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
646 $constraints = $db->queryCol($query);
647 if (PEAR::isError($constraints)) {
648 return $constraints;
649 }
650
651 $result = array();
652 foreach ($constraints as $constraint) {
653 $constraint = $this->_fixIndexName($constraint);
654 if (!empty($constraint)) {
655 $result[$constraint] = true;
656 }
657 }
658
659 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
660 && $db->options['field_case'] == CASE_LOWER
661 ) {
662 $result = array_change_key_case($result, $db->options['field_case']);
663 }
664 return array_keys($result);
665 }
666
667 // }}}
668 // {{{ createSequence()
669
678 function createSequence($seq_name, $start = 1)
679 {
680 $db =& $this->getDBInstance();
681 if (PEAR::isError($db)) {
682 return $db;
683 }
684
685 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
686 return $db->exec("CREATE SEQUENCE $sequence_name INCREMENT 1".
687 ($start < 1 ? " MINVALUE $start" : '')." START $start");
688 }
689
690 // }}}
691 // {{{ dropSequence()
692
700 function dropSequence($seq_name)
701 {
702 $db =& $this->getDBInstance();
703 if (PEAR::isError($db)) {
704 return $db;
705 }
706
707 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
708 return $db->exec("DROP SEQUENCE $sequence_name");
709 }
710
711 // }}}
712 // {{{ listSequences()
713
720 function listSequences()
721 {
722 $db =& $this->getDBInstance();
723 if (PEAR::isError($db)) {
724 return $db;
725 }
726
727 $query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
728 $query.= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
729 $table_names = $db->queryCol($query);
730 if (PEAR::isError($table_names)) {
731 return $table_names;
732 }
733 $result = array();
734 foreach ($table_names as $table_name) {
735 $result[] = $this->_fixSequenceName($table_name);
736 }
737 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
738 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
739 }
740 sort($result); // FSX patch
741
742 return $result;
743 }
744}
745?>
$result
const MDB2_PORTABILITY_FIX_CASE
Portability: convert names of tables and fields to case defined in the "field_case" option when using...
Definition: MDB2.php:163
const MDB2_OK(!class_exists('PEAR'))
The method mapErrorCode in each MDB2_dbtype implementation maps native error codes to one of these.
Definition: MDB2.php:72
const MDB2_ERROR_CANNOT_ALTER
Definition: MDB2.php:103
An exception for terminatinating execution or to throw for unit testing.
_fixIndexName($idx)
Removes any formatting in an index name using the 'idxname_format' option.
Definition: Common.php:144
_fixSequenceName($sqn, $check=false)
Removes any formatting in an sequence name using the 'seqname_format' option.
Definition: Common.php:116
listTables()
list all tables in the current database
Definition: pgsql.php:516
listTableIndexes($table)
list all indexes in a table
Definition: pgsql.php:595
listFunctions()
list all functions in the current database
Definition: pgsql.php:443
listViews()
list all views in the current database
Definition: pgsql.php:384
listTableTriggers($table=null)
list all triggers in the database that reference a given table
Definition: pgsql.php:482
listDatabases()
list all databases
Definition: pgsql.php:324
createSequence($seq_name, $start=1)
create sequence
Definition: pgsql.php:678
dropDatabase($name)
drop an existing database
Definition: pgsql.php:88
listTableConstraints($table)
list all constraints in a table
Definition: pgsql.php:635
listTableFields($table)
list all fields in a table in the current database
Definition: pgsql.php:564
listTableViews($table)
list the views in the database that reference a given table
Definition: pgsql.php:415
listSequences()
list all sequences in the current database
Definition: pgsql.php:720
dropSequence($seq_name)
drop existing sequence
Definition: pgsql.php:700
createDatabase($name)
create a new database
Definition: pgsql.php:67
listUsers()
list all users
Definition: pgsql.php:357
alterTable($name, $changes, $check)
alter an existing table
Definition: pgsql.php:192
& getDBInstance()
Get the instance of MDB2 associated with the module instance.
Definition: MDB2.php:4244
isResultCommon($value)
Tell whether a value is a MDB2 result implementing the common interface.
Definition: MDB2.php:660
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:280
if($format !==null) $name
Definition: metadata.php:146
$index
Definition: metadata.php:60
$query
if(empty($password)) $table
Definition: pwgen.php:24