From 51fc8e60207b0b5d4cae0bfe43a5113ea4e5d2fc Mon Sep 17 00:00:00 2001 From: xzilla Date: Wed, 12 Dec 2007 04:11:10 +0000 Subject: [PATCH] refer to objects with explicit schemas, patch by ioguix --- DEVELOPERS | 4 + TODO | 7 +- classes/database/ADODB_base.php | 10 +- classes/database/Postgres.php | 130 ++++--- classes/database/Postgres72.php | 10 +- classes/database/Postgres73.php | 580 +++++++++++++++++++++++++++++++- classes/database/Postgres74.php | 32 +- classes/database/Postgres80.php | 10 +- classes/database/Postgres81.php | 10 +- classes/database/Postgres83.php | 18 +- colproperties.php | 4 +- lang/french.php | 24 +- lang/recoded/french.php | 24 +- 13 files changed, 746 insertions(+), 117 deletions(-) diff --git a/DEVELOPERS b/DEVELOPERS index ffce67a9..448919f7 100644 --- a/DEVELOPERS +++ b/DEVELOPERS @@ -80,6 +80,10 @@ attributes in HTML form elements use action="thisformname.php". This ensures that browsers remove query strings when expanding the given relative URL into a full URL. +When working on database classes, always schema qualifing your SQL where it is +possible with the current schema ($data->_schema) for pg73+ classes. Then don't +forget to write your method for older classes which doesn't suppport schemas. + When working with CVS, always make sure to do a 'cvs update' both before you start; so you have the latest code to work with; and also again before you create your patch; to minimize the chance of having conflicts. diff --git a/TODO b/TODO index 1b4d3bd2..87a8beda 100644 --- a/TODO +++ b/TODO @@ -239,7 +239,12 @@ Exotic * Pivot reports (ADODB has a feature for this) * Parameterized reports (use prepared queries) * Full web accessability conformance - +* reversing the database stack inheritance + it would have the following advantages: + - less class to load when using the latests pg versions + - every newer/up-to-date methods will be in the same class file. Other classes will have specific methods + for their pg versions and older + - ... complete me please :) Principles ---------- diff --git a/classes/database/ADODB_base.php b/classes/database/ADODB_base.php index 0164b8db..642b453d 100644 --- a/classes/database/ADODB_base.php +++ b/classes/database/ADODB_base.php @@ -3,7 +3,7 @@ /* * Parent class of all ADODB objects. * - * $Id: ADODB_base.php,v 1.22 2007/01/10 02:01:17 soranzo Exp $ + * $Id: ADODB_base.php,v 1.23 2007/12/12 04:11:10 xzilla Exp $ */ include_once('./libraries/errorhandler.inc.php'); @@ -124,22 +124,26 @@ class ADODB_base { * Delete from the database * @param $table The name of the table * @param $conditions (array) A map of field names to conditions + * @param $schema (optional) The table's schema * @return 0 success * @return -1 on referential integrity violation * @return -2 on no rows deleted */ - function delete($table, $conditions) { + function delete($table, $conditions, $schema = '') { $this->fieldClean($table); reset($conditions); + if (!empty($schema)) + $schema = "\"{$schema}\"."; + // Build clause $sql = ''; while(list($key, $value) = each($conditions)) { $this->clean($key); $this->clean($value); if ($sql) $sql .= " AND \"{$key}\"='{$value}'"; - else $sql = "DELETE FROM \"{$table}\" WHERE \"{$key}\"='{$value}'"; + else $sql = "DELETE FROM {$schema}\"{$table}\" WHERE \"{$key}\"='{$value}'"; } // Check for failures diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index f5fb4c07..c4bec9f3 100755 --- a/classes/database/Postgres.php +++ b/classes/database/Postgres.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres.php,v 1.317 2007/12/11 14:17:17 ioguix Exp $ + * $Id: Postgres.php,v 1.318 2007/12/12 04:11:10 xzilla Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -422,6 +422,13 @@ class Postgres extends ADODB_base { return $temp; } + /** + * Returns the current schema to prepend on object names + */ + function schema() { + return ''; + } + // Database functions /** @@ -746,10 +753,12 @@ class Postgres extends ADODB_base { $status = $this->beginTransaction(); if ($status != 0) return -1; + $schema = $this->schema(); + $found = false; $first = true; $comment_sql = ''; //Accumulate comments for the columns - $sql = "CREATE TABLE \"{$name}\" ("; + $sql = "CREATE TABLE {$schema}\"{$name}\" ("; for ($i = 0; $i < $fields; $i++) { $this->fieldClean($field[$i]); $this->clean($type[$i]); @@ -874,19 +883,13 @@ class Postgres extends ADODB_base { // Comment $status = $this->setComment('TABLE', '', $table, $comment); - if ($status != 0) { - $this->rollbackTransaction(); - return -4; - } + if ($status != 0) return -4; // Rename (only if name has changed) if ($name != $table) { $sql = "ALTER TABLE \"{$table}\" RENAME TO \"{$name}\""; $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -3; - } + if ($status != 0) return -3; } return 0; @@ -962,6 +965,7 @@ class Postgres extends ADODB_base { * @param $newName The new name for the table * @return 0 success */ + /*XXX FIXME !! NOT USED ANYMORE ?? function renameTable($table, $newName) { $this->fieldClean($table); $this->fieldClean($newName); @@ -969,7 +973,7 @@ class Postgres extends ADODB_base { $sql = "ALTER TABLE \"{$table}\" RENAME TO \"{$newName}\""; return $this->execute($sql); - } + }*/ /** * Returns the SQL definition for the table. @@ -1006,7 +1010,9 @@ class Postgres extends ADODB_base { $sql = $this->getChangeUserSQL($t->fields['relowner']) . "\n\n"; // Set schema search path if we support schemas + $schema = ''; if ($this->hasSchemas()) { + $schema = "\"{$this->_schema}\"."; $sql .= "SET search_path = \"{$this->_schema}\", pg_catalog;\n\n"; } @@ -1016,11 +1022,8 @@ class Postgres extends ADODB_base { // in pg_catalog. if (!$clean) $sql .= "-- "; $sql .= "DROP TABLE "; - if ($this->hasSchemas()) { - $sql .= "\"{$this->_schema}\"."; - } - $sql .= "\"{$t->fields['relname']}\";\n"; - $sql .= "CREATE TABLE \"{$t->fields['relname']}\" (\n"; + $sql .= "{$schema}\"{$t->fields['relname']}\";\n"; + $sql .= "CREATE TABLE {$schema}\"{$t->fields['relname']}\" (\n"; // Output all table columns $col_comments_sql = ''; // Accumulate comments on columns @@ -1143,7 +1146,7 @@ class Postgres extends ADODB_base { $sql .= "\n"; $first = false; } - $sql .= "ALTER TABLE ONLY \"{$t->fields['relname']}\" ALTER COLUMN \"{$atts->fields['attname']}\" SET STATISTICS {$atts->fields['attstattarget']};\n"; + $sql .= "ALTER TABLE ONLY {$schema}\"{$t->fields['relname']}\" ALTER COLUMN \"{$atts->fields['attname']}\" SET STATISTICS {$atts->fields['attstattarget']};\n"; } // Then storage if ($atts->fields['attstorage'] != $atts->fields['typstorage']) { @@ -1165,7 +1168,7 @@ class Postgres extends ADODB_base { $this->rollbackTransaction(); return null; } - $sql .= "ALTER TABLE ONLY \"{$t->fields['relname']}\" ALTER COLUMN \"{$atts->fields['attname']}\" SET STORAGE {$storage};\n"; + $sql .= "ALTER TABLE ONLY {$schema}\"{$t->fields['relname']}\" ALTER COLUMN \"{$atts->fields['attname']}\" SET STORAGE {$storage};\n"; } $atts->moveNext(); @@ -1175,7 +1178,7 @@ class Postgres extends ADODB_base { if ($t->fields['relcomment'] !== null) { $this->clean($t->fields['relcomment']); $sql .= "\n-- Comment\n\n"; - $sql .= "COMMENT ON TABLE \"{$t->fields['relname']}\" IS '{$t->fields['relcomment']}';\n"; + $sql .= "COMMENT ON TABLE {$schema}\"{$t->fields['relname']}\" IS '{$t->fields['relcomment']}';\n"; } // Add comments on columns, if any @@ -1195,7 +1198,7 @@ class Postgres extends ADODB_base { * wire-in knowledge about the default public privileges for different * kinds of objects. */ - $sql .= "REVOKE ALL ON TABLE \"{$t->fields['relname']}\" FROM PUBLIC;\n"; + $sql .= "REVOKE ALL ON TABLE {$schema}\"{$t->fields['relname']}\" FROM PUBLIC;\n"; foreach ($privs as $v) { // Get non-GRANT OPTION privs $nongrant = array_diff($v[2], $v[4]); @@ -1400,8 +1403,10 @@ class Postgres extends ADODB_base { $this->clean($length); $this->clean($comment); + $schema = $this->schema(); + if ($length == '') - $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" {$type}"; + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD COLUMN \"{$column}\" {$type}"; else { switch ($type) { // Have to account for weird placing of length for with/without @@ -1409,15 +1414,15 @@ class Postgres extends ADODB_base { case 'timestamp with time zone': case 'timestamp without time zone': $qual = substr($type, 9); - $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" timestamp({$length}){$qual}"; + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD COLUMN \"{$column}\" timestamp({$length}){$qual}"; break; case 'time with time zone': case 'time without time zone': $qual = substr($type, 4); - $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" time({$length}){$qual}"; + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD COLUMN \"{$column}\" time({$length}){$qual}"; break; default: - $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" {$type}({$length})"; + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD COLUMN \"{$column}\" {$type}({$length})"; } } @@ -1651,7 +1656,7 @@ class Postgres extends ADODB_base { return -1; } - $status = $this->delete($table, $key); + $status = $this->delete($table, $key, $this->schema()); if ($status != 0 || $this->conn->Affected_Rows() != 1) { $this->rollbackTransaction(); return -2; @@ -1681,6 +1686,8 @@ class Postgres extends ADODB_base { // Build clause if (sizeof($vars) > 0) { + $schema = $this->schema(); + foreach($vars as $key => $value) { $this->fieldClean($key); @@ -1689,7 +1696,7 @@ class Postgres extends ADODB_base { else $tmp = $this->formatValue($types[$key], $format[$key], $value); if (isset($sql)) $sql .= ", \"{$key}\"={$tmp}"; - else $sql = "UPDATE \"{$table}\" SET \"{$key}\"={$tmp}"; + else $sql = "UPDATE {$schema}\"{$table}\" SET \"{$key}\"={$tmp}"; } $first = true; foreach ($keyarr as $k => $v) { @@ -1736,11 +1743,14 @@ class Postgres extends ADODB_base { * @return -1 invalid parameters */ function insertRow($table, $vars, $nulls, $format, $types) { + if (!is_array($vars) || !is_array($nulls) || !is_array($format) || !is_array($types)) return -1; else { $this->fieldClean($table); + $schema = $this->schema(); + // Build clause if (sizeof($vars) > 0) { $fields = ''; @@ -1753,7 +1763,7 @@ class Postgres extends ADODB_base { else $tmp = $this->formatValue($types[$key], $format[$key], $value); if ($fields) $fields .= ", \"{$key}\""; - else $fields = "INSERT INTO \"{$table}\" (\"{$key}\""; + else $fields = "INSERT INTO {$schema}\"{$table}\" (\"{$key}\""; if ($values) $values .= ", {$tmp}"; else $values = ") VALUES ({$tmp}"; @@ -1773,7 +1783,9 @@ class Postgres extends ADODB_base { function browseRow($table, $key) { $this->fieldClean($table); - $sql = "SELECT * FROM \"{$table}\""; + $schema = $this->schema(); + + $sql = "SELECT * FROM {$schema}\"{$table}\""; if (is_array($key) && sizeof($key) > 0) { $sql .= " WHERE true"; foreach ($key as $k => $v) { @@ -1961,7 +1973,9 @@ class Postgres extends ADODB_base { $this->clean($startvalue); $this->clean($cachevalue); - $sql = "CREATE SEQUENCE \"{$sequence}\""; + $schema = $this->schema(); + + $sql = "CREATE SEQUENCE {$schema}\"{$sequence}\""; if ($increment != '') $sql .= " INCREMENT {$increment}"; if ($minvalue != '') $sql .= " MINVALUE {$minvalue}"; if ($maxvalue != '') $sql .= " MAXVALUE {$maxvalue}"; @@ -2266,7 +2280,9 @@ class Postgres extends ADODB_base { $this->fieldArrayClean($tfields); $this->fieldClean($name); - $sql = "ALTER TABLE \"{$table}\" ADD "; + $schema = $this->schema(); + + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD "; if ($name != '') $sql .= "CONSTRAINT \"{$name}\" "; $sql .= "FOREIGN KEY (\"" . join('","', $sfields) . "\") "; $sql .= "REFERENCES "; @@ -2344,9 +2360,11 @@ class Postgres extends ADODB_base { $this->fieldClean($name); $this->fieldClean($table); + $schema = $this->schema(); + $sql = "CREATE"; if ($unique) $sql .= " UNIQUE"; - $sql .= " INDEX \"{$name}\" ON \"{$table}\" USING {$type} "; + $sql .= " INDEX \"{$name}\" ON {$schema}\"{$table}\" USING {$type} "; if (is_array($columns)) { $this->arrayClean($columns); @@ -2458,9 +2476,11 @@ class Postgres extends ADODB_base { $this->fieldClean($table); if (!in_array($event, $this->rule_events)) return -1; + $schema = $this->schema(); + $sql = "CREATE"; if ($replace) $sql .= " OR REPLACE"; - $sql .= " RULE \"{$name}\" AS ON {$event} TO \"{$table}\""; + $sql .= " RULE \"{$name}\" AS ON {$event} TO {$schema}\"{$table}\""; // Can't escape WHERE clause if ($where != '') $sql .= " WHERE {$where}"; $sql .= " DO"; @@ -2566,7 +2586,7 @@ class Postgres extends ADODB_base { $sql = "CREATE "; if ($replace) $sql .= "OR REPLACE "; - $sql .= "VIEW \"{$viewname}\" AS {$definition}"; + $sql .= "VIEW ". $this->schema() ."\"{$viewname}\" AS {$definition}"; $status = $this->execute($sql); if ($status) { @@ -2594,7 +2614,7 @@ class Postgres extends ADODB_base { function dropView($viewname, $cascade) { $this->fieldClean($viewname); - $sql = "DROP VIEW \"{$viewname}\""; + $sql = "DROP VIEW ". $this->schema() ."\"{$viewname}\""; if ($cascade) $sql .= " CASCADE"; return $this->execute($sql); @@ -2789,7 +2809,9 @@ class Postgres extends ADODB_base { $opr = $this->getOperator($operator_oid); $this->fieldClean($opr->fields['oprname']); - $sql = "DROP OPERATOR {$opr->fields['oprname']} ("; + $schema = $this->schema(); + + $sql = "DROP OPERATOR {$schema}{$opr->fields['oprname']} ("; // Quoting or formatting here??? if ($opr->fields['oprleftname'] !== null) $sql .= $opr->fields['oprleftname'] . ', '; else $sql .= "NONE, "; @@ -3095,8 +3117,10 @@ class Postgres extends ADODB_base { $this->fieldClean($typin); $this->fieldClean($typout); + $schema = $this->schema(); + $sql = " - CREATE TYPE \"{$typname}\" ( + CREATE TYPE {$schema}\"{$typname}\" ( INPUT = \"{$typin}\", OUTPUT = \"{$typout}\", INTERNALLENGTH = {$typlen}"; @@ -3183,7 +3207,10 @@ class Postgres extends ADODB_base { } // Table name - $tgdef .= " ON \"{$trigger['relname']}\" "; + $tgdef .= " ON "; + if($this->hasSchemas()) + $tgdef .= "\"{$this->_schema}\"."; + $tgdef .= "\"{$trigger['relname']}\" "; // Deferrability if ($trigger['tgisconstraint']) { @@ -3472,6 +3499,7 @@ class Postgres extends ADODB_base { (!$public && sizeof($usernames) == 0 && sizeof($groupnames) == 0)) return -4; if ($mode != 'GRANT' && $mode != 'REVOKE') return -5; + $schema = $this->schema(); $sql = $mode; // Grant option @@ -3488,7 +3516,7 @@ class Postgres extends ADODB_base { case 'view': case 'sequence': $this->fieldClean($object); - $sql .= " \"{$object}\""; + $sql .= " {$schema}\"{$object}\""; break; case 'database': $this->fieldClean($object); @@ -3498,7 +3526,7 @@ class Postgres extends ADODB_base { // Function comes in with $object as function OID $fn = $this->getFunction($object); $this->fieldClean($fn->fields['proname']); - $sql .= " FUNCTION \"{$fn->fields['proname']}\"({$fn->fields['proarguments']})"; + $sql .= " FUNCTION {$schema}\"{$fn->fields['proname']}\"({$fn->fields['proarguments']})"; break; case 'language': $this->fieldClean($object); @@ -3768,6 +3796,7 @@ class Postgres extends ADODB_base { // Comment on the function $this->fieldClean($newname); $this->clean($comment); + $status = $this->setComment('FUNCTION', "\"{$newname}\"({$args})", null, $comment); if ($status != 0) { $this->rollbackTransaction(); @@ -3944,7 +3973,8 @@ class Postgres extends ADODB_base { $this->beginTransaction(); - $sql = "CREATE AGGREGATE \"{$name}\" (BASETYPE = \"{$basetype}\", SFUNC = \"{$sfunc}\", STYPE = \"{$stype}\""; + $schema = $this->schema(); + $sql = "CREATE AGGREGATE {$schema}\"{$name}\" (BASETYPE = \"{$basetype}\", SFUNC = \"{$sfunc}\", STYPE = \"{$stype}\""; if(trim($ffunc) != '') $sql .= ", FINALFUNC = \"{$ffunc}\""; if(trim($initcond) != '') $sql .= ", INITCOND = \"{$initcond}\""; if(trim($sortop) != '') $sql .= ", SORTOP = \"{$sortop}\""; @@ -3984,9 +4014,6 @@ class Postgres extends ADODB_base { return $this->execute($sql); } - - - // Operator Class functions /** @@ -4056,20 +4083,23 @@ class Postgres extends ADODB_base { function setComment($obj_type, $obj_name, $table, $comment, $basetype = NULL) { $sql = "COMMENT ON {$obj_type} " ; + $schema = $this->schema(); + switch ($obj_type) { case 'TABLE': - $sql .= "\"{$table}\" IS "; + $sql .= "{$schema}\"{$table}\" IS "; break; case 'COLUMN': - $sql .= "\"{$table}\".\"{$obj_name}\" IS "; + $sql .= "{$schema}\"{$table}\".\"{$obj_name}\" IS "; break; + case 'SEQUENCE': + case 'VIEW': + case 'TYPE': + $sql .= "{$schema}"; case 'DATABASE': case 'ROLE': case 'SCHEMA': - case 'SEQUENCE': case 'TABLESPACE': - case 'TYPE': - case 'VIEW': case 'TEXT SEARCH CONFIGURATION': case 'TEXT SEARCH DICTIONARY': case 'TEXT SEARCH TEMPLATE': @@ -4077,10 +4107,10 @@ class Postgres extends ADODB_base { $sql .= "\"{$obj_name}\" IS "; break; case 'FUNCTION': - $sql .= "{$obj_name} IS "; + $sql .= "{$schema}{$obj_name} IS "; break; case 'AGGREGATE': - $sql .= "\"{$obj_name}\" (\"{$basetype}\") IS "; + $sql .= "{$schema}\"{$obj_name}\" (\"{$basetype}\") IS "; break; default: // Unknown object type diff --git a/classes/database/Postgres72.php b/classes/database/Postgres72.php index 16d64b8c..d6898717 100644 --- a/classes/database/Postgres72.php +++ b/classes/database/Postgres72.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres72.php,v 1.93 2007/12/11 14:17:17 ioguix Exp $ + * $Id: Postgres72.php,v 1.94 2007/12/12 04:11:10 xzilla Exp $ */ @@ -351,7 +351,9 @@ class Postgres72 extends Postgres71 { $this->fieldClean($name); $this->fieldClean($tablespace); - $sql = "ALTER TABLE \"{$table}\" ADD "; + $schema = $this->schema(); + + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD "; if ($name != '') $sql .= "CONSTRAINT \"{$name}\" "; $sql .= "UNIQUE (\"" . join('","', $fields) . "\")"; @@ -377,7 +379,9 @@ class Postgres72 extends Postgres71 { $this->fieldClean($name); $this->fieldClean($tablespace); - $sql = "ALTER TABLE \"{$table}\" ADD "; + $schema = $this->schema(); + + $sql = "ALTER TABLE {$schema}\"{$table}\" ADD "; if ($name != '') $sql .= "CONSTRAINT \"{$name}\" "; $sql .= "PRIMARY KEY (\"" . join('","', $fields) . "\")"; diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php index 31bc7661..ae054877 100644 --- a/classes/database/Postgres73.php +++ b/classes/database/Postgres73.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres73.php,v 1.180 2007/12/11 14:17:17 ioguix Exp $ + * $Id: Postgres73.php,v 1.181 2007/12/12 04:11:10 xzilla Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -62,6 +62,13 @@ class Postgres73 extends Postgres72 { return $this->help_page; } + /** + * Returns the current schema to prepend on object names + */ + function schema() { + return "\"{$this->_schema}\"."; + } + // Schema functions /** @@ -218,7 +225,6 @@ class Postgres73 extends Postgres72 { * @param $comment The new comment for this schema * @return 0 success */ - function updateSchema($schemaname, $comment, $name) { $this->fieldClean($schemaname); $this->fieldClean($name); @@ -327,6 +333,74 @@ class Postgres73 extends Postgres72 { } } + /** + * Protected method which alter a table + * SHOULDN'T BE CALLED OUTSIDE OF A TRANSACTION + * @param $tblrs The table recordSet returned by getTable() + * @param $name The new name for the table + * @param $owner The new owner for the table + * @param $schema The new schema for the table + * @param $comment The comment on the table + * @param $tablespace The new tablespace for the table ('' means leave as is) + * @return 0 success + * @return -3 rename error + * @return -4 comment error + * @return -5 owner error + * @return -6 tablespace error + * @return -7 schema error + */ + /* protected */ + function _alterTable($tblrs, $name, $owner, $schema, $comment, $tablespace) { + + $this->fieldClean($name); + $this->fieldClean($owner); + $this->clean($comment); + /* $schema, $owner, $tablespace not supported in pg70 */ + + $table = $tblrs->fields['relname']; + + // Comment + $status = $this->setComment('TABLE', '', $table, $comment); + if ($status != 0) return -4; + + // Rename (only if name has changed) + if ($name != $table) { + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" RENAME TO \"{$name}\""; + $status = $this->execute($sql); + if ($status != 0) + return -3; + $table = $name; + } + + // Owner + if (!empty($owner) && ($tblrs->fields['relowner'] != $owner)) { + // If owner has been changed, then do the alteration. We are + // careful to avoid this generally as changing owner is a + // superuser only function. + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" OWNER TO \"{$owner}\""; + + $status = $this->execute($sql); + if ($status != 0) return -5; + } + + return 0; + } + + /** + * Removes a table from the database + * @param $table The table to drop + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropTable($table, $cascade) { + $this->fieldClean($table); + + $sql = "DROP TABLE \"{$this->_schema}\".\"{$table}\""; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + /** * Given an array of attnums and a relation, returns an array mapping * attribute number to attribute name. @@ -527,6 +601,37 @@ class Postgres73 extends Postgres72 { return $this->selectSet($sql); } + /** + * Sets default value of a column + * @param $table The table from which to drop + * @param $column The column name to set + * @param $default The new default value + * @return 0 success + */ + function setColumnDefault($table, $column, $default) { + $this->fieldClean($table); + $this->fieldClean($column); + + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" ALTER COLUMN \"{$column}\" SET DEFAULT {$default}"; + + return $this->execute($sql); + } + + /** + * Drops default value of a column + * @param $table The table from which to drop + * @param $column The column name to drop default + * @return 0 success + */ + function dropColumnDefault($table, $column) { + $this->fieldClean($table); + $this->fieldClean($column); + + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" ALTER COLUMN \"{$column}\" DROP DEFAULT"; + + return $this->execute($sql); + } + /** * Drops a column from a table * @param $table The table from which to drop a column @@ -537,13 +642,30 @@ class Postgres73 extends Postgres72 { function dropColumn($table, $column, $cascade) { $this->fieldClean($table); $this->fieldClean($column); - - $sql = "ALTER TABLE \"{$table}\" DROP COLUMN \"{$column}\""; + + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" DROP COLUMN \"{$column}\""; if ($cascade) $sql .= " CASCADE"; return $this->execute($sql); } + /** + * Renames a column in a table + * @param $table The table containing the column to be renamed + * @param $column The column to be renamed + * @param $newName The new name for the column + * @return 0 success + */ + function renameColumn($table, $column, $newName) { + $this->fieldClean($table); + $this->fieldClean($column); + $this->fieldClean($newName); + + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" RENAME COLUMN \"{$column}\" TO \"{$newName}\""; + + return $this->execute($sql); + } + /** * Sets whether or not a column can contain NULLs * @param $table The table that contains the column @@ -555,7 +677,132 @@ class Postgres73 extends Postgres72 { $this->fieldClean($table); $this->fieldClean($column); - $sql = "ALTER TABLE \"{$table}\" ALTER COLUMN \"{$column}\" " . (($state) ? 'DROP' : 'SET') . " NOT NULL"; + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" ALTER COLUMN \"{$column}\" " . (($state) ? 'DROP' : 'SET') . " NOT NULL"; + + return $this->execute($sql); + } + + // Row functions + + /** + * Empties a table in the database + * @param $table The table to be emptied + * @return 0 success + */ + function emptyTable($table) { + $this->fieldClean($table); + + $sql = "DELETE FROM \"{$this->_schema}\".\"{$table}\""; + + return $this->execute($sql); + } + + /** + * Adds a check constraint to a table + * @param $table The table to which to add the check + * @param $definition The definition of the check + * @param $name (optional) The name to give the check, otherwise default name is assigned + * @return 0 success + */ + function addCheckConstraint($table, $definition, $name = '') { + $this->fieldClean($table); + $this->fieldClean($name); + // @@ How the heck do you clean a definition??? + + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" ADD "; + if ($name != '') $sql .= "CONSTRAINT \"{$name}\" "; + $sql .= "CHECK ({$definition})"; + + return $this->execute($sql); + } + + /** + * Drops a check constraint from a table + * @param $table The table from which to drop the check + * @param $name The name of the check to be dropped + * @return 0 success + * @return -2 transaction error + * @return -3 lock error + * @return -4 check drop error + */ + function dropCheckConstraint($table, $name) { + $this->clean($table); + $this->clean($name); + + // Begin transaction + $status = $this->beginTransaction(); + if ($status != 0) return -2; + + // Properly lock the table + $sql = "LOCK TABLE \"{$this->_schema}\".\"{$table}\" IN ACCESS EXCLUSIVE MODE"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -3; + } + + // Delete the check constraint + $sql = "DELETE FROM pg_relcheck WHERE rcrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}' + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE + nspname = '{$this->_schema}')) AND rcname='{$name}'"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -4; + } + + // Update the pg_class catalog to reflect the new number of checks + $sql = "UPDATE pg_class SET relchecks=(SELECT COUNT(*) FROM pg_relcheck WHERE + rcrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}' + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE + nspname = '{$this->_schema}'))) + WHERE relname='{$table}'"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -4; + } + + // Otherwise, close the transaction + return $this->endTransaction(); + } + + // Administration functions + + /** + * Vacuums a database + * @param $table The table to vacuum + * @param $analyze If true, also does analyze + * @param $full If true, selects "full" vacuum (PostgreSQL >= 7.2) + * @param $freeze If true, selects aggressive "freezing" of tuples (PostgreSQL >= 7.2) + */ + function vacuumDB($table = '', $analyze = false, $full = false, $freeze = false) { + + $sql = "VACUUM"; + if ($full) $sql .= " FULL"; + if ($freeze) $sql .= " FREEZE"; + if ($analyze) $sql .= " ANALYZE"; + if ($table != '') { + $this->fieldClean($table); + $sql .= " \"{$this->_schema}\".\"{$table}\""; + } + + return $this->execute($sql); + } + + /** + * Analyze a database + * @note PostgreSQL 7.2 finally had an independent ANALYZE command + * @param $table (optional) The table to analyze + */ + function analyzeDB($table = '') { + if ($table != '') { + $this->fieldClean($table); + + $sql = "ANALYZE \"{$this->_schema}\".\"{$table}\""; + } + else + $sql = "ANALYZE"; return $this->execute($sql); } @@ -611,7 +858,72 @@ class Postgres73 extends Postgres72 { return $this->selectSet($sql); } - // View functions + /** + * Alters a view + * @param $view The name of the view + * @param $name The new name for the view + * @param $owner The new owner for the view + * @param $comment The comment on the view + * @return 0 success + * @return -1 transaction error + * @return -2 owner error + * @return -3 rename error + * @return -4 comment error + * @return -5 get existing view error + */ + function alterView($view, $name, $owner, $comment) { + $this->fieldClean($view); + $this->fieldClean($name); + $this->fieldClean($owner); + $this->clean($comment); + + $status = $this->beginTransaction(); + if ($status != 0) { + $this->rollbackTransaction(); + return -1; + } + + // Comment + $status = $this->setComment('VIEW', $view, '', $comment); + if ($status != 0) { + $this->rollbackTransaction(); + return -4; + } + + // Owner + if ($this->hasAlterTableOwner() && $owner != '') { + // Fetch existing owner + $data = $this->getView($view); + if ($data->recordCount() != 1) { + $this->rollbackTransaction(); + return -5; + } + + // If owner has been changed, then do the alteration. We are + // careful to avoid this generally as changing owner is a + // superuser only function. + if ($data->fields['relowner'] != $owner) { + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$view}\" OWNER TO \"{$owner}\""; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -2; + } + } + } + + // Rename (only if name has changed) + if ($name != $view) { + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$view}\" RENAME TO \"{$name}\""; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -3; + } + } + + return $this->endTransaction(); + } /** * Returns a list of all views in the database @@ -674,7 +986,7 @@ class Postgres73 extends Postgres72 { $this->fieldClean($sequence); $this->clean($sequence); - $sql = "SELECT pg_catalog.SETVAL('\"{$sequence}\"', {$minvalue})"; + $sql = "SELECT pg_catalog.SETVAL('\"{$this->_schema}\".\"{$sequence}\"', {$minvalue})"; return $this->execute($sql); } @@ -690,7 +1002,7 @@ class Postgres73 extends Postgres72 { $this->fieldClean($sequence); $this->clean($sequence); - $sql = "SELECT pg_catalog.NEXTVAL('\"{$sequence}\"')"; + $sql = "SELECT pg_catalog.NEXTVAL('\"{$this->_schema}\".\"{$sequence}\"')"; return $this->execute($sql); } @@ -708,7 +1020,7 @@ class Postgres73 extends Postgres72 { $this->clean($sequence); $this->clean($nextvalue); - $sql = "SELECT pg_catalog.SETVAL('\"{$sequence}\"', '{$nextvalue}')"; + $sql = "SELECT pg_catalog.SETVAL('\"{$this->_schema}\".\"{$sequence}\"', '{$nextvalue}')"; return $this->execute($sql); } @@ -755,6 +1067,94 @@ class Postgres73 extends Postgres72 { return $this->selectSet( $sql ); } + /** + * Rename a sequence + * @param $sequence The sequence name + * @param $name The new name for the sequence + * @return 0 success + */ + function renameSequence($sequence, $name) { + $this->fieldClean($name); + $this->fieldClean($sequence); + + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$sequence}\" RENAME TO \"{$name}\""; + return $this->execute($sql); + } + + /** + * Protected method which alter a sequence + * SHOULDN'T BE CALLED OUTSIDE OF A TRANSACTION + * @param $seqrs The sequence recordSet returned by getSequence() + * @param $name The new name for the sequence + * @param $comment The comment on the sequence + * @param $owner The new owner for the sequence + * @param $schema The new schema for the sequence + * @param $increment The increment + * @param $minvalue The min value + * @param $maxvalue The max value + * @param $startvalue The starting value + * @param $cachevalue The cache value + * @param $cycledvalue True if cycled, false otherwise + * @return 0 success + * @return -3 rename error + * @return -4 comment error + * @return -5 owner error + * @return -7 schema error + */ + /*protected*/ + function _alterSequence($seqrs, $name, $comment, $owner, $schema, $increment, + $minvalue, $maxvalue, $startvalue, $cachevalue, $cycledvalue) { + + $sequence = $seqrs->fields['seqname']; + $this->fieldClean($name); + $this->clean($comment); + $this->fieldClean($owner); + + // Comment + $status = $this->setComment('SEQUENCE', $sequence, '', $comment); + if ($status != 0) + return -4; + + // Rename (only if name has changed) + if ($name != $sequence) { + $status = $this->renameSequence($sequence, $name); + if ($status != 0) + return -3; + $sequence = $name; + } + + // Owner + if (!empty($owner)) { + + // If owner has been changed, then do the alteration. We are + // careful to avoid this generally as changing owner is a + // superuser only function. + if ($seqrs->fields['seqowner'] != $owner) { + $sql = "ALTER TABLE \"{$sequence}\" OWNER TO \"{$owner}\""; + $status = $this->execute($sql); + if ($status != 0) + return -5; + } + } + + return 0; + } + + /** + * Drops a given sequence + * @param $sequence Sequence name + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropSequence($sequence, $cascade) { + $this->fieldClean($sequence); + + $sql = "DROP SEQUENCE \"{$this->_schema}\".\"{$sequence}\""; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + /** * Grabs a list of indexes for a table * @param $table The name of a table whose indexes to retrieve @@ -777,6 +1177,63 @@ class Postgres73 extends Postgres72 { return $this->selectSet($sql); } + /** + * Removes an index from the database + * @param $index The index to drop + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropIndex($index, $cascade) { + $this->fieldClean($index); + + $sql = "DROP INDEX \"{$this->_schema}\".\"{$index}\""; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + + /** + * Clusters an index + * @param $index The name of the index + * @param $table The table the index is on + * @return 0 success + */ + function clusterIndex($index, $table) { + $this->fieldClean($index); + $this->fieldClean($table); + + // We don't bother with a transaction here, as there's no point rolling + // back an expensive cluster if a cheap analyze fails for whatever reason + $sql = "CLUSTER \"{$index}\" ON \"{$this->_schema}\".\"{$table}\""; + + return $this->execute($sql); + } + + /** + * Rebuild indexes + * @param $type 'DATABASE' or 'TABLE' or 'INDEX' + * @param $name The name of the specific database, table, or index to be reindexed + * @param $force If true, recreates indexes forcedly in PostgreSQL 7.0-7.1, forces rebuild of system indexes in 7.2-7.3, ignored in >=7.4 + */ + function reindex($type, $name, $force = false) { + $this->fieldClean($name); + switch($type) { + case 'DATABASE': + $sql = "REINDEX {$type} \"{$name}\""; + if ($force) $sql .= ' FORCE'; + break; + case 'TABLE': + case 'INDEX': + $sql = "REINDEX {$type} \"{$this->_schema}\".\"{$name}\""; + if ($force) $sql .= ' FORCE'; + break; + default: + return -1; + } + + return $this->execute($sql); + } + /** * Grabs a single trigger * @param $table The name of a table whose triggers to retrieve @@ -825,6 +1282,46 @@ class Postgres73 extends Postgres72 { return $this->selectSet($sql); } + /** + * Creates a trigger + * @param $tgname The name of the trigger to create + * @param $table The name of the table + * @param $tgproc The function to execute + * @param $tgtime BEFORE or AFTER + * @param $tgevent Event + * @param $tgargs The function arguments + * @return 0 success + */ + function createTrigger($tgname, $table, $tgproc, $tgtime, $tgevent, $tgfrequency, $tgargs) { + $this->fieldClean($tgname); + $this->fieldClean($table); + $this->fieldClean($tgproc); + + /* No Statement Level Triggers in PostgreSQL (by now) */ + $sql = "CREATE TRIGGER \"{$tgname}\" {$tgtime} + {$tgevent} ON \"{$this->_schema}\".\"{$table}\" + FOR EACH {$tgfrequency} EXECUTE PROCEDURE \"{$tgproc}\"({$tgargs})"; + + return $this->execute($sql); + } + + /** + * Drops a trigger + * @param $tgname The name of the trigger to drop + * @param $table The table from which to drop the trigger + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropTrigger($tgname, $table, $cascade) { + $this->fieldClean($tgname); + $this->fieldClean($table); + + $sql = "DROP TRIGGER \"{$tgname}\" ON \"{$this->_schema}\".\"{$table}\""; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + /** * Alters a trigger * @param $table The name of the table containing the trigger @@ -837,7 +1334,7 @@ class Postgres73 extends Postgres72 { $this->fieldClean($trigger); $this->fieldClean($name); - $sql = "ALTER TRIGGER \"{$trigger}\" ON \"{$table}\" RENAME TO \"{$name}\""; + $sql = "ALTER TRIGGER \"{$trigger}\" ON \"{$this->_schema}\".\"{$table}\" RENAME TO \"{$name}\""; return $this->execute($sql); } @@ -986,7 +1483,7 @@ class Postgres73 extends Postgres72 { $sql = "CREATE"; if ($replace) $sql .= " OR REPLACE"; - $sql .= " FUNCTION \"{$funcname}\" ("; + $sql .= " FUNCTION \"{$this->_schema}\".\"{$funcname}\" ("; if ($args != '') $sql .= $args; @@ -1019,6 +1516,23 @@ class Postgres73 extends Postgres72 { return $this->execute($sql); } + /** + * Drops a function. + * @param $function_oid The OID of the function to drop + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropFunction($function_oid, $cascade) { + // Function comes in with $object as function OID + $fn = $this->getFunction($function_oid); + $this->fieldClean($fn->fields['proname']); + + $sql = "DROP FUNCTION \"{$this->_schema}\".\"{$fn->fields['proname']}\"({$fn->fields['proarguments']})"; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + // Type functions /** @@ -1063,6 +1577,21 @@ class Postgres73 extends Postgres72 { return $this->selectSet($sql); } + /** + * Drops a type. + * @param $typname The name of the type to drop + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropType($typname, $cascade) { + $this->fieldClean($typname); + + $sql = "DROP TYPE \"{$this->_schema}\".\"{$typname}\""; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + /** * Creates a new composite type in the database * @param $name The name of the type @@ -1086,7 +1615,7 @@ class Postgres73 extends Postgres72 { $found = false; $first = true; $comment_sql = ''; // Accumulate comments for the columns - $sql = "CREATE TYPE \"{$name}\" AS ("; + $sql = "CREATE TYPE \"{$this->_schema}\".\"{$name}\" AS ("; for ($i = 0; $i < $fields; $i++) { $this->fieldClean($field[$i]); $this->clean($type[$i]); @@ -1123,7 +1652,7 @@ class Postgres73 extends Postgres72 { // Add array qualifier if necessary if ($array[$i] == '[]') $sql .= '[]'; - if ($colcomment[$i] != '') $comment_sql .= "COMMENT ON COLUMN \"{$name}\".\"{$field[$i]}\" IS '{$colcomment[$i]}';\n"; + if ($colcomment[$i] != '') $comment_sql .= "COMMENT ON COLUMN \"{$this->_schema}\".\"{$name}\".\"{$field[$i]}\" IS '{$colcomment[$i]}';\n"; $found = true; } @@ -1170,7 +1699,7 @@ class Postgres73 extends Postgres72 { $this->fieldClean($rule); $this->fieldClean($relation); - $sql = "DROP RULE \"{$rule}\" ON \"{$relation}\""; + $sql = "DROP RULE \"{$rule}\" ON \"{$this->_schema}\".\"{$relation}\""; if ($cascade) $sql .= " CASCADE"; return $this->execute($sql); @@ -1366,7 +1895,7 @@ class Postgres73 extends Postgres72 { $this->fieldClean($constraint); $this->fieldClean($relation); - $sql = "ALTER TABLE \"{$relation}\" DROP CONSTRAINT \"{$constraint}\""; + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$relation}\" DROP CONSTRAINT \"{$constraint}\""; if ($cascade) $sql .= " CASCADE"; return $this->execute($sql); @@ -1523,7 +2052,7 @@ class Postgres73 extends Postgres72 { function createDomain($domain, $type, $length, $array, $notnull, $default, $check) { $this->fieldClean($domain); - $sql = "CREATE DOMAIN \"{$domain}\" AS "; + $sql = "CREATE DOMAIN \"{$this->_schema}\".\"{$domain}\" AS "; if ($length == '') $sql .= $type; @@ -1565,7 +2094,7 @@ class Postgres73 extends Postgres72 { function dropDomain($domain, $cascade) { $this->fieldClean($domain); - $sql = "DROP DOMAIN \"{$domain}\""; + $sql = "DROP DOMAIN \"{$this->_schema}\".\"{$domain}\""; if ($cascade) $sql .= " CASCADE"; return $this->execute($sql); @@ -1937,6 +2466,23 @@ class Postgres73 extends Postgres72 { return $this->selectSet($sql); } + /** + * Removes an aggregate function from the database + * @param $aggrname The name of the aggregate + * @param $aggrtype The input data type of the aggregate + * @param $cascade True to cascade drop, false to restrict + * @return 0 success + */ + function dropAggregate($aggrname, $aggrtype, $cascade) { + $this->fieldClean($aggrname); + $this->fieldClean($aggrtype); + + $sql = "DROP AGGREGATE \"{$this->_schema}\".\"{$aggrname}\" (\"{$aggrtype}\")"; + if ($cascade) $sql .= " CASCADE"; + + return $this->execute($sql); + } + // Query functions /** diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php index af0adcd8..08b4e024 100644 --- a/classes/database/Postgres74.php +++ b/classes/database/Postgres74.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres74.php,v 1.69 2007/12/11 14:17:17 ioguix Exp $ + * $Id: Postgres74.php,v 1.70 2007/12/12 04:11:10 xzilla Exp $ */ include_once('./classes/database/Postgres73.php'); @@ -222,7 +222,7 @@ class Postgres74 extends Postgres73 { $status = $this->beginTransaction(); if ($status != 0) return -1; - $sql = "CREATE TABLE \"{$name}\" (LIKE {$like}"; + $sql = "CREATE TABLE \"{$this->_schema}\".\"{$name}\" (LIKE {$like}"; if ($defaults) $sql .= " INCLUDING DEFAULTS"; if ($this->hasCreateTableLikeWithConstraints() && $constraints) $sql .= " INCLUDING CONSTRAINTS"; @@ -366,7 +366,7 @@ class Postgres74 extends Postgres73 { function recluster($table = '') { if ($table != '') { $this->fieldClean($table); - $sql = "CLUSTER \"{$table}\""; + $sql = "CLUSTER \"{$this->_schema}\".\"{$table}\""; } else $sql = "CLUSTER"; @@ -413,7 +413,7 @@ class Postgres74 extends Postgres73 { $this->fieldClean($domain); $this->fieldClean($constraint); - $sql = "ALTER DOMAIN \"{$domain}\" DROP CONSTRAINT \"{$constraint}\""; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" DROP CONSTRAINT \"{$constraint}\""; if ($cascade) $sql .= " CASCADE"; return $this->execute($sql); @@ -430,7 +430,7 @@ class Postgres74 extends Postgres73 { $this->fieldClean($domain); $this->fieldClean($name); - $sql = "ALTER DOMAIN \"{$domain}\" ADD "; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" ADD "; if ($name != '') $sql .= "CONSTRAINT \"{$name}\" "; $sql .= "CHECK ({$definition})"; @@ -461,9 +461,9 @@ class Postgres74 extends Postgres73 { // Default if ($domdefault == '') - $sql = "ALTER DOMAIN \"{$domain}\" DROP DEFAULT"; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" DROP DEFAULT"; else - $sql = "ALTER DOMAIN \"{$domain}\" SET DEFAULT {$domdefault}"; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" SET DEFAULT {$domdefault}"; $status = $this->execute($sql); if ($status != 0) { @@ -473,9 +473,9 @@ class Postgres74 extends Postgres73 { // NOT NULL if ($domnotnull) - $sql = "ALTER DOMAIN \"{$domain}\" SET NOT NULL"; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" SET NOT NULL"; else - $sql = "ALTER DOMAIN \"{$domain}\" DROP NOT NULL"; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" DROP NOT NULL"; $status = $this->execute($sql); if ($status != 0) { @@ -484,7 +484,7 @@ class Postgres74 extends Postgres73 { } // Owner - $sql = "ALTER DOMAIN \"{$domain}\" OWNER TO \"{$domowner}\""; + $sql = "ALTER DOMAIN \"{$this->_schema}\".\"{$domain}\" OWNER TO \"{$domowner}\""; $status = $this->execute($sql); if ($status != 0) { @@ -595,7 +595,7 @@ class Postgres74 extends Postgres73 { // Rename the function, if necessary $this->fieldClean($newname); if ($funcname != $newname) { - $sql = "ALTER FUNCTION \"{$funcname}\"({$args}) RENAME TO \"{$newname}\""; + $sql = "ALTER FUNCTION \"{$this->_schema}\".\"{$funcname}\"({$args}) RENAME TO \"{$newname}\""; $status = $this->execute($sql); if ($status != 0) { $this->rollbackTransaction(); @@ -609,7 +609,7 @@ class Postgres74 extends Postgres73 { if ($this->hasFunctionAlterOwner()) { $this->fieldClean($newown); if ($funcown != $newown) { - $sql = "ALTER FUNCTION \"{$funcname}\"({$args}) OWNER TO \"{$newown}\""; + $sql = "ALTER FUNCTION \"{$this->_schema}\".\"{$funcname}\"({$args}) OWNER TO \"{$newown}\""; $status = $this->execute($sql); if ($status != 0) { $this->rollbackTransaction(); @@ -623,7 +623,7 @@ class Postgres74 extends Postgres73 { if ($this->hasFunctionAlterSchema()) { $this->fieldClean($newschema); if ($funcschema != $newschema) { - $sql = "ALTER FUNCTION \"{$funcname}\"({$args}) SET SCHEMA \"{$newschema}\""; + $sql = "ALTER FUNCTION \"{$this->_schema}\".\"{$funcname}\"({$args}) SET SCHEMA \"{$newschema}\""; $status = $this->execute($sql); if ($status != 0) { $this->rollbackTransaction(); @@ -688,7 +688,7 @@ class Postgres74 extends Postgres73 { if (!is_null($cycledvalue)) $sql .= (!$cycledvalue ? ' NO ' : '') . " CYCLE"; if ($sql != '') { - $sql = "ALTER SEQUENCE \"{$sequence}\" $sql"; + $sql = "ALTER SEQUENCE \"{$this->_schema}\".\"{$sequence}\" $sql"; $status = $this->execute($sql); if ($status != 0) return -6; @@ -777,7 +777,7 @@ class Postgres74 extends Postgres73 { * @return 0 success */ function changeAggregateOwner($aggrname, $aggrtype, $newaggrowner) { - $sql = "ALTER AGGREGATE \"{$aggrname}\" (\"{$aggrtype}\") OWNER TO \"{$newaggrowner}\""; + $sql = "ALTER AGGREGATE \"{$this->_schema}\".\"{$aggrname}\" (\"{$aggrtype}\") OWNER TO \"{$newaggrowner}\""; return $this->execute($sql); } @@ -789,7 +789,7 @@ class Postgres74 extends Postgres73 { * @return 0 success */ function changeAggregateSchema($aggrname, $aggrtype, $newaggrschema) { - $sql = "ALTER AGGREGATE \"{$aggrname}\" (\"{$aggrtype}\") SET SCHEMA \"{$newaggrschema}\""; + $sql = "ALTER AGGREGATE \"{$this->_schema}\".\"{$aggrname}\" (\"{$aggrtype}\") SET SCHEMA \"{$newaggrschema}\""; return $this->execute($sql); } diff --git a/classes/database/Postgres80.php b/classes/database/Postgres80.php index 04642bbb..78472601 100644 --- a/classes/database/Postgres80.php +++ b/classes/database/Postgres80.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.0 support * - * $Id: Postgres80.php,v 1.27 2007/11/21 15:45:31 ioguix Exp $ + * $Id: Postgres80.php,v 1.28 2007/12/12 04:11:10 xzilla Exp $ */ include_once('./classes/database/Postgres74.php'); @@ -281,18 +281,18 @@ class Postgres80 extends Postgres74 { // Create the command for changing nullability if ($notnull != $oldnotnull) { - $sql .= "ALTER TABLE \"{$table}\" ALTER COLUMN \"{$column}\" " . (($notnull) ? 'SET' : 'DROP') . " NOT NULL"; + $sql .= "ALTER TABLE \"{$this->_schema}\".\"{$table}\" ALTER COLUMN \"{$column}\" " . (($notnull) ? 'SET' : 'DROP') . " NOT NULL"; } // Add default, if it has changed if ($default != $olddefault) { if ($default == '') { - if ($sql == '') $sql = "ALTER TABLE \"{$table}\" "; + if ($sql == '') $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" "; else $sql .= ", "; $sql .= "ALTER COLUMN \"{$column}\" DROP DEFAULT"; } else { - if ($sql == '') $sql = "ALTER TABLE \"{$table}\" "; + if ($sql == '') $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" "; else $sql .= ", "; $sql .= "ALTER COLUMN \"{$column}\" SET DEFAULT {$default}"; } @@ -324,7 +324,7 @@ class Postgres80 extends Postgres74 { if ($array) $ftype .= '[]'; if ($ftype != $oldtype) { - if ($sql == '') $sql = "ALTER TABLE \"{$table}\" "; + if ($sql == '') $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" "; else $sql .= ", "; $sql .= "ALTER COLUMN \"{$column}\" TYPE {$ftype}"; } diff --git a/classes/database/Postgres81.php b/classes/database/Postgres81.php index fa133a79..339f8ea4 100644 --- a/classes/database/Postgres81.php +++ b/classes/database/Postgres81.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.1 support * - * $Id: Postgres81.php,v 1.18 2007/12/11 14:17:17 ioguix Exp $ + * $Id: Postgres81.php,v 1.19 2007/12/12 04:11:10 xzilla Exp $ */ include_once('./classes/database/Postgres80.php'); @@ -501,7 +501,7 @@ class Postgres81 extends Postgres80 { // If tablespace has been changed, then do the alteration. We // don't want to do this unnecessarily. - $sql = "ALTER TABLE \"{$tablename}\" SET SCHEMA \"{$schema}\""; + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$tablename}\" SET SCHEMA \"{$schema}\""; $status = $this->execute($sql); if ($status != 0) return -7; @@ -520,7 +520,7 @@ class Postgres81 extends Postgres80 { $this->fieldClean($tgname); $this->fieldClean($table); - $sql = "ALTER TABLE \"{$table}\" ENABLE TRIGGER \"{$tgname}\""; + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" ENABLE TRIGGER \"{$tgname}\""; return $this->execute($sql); } @@ -535,7 +535,7 @@ class Postgres81 extends Postgres80 { $this->fieldClean($tgname); $this->fieldClean($table); - $sql = "ALTER TABLE \"{$table}\" DISABLE TRIGGER \"{$tgname}\""; + $sql = "ALTER TABLE \"{$this->_schema}\".\"{$table}\" DISABLE TRIGGER \"{$tgname}\""; return $this->execute($sql); } @@ -617,7 +617,7 @@ class Postgres81 extends Postgres80 { $this->clean($schema); if ($seqrs->fields['nspname'] != $schema) { - $sql = "ALTER SEQUENCE \"{$sequence}\" SET SCHEMA $schema"; + $sql = "ALTER SEQUENCE \"{$this->_schema}\".\"{$sequence}\" SET SCHEMA $schema"; $status = $this->execute($sql); if ($status != 0) return -7; diff --git a/classes/database/Postgres83.php b/classes/database/Postgres83.php index 2352cda7..4520c13c 100644 --- a/classes/database/Postgres83.php +++ b/classes/database/Postgres83.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.3 support * - * $Id: Postgres83.php,v 1.15 2007/12/11 14:17:17 ioguix Exp $ + * $Id: Postgres83.php,v 1.16 2007/12/12 04:11:10 xzilla Exp $ */ include_once('./classes/database/Postgres82.php'); @@ -75,13 +75,13 @@ class Postgres83 extends Postgres82 { function renameView($view, $name) { $this->fieldClean($name); $this->fieldClean($view); - $sql = "ALTER VIEW \"{$view}\" RENAME TO \"{$name}\""; + $sql = "ALTER VIEW \"{$this->_schema}\".\"{$view}\" RENAME TO \"{$name}\""; if ($this->execute($sql) != 0) return -1; return 0; } - // Indexe functions + // Index functions /** * Clusters an index @@ -96,7 +96,7 @@ class Postgres83 extends Postgres82 { // We don't bother with a transaction here, as there's no point rolling // back an expensive cluster if a cheap analyze fails for whatever reason - $sql = "CLUSTER \"{$table}\" USING \"{$index}\""; + $sql = "CLUSTER \"{$this->_schema}\".\"{$table}\" USING \"{$index}\""; return $this->execute($sql); } @@ -113,7 +113,7 @@ class Postgres83 extends Postgres82 { $this->fieldClean($name); $this->fieldClean($sequence); - $sql = "ALTER SEQUENCE \"{$sequence}\" RENAME TO \"{$name}\""; + $sql = "ALTER SEQUENCE \"{$this->_schema}\".\"{$sequence}\" RENAME TO \"{$name}\""; return $this->execute($sql); } @@ -613,7 +613,7 @@ class Postgres83 extends Postgres82 { for ($i = 0; $i < $nbval; $i++) $this->clean($values[$i]); - $sql = "CREATE TYPE \"{$name}\" AS ENUM ('"; + $sql = "CREATE TYPE \"{$this->_schema}\".\"{$name}\" AS ENUM ('"; $sql.= implode("','", $values); $sql .= "')"; @@ -642,7 +642,9 @@ class Postgres83 extends Postgres82 { function getEnumValues($name) { $this->fieldClean($name); - $sql = "SELECT enumlabel AS enumval FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum e ON (t.oid=e.enumtypid) WHERE t.typname = '{$name}' ORDER BY e.oid"; + $sql = "SELECT enumlabel AS enumval + FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum e ON (t.oid=e.enumtypid) + WHERE t.typname = '{$name}' ORDER BY e.oid"; return $this->selectSet($sql); } @@ -670,7 +672,7 @@ class Postgres83 extends Postgres82 { $sql = "CREATE"; if ($replace) $sql .= " OR REPLACE"; - $sql .= " FUNCTION \"{$funcname}\" ("; + $sql .= " FUNCTION \"{$this->_schema}\".\"{$funcname}\" ("; if ($args != '') $sql .= $args; diff --git a/colproperties.php b/colproperties.php index 81284e22..2e69a0ac 100644 --- a/colproperties.php +++ b/colproperties.php @@ -241,10 +241,12 @@ if ($isTable) { $return_url = urlencode("colproperties.php?{$misc->href}&table=$tableName&column={$_REQUEST['column']}"); + $schema = $data->schema(); + /* Browse link */ echo "\t
  • href}&subject=column&table=", urlencode($_REQUEST['table']), "&column=", urlencode($_REQUEST['column']), "&return_url={$return_url}&return_desc=", urlencode($lang['strback']), "&query=", - urlencode("SELECT \"{$_REQUEST['column']}\", count(*) AS \"count\" FROM \"$tableName\" GROUP BY \"{$_REQUEST['column']}\" ORDER BY \"{$_REQUEST['column']}\"") , "\">{$lang['strbrowse']}
    • \n"; + urlencode("SELECT \"{$_REQUEST['column']}\", count(*) AS \"count\" FROM {$schema}\"$tableName\" GROUP BY \"{$_REQUEST['column']}\" ORDER BY \"{$_REQUEST['column']}\"") , "\">{$lang['strbrowse']}\n"; /* Edit link */ echo "\t
  • href}&table=", urlencode($_REQUEST['table']), diff --git a/lang/french.php b/lang/french.php index 0d0c5d04..2c9fc443 100644 --- a/lang/french.php +++ b/lang/french.php @@ -4,7 +4,7 @@ * French Language file for phpPgAdmin. * @maintainer Pascal PEYRE [pascal.peyre@cir.fr] * - * $Id: french.php,v 1.31 2007/10/03 17:32:07 ioguix Exp $ + * $Id: french.php,v 1.32 2007/12/12 04:11:10 xzilla Exp $ */ // Language and character set @@ -140,6 +140,15 @@ $lang['strunselectall'] = 'Desélectionner tout'; $lang['strlocale'] = 'Locale'; + // User-supplied SQL history +$lang['strhistory'] = 'History'; +$lang['strnohistory'] = 'No history.'; +$lang['strclearhistory'] = 'Clear history'; +$lang['strdelhistory'] = 'Delete from history'; +$lang['strconfdelhistory'] = 'Really remove this request from history ?'; +$lang['strconfclearhistory'] = 'Really clear history ?'; +$lang['strnodatabaseselected'] = 'Please, select a database.'; + // Database Sizes $lang['strsize'] = 'Taille'; $lang['strbytes'] = 'octets'; @@ -530,7 +539,9 @@ $lang['strlocale'] = 'Locale'; $lang['strargraise'] = 'Monter.'; $lang['strarglower'] = 'Descendre.'; $lang['strargremoveconfirm'] = 'Êtes-vous sûr de vouloir supprimer cet argument ? cette opération ne peut pas être annulée.'; - +$lang['strfunctioncosting'] = 'Function Costing'; +$lang['strresultrows'] = 'Result Rows'; +$lang['strexecutioncost'] = 'Execution Cost'; // Triggers $lang['strtrigger'] = 'Trigger'; @@ -569,7 +580,6 @@ $lang['strlocale'] = 'Locale'; $lang['strtypeneedsvalue'] = 'Vous devez spécifier au moins une valeur.'; $lang['strtypeneedscols'] = 'Vous devez spécifier un nombre valide de champs.'; $lang['strtypeneedsvals'] = 'Vous devez spécifier un nombre valide de valeurs.'; - $lang['strtypename'] = 'Nom du type'; $lang['strinputfn'] = 'Fonction d\'entrée'; $lang['stroutputfn'] = 'Fonction de sortie'; $lang['strpassbyval'] = 'Passée par valeur ?'; @@ -912,6 +922,7 @@ $lang['strvirtualtransaction'] = 'Virtual Transaction ID'; // Fulltext search $lang['strfulltext'] = 'Full Text Search'; $lang['strftsconfig'] = 'FTS configuration'; + $lang['strftsconfigs'] = 'Configurations'; $lang['strftscreateconfig'] = 'Create FTS configuration'; $lang['strftscreatedict'] = 'Create dictionary'; $lang['strftscreatedicttemplate'] = 'Create dictionary template'; @@ -943,7 +954,7 @@ $lang['strftsdictcreated'] = 'FTS dictionary created'; $lang['strftsdictcreatedbad'] = 'FTS dictionary creation failed.'; $lang['strftslexize'] = 'Lexize'; $lang['strftsinit'] = 'Init'; -$lang['strftsoption'] = 'Option'; +$lang['strftsoptionsvalues'] = 'Options and Values'; $lang['strftsdictneedsname'] = 'You must give a name for your FTS dictionary.'; $lang['strftsdictdropped'] = 'FTS dictionary dropped.'; $lang['strftsdictdroppedbad'] = 'FTS dictionary drop failed.'; @@ -959,6 +970,11 @@ $lang['strftsmappingaltered'] = 'FTS mapping altered.'; $lang['strftsmappingalteredbad'] = 'FTS mapping alter failed.'; $lang['strftsmappingadded'] = 'FTS mapping added.'; $lang['strftsmappingaddedbad'] = 'FTS mapping add failed.'; + $lang['strftsmappingdropped'] = 'FTS mapping dropped.'; + $lang['strftsmappingdroppedbad'] = 'FTS mapping drop failed.'; + $lang['strftstabconfigs'] = 'Configurations'; +$lang['strftstabdicts'] = 'Dictionaries'; +$lang['strftstabparsers'] = 'Parsers'; ?> diff --git a/lang/recoded/french.php b/lang/recoded/french.php index 621249f8..dcade5a1 100644 --- a/lang/recoded/french.php +++ b/lang/recoded/french.php @@ -4,7 +4,7 @@ * French Language file for phpPgAdmin. * @maintainer Pascal PEYRE [pascal.peyre@cir.fr] * - * $Id: french.php,v 1.31 2007/11/15 06:06:46 xzilla Exp $ + * $Id: french.php,v 1.32 2007/12/12 04:11:10 xzilla Exp $ */ // Language and character set @@ -140,6 +140,15 @@ $lang['strunselectall'] = 'Desélectionner tout'; $lang['strlocale'] = 'Locale'; + // User-supplied SQL history +$lang['strhistory'] = 'History'; +$lang['strnohistory'] = 'No history.'; +$lang['strclearhistory'] = 'Clear history'; +$lang['strdelhistory'] = 'Delete from history'; +$lang['strconfdelhistory'] = 'Really remove this request from history ?'; +$lang['strconfclearhistory'] = 'Really clear history ?'; +$lang['strnodatabaseselected'] = 'Please, select a database.'; + // Database Sizes $lang['strsize'] = 'Taille'; $lang['strbytes'] = 'octets'; @@ -530,7 +539,9 @@ $lang['strlocale'] = 'Locale'; $lang['strargraise'] = 'Monter.'; $lang['strarglower'] = 'Descendre.'; $lang['strargremoveconfirm'] = 'Êtes-vous sûr de vouloir supprimer cet argument ? cette opération ne peut pas être annulée.'; - +$lang['strfunctioncosting'] = 'Function Costing'; +$lang['strresultrows'] = 'Result Rows'; +$lang['strexecutioncost'] = 'Execution Cost'; // Triggers $lang['strtrigger'] = 'Trigger'; @@ -569,7 +580,6 @@ $lang['strlocale'] = 'Locale'; $lang['strtypeneedsvalue'] = 'Vous devez spécifier au moins une valeur.'; $lang['strtypeneedscols'] = 'Vous devez spécifier un nombre valide de champs.'; $lang['strtypeneedsvals'] = 'Vous devez spécifier un nombre valide de valeurs.'; - $lang['strtypename'] = 'Nom du type'; $lang['strinputfn'] = 'Fonction d\'entrée'; $lang['stroutputfn'] = 'Fonction de sortie'; $lang['strpassbyval'] = 'Passée par valeur ?'; @@ -912,6 +922,7 @@ $lang['strvirtualtransaction'] = 'Virtual Transaction ID'; // Fulltext search $lang['strfulltext'] = 'Full Text Search'; $lang['strftsconfig'] = 'FTS configuration'; + $lang['strftsconfigs'] = 'Configurations'; $lang['strftscreateconfig'] = 'Create FTS configuration'; $lang['strftscreatedict'] = 'Create dictionary'; $lang['strftscreatedicttemplate'] = 'Create dictionary template'; @@ -943,7 +954,7 @@ $lang['strftsdictcreated'] = 'FTS dictionary created'; $lang['strftsdictcreatedbad'] = 'FTS dictionary creation failed.'; $lang['strftslexize'] = 'Lexize'; $lang['strftsinit'] = 'Init'; -$lang['strftsoption'] = 'Option'; +$lang['strftsoptionsvalues'] = 'Options and Values'; $lang['strftsdictneedsname'] = 'You must give a name for your FTS dictionary.'; $lang['strftsdictdropped'] = 'FTS dictionary dropped.'; $lang['strftsdictdroppedbad'] = 'FTS dictionary drop failed.'; @@ -959,6 +970,11 @@ $lang['strftsmappingaltered'] = 'FTS mapping altered.'; $lang['strftsmappingalteredbad'] = 'FTS mapping alter failed.'; $lang['strftsmappingadded'] = 'FTS mapping added.'; $lang['strftsmappingaddedbad'] = 'FTS mapping add failed.'; + $lang['strftsmappingdropped'] = 'FTS mapping dropped.'; + $lang['strftsmappingdroppedbad'] = 'FTS mapping drop failed.'; + $lang['strftstabconfigs'] = 'Configurations'; +$lang['strftstabdicts'] = 'Dictionaries'; +$lang['strftstabparsers'] = 'Parsers'; ?> -- 2.39.5