From e8f6ff8f8dafb5073eb6328340408979bc5eca8b Mon Sep 17 00:00:00 2001 From: xzilla Date: Thu, 15 Nov 2007 23:09:21 +0000 Subject: [PATCH] add alter function owner, schema. thanks go to ioguix for some pointers. needs testing against < 8.1, but I don't have them here. --- HISTORY | 1 + TODO | 1 + classes/database/Postgres.php | 4 +- classes/database/Postgres74.php | 37 +++++++++++++++- classes/database/Postgres80.php | 12 +++-- classes/database/Postgres81.php | 3 +- functions.php | 78 ++++++++++++++++++++++++++------- 7 files changed, 114 insertions(+), 22 deletions(-) diff --git a/HISTORY b/HISTORY index 0376e0be..49f3a948 100644 --- a/HISTORY +++ b/HISTORY @@ -20,6 +20,7 @@ Features properties page (ioguix) * Add Support for Enum type creation (ioguix,xzilla) * Add alter name, owner, comment and properties for sequences (ioguix) +* Add function costing options Bugs * Fix inability to assign a field type/domain of a different schema diff --git a/TODO b/TODO index 7dcabeb2..500bb454 100644 --- a/TODO +++ b/TODO @@ -113,6 +113,7 @@ Functions * Display owner * Alter owner * Alter schema +* GUC settings [8.3] Indexes ------- diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index 8ad6dd7e..0c718ada 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.309 2007/11/15 06:06:45 xzilla Exp $ + * $Id: Postgres.php,v 1.310 2007/11/15 23:09:21 xzilla Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -4846,6 +4846,8 @@ class Postgres extends ADODB_base { function hasVirtualTransactionId() {return false;} function hasFunctionCosting() {return false;} function hasFunctionGUC() {return false;} + function hasFunctionAlterSchema() { return false; } + function hasFunctionAlterOwner() { return false; } } ?> diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php index 028080ec..14c609d9 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.65 2007/11/15 06:06:45 xzilla Exp $ + * $Id: Postgres74.php,v 1.66 2007/11/15 23:09:21 xzilla Exp $ */ include_once('./classes/database/Postgres73.php'); @@ -565,8 +565,10 @@ class Postgres74 extends Postgres73 { * @return -3 create function error * @return -4 comment error * @return -5 rename function error + * @return -6 alter owner error + * @return -7 alter schema error */ - function setFunction($function_oid, $funcname, $newname, $args, $returns, $definition, $language, $flags, $setof, $cost, $rows, $comment) { + function setFunction($function_oid, $funcname, $newname, $args, $returns, $definition, $language, $flags, $setof, $funcown, $newown, $funcschema, $newschema, $cost, $rows, $comment) { // Begin a transaction $status = $this->beginTransaction(); if ($status != 0) { @@ -599,8 +601,39 @@ class Postgres74 extends Postgres73 { $this->rollbackTransaction(); return -5; } + + $funcname = $newname; } + // Alter the owner, if necessary + if ($this->hasFunctionAlterOwner()) { + $this->fieldClean($newown); + if ($funcown != $newown) { + $sql = "ALTER FUNCTION \"{$funcname}\"({$args}) OWNER TO \"{$newown}\""; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -6; + } + } + + } + + // Alter the schema, if necessary + if ($this->hasFunctionAlterSchema()) { + $this->fieldClean($newschema); + if ($funcschema != $newschema) { + $sql = "ALTER FUNCTION \"{$funcname}\"({$args}) SET SCHEMA \"{$newschema}\""; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -7; + } + } + + } + + return $this->endTransaction(); } diff --git a/classes/database/Postgres80.php b/classes/database/Postgres80.php index db1d09e8..9a390307 100644 --- a/classes/database/Postgres80.php +++ b/classes/database/Postgres80.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.0 support * - * $Id: Postgres80.php,v 1.24 2007/10/02 21:44:35 ioguix Exp $ + * $Id: Postgres80.php,v 1.25 2007/11/15 23:09:21 xzilla Exp $ */ include_once('./classes/database/Postgres74.php'); @@ -511,6 +511,8 @@ class Postgres80 extends Postgres74 { $sql = "SELECT pc.oid AS prooid, proname, + pg_catalog.pg_get_userbyid(proowner) AS proowner, + nspname as proschema, lanname as prolanguage, pg_catalog.format_type(prorettype, NULL) as proresult, prosrc, @@ -523,10 +525,13 @@ class Postgres80 extends Postgres74 { proargnames AS proargnames, pg_catalog.obj_description(pc.oid, 'pg_proc') AS procomment FROM - pg_catalog.pg_proc pc, pg_catalog.pg_language pl + pg_catalog.pg_proc pc, pg_catalog.pg_language pl, pg_catalog.pg_namespace pn WHERE pc.oid = '{$function_oid}'::oid - AND pc.prolang = pl.oid + AND + pc.prolang = pl.oid + AND + pc.pronamespace = pn.oid "; return $this->selectSet($sql); @@ -538,6 +543,7 @@ class Postgres80 extends Postgres74 { function hasTablespaces() { return true; } function hasSignals() { return true; } function hasNamedParams() { return true; } + function hasFunctionAlterOwner() { return true; } } diff --git a/classes/database/Postgres81.php b/classes/database/Postgres81.php index 15fadcac..6b78987f 100644 --- a/classes/database/Postgres81.php +++ b/classes/database/Postgres81.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.1 support * - * $Id: Postgres81.php,v 1.14 2006/12/31 19:04:05 xzilla Exp $ + * $Id: Postgres81.php,v 1.15 2007/11/15 23:09:21 xzilla Exp $ */ include_once('./classes/database/Postgres80.php'); @@ -501,6 +501,7 @@ class Postgres81 extends Postgres80 { function hasAutovacuum() { return true; } function hasPreparedXacts() { return true; } function hasDisableTriggers() { return true; } + function hasFunctionAlterSchema() { return true; } } ?> diff --git a/functions.php b/functions.php index 0bcde67f..e3cfb0db 100644 --- a/functions.php +++ b/functions.php @@ -3,7 +3,7 @@ /** * Manage functions in a database * - * $Id: functions.php,v 1.71 2007/11/15 17:13:16 xzilla Exp $ + * $Id: functions.php,v 1.72 2007/11/15 23:09:21 xzilla Exp $ */ // Include application functions @@ -16,7 +16,8 @@ * Function to save after editing a function */ function doSaveEdit() { - global $data, $lang; + global $data, $lang; + global $misc, $_reload_browser; $fnlang = strtolower($_POST['original_lang']); @@ -32,13 +33,25 @@ $_POST['original_arguments'], $_POST['original_returns'], $def, $_POST['original_lang'], $_POST['formProperties'], - isset($_POST['original_setof']), + isset($_POST['original_setof']), + $_POST['original_owner'], $_POST['formFuncOwn'], + $_POST['original_schema'], $_POST['formFuncSchema'], isset($_POST['formCost']) ? $_POST['formCost'] : null, isset($_POST['formRows']) ? $_POST['formRows'] : 0, $_POST['formComment']); - if ($status == 0) + if ($status == 0) { + // If function has had schema altered, need to change to the new schema + // and reload the browser frame. + if ($_POST['formFuncSchema'] != $_POST['original_schema']) { + // Jump them to the new function schema + $_REQUEST['schema'] = $_POST['formFuncSchema']; + $misc->href = "server={$_REQUEST['server']}&database={$_REQUEST['database']}&schema={$_REQUEST['schema']}"; + // Force a browser reload + $_reload_browser = true; + } doProperties($lang['strfunctionupdated']); - else + } else { doEdit($lang['strfunctionupdatedbad']); + } } /** @@ -63,6 +76,8 @@ if (!isset($_POST['formComment'])) $_POST['formComment'] = $fndata->fields['procomment']; if (!isset($_POST['formObjectFile'])) $_POST['formObjectFile'] = $fndata->fields['probin']; if (!isset($_POST['formLinkSymbol'])) $_POST['formLinkSymbol'] = $fndata->fields['prosrc']; + if (!isset($_POST['formFuncOwn'])) $_POST['formFuncOwn'] = $fndata->fields['proowner']; + if (!isset($_POST['formFuncSchema'])) $_POST['formFuncSchema'] = $fndata->fields['proschema']; if ($data->hasFunctionCosting()) { if (!isset($_POST['formCost'])) $_POST['formCost'] = $fndata->fields['procost']; @@ -92,14 +107,29 @@ echo "
\n"; echo "\n"; echo "\n"; + echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; - + echo "\n"; echo "\n"; + echo "\n"; @@ -129,23 +159,23 @@ echo "\n"; } else if ($fnlang == 'internal') { - echo "\n"; - echo "\n"; + echo "\n"; } else { - echo "\n"; - echo "\n"; } // Display function comment - echo "\n"; - echo "\n"; // Display function cost options if ($data->hasFunctionCosting()) { - echo "\n"; + echo "\n"; echo ""; echo "\n"; - echo "\n"; + echo "\n"; } + + // function owner + if ($data->hasFunctionAlterOwner()) { + $users = $data->getUsers(); + echo "\n"; + } echo "
{$lang['strschema']}{$lang['strfunction']}{$lang['strarguments']}{$lang['strreturns']}{$lang['strproglanguage']}
"; + echo "fields['proschema']),"\" />\n"; + if ($data->hasFunctionAlterSchema()) { + $schemas = $data->getSchemas(); + echo "\n"; + } + echo ""; echo "fields['proname']),"\" />\n"; echo "_maxNameLen}\" value=\"", htmlspecialchars($_POST['formFunction']), "\" />"; echo "
{$lang['strlinksymbol']}
{$lang['strlinksymbol']}
{$lang['strdefinition']}
{$lang['strcomment']}
{$lang['strfunctioncosting']}
{$lang['strfunctioncosting']}
{$lang['strexecutioncost']}: {$lang['strresultrows']}: funcprops) && sizeof($data->funcprops) > 0) { - echo "
{$lang['strproperties']}
\n"; + echo "
{$lang['strproperties']}
\n"; $i = 0; foreach ($data->funcprops as $k => $v) { echo "
{$lang['strowner']}: \n"; + echo "fields['proowner']),"\" />\n"; + echo "
\n"; echo "

\n"; echo "\n"; @@ -274,6 +319,9 @@ } echo "\n"; } + + echo "{$lang['strowner']}: ", htmlspecialchars($funcdata->fields['proowner']),"\n"; + echo "\n"; echo "\n"; } else echo "

{$lang['strnodata']}

\n"; -- 2.39.5