bootstrap.sql

java开源的企业总线.xmlBlaster

-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}base64_enc_clob(msg CLOB, 
                           res IN OUT NOCOPY CLOB)
   RETURN INTEGER AS
     len        INTEGER;
     offset     INTEGER;
     tmp        VARCHAR2(32766);
     increment  INTEGER;
     fake       INTEGER;
BEGIN
   offset := 1;
   increment := 32766;
   len := dbms_lob.getlength(msg);
   WHILE offset < len LOOP
      dbms_lob.read(msg, increment, offset, tmp);
      offset := offset + increment;
      -- the next line would be used for oracle from version 9 up.              
      -- res := res || utl_raw.cast_to_varchar2(utl_encode.base64_encode(tmp)); 
      fake := ${replPrefix}base64_enc_vch(tmp, res);
   END LOOP;
   RETURN 0;
END ${replPrefix}base64_enc_clob;
-- EOC (end of command: needed as a separator for our script parser)            


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


-- ---------------------------------------------------------------------------- 
-- ${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 VARCHAR, content CLOB, 
                           tmp IN OUT NOCOPY CLOB) RETURN INTEGER AS
   pos  INTEGER;
   ch   VARCHAR(40);
   fake INTEGER;
BEGIN
   pos := ${replPrefix}needs_prot(content);
   IF pos = 0 THEN
      ch := '<col name="';
      dbms_lob.writeappend(tmp, length(ch), ch);
      dbms_lob.writeappend(tmp, length(name), name);
      ch := '">';
      dbms_lob.writeappend(tmp, length(ch), ch);
      dbms_lob.append(tmp, content);
      ch := '</col>';
      dbms_lob.writeappend(tmp, length(ch), ch);
      RETURN 0;
   END IF;
   IF pos = 1 THEN 
      fake := ${replPrefix}col2xml_cdata(name, content, tmp); 
      RETURN 0;
   END IF; 
   ch := '<col name="';
   dbms_lob.writeappend(tmp, length(ch), ch);
   dbms_lob.writeappend(tmp, length(name), name);
   ch := '" encoding="base64">';
   dbms_lob.writeappend(tmp, length(ch), ch);
   fake := ${replPrefix}base64_enc_clob(content, tmp);
   ch := '</col>';
   dbms_lob.writeappend(tmp, length(ch), ch);
   RETURN 0;
END ${replPrefix}col2xml;
-- EOC (end of command: needed as a separator for our script parser)            


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

CREATE OR REPLACE FUNCTION ${replPrefix}check_structure
   RETURN VARCHAR AS
BEGIN
   RETURN 'OK';
END ${replPrefix}check_structure;

-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}debug_trigger is used to check wether a trigger has to be       
-- debugged or not.                                                             
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}debug_trigger(schmName VARCHAR, 
   tblName VARCHAR) RETURN INTEGER AS 
   ret    INTEGER;
BEGIN
   SELECT debug INTO ret FROM ${replPrefix}tables WHERE tablename=tblName AND
          schemaname=schmName;
   RETURN ret;
END ${replPrefix}debug_trigger;

-- EOC (end of command: needed as a separator for our script parser)            

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

CREATE OR REPLACE FUNCTION ${replPrefix}check_tables(dbName VARCHAR, schName 
                                          VARCHAR, tblName VARCHAR, op VARCHAR,
                                          cont VARCHAR)
   RETURN VARCHAR AS
   transId    VARCHAR(${charWidth});
   res        VARCHAR(${charWidthSmall});
   tmp        INTEGER;
   replKey    INTEGER;
   contClob   CLOB;
BEGIN
   ${replPrefix}debug('CHECK_TABLES ' || schName || '.' || tblName);
--   SELECT count(*) INTO tmp FROM all_tables WHERE (table_name=tblName         
--                   OR table_name=UPPER(tblName) OR table_name=LOWER(tblName)) 
--                 AND (owner=schName OR owner=UPPER(schName) OR                
--                 owner=LOWER(schName));                                       

   SELECT count(*) INTO tmp FROM sys.all_tables WHERE 
          table_name=tblName AND owner=schName;

   ${replPrefix}debug('CHECK_TABLES count=' || TO_CHAR(tmp));
   IF tmp = 0 THEN 
      res := 'FALSE';
   ELSE
      if cont = NULL THEN
         contClob := EMPTY_CLOB;
         dbms_lob.writeappend(contClob, length(cont), cont);
         dbms_lob.close(contClob);
      ELSE
        contClob := NULL;
      END IF;

      SELECT ${replPrefix}seq.nextval INTO replKey FROM DUAL;
      INSERT INTO ${replPrefix}items (repl_key, trans_key, dbId, tablename, 
                  guid, db_action, db_catalog, db_schema, content, oldContent,
                  version) values (replKey, 'UNKNOWN', dbName, tblName,
                  NULL, op, NULL, schName, cont, NULL, '${replVersion}');

      transId := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);
      -- we compare in the db watcher if replKey = transKey and warn for it
      if transId = NULL THEN
         transId := CHR(replKey);
      END IF;
      UPDATE ${replPrefix}items SET trans_key=transId WHERE repl_key=replKey;
      res := 'TRUE';
   END IF;        
   RETURN res;
END ${replPrefix}check_tables;
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}tables_trigger is invoked when a change occurs on               
-- ${replPrefix}tables.                                                         
-- TODO: need to get DB_NAME and SCHEMA_NAME from system properties             
-- ---------------------------------------------------------------------------- 

CREATE TRIGGER ${replPrefix}tables_trigger AFTER UPDATE OR DELETE OR INSERT
ON ${replPrefix}tables
FOR EACH ROW
   DECLARE
      op         VARCHAR(${charWidth});
      tableName  VARCHAR(${charWidth});
      ret        VARCHAR(${charWidthSmall});
      -- these need to be replaced later on !!!!
      schemaName VARCHAR(${charWidth});
      dbName     VARCHAR(${charWidth});
BEGIN
   ${replPrefix}debug('TABLES TRIGGER ENTERING');
   schemaName := '';
   dbName := '';

   op := 'UNKNOWN';
   IF INSERTING THEN
      op := 'CREATE';
      tableName := :new.tablename;
      schemaName := :new.schemaname;
   ELSIF DELETING THEN
      op := 'DROP';
      tableName := :old.tablename;
      schemaName := :old.schemaname;
   ELSE
      op := 'REPLMOD';
      tableName := :new.tablename;
   END IF;
   ret := ${replPrefix}check_tables(dbName, schemaName, tableName, op, NULL);
END ${replPrefix}tables_trigger;
-- EOC (end of command: needed as a separator for our script parser)            


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

CREATE OR REPLACE FUNCTION ${replPrefix}increment
   RETURN INTEGER AS
   val INTEGER;
BEGIN
   SELECT ${replPrefix}seq.nextval INTO val FROM DUAL;
   RETURN val;
END ${replPrefix}increment;
-- EOC (end of command: needed as a separator for our script parser)            


-- ---------------------------------------------------------------------------- 
-- ${replPrefix}add_table is invoked by the triggers on schemas (can also be    
-- invoked outside the triggers by the DbWatcher via JDBC) to determine if a    
-- table has to be replicated. If the table exists it also adds it to the       
-- ${replPrefix}items table.                                                    
-- dbName the name of the database                                              
-- tblName the name of the table to be replicated.                              
-- schemaName the name of the schema containing this table.                     
-- op the name of the operation. It can be CREATE, ALTER or DROP.               
-- returns TRUE if the table exists (has to be replicated) or FALSE otherwise.  
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}add_table(dbName VARCHAR, schName 
                               VARCHAR, tblName VARCHAR, op VARCHAR)
   RETURN VARCHAR AS
   replKey INTEGER;
   transId VARCHAR(${charWidth});
   tmp     NUMBER;
   res     VARCHAR(${charWidthSmall});
BEGIN


   ${replPrefix}debug('ADD_TABLE ' || schName || '.' || tblName);

   SELECT count(*) INTO tmp FROM ${replPrefix}tables WHERE (tablename=tblName 
                   OR tablename=UPPER(tblName) OR tablename=LOWER(tblName))
                   AND (schemaname=schName OR schemaname=UPPER(schName) OR 
                   schemaname=LOWER(schName));
   ${replPrefix}debug('ADD_TABLE count=' || TO_CHAR(tmp));
   IF tmp = 0 THEN
      res := 'FALSE';
   ELSE
      ${replPrefix}debug('ADD_TABLE inserting entry into items table');

      SELECT ${replPrefix}seq.nextval INTO replKey FROM DUAL;
      INSERT INTO ${replPrefix}items (repl_key, trans_key, dbId, tablename, 
                  guid, db_action, db_catalog, db_schema, content, oldContent,
                  version) values (replKey, 'UNKNOWN', dbName, tblName,
                  NULL, op, NULL, schName, NULL, NULL, '${replVersion}');

      transId := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);
      -- we compare in the db watcher if replKey = transKey and warn for it
      if transId = NULL THEN
         transId := CHR(replKey);
      END IF;
      UPDATE ${replPrefix}items SET trans_key=transId WHERE repl_key=replKey;
      res := 'TRUE';
   END IF;
   RETURN res;
END ${replPrefix}add_table;
-- EOC (end of command: needed as a separator for our script parser)            

-- ---------------------------------------------------------------------------- 
-- ${replPrefix}prepare_broadcast is used to make the first entry in the items  
-- table before publishing a statement.                                         
-- returns TRUE if the table exists (has to be replicated) or FALSE otherwise.  
-- ---------------------------------------------------------------------------- 

CREATE OR REPLACE FUNCTION ${replPrefix}prepare_broadcast(txt VARCHAR)
   RETURN VARCHAR AS
   replKey  INTEGER;
   transId  VARCHAR(${charWidth});
   tmp      NUMBER;
   res      VARCHAR(${charWidthSmall});
   contClob CLOB;
BEGIN
   ${replPrefix}debug('PREPARE_BROADCAST ' || txt);
   contClob := EMPTY_CLOB;
   dbms_lob.createtemporary(contClob, TRUE);
   dbms_lob.open(contClob, dbms_lob.lob_readwrite);
   dbms_lob.writeappend(contClob, length(txt), txt);
   dbms_lob.close(contClob);

   SELECT ${replPrefix}seq.nextval INTO replKey FROM DUAL;
   INSERT INTO ${replPrefix}items (repl_key, trans_key, db_action, content, 
               version) values (replKey, 'UNKNOWN', 'statement', 
               contClob, '${replVersion}');
   transId := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);
   -- we compare in the db watcher if replKey = transKey and warn for it
   if transId = NULL THEN
      transId := CHR(replKey);
   END IF;
   UPDATE ${replPrefix}items SET trans_key=transId WHERE repl_key=replKey;

   res := 'TRUE';
   RETURN res;
END ${replPrefix}prepare_broadcast;
-- EOC (end of command: needed as a separator for our script parser)