ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
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
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?>
$result
const MDB2_OK
The method mapErrorCode in each MDB2_dbtype implementation maps native error codes to one of these.
Definition: MDB2.php:67
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:158
const MDB2_ERROR_CANNOT_ALTER
Definition: MDB2.php:98
_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:497
listTableIndexes($table)
list all indexes in a table
Definition: pgsql.php:575
listFunctions()
list all functions in the current database
Definition: pgsql.php:424
listViews()
list all views in the current database
Definition: pgsql.php:365
listTableTriggers($table=null)
list all triggers in the database that reference a given table
Definition: pgsql.php:463
listDatabases()
list all databases
Definition: pgsql.php:305
createSequence($seq_name, $start=1)
create sequence
Definition: pgsql.php:658
dropDatabase($name)
drop an existing database
Definition: pgsql.php:88
listTableConstraints($table)
list all constraints in a table
Definition: pgsql.php:615
listTableFields($table)
list all fields in a table in the current database
Definition: pgsql.php:544
listTableViews($table)
list the views in the database that reference a given table
Definition: pgsql.php:396
listSequences()
list all sequences in the current database
Definition: pgsql.php:700
dropSequence($seq_name)
drop existing sequence
Definition: pgsql.php:680
createDatabase($name)
create a new database
Definition: pgsql.php:67
listUsers()
list all users
Definition: pgsql.php:338
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:4206
isResultCommon($value)
Tell whether a value is a MDB2 result implementing the common interface.
Definition: MDB2.php:655
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:279