From 9e979d6811b7d547aa00ec15403552949a775f68 Mon Sep 17 00:00:00 2001 From: chriskl Date: Thu, 16 Jun 2005 14:40:10 +0000 Subject: [PATCH] Merge DEV_SLONY. Scary. Still a few bugs and many rough edges. Also, there's no way to turn Slony support off :) --- CREDITS | 4 + HISTORY | 4 +- classes/Misc.php | 25 +- classes/database/Connection.php | 2 +- classes/database/Postgres.php | 14 +- classes/database/Postgres73.php | 29 +- classes/database/Postgres74.php | 9 +- classes/database/Postgres80.php | 26 +- classes/plugins/Plugin.php | 30 + classes/plugins/Slony.php | 834 ++++++ conf/slony.inc.php | 11 + database.php | 49 +- lang/english.php | 115 +- lang/recoded/english.php | 115 +- libraries/lib.inc.php | 13 +- plugin_slony.php | 2235 ++++++++++++++++ sequences.php | 4 +- sql.php | 8 +- sql/plugins/slony1_base.sql | 387 +++ sql/plugins/slony1_base.v73.sql | 12 + sql/plugins/slony1_base.v74.sql | 12 + sql/plugins/slony1_funcs.sql | 4271 ++++++++++++++++++++++++++++++ sql/plugins/slony1_funcs.v73.sql | 28 + sql/plugins/slony1_funcs.v74.sql | 28 + sql/plugins/xxid.v73.sql | 186 ++ sql/plugins/xxid.v74.sql | 186 ++ 26 files changed, 8581 insertions(+), 56 deletions(-) create mode 100755 classes/plugins/Plugin.php create mode 100755 classes/plugins/Slony.php create mode 100755 conf/slony.inc.php create mode 100755 plugin_slony.php create mode 100755 sql/plugins/slony1_base.sql create mode 100755 sql/plugins/slony1_base.v73.sql create mode 100755 sql/plugins/slony1_base.v74.sql create mode 100755 sql/plugins/slony1_funcs.sql create mode 100755 sql/plugins/slony1_funcs.v73.sql create mode 100755 sql/plugins/slony1_funcs.v74.sql create mode 100755 sql/plugins/xxid.v73.sql create mode 100755 sql/plugins/xxid.v74.sql diff --git a/CREDITS b/CREDITS index 45caa9e0..8f044e27 100644 --- a/CREDITS +++ b/CREDITS @@ -58,3 +58,7 @@ Third Party Libraries - Highlight.php (Jacob D. Cohen of rafb.net) - XLoadTree2 (Erik Arvidsson & Emil A Eklund of webfx.eae.net) + +Corporate Sponsors + +- SpikeSource (www.spikesource.com) - Slony support \ No newline at end of file diff --git a/HISTORY b/HISTORY index f08863a0..8d3b7e04 100644 --- a/HISTORY +++ b/HISTORY @@ -1,10 +1,11 @@ phpPgAdmin History ------------------ -Version 3.6-dev +Version 4.0-dev --------------- Features +* Slony replication support (Thanks to sponsorship from SpikeSource) * Allow current database to be at the top * Allow base URL of PostgreSQL documentation to be configured * Allow variable size textarea when editing values (Juergen Weigert) @@ -21,6 +22,7 @@ Features (Using XLoadTree2 from http://webfx.eae.net/) * Avoid getting and setting encoding queries if possible * Allow language change from the intro page at any time +* Avoid version query in PHP 5 / PostgreSQL 7.4+ Bugs * Tree Icons are displayed middle instead of top diff --git a/classes/Misc.php b/classes/Misc.php index 6bafe136..71b4debe 100644 --- a/classes/Misc.php +++ b/classes/Misc.php @@ -2,7 +2,7 @@ /** * Class to hold various commonly used functions * - * $Id: Misc.php,v 1.101 2005/05/16 14:30:14 jollytoad Exp $ + * $Id: Misc.php,v 1.102 2005/06/16 14:40:11 chriskl Exp $ */ class Misc { @@ -515,24 +515,27 @@ case 'database': # $vars = $servervar . $databasevar . '&subject=database'; - return array ( + $tabs = array ( 'schemas' => array ( 'title' => $lang['strschemas'], 'url' => 'database.php', 'urlvars' => array('subject' => 'database'), 'hide' => (!$data->hasSchemas()), 'help' => 'pg.schema', + 'tree' => false, ), 'sql' => array ( 'title' => $lang['strsql'], 'url' => 'database.php', 'urlvars' => array('subject' => 'database', 'action' => 'sql'), 'help' => 'pg.sql', + 'tree' => false, ), 'find' => array ( 'title' => $lang['strfind'], 'url' => 'database.php', 'urlvars' => array('subject' => 'database', 'action' => 'find'), + 'tree' => false, ), 'variables' => array ( 'title' => $lang['strvariables'], @@ -540,6 +543,7 @@ 'urlvars' => array('subject' => 'database', 'action' => 'variables'), 'hide' => (!$data->hasVariables()), 'help' => 'pg.variable', + 'tree' => false, ), 'processes' => array ( 'title' => $lang['strprocesses'], @@ -547,11 +551,13 @@ 'urlvars' => array('subject' => 'database', 'action' => 'processes'), 'hide' => (!$data->hasProcesses()), 'help' => 'pg.process', + 'tree' => false, ), 'admin' => array ( 'title' => $lang['stradmin'], 'url' => 'database.php', 'urlvars' => array('subject' => 'database', 'action' => 'admin'), + 'tree' => false, ), 'privileges' => array ( 'title' => $lang['strprivileges'], @@ -559,6 +565,7 @@ 'urlvars' => array('subject' => 'database'), 'hide' => (!isset($data->privlist['database'])), 'help' => 'pg.privilege', + 'tree' => false, ), 'languages' => array ( 'title' => $lang['strlanguages'], @@ -579,9 +586,11 @@ 'url' => 'database.php', 'urlvars' => array('subject' => 'database', 'action' => 'export'), 'hide' => (!$this->isDumpEnabled()), + 'tree' => false, ), ); + return $tabs; case 'schema': # $vars = $servervar . $databasevar . $schemavar . '&subject=schema'; return array ( @@ -667,6 +676,7 @@ 'urlvars' => array('subject' => 'schema'), 'hide' => (!$data->hasSchemas()), 'help' => 'pg.privilege', + 'tree' => false, ), ); @@ -1424,6 +1434,17 @@ } } + function &adjustTabsForTree(&$tabs) { + include_once('classes/ArrayRecordSet.php'); + + foreach ($tabs as $i => $tab) { + if ((isset($tab['hide']) && $tab['hide'] === true) || (isset($tab['tree']) && $tab['tree'] === false)) { + unset($tabs[$i]); + } + } + return new ArrayRecordSet($tabs); + } + function icon($icon) { global $conf; $path = "images/themes/{$conf['theme']}/{$icon}"; diff --git a/classes/database/Connection.php b/classes/database/Connection.php index 76ed6364..8622505a 100755 --- a/classes/database/Connection.php +++ b/classes/database/Connection.php @@ -3,7 +3,7 @@ /** * Class to represent a database connection * - * $Id: Connection.php,v 1.10 2005/06/02 01:35:51 chriskl Exp $ + * $Id: Connection.php,v 1.11 2005/06/16 14:40:11 chriskl Exp $ */ include_once('./classes/database/ADODB_base.php'); diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index f6177ee3..9322124f 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.262 2005/06/02 11:31:02 chriskl Exp $ + * $Id: Postgres.php,v 1.263 2005/06/16 14:40:11 chriskl Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -1801,14 +1801,15 @@ class Postgres extends ADODB_base { * Returns all sequences in the current database * @return A recordset */ - function &getSequences() { + function &getSequences($all = false) { + // $all argument is ignored as it makes no difference $sql = "SELECT c.relname AS seqname, u.usename AS seqowner, (SELECT description FROM pg_description pd WHERE c.oid=pd.objoid) AS seqcomment FROM pg_class c, pg_user u WHERE c.relowner=u.usesysid AND c.relkind = 'S' ORDER BY seqname"; - + return $this->selectSet( $sql ); } @@ -2135,14 +2136,17 @@ class Postgres extends ADODB_base { /** * Grabs a list of indexes for a table * @param $table The name of a table whose indexes to retrieve + * @param $unique Only get unique/pk indexes * @return A recordset */ - function &getIndexes($table = '') { + function &getIndexes($table = '', $unique = false) { $this->clean($table); $sql = "SELECT c2.relname AS indname, i.indisprimary, i.indisunique, pg_get_indexdef(i.indexrelid) AS inddef FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = '{$table}' AND c.oid = i.indrelid AND i.indexrelid = c2.oid - ORDER BY c2.relname"; + "; + if ($unique) $sql .= " AND i.indisunique "; + $sql .= " ORDER BY c2.relname"; return $this->selectSet($sql); } diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php index 0ea905c1..a445ddea 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.143 2005/05/02 15:47:26 chriskl Exp $ + * $Id: Postgres73.php,v 1.144 2005/06/16 14:40:12 chriskl Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -587,11 +587,21 @@ class Postgres73 extends Postgres72 { * Returns all sequences in the current database * @return A recordset */ - function &getSequences() { - $sql = "SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment - FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n - WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid - AND c.relkind = 'S' AND n.nspname='{$this->_schema}' ORDER BY seqname"; + function &getSequences($all = false) { + if ($all) { + // Exclude pg_catalog and information_schema tables + $sql = "SELECT n.nspname, c.relname AS seqname, u.usename AS seqowner + FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n + WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid + AND c.relkind = 'S' + AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') + ORDER BY nspname, seqname"; + } else { + $sql = "SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment + FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n + WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid + AND c.relkind = 'S' AND n.nspname='{$this->_schema}' ORDER BY seqname"; + } return $this->selectSet( $sql ); } @@ -612,9 +622,10 @@ class Postgres73 extends Postgres72 { /** * Grabs a list of indexes for a table * @param $table The name of a table whose indexes to retrieve + * @param $unique Only get unique/pk indexes * @return A recordset */ - function &getIndexes($table = '') { + function &getIndexes($table = '', $unique = false) { $this->clean($table); $sql = "SELECT c2.relname AS indname, i.indisprimary, i.indisunique, i.indisclustered, @@ -622,7 +633,9 @@ class Postgres73 extends Postgres72 { FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.relname = '{$table}' AND pg_catalog.pg_table_is_visible(c.oid) AND c.oid = i.indrelid AND i.indexrelid = c2.oid - ORDER BY c2.relname"; + "; + if ($unique) $sql .= " AND i.indisunique "; + $sql .= " ORDER BY c2.relname"; return $this->selectSet($sql); } diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php index 94c010bf..6dab325a 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.44 2005/04/30 18:02:01 soranzo Exp $ + * $Id: Postgres74.php,v 1.45 2005/06/16 14:40:12 chriskl Exp $ */ include_once('./classes/database/Postgres73.php'); @@ -186,9 +186,10 @@ class Postgres74 extends Postgres73 { /** * Grabs a list of indexes for a table * @param $table The name of a table whose indexes to retrieve + * @param $unique Only get unique/pk indexes * @return A recordset */ - function &getIndexes($table = '') { + function &getIndexes($table = '', $unique = false) { $this->clean($table); $sql = "SELECT c2.relname AS indname, i.indisprimary, i.indisunique, i.indisclustered, @@ -196,7 +197,9 @@ class Postgres74 extends Postgres73 { FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.relname = '{$table}' AND pg_catalog.pg_table_is_visible(c.oid) AND c.oid = i.indrelid AND i.indexrelid = c2.oid - ORDER BY c2.relname"; + "; + if ($unique) $sql .= " AND i.indisunique "; + $sql .= " ORDER BY c2.relname"; return $this->selectSet($sql); } diff --git a/classes/database/Postgres80.php b/classes/database/Postgres80.php index abba2611..37f5ad16 100644 --- a/classes/database/Postgres80.php +++ b/classes/database/Postgres80.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.0 support * - * $Id: Postgres80.php,v 1.13 2005/05/02 15:47:26 chriskl Exp $ + * $Id: Postgres80.php,v 1.14 2005/06/16 14:40:12 chriskl Exp $ */ include_once('./classes/database/Postgres74.php'); @@ -324,13 +324,23 @@ class Postgres80 extends Postgres74 { * Returns all sequences in the current database * @return A recordset */ - function &getSequences() { - $sql = "SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment, - (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace - FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n - WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid - AND c.relkind = 'S' AND n.nspname='{$this->_schema}' ORDER BY seqname"; - + function &getSequences($all = false) { + if ($all) { + // Exclude pg_catalog and information_schema tables + $sql = "SELECT n.nspname, c.relname AS seqname, u.usename AS seqowner + FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n + WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid + AND c.relkind = 'S' + AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') + ORDER BY nspname, seqname"; + } else { + $sql = "SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment, + (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace + FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n + WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid + AND c.relkind = 'S' AND n.nspname='{$this->_schema}' ORDER BY seqname"; + } + return $this->selectSet( $sql ); } diff --git a/classes/plugins/Plugin.php b/classes/plugins/Plugin.php new file mode 100755 index 00000000..687d4e2e --- /dev/null +++ b/classes/plugins/Plugin.php @@ -0,0 +1,30 @@ +name = $name; + + // Read in configuration + if ($this->config !== null) { + global $conf; + include('./conf/' . $name . '.inc.php'); + } + } + +} + +?> diff --git a/classes/plugins/Slony.php b/classes/plugins/Slony.php new file mode 100755 index 00000000..3049a606 --- /dev/null +++ b/classes/plugins/Slony.php @@ -0,0 +1,834 @@ +Plugin('slony'); + } + + /** + * Determines whether or not Slony is installed in the current + * database. + * @post Will populate version and schema fields, etc. + * @return True if Slony is installed, false otherwise. + */ + function isEnabled() { + global $data; + + // Slony needs schemas + if (!$data->hasSchemas()) return false; + + // Check for the slonyversion() function and find the schema + // it's in. We put an order by and limit 1 in here to guarantee + // only finding the first one, even if there are somehow two + // Slony schemas. + $sql = "SELECT pn.nspname AS schema, pu.usename AS owner, SUBSTRING(pn.nspname FROM 2) AS cluster, + pg_catalog.obj_description(pn.oid, 'pg_namespace') AS nspcomment + FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pn, pg_catalog.pg_user pu + WHERE pp.pronamespace=pn.oid + AND pn.nspowner = pu.usesysid + AND pp.proname='slonyversion' + AND pn.nspname LIKE '\\\\_%' + ORDER BY pn.nspname LIMIT 1"; + $rs = $data->selectSet($sql); + if ($rs->recordCount() == 1) { + $schema = $rs->f['schema']; + $this->slony_schema = $schema; + $this->slony_owner = $rs->f['owner']; + $this->slony_comment = $rs->f['nspcomment']; + // Cluster name is schema minus "_" prefix. + $this->slony_cluster = $rs->f['cluster']; + $data->fieldClean($schema); + $sql = "SELECT \"{$schema}\".slonyversion() AS version"; + $version = $data->selectField($sql, 'version'); + if ($version === -1) return false; + else { + $this->slony_version = $version; + return true; + } + } + else return false; + } + + // CLUSTERS + + /** + * Gets the clusters in this database + */ + function getClusters() { + include_once('classes/ArrayRecordSet.php'); + + if ($this->isEnabled()) { + $clusters = array(array('cluster' => $this->slony_cluster, 'comment' => $this->slony_comment)); + } + else + $clusters = array(); + + return new ArrayRecordSet($clusters); + } + + /** + * Gets a single cluster + */ + function getCluster() { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + + $sql = "SELECT no_id, no_comment, \"{$schema}\".slonyversion() AS version + FROM \"{$schema}\".sl_local_node_id, \"{$schema}\".sl_node + WHERE no_id=last_value"; + + + return $data->selectSet($sql); + } + + /** + * Drops an entire cluster. + */ + function dropCluster() { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + + $sql = "SELECT \"{$schema}\".uninstallnode(); DROP SCHEMA \"{$schema}\" CASCADE"; + + return $data->execute($sql); + } + + /** + * Helper function to get a file into a string and replace + * variables. + */ + function _getFile($file, $cluster) { + global $data; + $schema = '_' . $cluster; + $data->fieldClean($cluster); + + $buffer = null; + $handle = fopen("./sql/plugins/{$file}", "r"); + while (!feof($handle)) { + $temp = fgets($handle, 4096); + $temp = str_replace('@CLUSTERNAME@', $cluster, $temp); + + $temp = str_replace('@NAMESPACE@', $schema, $temp); + $buffer .= $temp; + } + fclose($handle); + + return $buffer; + } + + /** + * Initializes a new cluster + */ + function initCluster($name, $no_id, $no_comment) { + global $data, $misc; + + // Prevent timeouts since cluster initialization can be slow + if (!ini_get('safe_mode')) set_time_limit(0); + + $server_info = $misc->getServerInfo(); + + if (!$data->isSuperUser($server_info['username'])) { + return -10; + } + + $status = $data->beginTransaction(); + if ($status != 0) return -1; + + // Create the schema + $status = $data->createSchema('_' . $name); + if ($status != 0) { + $data->rollbackTransaction(); + return -2; + } + + // XXX: Support only Postgresql 7.4+ at the moment + $sql = $this->_getFile('xxid.v74.sql', $name); + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -3; + } + + $sql = $this->_getFile('slony1_base.sql', $name); + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -3; + } +/* THIS FILE IS EMPTY AND JUST CAUSES ERRORS + $sql = $this->_getFile('slony1_base.v74.sql', $name); + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -3; + } +*/ + $sql = $this->_getFile('slony1_funcs.sql', $name); + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -3; + } + + $sql = $this->_getFile('slony1_funcs.v74.sql', $name); + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -3; + } + + $enabled = $this->isEnabled(); + if (!$enabled) { + $data->rollbackTransaction(); + return -4; + } + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($no_comment); + + $sql = "SELECT \"{$schema}\".initializelocalnode('{$no_id}', '{$no_comment}'); SELECT \"{$schema}\".enablenode('{$no_id}')"; + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -5; + } + + return $data->endTransaction(); + } + + // NODES + + /** + * Gets the nodes in this database + */ + function getNodes() { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + + $sql = "SELECT * FROM \"{$schema}\".sl_node ORDER BY no_comment"; + + return $data->selectSet($sql); + } + + /** + * Gets a single node + */ + function getNode($no_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + + $sql = "SELECT * FROM \"{$schema}\".sl_node WHERE no_id='{$no_id}'"; + + return $data->selectSet($sql); + } + + /** + * Creates a node + */ + function createNode($no_id, $no_comment) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_comment); + $data->clean($no_id); + + if ($no_id != '') + $sql = "SELECT \"{$schema}\".storenode('{$no_id}', '{$no_comment}')"; + else + $sql = "SELECT \"{$schema}\".storenode((SELECT COALESCE(MAX(no_id), 0) + 1 FROM \"{$schema}\".sl_node), '{$no_comment}')"; + + return $data->execute($sql); + } + + /** + * Drops a node + */ + function dropNode($no_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + + $sql = "SELECT \"{$schema}\".dropnode('{$no_id}')"; + + return $data->execute($sql); + } + + // REPLICATION SETS + + /** + * Gets the replication sets in this database + */ + function getReplicationSets() { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + + $sql = "SELECT *, set_locked IS NOT NULL AS is_locked FROM \"{$schema}\".sl_set ORDER BY set_id"; + + return $data->selectSet($sql); + } + + /** + * Gets a particular replication set + */ + function getReplicationSet($set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + + $sql = "SELECT *, (SELECT COUNT(*) FROM \"{$schema}\".sl_subscribe ssub WHERE ssub.sub_set=ss.set_id) AS subscriptions, + set_locked IS NOT NULL AS is_locked + FROM \"{$schema}\".sl_set ss, \"{$schema}\".sl_node sn + WHERE ss.set_origin=sn.no_id + AND set_id='{$set_id}'"; + + return $data->selectSet($sql); + } + + /** + * Creates a set + */ + function createReplicationSet($set_id, $set_comment) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_comment); + $data->clean($set_id); + + if ($set_id != '') + $sql = "SELECT \"{$schema}\".storeset('{$set_id}', '{$set_comment}')"; + else + $sql = "SELECT \"{$schema}\".storeset((SELECT COALESCE(MAX(set_id), 0) + 1 FROM \"{$schema}\".sl_set), '{$set_comment}')"; + + return $data->execute($sql); + } + + /** + * Drops a set + */ + function dropReplicationSet($set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + + $sql = "SELECT \"{$schema}\".dropset('{$set_id}')"; + + return $data->execute($sql); + } + + /** + * Locks or unlocks a set + * @param boolean $lock True to lock, false to unlock + */ + function lockReplicationSet($set_id, $lock) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + + if ($lock) + $sql = "SELECT \"{$schema}\".lockset('{$set_id}')"; + else + $sql = "SELECT \"{$schema}\".unlockset('{$set_id}')"; + + return $data->execute($sql); + } + + /** + * Merges two sets + */ + function mergeReplicationSet($set_id, $target) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + $data->clean($target); + + $sql = "SELECT \"{$schema}\".mergeset('{$target}', '{$set_id}')"; + + return $data->execute($sql); + } + + /** + * Moves a set to a new origin + */ + function moveReplicationSet($set_id, $new_origin) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + $data->clean($new_origin); + + $sql = "SELECT \"{$schema}\".moveset('{$set_id}', '{$new_origin}')"; + + return $data->execute($sql); + } + + /** + * Executes schema changing DDL set on nodes + */ + function executeReplicationSet($set_id, $script) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + $data->clean($script); + + $sql = "SELECT \"{$schema}\".ddlscript('{$set_id}', '{$script}')"; + + return $data->execute($sql); + } + + // TABLES + + /** + * Return all tables in a replication set + * @param $set_id The ID of the replication set + * @return Tables in the replication set, sorted alphabetically + */ + function getTables($set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + + $sql = "SELECT st.tab_id, c.relname, n.nspname, n.nspname||'.'||c.relname AS qualname, + pg_catalog.pg_get_userbyid(c.relowner) AS relowner, + reltuples::integer"; + // Tablespace + if ($data->hasTablespaces()) { + $sql .= ", (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace"; + } + $sql .= " FROM pg_catalog.pg_class c, \"{$schema}\".sl_table st, pg_catalog.pg_namespace n + WHERE c.oid=st.tab_reloid + AND c.relnamespace=n.oid + AND st.tab_set='{$set_id}' + ORDER BY n.nspname, c.relname"; + + return $data->selectSet($sql); + } + + /** + * Adds a table to a replication set + */ + function addTable($set_id, $tab_id, $nspname, $relname, $idxname, $comment, $storedtriggers) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + $data->clean($tab_id); + $fqname = $nspname . '.' . $relname; + $data->clean($fqname); + $data->clean($nspname); + $data->clean($relname); + $data->clean($idxname); + $data->clean($comment); + + $hastriggers = (sizeof($storedtriggers) > 0); + if ($hastriggers) { + // Begin a transaction + $status = $data->beginTransaction(); + if ($status != 0) return -1; + } + + if ($tab_id != '') + $sql = "SELECT \"{$schema}\".setaddtable('{$set_id}', '{$tab_id}', '{$fqname}', '{$idxname}', '{$comment}')"; + else { + $sql = "SELECT \"{$schema}\".setaddtable('{$set_id}', (SELECT COALESCE(MAX(tab_id), 0) + 1 FROM \"{$schema}\".sl_table), '{$fqname}', '{$idxname}', '{$comment}')"; + } + + $status = $data->execute($sql); + if ($status != 0) { + if ($hastriggers) $data->rollbackTransaction(); + return -3; + } + + // If we are storing triggers, we need to know the tab_id that was assigned to the table + if ($tab_id == '' && $hastriggers) { + $sql = "SELECT tab_id + FROM \"{$schema}\".sl_table + WHERE tab_set='{$set_id}' + AND tab_reloid=(SELECT pc.oid FROM pg_catalog.pg_class pc, pg_namespace pn + WHERE pc.relnamespace=pn.oid AND pc.relname='{$relname}' + AND pn.nspname='{$nspname}')"; + $tab_id = $data->selectField($sql, 'tab_id'); + if ($tab_id === -1) { + $data->rollbackTransaction(); + return -4; + } + } + + // Store requested triggers + if ($hastriggers) { + foreach ($storedtriggers as $tgname) { + $data->clean($tgname); + $sql = "SELECT \"{$schema}\".storetrigger('{$tab_id}', '{$tgname}')"; + $status = $data->execute($sql); + if ($status != 0) { + $data->rollbackTransaction(); + return -5; + } + } + } + + if ($hastriggers) + return $data->endTransaction(); + else + return $status; + } + + /** + * Removes a table from a replication set + */ + function removeTable($tab_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($tab_id); + + $sql = "SELECT \"{$schema}\".setdroptable('{$tab_id}')"; + + return $data->execute($sql); + } + + /** + * Moves a table to another replication set + */ + function moveTable($tab_id, $new_set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($tab_id); + $data->clean($new_set_id); + + $sql = "SELECT \"{$schema}\".setmovetable('{$tab_id}', '{$new_set_id}')"; + + return $data->execute($sql); + } + + // SEQUENCES + + /** + * Return all sequences in a replication set + * @param $set_id The ID of the replication set + * @return Sequences in the replication set, sorted alphabetically + */ + function getSequences($set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + + $sql = "SELECT ss.seq_id, c.relname AS seqname, n.nspname, n.nspname||'.'||c.relname AS qualname, + pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment, + pg_catalog.pg_get_userbyid(c.relowner) AS seqowner + FROM pg_catalog.pg_class c, \"{$schema}\".sl_sequence ss, pg_catalog.pg_namespace n + WHERE c.oid=ss.seq_reloid + AND c.relnamespace=n.oid + AND ss.seq_set='{$set_id}' + ORDER BY n.nspname, c.relname"; + + return $data->selectSet($sql); + } + + /** + * Adds a sequence to a replication set + */ + function addSequence($set_id, $seq_id, $fqname, $comment) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + $data->clean($seq_id); + $data->clean($fqname); + $data->clean($comment); + + if ($seq_id != '') + $sql = "SELECT \"{$schema}\".setaddsequence('{$set_id}', '{$seq_id}', '{$fqname}', '{$comment}')"; + else + $sql = "SELECT \"{$schema}\".setaddsequence('{$set_id}', (SELECT COALESCE(MAX(seq_id), 0) + 1 FROM \"{$schema}\".sl_sequence), '{$fqname}', '{$comment}')"; + + return $data->execute($sql); } + + /** + * Removes a sequence from a replication set + */ + function removeSequence($seq_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($seq_id); + + $sql = "SELECT \"{$schema}\".setdropsequence('{$seq_id}')"; + + return $data->execute($sql); + } + + /** + * Moves a sequence to another replication set + */ + function moveSequence($seq_id, $new_set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($seq_id); + $data->clean($new_set_id); + + $sql = "SELECT \"{$schema}\".setmovesequence('{$seq_id}', '{$new_set_id}')"; + + return $data->execute($sql); + } + + // SUBSCRIPTIONS + + /** + * Gets all nodes subscribing to a set + * @param $set_id The ID of the replication set + * @return Nodes subscribing to this set + */ + function getSubscribedNodes($set_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + + $sql = "SELECT sn.*, ss.sub_set + FROM \"{$schema}\".sl_subscribe ss, \"{$schema}\".sl_node sn + WHERE ss.sub_set='{$set_id}' + AND ss.sub_receiver = sn.no_id + ORDER BY sn.no_comment"; + + return $data->selectSet($sql); + } + + /** + * Gets all nodes subscribing to a set + * @param $set_id The ID of the replication set + * @return Nodes subscribing to this set + */ + function getSubscription($set_id, $no_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($set_id); + $data->clean($no_id); + + $sql = "SELECT ss.*, sn.no_comment AS receiver, sn2.no_comment AS provider + FROM \"{$schema}\".sl_subscribe ss, \"{$schema}\".sl_node sn, \"{$schema}\".sl_node sn2 + WHERE ss.sub_set='{$set_id}' + AND ss.sub_receiver = sn.no_id + AND ss.sub_provider = sn2.no_id + AND sn.no_id='{$no_id}'"; + + return $data->selectSet($sql); + } + + // NODES + + /** + * Gets node paths + */ + function getPaths($no_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + + $sql = "SELECT * FROM \"{$schema}\".sl_path sp, \"{$schema}\".sl_node sn + WHERE sp.pa_server=sn.no_id + AND sp.pa_client='{$no_id}' + ORDER BY sn.no_comment"; + + return $data->selectSet($sql); + } + + /** + * Gets node path details + */ + function getPath($no_id, $path_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($path_id); + + $sql = "SELECT * FROM \"{$schema}\".sl_path sp, \"{$schema}\".sl_node sn + WHERE sp.pa_server=sn.no_id + AND sp.pa_client='{$no_id}' + AND sn.no_id='{$path_id}'"; + + return $data->selectSet($sql); + } + + /** + * Creates a path + */ + function createPath($no_id, $server, $conn, $retry) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($server); + $data->clean($conn); + $data->clean($retry); + + $sql = "SELECT \"{$schema}\".storepath('{$server}', '{$no_id}', '{$conn}', '{$retry}')"; + + return $data->execute($sql); + } + + /** + * Drops a path + */ + function dropPath($no_id, $path_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($path_id); + + $sql = "SELECT \"{$schema}\".droppath('{$path_id}', '{$no_id}')"; + + return $data->execute($sql); + } + + // LISTENS + + /** + * Gets node listens + */ + function getListens($no_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + + $sql = "SELECT * FROM \"{$schema}\".sl_listen sl, \"{$schema}\".sl_node sn + WHERE sl.li_provider=sn.no_id + AND sl.li_receiver='{$no_id}' + ORDER BY sn.no_comment"; + + return $data->selectSet($sql); + } + + /** + * Gets node listen details + */ + function getListen($no_id, $listen_id) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($listen_id); + + $sql = "SELECT sl.*, sn.*, sn2.no_comment AS origin FROM \"{$schema}\".sl_listen sl, \"{$schema}\".sl_node sn, \"{$schema}\".sl_node sn2 + WHERE sl.li_provider=sn.no_id + AND sl.li_receiver='{$no_id}' + AND sn.no_id='{$listen_id}' + AND sn2.no_id=sl.li_origin"; + + return $data->selectSet($sql); + } + + /** + * Creates a listen + */ + function createListen($no_id, $origin, $provider) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($origin); + $data->clean($provider); + + $sql = "SELECT \"{$schema}\".storelisten('{$origin}', '{$provider}', '{$no_id}')"; + + return $data->execute($sql); + } + + /** + * Drops a listen + */ + function dropListen($no_id, $origin, $provider) { + global $data; + + $schema = $this->slony_schema; + $data->fieldClean($schema); + $data->clean($no_id); + $data->clean($origin); + $data->clean($provider); + + $sql = "SELECT \"{$schema}\".droplisten('{$origin}', '{$provider}', '{$no_id}')"; + + return $data->execute($sql); + } + + // ACTIONS + + + +} + +?> diff --git a/conf/slony.inc.php b/conf/slony.inc.php new file mode 100755 index 00000000..5094074a --- /dev/null +++ b/conf/slony.inc.php @@ -0,0 +1,11 @@ + diff --git a/database.php b/database.php index ae7566c6..4c9768aa 100755 --- a/database.php +++ b/database.php @@ -3,7 +3,7 @@ /** * Manage schemas within a database * - * $Id: database.php,v 1.68 2005/06/06 15:13:11 soranzo Exp $ + * $Id: database.php,v 1.69 2005/06/16 14:40:10 chriskl Exp $ */ // Include application functions @@ -710,7 +710,7 @@ } function doTree() { - global $misc, $data, $lang, $PHP_SELF; + global $misc, $data, $lang, $PHP_SELF, $slony; $schemas = &$data->getSchemas(); @@ -733,28 +733,49 @@ 'action' => 'subtree', 'schema' => field('nspname') ) - ) + ), + 'nofoot' => true ); $misc->printTreeXML($schemas, $attrs); + + $tabs = $misc->getNavTabs('database'); + $tabs['slony'] = array ( + 'title' => 'Slony', + 'url' => 'plugin_slony.php', + 'urlvars' => array('action' => 'clusters_properties'), + 'hide' => false, + 'help' => '' + ); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'tree') + ), + 'nohead' => true + ); + + $misc->printTreeXML($items, $attrs); + exit; } function doSubTree() { - global $misc, $data, $lang; + global $misc, $data, $lang, $slony; - include_once('classes/ArrayRecordSet.php'); $tabs = $misc->getNavTabs('schema'); - // Remove Privileges link - unset($tabs['privileges']); - - // Remove hidden links - foreach ($tabs as $i => $tab) { - if (isset($tab['hide']) && $tab['hide'] === true) - unset($tabs[$i]); - } - $items =& new ArrayRecordSet($tabs); + $items =& $misc->adjustTabsForTree($tabs); $reqvars = $misc->getRequestVars('schema'); diff --git a/lang/english.php b/lang/english.php index 12681c9d..b34e0015 100755 --- a/lang/english.php +++ b/lang/english.php @@ -4,7 +4,7 @@ * English language file for phpPgAdmin. Use this as a basis * for new translations. * - * $Id: english.php,v 1.177 2005/06/06 15:13:12 soranzo Exp $ + * $Id: english.php,v 1.178 2005/06/16 14:40:13 chriskl Exp $ */ // Language and character set @@ -95,6 +95,7 @@ $lang['strreindex'] = 'Reindex'; $lang['strrun'] = 'Run'; $lang['stradd'] = 'Add'; + $lang['strremove'] = 'Remove'; $lang['strevent'] = 'Event'; $lang['strwhere'] = 'Where'; $lang['strinstead'] = 'Do Instead'; @@ -315,8 +316,8 @@ $lang['strviewdroppedbad'] = 'View drop failed.'; $lang['strviewupdated'] = 'View updated.'; $lang['strviewupdatedbad'] = 'View update failed.'; - $lang['strviewlink'] = 'Linking Keys'; - $lang['strviewconditions'] = 'Additional Conditions'; + $lang['strviewlink'] = 'Linking keys'; + $lang['strviewconditions'] = 'Additional conditions'; $lang['strcreateviewwiz'] = 'Create view with wizard'; // Sequences @@ -637,6 +638,114 @@ $lang['strtablespacealtered'] = 'Tablespace altered.'; $lang['strtablespacealteredbad'] = 'Tablespace alteration failed.'; + // Slony clusters + $lang['strnoclusters'] = 'No clusters found.'; + $lang['strconfdropcluster'] = 'Are you sure you want to drop cluster "%s"?'; + $lang['strclusterdropped'] = 'Cluster dropped.'; + $lang['strclusterdroppedbad'] = 'Cluster drop failed.'; + $lang['strinitcluster'] = 'Initialize Cluster'; + $lang['strclustercreated'] = 'Cluster initialized.'; + $lang['strclustercreatedbad'] = 'Cluster initialization failed.'; + $lang['strclusterneedsname'] = 'You must give a name for the cluster.'; + $lang['strclusterneedsnodeid'] = 'You must give an ID for the local node.'; + + // Slony nodes + $lang['strnodes'] = 'Nodes'; + $lang['strnonodes'] = 'No nodes found.'; + $lang['strcreatenode'] = 'Create node'; + $lang['strid'] = 'ID'; + $lang['stractive'] = 'Active'; + $lang['strnodecreated'] = 'Node created.'; + $lang['strnodecreatedbad'] = 'Node creation failed.'; + $lang['strconfdropnode'] = 'Are you sure you want to drop node "%s"?'; + $lang['strnodedropped'] = 'Node dropped.'; + $lang['strnodedroppedbad'] = 'Node drop failed'; + $lang['strfailover'] = 'Failover'; + $lang['strnodefailedover'] = 'Node failed over.'; + $lang['strnodefailedoverbad'] = 'Node fail over fail.'; + + // Slony paths + $lang['strpaths'] = 'Paths'; + $lang['strnopaths'] = 'No paths found.'; + $lang['strcreatepath'] = 'Create path'; + $lang['strnodename'] = 'Node name'; + $lang['strnodeid'] = 'Node ID'; + $lang['strconninfo'] = 'Connection string'; + $lang['strconnretry'] = 'Seconds before retry to connect'; + $lang['strpathneedsconninfo'] = 'You must give a connection string for the path.'; + $lang['strpathneedsconnretry'] = 'You must give the number of seconds to wait before retry to connect.'; + $lang['strpathcreated'] = 'Path created.'; + $lang['strpathcreatedbad'] = 'Path creation failed.'; + $lang['strconfdroppath'] = 'Are you sure you want to drop path "%s"?'; + $lang['strpathdropped'] = 'Path dropped.'; + $lang['strpathdroppedbad'] = 'Path drop failed.'; + + // Slony listens + $lang['strlistens'] = 'Listens'; + $lang['strnolistens'] = 'No listens found.'; + $lang['strcreatelisten'] = 'Create listen'; + $lang['strlistencreated'] = 'Listen created.'; + $lang['strlistencreatedbad'] = 'Listen creation failed.'; + $lang['strconfdroplisten'] = 'Are you sure you want to drop listen "%s"?'; + $lang['strlistendropped'] = 'Listen dropped.'; + $lang['strlistendroppedbad'] = 'Listen drop failed.'; + + // Slony replication sets + $lang['strrepsets'] = 'Replication sets'; + $lang['strnorepsets'] = 'No replication sets found.'; + $lang['strcreaterepset'] = 'Create replication set'; + $lang['strrepsetcreated'] = 'Replication set created.'; + $lang['strrepsetcreatedbad'] = 'Replication set creation failed.'; + $lang['strconfdroprepset'] = 'Are you sure you want to drop replication set "%s"?'; + $lang['strrepsetdropped'] = 'Replication set dropped.'; + $lang['strrepsetdroppedbad'] = 'Replication set drop failed.'; + $lang['strmerge'] = 'Merge'; + $lang['strmergeinto'] = 'Merge Into'; + $lang['strrepsetmerged'] = 'Replication sets merged.'; + $lang['strrepsetmergedbad'] = 'Replication sets merge failed.'; + $lang['strmove'] = 'Move'; + $lang['strneworigin'] = 'New Origin'; + $lang['strrepsetmoved'] = 'Replication set moved.'; + $lang['strrepsetmovedbad'] = 'Replication set move failed.'; + $lang['strnewrepset'] = 'New replication set'; + $lang['strlock'] = 'Lock'; + $lang['strlocked'] = 'Locked'; + $lang['strunlock'] = 'Unlock'; + $lang['strconflockrepset'] = 'Are you sure you want to lock replication set "%s"?'; + $lang['strrepsetlocked'] = 'Replication set locked.'; + $lang['strrepsetlockedbad'] = 'Replication set lock failed.'; + $lang['strconfunlockrepset'] = 'Are you sure you want to unlock replication set "%s"?'; + $lang['strrepsetunlocked'] = 'Replication set unlocked.'; + $lang['strrepsetunlockedbad'] = 'Replication set unlock failed.'; + $lang['strexecute'] = 'Execute'; + $lang['stronlyonnode'] = 'Only on node'; + $lang['strddlscript'] = 'DDL Script'; + $lang['strscriptneedsbody'] = 'You must supply a script to be executed on all nodes.'; + $lang['strscriptexecuted'] = 'Replication set DDL script executed.'; + $lang['strscriptexecutedbad'] = 'Failed executing replication set DDL script.'; + $lang['strtabletriggerstoretain'] = 'The following triggers will NOT be disabled by Slony:'; + + // Slony tables in replication sets + $lang['straddtable'] = 'Add table'; + $lang['strtableneedsuniquekey'] = 'Table to be added requires a primary or unique key.'; + $lang['strtableaddedtorepset'] = 'Table added to replication set.'; + $lang['strtableaddedtorepsetbad'] = 'Failed adding table to replication set.'; + $lang['strconfremovetablefromrepset'] = 'Are you sure you want to remove the table "%s" from replication set "%s"?'; + $lang['strtableremovedfromrepset'] = 'Table removed from replication set.'; + $lang['strtableremovedfromrepsetbad'] = 'Failed to remove table from replication set.'; + + // Slony sequences in replication sets + $lang['straddsequence'] = 'Add sequence'; + $lang['strsequenceaddedtorepset'] = 'Sequence added to replication set.'; + $lang['strsequenceaddedtorepsetbad'] = 'Failed adding sequence to replication set.'; + $lang['strconfremovesequencefromrepset'] = 'Are you sure you want to remove the sequence "%s" from replication set "%s"?'; + $lang['strsequenceremovedfromrepset'] = 'Sequence removed from replication set.'; + $lang['strsequenceremovedfromrepsetbad'] = 'Failed to remove sequence from replication set.'; + + // Slony subscriptions + $lang['strsubscriptions'] = 'Subscriptions'; + $lang['strnosubscriptions'] = 'No subscriptions found.'; + // Miscellaneous $lang['strtopbar'] = '%s running on %s:%s -- You are logged in as user "%s", %s'; $lang['strtimefmt'] = 'jS M, Y g:iA'; diff --git a/lang/recoded/english.php b/lang/recoded/english.php index ce1cc1bf..fc7ada89 100644 --- a/lang/recoded/english.php +++ b/lang/recoded/english.php @@ -4,7 +4,7 @@ * English language file for phpPgAdmin. Use this as a basis * for new translations. * - * $Id: english.php,v 1.129 2005/06/06 15:13:13 soranzo Exp $ + * $Id: english.php,v 1.130 2005/06/16 14:40:13 chriskl Exp $ */ // Language and character set @@ -95,6 +95,7 @@ $lang['strreindex'] = 'Reindex'; $lang['strrun'] = 'Run'; $lang['stradd'] = 'Add'; + $lang['strremove'] = 'Remove'; $lang['strevent'] = 'Event'; $lang['strwhere'] = 'Where'; $lang['strinstead'] = 'Do Instead'; @@ -315,8 +316,8 @@ $lang['strviewdroppedbad'] = 'View drop failed.'; $lang['strviewupdated'] = 'View updated.'; $lang['strviewupdatedbad'] = 'View update failed.'; - $lang['strviewlink'] = 'Linking Keys'; - $lang['strviewconditions'] = 'Additional Conditions'; + $lang['strviewlink'] = 'Linking keys'; + $lang['strviewconditions'] = 'Additional conditions'; $lang['strcreateviewwiz'] = 'Create view with wizard'; // Sequences @@ -637,6 +638,114 @@ $lang['strtablespacealtered'] = 'Tablespace altered.'; $lang['strtablespacealteredbad'] = 'Tablespace alteration failed.'; + // Slony clusters + $lang['strnoclusters'] = 'No clusters found.'; + $lang['strconfdropcluster'] = 'Are you sure you want to drop cluster "%s"?'; + $lang['strclusterdropped'] = 'Cluster dropped.'; + $lang['strclusterdroppedbad'] = 'Cluster drop failed.'; + $lang['strinitcluster'] = 'Initialize Cluster'; + $lang['strclustercreated'] = 'Cluster initialized.'; + $lang['strclustercreatedbad'] = 'Cluster initialization failed.'; + $lang['strclusterneedsname'] = 'You must give a name for the cluster.'; + $lang['strclusterneedsnodeid'] = 'You must give an ID for the local node.'; + + // Slony nodes + $lang['strnodes'] = 'Nodes'; + $lang['strnonodes'] = 'No nodes found.'; + $lang['strcreatenode'] = 'Create node'; + $lang['strid'] = 'ID'; + $lang['stractive'] = 'Active'; + $lang['strnodecreated'] = 'Node created.'; + $lang['strnodecreatedbad'] = 'Node creation failed.'; + $lang['strconfdropnode'] = 'Are you sure you want to drop node "%s"?'; + $lang['strnodedropped'] = 'Node dropped.'; + $lang['strnodedroppedbad'] = 'Node drop failed'; + $lang['strfailover'] = 'Failover'; + $lang['strnodefailedover'] = 'Node failed over.'; + $lang['strnodefailedoverbad'] = 'Node fail over fail.'; + + // Slony paths + $lang['strpaths'] = 'Paths'; + $lang['strnopaths'] = 'No paths found.'; + $lang['strcreatepath'] = 'Create path'; + $lang['strnodename'] = 'Node name'; + $lang['strnodeid'] = 'Node ID'; + $lang['strconninfo'] = 'Connection string'; + $lang['strconnretry'] = 'Seconds before retry to connect'; + $lang['strpathneedsconninfo'] = 'You must give a connection string for the path.'; + $lang['strpathneedsconnretry'] = 'You must give the number of seconds to wait before retry to connect.'; + $lang['strpathcreated'] = 'Path created.'; + $lang['strpathcreatedbad'] = 'Path creation failed.'; + $lang['strconfdroppath'] = 'Are you sure you want to drop path "%s"?'; + $lang['strpathdropped'] = 'Path dropped.'; + $lang['strpathdroppedbad'] = 'Path drop failed.'; + + // Slony listens + $lang['strlistens'] = 'Listens'; + $lang['strnolistens'] = 'No listens found.'; + $lang['strcreatelisten'] = 'Create listen'; + $lang['strlistencreated'] = 'Listen created.'; + $lang['strlistencreatedbad'] = 'Listen creation failed.'; + $lang['strconfdroplisten'] = 'Are you sure you want to drop listen "%s"?'; + $lang['strlistendropped'] = 'Listen dropped.'; + $lang['strlistendroppedbad'] = 'Listen drop failed.'; + + // Slony replication sets + $lang['strrepsets'] = 'Replication sets'; + $lang['strnorepsets'] = 'No replication sets found.'; + $lang['strcreaterepset'] = 'Create replication set'; + $lang['strrepsetcreated'] = 'Replication set created.'; + $lang['strrepsetcreatedbad'] = 'Replication set creation failed.'; + $lang['strconfdroprepset'] = 'Are you sure you want to drop replication set "%s"?'; + $lang['strrepsetdropped'] = 'Replication set dropped.'; + $lang['strrepsetdroppedbad'] = 'Replication set drop failed.'; + $lang['strmerge'] = 'Merge'; + $lang['strmergeinto'] = 'Merge Into'; + $lang['strrepsetmerged'] = 'Replication sets merged.'; + $lang['strrepsetmergedbad'] = 'Replication sets merge failed.'; + $lang['strmove'] = 'Move'; + $lang['strneworigin'] = 'New Origin'; + $lang['strrepsetmoved'] = 'Replication set moved.'; + $lang['strrepsetmovedbad'] = 'Replication set move failed.'; + $lang['strnewrepset'] = 'New replication set'; + $lang['strlock'] = 'Lock'; + $lang['strlocked'] = 'Locked'; + $lang['strunlock'] = 'Unlock'; + $lang['strconflockrepset'] = 'Are you sure you want to lock replication set "%s"?'; + $lang['strrepsetlocked'] = 'Replication set locked.'; + $lang['strrepsetlockedbad'] = 'Replication set lock failed.'; + $lang['strconfunlockrepset'] = 'Are you sure you want to unlock replication set "%s"?'; + $lang['strrepsetunlocked'] = 'Replication set unlocked.'; + $lang['strrepsetunlockedbad'] = 'Replication set unlock failed.'; + $lang['strexecute'] = 'Execute'; + $lang['stronlyonnode'] = 'Only on node'; + $lang['strddlscript'] = 'DDL Script'; + $lang['strscriptneedsbody'] = 'You must supply a script to be executed on all nodes.'; + $lang['strscriptexecuted'] = 'Replication set DDL script executed.'; + $lang['strscriptexecutedbad'] = 'Failed executing replication set DDL script.'; + $lang['strtabletriggerstoretain'] = 'The following triggers will NOT be disabled by Slony:'; + + // Slony tables in replication sets + $lang['straddtable'] = 'Add table'; + $lang['strtableneedsuniquekey'] = 'Table to be added requires a primary or unique key.'; + $lang['strtableaddedtorepset'] = 'Table added to replication set.'; + $lang['strtableaddedtorepsetbad'] = 'Failed adding table to replication set.'; + $lang['strconfremovetablefromrepset'] = 'Are you sure you want to remove the table "%s" from replication set "%s"?'; + $lang['strtableremovedfromrepset'] = 'Table removed from replication set.'; + $lang['strtableremovedfromrepsetbad'] = 'Failed to remove table from replication set.'; + + // Slony sequences in replication sets + $lang['straddsequence'] = 'Add sequence'; + $lang['strsequenceaddedtorepset'] = 'Sequence added to replication set.'; + $lang['strsequenceaddedtorepsetbad'] = 'Failed adding sequence to replication set.'; + $lang['strconfremovesequencefromrepset'] = 'Are you sure you want to remove the sequence "%s" from replication set "%s"?'; + $lang['strsequenceremovedfromrepset'] = 'Sequence removed from replication set.'; + $lang['strsequenceremovedfromrepsetbad'] = 'Failed to remove sequence from replication set.'; + + // Slony subscriptions + $lang['strsubscriptions'] = 'Subscriptions'; + $lang['strnosubscriptions'] = 'No subscriptions found.'; + // Miscellaneous $lang['strtopbar'] = '%s running on %s:%s -- You are logged in as user "%s", %s'; $lang['strtimefmt'] = 'jS M, Y g:iA'; diff --git a/libraries/lib.inc.php b/libraries/lib.inc.php index 0070c728..761749bc 100644 --- a/libraries/lib.inc.php +++ b/libraries/lib.inc.php @@ -3,7 +3,7 @@ /** * Function library read in upon startup * - * $Id: lib.inc.php,v 1.95 2005/06/02 11:31:03 chriskl Exp $ + * $Id: lib.inc.php,v 1.96 2005/06/16 14:40:14 chriskl Exp $ */ include_once('decorator.inc.php'); include_once('./lang/translations.php'); @@ -15,7 +15,7 @@ $appName = 'phpPgAdmin'; // Application version - $appVersion = '3.6-dev'; + $appVersion = '4.0-dev'; // PostgreSQL and PHP minimum version $postgresqlMinVer = '7.0'; @@ -197,6 +197,15 @@ else $lang['appcharset'] = $dbEncoding; } + + // XXX: THIS IS A TEMPORARY HACK TO GET SLONY OBJECT LOADED + include('./classes/plugins/Slony.php'); + $slony = new Slony(); + if ($slony->isEnabled()) { + $plugins = array(&$slony); + } + else $plugins = array(); + } ?> diff --git a/plugin_slony.php b/plugin_slony.php new file mode 100755 index 00000000..fc179176 --- /dev/null +++ b/plugin_slony.php @@ -0,0 +1,2235 @@ +getRequestVars('database'); + + // Determine what actual tree we are building + switch ($subject) { + case 'clusters': + // Clusters + + // Enabled check here is just a hack. + if ($slony->isEnabled()) { + $tabs = array('cluster' => array ( + 'title' => $slony->slony_cluster, + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'clusters_top') + )); + } + else $tabs = array(); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + array( + 'action' => 'cluster_properties' + ) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'clusters_top') + ), + ); + + $misc->printTreeXML($items, $attrs); + + break; + case 'clusters_top': + // Top level Nodes and Replication sets folders + $tabs = array('nodes' => array ( + 'title' => $lang['strnodes'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'nodes') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'nodes_properties') + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'nodes') + ), + 'nofoot' => true + ); + + $misc->printTreeXML($items, $attrs); + + $tabs = array('sets' => array ( + 'title' => $lang['strrepsets'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'sets') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'sets_properties') + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'sets') + ), + 'nohead' => true + ); + + $misc->printTreeXML($items, $attrs); + + break; + case 'nodes': + $nodes = &$slony->getNodes(); + + $attrs = array( + 'text' => field('no_comment'), + 'icon' => 'folder', + 'action' => url('plugin_slony.php', + $reqvars, + array( + 'action' => 'node_properties', + 'no_id' => field('no_id') + ) + ), + 'branch' => url('plugin_slony.php', + $reqvars, + array( + 'action' => 'nodes_top', + 'no_id' => field('no_id') + ) + ) + ); + + $misc->printTreeXML($nodes, $attrs); + + break; + case 'nodes_top': + // Nodes paths and listens entries + + $tabs = array('paths' => array ( + 'title' => $lang['strpaths'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'paths') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'paths_properties', 'no_id' => $_REQUEST['no_id']) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'paths', 'no_id' => $_REQUEST['no_id']) + ), + 'nofoot' => true + ); + + $misc->printTreeXML($items, $attrs); + + $tabs = array('listens' => array ( + 'title' => $lang['strlistens'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'listens') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'listens_properties', 'no_id' => $_REQUEST['no_id']) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'listens', 'no_id' => $_REQUEST['no_id']) + ), + 'nohead' => true + ); + + $misc->printTreeXML($items, $attrs); + + break; + case 'paths': + $tables = &$slony->getPaths($_REQUEST['no_id']); + + $attrs = array( + 'text' => field('no_comment'), + 'icon' => field('icon', 'folder'), + 'action' => url('plugin_slony.php', + $reqvars, + array('no_id' => field('pa_client'), 'path_id' => field('no_id'), 'action' => 'path_properties') + ) + ); + + $misc->printTreeXML($tables, $attrs); + + break; + case 'listens': + $tables = &$slony->getListens($_REQUEST['no_id']); + + $attrs = array( + 'text' => field('no_comment'), + 'icon' => field('icon', 'folder'), + 'action' => url('plugin_slony.php', + $reqvars, + array('no_id' => field('li_receiver'), 'listen_id' => field('no_id'), 'action' => 'listen_properties') + ) + ); + + $misc->printTreeXML($tables, $attrs); + + break; + case 'sets': + $sets = &$slony->getReplicationSets(); + + $attrs = array( + 'text' => field('set_comment'), + 'icon' => 'folder', + 'action' => url('plugin_slony.php', + $reqvars, + array( + 'action' => 'set_properties', + 'set_id' => field('set_id') + ) + ), + 'branch' => url('plugin_slony.php', + $reqvars, + array( + 'action' => 'sets_top', + 'set_id' => field('set_id') + ) + ) + ); + + $misc->printTreeXML($sets, $attrs); + break; + case 'sets_top': + // Top level Nodes and Replication sets folders + + $tabs = array('sequences' => array ( + 'title' => $lang['strsequences'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'sequences') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'sequences'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'sequences_properties', 'set_id' => $_REQUEST['set_id']) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'sequences', 'set_id' => $_REQUEST['set_id']) + ), + 'nofoot' => true + ); + + $misc->printTreeXML($items, $attrs); + + $tabs = array('tables' => array ( + 'title' => $lang['strtables'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'tables') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'tables'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'tables_properties', 'set_id' => $_REQUEST['set_id']) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'tables', 'set_id' => $_REQUEST['set_id']) + ), + 'nohead' => true, + 'nofoot' => true + ); + + $misc->printTreeXML($items, $attrs); + + $tabs = array('subscriptions' => array ( + 'title' => $lang['strsubscriptions'], + 'url' => 'plugin_slony.php', + 'urlvars' => array('subject' => 'subscriptions') + )); + + $items =& $misc->adjustTabsForTree($tabs); + + $attrs = array( + 'text' => noEscape(field('title')), + 'icon' => field('icon', 'folder'), + 'action' => url(field('url'), + $reqvars, + field('urlvars', array()), + array('action' => 'subscriptions_properties', 'set_id' => $_REQUEST['set_id']) + ), + 'branch' => url(field('url'), + $reqvars, + field('urlvars'), + array('action' => 'subscriptions', 'set_id' => $_REQUEST['set_id']) + ), + 'nohead' => true + ); + + $misc->printTreeXML($items, $attrs); + + break; + case 'sequences': + $tables = &$slony->getSequences($_REQUEST['set_id']); + + $reqvars = $misc->getRequestVars('sequence'); + + $attrs = array( + 'text' => field('qualname'), + 'icon' => 'sequences', + 'toolTip'=> field('seqcomment'), + 'action' => url('sequences.php', + $reqvars, + array ( + 'action' => 'properties', + 'sequence' => field('seqname'), + 'schema' => field('nspname') + ) + ) + ); + + $misc->printTreeXML($tables, $attrs); + + break; + case 'tables': + $tables = &$slony->getTables($_REQUEST['set_id']); + + $reqvars = $misc->getRequestVars('table'); + + $attrs = array( + 'text' => field('qualname'), + 'icon' => 'tables', + 'toolTip'=> field('relcomment'), + 'action' => url('redirect.php', + $reqvars, + array('table' => field('relname'), 'schema' => field('nspname')) + ) + ); + + $misc->printTreeXML($tables, $attrs); + + break; + case 'subscriptions': + $tables = &$slony->getSubscribedNodes($_REQUEST['set_id']); + + $attrs = array( + 'text' => field('no_comment'), + 'icon' => field('icon', 'folder'), + 'action' => url('plugin_slony.php', + $reqvars, + array('set_id' => field('sub_set'), 'no_id' => field('no_id'), 'action' => 'subscription_properties') + ) + ); + + $misc->printTreeXML($tables, $attrs); + + break; + } + + exit; + } + + /** + * Display the slony clusters (we only support one) + */ + function doClusters($msg = '') { + global $PHP_SELF, $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $clusters = $slony->getClusters(); + + $columns = array( + 'no_name' => array( + 'title' => $lang['strcluster'], + 'field' => 'cluster' + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'no_comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'comment' + ) + ); + + $actions = array ( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "plugin_slony.php?{$misc->href}&action=cluster_properties&", + 'vars' => array() + ), + 'drop' => array( + 'title' => $lang['strdrop'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_cluster&", + 'vars' => array() + ) + ); + + $misc->printTable($clusters, $columns, $actions, $lang['strnoclusters']); + + // XXX: FIX THIS ONCE WE SUPPORT MULTIPLE CLUSTERS + if ($clusters->recordCount() == 0) { + echo "

href}\">{$lang['strinitcluster']}

\n"; + } + } + + // CLUSTERS + + /** + * Display the properties of a slony cluster + */ + function doCluster($msg = '') { + global $data, $slony, $misc, $PHP_SELF; + global $lang; + + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strproperties']); + $misc->printMsg($msg); + + // Fetch the cluster information + $cluster = &$slony->getCluster(); + + if (is_object($cluster) && $cluster->recordCount() > 0) { + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
{$lang['strname']}", $misc->printVal($slony->slony_cluster), "
Local Node ID", $misc->printVal($cluster->f['no_id']), "
Local Node", $misc->printVal($cluster->f['no_comment']), "
Version", $misc->printVal($cluster->f['version']), "
{$lang['strowner']}", $misc->printVal($slony->slony_owner), "
{$lang['strcomment']}
\n"; + } + else echo "

{$lang['strnodata']}

\n"; + } + + /** + * Displays a screen where they can enter a new cluster + */ + function doCreateCluster($confirm, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['cluster'])) $_POST['cluster'] = ''; + if (!isset($_POST['no_id'])) $_POST['no_id'] = '1'; + if (!isset($_POST['no_comment'])) $_POST['no_comment'] = ''; + + $misc->printTrail('slony_clusters'); + $misc->printTitle($lang['strinitcluster']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "
{$lang['strcluster']}_maxNameLen}\" value=\"", + htmlspecialchars($_POST['cluster']), "\" />
{$lang['strid']}
{$lang['strcomment']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + if (trim($_POST['cluster']) == '') { + doCreateCluster(true, $lang['strclusterneedsname']); + return; + } + elseif (trim($_POST['no_id']) == '') { + doCreateCluster(true, $lang['strclusterneedsnodeid']); + return; + } + + $status = $slony->initCluster($_POST['cluster'], $_POST['no_id'], $_POST['no_comment']); + if ($status == 0) + doClusters($lang['strclustercreated']); + else + doCreateCluster(true, $lang['strclustercreatedbad'] . ':' . $status); + } + } + + /** + * Show confirmation of drop and perform actual drop of a cluster + */ + function doDropCluster($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strdrop']); + + echo "

", sprintf($lang['strconfdropcluster'], $misc->printVal($slony->slony_cluster)), "

\n"; + + echo "
\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->dropCluster(); + if ($status == 0) + doClusters($lang['strclusterdropped']); + else + doClusters($lang['strclusterdroppedbad']); + } + } + + // NODES + + /** + * List all the nodes + */ + function doNodes($msg = '') { + global $PHP_SELF, $slony, $misc; + global $lang; + + $misc->printTrail('slony_nodes'); + $misc->printMsg($msg); + + $nodes = $slony->getNodes(); + + $columns = array( + 'no_name' => array( + 'title' => $lang['strname'], + 'field' => 'no_comment' + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'no_comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'no_comment' + ) + ); + + $actions = array ( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "plugin_slony.php?{$misc->href}&action=node_properties&", + 'vars' => array('no_id' => 'no_id') + ), + 'drop' => array( + 'title' => $lang['strdrop'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_node&", + 'vars' => array('no_id' => 'no_id') + ) + ); + + $misc->printTable($nodes, $columns, $actions, $lang['strnonodes']); + + echo "

href}\">{$lang['strcreatenode']}

\n"; + } + + /** + * Display the properties of a node + */ + function doNode($msg = '') { + global $data, $slony, $misc, $PHP_SELF; + global $lang; + + $misc->printTrail('slony_node'); + $misc->printTitle($lang['strproperties']); + $misc->printMsg($msg); + + // Fetch the node information + $node = &$slony->getNode($_REQUEST['no_id']); + + if (is_object($node) && $node->recordCount() > 0) { + // Show comment if any + if ($node->f['no_comment'] !== null) + echo "

", $misc->printVal($node->f['no_comment']), "

\n"; + + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
{$lang['strname']}", $misc->printVal($node->f['no_comment']), "
{$lang['strid']}", $misc->printVal($node->f['no_id']), "
{$lang['stractive']}", ($data->phpBool($node->f['no_active'])) ? $lang['stryes'] : $lang['strno'], "
{$lang['strcomment']}", $misc->printVal($node->f['no_comment']), "
\n"; + } + else echo "

{$lang['strnodata']}

\n"; + + echo "

href}&no_id={$_REQUEST['no_id']}\">{$lang['strdrop']}

\n"; + } + + /** + * Displays a screen where they can enter a new node + */ + function doCreateNode($confirm, $msg = '') { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['nodeid'])) $_POST['nodeid'] = ''; + if (!isset($_POST['nodecomment'])) $_POST['nodecomment'] = ''; + + $misc->printTrail('slony_nodes'); + $misc->printTitle($lang['strcreatenode']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + + echo "\t\n"; + echo "
{$lang['strid']}
{$lang['strcomment']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + $status = $slony->createNode($_POST['nodeid'], $_POST['nodecomment']); + if ($status == 0) + doNodes($lang['strnodecreated']); + else + doCreateNode(true, $lang['strnodecreatedbad']); + } + } + + /** + * Show confirmation of drop and perform actual drop of a node + */ + function doDropNode($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strdrop']); + + echo "

", sprintf($lang['strconfdropnode'], $misc->printVal($_REQUEST['no_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->dropNode($_REQUEST['no_id']); + if ($status == 0) + doNodes($lang['strnodedropped']); + else + doNodes($lang['strnodedroppedbad']); + } + } + + // PATHS + + /** + * List all the paths + */ + function doPaths($msg = '') { + global $PHP_SELF, $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $paths = $slony->getPaths($_REQUEST['no_id']); + + $columns = array( + 'no_name' => array( + 'title' => $lang['strname'], + 'field' => 'no_comment' + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'no_comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'no_comment' + ) + ); + + $actions = array ( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "plugin_slony.php?{$misc->href}&action=path_properties&", + 'vars' => array('no_id' => 'pa_client', 'path_id' => 'no_id') + ), + 'drop' => array( + 'title' => $lang['strdrop'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_path&", + 'vars' => array('no_id' => 'pa_client', 'path_id' => 'no_id') + ) + ); + + $misc->printTable($paths, $columns, $actions, $lang['strnopaths']); + + echo "

href}&no_id={$_REQUEST['no_id']}\">{$lang['strcreatepath']}

\n"; + } + + /** + * Display the properties of a path + */ + function doPath($msg = '') { + global $data, $slony, $misc, $PHP_SELF; + global $lang; + + $misc->printTrail('slony_path'); + $misc->printTitle($lang['strproperties']); + $misc->printMsg($msg); + + // Fetch the path information + $path = &$slony->getPath($_REQUEST['no_id'], $_REQUEST['path_id']); + + if (is_object($path) && $path->recordCount() > 0) { + // Show comment if any + if ($path->f['no_comment'] !== null) + echo "

", $misc->printVal($path->f['no_comment']), "

\n"; + + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
{$lang['strnodename']}", $misc->printVal($path->f['no_comment']), "
{$lang['strnodeid']}", $misc->printVal($path->f['no_id']), "
{$lang['strconninfo']}", $misc->printVal($path->f['pa_conninfo']), "
{$lang['strconnretry']}", $misc->printVal($path->f['pa_connretry']), "
\n"; + } + else echo "

{$lang['strnodata']}

\n"; + + echo "

href}&no_id={$_REQUEST['no_id']}&path_id={$_REQUEST['path_id']}\">{$lang['strdrop']}

\n"; + } + + /** + * Displays a screen where they can enter a new path + */ + function doCreatePath($confirm, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['pathserver'])) $_POST['pathserver'] = ''; + if (!isset($_POST['pathconn'])) $_POST['pathconn'] = ''; + if (!isset($_POST['pathretry'])) $_POST['pathretry'] = '10'; + + // Fetch all servers + $nodes = &$slony->getNodes(); + + $misc->printTrail('slony_paths'); + $misc->printTitle($lang['strcreatepath']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + + echo "\t\n"; + echo "
{$lang['strnodename']}\n\t\t\t\n\t\t
{$lang['strconninfo']}_maxNameLen}\" value=\"", + htmlspecialchars($_POST['pathconn']), "\" />
{$lang['strconnretry']}_maxNameLen}\" value=\"", + htmlspecialchars($_POST['pathretry']), "\" />
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + if (trim($_POST['pathconn']) == '') { + doCreatePath(true, $lang['strpathneedsconninfo']); + return; + } + elseif (trim($_POST['pathretry']) == '') { + doCreatePath(true, $lang['strpathneedsconnretry']); + return; + } + + $status = $slony->createPath($_POST['no_id'], $_POST['pathserver'], $_POST['pathconn'], $_POST['pathretry']); + if ($status == 0) + doPaths($lang['strpathcreated']); + else + doCreatePath(true, $lang['strpathcreatedbad']); + } + } + + /** + * Show confirmation of drop and perform actual drop of a path + */ + function doDropPath($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strdrop']); + + echo "

", sprintf($lang['strconfdroppath'], $misc->printVal($_REQUEST['path_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->dropPath($_REQUEST['no_id'], $_REQUEST['path_id']); + if ($status == 0) + doPaths($lang['strpathdropped']); + else + doPaths($lang['strpathdroppedbad']); + } + } + + // LISTENS + + /** + * List all the listens + */ + function doListens($msg = '') { + global $PHP_SELF, $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $listens = $slony->getListens($_REQUEST['no_id']); + + $columns = array( + 'no_name' => array( + 'title' => $lang['strname'], + 'field' => 'no_comment' + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'no_comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'no_comment' + ) + ); + + $actions = array ( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "plugin_slony.php?{$misc->href}&action=listen_properties&", + 'vars' => array('no_id' => 'li_receiver', 'listen_id' => 'no_id', 'origin_id' => 'li_origin') + ), + 'drop' => array( + 'title' => $lang['strdrop'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_listen&", + 'vars' => array('no_id' => 'li_receiver', 'listen_id' => 'no_id', 'origin_id' => 'li_origin') + ) + + ); + + $misc->printTable($listens, $columns, $actions, $lang['strnolistens']); + + echo "

href}&no_id={$_REQUEST['no_id']}\">{$lang['strcreatelisten']}

\n"; + } + + /** + * Display the properties of a listen + */ + function doListen($msg = '') { + global $data, $slony, $misc, $PHP_SELF; + global $lang; + + $misc->printTrail('slony_path'); + $misc->printTitle($lang['strproperties']); + $misc->printMsg($msg); + + // Fetch the listen information + $listen = &$slony->getListen($_REQUEST['no_id'], $_REQUEST['listen_id']); + + if (is_object($listen) && $listen->recordCount() > 0) { + // Show comment if any + if ($listen->f['no_comment'] !== null) + echo "

", $misc->printVal($listen->f['no_comment']), "

\n"; + + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
Provider", $misc->printVal($listen->f['no_comment']), "
Provider ID", $misc->printVal($listen->f['li_provider']), "
Origin", $misc->printVal($listen->f['origin']), "
Origin ID", $misc->printVal($listen->f['li_origin']), "
\n"; + } + else echo "

{$lang['strnodata']}

\n"; + + echo "

href}&no_id={$_REQUEST['no_id']}&listen_id={$_REQUEST['listen_id']}&origin_id={$listen->f['li_origin']}\">{$lang['strdrop']}

\n"; + } + + /** + * Displays a screen where they can enter a new listen + */ + function doCreateListen($confirm, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['listenorigin'])) $_POST['listenorigin'] = ''; + if (!isset($_POST['listenprovider'])) $_POST['listenprovider'] = ''; + + // Fetch all servers + $nodes = &$slony->getNodes(); + + $misc->printTrail('slony_listens'); + $misc->printTitle($lang['strcreatelisten']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n"; + echo "
Origin\n\t\t\t\n\t\t
Provider\n\t\t\t\n\t\t
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + $status = $slony->createListen($_POST['no_id'], $_POST['listenorigin'], $_POST['listenprovider']); + if ($status == 0) + doListens($lang['strlistencreated']); + else + doCreateListen(true, $lang['strlistencreatedbad']); + } + } + + /** + * Show confirmation of drop and perform actual drop of a listen + */ + function doDropListen($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strdrop']); + + echo "

", sprintf($lang['strconfdroplisten'], $misc->printVal($_REQUEST['listen_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->dropListen($_REQUEST['no_id'], $_REQUEST['origin_id'], $_REQUEST['listen_id']); + if ($status == 0) + doListens($lang['strlistendropped']); + else + doListens($lang['strlistendroppedbad']); + } + } + + // REPLICATION SETS + + /** + * List all the replication sets + */ + function doReplicationSets($msg = '') { + global $PHP_SELF, $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $sets = $slony->getReplicationSets(); + + $columns = array( + 'set_name' => array( + 'title' => $lang['strname'], + 'field' => 'set_comment' + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'set_comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'set_comment' + ) + ); + + $actions = array ( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "plugin_slony.php?{$misc->href}&action=set_properties&", + 'vars' => array('set_id' => 'set_id') + ), + 'drop' => array( + 'title' => $lang['strdrop'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_set&", + 'vars' => array('set_id' => 'set_id') + ), + 'lock' => array( + 'title' => $lang['strlock'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_lock_set&", + 'vars' => array('set_id' => 'set_id') + ), + 'unlock' => array( + 'title' => $lang['strunlock'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_unlock_set&", + 'vars' => array('set_id' => 'set_id') + ), + 'merge' => array( + 'title' => $lang['strmerge'], + 'url' => "plugin_slony.php?{$misc->href}&action=merge_set&", + 'vars' => array('set_id' => 'set_id') + ), + 'move' => array( + 'title' => $lang['strmove'], + 'url' => "plugin_slony.php?{$misc->href}&action=move_set&", + 'vars' => array('set_id' => 'set_id') + ), + 'execute' => array( + 'title' => $lang['strexecute'], + 'url' => "plugin_slony.php?{$misc->href}&action=execute_set&", + 'vars' => array('set_id' => 'set_id') + ) + ); + + $misc->printTable($sets, $columns, $actions, $lang['strnorepsets']); + + echo "

href}\">{$lang['strcreaterepset']}

\n"; + } + + /** + * Display the properties of a replication set + */ + function doReplicationSet($msg = '') { + global $data, $slony, $misc, $PHP_SELF; + global $lang; + + $misc->printTrail('slony_set'); + $misc->printTitle($lang['strproperties']); + $misc->printMsg($msg); + + // Fetch the set information + $set = &$slony->getReplicationSet($_REQUEST['set_id']); + + if (is_object($set) && $set->recordCount() > 0) { + // Show comment if any + if ($set->f['set_comment'] !== null) + echo "

", $misc->printVal($set->f['set_comment']), "

\n"; + + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
{$lang['strname']}", $misc->printVal($set->f['set_comment']), "
{$lang['strid']}", $misc->printVal($set->f['set_id']), "
{$lang['strlocked']}", ($data->phpBool($set->f['is_locked'])) ? $lang['stryes'] : $lang['strno'], "
Origin ID", $misc->printVal($set->f['set_origin']), "
Origin Node", $misc->printVal($set->f['no_comment']), "
Subscriptions", $misc->printVal($set->f['subscriptions']), "
{$lang['strcomment']}", $misc->printVal($set->f['set_comment']), "
\n"; + } + else echo "

{$lang['strnodata']}

\n"; + + echo "

href}&set_id={$_REQUEST['set_id']}\">{$lang['strdrop']} |\n"; + echo "href}&set_id={$_REQUEST['set_id']}\">{$lang['strlock']} |\n"; + echo "href}&set_id={$_REQUEST['set_id']}\">{$lang['strunlock']} |\n"; + echo "href}&set_id={$_REQUEST['set_id']}\">{$lang['strmerge']} |\n"; + echo "href}&set_id={$_REQUEST['set_id']}\">{$lang['strmove']} |\n"; + echo "href}&set_id={$_REQUEST['set_id']}\">{$lang['strexecute']}

\n"; + } + + /** + * Displays a screen where they can enter a new set + */ + function doCreateReplicationSet($confirm, $msg = '') { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['setid'])) $_POST['setid'] = ''; + if (!isset($_POST['setcomment'])) $_POST['setcomment'] = ''; + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['strcreaterepset']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + + echo "\t\n"; + echo "
{$lang['strid']}
{$lang['strcomment']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + $status = $slony->createReplicationSet($_POST['setid'], $_POST['setcomment']); + if ($status == 0) + doReplicationSets($lang['strrepsetcreated']); + else + doCreateReplicationSet(true, $lang['strrepsetcreatedbad']); + } + } + + /** + * Show confirmation of drop and perform actual drop of a set + */ + function doDropReplicationSet($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strdrop']); + + echo "

", sprintf($lang['strconfdroprepset'], $misc->printVal($_REQUEST['set_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->dropReplicationSet($_REQUEST['set_id']); + if ($status == 0) + doReplicationSet($lang['strrepsetdropped']); + else + doReplicationSet($lang['strrepsetdroppedbad']); + } + } + + /** + * Show confirmation of lock and perform actual lock of a set + */ + function doLockReplicationSet($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strlock']); + + echo "

", sprintf($lang['strconflockrepset'], $misc->printVal($_REQUEST['set_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->lockReplicationSet($_REQUEST['set_id'], true); + if ($status == 0) + doReplicationSet($lang['strrepsetlocked']); + else + doReplicationSet($lang['strrepsetlockedbad']); + } + } + + /** + * Show confirmation of unlock and perform actual unlock of a set + */ + function doUnlockReplicationSet($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strunlock']); + + echo "

", sprintf($lang['strconfunlockrepset'], $misc->printVal($_REQUEST['set_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->lockReplicationSet($_REQUEST['set_id'], false); + if ($status == 0) + doReplicationSets($lang['strrepsetunlocked']); + else + doReplicationSets($lang['strrepsetunlockedbad']); + } + } + + /** + * Displays a screen where they can merge one set into another + */ + function doMergeReplicationSet($confirm, $msg = '') { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['target'])) $_POST['target'] = ''; + + $sets = $slony->getReplicationSets(); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['strmerge']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\n"; + echo "
{$lang['strmergeinto']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + $status = $slony->mergeReplicationSet($_POST['set_id'], $_POST['target']); + if ($status == 0) + doReplicationSet($lang['strrepsetmerged']); + else + doMergeReplicationSet(true, $lang['strrepsetmergedbad']); + } + } + + /** + * Displays a screen where they can move one set into another + */ + function doMoveReplicationSet($confirm, $msg = '') { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['new_origin'])) $_POST['new_origin'] = ''; + + $nodes = $slony->getNodes(); + $set = $slony->getReplicationSet($_REQUEST['set_id']); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['strmove']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\n"; + echo "
{$lang['strneworigin']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + $status = $slony->moveReplicationSet($_POST['set_id'], $_POST['new_origin']); + if ($status == 0) + doReplicationSet($lang['strrepsetmoved']); + else + doMoveReplicationSet(true, $lang['strrepsetmovedbad']); + } + } + + /** + * Displays a screen where they can enter a DDL script to + * be executed on all or a particular node, for this set. + */ + function doExecuteReplicationSet($confirm, $msg = '') { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + if (!isset($_POST['script'])) $_POST['script'] = ''; + + $nodes = $slony->getNodes(); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['strexecute']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + /* Slony 1.1 only + echo "\t\n\t\t\n"; + echo "\n"; + */ + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "
{$lang['stronlyonnode']}
{$lang['strddlscript']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + } + else { + if (trim($_POST['script']) == '') { + doExecuteReplicationSet(true, $lang['strscriptneedsbody']); + return; + } + + $status = $slony->executeReplicationSet($_POST['set_id'], $_POST['script']); + if ($status == 0) + doReplicationSet($lang['strscriptexecuted']); + else + doExecuteReplicationSet(true, $lang['strscriptexecutedbad']); + } + } + + // TABLES + + /** + * List all the tables in a replication set + */ + function doTables($msg = '') { + global $PHP_SELF, $data, $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $tables = $slony->getTables($_REQUEST['set_id']); + + $columns = array( + 'table' => array( + 'title' => $lang['strtable'], + 'field' => 'qualname', + ), + 'owner' => array( + 'title' => $lang['strowner'], + 'field' => 'relowner', + ), + 'tablespace' => array( + 'title' => $lang['strtablespace'], + 'field' => 'tablespace' + ), + 'tuples' => array( + 'title' => $lang['strestimatedrowcount'], + 'field' => 'reltuples', + 'type' => 'numeric' + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'relcomment', + ), + ); + + $actions = array( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "redirect.php?subject=table&{$misc->href}&", + 'vars' => array('table' => 'relname', 'schema' => 'nspname'), + ), + 'remove' => array( + 'title' => $lang['strremove'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_table&set_id={$_REQUEST['set_id']}&", + 'vars' => array('tab_id' => 'tab_id', 'qualname' => 'qualname'), + ), + 'move' => array( + 'title' => $lang['strmove'], + 'url' => "plugin_slony.php?{$misc->href}&action=move_table&set_id={$_REQUEST['set_id']}&stage=1&", + 'vars' => array('tab_id' => 'tab_id'), + ) + ); + + if (!$data->hasTablespaces()) unset($columns['tablespace']); + + $misc->printTable($tables, $columns, $actions, $lang['strnotables']); + + echo "

href}\">{$lang['straddtable']}

\n"; + } + + /** + * Displays a screen where they can add a table to a + * replication set. + */ + function doAddTable($stage, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + switch ($stage) { + case 1: + if (!isset($_POST['tab_id'])) $_POST['tab_id'] = ''; + if (!isset($_POST['comment'])) $_POST['comment'] = ''; + + $tables = &$data->getTables(true); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['straddtable']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + + echo "\t\n"; + echo "
{$lang['strtable']}
{$lang['strid']}
{$lang['strcomment']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + break; + case 2: + // Unserialize table and fetch. This is a bit messy + // because the table could be in another schema. + $_REQUEST['target'] = unserialize($_REQUEST['target']); + $data->setSchema($_REQUEST['target']['schemaname']); + // Get indexes + $indexes = &$data->getIndexes($_REQUEST['target']['tablename'], true); + if ($indexes->recordCount() == 0) { + doAddTable(1, $lang['strtableneedsuniquekey']); + return; + } + + // Get triggers + $triggers = &$data->getTriggers($_REQUEST['target']['tablename']); + + // If only one index and no triggers then jump to next step + if ($indexes->recordCount() == 1 && $triggers->recordCount() == 0) { + $_REQUEST['idxname'] = $indexes->f['indname']; + $_REQUEST['nspname'] = $_REQUEST['target']['schemaname']; + $_REQUEST['relname'] = $_REQUEST['target']['tablename']; + $_REQUEST['target'] = serialize($_REQUEST['target']); + doAddTable(3); + return; + } + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['straddtable']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + if ($indexes->recordCount() > 1) { + echo "\t\n\t\t\n"; + echo "\n"; + } + else { + echo "f['indname']), "\" />\n"; + } + if ($triggers->recordCount() > 0) { + echo "\t\n\t\t\n"; + echo "\n"; + } + echo "
{$lang['strindex']}
{$lang['strtriggers']}

{$lang['strtabletriggerstoretain']}

\n"; + while (!$triggers->EOF) { + echo "f['tgname']), "]\">"; + echo htmlspecialchars($triggers->f['tgname']), "
\n"; + $triggers->moveNext(); + } + echo "
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + break; + case 3: + if (!isset($_REQUEST['storedtriggers'])) $_REQUEST['storedtriggers'] = array(); + $status = $slony->addTable($_REQUEST['set_id'], $_REQUEST['tab_id'], $_REQUEST['nspname'], $_REQUEST['relname'], + $_REQUEST['idxname'], $_REQUEST['comment'], array_keys($_REQUEST['storedtriggers'])); + if ($status == 0) + doTables($lang['strtableaddedtorepset']); + else + doAddTable(2, $lang['strtableaddedtorepsetbad']); + break; + } + } + + /** + * Displays a screen where they can move a table to a + * replication set. + */ + function doMoveTable($stage, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + switch ($stage) { + case 1: + if (!isset($_POST['new_set_id'])) $_POST['new_set_id'] = ''; + + $sets = &$slony->getReplicationSets(); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['strmove']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\n"; + echo "
{$lang['strnewrepset']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + break; + case 2: + $status = $slony->moveTable($_REQUEST['tab_id'], $_REQUEST['new_set_id']); + if ($status == 0) + doTables('Table moved to replication set.'); + else + doMoveTable(1, 'Failed moving table to replication set.'); + break; + } + } + + /** + * Show confirmation of drop and perform actual drop of a table from a + * replication set. + */ + function doRemoveTable($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle('Remove'); + + echo "

", sprintf($lang['strconfremovetablefromrepset'], + $misc->printVal($_REQUEST['qualname']), $misc->printVal($_REQUEST['set_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->removeTable($_REQUEST['tab_id']); + if ($status == 0) + doTables($lang['strtableremovedfromrepset']); + else + doTables($lang['strtableremovedfromrepsetbad']); + } + } + + // SEQUENCES + + /** + * List all the sequences in a replication set + */ + function doSequences($msg = '') { + global $PHP_SELF, $data, $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $sequences = $slony->getSequences($_REQUEST['set_id']); + + $columns = array( + 'sequence' => array( + 'title' => $lang['strsequence'], + 'field' => 'qualname', + ), + 'owner' => array( + 'title' => $lang['strowner'], + 'field' => 'seqowner', + ), + 'actions' => array( + 'title' => $lang['stractions'], + ), + 'comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'seqcomment', + ), + ); + + $actions = array( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "sequences.php?action=properties&{$misc->href}&", + 'vars' => array('sequence' => 'seqname', 'schema' => 'nspname'), + ), + 'remove' => array( + 'title' => $lang['strremove'], + 'url' => "plugin_slony.php?{$misc->href}&action=confirm_drop_sequence&set_id={$_REQUEST['set_id']}&", + 'vars' => array('seq_id' => 'seq_id', 'qualname' => 'qualname'), + ), + 'move' => array( + 'title' => $lang['strmove'], + 'url' => "plugin_slony.php?{$misc->href}&action=move_sequence&set_id={$_REQUEST['set_id']}&stage=1&", + 'vars' => array('seq_id' => 'seq_id'), + ) + ); + + $misc->printTable($sequences, $columns, $actions, $lang['strnosequences']); + + echo "

href}\">{$lang['straddsequence']}

\n"; + } + + /** + * Displays a screen where they can add a sequence to a + * replication set. + */ + function doAddSequence($stage, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + switch ($stage) { + case 1: + if (!isset($_POST['seq_id'])) $_POST['seq_id'] = ''; + if (!isset($_POST['comment'])) $_POST['comment'] = ''; + + $sequences = &$data->getSequences(true); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['straddsequence']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + echo "\t\n\t\t\n"; + echo "\t\t\n\t\n"; + + echo "\t\n"; + echo "
{$lang['strsequence']}
{$lang['strid']}
{$lang['strcomment']}
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + break; + case 2: + // Unserialize sequence. + $_REQUEST['target'] = unserialize($_REQUEST['target']); + + $status = $slony->addSequence($_REQUEST['set_id'], $_REQUEST['seq_id'], + $_REQUEST['target']['schemaname'] . '.' . $_REQUEST['target']['sequencename'], + $_REQUEST['comment']); + if ($status == 0) + doSequences($lang['strsequenceaddedtorepset']); + else + doAddSequence(1, $lang['strsequenceaddedtorepsetbad']); + break; + } + } + + /** + * Show confirmation of drop and perform actual drop of a sequence from a + * replication set. + */ + function doRemoveSequence($confirm) { + global $slony, $misc; + global $PHP_SELF, $lang; + + if ($confirm) { + $misc->printTrail('slony_cluster'); + $misc->printTitle($lang['strremove']); + + echo "

", sprintf($lang['strconfremovesequencefromrepset'], + $misc->printVal($_REQUEST['qualname']), $misc->printVal($_REQUEST['set_id'])), "

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo $misc->form; + echo "\n"; + echo "\n"; + echo "
\n"; + } + else { + $status = $slony->removeSequence($_REQUEST['seq_id']); + if ($status == 0) + doSequences($lang['strsequenceremovedfromrepset']); + else + doSequences($lang['strsequenceremovedfromrepsetbad']); + } + } + + /** + * Displays a screen where they can move a sequence to a + * replication set. + */ + function doMoveSequence($stage, $msg = '') { + global $data, $slony, $misc; + global $PHP_SELF, $lang; + + switch ($stage) { + case 1: + if (!isset($_POST['new_set_id'])) $_POST['new_set_id'] = ''; + + $sets = &$slony->getReplicationSets(); + + $misc->printTrail('slony_sets'); + $misc->printTitle($lang['strmove']); + $misc->printMsg($msg); + + echo "
\n"; + echo $misc->form; + echo "\n"; + echo "\t\n\t\t{$lang['strnewrepset']}\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "
\n"; + break; + case 2: + $status = $slony->moveSequence($_REQUEST['seq_id'], $_REQUEST['new_set_id']); + if ($status == 0) + doSequences('Sequence moved to replication set.'); + else + doMoveSequence(1, 'Failed moving sequence to replication set.'); + break; + } + } + + // SUBSCRIPTIONS + + /** + * List all the subscriptions + */ + function doSubscriptions($msg = '') { + global $slony, $misc; + global $lang; + + $misc->printTrail('database'); + $misc->printMsg($msg); + + $subscriptions = $slony->getSubscribedNodes($_REQUEST['set_id']); + + $columns = array( + 'no_name' => array( + 'title' => $lang['strname'], + 'field' => 'no_comment' + ), +/* 'actions' => array( + 'title' => $lang['stractions'], + ),*/ + 'no_comment' => array( + 'title' => $lang['strcomment'], + 'field' => 'no_comment' + ) + ); + + $actions = array ( + 'properties' => array( + 'title' => $lang['strproperties'], + 'url' => "plugin_slony.php?{$misc->href}&action=subscription_properties&", + 'vars' => array('set_id' => 'sub_set', 'no_id' => 'no_id') + ) + ); + + $misc->printTable($subscriptions, $columns, $actions, $lang['strnosubscriptions']); + } + + /** + * Display the properties of a subscription + */ + function doSubscription($msg = '') { + global $data, $slony, $misc, $PHP_SELF; + global $lang; + + $misc->printTrail('slony_subscription'); + $misc->printTitle($lang['strproperties']); + $misc->printMsg($msg); + + // Fetch the subscription information + $subscription = &$slony->getSubscription($_REQUEST['set_id'], $_REQUEST['no_id']); + + if (is_object($subscription) && $subscription->recordCount() > 0) { + // Show comment if any + if ($subscription->f['receiver'] !== null) + echo "

", $misc->printVal($subscription->f['receiver']), "

\n"; + + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
Provider ID", $misc->printVal($subscription->f['sub_provider']), "
Provider Name", $misc->printVal($subscription->f['provider']), "
Receiver ID", $misc->printVal($subscription->f['sub_receiver']), "
Receiver Name", $misc->printVal($subscription->f['receiver']), "
{$lang['stractive']}", ($data->phpBool($subscription->f['sub_active'])) ? $lang['stryes'] : $lang['strno'], "
May Forward", ($data->phpBool($subscription->f['sub_forward'])) ? $lang['stryes'] : $lang['strno'], "
\n"; + } + else echo "

{$lang['strnodata']}

\n"; + } + + // Tree actions + if ($action == 'tree') doTree('clusters'); + elseif ($action == 'clusters_top') doTree('clusters_top'); + elseif ($action == 'nodes') doTree('nodes'); + elseif ($action == 'nodes_top') doTree('nodes_top'); + elseif ($action == 'paths') doTree('paths'); + elseif ($action == 'listens') doTree('listens'); + elseif ($action == 'sets') doTree('sets'); + elseif ($action == 'sets_top') doTree('sets_top'); + elseif ($action == 'subscriptions') doTree('subscriptions'); + elseif ($action == 'sequences') doTree('sequences'); + elseif ($action == 'tables') doTree('tables'); + + $misc->printHeader('Slony'); + $misc->printBody(); + + switch ($action) { + case 'save_create_cluster': + if (isset($_POST['cancel'])) doClusters(); + else doCreateCluster(false); + break; + case 'create_cluster': + doCreateCluster(true); + break; + case 'drop_cluster': + if (isset($_POST['cancel'])) doClusters(); + else doDropCluster(false); + break; + case 'confirm_drop_cluster': + doDropCluster(true); + break; + case 'nodes_properties': + doNodes(); + break; + case 'node_properties': + doNode(); + break; + case 'save_create_node': + if (isset($_POST['cancel'])) doNodes(); + else doCreateNode(false); + break; + case 'create_node': + doCreateNode(true); + break; + case 'drop_node': + if (isset($_POST['cancel'])) doNodes(); + else doDropNode(false); + break; + case 'confirm_drop_node': + doDropNode(true); + break; + case 'failover_node': + if (isset($_POST['cancel'])) doNodes(); + else doFailoverNode(false); + break; + case 'confirm_failover_node': + doFailoverNode(true); + break; + case 'paths_properties': + doPaths(); + break; + case 'path_properties': + doPath(); + break; + case 'save_create_path': + if (isset($_POST['cancel'])) doPaths(); + else doCreatePath(false); + break; + case 'create_path': + doCreatePath(true); + break; + case 'drop_path': + if (isset($_POST['cancel'])) doPaths(); + else doDropPath(false); + break; + case 'confirm_drop_path': + doDropPath(true); + break; + case 'listens_properties': + doListens(); + break; + case 'listen_properties': + doListen(); + break; + case 'save_create_listen': + if (isset($_POST['cancel'])) doListens(); + else doCreateListen(false); + break; + case 'create_listen': + doCreateListen(true); + break; + case 'drop_listen': + if (isset($_POST['cancel'])) doListens(); + else doDropListen(false); + break; + case 'confirm_drop_listen': + doDropListen(true); + break; + case 'sets_properties': + doReplicationSets(); + break; + case 'set_properties': + doReplicationSet(); + break; + case 'save_create_set': + if (isset($_POST['cancel'])) doReplicationSets(); + else doCreateReplicationSet(false); + break; + case 'create_set': + doCreateReplicationSet(true); + break; + case 'drop_set': + if (isset($_POST['cancel'])) doReplicationSets(); + else doDropReplicationSet(false); + break; + case 'confirm_drop_set': + doDropReplicationSet(true); + break; + case 'lock_set': + if (isset($_POST['cancel'])) doReplicationSets(); + else doLockReplicationSet(false); + break; + case 'confirm_lock_set': + doLockReplicationSet(true); + break; + case 'unlock_set': + if (isset($_POST['cancel'])) doReplicationSets(); + else doUnlockReplicationSet(false); + break; + case 'confirm_unlock_set': + doUnlockReplicationSet(true); + break; + case 'save_merge_set': + if (isset($_POST['cancel'])) doReplicationSet(); + else doMergeReplicationSet(false); + break; + case 'merge_set': + doMergeReplicationSet(true); + break; + case 'save_move_set': + if (isset($_POST['cancel'])) doReplicationSet(); + else doMoveReplicationSet(false); + break; + case 'move_set': + doMoveReplicationSet(true); + break; + case 'save_execute_set': + if (isset($_POST['cancel'])) doReplicationSet(); + else doExecuteReplicationSet(false); + break; + case 'execute_set': + doExecuteReplicationSet(true); + break; + case 'tables_properties': + doTables(); + break; + case 'add_table': + if (isset($_REQUEST['cancel'])) doTables(); + else doAddTable($_REQUEST['stage']); + break; + case 'drop_table': + if (isset($_POST['cancel'])) doTables(); + else doRemoveTable(false); + break; + case 'confirm_drop_table': + doRemoveTable(true); + break; + case 'move_table': + if (isset($_REQUEST['cancel'])) doTables(); + else doMoveTable($_REQUEST['stage']); + break; + case 'sequences_properties': + doSequences(); + break; + case 'add_sequence': + if (isset($_REQUEST['cancel'])) doSequences(); + else doAddSequence($_REQUEST['stage']); + break; + case 'drop_sequence': + if (isset($_POST['cancel'])) doSequences(); + else doRemoveSequence(false); + break; + case 'confirm_drop_sequence': + doRemoveSequence(true); + break; + case 'move_sequence': + if (isset($_REQUEST['cancel'])) doSequences(); + else doMoveSequence($_REQUEST['stage']); + break; + case 'subscriptions_properties': + doSubscriptions(); + break; + case 'subscription_properties': + doSubscription(); + break; + case 'clusters_properties': + doClusters(); + break; + case 'cluster_properties': + doCluster(); + break; + default: + // Shouldn't happen + } + + $misc->printFooter(); + +?> diff --git a/sequences.php b/sequences.php index b5b27d1b..aa40dfa4 100644 --- a/sequences.php +++ b/sequences.php @@ -3,7 +3,7 @@ /** * Manage sequences in a database * - * $Id: sequences.php,v 1.28 2005/05/02 15:47:24 chriskl Exp $ + * $Id: sequences.php,v 1.29 2005/06/16 14:40:11 chriskl Exp $ */ // Include application functions @@ -145,7 +145,7 @@ echo "

href}&sequence=", urlencode($sequence->f['seqname']), "\">{$lang['strreset']} |\n"; echo "href}\">{$lang['strshowallsequences']}

\n"; } - else echo "

{$lang['strnodata']}.

\n"; + else echo "

{$lang['strnodata']}

\n"; } /** diff --git a/sql.php b/sql.php index a144582b..54df2429 100644 --- a/sql.php +++ b/sql.php @@ -6,7 +6,7 @@ * how many SQL statements have been strung together with semi-colons * @param $query The SQL query string to execute * - * $Id: sql.php,v 1.31 2005/06/01 10:38:14 soranzo Exp $ + * $Id: sql.php,v 1.32 2005/06/16 14:40:11 chriskl Exp $ */ // Prevent timeouts on large exports (non-safe mode only) @@ -169,9 +169,9 @@ } echo "

{$lang['strsqlexecuted']}

\n"; - - echo "

href}", - "&action=sql&query=", urlencode($_POST['query']), "\">{$lang['streditsql']}"; + + echo "

{$lang['streditsql']}"; if ($conf['show_reports'] && isset($rs) && is_object($rs) && $rs->recordCount() > 0) { echo " | href}&action=create&report_sql=", urlencode($_POST['query']), "\">{$lang['strcreatereport']}"; diff --git a/sql/plugins/slony1_base.sql b/sql/plugins/slony1_base.sql new file mode 100755 index 00000000..ce35e49f --- /dev/null +++ b/sql/plugins/slony1_base.sql @@ -0,0 +1,387 @@ +-- ---------------------------------------------------------------------- +-- slony1_base.sql +-- +-- Declaration of the basic replication schema. +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: slony1_base.sql,v 1.2 2005/06/16 14:40:14 chriskl Exp $ +-- ---------------------------------------------------------------------- + + +-- ********************************************************************** +-- * Tables +-- ********************************************************************** + + +-- ---------------------------------------------------------------------- +-- TABLE sl_node +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_node ( + no_id int4, + no_active bool, + no_comment text, + + CONSTRAINT "sl_node-pkey" + PRIMARY KEY (no_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_set +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_set ( + set_id int4, + set_origin int4, + set_locked @NAMESPACE@.xxid, + set_comment text, + + CONSTRAINT "sl_set-pkey" + PRIMARY KEY (set_id), + CONSTRAINT "set_origin-no_id-ref" + FOREIGN KEY (set_origin) + REFERENCES @NAMESPACE@.sl_node (no_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_setsync +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_setsync ( + ssy_setid int4, + ssy_origin int4, + ssy_seqno int8, + ssy_minxid @NAMESPACE@.xxid, + ssy_maxxid @NAMESPACE@.xxid, + ssy_xip text, + ssy_action_list text, + + CONSTRAINT "sl_setsync-pkey" + PRIMARY KEY (ssy_setid), + CONSTRAINT "ssy_setid-set_id-ref" + FOREIGN KEY (ssy_setid) + REFERENCES @NAMESPACE@.sl_set (set_id), + CONSTRAINT "ssy_origin-no_id-ref" + FOREIGN KEY (ssy_origin) + REFERENCES @NAMESPACE@.sl_node (no_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_table +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_table ( + tab_id int4, + tab_reloid oid UNIQUE NOT NULL, + tab_set int4, + tab_idxname name NOT NULL, + tab_altered boolean NOT NULL, + tab_comment text, + + CONSTRAINT "sl_table-pkey" + PRIMARY KEY (tab_id), + CONSTRAINT "tab_set-set_id-ref" + FOREIGN KEY (tab_set) + REFERENCES @NAMESPACE@.sl_set (set_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_trigger +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_trigger ( + trig_tabid int4, + trig_tgname name, + + CONSTRAINT "sl_trigger-pkey" + PRIMARY KEY (trig_tabid, trig_tgname), + CONSTRAINT "trig_tabid-tab_id-ref" + FOREIGN KEY (trig_tabid) + REFERENCES @NAMESPACE@.sl_table (tab_id) + ON DELETE CASCADE +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_sequence +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_sequence ( + seq_id int4, + seq_reloid oid UNIQUE NOT NULL, + seq_set int4, + seq_comment text, + + CONSTRAINT "sl_sequence-pkey" + PRIMARY KEY (seq_id), + CONSTRAINT "seq_set-set_id-ref" + FOREIGN KEY (seq_set) + REFERENCES @NAMESPACE@.sl_set (set_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_path +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_path ( + pa_server int4, + pa_client int4, + pa_conninfo text NOT NULL, + pa_connretry int4, + + CONSTRAINT "sl_path-pkey" + PRIMARY KEY (pa_server, pa_client), + CONSTRAINT "pa_server-no_id-ref" + FOREIGN KEY (pa_server) + REFERENCES @NAMESPACE@.sl_node (no_id), + CONSTRAINT "pa_client-no_id-ref" + FOREIGN KEY (pa_client) + REFERENCES @NAMESPACE@.sl_node (no_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_listen +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_listen ( + li_origin int4, + li_provider int4, + li_receiver int4, + + CONSTRAINT "sl_listen-pkey" + PRIMARY KEY (li_origin, li_provider, li_receiver), + CONSTRAINT "li_origin-no_id-ref" + FOREIGN KEY (li_origin) + REFERENCES @NAMESPACE@.sl_node (no_id), + CONSTRAINT "sl_listen-sl_path-ref" + FOREIGN KEY (li_provider, li_receiver) + REFERENCES @NAMESPACE@.sl_path (pa_server, pa_client) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_subscribe +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_subscribe ( + sub_set int4, + sub_provider int4, + sub_receiver int4, + sub_forward bool, + sub_active bool, + + CONSTRAINT "sl_subscribe-pkey" + PRIMARY KEY (sub_receiver, sub_set), + CONSTRAINT "sl_subscribe-sl_path-ref" + FOREIGN KEY (sub_provider, sub_receiver) + REFERENCES @NAMESPACE@.sl_path (pa_server, pa_client), + CONSTRAINT "sub_set-set_id-ref" + FOREIGN KEY (sub_set) + REFERENCES @NAMESPACE@.sl_set (set_id) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_event +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_event ( + ev_origin int4, + ev_seqno int8, + ev_timestamp timestamp, + ev_minxid @NAMESPACE@.xxid, + ev_maxxid @NAMESPACE@.xxid, + ev_xip text, + ev_type text, + ev_data1 text, + ev_data2 text, + ev_data3 text, + ev_data4 text, + ev_data5 text, + ev_data6 text, + ev_data7 text, + ev_data8 text, + + CONSTRAINT "sl_event-pkey" + PRIMARY KEY (ev_origin, ev_seqno) +); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_confirm +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_confirm ( + con_origin int4, + con_received int4, + con_seqno int8, + con_timestamp timestamp DEFAULT timeofday()::timestamp +); +create index sl_confirm_idx1 on @NAMESPACE@.sl_confirm + (con_origin, con_received, con_seqno); +create index sl_confirm_idx2 on @NAMESPACE@.sl_confirm + (con_received, con_seqno); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_seqlog +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_seqlog ( + seql_seqid int4, + seql_origin int4, + seql_ev_seqno int8, + seql_last_value int8 +); +create index sl_seqlog_idx on @NAMESPACE@.sl_seqlog + (seql_origin, seql_ev_seqno, seql_seqid); + + +-- ---------------------------------------------------------------------- +-- FUNCTION sequenceLastValue (seqname) +-- +-- Support function used in sl_seqlastvalue view +-- ---------------------------------------------------------------------- +create function @NAMESPACE@.sequenceLastValue(text) returns int8 +as ' +declare + p_seqname alias for $1; + v_seq_row record; +begin + for v_seq_row in execute ''select last_value from '' || p_seqname + loop + return v_seq_row.last_value; + end loop; + + -- not reached +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- VIEW sl_seqlastvalue +-- ---------------------------------------------------------------------- +create view @NAMESPACE@.sl_seqlastvalue as + select SQ.seq_id, SQ.seq_set, SQ.seq_reloid, + S.set_origin as seq_origin, + @NAMESPACE@.sequenceLastValue( + "pg_catalog".quote_ident(PGN.nspname) || '.' || + "pg_catalog".quote_ident(PGC.relname)) as seq_last_value + from @NAMESPACE@.sl_sequence SQ, @NAMESPACE@.sl_set S, + "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN + where S.set_id = SQ.seq_set + and PGC.oid = SQ.seq_reloid and PGN.oid = PGC.relnamespace; + + +-- ---------------------------------------------------------------------- +-- TABLE sl_log_1 +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_log_1 ( + log_origin int4, + log_xid @NAMESPACE@.xxid, + log_tableid int4, + log_actionseq int8, + log_cmdtype char, + log_cmddata text +); +create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1 + (log_origin, log_xid @NAMESPACE@.xxid_ops, log_actionseq); + + +-- ---------------------------------------------------------------------- +-- TABLE sl_log_2 +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_log_2 ( + log_origin int4, + log_xid @NAMESPACE@.xxid, + log_tableid int4, + log_actionseq int8, + log_cmdtype char, + log_cmddata text +); +create index sl_log_2_idx1 on @NAMESPACE@.sl_log_2 + (log_origin, log_xid @NAMESPACE@.xxid_ops, log_actionseq); + + +-- ********************************************************************** +-- * Sequences +-- ********************************************************************** + + +-- ---------------------------------------------------------------------- +-- SEQUENCE sl_local_node_id +-- +-- The local node ID is initialized to -1, meaning that this node +-- is not initialized yet. +-- ---------------------------------------------------------------------- +create sequence @NAMESPACE@.sl_local_node_id + MINVALUE -1; +SELECT setval('@NAMESPACE@.sl_local_node_id', -1); + + +-- ---------------------------------------------------------------------- +-- SEQUENCE sl_event_seq +-- +-- The sequence for numbering events originating from this node. +-- ---------------------------------------------------------------------- +create sequence @NAMESPACE@.sl_event_seq; + + +-- ---------------------------------------------------------------------- +-- SEQUENCE sl_action_seq +-- +-- The sequence to number statements in the transaction logs, so that +-- the replication engines can figure out the "agreeable" order of +-- statements. +-- ---------------------------------------------------------------------- +create sequence @NAMESPACE@.sl_action_seq; + + +-- ---------------------------------------------------------------------- +-- SEQUENCE sl_rowid_seq +-- +-- Application tables that do not have a natural primary key must +-- be modified and an int8 column added that serves as a rowid for us. +-- The values are assigned with a default from this sequence. +-- ---------------------------------------------------------------------- +create sequence @NAMESPACE@.sl_rowid_seq; +grant select, update on @NAMESPACE@.sl_rowid_seq to public; + + +-- ---------------------------------------------------------------------- +-- SEQUENCE sl_log_status +-- +-- Bit 0x01 determines the currently active log table +-- Bit 0x02 tells if the engine needs to read both logs +-- after switching until the old log is clean and truncated. +-- +-- Possible values: +-- 0 sl_log_1 active, sl_log_2 clean +-- 1 sl_log_2 active, sl_log_1 clean +-- 2 sl_log_1 active, sl_log_2 unknown - cleanup +-- 3 sl_log_2 active, sl_log_1 unknown - cleanup +-- ---------------------------------------------------------------------- +create sequence @NAMESPACE@.sl_log_status + MINVALUE 0 MAXVALUE 3; +SELECT setval('@NAMESPACE@.sl_log_status', 0); + + +-- ********************************************************************** +-- * Misc +-- ********************************************************************** + + +-- ---------------------------------------------------------------------- +-- TABLE sl_config_lock +-- +-- This table exists solely to prevent overlapping execution of +-- configuration change procedures and the resulting possible +-- deadlocks. +-- ---------------------------------------------------------------------- +create table @NAMESPACE@.sl_config_lock ( + dummy integer +); + + +-- ---------------------------------------------------------------------- +-- Last but not least grant USAGE to the replication schema objects. +-- ---------------------------------------------------------------------- +grant usage on schema @NAMESPACE@ to public; + + diff --git a/sql/plugins/slony1_base.v73.sql b/sql/plugins/slony1_base.v73.sql new file mode 100755 index 00000000..26a1cf18 --- /dev/null +++ b/sql/plugins/slony1_base.v73.sql @@ -0,0 +1,12 @@ +-- ---------------------------------------------------------------------- +-- slony1_base.v73.sql +-- +-- Version 7.3 specific parts of the basic replication schema. +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: slony1_base.v73.sql,v 1.2 2005/06/16 14:40:14 chriskl Exp $ +-- ---------------------------------------------------------------------- + + diff --git a/sql/plugins/slony1_base.v74.sql b/sql/plugins/slony1_base.v74.sql new file mode 100755 index 00000000..fee05c21 --- /dev/null +++ b/sql/plugins/slony1_base.v74.sql @@ -0,0 +1,12 @@ +-- ---------------------------------------------------------------------- +-- slony1_base.v73.sql +-- +-- Version 7.3 specific parts of the basic replication schema. +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: slony1_base.v74.sql,v 1.2 2005/06/16 14:40:14 chriskl Exp $ +-- ---------------------------------------------------------------------- + + diff --git a/sql/plugins/slony1_funcs.sql b/sql/plugins/slony1_funcs.sql new file mode 100755 index 00000000..00084665 --- /dev/null +++ b/sql/plugins/slony1_funcs.sql @@ -0,0 +1,4271 @@ +-- ---------------------------------------------------------------------- +-- slony1_funcs.sql +-- +-- Declaration of replication support functions. +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: slony1_funcs.sql,v 1.2 2005/06/16 14:40:14 chriskl Exp $ +-- ---------------------------------------------------------------------- + + +-- ********************************************************************** +-- * C functions in src/backend/slony1_base.c +-- ********************************************************************** + + +-- ---------------------------------------------------------------------- +-- FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) +-- +-- Create an sl_event entry +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.createEvent (name, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text, text, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text, text, text, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + +create or replace function @NAMESPACE@.createEvent (name, text, text, text, text, text, text, text, text, text) + returns bigint + as '$libdir/slony1_funcs', '_Slony_I_createEvent' + language C + called on null input; + + +-- ---------------------------------------------------------------------- +-- FUNCTION denyAccess (cluster_name) +-- +-- Trigger function to prevent modifications to a table on +-- a subscriber. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.denyAccess () + returns trigger + as '$libdir/slony1_funcs', '_Slony_I_denyAccess' + language C + security definer; +grant execute on function @NAMESPACE@.denyAccess () to public; + + +-- ---------------------------------------------------------------------- +-- FUNCTION lockedSet (cluster_name) +-- +-- Trigger function to prevent modifications to a table before +-- and after a moveSet(). +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.lockedSet () + returns trigger + as '$libdir/slony1_funcs', '_Slony_I_lockedSet' + language C; + + +-- ---------------------------------------------------------------------- +-- FUNCTION getLocalNodeId (name) +-- +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.getLocalNodeId (name) returns int4 + as '$libdir/slony1_funcs', '_Slony_I_getLocalNodeId' + language C + security definer; +grant execute on function @NAMESPACE@.getLocalNodeId (name) to public; + + +-- ---------------------------------------------------------------------- +-- FUNCTION getModuleVersion () +-- +-- Returns the compiled in version number of the Slony-I shared +-- object. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.getModuleVersion () returns text + as '$libdir/slony1_funcs', '_Slony_I_getModuleVersion' + language C + security definer; +grant execute on function @NAMESPACE@.getModuleVersion () to public; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setSessionRole (name, role) +-- +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setSessionRole (name, text) returns text + as '$libdir/slony1_funcs', '_Slony_I_setSessionRole' + language C + security definer; +grant execute on function @NAMESPACE@.setSessionRole (name, text) to public; + + +-- ---------------------------------------------------------------------- +-- FUNCTION getSessionRole (name, role) +-- +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.getSessionRole (name) returns text + as '$libdir/slony1_funcs', '_Slony_I_getSessionRole' + language C + security definer; +grant execute on function @NAMESPACE@.getSessionRole (name) to public; + + +-- ---------------------------------------------------------------------- +-- FUNCTION logTrigger () +-- +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.logTrigger () returns trigger + as '$libdir/slony1_funcs', '_Slony_I_logTrigger' + language C + security definer; +grant execute on function @NAMESPACE@.logTrigger () to public; + + +-- ---------------------------------------------------------------------- +-- FUNCTION terminateNodeConnections (name) +-- +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.terminateNodeConnections (name) returns int4 + as '$libdir/slony1_funcs', '_Slony_I_terminateNodeConnections' + language C; + + +-- ---------------------------------------------------------------------- +-- FUNCTION cleanupListener () +-- +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.cleanupListener () returns int4 + as '$libdir/slony1_funcs', '_Slony_I_cleanupListener' + language C; + + +-- ********************************************************************** +-- * PL/pgSQL functions for administrative tasks +-- ********************************************************************** + + +-- ---------------------------------------------------------------------- +-- FUNCTION slonyVersionMajor() +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.slonyVersionMajor() +returns int4 +as ' +begin + return 1; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION slonyVersionMinor() +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.slonyVersionMinor() +returns int4 +as ' +begin + return 0; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION slonyVersionPatchlevel() +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.slonyVersionPatchlevel() +returns int4 +as ' +begin + return 5; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION slonyVersion() +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.slonyVersion() +returns text +as ' +begin + return '''' || @NAMESPACE@.slonyVersionMajor() || ''.'' + || @NAMESPACE@.slonyVersionMinor() || ''.'' + || @NAMESPACE@.slonyVersionPatchlevel(); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION initializeLocalNode (no_id, no_comment) +-- +-- Initializes a new node. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.initializeLocalNode (int4, text) +returns int4 +as ' +declare + p_local_node_id alias for $1; + p_comment alias for $2; + v_old_node_id int4; + v_first_log_no int4; + v_event_seq int8; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Make sure this node is uninitialized or got reset + -- ---- + select last_value::int4 into v_old_node_id from @NAMESPACE@.sl_local_node_id; + if v_old_node_id != -1 then + raise exception ''Slony-I: This node is already initialized''; + end if; + + -- ---- + -- Set sl_local_node_id to the requested value and add our + -- own system to sl_node. + -- ---- + perform setval(''@NAMESPACE@.sl_local_node_id'', p_local_node_id); + perform setval(''@NAMESPACE@.sl_rowid_seq'', + p_local_node_id::int8 * ''1000000000000000''::int8); + perform @NAMESPACE@.storeNode_int (p_local_node_id, p_comment); + + return p_local_node_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeNode (no_id, no_comment) +-- +-- Generate the STORE_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeNode (int4, text) +returns bigint +as ' +declare + p_no_id alias for $1; + p_no_comment alias for $2; +begin + perform @NAMESPACE@.storeNode_int (p_no_id, p_no_comment); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''STORE_NODE'', + p_no_id, p_no_comment); +end; +' language plpgsql + called on null input; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeNode_int (no_id, no_comment) +-- +-- Process the STORE_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeNode_int (int4, text) +returns int4 +as ' +declare + p_no_id alias for $1; + p_no_comment alias for $2; + v_old_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check if the node exists + -- ---- + select * into v_old_row + from @NAMESPACE@.sl_node + where no_id = p_no_id + for update; + if found then + -- ---- + -- Node exists, update the existing row. + -- ---- + update @NAMESPACE@.sl_node + set no_comment = p_no_comment + where no_id = p_no_id; + else + -- ---- + -- New node, insert the sl_node row + -- ---- + insert into @NAMESPACE@.sl_node + (no_id, no_active, no_comment) values + (p_no_id, ''f'', p_no_comment); + end if; + + return p_no_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION enableNode (no_id) +-- +-- Generate the ENABLE_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.enableNode (int4) +returns bigint +as ' +declare + p_no_id alias for $1; + v_local_node_id int4; + v_node_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that we are the node to activate and that we are + -- currently disabled. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select * into v_node_row + from @NAMESPACE@.sl_node + where no_id = p_no_id + for update; + if not found then + raise exception ''Slony-I: node % not found'', p_no_id; + end if; + if v_node_row.no_active then + raise exception ''Slony-I: node % is already active'', p_no_id; + end if; + + -- ---- + -- Activate this node and generate the ENABLE_NODE event + -- ---- + perform @NAMESPACE@.enableNode_int (p_no_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''ENABLE_NODE'', + p_no_id); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION enableNode_int (no_id) +-- +-- Process the ENABLE_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.enableNode_int (int4) +returns int4 +as ' +declare + p_no_id alias for $1; + v_local_node_id int4; + v_node_row record; + v_sub_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that the node is inactive + -- ---- + select * into v_node_row + from @NAMESPACE@.sl_node + where no_id = p_no_id + for update; + if not found then + raise exception ''Slony-I: node % not found'', p_no_id; + end if; + if v_node_row.no_active then + return p_no_id; + end if; + + -- ---- + -- Activate the node and generate sl_confirm status rows for it. + -- ---- + update @NAMESPACE@.sl_node + set no_active = ''t'' + where no_id = p_no_id; + insert into @NAMESPACE@.sl_confirm + (con_origin, con_received, con_seqno) + select no_id, p_no_id, 0 from @NAMESPACE@.sl_node + where no_id != p_no_id + and no_active; + insert into @NAMESPACE@.sl_confirm + (con_origin, con_received, con_seqno) + select p_no_id, no_id, 0 from @NAMESPACE@.sl_node + where no_id != p_no_id + and no_active; + + -- ---- + -- Generate ENABLE_SUBSCRIPTION events for all sets that + -- origin here and are subscribed by the just enabled node. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + for v_sub_row in select SUB.sub_set, SUB.sub_provider from + @NAMESPACE@.sl_set S, + @NAMESPACE@.sl_subscribe SUB + where S.set_origin = v_local_node_id + and S.set_id = SUB.sub_set + and SUB.sub_receiver = p_no_id + for update of S + loop + perform @NAMESPACE@.enableSubscription (v_sub_row.sub_set,, + v_sub_row.sub_provider, p_no_id); + end loop; + + return p_no_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION disableNode (no_id) +-- +-- Generate the DISABLE_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.disableNode (int4) +returns bigint +as ' +declare + p_no_id alias for $1; +begin + -- **** TODO **** + raise exception ''Slony-I: disableNode() not implemented''; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION disableNode_int (no_id) +-- +-- Process the DISABLE_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.disableNode_int (int4) +returns int4 +as ' +declare + p_no_id alias for $1; +begin + -- **** TODO **** + raise exception ''Slony-I: disableNode_int() not implemented''; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropNode (no_id) +-- +-- Generate the DROP_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropNode (int4) +returns bigint +as ' +declare + p_no_id alias for $1; + v_node_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that this got called on a different node + -- ---- + if p_no_id = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: DROP_NODE cannot initiate on the dropped node''; + end if; + + select * into v_node_row from @NAMESPACE@.sl_node + where no_id = p_no_id + for update; + if not found then + raise exception ''Slony-I: unknown node ID %'', p_no_id; + end if; + + -- ---- + -- Make sure we do not break other nodes subscriptions with this + -- ---- + if exists (select true from @NAMESPACE@.sl_subscribe + where sub_provider = p_no_id) + then + raise exception ''Slony-I: Node % is still configured as data provider'', + p_no_id; + end if; + + -- ---- + -- Make sure no set originates there any more + -- ---- + if exists (select true from @NAMESPACE@.sl_set + where set_origin = p_no_id) + then + raise exception ''Slony-I: Node % is still origin of one or more sets'', + p_no_id; + end if; + + -- ---- + -- Call the internal drop functionality and generate the event + -- ---- + perform @NAMESPACE@.dropNode_int(p_no_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''DROP_NODE'', + p_no_id); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropNode_int (no_id) +-- +-- Process the DROP_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropNode_int (int4) +returns int4 +as ' +declare + p_no_id alias for $1; + v_tab_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- If the dropped node is a remote node, clean the configuration + -- from all traces for it. + -- ---- + if p_no_id <> @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + delete from @NAMESPACE@.sl_subscribe + where sub_receiver = p_no_id; + delete from @NAMESPACE@.sl_listen + where li_origin = p_no_id + or li_provider = p_no_id + or li_receiver = p_no_id; + delete from @NAMESPACE@.sl_path + where pa_server = p_no_id + or pa_client = p_no_id; + delete from @NAMESPACE@.sl_confirm + where con_origin = p_no_id + or con_received = p_no_id; + delete from @NAMESPACE@.sl_event + where ev_origin = p_no_id; + delete from @NAMESPACE@.sl_node + where no_id = p_no_id; + + return p_no_id; + end if; + + -- ---- + -- This is us ... deactivate the node for now, the daemon + -- will call uninstallNode() in a separate transaction. + -- ---- + update @NAMESPACE@.sl_node + set no_active = false + where no_id = p_no_id; + + return p_no_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION failedNode (failed_node, backup_node) +-- +-- Initiate a failover. This function must be called on all nodes +-- and then waited for the restart of all node deamons. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.failedNode(int4, int4) +returns int4 +as ' +declare + p_failed_node alias for $1; + p_backup_node alias for $2; + v_row record; + v_row2 record; + v_n int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- All consistency checks first + -- Check that every system that has a path to the failed node + -- also has a path to the backup node. + -- ---- + for v_row in select P.pa_client + from @NAMESPACE@.sl_path P + where P.pa_server = p_failed_node + and P.pa_client <> p_backup_node + and not exists (select true from @NAMESPACE@.sl_path PP + where PP.pa_server = p_backup_node + and PP.pa_client = P.pa_client) + loop + raise exception ''Slony-I: cannot failover - node % has no path to the backup node'', + v_row.pa_client; + end loop; + + -- ---- + -- Check all sets originating on the failed node + -- ---- + for v_row in select set_id + from @NAMESPACE@.sl_set + where set_origin = p_failed_node + loop + -- ---- + -- Check that the backup node is subscribed to all sets + -- that origin on the failed node + -- ---- + select into v_row2 sub_forward, sub_active + from @NAMESPACE@.sl_subscribe + where sub_set = v_row.set_id + and sub_receiver = p_backup_node; + if not found then + raise exception ''Slony-I: cannot failover - node % is not subscribed to set %'', + p_backup_node, v_row.set_id; + end if; + + -- ---- + -- Check that the subscription is active + -- ---- + if not v_row2.sub_active then + raise exception ''Slony-I: cannot failover - subscription for set % is not active'', + v_row.set_id; + end if; + + -- ---- + -- If there are other subscribers, the backup node needs to + -- be a forwarder too. + -- ---- + select into v_n count(*) + from @NAMESPACE@.sl_subscribe + where sub_set = v_row.set_id + and sub_receiver <> p_backup_node; + if v_n > 0 and not v_row2.sub_forward then + raise exception ''Slony-I: cannot failover - node % is not a forwarder of set %'', + p_backup_node, v_row.set_id; + end if; + end loop; + + -- ---- + -- Terminate all connections of the failed node the hard way + -- ---- + perform @NAMESPACE@.terminateNodeConnections( + ''_@CLUSTERNAME@_Node_'' || p_failed_node); + + -- ---- + -- Let every node that listens for something on the failed node + -- listen for that on the backup node instead. + -- ---- + for v_row in select * from @NAMESPACE@.sl_listen + where li_provider = p_failed_node + and li_receiver <> p_backup_node + loop + perform @NAMESPACE@.storeListen_int(v_row.li_origin, + p_backup_node, v_row.li_receiver); + end loop; + + -- ---- + -- Let the backup node listen for all events where the + -- failed node did listen for it. + -- ---- + for v_row in select li_origin, li_provider + from @NAMESPACE@.sl_listen + where li_receiver = p_failed_node + and li_provider <> p_backup_node + loop + perform @NAMESPACE@.storeListen_int(v_row.li_origin, + v_row.li_provider, p_backup_node); + end loop; + + -- ---- + -- Remove all sl_listen entries that receive anything from the + -- failed node. + -- ---- + delete from @NAMESPACE@.sl_listen + where li_provider = p_failed_node + or li_receiver = p_failed_node; + + -- ---- + -- Move the sets + -- ---- + for v_row in select S.set_id, (select count(*) + from @NAMESPACE@.sl_subscribe SUB + where S.set_id = SUB.sub_set + and SUB.sub_receiver <> p_backup_node + and SUB.sub_provider = p_failed_node) + as num_direct_receivers + from @NAMESPACE@.sl_set S + where S.set_origin = p_failed_node + for update + loop + -- ---- + -- If the backup node is the only direct subscriber ... + -- ---- + if v_row.num_direct_receivers = 0 then +raise notice ''failedNode: set % has no other direct receivers - move now'', v_row.set_id; + -- ---- + -- backup_node is the only direct subscriber, move the set + -- right now. On the backup node itself that includes restoring + -- all user mode triggers, removing the protection trigger, + -- adding the log trigger, removing the subscription and the + -- obsolete setsync status. + -- ---- + if p_backup_node = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + for v_row2 in select * from @NAMESPACE@.sl_table + where tab_set = v_row.set_id + loop + perform @NAMESPACE@.alterTableRestore(v_row2.tab_id); + end loop; + end if; + + update @NAMESPACE@.sl_set set set_origin = p_backup_node + where set_id = v_row.set_id; + + if p_backup_node = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + delete from @NAMESPACE@.sl_setsync + where ssy_setid = v_row.set_id; + + for v_row2 in select * from @NAMESPACE@.sl_table + where tab_set = v_row.set_id + loop + perform @NAMESPACE@.alterTableForReplication(v_row2.tab_id); + end loop; + end if; + + delete from @NAMESPACE@.sl_subscribe + where sub_set = v_row.set_id + and sub_receiver = p_backup_node; + else +raise notice ''failedNode: set % has other direct receivers - change providers only'', v_row.set_id; + -- ---- + -- Backup node is not the only direct subscriber. This + -- means that at this moment, we redirect all direct + -- subscribers to receive from the backup node, and the + -- backup node itself to receive from another one. + -- The admin utility will wait for the slon engine to + -- restart and then call failedNode2() on the node with + -- the highest SYNC and redirect this to it on + -- backup node later. + -- ---- + update @NAMESPACE@.sl_subscribe + set sub_provider = (select min(SS.sub_receiver) + from @NAMESPACE@.sl_subscribe SS + where SS.sub_set = v_row.set_id + and SS.sub_provider = p_failed_node + and SS.sub_receiver <> p_backup_node + and SS.sub_forward) + where sub_set = v_row.set_id + and sub_receiver = p_backup_node; + update @NAMESPACE@.sl_subscribe + set sub_provider = p_backup_node + where sub_set = v_row.set_id + and sub_provider = p_failed_node + and sub_receiver <> p_backup_node; + end if; + end loop; + + -- ---- + -- Make sure the node daemon will restart + -- ---- + notify "_@CLUSTERNAME@_Restart"; + + -- ---- + -- That is it - so far. + -- ---- + return p_failed_node; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION failedNode2 (failed_node, backup_node, set_id, ev_seqno, ev_seqfake) +-- +-- On the node that has the highest sequence number of the failed node, +-- fake the FAILED_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.failedNode2 (int4, int4, int4, int8, int8) +returns bigint +as ' +declare + p_failed_node alias for $1; + p_backup_node alias for $2; + p_set_id alias for $3; + p_ev_seqno alias for $4; + p_ev_seqfake alias for $5; + v_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + select * into v_row + from @NAMESPACE@.sl_event + where ev_origin = p_failed_node + and ev_seqno = p_ev_seqno; + if not found then + raise exception ''Slony-I: event %,% not found'', + p_failed_node, p_ev_seqno; + end if; + + insert into @NAMESPACE@.sl_event + (ev_origin, ev_seqno, ev_timestamp, + ev_minxid, ev_maxxid, ev_xip, + ev_type, ev_data1, ev_data2, ev_data3) + values + (p_failed_node, p_ev_seqfake, CURRENT_TIMESTAMP, + v_row.ev_minxid, v_row.ev_maxxid, v_row.ev_xip, + ''FAILOVER_SET'', p_failed_node::text, p_backup_node::text, + p_set_id::text); + insert into @NAMESPACE@.sl_confirm + (con_origin, con_received, con_seqno, con_timestamp) + values + (p_failed_node, @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''), + p_ev_seqfake, CURRENT_TIMESTAMP); + notify "_@CLUSTERNAME@_Event"; + notify "_@CLUSTERNAME@_Confirm"; + notify "_@CLUSTERNAME@_Restart"; + + perform @NAMESPACE@.failoverSet_int(p_failed_node, + p_backup_node, p_set_id); + + return p_ev_seqfake; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION failoverSet_int (failed_node, backup_node, set_id) +-- +-- Finish failover for one set. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.failoverSet_int (int4, int4, int4) +returns int4 +as ' +declare + p_failed_node alias for $1; + p_backup_node alias for $2; + p_set_id alias for $3; + v_row record; + v_last_sync int8; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Change the origin of the set now to the backup node. + -- On the backup node this includes changing all the + -- trigger and protection stuff + -- ---- + if p_backup_node = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + for v_row in select * from @NAMESPACE@.sl_table + where tab_set = p_set_id + loop + perform @NAMESPACE@.alterTableRestore(v_row.tab_id); + end loop; + + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_set_id; + delete from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = p_backup_node; + update @NAMESPACE@.sl_set + set set_origin = p_backup_node + where set_id = p_set_id; + + for v_row in select * from @NAMESPACE@.sl_table + where tab_set = p_set_id + loop + perform @NAMESPACE@.alterTableForReplication(v_row.tab_id); + end loop; + else + delete from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = p_backup_node; + update @NAMESPACE@.sl_set + set set_origin = p_backup_node + where set_id = p_set_id; + end if; + + -- ---- + -- If we are a subscriber of the set ourself, change our + -- setsync status to reflect the new set origin. + -- ---- + if exists (select true from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = @NAMESPACE@.getLocalNodeId( + ''_@CLUSTERNAME@'')) + then + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_set_id; + + select coalesce(max(ev_seqno), 0) into v_last_sync + from @NAMESPACE@.sl_event + where ev_origin = p_backup_node + and ev_type = ''SYNC''; + if v_last_sync > 0 then + insert into @NAMESPACE@.sl_setsync + (ssy_setid, ssy_origin, ssy_seqno, + ssy_minxid, ssy_maxxid, ssy_xip, ssy_action_list) + select p_set_id, p_backup_node, v_last_sync, + ev_minxid, ev_maxxid, ev_xip, NULL + from @NAMESPACE@.sl_event + where ev_origin = p_backup_node + and ev_seqno = v_last_sync; + else + insert into @NAMESPACE@.sl_setsync + (ssy_setid, ssy_origin, ssy_seqno, + ssy_minxid, ssy_maxxid, ssy_xip, ssy_action_list) + values (p_set_id, p_backup_node, ''0'', + ''0'', ''0'', '''', NULL); + end if; + + end if; + + return p_failed_node; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION uninstallNode () +-- +-- Reset the whole database to standalone by removing the whole +-- replication system. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.uninstallNode () +returns int4 +as ' +declare + v_tab_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- This is us ... time for suicide! Restore all tables to + -- their original status. + -- ---- + for v_tab_row in select * from @NAMESPACE@.sl_table loop + perform @NAMESPACE@.alterTableRestore(v_tab_row.tab_id); + perform @NAMESPACE@.tableDropKey(v_tab_row.tab_id); + end loop; + + raise notice ''Slony-I: Please drop schema "_@CLUSTERNAME@"''; + return 0; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storePath (pa_server, pa_client, pa_conninfo, pa_connretry) +-- +-- Generate the STORE_PATH event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storePath (int4, int4, text, int4) +returns bigint +as ' +declare + p_pa_server alias for $1; + p_pa_client alias for $2; + p_pa_conninfo alias for $3; + p_pa_connretry alias for $4; +begin + perform @NAMESPACE@.storePath_int(p_pa_server, p_pa_client, + p_pa_conninfo, p_pa_connretry); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''STORE_PATH'', + p_pa_server, p_pa_client, p_pa_conninfo, p_pa_connretry); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storePath_int (pa_server, pa_client, pa_conninfo, pa_connretry) +-- +-- Process the STORE_PATH event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storePath_int (int4, int4, text, int4) +returns int4 +as ' +declare + p_pa_server alias for $1; + p_pa_client alias for $2; + p_pa_conninfo alias for $3; + p_pa_connretry alias for $4; + v_dummy int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check if the path already exists + -- ---- + select 1 into v_dummy + from @NAMESPACE@.sl_path + where pa_server = p_pa_server + and pa_client = p_pa_client + for update; + if found then + -- ---- + -- Path exists, update pa_conninfo + -- ---- + update @NAMESPACE@.sl_path + set pa_conninfo = p_pa_conninfo, + pa_connretry = p_pa_connretry + where pa_server = p_pa_server + and pa_client = p_pa_client; + else + -- ---- + -- New path + -- + -- In case we receive STORE_PATH events before we know + -- about the nodes involved in this, we generate those nodes + -- as pending. + -- ---- + if not exists (select 1 from @NAMESPACE@.sl_node + where no_id = p_pa_server) then + perform @NAMESPACE@.storeNode_int (p_pa_server, ''''); + end if; + if not exists (select 1 from @NAMESPACE@.sl_node + where no_id = p_pa_client) then + perform @NAMESPACE@.storeNode_int (p_pa_client, ''''); + end if; + insert into @NAMESPACE@.sl_path + (pa_server, pa_client, pa_conninfo, pa_connretry) values + (p_pa_server, p_pa_client, p_pa_conninfo, p_pa_connretry); + end if; + + return 0; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropPath (pa_server, pa_client) +-- +-- Generate the DROP_PATH event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropPath (int4, int4) +returns bigint +as ' +declare + p_pa_server alias for $1; + p_pa_client alias for $2; + v_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- There should be no existing subscriptions. Auto unsubscribing + -- is considered too dangerous. + -- ---- + for v_row in select sub_set, sub_provider, sub_receiver + from @NAMESPACE@.sl_subscribe + where sub_provider = p_pa_server + and sub_receiver = p_pa_client + loop + raise exception + ''Slony-I: Path cannot be dropped, subscription of set % needs it'', + v_row.sub_set; + end loop; + + -- ---- + -- Drop all sl_listen entries that depend on this path + -- ---- + for v_row in select li_origin, li_provider, li_receiver + from @NAMESPACE@.sl_listen + where li_provider = p_pa_server + and li_receiver = p_pa_client + loop + perform @NAMESPACE@.dropListen( + v_row.li_origin, v_row.li_provider, v_row.li_receiver); + end loop; + + -- ---- + -- Now drop the path and create the event + -- ---- + perform @NAMESPACE@.dropPath_int(p_pa_server, p_pa_client); + return @NAMESPACE@.createEvent (''_@CLUSTERNAME@'', ''DROP_PATH'', + p_pa_server, p_pa_client); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropPath_int (pa_server, pa_client) +-- +-- Process the DROP_NODE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropPath_int (int4, int4) +returns int4 +as ' +declare + p_pa_server alias for $1; + p_pa_client alias for $2; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Remove any dangling sl_listen entries with the server + -- as provider and the client as receiver. This must have + -- been cleared out before, but obviously was not. + -- ---- + delete from @NAMESPACE@.sl_listen + where li_provider = p_pa_server + and li_receiver = p_pa_client; + + delete from @NAMESPACE@.sl_path + where pa_server = p_pa_server + and pa_client = p_pa_client; + + if found then + return 1; + else + return 0; + end if; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeListen (li_origin, li_provider, li_receiver) +-- +-- Generate the STORE_LISTEN event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeListen (int4, int4, int4) +returns bigint +as ' +declare + p_li_origin alias for $1; + p_li_provider alias for $2; + p_li_receiver alias for $3; +begin + perform @NAMESPACE@.storeListen_int (p_li_origin, p_li_provider, p_li_receiver); + return @NAMESPACE@.createEvent (''_@CLUSTERNAME@'', ''STORE_LISTEN'', + p_li_origin, p_li_provider, p_li_receiver); +end; +' language plpgsql + called on null input; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeListen_int (li_origin, li_provider, li_receiver) +-- +-- Process the STORE_LISTEN event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeListen_int (int4, int4, int4) +returns int4 +as ' +declare + p_li_origin alias for $1; + p_li_provider alias for $2; + p_li_receiver alias for $3; + v_exists int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + select 1 into v_exists + from @NAMESPACE@.sl_listen + where li_origin = p_li_origin + and li_provider = p_li_provider + and li_receiver = p_li_receiver; + if not found then + -- ---- + -- In case we receive STORE_LISTEN events before we know + -- about the nodes involved in this, we generate those nodes + -- as pending. + -- ---- + if not exists (select 1 from @NAMESPACE@.sl_node + where no_id = p_li_origin) then + perform @NAMESPACE@.storeNode_int (p_li_origin, ''''); + end if; + if not exists (select 1 from @NAMESPACE@.sl_node + where no_id = p_li_provider) then + perform @NAMESPACE@.storeNode_int (p_li_provider, ''''); + end if; + if not exists (select 1 from @NAMESPACE@.sl_node + where no_id = p_li_receiver) then + perform @NAMESPACE@.storeNode_int (p_li_receiver, ''''); + end if; + + insert into @NAMESPACE@.sl_listen + (li_origin, li_provider, li_receiver) values + (p_li_origin, p_li_provider, p_li_receiver); + end if; + + return 0; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropListen (li_origin, li_provider, li_receiver) +-- +-- Generate the DROP_LISTEN event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropListen (int4, int4, int4) +returns bigint +as ' +declare + p_li_origin alias for $1; + p_li_provider alias for $2; + p_li_receiver alias for $3; +begin + perform @NAMESPACE@.dropListen_int(p_li_origin, + p_li_provider, p_li_receiver); + + return @NAMESPACE@.createEvent (''_@CLUSTERNAME@'', ''DROP_LISTEN'', + p_li_origin, p_li_provider, p_li_receiver); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropListen_int (li_origin, li_provider, li_receiver) +-- +-- Process the DROP_LISTEN event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropListen_int (int4, int4, int4) +returns int4 +as ' +declare + p_li_origin alias for $1; + p_li_provider alias for $2; + p_li_receiver alias for $3; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + delete from @NAMESPACE@.sl_listen + where li_origin = p_li_origin + and li_provider = p_li_provider + and li_receiver = p_li_receiver; + if found then + return 1; + else + return 0; + end if; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeSet (set_id, set_comment) +-- +-- Generate the STORE_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeSet (int4, text) +returns bigint +as ' +declare + p_set_id alias for $1; + p_set_comment alias for $2; + v_local_node_id int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + + insert into @NAMESPACE@.sl_set + (set_id, set_origin, set_comment) values + (p_set_id, v_local_node_id, p_set_comment); + + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''STORE_SET'', + p_set_id, v_local_node_id, p_set_comment); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeSet_int (set_id, set_origin, set_comment) +-- +-- Process the STORE_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeSet_int (int4, int4, text) +returns int4 +as ' +declare + p_set_id alias for $1; + p_set_origin alias for $2; + p_set_comment alias for $3; + v_dummy int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + select 1 into v_dummy + from @NAMESPACE@.sl_set + where set_id = p_set_id + for update; + if found then + update @NAMESPACE@.sl_set + set set_comment = p_set_comment + where set_id = p_set_id; + else + if not exists (select 1 from @NAMESPACE@.sl_node + where no_id = p_set_origin) then + perform @NAMESPACE@.storeNode_int (p_set_origin, ''''); + end if; + insert into @NAMESPACE@.sl_set + (set_id, set_origin, set_comment) values + (p_set_id, p_set_origin, p_set_comment); + end if; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION lockSet (set_id) +-- +-- Add a special trigger to all tables of a set that disables +-- access to it. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.lockSet (int4) +returns int4 +as ' +declare + p_set_id alias for $1; + v_local_node_id int4; + v_set_row record; + v_tab_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that the set exists and that we are the origin + -- and that it is not already locked. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select * into v_set_row from @NAMESPACE@.sl_set + where set_id = p_set_id + for update; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_set_row.set_origin <> v_local_node_id then + raise exception ''Slony-I: set % does not originate on local node'', + p_set_id; + end if; + if v_set_row.set_locked notnull then + raise exception ''Slony-I: set % is already locked'', p_set_id; + end if; + + -- ---- + -- Place the lockedSet trigger on all tables in the set. + -- ---- + for v_tab_row in select T.tab_id, + "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) as tab_fqname + from @NAMESPACE@.sl_table T, + "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN + where T.tab_set = p_set_id + and T.tab_reloid = PGC.oid + and PGC.relnamespace = PGN.oid + order by tab_id + loop + execute ''create trigger "_@CLUSTERNAME@_lockedset_'' || + v_tab_row.tab_id || + ''" before insert or update or delete on '' || + v_tab_row.tab_fqname || '' for each row execute procedure + @NAMESPACE@.lockedSet (''''_@CLUSTERNAME@'''');''; + end loop; + + -- ---- + -- Remember our snapshots xmax as for the set locking + -- ---- + update @NAMESPACE@.sl_set + set set_locked = @NAMESPACE@.getMaxXid() + where set_id = p_set_id; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION unlockSet (set_id) +-- +-- Remove the special trigger from all tables of a set that disables +-- access to it. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.unlockSet (int4) +returns int4 +as ' +declare + p_set_id alias for $1; + v_local_node_id int4; + v_set_row record; + v_tab_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that the set exists and that we are the origin + -- and that it is not already locked. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select * into v_set_row from @NAMESPACE@.sl_set + where set_id = p_set_id + for update; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_set_row.set_origin <> v_local_node_id then + raise exception ''Slony-I: set % does not originate on local node'', + p_set_id; + end if; + if v_set_row.set_locked isnull then + raise exception ''Slony-I: set % is not locked'', p_set_id; + end if; + + -- ---- + -- Drop the lockedSet trigger from all tables in the set. + -- ---- + for v_tab_row in select T.tab_id, + "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) as tab_fqname + from @NAMESPACE@.sl_table T, + "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN + where T.tab_set = p_set_id + and T.tab_reloid = PGC.oid + and PGC.relnamespace = PGN.oid + order by tab_id + loop + execute ''drop trigger "_@CLUSTERNAME@_lockedset_'' || + v_tab_row.tab_id || ''" on '' || v_tab_row.tab_fqname; + end loop; + + -- ---- + -- Clear out the set_locked field + -- ---- + update @NAMESPACE@.sl_set + set set_locked = NULL + where set_id = p_set_id; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION moveSet (set_id, new_origin) +-- +-- Generate the MOVE_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.moveSet (int4, int4) +returns bigint +as ' +declare + p_set_id alias for $1; + p_new_origin alias for $2; + v_local_node_id int4; + v_set_row record; + v_sub_row record; + v_sync_seqno int8; + v_lv_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that the set is locked and that this locking + -- happened long enough ago. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select * into v_set_row from @NAMESPACE@.sl_set + where set_id = p_set_id + for update; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_set_row.set_origin <> v_local_node_id then + raise exception ''Slony-I: set % does not originate on local node'', + p_set_id; + end if; + if v_set_row.set_locked isnull then + raise exception ''Slony-I: set % is not locked'', p_set_id; + end if; + if v_set_row.set_locked > @NAMESPACE@.getMinXid() then + raise exception ''Slony-I: cannot move set % yet, transactions < % are still in progress'', + p_set_id, v_set_row.set_locked; + end if; + + -- ---- + -- Unlock the set + -- ---- + perform @NAMESPACE@.unlockSet(p_set_id); + + -- ---- + -- Check that the new_origin is an active subscriber of the set + -- ---- + select * into v_sub_row from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = p_new_origin; + if not found then + raise exception ''Slony-I: set % is not subscribed by node %'', + p_set_id, p_new_origin; + end if; + if not v_sub_row.sub_active then + raise exception ''Slony-I: subsctiption of node % for set % is inactive'', + p_new_origin, p_set_id; + end if; + + -- ---- + -- Reconfigure everything + -- ---- + perform @NAMESPACE@.moveSet_int(p_set_id, v_local_node_id, + p_new_origin); + + -- ---- + -- At this time we hold access exclusive locks for every table + -- in the set. But we did move the set to the new origin, so the + -- createEvent() we are doing now will not record the sequences. + -- ---- + v_sync_seqno := @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SYNC''); + insert into @NAMESPACE@.sl_seqlog + (seql_seqid, seql_origin, seql_ev_seqno, seql_last_value) + select seq_id, v_local_node_id, v_sync_seqno, seq_last_value + from @NAMESPACE@.sl_seqlastvalue + where seq_set = p_set_id; + + -- ---- + -- Finally we generate the real event + -- ---- + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''MOVE_SET'', + p_set_id, v_local_node_id, p_new_origin); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION moveSet_int (set_id, old_origin, new_origin) +-- +-- Process the MOVE_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.moveSet_int (int4, int4, int4) +returns int4 +as ' +declare + p_set_id alias for $1; + p_old_origin alias for $2; + p_new_origin alias for $3; + v_local_node_id int4; + v_tab_row record; + v_sub_row record; + v_sub_node int4; + v_sub_last int4; + v_sub_next int4; + v_last_sync int8; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get our local node ID + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + + -- ---- + -- If we are the old or new origin of the set, we need to + -- remove the log trigger from all tables first. + -- ---- + if v_local_node_id = p_old_origin or v_local_node_id = p_new_origin then + for v_tab_row in select tab_id from @NAMESPACE@.sl_table + where tab_set = p_set_id + order by tab_id + loop + perform @NAMESPACE@.alterTableRestore(v_tab_row.tab_id); + end loop; + end if; + + -- ---- + -- Next we have to reverse the subscription path + -- ---- + v_sub_last = p_new_origin; + select sub_provider into v_sub_node + from @NAMESPACE@.sl_subscribe + where sub_receiver = p_new_origin; + if not found then + raise exception ''Slony-I: subscription path broken in moveSet_int''; + end if; + while v_sub_node <> p_old_origin loop + -- ---- + -- Tracing node by node, the old receiver is now in + -- v_sub_last and the old provider is in v_sub_node. + -- ---- + + -- ---- + -- Get the current provider of this node as next + -- and change the provider to the previous one in + -- the reverse chain. + -- ---- + select sub_provider into v_sub_next + from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = v_sub_node + for update; + if not found then + raise exception ''Slony-I: subscription path broken in moveSet_int''; + end if; + update @NAMESPACE@.sl_subscribe + set sub_provider = v_sub_last + where sub_set = p_set_id + and sub_receiver = v_sub_node; + + v_sub_last = v_sub_node; + v_sub_node = v_sub_next; + end loop; + + -- ---- + -- This includes creating a subscription for the old origin + -- ---- + insert into @NAMESPACE@.sl_subscribe + (sub_set, sub_provider, sub_receiver, + sub_forward, sub_active) + values (p_set_id, v_sub_last, p_old_origin, true, true); + if v_local_node_id = p_old_origin then + select coalesce(max(ev_seqno), 0) into v_last_sync + from @NAMESPACE@.sl_event + where ev_origin = p_new_origin + and ev_type = ''SYNC''; + if v_last_sync > 0 then + insert into @NAMESPACE@.sl_setsync + (ssy_setid, ssy_origin, ssy_seqno, + ssy_minxid, ssy_maxxid, ssy_xip, ssy_action_list) + select p_set_id, p_new_origin, v_last_sync, + ev_minxid, ev_maxxid, ev_xip, NULL + from @NAMESPACE@.sl_event + where ev_origin = p_new_origin + and ev_seqno = v_last_sync; + else + insert into @NAMESPACE@.sl_setsync + (ssy_setid, ssy_origin, ssy_seqno, + ssy_minxid, ssy_maxxid, ssy_xip, ssy_action_list) + values (p_set_id, p_new_origin, ''0'', + ''0'', ''0'', '''', NULL); + end if; + end if; + + -- ---- + -- Now change the ownership of the set. + -- ---- + update @NAMESPACE@.sl_set + set set_origin = p_new_origin + where set_id = p_set_id; + + -- ---- + -- On the new origin, delete the obsolete setsync information + -- and the subscription. + -- ---- + if v_local_node_id = p_new_origin then + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_set_id; + else + if v_local_node_id <> p_old_origin then + -- + -- On every other node, change the setsync so that it will + -- pick up from the new origins last known sync. + -- + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_set_id; + select coalesce(max(ev_seqno), 0) into v_last_sync + from @NAMESPACE@.sl_event + where ev_origin = p_new_origin + and ev_type = ''SYNC''; + if v_last_sync > 0 then + insert into @NAMESPACE@.sl_setsync + (ssy_setid, ssy_origin, ssy_seqno, + ssy_minxid, ssy_maxxid, ssy_xip, ssy_action_list) + select p_set_id, p_new_origin, v_last_sync, + ev_minxid, ev_maxxid, ev_xip, NULL + from @NAMESPACE@.sl_event + where ev_origin = p_new_origin + and ev_seqno = v_last_sync; + else + insert into @NAMESPACE@.sl_setsync + (ssy_setid, ssy_origin, ssy_seqno, + ssy_minxid, ssy_maxxid, ssy_xip, ssy_action_list) + values (p_set_id, p_new_origin, ''0'', + ''0'', ''0'', '''', NULL); + end if; + end if; + end if; + delete from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = p_new_origin; + + -- ---- + -- If we are the new or old origin, we have to + -- put all the tables into altered state again. + -- ---- + if v_local_node_id = p_old_origin or v_local_node_id = p_new_origin then + for v_tab_row in select tab_id from @NAMESPACE@.sl_table + where tab_set = p_set_id + order by tab_id + loop + perform @NAMESPACE@.alterTableForReplication(v_tab_row.tab_id); + end loop; + end if; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropSet (set_id) +-- +-- Generate the DROP_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropSet (int4) +returns bigint +as ' +declare + p_set_id alias for $1; + v_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that the set exists and originates here + -- ---- + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = p_set_id; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + p_set_id; + end if; + + -- ---- + -- Call the internal drop set functionality and generate the event + -- ---- + perform @NAMESPACE@.dropSet_int(p_set_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''DROP_SET'', + p_set_id); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropSet_int (set_id) +-- +-- Process the DROP_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropSet_int (int4) +returns int4 +as ' +declare + p_set_id alias for $1; + v_tab_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Restore all tables original triggers and rules and remove + -- our replication stuff. + -- ---- + for v_tab_row in select tab_id from @NAMESPACE@.sl_table + where tab_set = p_set_id + order by tab_id + loop + perform @NAMESPACE@.alterTableRestore(v_tab_row.tab_id); + perform @NAMESPACE@.tableDropKey(v_tab_row.tab_id); + end loop; + + -- ---- + -- Remove all traces of the set configuration + -- ---- + delete from @NAMESPACE@.sl_sequence + where seq_set = p_set_id; + delete from @NAMESPACE@.sl_table + where tab_set = p_set_id; + delete from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id; + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_set_id; + delete from @NAMESPACE@.sl_set + where set_id = p_set_id; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION mergeSet (set_id, add_id) +-- +-- Generate the MERGE_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.mergeSet (int4, int4) +returns bigint +as ' +declare + p_set_id alias for $1; + p_add_id alias for $2; + v_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that both sets exist and originate here + -- ---- + if p_set_id = p_add_id then + raise exception ''Slony-I: merged set ids cannot be identical''; + end if; + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = p_set_id; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + p_set_id; + end if; + + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = p_add_id; + if not found then + raise exception ''Slony-I: set % not found'', p_add_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + p_add_id; + end if; + + -- ---- + -- Check that both sets are subscribed by the same set of nodes + -- ---- + if exists (select true from @NAMESPACE@.sl_subscribe SUB1 + where SUB1.sub_set = p_set_id + and SUB1.sub_receiver not in (select SUB2.sub_receiver + from @NAMESPACE@.sl_subscribe SUB2 + where SUB2.sub_set = p_add_id)) + then + raise exception ''Slony-I: subscriber lists of set % and % are different'', + p_set_id, p_add_id; + end if; + + if exists (select true from @NAMESPACE@.sl_subscribe SUB1 + where SUB1.sub_set = p_add_id + and SUB1.sub_receiver not in (select SUB2.sub_receiver + from @NAMESPACE@.sl_subscribe SUB2 + where SUB2.sub_set = p_set_id)) + then + raise exception ''Slony-I: subscriber lists of set % and % are different'', + p_add_id, p_set_id; + end if; + + -- ---- + -- Also check that there are no unconfirmed enable subscriptions + -- still lingering (prevents bug 896) + -- ---- + if exists (select true from @NAMESPACE@.sl_event + where ev_origin = v_origin + and ev_type = ''ENABLE_SUBSCRIPTION'' + and ev_data1 = p_set_id + and ev_seqno > (select min(max_con_seqno) from + (select con_received, max(con_seqno) as max_con_seqno + from @NAMESPACE@.sl_confirm + where con_origin = v_origin + group by con_received) as CON + ) + ) + then + raise exception ''Slony-I: set % cannot be merged because of pending subscription'', + p_set_id; + end if; + + if exists (select true from @NAMESPACE@.sl_event + where ev_origin = v_origin + and ev_type = ''ENABLE_SUBSCRIPTION'' + and ev_data1 = p_add_id + and ev_seqno > (select min(max_con_seqno) from + (select con_received, max(con_seqno) as max_con_seqno + from @NAMESPACE@.sl_confirm + where con_origin = v_origin + group by con_received) as CON + ) + ) + then + raise exception ''Slony-I: set % cannot be merged because of pending subscription'', + p_add_id; + end if; + + -- ---- + -- Create a SYNC event, merge the sets, create a MERGE_SET event + -- ---- + perform @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SYNC'', NULL); + perform @NAMESPACE@.mergeSet_int(p_set_id, p_add_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''MERGE_SET'', + p_set_id, p_add_id); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION mergeSet_int (set_id, add_id) +-- +-- Process the MERGE_SET event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.mergeSet_int (int4, int4) +returns int4 +as ' +declare + p_set_id alias for $1; + p_add_id alias for $2; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + update @NAMESPACE@.sl_sequence + set seq_set = p_set_id + where seq_set = p_add_id; + update @NAMESPACE@.sl_table + set tab_set = p_set_id + where tab_set = p_add_id; + delete from @NAMESPACE@.sl_subscribe + where sub_set = p_add_id; + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_add_id; + delete from @NAMESPACE@.sl_set + where set_id = p_add_id; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setAddTable (set_id, tab_id, tab_fqname, tab_idxname, +-- tab_comment) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setAddTable(int4, int4, text, name, text) +returns bigint +as ' +declare + p_set_id alias for $1; + p_tab_id alias for $2; + p_fqname alias for $3; + p_tab_idxname alias for $4; + p_tab_comment alias for $5; + v_set_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that we are the origin of the set + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_set_id; + if not found then + raise exception ''Slony-I: setAddTable(): set % not found'', p_set_id; + end if; + if v_set_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: setAddTable(): set % has remote origin'', p_set_id; + end if; + + if exists (select true from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id) + then + raise exception ''Slony-I: cannot add table to currently subscribed set %'', + p_set_id; + end if; + + -- ---- + -- Add the table to the set and generate the SET_ADD_TABLE event + -- ---- + perform @NAMESPACE@.setAddTable_int(p_set_id, p_tab_id, p_fqname, + p_tab_idxname, p_tab_comment); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SET_ADD_TABLE'', + p_set_id, p_tab_id, p_fqname, + p_tab_idxname, p_tab_comment); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setAddTable_int (set_id, tab_id, tab_fqname, tab_idxname, +-- tab_comment) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setAddTable_int(int4, int4, text, name, text) +returns int4 +as ' +declare + p_set_id alias for $1; + p_tab_id alias for $2; + p_fqname alias for $3; + p_tab_idxname alias for $4; + p_tab_comment alias for $5; + v_local_node_id int4; + v_set_origin int4; + v_sub_provider int4; + v_relkind char; + v_tab_reloid oid; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- For sets with a remote origin, check that we are subscribed + -- to that set. Otherwise we ignore the table because it might + -- not even exist in our database. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_set_id; + if not found then + raise exception ''Slony-I: setAddTable_int(): set % not found'', + p_set_id; + end if; + if v_set_origin != v_local_node_id then + select sub_provider into v_sub_provider + from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + if not found then + return 0; + end if; + end if; + + -- ---- + -- Get the tables OID and check that it is a real table + -- ---- + select PGC.oid, PGC.relkind into v_tab_reloid, v_relkind + from "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN + where PGC.relnamespace = PGN.oid + and p_fqname = "pg_catalog".quote_ident(PGN.nspname) || + ''.'' || "pg_catalog".quote_ident(PGC.relname); + if not found then + raise exception ''Slony-I: setAddTable(): table % not found'', + p_fqname; + end if; + if v_relkind != ''r'' then + raise exception ''Slony-I: setAddTable(): % is not a regular table'', + p_fqname; + end if; + + if not exists (select indexrelid + from "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGC + where PGX.indrelid = v_tab_reloid + and PGX.indexrelid = PGC.oid + and PGC.relname = p_tab_idxname) + then + raise exception ''Slony-I: setAddTable(): table % has no index %'', + p_fqname, p_tab_idxname; + end if; + + -- ---- + -- Add the table to sl_table and create the trigger on it. + -- ---- + insert into @NAMESPACE@.sl_table + (tab_id, tab_reloid, tab_set, tab_idxname, + tab_altered, tab_comment) values + (p_tab_id, v_tab_reloid, p_set_id, p_tab_idxname, + false, p_tab_comment); + perform @NAMESPACE@.alterTableForReplication(p_tab_id); + + return p_tab_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setDropTable (tab_id) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setDropTable(int4) +returns bigint +as ' +declare + p_tab_id alias for $1; + v_set_id int4; + v_set_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Determine the set_id + -- ---- + select tab_set into v_set_id from @NAMESPACE@.sl_table where tab_id = p_tab_id; + + -- ---- + -- Ensure table exists + -- ---- + if not found then + raise exception ''Slony-I: setDropTable_int(): table % not found'', + p_tab_id; + end if; + + -- ---- + -- Check that we are the origin of the set + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = v_set_id; + if not found then + raise exception ''Slony-I: setDropTable(): set % not found'', v_set_id; + end if; + if v_set_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: setDropTable(): set % has remote origin'', v_set_id; + end if; + + -- ---- + -- Drop the table from the set and generate the SET_ADD_TABLE event + -- ---- + perform @NAMESPACE@.setDropTable_int(p_tab_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SET_DROP_TABLE'', p_tab_id); +end; +' language plpgsql; +comment on function @NAMESPACE@.setDropTable(int4) is +'setDropTable (tab_id) + +Drop table tab_id from set on origin node, and generate SET_DROP_TABLE +event to allow this to propagate to other nodes.'; + +-- ---------------------------------------------------------------------- +-- FUNCTION setDropTable_int (tab_id) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setDropTable_int(int4) +returns int4 +as ' +declare + p_tab_id alias for $1; + v_set_id int4; + v_local_node_id int4; + v_set_origin int4; + v_sub_provider int4; + v_tab_reloid oid; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Determine the set_id + -- ---- + select tab_set into v_set_id from @NAMESPACE@.sl_table where tab_id = p_tab_id; + + -- ---- + -- Ensure table exists + -- ---- + if not found then + return 0; + end if; + + -- ---- + -- For sets with a remote origin, check that we are subscribed + -- to that set. Otherwise we ignore the table because it might + -- not even exist in our database. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = v_set_id; + if not found then + raise exception ''Slony-I: setDropTable_int(): set % not found'', + v_set_id; + end if; + if v_set_origin != v_local_node_id then + select sub_provider into v_sub_provider + from @NAMESPACE@.sl_subscribe + where sub_set = v_set_id + and sub_receiver = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + if not found then + return 0; + end if; + end if; + + -- ---- + -- Drop the table from sl_table and drop trigger from it. + -- ---- + perform @NAMESPACE@.alterTableRestore(p_tab_id); + perform @NAMESPACE@.tableDropKey(p_tab_id); + delete from @NAMESPACE@.sl_table where tab_id = p_tab_id; + return p_tab_id; +end; +' language plpgsql; +comment on function @NAMESPACE@.setDropTable_int(int4) is +'setDropTable_int (tab_id) + +This function processes the SET_DROP_TABLE event on remote nodes, +dropping a table from replication if the remote node is subscribing to +its replication set.'; + +-- ---------------------------------------------------------------------- +-- FUNCTION setAddSequence (set_id, seq_id, seq_fqname, seq_comment) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setAddSequence (int4, int4, text, text) +returns bigint +as ' +declare + p_set_id alias for $1; + p_seq_id alias for $2; + p_fqname alias for $3; + p_seq_comment alias for $4; + v_set_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that we are the origin of the set + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_set_id; + if not found then + raise exception ''Slony-I: setAddSequence(): set % not found'', p_set_id; + end if; + if v_set_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: setAddSequence(): set % has remote origin'', p_set_id; + end if; + + if exists (select true from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id) + then + raise exception ''Slony-I: cannot add sequence to currently subscribed set %'', + p_set_id; + end if; + + -- ---- + -- Add the sequence to the set and generate the SET_ADD_SEQUENCE event + -- ---- + perform @NAMESPACE@.setAddSequence_int(p_set_id, p_seq_id, p_fqname, + p_seq_comment); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SET_ADD_SEQUENCE'', + p_set_id, p_seq_id, p_fqname, p_seq_comment); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setAddSequence_int (set_id, seq_id, seq_fqname, seq_comment +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setAddSequence_int(int4, int4, text, text) +returns int4 +as ' +declare + p_set_id alias for $1; + p_seq_id alias for $2; + p_fqname alias for $3; + p_seq_comment alias for $4; + v_local_node_id int4; + v_set_origin int4; + v_sub_provider int4; + v_relkind char; + v_seq_reloid oid; + v_sync_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- For sets with a remote origin, check that we are subscribed + -- to that set. Otherwise we ignore the sequence because it might + -- not even exist in our database. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_set_id; + if not found then + raise exception ''Slony-I: setAddSequence_int(): set % not found'', + p_set_id; + end if; + if v_set_origin != v_local_node_id then + select sub_provider into v_sub_provider + from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + if not found then + return 0; + end if; + end if; + + -- ---- + -- Get the sequences OID and check that it is a sequence + -- ---- + select PGC.oid, PGC.relkind into v_seq_reloid, v_relkind + from "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN + where PGC.relnamespace = PGN.oid + and p_fqname = "pg_catalog".quote_ident(PGN.nspname) || + ''.'' || "pg_catalog".quote_ident(PGC.relname); + if not found then + raise exception ''Slony-I: setAddSequence_int(): sequence % not found'', + p_fqname; + end if; + if v_relkind != ''S'' then + raise exception ''Slony-I: setAddSequence_int(): % is not a sequence'', + p_fqname; + end if; + + -- ---- + -- Add the sequence to sl_sequence + -- ---- + insert into @NAMESPACE@.sl_sequence + (seq_id, seq_reloid, seq_set, seq_comment) values + (p_seq_id, v_seq_reloid, p_set_id, p_seq_comment); + + -- ---- + -- On the set origin, fake a sl_seqlog row for the last sync event + -- ---- + if v_set_origin = v_local_node_id then + for v_sync_row in select coalesce (max(ev_seqno), 0) as ev_seqno + from @NAMESPACE@.sl_event + where ev_origin = v_local_node_id + and ev_type = ''SYNC'' + loop + insert into @NAMESPACE@.sl_seqlog + (seql_seqid, seql_origin, seql_ev_seqno, + seql_last_value) values + (p_seq_id, v_local_node_id, v_sync_row.ev_seqno, + @NAMESPACE@.sequenceLastValue(p_fqname)); + end loop; + end if; + + return p_seq_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setDropSequence (seq_id) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setDropSequence (int4) +returns bigint +as ' +declare + p_seq_id alias for $1; + v_set_id int4; + v_set_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Determine set id for this sequence + -- ---- + select seq_set into v_set_id from @NAMESPACE@.sl_sequence where seq_id = p_seq_id; + + -- ---- + -- Ensure sequence exists + -- ---- + if not found then + raise exception ''Slony-I: setDropSequence_int(): sequence % not found'', + p_seq_id; + end if; + + -- ---- + -- Check that we are the origin of the set + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = v_set_id; + if not found then + raise exception ''Slony-I: setDropSequence(): set % not found'', v_set_id; + end if; + if v_set_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: setDropSequence(): set % has remote origin'', v_set_id; + end if; + + -- ---- + -- Add the sequence to the set and generate the SET_ADD_SEQUENCE event + -- ---- + perform @NAMESPACE@.setDropSequence_int(p_seq_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SET_DROP_SEQUENCE'', + p_seq_id); +end; +' language plpgsql; +comment on function @NAMESPACE@.setDropSequence (int4) is +'setDropSequence (seq_id) + +On the origin node for the set, drop sequence seq_id from replication +set, and raise SET_DROP_SEQUENCE to cause this to replicate to +subscriber nodes.'; + +-- ---------------------------------------------------------------------- +-- FUNCTION setDropSequence_int (seq_id) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setDropSequence_int(int4) +returns int4 +as ' +declare + p_seq_id alias for $1; + v_set_id int4; + v_local_node_id int4; + v_set_origin int4; + v_sub_provider int4; + v_relkind char; + v_sync_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Determine set id for this sequence + -- ---- + select seq_set into v_set_id from @NAMESPACE@.sl_sequence where seq_id = p_seq_id; + + -- ---- + -- Ensure sequence exists + -- ---- + if not found then + return 0; + end if; + + -- ---- + -- For sets with a remote origin, check that we are subscribed + -- to that set. Otherwise we ignore the sequence because it might + -- not even exist in our database. + -- ---- + v_local_node_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = v_set_id; + if not found then + raise exception ''Slony-I: setDropSequence_int(): set % not found'', + v_set_id; + end if; + if v_set_origin != v_local_node_id then + select sub_provider into v_sub_provider + from @NAMESPACE@.sl_subscribe + where sub_set = v_set_id + and sub_receiver = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + if not found then + return 0; + end if; + end if; + + -- ---- + -- drop the sequence from sl_sequence, sl_seqlog + -- ---- + delete from @NAMESPACE@.sl_seqlog where seql_seqid = p_seq_id; + delete from @NAMESPACE@.sl_sequence where seq_id = p_seq_id; + + return p_seq_id; +end; +' language plpgsql; +comment on function @NAMESPACE@.setDropSequence_int(int4) is +'setDropSequence_int (seq_id) + +This processes the SET_DROP_SEQUENCE event. On remote nodes that +subscribe to the set containing sequence seq_id, drop the sequence +from the replication set.'; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setMoveTable (tab_id, new_set_id) +-- +-- Generate the SET_MOVE_TABLE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setMoveTable (int4, int4) +returns bigint +as ' +declare + p_tab_id alias for $1; + p_new_set_id alias for $2; + v_old_set_id int4; + v_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get the tables current set + -- ---- + select tab_set into v_old_set_id from @NAMESPACE@.sl_table + where tab_id = p_tab_id; + if not found then + raise exception ''Slony-I: table %d not found'', p_tab_id; + end if; + + -- ---- + -- Check that both sets exist and originate here + -- ---- + if p_new_set_id = v_old_set_id then + raise exception ''Slony-I: set ids cannot be identical''; + end if; + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = p_new_set_id; + if not found then + raise exception ''Slony-I: set % not found'', p_new_set_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + p_new_set_id; + end if; + + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = v_old_set_id; + if not found then + raise exception ''Slony-I: set % not found'', v_old_set_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + v_old_set_id; + end if; + + -- ---- + -- Check that both sets are subscribed by the same set of nodes + -- ---- + if exists (select true from @NAMESPACE@.sl_subscribe SUB1 + where SUB1.sub_set = p_new_set_id + and SUB1.sub_receiver not in (select SUB2.sub_receiver + from @NAMESPACE@.sl_subscribe SUB2 + where SUB2.sub_set = v_old_set_id)) + then + raise exception ''Slony-I: subscriber lists of set % and % are different'', + p_new_set_id, v_old_set_id; + end if; + + if exists (select true from @NAMESPACE@.sl_subscribe SUB1 + where SUB1.sub_set = v_old_set_id + and SUB1.sub_receiver not in (select SUB2.sub_receiver + from @NAMESPACE@.sl_subscribe SUB2 + where SUB2.sub_set = p_new_set_id)) + then + raise exception ''Slony-I: subscriber lists of set % and % are different'', + v_old_set_id, p_new_set_id; + end if; + + -- ---- + -- Also check that there are no unconfirmed enable subscriptions + -- still lingering (prevents bug 896) + -- ---- + if exists (select true from @NAMESPACE@.sl_event + where ev_origin = v_origin + and ev_type = ''ENABLE_SUBSCRIPTION'' + and ev_data1 = v_old_set_id + and ev_seqno > (select min(max_con_seqno) from + (select con_received, max(con_seqno) as max_con_seqno + from @NAMESPACE@.sl_confirm + where con_origin = v_origin + group by con_received) as CON + ) + ) + then + raise exception ''Slony-I: table cannot be moved because of pending subscription''; + end if; + + if exists (select true from @NAMESPACE@.sl_event + where ev_origin = v_origin + and ev_type = ''ENABLE_SUBSCRIPTION'' + and ev_data1 = p_new_set_id + and ev_seqno > (select min(max_con_seqno) from + (select con_received, max(con_seqno) as max_con_seqno + from @NAMESPACE@.sl_confirm + where con_origin = v_origin + group by con_received) as CON + ) + ) + then + raise exception ''Slony-I: table cannot be moved because of pending subscription''; + end if; + + -- ---- + -- Change the set the table belongs to + -- ---- + perform @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SYNC'', NULL); + perform @NAMESPACE@.setMoveTable_int(p_tab_id, p_new_set_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SET_MOVE_TABLE'', + p_tab_id, p_new_set_id); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setMoveTable_int (tab_id, new_set_id) +-- +-- Process the SET_MOVE_TABLE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setMoveTable_int (int4, int4) +returns int4 +as ' +declare + p_tab_id alias for $1; + p_new_set_id alias for $2; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Move the table to the new set + -- ---- + update @NAMESPACE@.sl_table + set tab_set = p_new_set_id + where tab_id = p_tab_id; + + return p_tab_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setMoveSequence (seq_id, new_set_id) +-- +-- Generate the SET_MOVE_SEQUENCE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setMoveSequence (int4, int4) +returns bigint +as ' +declare + p_seq_id alias for $1; + p_new_set_id alias for $2; + v_old_set_id int4; + v_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get the sequences current set + -- ---- + select seq_set into v_old_set_id from @NAMESPACE@.sl_sequence + where seq_id = p_seq_id; + if not found then + raise exception ''Slony-I: sequence %d not found'', p_seq_id; + end if; + + -- ---- + -- Check that both sets exist and originate here + -- ---- + if p_new_set_id = v_old_set_id then + raise exception ''Slony-I: set ids cannot be identical''; + end if; + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = p_new_set_id; + if not found then + raise exception ''Slony-I: set % not found'', p_new_set_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + p_new_set_id; + end if; + + select set_origin into v_origin from @NAMESPACE@.sl_set + where set_id = v_old_set_id; + if not found then + raise exception ''Slony-I: set % not found'', v_old_set_id; + end if; + if v_origin != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + v_old_set_id; + end if; + + -- ---- + -- Check that both sets are subscribed by the same set of nodes + -- ---- + if exists (select true from @NAMESPACE@.sl_subscribe SUB1 + where SUB1.sub_set = p_new_set_id + and SUB1.sub_receiver not in (select SUB2.sub_receiver + from @NAMESPACE@.sl_subscribe SUB2 + where SUB2.sub_set = v_old_set_id)) + then + raise exception ''Slony-I: subscriber lists of set % and % are different'', + p_new_set_id, v_old_set_id; + end if; + + if exists (select true from @NAMESPACE@.sl_subscribe SUB1 + where SUB1.sub_set = v_old_set_id + and SUB1.sub_receiver not in (select SUB2.sub_receiver + from @NAMESPACE@.sl_subscribe SUB2 + where SUB2.sub_set = p_new_set_id)) + then + raise exception ''Slony-I: subscriber lists of set % and % are different'', + v_old_set_id, p_new_set_id; + end if; + + -- ---- + -- Also check that there are no unconfirmed enable subscriptions + -- still lingering (prevents bug 896) + -- ---- + if exists (select true from @NAMESPACE@.sl_event + where ev_origin = v_origin + and ev_type = ''ENABLE_SUBSCRIPTION'' + and ev_data1 = v_old_set_id + and ev_seqno > (select min(max_con_seqno) from + (select con_received, max(con_seqno) as max_con_seqno + from @NAMESPACE@.sl_confirm + where con_origin = v_origin + group by con_received) as CON + ) + ) + then + raise exception ''Slony-I: sequence cannot be moved because of pending subscription''; + end if; + + if exists (select true from @NAMESPACE@.sl_event + where ev_origin = v_origin + and ev_type = ''ENABLE_SUBSCRIPTION'' + and ev_data1 = p_new_set_id + and ev_seqno > (select min(max_con_seqno) from + (select con_received, max(con_seqno) as max_con_seqno + from @NAMESPACE@.sl_confirm + where con_origin = v_origin + group by con_received) as CON + ) + ) + then + raise exception ''Slony-I: sequence cannot be moved because of pending subscription''; + end if; + + -- ---- + -- Change the set the sequence belongs to + -- ---- + perform @NAMESPACE@.setMoveSequence_int(p_seq_id, p_new_set_id); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SET_MOVE_SEQUENCE'', + p_seq_id, p_new_set_id); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION setMoveSequence_int (seq_id, new_set_id) +-- +-- Process the SET_MOVE_SEQUENCE event. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.setMoveSequence_int (int4, int4) +returns int4 +as ' +declare + p_seq_id alias for $1; + p_new_set_id alias for $2; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Move the sequence to the new set + -- ---- + update @NAMESPACE@.sl_sequence + set seq_set = p_new_set_id + where seq_id = p_seq_id; + + return p_seq_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION sequenceSetValue (seq_id, seq_origin, ev_seqno, last_value) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.sequenceSetValue(int4, int4, int8, int8) returns int4 +as ' +declare + p_seq_id alias for $1; + p_seq_origin alias for $2; + p_ev_seqno alias for $3; + p_last_value alias for $4; + v_fqname text; +begin + -- ---- + -- Get the sequences fully qualified name + -- ---- + select "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) into v_fqname + from @NAMESPACE@.sl_sequence SQ, + "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN + where SQ.seq_id = p_seq_id + and SQ.seq_reloid = PGC.oid + and PGC.relnamespace = PGN.oid; + if not found then + raise exception ''Slony-I: sequence % not found'', p_seq_id; + end if; + + -- ---- + -- Update it to the new value + -- ---- + execute ''select setval('''''' || v_fqname || + '''''', '''''' || p_last_value || '''''')''; + + insert into @NAMESPACE@.sl_seqlog + (seql_seqid, seql_origin, seql_ev_seqno, seql_last_value) + values (p_seq_id, p_seq_origin, p_ev_seqno, p_last_value); + + return p_seq_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeTrigger (trig_tabid, trig_tgname) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeTrigger (int4, name) +returns bigint +as ' +declare + p_trig_tabid alias for $1; + p_trig_tgname alias for $2; +begin + perform @NAMESPACE@.storeTrigger_int(p_trig_tabid, p_trig_tgname); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''STORE_TRIGGER'', + p_trig_tabid, p_trig_tgname); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION storeTrigger_int (trig_tabid, trig_tgname) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.storeTrigger_int (int4, name) +returns int4 +as ' +declare + p_trig_tabid alias for $1; + p_trig_tgname alias for $2; + v_tab_altered boolean; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get the current table status (altered or not) + -- ---- + select tab_altered into v_tab_altered + from @NAMESPACE@.sl_table where tab_id = p_trig_tabid; + if not found then + -- ---- + -- Not found is no hard error here, because that might + -- mean that we are not subscribed to that set + -- ---- + return 0; + end if; + + -- ---- + -- If the table is modified for replication, restore the original state + -- ---- + if v_tab_altered then + perform @NAMESPACE@.alterTableRestore(p_trig_tabid); + end if; + + -- ---- + -- Make sure that an entry for this trigger exists + -- ---- + delete from @NAMESPACE@.sl_trigger + where trig_tabid = p_trig_tabid + and trig_tgname = p_trig_tgname; + insert into @NAMESPACE@.sl_trigger ( + trig_tabid, trig_tgname + ) values ( + p_trig_tabid, p_trig_tgname + ); + + -- ---- + -- Put the table back into replicated state if it was + -- ---- + if v_tab_altered then + perform @NAMESPACE@.alterTableForReplication(p_trig_tabid); + end if; + + return p_trig_tabid; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropTrigger (trig_tabid, trig_tgname) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropTrigger (int4, name) +returns bigint +as ' +declare + p_trig_tabid alias for $1; + p_trig_tgname alias for $2; +begin + perform @NAMESPACE@.dropTrigger_int(p_trig_tabid, p_trig_tgname); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''DROP_TRIGGER'', + p_trig_tabid, p_trig_tgname); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION dropTrigger_int (trig_tabid, trig_tgname) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.dropTrigger_int (int4, name) +returns int4 +as ' +declare + p_trig_tabid alias for $1; + p_trig_tgname alias for $2; + v_tab_altered boolean; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get the current table status (altered or not) + -- ---- + select tab_altered into v_tab_altered + from @NAMESPACE@.sl_table where tab_id = p_trig_tabid; + if not found then + -- ---- + -- Not found is no hard error here, because that might + -- mean that we are not subscribed to that set + -- ---- + return 0; + end if; + + -- ---- + -- If the table is modified for replication, restore the original state + -- ---- + if v_tab_altered then + perform @NAMESPACE@.alterTableRestore(p_trig_tabid); + end if; + + -- ---- + -- Remove the entry from sl_trigger + -- ---- + delete from @NAMESPACE@.sl_trigger + where trig_tabid = p_trig_tabid + and trig_tgname = p_trig_tgname; + + -- ---- + -- Put the table back into replicated state if it was + -- ---- + if v_tab_altered then + perform @NAMESPACE@.alterTableForReplication(p_trig_tabid); + end if; + + return p_trig_tabid; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION ddlScript (set_id, script) +-- +-- Generate the DDL_SCRIPT event +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.ddlScript (int4, text) +returns bigint +as ' +declare + p_set_id alias for $1; + p_script alias for $2; + v_set_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that the set exists and originates here + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_set_id + for update; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_set_origin <> @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: set % does not originate on local node'', + p_set_id; + end if; + + -- ---- + -- Create a SYNC event, run the script and generate the DDL_SCRIPT event + -- ---- + perform @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SYNC'', NULL); + perform @NAMESPACE@.ddlScript_int(p_set_id, p_script); + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''DDL_SCRIPT'', + p_set_id, p_script); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION ddlScript_int (set_id, script) +-- +-- Process the DDL_SCRIPT event +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.ddlScript_int (int4, text) +returns int4 +as ' +declare + p_set_id alias for $1; + p_script alias for $2; + v_set_origin int4; + v_no_id int4; + v_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that we either are the set origin or a current + -- subscriber of the set. + -- ---- + v_no_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_set_id + for update; + if not found then + raise exception ''Slony-I: set % not found'', p_set_id; + end if; + if v_set_origin <> v_no_id + and not exists (select 1 from @NAMESPACE@.sl_subscribe + where sub_set = p_set_id + and sub_receiver = v_no_id) + then + return 0; + end if; + + -- ---- + -- Restore all original triggers and rules + -- ---- + for v_row in select * from @NAMESPACE@.sl_table + where tab_set = p_set_id + loop + perform @NAMESPACE@.alterTableRestore(v_row.tab_id); + end loop; + + -- ---- + -- Run the script + -- ---- + execute p_script; + + -- ---- + -- Put all tables back into replicated mode + -- ---- + for v_row in select * from @NAMESPACE@.sl_table + where tab_set = p_set_id + loop + perform @NAMESPACE@.alterTableForReplication(v_row.tab_id); + end loop; + + return p_set_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION alterTableForReplication (tab_id) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.alterTableForReplication (int4) +returns int4 +as ' +declare + p_tab_id alias for $1; + v_no_id int4; + v_tab_row record; + v_tab_fqname text; + v_tab_attkind text; + v_n int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get our local node ID + -- ---- + v_no_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + + -- ---- + -- Get the sl_table row and the current tables origin. Check + -- that the table currently is NOT in altered state. + -- ---- + select T.tab_reloid, T.tab_set, T.tab_idxname, T.tab_altered, + S.set_origin, PGX.indexrelid, + "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) as tab_fqname + into v_tab_row + from @NAMESPACE@.sl_table T, @NAMESPACE@.sl_set S, + "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGXC + where T.tab_id = p_tab_id + and T.tab_set = S.set_id + and T.tab_reloid = PGC.oid + and PGC.relnamespace = PGN.oid + and PGX.indrelid = T.tab_reloid + and PGX.indexrelid = PGXC.oid + and PGXC.relname = T.tab_idxname + for update; + if not found then + raise exception ''Slony-I: Table with id % not found'', p_tab_id; + end if; + v_tab_fqname = v_tab_row.tab_fqname; + if v_tab_row.tab_altered then + raise exception ''Slony-I: Table % is already in altered state'', + v_tab_fqname; + end if; + + v_tab_attkind := @NAMESPACE@.determineAttKindUnique(v_tab_row.tab_fqname, + v_tab_row.tab_idxname); + + execute ''lock table '' || v_tab_fqname || '' in access exclusive mode''; + + -- ---- + -- Procedures are different on origin and subscriber + -- ---- + if v_no_id = v_tab_row.set_origin then + -- ---- + -- On the Origin we add the log trigger to the table and done + -- ---- + execute ''create trigger "_@CLUSTERNAME@_logtrigger_'' || + p_tab_id || ''" after insert or update or delete on '' || + v_tab_fqname || '' for each row execute procedure + @NAMESPACE@.logTrigger (''''_@CLUSTERNAME@'''', '''''' || + p_tab_id || '''''', '''''' || + v_tab_attkind || '''''');''; + else + -- ---- + -- On the subscriber the thing is a bit more difficult. We want + -- to disable all user- and foreign key triggers and rules. + -- ---- + + + -- ---- + -- Disable all existing triggers + -- ---- + update "pg_catalog".pg_trigger + set tgrelid = v_tab_row.indexrelid + where tgrelid = v_tab_row.tab_reloid + and not exists ( + select true from @NAMESPACE@.sl_table TAB, + @NAMESPACE@.sl_trigger TRIG + where TAB.tab_reloid = tgrelid + and TAB.tab_id = TRIG.trig_tabid + and TRIG.trig_tgname = tgname + ); + get diagnostics v_n = row_count; + if v_n > 0 then + update "pg_catalog".pg_class + set reltriggers = reltriggers - v_n + where oid = v_tab_row.tab_reloid; + end if; + + -- ---- + -- Disable all existing rules + -- ---- + update "pg_catalog".pg_rewrite + set ev_class = v_tab_row.indexrelid + where ev_class = v_tab_row.tab_reloid; + get diagnostics v_n = row_count; + if v_n > 0 then + update "pg_catalog".pg_class + set relhasrules = false + where oid = v_tab_row.tab_reloid; + end if; + + -- ---- + -- Add the trigger that denies write access to replicated tables + -- ---- + execute ''create trigger "_@CLUSTERNAME@_denyaccess_'' || + p_tab_id || ''" before insert or update or delete on '' || + v_tab_fqname || '' for each row execute procedure + @NAMESPACE@.denyAccess (''''_@CLUSTERNAME@'''');''; + end if; + + -- ---- + -- Mark the table altered in our configuration + -- ---- + update @NAMESPACE@.sl_table + set tab_altered = true where tab_id = p_tab_id; + + return p_tab_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION alterTableRestore (tab_id) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.alterTableRestore (int4) +returns int4 +as ' +declare + p_tab_id alias for $1; + v_no_id int4; + v_tab_row record; + v_tab_fqname text; + v_n int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Get our local node ID + -- ---- + v_no_id := @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@''); + + -- ---- + -- Get the sl_table row and the current tables origin. Check + -- that the table currently IS in altered state. + -- ---- + select T.tab_reloid, T.tab_set, T.tab_altered, + S.set_origin, PGX.indexrelid, + "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) as tab_fqname + into v_tab_row + from @NAMESPACE@.sl_table T, @NAMESPACE@.sl_set S, + "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGXC + where T.tab_id = p_tab_id + and T.tab_set = S.set_id + and T.tab_reloid = PGC.oid + and PGC.relnamespace = PGN.oid + and PGX.indrelid = T.tab_reloid + and PGX.indexrelid = PGXC.oid + and PGXC.relname = T.tab_idxname + for update; + if not found then + raise exception ''Slony-I: Table with id % not found'', p_tab_id; + end if; + v_tab_fqname = v_tab_row.tab_fqname; + if not v_tab_row.tab_altered then + raise exception ''Slony-I: Table % is not in altered state'', + v_tab_fqname; + end if; + + execute ''lock table '' || v_tab_fqname || '' in access exclusive mode''; + + -- ---- + -- Procedures are different on origin and subscriber + -- ---- + if v_no_id = v_tab_row.set_origin then + -- ---- + -- On the Origin we just drop the trigger we originally added + -- ---- + execute ''drop trigger "_@CLUSTERNAME@_logtrigger_'' || + p_tab_id || ''" on '' || v_tab_fqname; + else + -- ---- + -- On the subscriber drop the denyAccess trigger + -- ---- + execute ''drop trigger "_@CLUSTERNAME@_denyaccess_'' || + p_tab_id || ''" on '' || v_tab_fqname; + + -- ---- + -- Restore all original triggers + -- ---- + update "pg_catalog".pg_trigger + set tgrelid = v_tab_row.tab_reloid + where tgrelid = v_tab_row.indexrelid; + get diagnostics v_n = row_count; + if v_n > 0 then + update "pg_catalog".pg_class + set reltriggers = reltriggers + v_n + where oid = v_tab_row.tab_reloid; + end if; + + -- ---- + -- Restore all original rewrite rules + -- ---- + update "pg_catalog".pg_rewrite + set ev_class = v_tab_row.tab_reloid + where ev_class = v_tab_row.indexrelid; + get diagnostics v_n = row_count; + if v_n > 0 then + update "pg_catalog".pg_class + set relhasrules = true + where oid = v_tab_row.tab_reloid; + end if; + + end if; + + -- ---- + -- Mark the table not altered in our configuration + -- ---- + update @NAMESPACE@.sl_table + set tab_altered = false where tab_id = p_tab_id; + + return p_tab_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION subscribeSet (sub_set, sub_provider, sub_receiver, sub_forward) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.subscribeSet (int4, int4, int4, bool) +returns bigint +as ' +declare + p_sub_set alias for $1; + p_sub_provider alias for $2; + p_sub_receiver alias for $3; + p_sub_forward alias for $4; + v_set_origin int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that this is called on the receiver node + -- ---- + if p_sub_receiver != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: subscribeSet() must be called on receiver''; + end if; + + -- ---- + -- Check that the origin and provider of the set are remote + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_sub_set; + if not found then + raise exception ''Slony-I: set % not found'', p_sub_set; + end if; + if v_set_origin = p_sub_receiver then + raise exception + ''Slony-I: set origin and receiver cannot be identical''; + end if; + if p_sub_receiver = p_sub_provider then + raise exception + ''Slony-I: set provider and receiver cannot be identical''; + end if; + + -- ---- + -- Call the internal procedure to store the subscription + -- ---- + perform @NAMESPACE@.subscribeSet_int(p_sub_set, p_sub_provider, + p_sub_receiver, p_sub_forward); + + -- ---- + -- Create the SUBSCRIBE_SET event + -- ---- + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''SUBSCRIBE_SET'', + p_sub_set, p_sub_provider, p_sub_receiver, + case p_sub_forward when true then ''t'' else ''f'' end); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION subscribeSet_int (sub_set, sub_provider, sub_receiver, sub_forward) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.subscribeSet_int (int4, int4, int4, bool) +returns int4 +as ' +declare + p_sub_set alias for $1; + p_sub_provider alias for $2; + p_sub_receiver alias for $3; + p_sub_forward alias for $4; + v_set_origin int4; + v_sub_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Provider change is only allowed for active sets + -- ---- + if p_sub_receiver = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + select sub_active into v_sub_row from @NAMESPACE@.sl_subscribe + where sub_set = p_sub_set + and sub_receiver = p_sub_receiver; + if found then + if not v_sub_row.sub_active then + raise exception ''Slony-I: set % is not active, cannot change provider'', + p_sub_set; + end if; + end if; + end if; + + -- ---- + -- Try to change provider and/or forward for an existing subscription + -- ---- + update @NAMESPACE@.sl_subscribe + set sub_provider = p_sub_provider, + sub_forward = p_sub_forward + where sub_set = p_sub_set + and sub_receiver = p_sub_receiver; + if found then + return p_sub_set; + end if; + + -- ---- + -- Not found, insert a new one + -- ---- + if not exists (select true from @NAMESPACE@.sl_path + where pa_server = p_sub_provider + and pa_client = p_sub_receiver) + then + insert into @NAMESPACE@.sl_path + (pa_server, pa_client, pa_conninfo, pa_connretry) + values + (p_sub_provider, p_sub_receiver, + '''', 10); + end if; + insert into @NAMESPACE@.sl_subscribe + (sub_set, sub_provider, sub_receiver, sub_forward, sub_active) + values (p_sub_set, p_sub_provider, p_sub_receiver, + p_sub_forward, false); + + -- ---- + -- If the set origin is here, then enable the subscription + -- ---- + select set_origin into v_set_origin + from @NAMESPACE@.sl_set + where set_id = p_sub_set; + if not found then + raise exception ''Slony-I: set % not found'', p_sub_set; + end if; + + if v_set_origin = @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + perform @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''ENABLE_SUBSCRIPTION'', + p_sub_set, p_sub_provider, p_sub_receiver, + case p_sub_forward when true then ''t'' else ''f'' end); + perform @NAMESPACE@.enableSubscription(p_sub_set, + p_sub_provider, p_sub_receiver); + end if; + + return p_sub_set; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION unsubscribeSet (sub_set, sub_receiver) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.unsubscribeSet (int4, int4) +returns bigint +as ' +declare + p_sub_set alias for $1; + p_sub_receiver alias for $2; + v_tab_row record; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Check that this is called on the receiver node + -- ---- + if p_sub_receiver != @NAMESPACE@.getLocalNodeId(''_@CLUSTERNAME@'') then + raise exception ''Slony-I: unsubscribeSet() must be called on receiver''; + end if; + + -- ---- + -- Check that this does not break any chains + -- ---- + if exists (select true from @NAMESPACE@.sl_subscribe + where sub_set = p_sub_set + and sub_provider = p_sub_receiver) + then + raise exception ''Slony-I: Cannot unsubscibe set % while being provider'', + p_sub_set; + end if; + + -- ---- + -- Restore all tables original triggers and rules and remove + -- our replication stuff. + -- ---- + for v_tab_row in select tab_id from @NAMESPACE@.sl_table + where tab_set = p_sub_set + order by tab_id + loop + perform @NAMESPACE@.alterTableRestore(v_tab_row.tab_id); + perform @NAMESPACE@.tableDropKey(v_tab_row.tab_id); + end loop; + + -- ---- + -- Remove the setsync status. This will also cause the + -- worker thread to ignore the set and stop replicating + -- right now. + -- ---- + delete from @NAMESPACE@.sl_setsync + where ssy_setid = p_sub_set; + + -- ---- + -- Remove all sl_table and sl_sequence entries for this set. + -- Should we ever subscribe again, the initial data + -- copy process will create new ones. + -- ---- + delete from @NAMESPACE@.sl_table + where tab_set = p_sub_set; + delete from @NAMESPACE@.sl_sequence + where seq_set = p_sub_set; + + -- ---- + -- Call the internal procedure to drop the subscription + -- ---- + perform @NAMESPACE@.unsubscribeSet_int(p_sub_set, p_sub_receiver); + + -- ---- + -- Create the UNSUBSCRIBE_SET event + -- ---- + return @NAMESPACE@.createEvent(''_@CLUSTERNAME@'', ''UNSUBSCRIBE_SET'', + p_sub_set, p_sub_receiver); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION unsubscribeSet_int (sub_set, sub_receiver) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.unsubscribeSet_int (int4, int4) +returns int4 +as ' +declare + p_sub_set alias for $1; + p_sub_receiver alias for $2; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- All the real work is done before event generation on the + -- subscriber. + -- ---- + delete from @NAMESPACE@.sl_subscribe + where sub_set = p_sub_set + and sub_receiver = p_sub_receiver; + + return p_sub_set; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION enableSubscription (sub_set, sub_provider, sub_receiver) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.enableSubscription (int4, int4, int4) +returns int4 +as ' +declare + p_sub_set alias for $1; + p_sub_provider alias for $2; + p_sub_receiver alias for $3; +begin + return @NAMESPACE@.enableSubscription_int (p_sub_set, + p_sub_provider, p_sub_receiver); +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION enableSubscription_int (sub_set, sub_provider, sub_receiver) +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.enableSubscription_int (int4, int4, int4) +returns int4 +as ' +declare + p_sub_set alias for $1; + p_sub_provider alias for $2; + p_sub_receiver alias for $3; + v_n int4; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- The real work is done in the replication engine. All + -- we have to do here is remembering that it happened. + -- ---- + update @NAMESPACE@.sl_subscribe + set sub_active = ''t'' + where sub_set = p_sub_set + and sub_receiver = p_sub_receiver; + get diagnostics v_n = row_count; + if v_n = 0 then + insert into @NAMESPACE@.sl_subscribe + (sub_set, sub_provider, sub_receiver, + sub_forward, sub_active) + values + (p_sub_set, p_sub_provider, p_sub_receiver, + false, true); + end if; + + return p_sub_set; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION forwardConfirm () +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.forwardConfirm (int4, int4, int8, timestamp) +returns bigint +as ' +declare + p_con_origin alias for $1; + p_con_received alias for $2; + p_con_seqno alias for $3; + p_con_timestamp alias for $4; + v_max_seqno bigint; +begin + select into v_max_seqno coalesce(max(con_seqno), 0) + from @NAMESPACE@.sl_confirm + where con_origin = p_con_origin + and con_received = p_con_received; + if v_max_seqno < p_con_seqno then + insert into @NAMESPACE@.sl_confirm + (con_origin, con_received, con_seqno, con_timestamp) + values (p_con_origin, p_con_received, p_con_seqno, + p_con_timestamp); + notify "_@CLUSTERNAME@_Confirm"; + v_max_seqno = p_con_seqno; + end if; + + return v_max_seqno; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION cleanupEvent () +-- +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.cleanupEvent () +returns int4 +as ' +declare + v_max_row record; + v_min_row record; + v_max_sync int8; +begin + -- ---- + -- First remove all but the oldest confirm row per origin,receiver pair + -- ---- + delete from @NAMESPACE@.sl_confirm + where con_origin not in (select no_id from @NAMESPACE@.sl_node); + delete from @NAMESPACE@.sl_confirm + where con_received not in (select no_id from @NAMESPACE@.sl_node); + -- ---- + -- Next remove all but the oldest confirm row per origin,receiver pair. + -- Ignore confirmations that are younger than 10 minutes. We currently + -- have an not confirmed suspicion that a possibly lost transaction due + -- to a server crash might have been visible to another session, and + -- that this led to log data that is needed again got removed. + -- ---- + for v_max_row in select con_origin, con_received, max(con_seqno) as con_seqno + from @NAMESPACE@.sl_confirm + where con_timestamp < (CURRENT_TIMESTAMP - ''10 min''::interval) + group by con_origin, con_received + loop + delete from @NAMESPACE@.sl_confirm + where con_origin = v_max_row.con_origin + and con_received = v_max_row.con_received + and con_seqno < v_max_row.con_seqno; + end loop; + + -- ---- + -- Then remove all events that are confirmed by all nodes in the + -- whole cluster up to the last SYNC + -- ---- + for v_min_row in select con_origin, min(con_seqno) as con_seqno + from @NAMESPACE@.sl_confirm + group by con_origin + loop + select coalesce(max(ev_seqno), 0) into v_max_sync + from @NAMESPACE@.sl_event + where ev_origin = v_min_row.con_origin + and ev_seqno <= v_min_row.con_seqno + and ev_type = ''SYNC''; + if v_max_sync > 0 then + delete from @NAMESPACE@.sl_event + where ev_origin = v_min_row.con_origin + and ev_seqno < v_max_sync; + end if; + end loop; + + return 0; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION tableAddKey (tab_fqname) +-- +-- If the specified table does not have a column +-- "_Slony-I__rowID", then add it as a bigint +-- with default nextval('"_".sl_rowid_seq'). +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.tableAddKey(text) returns text +as ' +declare + p_tab_fqname alias for $1; + v_attkind text default ''''; + v_attrow record; + v_have_serial bool default ''f''; +begin + -- + -- Loop over the attributes of this relation + -- and add a "v" for every user column, and a "k" + -- if we find the Slony-I special serial column. + -- + for v_attrow in select PGA.attnum, PGA.attname + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_attribute PGA + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace + and PGA.attrelid = PGC.oid + and not PGA.attisdropped + and PGA.attnum > 0 + order by attnum + loop + if v_attrow.attname = ''_Slony-I_@CLUSTERNAME@_rowID'' then + v_attkind := v_attkind || ''k''; + v_have_serial := ''t''; + else + v_attkind := v_attkind || ''v''; + end if; + end loop; + + -- + -- A table must have at least one attribute, so not finding + -- anything means the table does not exist. + -- + if not found then + raise exception ''Slony-I: table % not found'', p_tab_fqname; + end if; + + -- + -- If it does not have the special serial column, we + -- have to add it. This will be only half way done. + -- The function to add the table to the set must finish + -- these definitions with NOT NULL and UNIQUE after + -- updating all existing rows. + -- + if not v_have_serial then + execute ''lock table '' || p_tab_fqname || + '' in access exclusive mode''; + execute ''alter table only '' || p_tab_fqname || + '' add column "_Slony-I_@CLUSTERNAME@_rowID" bigint;''; + execute ''alter table only '' || p_tab_fqname || + '' alter column "_Slony-I_@CLUSTERNAME@_rowID" '' || + '' set default "pg_catalog".nextval(''''@NAMESPACE@.sl_rowid_seq'''');''; + + v_attkind := v_attkind || ''k''; + end if; + + -- + -- Return the resulting Slony-I attkind + -- + return v_attkind; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION tableDropKey (tab_id) +-- +-- If the specified table does not have a column +-- "_Slony-I__rowID", then add it as a bigint +-- with default nextval('"_".sl_rowid_seq'). +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.tableDropKey(int4) returns int4 +as ' +declare + p_tab_id alias for $1; + v_tab_fqname text; + v_tab_oid oid; +begin + -- ---- + -- Grab the central configuration lock + -- ---- + lock table @NAMESPACE@.sl_config_lock; + + -- ---- + -- Construct the tables fully qualified name and get its oid + -- ---- + select "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname), + PGC.oid into v_tab_fqname, v_tab_oid + from @NAMESPACE@.sl_table T, + "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN + where T.tab_id = p_tab_id + and T.tab_reloid = PGC.oid + and PGC.relnamespace = PGN.oid; + if not found then + raise exception ''Slony-I: table with ID % not found'', p_tab_id; + end if; + + -- ---- + -- Drop the special serial ID column if the table has it + -- ---- + if exists (select true from "pg_catalog".pg_attribute + where attrelid = v_tab_oid + and attname = ''_Slony-I_@CLUSTERNAME@_rowID'') + then + execute ''lock table '' || v_tab_fqname || + '' in access exclusive mode''; + execute ''alter table '' || v_tab_fqname || + '' drop column "_Slony-I_@CLUSTERNAME@_rowID"''; + end if; + + return p_tab_id; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION determineIdxnameUnique (tab_fqname, indexname) +-- +-- Given a tablename, check that a unique index exists or return +-- the tables primary key index name. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.determineIdxnameUnique(text, name) returns name +as ' +declare + p_tab_fqname alias for $1; + p_idx_name alias for $2; + v_idxrow record; +begin + -- + -- Lookup the tables primary key or the specified unique index + -- + if p_idx_name isnull then + select PGXC.relname + into v_idxrow + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_index PGX, + "pg_catalog".pg_class PGXC + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace + and PGX.indrelid = PGC.oid + and PGX.indexrelid = PGXC.oid + and PGX.indisprimary; + if not found then + raise exception ''Slony-I: table % has no primary key'', + p_tab_fqname; + end if; + else + select PGXC.relname + into v_idxrow + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_index PGX, + "pg_catalog".pg_class PGXC + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace + and PGX.indrelid = PGC.oid + and PGX.indexrelid = PGXC.oid + and PGX.indisunique + and PGXC.relname = p_idx_name; + if not found then + raise exception ''Slony-I: table % has no unique index %'', + p_tab_fqname, p_idx_name; + end if; + end if; + + -- + -- Return the found index name + -- + return v_idxrow.relname; +end; +' language plpgsql called on null input; + + +-- ---------------------------------------------------------------------- +-- FUNCTION determineIdxnameSerial (tab_fqname) +-- +-- Given a tablename, construct the serial columns index name +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.determineIdxnameSerial(text) returns name +as ' +declare + p_tab_fqname alias for $1; + v_row record; +begin + -- + -- Lookup the table name alone + -- + select PGC.relname + into v_row + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace; + if not found then + raise exception ''Slony-I: table % not found'', + p_tab_fqname; + end if; + + -- + -- Return the found index name + -- + return v_row.relname || ''__Slony-I_@CLUSTERNAME@_rowID_key''; +end; +' language plpgsql called on null input; + + +-- ---------------------------------------------------------------------- +-- FUNCTION determineAttKindUnique (tab_fqname, indexname) +-- +-- Given a tablename, return the Slony-I specific attkind (used for +-- the log trigger) of the table. Use the specified unique index or +-- the primary key (if indexname is NULL). +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.determineAttkindUnique(text, name) returns text +as ' +declare + p_tab_fqname alias for $1; + p_idx_name alias for $2; + v_idxrow record; + v_attrow record; + v_i integer; + v_attno int2; + v_attkind text default ''''; + v_attfound bool; +begin + -- + -- Lookup the tables primary key or the specified unique index + -- + if p_idx_name isnull then + raise exception ''Slony-I: index name must be specified''; + else + select PGXC.relname, PGX.indexrelid, PGX.indkey + into v_idxrow + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_index PGX, + "pg_catalog".pg_class PGXC + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace + and PGX.indrelid = PGC.oid + and PGX.indexrelid = PGXC.oid + and PGX.indisunique + and PGXC.relname = p_idx_name; + if not found then + raise exception ''Slony-I: table % has no unique index %'', + p_tab_fqname, p_idx_name; + end if; + end if; + + -- + -- Loop over the tables attributes and check if they are + -- index attributes. If so, add a "k" to the return value, + -- otherwise add a "v". + -- + for v_attrow in select PGA.attnum, PGA.attname + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_attribute PGA + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace + and PGA.attrelid = PGC.oid + and not PGA.attisdropped + and PGA.attnum > 0 + order by attnum + loop + v_attfound = ''f''; + + v_i := 0; + loop + select indkey[v_i] into v_attno from "pg_catalog".pg_index + where indexrelid = v_idxrow.indexrelid; + if v_attno = 0 then + exit; + end if; + if v_attrow.attnum = v_attno then + v_attfound = ''t''; + exit; + end if; + v_i := v_i + 1; + end loop; + + if v_attfound then + v_attkind := v_attkind || ''k''; + else + v_attkind := v_attkind || ''v''; + end if; + end loop; + + -- + -- Return the resulting attkind + -- + return v_attkind; +end; +' language plpgsql called on null input; + + +-- ---------------------------------------------------------------------- +-- FUNCTION determineAttKindSerial (tab_fqname) +-- +-- A table was that was specified without a primary key is added +-- to the replication. Assume that tableAddKey() was called before +-- and finish the creation of the serial column. The return an +-- attkind according to that. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.determineAttkindSerial(text) +returns text +as ' +declare + p_tab_fqname alias for $1; + v_attkind text default ''''; + v_attrow record; + v_have_serial bool default ''f''; +begin + -- + -- Loop over the attributes of this relation + -- and add a "v" for every user column, and a "k" + -- if we find the Slony-I special serial column. + -- + for v_attrow in select PGA.attnum, PGA.attname + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_attribute PGA + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGN.oid = PGC.relnamespace + and PGA.attrelid = PGC.oid + and not PGA.attisdropped + and PGA.attnum > 0 + order by attnum + loop + if v_attrow.attname = ''_Slony-I_@CLUSTERNAME@_rowID'' then + v_attkind := v_attkind || ''k''; + v_have_serial := ''t''; + else + v_attkind := v_attkind || ''v''; + end if; + end loop; + + -- + -- A table must have at least one attribute, so not finding + -- anything means the table does not exist. + -- + if not found then + raise exception ''Slony-I: table % not found'', p_tab_fqname; + end if; + + -- + -- If it does not have the special serial column, we + -- should not have been called in the first place. + -- + if not v_have_serial then + raise exception ''Slony-I: table % does not have the serial key'', + p_tab_fqname; + end if; + + execute ''update '' || p_tab_fqname || + '' set "_Slony-I_@CLUSTERNAME@_rowID" ='' || + '' "pg_catalog".nextval(''''@NAMESPACE@.sl_rowid_seq'''');''; + execute ''alter table only '' || p_tab_fqname || + '' add unique ("_Slony-I_@CLUSTERNAME@_rowID");''; + execute ''alter table only '' || p_tab_fqname || + '' alter column "_Slony-I_@CLUSTERNAME@_rowID" '' || + '' set not null;''; + + -- + -- Return the resulting Slony-I attkind + -- + return v_attkind; +end; +' language plpgsql; + + +-- ---------------------------------------------------------------------- +-- FUNCTION tableHasSerialKey (tab_fqname) +-- +-- Checks if a table has our special serial key column that is +-- used if the table has no natural unique constraint. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.tableHasSerialKey(text) +returns bool +as ' +declare + p_tab_fqname alias for $1; + v_attnum int2; +begin + select PGA.attnum into v_attnum + from "pg_catalog".pg_class PGC, + "pg_catalog".pg_namespace PGN, + "pg_catalog".pg_attribute PGA + where "pg_catalog".quote_ident(PGN.nspname) || ''.'' || + "pg_catalog".quote_ident(PGC.relname) = p_tab_fqname + and PGC.relnamespace = PGN.oid + and PGA.attrelid = PGC.oid + and PGA.attname = ''_Slony-I_@CLUSTERNAME@_rowID'' + and not PGA.attisdropped; + return found; +end; +' language plpgsql; + + +-- ********************************************************************** +-- * Views +-- ********************************************************************** + + +-- ---------------------------------------------------------------------- +-- VIEW sl_status +-- +-- This view shows the local nodes last event sequence number +-- and how far all remote nodes have processed events. +-- ---------------------------------------------------------------------- +create or replace view @NAMESPACE@.sl_status as select + E.ev_origin as st_origin, + C.con_received as st_received, + E.ev_seqno as st_last_event, + E.ev_timestamp as st_last_event_ts, + C.con_seqno as st_last_received, + C.con_timestamp as st_last_received_ts, + CE.ev_timestamp as st_last_received_event_ts, + E.ev_seqno - C.con_seqno as st_lag_num_events, + current_timestamp - CE.ev_timestamp as st_lag_time + from @NAMESPACE@.sl_event E, @NAMESPACE@.sl_confirm C, + @NAMESPACE@.sl_event CE + where E.ev_origin = C.con_origin + and CE.ev_origin = E.ev_origin + and CE.ev_seqno = C.con_seqno + and (E.ev_origin, E.ev_seqno) in + (select ev_origin, max(ev_seqno) + from @NAMESPACE@.sl_event + where ev_origin = @NAMESPACE@.getLocalNodeId('_@CLUSTERNAME@') + group by 1 + ) + and (C.con_origin, C.con_received, C.con_seqno) in + (select con_origin, con_received, max(con_seqno) + from @NAMESPACE@.sl_confirm + where con_origin = @NAMESPACE@.getLocalNodeId('_@CLUSTERNAME@') + group by 1, 2 + ); + + diff --git a/sql/plugins/slony1_funcs.v73.sql b/sql/plugins/slony1_funcs.v73.sql new file mode 100755 index 00000000..97580e78 --- /dev/null +++ b/sql/plugins/slony1_funcs.v73.sql @@ -0,0 +1,28 @@ +-- ---------------------------------------------------------------------- +-- slony1_funcs.v73.sql +-- +-- Version 7.3 specific part of the replication support functions. +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: slony1_funcs.v73.sql,v 1.2 2005/06/16 14:40:15 chriskl Exp $ +-- ---------------------------------------------------------------------- + +-- ---------------------------------------------------------------------- +-- FUNCTION truncateTable(tab_fqname) +-- +-- Remove all content from a table before the subscription +-- content is loaded via COPY. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.truncateTable(text) +returns int4 +as ' +declare + p_tab_fqname alias for $1; +begin + execute ''delete from only '' || p_tab_fqname; + return 1; +end; +' language plpgsql; + diff --git a/sql/plugins/slony1_funcs.v74.sql b/sql/plugins/slony1_funcs.v74.sql new file mode 100755 index 00000000..489d9a6c --- /dev/null +++ b/sql/plugins/slony1_funcs.v74.sql @@ -0,0 +1,28 @@ +-- ---------------------------------------------------------------------- +-- slony1_funcs.v74.sql +-- +-- Version 7.4 specific part of the replication support functions. +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: slony1_funcs.v74.sql,v 1.2 2005/06/16 14:40:15 chriskl Exp $ +-- ---------------------------------------------------------------------- + +-- ---------------------------------------------------------------------- +-- FUNCTION truncateTable(tab_fqname) +-- +-- Remove all content from a table before the subscription +-- content is loaded via COPY. +-- ---------------------------------------------------------------------- +create or replace function @NAMESPACE@.truncateTable(text) +returns int4 +as ' +declare + p_tab_fqname alias for $1; +begin + execute ''delete from only '' || p_tab_fqname; + return 1; +end; +' language plpgsql; + diff --git a/sql/plugins/xxid.v73.sql b/sql/plugins/xxid.v73.sql new file mode 100755 index 00000000..20cd6d7c --- /dev/null +++ b/sql/plugins/xxid.v73.sql @@ -0,0 +1,186 @@ +-- ---------- +-- xxid.v73.sql.in +-- +-- SQL script for loading the transaction ID compatible datatype +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: xxid.v73.sql,v 1.2 2005/06/16 14:40:15 chriskl Exp $ +-- ---------- + +-- +-- Type specific input and output functions +-- +CREATE FUNCTION @NAMESPACE@."xxidin"(cstring) RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_xxidin' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidout"(@NAMESPACE@."xxid") RETURNS cstring + AS '$libdir/xxid', '_Slony_I_xxidout' + LANGUAGE C; + + +-- +-- The data type itself +-- +CREATE TYPE @NAMESPACE@."xxid" ( + INPUT = @NAMESPACE@."xxidin", + OUTPUT = @NAMESPACE@."xxidout", + EXTERNALLENGTH = 12, + INTERNALLENGTH = 4, + PASSEDBYVALUE, + ALIGNMENT = int4 +); + + +-- +-- Since our xxid type has special cases for values 0-3, it +-- in fact IS xid, so allow implicit type casting to and from. +-- +CREATE CAST (xid AS @NAMESPACE@.xxid) + WITHOUT FUNCTION AS IMPLICIT; +CREATE CAST (@NAMESPACE@.xxid AS xid) + WITHOUT FUNCTION AS IMPLICIT; + + +-- +-- Comparision functions for the new datatype +-- +CREATE FUNCTION @NAMESPACE@."xxideq"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxideq' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidne"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidne' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidlt"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidlt' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidle"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidle' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidgt"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidgt' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidge"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidge' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."btxxidcmp"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS int4 + AS '$libdir/xxid', '_Slony_I_btxxidcmp' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@.getCurrentXid() RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_getCurrentXid' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@.getMinXid() RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_getMinXid' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@.getMaxXid() RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_getMaxXid' + LANGUAGE C; + + +-- +-- Operators on these comparision functions +-- +CREATE OPERATOR < ( + PROCEDURE = @NAMESPACE@."xxidlt", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = >, NEGATOR = >=, + RESTRICT = scalarltsel, JOIN = scalarltjoinsel +); +CREATE OPERATOR = ( + PROCEDURE = @NAMESPACE@."xxideq", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = =, NEGATOR = <>, + RESTRICT = eqsel, JOIN = eqjoinsel, + SORT1 = <, SORT2 = <, + HASHES +); +CREATE OPERATOR <> ( + PROCEDURE = @NAMESPACE@."xxidne", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = <>, NEGATOR = =, + RESTRICT = neqsel, JOIN = neqjoinsel +); +CREATE OPERATOR > ( + PROCEDURE = @NAMESPACE@."xxidgt", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = <, NEGATOR = <=, + RESTRICT = scalargtsel, JOIN = scalargtjoinsel +); +CREATE OPERATOR <= ( + PROCEDURE = @NAMESPACE@."xxidle", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = >=, NEGATOR = >, + RESTRICT = scalarltsel, JOIN = scalarltjoinsel +); +CREATE OPERATOR >= ( + PROCEDURE = @NAMESPACE@."xxidge", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = <=, NEGATOR = <, + RESTRICT = scalargtsel, JOIN = scalargtjoinsel +); + + +-- +-- Finally the default operator class so that we can use our +-- new data type in btree indexes. +-- +CREATE OPERATOR CLASS @NAMESPACE@."xxid_ops" + DEFAULT FOR TYPE @NAMESPACE@."xxid" USING btree AS + OPERATOR 1 < (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 2 <= (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 3 = (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 4 >= (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 5 > (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + FUNCTION 1 @NAMESPACE@."btxxidcmp" (@NAMESPACE@."xxid", @NAMESPACE@."xxid"); + + +-- +-- A special transaction snapshot data type for faster visibility checks +-- +CREATE FUNCTION @NAMESPACE@."xxid_snapshot_in"(cstring) +RETURNS @NAMESPACE@."xxid_snapshot" + AS '$libdir/xxid', '_Slony_I_xxid_snapshot_in' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxid_snapshot_out"(@NAMESPACE@."xxid_snapshot") +RETURNS cstring + AS '$libdir/xxid', '_Slony_I_xxid_snapshot_out' + LANGUAGE C; + + +-- +-- The data type itself +-- +CREATE TYPE @NAMESPACE@."xxid_snapshot" ( + INPUT = @NAMESPACE@."xxid_snapshot_in", + OUTPUT = @NAMESPACE@."xxid_snapshot_out", + INTERNALLENGTH = variable, + ALIGNMENT = int4 +); + + +-- +-- Special comparision functions used by the remote worker +-- for sync chunk selection +-- +CREATE FUNCTION @NAMESPACE@."xxid_lt_snapshot"( + @NAMESPACE@."xxid", + @NAMESPACE@."xxid_snapshot") +RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxid_lt_snapshot' + LANGUAGE C; + +CREATE FUNCTION @NAMESPACE@."xxid_ge_snapshot"( + @NAMESPACE@."xxid", + @NAMESPACE@."xxid_snapshot") +RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxid_ge_snapshot' + LANGUAGE C; + + diff --git a/sql/plugins/xxid.v74.sql b/sql/plugins/xxid.v74.sql new file mode 100755 index 00000000..15632566 --- /dev/null +++ b/sql/plugins/xxid.v74.sql @@ -0,0 +1,186 @@ +-- ---------- +-- xxid.v73.sql.in +-- +-- SQL script for loading the transaction ID compatible datatype +-- +-- Copyright (c) 2003-2004, PostgreSQL Global Development Group +-- Author: Jan Wieck, Afilias USA INC. +-- +-- $Id: xxid.v74.sql,v 1.2 2005/06/16 14:40:15 chriskl Exp $ +-- ---------- + +-- +-- Type specific input and output functions +-- +CREATE FUNCTION @NAMESPACE@."xxidin"(cstring) RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_xxidin' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidout"(@NAMESPACE@."xxid") RETURNS cstring + AS '$libdir/xxid', '_Slony_I_xxidout' + LANGUAGE C; + + +-- +-- The data type itself +-- +CREATE TYPE @NAMESPACE@."xxid" ( + INPUT = @NAMESPACE@."xxidin", + OUTPUT = @NAMESPACE@."xxidout", + EXTERNALLENGTH = 12, + INTERNALLENGTH = 4, + PASSEDBYVALUE, + ALIGNMENT = int4 +); + + +-- +-- Since our xxid type has special cases for values 0-3, it +-- in fact IS xid, so allow implicit type casting to and from. +-- +CREATE CAST (xid AS @NAMESPACE@.xxid) + WITHOUT FUNCTION AS IMPLICIT; +CREATE CAST (@NAMESPACE@.xxid AS xid) + WITHOUT FUNCTION AS IMPLICIT; + + +-- +-- Comparision functions for the new datatype +-- +CREATE FUNCTION @NAMESPACE@."xxideq"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxideq' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidne"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidne' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidlt"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidlt' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidle"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidle' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidgt"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidgt' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxidge"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxidge' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."btxxidcmp"(@NAMESPACE@."xxid", @NAMESPACE@."xxid") RETURNS int4 + AS '$libdir/xxid', '_Slony_I_btxxidcmp' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@.getCurrentXid() RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_getCurrentXid' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@.getMinXid() RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_getMinXid' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@.getMaxXid() RETURNS @NAMESPACE@."xxid" + AS '$libdir/xxid', '_Slony_I_getMaxXid' + LANGUAGE C; + + +-- +-- Operators on these comparision functions +-- +CREATE OPERATOR < ( + PROCEDURE = @NAMESPACE@."xxidlt", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = >, NEGATOR = >=, + RESTRICT = scalarltsel, JOIN = scalarltjoinsel +); +CREATE OPERATOR = ( + PROCEDURE = @NAMESPACE@."xxideq", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = =, NEGATOR = <>, + RESTRICT = eqsel, JOIN = eqjoinsel, + SORT1 = <, SORT2 = <, + HASHES +); +CREATE OPERATOR <> ( + PROCEDURE = @NAMESPACE@."xxidne", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = <>, NEGATOR = =, + RESTRICT = neqsel, JOIN = neqjoinsel +); +CREATE OPERATOR > ( + PROCEDURE = @NAMESPACE@."xxidgt", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = <, NEGATOR = <=, + RESTRICT = scalargtsel, JOIN = scalargtjoinsel +); +CREATE OPERATOR <= ( + PROCEDURE = @NAMESPACE@."xxidle", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = >=, NEGATOR = >, + RESTRICT = scalarltsel, JOIN = scalarltjoinsel +); +CREATE OPERATOR >= ( + PROCEDURE = @NAMESPACE@."xxidge", + LEFTARG = @NAMESPACE@."xxid", + RIGHTARG = @NAMESPACE@."xxid", + COMMUTATOR = <=, NEGATOR = <, + RESTRICT = scalargtsel, JOIN = scalargtjoinsel +); + + +-- +-- Finally the default operator class so that we can use our +-- new data type in btree indexes. +-- +CREATE OPERATOR CLASS @NAMESPACE@."xxid_ops" + DEFAULT FOR TYPE @NAMESPACE@."xxid" USING btree AS + OPERATOR 1 < (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 2 <= (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 3 = (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 4 >= (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + OPERATOR 5 > (@NAMESPACE@."xxid", @NAMESPACE@."xxid"), + FUNCTION 1 @NAMESPACE@."btxxidcmp" (@NAMESPACE@."xxid", @NAMESPACE@."xxid"); + + +-- +-- A special transaction snapshot data type for faster visibility checks +-- +CREATE FUNCTION @NAMESPACE@."xxid_snapshot_in"(cstring) +RETURNS @NAMESPACE@."xxid_snapshot" + AS '$libdir/xxid', '_Slony_I_xxid_snapshot_in' + LANGUAGE C; +CREATE FUNCTION @NAMESPACE@."xxid_snapshot_out"(@NAMESPACE@."xxid_snapshot") +RETURNS cstring + AS '$libdir/xxid', '_Slony_I_xxid_snapshot_out' + LANGUAGE C; + + +-- +-- The data type itself +-- +CREATE TYPE @NAMESPACE@."xxid_snapshot" ( + INPUT = @NAMESPACE@."xxid_snapshot_in", + OUTPUT = @NAMESPACE@."xxid_snapshot_out", + INTERNALLENGTH = variable, + ALIGNMENT = int4 +); + + +-- +-- Special comparision functions used by the remote worker +-- for sync chunk selection +-- +CREATE FUNCTION @NAMESPACE@."xxid_lt_snapshot"( + @NAMESPACE@."xxid", + @NAMESPACE@."xxid_snapshot") +RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxid_lt_snapshot' + LANGUAGE C; + +CREATE FUNCTION @NAMESPACE@."xxid_ge_snapshot"( + @NAMESPACE@."xxid", + @NAMESPACE@."xxid_snapshot") +RETURNS boolean + AS '$libdir/xxid', '_Slony_I_xxid_ge_snapshot' + LANGUAGE C; + + -- 2.39.5