bootstrap.sql

java开源的企业总线.xmlBlaster

-- ---------------------------------------------------------------------------- 
-- Written by Michele Laghi ([e-mail]) 2005-08-09                    
--                                                                              
-- Some Comments:                                                               
--  The effect of triggers has been checked. An open issue is how to determine  
--  wether an action has been caused by a direct operation of the user (primary 
--  action) or if it is a reaction to that as an operation performed by a       
--  trigger (reaction).                                                         
--                                                                              
-- NOTES:                                                                       
-- Avoid the usage of TRUNCATE in the tables to be replicated, since the        
-- deletion seems not to detect such a change.                                  
-- ---------------------------------------------------------------------------- 


-- ---------------------------------------------------------------------------- 
-- Before that we need to manually register (in case this is not already done)  
-- the PL-SQL language hander:                                                  
-- Manual Installation of PL/pgSQL (you must have the rights to do so to make   
-- it simple be the user 'postgres'):                                           
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION plpgsql_call_handler()                            
       RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;             
CREATE OR REPLACE FUNCTION plpgsql_validator(oid)                            
       RETURNS void AS '$libdir/plpgsql' LANGUAGE C;                         
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql                                   
       HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;             
-- FLUSH (dropped ${replPrefix}cols_view)                                       

-- note that what written here is only needed once (this is why we have         
-- commented out it)                                                            
-- ---------------------------------------------------------------------------- 

-- ---------------------------------------------------------------------------- 
-- WE FIRST CREATE THE TABLE HOLDING A LIST OF ALL TABLES TO BE REPLICATED      
-- ---------------------------------------------------------------------------- 

DROP VIEW ${replPrefix}cols_view;
-- FLUSH (dropped ${replPrefix}cols_view)                                       
DROP TABLE ${replPrefix}tables CASCADE;
-- FLUSH (dropped ${replPrefix}tables)                                          
DROP TABLE ${replPrefix}current_tables CASCADE;
-- FLUSH (dropped ${replPrefix}current_tables)                                  
DROP TABLE ${replPrefix}cols_table CASCADE;
-- FLUSH (dropped ${replPrefix}cols_table)                                      
DROP SEQUENCE ${replPrefix}seq;
-- FLUSH (dropped ${replPrefix}seq)                                             
DROP TABLE ${replPrefix}items CASCADE;
-- FLUSH (dropped ${replPrefix}items)                                           

-- ---------------------------------------------------------------------------- 
-- This table contains the list of tables to watch.                             
-- tablename is the name of the table to watch                                  
-- actions is the flag being a combination of I (indicating it acts on inserts),
-- D (for deletes) and U (for updates).                                         
-- it will only watch for initial replication.                                  
-- ---------------------------------------------------------------------------- 

CREATE TABLE ${replPrefix}tables(catalogname VARCHAR(30), schemaname 
                         VARCHAR(30), tablename VARCHAR(30), 
			 actions CHAR(3), status VARCHAR(10), 
			 repl_key INTEGER, trigger_name VARCHAR(30), 
			 debug INTEGER, 
			 PRIMARY KEY(catalogname, schemaname, tablename));

-- ---------------------------------------------------------------------------- 
-- create the ${replPrefix}current_tables as a placeholder for the current      
-- tables (this is used to detect a CREATE TABLE and a DROP TABLE.              
-- ---------------------------------------------------------------------------- 

CREATE TABLE ${replPrefix}current_tables AS SELECT relname 
       FROM pg_statio_user_tables WHERE relname IN (SELECT tablename 
       FROM ${replPrefix}tables);

-- ---------------------------------------------------------------------------- 
-- A Difference between these two means that an ALTER operation has been        
-- invoked.                                                                     
-- ---------------------------------------------------------------------------- 

CREATE VIEW ${replPrefix}cols_view AS SELECT attname,(SELECT relname 
       FROM pg_class WHERE oid=attrelid) AS owner, atttypid, attlen, attnotnull, 
       attnum FROM pg_attribute WHERE attnum > 0 AND (SELECT relname FROM 
       pg_class WHERE oid=attrelid) IN (SELECT tablename 
       FROM ${replPrefix}tables) ORDER BY owner, attnum;

CREATE TABLE ${replPrefix}cols_table AS SELECT attname,(SELECT relname 
       FROM pg_class WHERE oid=attrelid) AS owner, atttypid, attlen, attnotnull, 
       attnum FROM pg_attribute WHERE attnum > 0 AND (SELECT relname 
       FROM pg_class WHERE oid=attrelid) IN (SELECT tablename 
       FROM ${replPrefix}tables) ORDER BY owner, attnum;

-- ---------------------------------------------------------------------------- 
-- Invoked to detect if a table has been altered.                               
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}is_altered(name text) 
       RETURNS BOOLEAN AS $${replPrefix}is_altered$
  DECLARE
     colVar RECORD;
     md5A TEXT;
     md5B TEXT;
     tmp  TEXT;
  BEGIN
   FOR colVar IN (SELECT * FROM ${replPrefix}cols_table WHERE owner=$1) LOOP
      md5A = colVar.attname;
      tmp = colVar.atttypid;
      md5A = md5A || tmp;
      tmp = colVar.attlen;
      md5A = md5A || tmp;
      tmp = colvar.attnotnull;
      md5A = md5A || tmp;
      tmp = colVar.attnum;
      md5A = md5A || tmp;
   END LOOP;
   FOR colVar IN (SELECT * FROM ${replPrefix}cols_view WHERE owner=$1) LOOP
      md5B = colVar.attname;
      tmp = colVar.atttypid;
      md5B = md5B || tmp;
      tmp = colVar.attlen;
      md5B = md5B || tmp;
      tmp = colvar.attnotnull;
      md5B = md5B || tmp;
      tmp = colVar.attnum;
      md5B = md5B || tmp;
   END LOOP;

   RETURN md5A != md5B;
END;
$${replPrefix}is_altered$ LANGUAGE 'plpgsql';


-- ---------------------------------------------------------------------------- 
-- We create the table which will be used for the outgoing replica messages and 
-- a sequence needed for a monotone increasing sequence for the primary key.    
-- In postgres this will implicitly create an index "repltest_pkey" for this    
-- table. The necessary generic lowlevel functions are created.                 
-- ---------------------------------------------------------------------------- 

CREATE SEQUENCE ${replPrefix}seq MINVALUE 1 MAXVALUE 1000000000 CYCLE;

CREATE TABLE ${replPrefix}items (repl_key INTEGER DEFAULT 
             nextval('${replPrefix}seq'), trans_key VARCHAR(30), 
	     dbId VARCHAR(30), tablename VARCHAR(30), guid VARCHAR(30), 
	     db_action VARCHAR(15), db_catalog VARCHAR(30),
	     db_schema VARCHAR(30), content TEXT, oldContent TEXT, 
	     version VARCHAR(10), PRIMARY KEY (repl_key));

-- ---------------------------------------------------------------------------- 
-- ${replPrefix}needs_prot (prot stands for protection) detects wether a        
-- protection to  BASE64 is needed or not in a text string.                     
-- returns an integer. If 1 it means CDATA protection will suffice, if 2 it     
-- means it needs BASE64.                                                       
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}needs_prot(content text) 
RETURNS INT AS $${replPrefix}needs_prot$
DECLARE
   pos INT;
BEGIN
   pos = position(']]>' IN content);
   IF POS > 0 THEN
      RETURN 2;
   END IF;
   pos = position('<' IN content);
   IF POS > 0 THEN 
      RETURN 1;
   END IF;
   pos = position('&' IN content);
   IF POS > 0 THEN 
      RETURN 1;
   END IF;
   RETURN 0;
END;
$${replPrefix}needs_prot$ LANGUAGE 'plpgsql';