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.                                  
-- some suggestions on how to debug:                                            
-- with sqplus you can invoke:                                                  
-- show errors trigger|function name                                            
-- and the line number indicated can be retrieved with (for example):           
-- select text from all_source where name='REPL_BASE64_ENC_RAW' AND line=18     
-- ---------------------------------------------------------------------------- 


DROP PROCEDURE ${replPrefix}debug
-- FLUSH (dropped ${replPrefix}debug)                                           


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


-- ---------------------------------------------------------------------------- 
-- This is only used to be filled for debugging purposes                        
-- ---------------------------------------------------------------------------- 
CREATE TABLE ${replPrefix}debug_table(replKey INTEGER, line VARCHAR(255))
-- EOC (end of command: needed as a separator for our script parser)            

CREATE OR REPLACE PROCEDURE ${replPrefix}debug(lineTxt VARCHAR2) AS
   replKey INTEGER;
BEGIN
   replKey := 1;                                                            
   -- SELECT ${replPrefix}seq.nextval INTO replKey FROM DUAL;
   -- INSERT INTO ${replPrefix}debug_table VALUES (replKey, lineTxt);
END;
-- EOC (end of command: needed as a separator for our script parser)            

CREATE OR REPLACE PROCEDURE ${replPrefix}test_prepost AS
   replKey INTEGER;
BEGIN
   replKey := 1;                                                            
   SELECT ${replPrefix}seq.nextval INTO replKey FROM DUAL;
   INSERT INTO ${replPrefix}debug_table VALUES (replKey, 'test pre post');
END;
-- EOC (end of command: needed as a separator for our script parser)            

-- ---------------------------------------------------------------------------- 
-- ${replPrefix}col2xml_null writes a column containing a null object.          
--   name: the name of the column                                               
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}col2xml_null(name VARCHAR, 
                           tmp IN OUT NOCOPY CLOB) RETURN INTEGER AS
  ch   VARCHAR(${charWidth});
  fake INTEGER;
BEGIN
   ch := '<col name="';
   dbms_lob.writeappend(tmp, length(ch), ch);
   dbms_lob.writeappend(tmp, length(name), name);
   ch := '" type="null"/>';
   dbms_lob.writeappend(tmp, length(ch), ch);
   RETURN 0;
END ${replPrefix}col2xml_null;
-- EOC (end of command: needed as a separator for our script parser)            

-- ---------------------------------------------------------------------------- 
-- This is only for old LONG datas                                              
-- ---------------------------------------------------------------------------- 

CREATE TABLE ${replPrefix}longs_table(repl_key INTEGER, 
                              content CLOB, PRIMARY KEY (repl_key)) 
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- 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(${charWidth}), 
                         schemaname VARCHAR(${charWidth}),
                         tablename VARCHAR(${charWidth}), actions CHAR(3),
                         status VARCHAR(${charWidthSmall}), repl_key INTEGER, 
                         trigger_name VARCHAR(${charWidth}), debug INTEGER,
                         PRIMARY KEY(catalogname, schemaname, tablename))
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- 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 table_name AS relname 
       FROM all_tables WHERE table_name IN (SELECT tablename 
       FROM ${replPrefix}tables)
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- 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
-- EOC (end of command: needed as a separator for our script parser)            

CREATE TABLE ${replPrefix}items (repl_key INTEGER, 
             trans_key VARCHAR(${charWidth}), dbId VARCHAR(${charWidth}), 
             tablename VARCHAR(${charWidth}), guid VARCHAR(${charWidth}), 
             db_action VARCHAR(${charWidth}), db_catalog VARCHAR(${charWidth}),
             db_schema VARCHAR(${charWidth}), content CLOB, oldContent CLOB, 
             version VARCHAR(${charWidthSmall}), PRIMARY KEY (repl_key))
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- This trigger is needed if you want to detect change events synchronously as  
-- they occur without the need of polling. It is only used in conjunction with  
-- the OracleByEventScheduler.                                                  
-- ---------------------------------------------------------------------------- 
CREATE TRIGGER ${replPrefix}scheduler_trigger AFTER INSERT
ON ${replPrefix}ITEMS
FOR EACH ROW
BEGIN
   dbms_alert.signal('${replPrefix}ITEMS', 'INSERT');
END ${replPrefix}scheduler_trigger;
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}fill_blob_char                                                  
-- must be invoked as:                                                          
-- repl_fill_blob_char(newCont, :new.SRWY_RWY_ID, 'SRWY_RWY_ID');               
-- ---------------------------------------------------------------------------- 


CREATE OR REPLACE FUNCTION ${replPrefix}fill_blob_char(val VARCHAR, 
                           nameOfParam VARCHAR, res IN OUT NOCOPY CLOB) 
   RETURN INTEGER AS
   tmpCont CLOB;
   fake    INTEGER;
BEGIN
   tmpCont := EMPTY_CLOB;
   dbms_lob.createtemporary(tmpCont, TRUE);
   dbms_lob.open(tmpCont, dbms_lob.lob_readwrite);
   dbms_lob.writeappend(tmpCont, LENGTH(val), val);
   -- dbms_lob.append(completeCont, ${replPrefix}col2xml(nameOfParam, tmpCont));
   fake := ${replPrefix}col2xml(nameOfParam, tmpCont, res);
   dbms_lob.close(tmpCont);
   dbms_lob.freetemporary(tmpCont);
   RETURN 0;
END ${replPrefix}fill_blob_char;
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- ${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 VARCHAR, 
                           content CLOB, tmp IN OUT NOCOPY CLOB) RETURN INTEGER AS
  ch   VARCHAR(${charWidth});
  fake INTEGER;
BEGIN
   ch := '<col name="';
   dbms_lob.writeappend(tmp, length(ch), ch);
   dbms_lob.writeappend(tmp, length(name), name);
   ch := '"><![CDATA[';
   dbms_lob.writeappend(tmp, length(ch), ch);
   dbms_lob.append(tmp, content);
   ch := ']]></col>';
   dbms_lob.writeappend(tmp, length(ch), ch);
   RETURN 0;
END ${replPrefix}col2xml_cdata;
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- ${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 CLOB) 
RETURN INTEGER AS
   pos       INTEGER;
   ret       INTEGER;
   len       INTEGER;
   offset    INTEGER;
   tmp       VARCHAR(32766);
   increment INTEGER;
BEGIN
   ret := 0;
   offset := 1;
   increment := 32766;
   len := dbms_lob.getlength(content);

   WHILE offset < len LOOP
      dbms_lob.read(content, increment, offset, tmp);
      offset := offset + increment; 
      if len > increment THEN 
         offset := offset - 3; -- overlap to be sure not to cut a token
      END IF;
      pos := INSTR(tmp, ']]>', 1, 1);
      IF POS > 0 THEN
         IF ret < 2 THEN
            ret := 2;
         END IF;
      END IF;
      pos := INSTR(tmp, '<', 1, 1);
      IF POS > 0 THEN 
         IF ret < 1 THEN
            ret := 1;
         END IF;
      END IF;
      pos := INSTR(tmp, '&', 1, 1);
      IF POS > 0 THEN 
         IF ret < 1 THEN 
            ret := 1;
         END IF;
      END IF;
   END LOOP;
   RETURN ret;
END ${replPrefix}needs_prot;
-- EOC (end of command: needed as a separator for our script parser)            


                 
-- ---------------------------------------------------------------------------- 
-- ${replPrefix}base64_helper is only needed in ORACLE previous to version 9    
-- and is used by ${replPrefix}base64_enc_raw and ${replPrefix}base64_enc_char. 
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}base64_helper(zeros SMALLINT, 
                  val INTEGER) RETURN VARCHAR AS
     numBuild INTEGER;
     char1 SMALLINT;
     char2 SMALLINT;
     char3 SMALLINT;
     char4 SMALLINT;
BEGIN
   numBuild := val;
   char1    := TRUNC(numBuild / 262144); -- 64^3 (first number)                 
   numBuild := MOD(numBuild, 262144);
   char2    := TRUNC(numBuild / 4096);   --64^2 (second number)                 
   numBuild := MOD(numBuild, 4096);
   char3    := TRUNC(numBuild / 64);     --64^1 (third number)                  
   numBuild := MOD(numBuild, 64);
   char4    := numBuild;                 --64^0 (fifth number)                  
   --Convert from actual base64 to ascii representation of base 64              

   IF char1 BETWEEN 0 AND 25 THEN
      char1 := char1 + ASCII('A');
   ELSE
      IF char1 BETWEEN 26 AND 51 THEN
         char1 := char1 + ASCII('a') - 26;
      ELSE
         IF char1 BETWEEN 52 AND 63 THEN
            IF char1 = 62 THEN
               char1 := 43;
            ELSE  
               char1 := char1 + ASCII('0') - 52;
            END IF;
         END IF;
      END IF;
   END IF;
   IF char1 = 59 THEN
      char1 := 47;
   END IF;

   IF char2 BETWEEN 0 AND 25 THEN
      char2 := char2 + ASCII('A');
   ELSE
      IF char2 BETWEEN 26 AND 51 THEN
         char2 := char2 + ASCII('a') - 26;
      ELSE
         IF char2 BETWEEN 52 AND 63 THEN
            IF char2 = 62 THEN
               char2 := 43;
            ELSE  
               char2 := char2 + ASCII('0') - 52;
            END IF; 
         END IF;
      END IF;
   END IF;
   IF char2 = 59 THEN
      char2 := 47;
   END IF;

   IF char3 BETWEEN 0 AND 25 THEN
      char3 := char3 + ASCII('A');
   ELSE
      IF char3 BETWEEN 26 AND 51 THEN
         char3 := char3 + ASCII('a') - 26;
      ELSE
         IF char3 BETWEEN 52 AND 63 THEN
            IF char3 = 62 THEN
               char3 := 43;
            ELSE  
               char3 := char3 + ASCII('0') - 52;
            END IF;
         END IF;
      END IF;
   END IF;
   IF char3 = 59 THEN
      char3 := 47;
   END IF;

   IF char4 BETWEEN 0 AND 25 THEN