bootstrap.sql

java开源的企业总线.xmlBlaster

-- ---------------------------------------------------------------------------- 
-- ${replPrefix}col2xml converts a column into a simple xml notation. The       
-- output of these functions follows the notation of the dbWatcher. More about  
-- that on                                                                      
-- http://www.xmlblaster.org/xmlBlaster/doc/requirements/contrib.dbwatcher.html 
--   name: the name of the column                                               
--   content the content of the column. If it is a blob                         
-- use ${replPrefix}col2xml_base64.                                             
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}col2xml(name text, content text) 
RETURNS text AS $${replPrefix}col2xml$
DECLARE pos INT;
        blobCont BYTEA;
BEGIN
   pos = ${replPrefix}needs_prot(content);
   IF pos = 0 THEN
      RETURN '<col name="' || name || '">' || content || '</col>';
   END IF;
   IF pos = 1 THEN 
      RETURN ${replPrefix}col2xml_cdata(name, content);
   END IF; 
   blobCont = content; -- needed for the conversion
   RETURN ${replPrefix}col2xml_base64(name, blobCont);
END;
$${replPrefix}col2xml$ LANGUAGE 'plpgsql';


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}col2xml_base64 converts a column into a simple xml notation     
-- where the                                                                    
-- content will be decoded to base64.                                           
--   name: the name of the column                                               
--   content the content of the column (must be bytea or compatible)            
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}col2xml_base64(name text, content bytea) 
RETURNS text AS $${replPrefix}col2xml_base64$
BEGIN 
   RETURN '<col name="' || name || '" encoding="base64">' ||  
           encode(content,'base64') || '</col>';
END;
$${replPrefix}col2xml_base64$ LANGUAGE 'plpgsql';


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}col2xml_cdata converts a column into a simple xml notation and  
-- wraps the content into a _cdata object.                                      
--   name: the name of the column                                               
--   content the content of the column. If it is a blob use                     
--${replPrefix}col2xml_base64                                                   
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}col2xml_cdata(name text, content text) 
RETURNS text AS $${replPrefix}col2xml_cdata$
BEGIN
   RETURN '<col name="' || name || '"><![CDATA[' || 
           content || ']]></col>';
END;
$${replPrefix}col2xml_cdata$ LANGUAGE 'plpgsql';


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}check_structure is used to check wether a table has been        
-- created, dropped or altered.                                                 
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}check_structure()
   RETURNS TEXT AS $${replPrefix}check_structure$
   DECLARE colVar  RECORD;
           counter INT4;
BEGIN
    counter = 0;
    FOR colVar IN (SELECT relname FROM ${replPrefix}current_tables WHERE 
               relname NOT IN (SELECT relname FROM pg_statio_user_tables WHERE 
	       relname IN (SELECT tablename FROM ${replPrefix}tables)))
    LOOP
      INSERT INTO ${replPrefix}items (trans_key, dbId, tablename, guid, 
                       db_action, db_catalog, db_schema, content, oldContent, 
		       version) values (CURRENT_TIMESTAMP, current_database(), 
		       colVar.relname, colVar.oid, 'DROP', NULL, 
		       current_schema(), NULL, NULL, '${replVersion}');
      counter = 1;
    END LOOP;					 
    FOR colVar IN (SELECT relname FROM pg_statio_user_tables WHERE relname 
	       IN (SELECT tablename FROM ${replPrefix}tables) AND (relname NOT 
	       IN (SELECT relname FROM ${replPrefix}current_tables)))
      LOOP
      INSERT INTO ${replPrefix}items (trans_key, dbId, tablename, guid, 
                       db_action, db_catalog, db_schema, content, oldContent, 
		       version) values (CURRENT_TIMESTAMP, 
		       current_database(), colVar.relname, colVar.oid, 
		       'CREATE', NULL, current_schema(), NULL, NULL, 
		       '${replVersion}');
      counter = 1;
    END LOOP;
    IF counter > 0 THEN
       TRUNCATE ${replPrefix}current_tables;
       FOR colVar IN (SELECT relname FROM pg_statio_user_tables WHERE relname 
                  IN (SELECT tablename FROM ${replPrefix}tables))
       LOOP 
         INSERT INTO ${replPrefix}current_tables (relname) 
	        VALUES (colVar.relname);
       END LOOP;
    --    the following would not work (probably because table is cached)         
    --    CREATE TABLE ${replPrefix}current_tables AS SELECT relname FROM         
    --    pg_statio_user_tables;                                                  
    END IF;
    -- now CREATE and DROP have been handled ...                                  
    -- now we must handle ALTER TABLE (or column)                                 
    counter = 0;
    FOR colVar IN (SELECT relname FROM ${replPrefix}current_tables) LOOP
       IF ${replPrefix}is_altered(colVar.relname) = 't' THEN
         INSERT INTO ${replPrefix}items (trans_key, dbId, tablename, guid, 
	               db_action, db_catalog, db_schema, content, oldContent, 
		       version) VALUES (CURRENT_TIMESTAMP, 
		       current_database(), colVar.relname, colVar.oid, 
		       'ALTER', NULL, current_schema(), NULL, NULL, 
		       '${replVersion}');
	 counter = 1;
       END IF;
    END LOOP;
    IF counter > 0 THEN
      TRUNCATE ${replPrefix}cols_table;
      FOR colVar IN (SELECT * FROM ${replPrefix}cols_view) LOOP
         INSERT INTO ${replPrefix}cols_table VALUES (colVar.attname, colVar.owner, 
	                                   colVar.atttypid, colVar.attlen, 
					   colVar.attnotnull, colVar.attnum);
      END LOOP;
    END IF;
    RETURN 'OK';
END;
$${replPrefix}check_structure$ LANGUAGE 'plpgsql';


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}tables_func is invoked by the trigger on ${replPrefix}tables.   
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}tables_func() 
   RETURNS trigger AS $${replPrefix}tables_func$
   DECLARE
      ret TEXT;
BEGIN
   ret = ${replPrefix}check_structure();
   RETURN NULL;
END;
$${replPrefix}tables_func$ LANGUAGE 'plpgsql';


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}tables_trigger is invoked when a change occurs                  
-- on ${replPrefix}tables.                                                      
-- ---------------------------------------------------------------------------- 

CREATE TRIGGER ${replPrefix}tables_trigger AFTER UPDATE OR DELETE OR INSERT
ON ${replPrefix}tables
FOR EACH ROW
EXECUTE PROCEDURE ${replPrefix}tables_func();


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}tables_func is invoked by the trigger on ${replPrefix}tables.   
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}increment() 
   RETURNS INTEGER AS $${replPrefix}increment$
BEGIN
   RETURN nextval('${replPrefix}seq');
END;
$${replPrefix}increment$ LANGUAGE 'plpgsql';