bootstrap.sql

java开源的企业总线.xmlBlaster

   @ch  VARCHAR(MAX)
BEGIN
   SET @ch = '<col name="' + @name +
            '" encoding="base64">' +
            dbo.base64_encode(@content) +
            '</col>'
END
   RETURN  @ch;
END
-- 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 VARCHAR(MAX))
RETURN VARCHAR(MAX) AS
   pos INTEGER;
   tmp VARCHAR(MAX);
   ch  VARCHAR(40);
BEGIN
   tmp := EMPTY_VARCHAR(MAX);
   dbms_lob.createtemporary(tmp, TRUE);
   dbms_lob.open(tmp, dbms_lob.lob_readwrite);
   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);
      dbms_lob.close(tmp);
      RETURN tmp;
   END IF;
   IF pos = 1 THEN 
      RETURN ${replPrefix}col2xml_cdata(name, content);
   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);
   dbms_lob.append(tmp, ${replPrefix}base64_enc_clob(content));
   ch := '</col>';
   dbms_lob.writeappend(tmp, length(ch), ch);
   dbms_lob.close(tmp);
   RETURN  tmp;
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 FUNCTION ${replPrefix}check_structure()
   RETURNS VARCHAR(50) AS
BEGIN
DECLARE
   @dummy VARCHAR(50)
BEGIN
   set @dummy = 'OK'
END
   RETURN @dummy
END

-- 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   VARCHAR(MAX);
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));
   -- tmp := 1; -- THIS IS A HACK. TODO: Fix this, strangely a foreign schema table returns 0
             -- even if it exists (this hack makes the assumption the table exists)
   IF tmp = 0 THEN 
      res := 'FALSE';
   ELSE
      if cont = NULL THEN
         contClob := EMPTY_VARCHAR(MAX);
         dbms_lob.writeappend(contClob, length(cont), cont);
         dbms_lob.close(contClob);
      ELSE
	contClob := NULL;
      END IF;

      transId := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);
      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, transId, dbName, tblName,
                  NULL, op, NULL, schName, cont, NULL,  '0.0');
      res := 'TRUE';
   END IF;        
   RETURN res;
END ${replPrefix}check_tables;
-- EOC (end of command: needed as a separator for our script parser)            

-- TRIGGER for DELETED INSERTED ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ed84567f-7b91-4b44-b5b2-c400bda4590d.htm

-- ---------------------------------------------------------------------------- 
-- ${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 repl_insert_tables_trigger
ON repl_tables
AFTER INSERT
AS 
BEGIN
DECLARE
      @op         VARCHAR(50),
      @tableName  VARCHAR(50),
      @ret        VARCHAR(30),
      @schemaName VARCHAR(50),
      @dbName     VARCHAR(50)
BEGIN
   SET @op = 'CREATE'
   SELECT @tableName = tablename FROM inserted
   SELECT @schemaName = schemaname FROM inserted
   SET @ret = test.dbo.repl_check_tables(@dbName, @schemaName, @tableName, @op, NULL)
END
END

-- OLD: Replace
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');
      transId := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);
      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, transId, dbName, tblName,
                  NULL, op, NULL, schName, NULL, NULL,  '0.0');
      res := 'TRUE';
   END IF;
   RETURN res;
END ${replPrefix}add_table;
-- EOC (end of command: needed as a separator for our script parser)