From 09fedd117d7604189d6645884915ae61a113b5a4 Mon Sep 17 00:00:00 2001 From: ioguix Date: Wed, 21 Nov 2007 15:45:31 +0000 Subject: [PATCH] refactor alterTable methods, add support for altering table's schema and fix a bad test in alterSeq methods. To test for pg < 8.2 --- classes/database/Postgres.php | 115 +++++++++++++++----------------- classes/database/Postgres71.php | 47 ++++++++++++- classes/database/Postgres74.php | 4 +- classes/database/Postgres80.php | 51 ++++++++++++-- classes/database/Postgres81.php | 47 ++++++++++++- tblproperties.php | 29 ++++++-- 6 files changed, 216 insertions(+), 77 deletions(-) diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index 3cc13c98..7c6a7678 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.311 2007/11/21 12:59:42 ioguix Exp $ + * $Id: Postgres.php,v 1.312 2007/11/21 15:45:31 ioguix Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -846,33 +846,30 @@ class Postgres extends ADODB_base { } /** - * Alters a table - * @param $table The name of the table + * 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 -1 transaction error - * @return -2 owner error * @return -3 rename error * @return -4 comment error - * @return -5 get existing table error + * @return -5 owner error * @return -6 tablespace error + * @return -7 schema error */ - function alterTable($table, $name, $owner, $comment, $tablespace) { - $this->fieldClean($table); + /* protected */ + function _alterTable($tblrs, $name, $owner, $schema, $comment, $tablespace) { + $this->fieldClean($name); - $this->fieldClean($owner); $this->clean($comment); - $this->fieldClean($tablespace); - - $status = $this->beginTransaction(); - if ($status != 0) { - $this->rollbackTransaction(); - return -1; - } - + /* $schema, $owner, $tablespace not supported in pg70 */ + + $table = $tblrs->fields['relname']; + // Comment $status = $this->setComment('TABLE', '', $table, $comment); if ($status != 0) { @@ -880,51 +877,6 @@ class Postgres extends ADODB_base { return -4; } - // Owner - if ($this->hasAlterTableOwner() && $owner != '') { - // Fetch existing owner - $data = $this->getTable($table); - 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 \"{$table}\" OWNER TO \"{$owner}\""; - - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -2; - } - } - } - - // Tablespace - if ($this->hasTablespaces() && $tablespace != '') { - // Fetch existing tablespace - $data = $this->getTable($table); - if ($data->recordCount() != 1) { - $this->rollbackTransaction(); - return -5; - } - - // If tablespace has been changed, then do the alteration. We - // don't want to do this unnecessarily. - if ($data->fields['tablespace'] != $tablespace) { - $sql = "ALTER TABLE \"{$table}\" SET TABLESPACE \"{$tablespace}\""; - - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -6; - } - } - } - // Rename (only if name has changed) if ($name != $table) { $sql = "ALTER TABLE \"{$table}\" RENAME TO \"{$name}\""; @@ -935,9 +887,45 @@ class Postgres extends ADODB_base { } } - return $this->endTransaction(); + return 0; } + /** + * Alter table properties + * @param $table The name of the table + * @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 -1 transaction error + * @return -2 get existing table error + * @return $this->_alterTable error code + */ + function alterTable($table, $name, $owner, $schema, $comment, $tablespace) { + + $this->fieldClean($table); + $data = $this->getTable($table); + if ($data->recordCount() != 1) + return -2; + + $status = $this->beginTransaction(); + if ($status != 0) { + $this->rollbackTransaction(); + return -1; + } + + $status = $this->_alterTable($data, $name, $owner, $schema, $comment, $tablespace); + + if ($status != 0) { + $this->rollbackTransaction(); + return $status; + } + + return $this->endTransaction(); + } + /** * Removes a table from the database * @param $table The table to drop @@ -4808,6 +4796,7 @@ class Postgres extends ADODB_base { function hasAlterTrigger() { return false; } function hasWithoutOIDs() { return false; } function hasAlterTableOwner() { return false; } + function hasAlterTableSchema() { return false; } function hasAlterSequenceOwner() { return false; } function hasAlterSequenceProps() { return false; } function hasSequenceAlterSchema() { return false; } diff --git a/classes/database/Postgres71.php b/classes/database/Postgres71.php index 24fba048..4fc3db76 100644 --- a/classes/database/Postgres71.php +++ b/classes/database/Postgres71.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres71.php,v 1.78 2007/11/21 12:59:42 ioguix Exp $ + * $Id: Postgres71.php,v 1.79 2007/11/21 15:45:31 ioguix Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -121,6 +121,49 @@ class Postgres71 extends Postgres { // Table functions + /** + * 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) { + + $status = parent::_alterTable($tblrs, $name, $owner, $schema, $comment, $tablespace); + if ($status != 0) + return $status; + + // if name != tablename, table has been renamed in parent + $tablename = ($tblrs->fields['relname'] == $name) ? $tblrs->fields['relname'] : $name; + + /* $tablespace, schema not supported in pg71 */ + $this->fieldClean($owner); + + // 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 \"{$tablename}\" OWNER TO \"{$owner}\""; + + $status = $this->execute($sql); + if ($status != 0) return -5; + } + + return 0; + } + /** * Finds the number of rows that would be returned by a * query. @@ -218,7 +261,7 @@ class Postgres71 extends Postgres { /* $schema, $increment, $minvalue, $maxvalue, $startvalue, $cachevalue, * $cycledvalue not supported in pg71 */ // if name != seqname, sequence has been renamed in parent - $sequence = ($seqrs->fields['seqname'] = $name) ? $seqrs->fields['seqname'] : $name; + $sequence = ($seqrs->fields['seqname'] == $name) ? $seqrs->fields['seqname'] : $name; $this->fieldClean($owner); // Owner diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php index a1699bc4..67ecdcc1 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.67 2007/11/21 12:59:42 ioguix Exp $ + * $Id: Postgres74.php,v 1.68 2007/11/21 15:45:31 ioguix Exp $ */ include_once('./classes/database/Postgres73.php'); @@ -669,7 +669,7 @@ class Postgres74 extends Postgres73 { /* $schema not supported in pg74 */ // if name != seqname, sequence has been renamed in parent - $sequence = ($seqrs->fields['seqname'] = $name) ? $seqrs->fields['seqname'] : $name; + $sequence = ($seqrs->fields['seqname'] == $name) ? $seqrs->fields['seqname'] : $name; $this->clean($increment); $this->clean($minvalue); $this->clean($maxvalue); diff --git a/classes/database/Postgres80.php b/classes/database/Postgres80.php index 9ca661ae..04642bbb 100644 --- a/classes/database/Postgres80.php +++ b/classes/database/Postgres80.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.0 support * - * $Id: Postgres80.php,v 1.26 2007/11/16 18:34:24 ioguix Exp $ + * $Id: Postgres80.php,v 1.27 2007/11/21 15:45:31 ioguix Exp $ */ include_once('./classes/database/Postgres74.php'); @@ -193,7 +193,7 @@ class Postgres80 extends Postgres74 { $sql = " SELECT - c.relname, u.usename AS relowner, + c.relname, n.nspname, u.usename AS relowner, pg_catalog.obj_description(c.oid, 'pg_class') AS relcomment, (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace FROM pg_catalog.pg_class c @@ -201,10 +201,54 @@ class Postgres80 extends Postgres74 { LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname = '{$this->_schema}' + AND n.oid = c.relnamespace AND c.relname = '{$table}'"; return $this->selectSet($sql); } + + /** + * 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) { + + $status = parent::_alterTable($tblrs, $name, $owner, $schema, $comment, $tablespace); + if ($status != 0) + return $status; + + // if name != tablename, table has been renamed in parent + $tablename = ($tblrs->fields['relname'] == $name) ? $tblrs->fields['relname'] : $name; + + /* $schema not supported in pg80 */ + $this->fieldClean($tablespace); + + // Tablespace + if (!empty($tablespace) && ($tblrs->fields['tablespace'] != $tablespace)) { + + // If tablespace has been changed, then do the alteration. We + // don't want to do this unnecessarily. + $sql = "ALTER TABLE \"{$tablename}\" SET TABLESPACE \"{$tablespace}\""; + + $status = $this->execute($sql); + if ($status != 0) return -6; + } + + return 0; + } /** * Alters a column in a table @@ -541,8 +585,7 @@ class Postgres80 extends Postgres74 { function hasTablespaces() { return true; } function hasSignals() { return true; } function hasNamedParams() { return true; } - function hasFunctionAlterOwner() { return true; } + function hasFunctionAlterOwner() { return true; } } - ?> diff --git a/classes/database/Postgres81.php b/classes/database/Postgres81.php index 52934952..e5b7ae12 100644 --- a/classes/database/Postgres81.php +++ b/classes/database/Postgres81.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.1 support * - * $Id: Postgres81.php,v 1.16 2007/11/21 12:59:42 ioguix Exp $ + * $Id: Postgres81.php,v 1.17 2007/11/21 15:45:31 ioguix Exp $ */ include_once('./classes/database/Postgres80.php'); @@ -468,6 +468,48 @@ class Postgres81 extends Postgres80 { // Table methods + /** + * 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) { + + $status = parent::_alterTable($tblrs, $name, $owner, $schema, $comment, $tablespace); + if ($status != 0) + return $status; + + // if name != tablename, table has been renamed in parent + $tablename = ($tblrs->fields['relname'] == $name) ? $tblrs->fields['relname'] : $name; + + $this->fieldClean($schema); + + // Schema + if (!empty($schema) && ($tblrs->fields['nspname'] != $schema)) { + + // If tablespace has been changed, then do the alteration. We + // don't want to do this unnecessarily. + $sql = "ALTER TABLE \"{$tablename}\" SET SCHEMA \"{$schema}\""; + + $status = $this->execute($sql); + if ($status != 0) return -7; + } + + return 0; + } + /** * Enables a trigger * @param $tgname The name of the trigger to enable @@ -531,7 +573,7 @@ class Postgres81 extends Postgres80 { return $status; // if name != seqname, sequence has been renamed in parent - $sequence = ($seqrs->fields['seqname'] = $name) ? $seqrs->fields['seqname'] : $name; + $sequence = ($seqrs->fields['seqname'] == $name) ? $seqrs->fields['seqname'] : $name; $this->clean($schema); if ($seqrs->fields['nspname'] != $schema) { @@ -551,6 +593,7 @@ class Postgres81 extends Postgres80 { function hasPreparedXacts() { return true; } function hasDisableTriggers() { return true; } function hasFunctionAlterSchema() { return true; } + function hasAlterTableSchema() { return true; } function hasSequenceAlterSchema() { return true; } } diff --git a/tblproperties.php b/tblproperties.php index 4df95859..850c46e9 100644 --- a/tblproperties.php +++ b/tblproperties.php @@ -3,7 +3,7 @@ /** * List tables in a database * - * $Id: tblproperties.php,v 1.88 2007/10/19 08:59:22 ioguix Exp $ + * $Id: tblproperties.php,v 1.89 2007/11/21 15:45:31 ioguix Exp $ */ // Include application functions @@ -15,14 +15,15 @@ * Function to save after altering a table */ function doSaveAlter() { - global $data, $lang, $_reload_browser; + global $data, $lang, $_reload_browser, $misc; // For databases that don't allow owner change if (!isset($_POST['owner'])) $_POST['owner'] = ''; // Default tablespace to null if it isn't set if (!isset($_POST['tablespace'])) $_POST['tablespace'] = null; + if (!isset($_POST['newschema'])) $_POST['newschema'] = null; - $status = $data->alterTable($_POST['table'], $_POST['name'], $_POST['owner'], $_POST['comment'], $_POST['tablespace']); + $status = $data->alterTable($_POST['table'], $_POST['name'], $_POST['owner'], $_POST['newschema'], $_POST['comment'], $_POST['tablespace']); if ($status == 0) { // If table has been renamed, need to change to the new name and // reload the browser frame. @@ -32,6 +33,12 @@ // Force a browser reload $_reload_browser = true; } + // If schema has changed, need to change to the new schema and reload the browser + if (!empty($_POST['newschema']) && ($_POST['newschema'] != $data->_schema)) { + // Jump them to the new sequence schema + $misc->setCurrentSchema($_POST['newschema']); + $_reload_browser = true; + } doDefault($lang['strtablealtered']); } else @@ -60,6 +67,7 @@ if (!isset($_POST['name'])) $_POST['name'] = $table->fields['relname']; if (!isset($_POST['owner'])) $_POST['owner'] = $table->fields['relowner']; + if (!isset($_POST['newschema'])) $_POST['newschema'] = $table->fields['nspname']; if (!isset($_POST['comment'])) $_POST['comment'] = $table->fields['relcomment']; if ($data->hasTablespaces() && !isset($_POST['tablespace'])) $_POST['tablespace'] = $table->fields['tablespace']; @@ -82,7 +90,20 @@ } echo "\n"; } - + + if ($data->hasAlterTableSchema()) { + $schemas = $data->getSchemas(); + echo "{$lang['strschema']}\n"; + echo "\n"; + } + // Tablespace (if there are any) if ($data->hasTablespaces() && $tablespaces->recordCount() > 0) { echo "\t\n\t\t{$lang['strtablespace']}\n"; -- 2.39.5