/******************************************************************************/
/***         Generated by IBExpert 2006.03.06.2 07.03.2006 13:25:59         ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1252;

CREATE DATABASE 'localhost:C:\DB2.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1252;



/******************************************************************************/
/***                         User Defined Functions                         ***/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION CRLF

    RETURNS CSTRING(3) CHARACTER SET WIN1252
    ENTRY_POINT 'CRLF' MODULE_NAME 'FreeUDFLib';


DECLARE EXTERNAL FUNCTION RTRIM
    CSTRING(256) CHARACTER SET WIN1252
    RETURNS CSTRING(256) CHARACTER SET WIN1252
    ENTRY_POINT 'fn_rtrim' MODULE_NAME 'rfunc';


DECLARE EXTERNAL FUNCTION TRIM
    CSTRING(256) CHARACTER SET WIN1252
    RETURNS CSTRING(256) CHARACTER SET WIN1252
    ENTRY_POINT 'fn_trim' MODULE_NAME 'rfunc';

DECLARE EXTERNAL FUNCTION LONGSTRREPLACE
   CSTRING(16384), CSTRING(16384), CSTRING(16384)
   RETURNS CSTRING(16384) FREE_IT
  ENTRY_POINT 'fn_longstrreplace'  MODULE_NAME 'rfunc';

DECLARE EXTERNAL FUNCTION LONGTRIM
   CSTRING(16384)
   RETURNS CSTRING(16384)
  ENTRY_POINT 'fn_trim'  MODULE_NAME 'rfunc';


/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR ID;
SET GENERATOR ID TO 4319;



SET TERM ^ ; 



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE PROCEDURE DROPLOG
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE IBE$REPL_IU (
    ID BIGINT,
    USR CHAR(30) CHARACTER SET WIN1252,
    DB VARCHAR(254) CHARACTER SET WIN1252,
    SQLSTMT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CRT_TME TIMESTAMP,
    RPL_TME TIMESTAMP)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE INITLOG
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE ADR (
    ID       BIGINT NOT NULL,
    ADR1     VARCHAR(60) CHARACTER SET WIN1252 NOT NULL,
    ADR2     VARCHAR(60) CHARACTER SET WIN1252,
    ADR3     VARCHAR(60) CHARACTER SET WIN1252,
    STREET   VARCHAR(60) CHARACTER SET WIN1252,
    ZIP      VARCHAR(10) CHARACTER SET WIN1252,
    CITY     VARCHAR(60) CHARACTER SET WIN1252,
    STATE    VARCHAR(10) CHARACTER SET WIN1252,
    COUNTRY  VARCHAR(60) CHARACTER SET WIN1252
);

CREATE TABLE IBE$DAT (
    ID   BIGINT NOT NULL,
    FN   VARCHAR(80) CHARACTER SET WIN1252,
    DAT  BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

CREATE TABLE IBE$DB (
    ID  BIGINT NOT NULL,
    DB  VARCHAR(254) CHARACTER SET WIN1252
);

CREATE TABLE IBE$REPL (
    ID       BIGINT NOT NULL,
    USR      CHAR(30) CHARACTER SET WIN1252,
    DB       VARCHAR(254) CHARACTER SET WIN1252,
    SQLSTMT  BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CRT_TME  TIMESTAMP default current_timestamp,
    RPL_TME  TIMESTAMP default null
);



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: IBE$REPL_V */
CREATE VIEW IBE$REPL_V(
    ID,
    USR,
    DB,
    SQLSTMT,
    CRT_TME,
    RPL_TME)
AS
select r.ID, r.USR, r.DB, r.SQLSTMT, r.CRT_TME, r.RPL_TME from ibe$repl r join ibe$db d on d.db=r.db
;




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE ADR ADD PRIMARY KEY (ID);
ALTER TABLE IBE$DAT ADD PRIMARY KEY (ID);
ALTER TABLE IBE$DB ADD PRIMARY KEY (ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: ADR_BI */
CREATE TRIGGER ADR_BI FOR ADR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(ID,1);
END
^

/* Trigger: IBE$REPL_BI */
CREATE TRIGGER IBE$REPL_BI FOR IBE$REPL
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.db is null)
  then select db from ibe$db where id=1 into new.db;
end
^

SET TERM ; ^



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE DROPLOG
AS
DECLARE VARIABLE TN VARCHAR(64);
DECLARE VARIABLE SQLSTMT VARCHAR(30000);
DECLARE VARIABLE ANZ INTEGER;
BEGIN
  FOR            --ALLE TABELLEN UND FELDNAMEN RAUSHOLEN, DIE KEINE SYSTEMTABELLEN SIND
  SELECT
    DISTINCT
    R.RDB$RELATION_NAME
  FROM
    RDB$RELATIONS R
  WHERE
    R.RDB$SYSTEM_FLAG=0
  AND
    NOT R.RDB$RELATION_NAME CONTAINING '$'
  ORDER BY R.RDB$RELATION_NAME
  INTO :TN
  DO
  BEGIN
    TN=TRIM(TN);
    SQLSTMT='DROP TRIGGER '||tn||'_LOGD;';
    EXECUTE STATEMENT :SQLSTMT;
    SQLSTMT='DROP TRIGGER '||tn||'_LOGI;';
    EXECUTE STATEMENT :SQLSTMT;
    SQLSTMT='DROP TRIGGER '||tn||'_LOGU;';
    EXECUTE STATEMENT :SQLSTMT;
  END
END
^

ALTER PROCEDURE IBE$REPL_IU (
    ID BIGINT,
    USR CHAR(30) CHARACTER SET WIN1252,
    DB VARCHAR(254) CHARACTER SET WIN1252,
    SQLSTMT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CRT_TME TIMESTAMP,
    RPL_TME TIMESTAMP)
AS
BEGIN
  IF (EXISTS(SELECT id FROM IBE$REPL WHERE id=:id)) THEN
  begin

  end
  ELSE
    INSERT INTO IBE$REPL (
        ID,
        USR,
        DB,
        SQLSTMT,
        CRT_TME,
        RPL_TME)
    VALUES (
        :ID,
        :USR,
        :DB,
        :SQLSTMT,
        :CRT_TME,
        :RPL_TME);
END
^

ALTER PROCEDURE INITLOG
AS
DECLARE VARIABLE TN VARCHAR(64);
DECLARE VARIABLE TN_ALT VARCHAR(64);
DECLARE VARIABLE FN VARCHAR(64);
DECLARE VARIABLE TP INTEGER;
DECLARE VARIABLE TRGI VARCHAR(30000);
DECLARE VARIABLE TRGU VARCHAR(30000);
DECLARE VARIABLE TRGD VARCHAR(30000);
DECLARE VARIABLE SQLSTMT VARCHAR(30000);
DECLARE VARIABLE TRGIX VARCHAR(2000);
DECLARE VARIABLE TRGUX VARCHAR(2000);
DECLARE VARIABLE TRGDX VARCHAR(2000);
DECLARE VARIABLE SQLF VARCHAR(30000);
DECLARE VARIABLE SQLV VARCHAR(30000);
DECLARE VARIABLE SQLU VARCHAR(30000);
DECLARE VARIABLE ANZ INTEGER;
DECLARE VARIABLE L INTEGER;
DECLARE VARIABLE P INTEGER;
DECLARE VARIABLE S INTEGER;
DECLARE VARIABLE FT CHAR(1);
BEGIN
  TN_ALT='';
  TRGI='';
  TRGU='';
  TRGD='';
  TRGIX='';
  TRGUX='';
  TRGDX='';
  SQLF='';
  SQLV='';
  SQLU='';
  FT='';
  FOR            --ALLE TABELLEN UND FELDNAMEN RAUSHOLEN, DIE KEINE SYSTEMTABELLEN SIND
  SELECT
    R.RDB$RELATION_NAME,
    R.RDB$FIELD_NAME,
    F.RDB$FIELD_TYPE,
    F.RDB$FIELD_LENGTH,
    F.RDB$FIELD_PRECISION,
    F.RDB$FIELD_SCALE
  FROM
    RDB$RELATION_FIELDS R,
    RDB$FIELDS F
  WHERE
    R.RDB$SYSTEM_FLAG=0
  AND
    R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME
  AND
    NOT R.RDB$RELATION_NAME CONTAINING '$'
  ORDER BY R.RDB$RELATION_NAME,R.RDB$FIELD_POSITION
  INTO :TN,:FN,:TP,:L,:P,:S
  DO
  BEGIN
    TN=RTRIM(TN);
    FN=RTRIM(FN);


    IF ((TN_ALT<>TN) AND (TN_ALT<>'')) THEN   --ACHTUNG, TABELLENWECHSEL
    BEGIN
      IF (TRGIX<>'') THEN  EXECUTE STATEMENT TRGIX;
      EXECUTE STATEMENT TRGI;
      IF (TRGUX<>'') THEN  EXECUTE STATEMENT TRGUX;
      EXECUTE STATEMENT TRGU;
      IF (TRGDX<>'') THEN  EXECUTE STATEMENT TRGDX;
      EXECUTE STATEMENT TRGD;

      SQLF='';
      SQLV='';
      SQLU='';
    END


    --
    S=-S;
/*  TP=7 SHORT
    TP=8 INTEGER
    TP=10 FLOAT
    TP=16 NUMERIC
    TP=27 DOUBLE PRECISION

    TP=12 DATE
    TP=13 TIME
    TP=35 TIMESTAMP

    TP=14 CHAR
    TP=37 VARCHAR */
    if ((tp=7) or (tp=8) or (tp=10) or (tp=16) or (tp=27))
    then ft='N';   --numerisch
    else
    if ((tp=12) or (tp=13) or (tp=35))
    then ft='Z';   --zeit
    else ft='T';


    IF (SQLF='')
    THEN SQLF='INSERT INTO '||TN||'('||FN;
    ELSE SQLF=SQLF||','||FN;

    if (ft='N') then
    begin
      IF (SQLV='')
      THEN SQLV=      '  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'' NULL'';ELSE  SQLSTMT=SQLSTMT||'' ''||NEW.'||FN||'||'''';'||CRLF();
      ELSE SQLV=SQLV||'  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'',NULL'';ELSE  SQLSTMT=SQLSTMT||'',''||NEW.'||FN||'||'''';'||CRLF();
  
      IF (SQLU='')
      THEN SQLU=      '  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=''UPDATE '||TN||' SET '||FN||'=NULL'';ELSE  SQLSTMT=''UPDATE '||TN||' SET '||FN||'=''||NEW.'||FN||'||'''';'||CRLF();
      ELSE SQLU=SQLU||'  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'','||FN||'=NULL'';ELSE  SQLSTMT=SQLSTMT||'','||FN||'=''||NEW.'||FN||'||'''';'||CRLF();
    end
    else
    if (ft='Z') then
    begin
      IF (SQLV='')
      THEN SQLV=      '  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'' NULL'';ELSE  SQLSTMT=SQLSTMT||'' ''''''||longstrreplace(longstrreplace(longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿''),'' '',''-''),'':'',''.'')||'''''''';'||CRLF();
      ELSE SQLV=SQLV||'  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'',NULL'';ELSE  SQLSTMT=SQLSTMT||'',''''''||longstrreplace(longstrreplace(longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿''),'' '',''-''),'':'',''.'')||'''''''';'||CRLF();
  
      IF (SQLU='')
      THEN SQLU=      '  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=''UPDATE '||TN||' SET '||FN||'=NULL'';ELSE  SQLSTMT=''UPDATE '||TN||' SET '||FN||'=''''''||longstrreplace(longstrreplace(longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿''),'' '',''-''),'':'',''.'')||'''''''';'||CRLF();
      ELSE SQLU=SQLU||'  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'','||FN||'=NULL'';ELSE  SQLSTMT=SQLSTMT||'','||FN||'=''''''||longstrreplace(longstrreplace(longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿''),'' '',''-''),'':'',''.'')||'''''''';'||CRLF();
    end
    else
    begin
      IF (SQLV='')
      THEN SQLV=      '  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'' NULL'';ELSE  SQLSTMT=SQLSTMT||'' ''''''||longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿'')||'''''''';'||CRLF();
      ELSE SQLV=SQLV||'  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'',NULL'';ELSE  SQLSTMT=SQLSTMT||'',''''''||longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿'')||'''''''';'||CRLF();
  
      IF (SQLU='')
      THEN SQLU=      '  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=''UPDATE '||TN||' SET '||FN||'=NULL'';ELSE  SQLSTMT=''UPDATE '||TN||' SET '||FN||'=''''''||longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿'')||'''''''';'||CRLF();
      ELSE SQLU=SQLU||'  IF (NEW.'||FN||' IS NULL) THEN SQLSTMT=SQLSTMT||'','||FN||'=NULL'';ELSE  SQLSTMT=SQLSTMT||'','||FN||'=''''''||longstrreplace(LONGTRIM(NEW.'||FN||'),'''''''',''¿'')||'''''''';'||CRLF();
    end



    SELECT COUNT(*) FROM RDB$TRIGGERS WHERE RDB$TRIGGER_NAME=:TN||'_LOGI' INTO :ANZ;
    IF (ANZ>0) THEN TRGIX='DROP TRIGGER '||TN||'_LOGI'; ELSE TRGIX='';
    TRGI='CREATE TRIGGER '||TN||'_LOGI FOR '||TN||' ACTIVE AFTER INSERT POSITION 255 AS '||CRLF()||
         'DECLARE VARIABLE SQLSTMT VARCHAR(30000);'||CRLF()||
         'DECLARE VARIABLE ID BIGINT;'||CRLF()||
         'BEGIN'||CRLF()||
         '  ID=GEN_ID(ID,1);'||CRLF()||
         '  SQLSTMT='''||SQLF||') VALUES ('';'||CRLF()||
         SQLV||
         '  SQLSTMT=SQLSTMT||'');'';'||CRLF();
    TRGI=TRGI||'  IF ((CURRENT_USER<>''REPLICAT'')) THEN INSERT INTO IBE$REPL(ID,USR,SQLSTMT) VALUES (:ID, CURRENT_USER,:SQLSTMT);' ||CRLF();

    TRGI=TRGI||'END';

    SELECT COUNT(*) FROM RDB$TRIGGERS WHERE RDB$TRIGGER_NAME=:TN||'_LOGU' INTO :ANZ;
    IF (ANZ>0) THEN TRGUX='DROP TRIGGER '||TN||'_LOGU'; ELSE TRGUX='';
    TRGU='CREATE TRIGGER '||TN||'_LOGU FOR '||TN||' ACTIVE AFTER UPDATE POSITION 255 AS '||CRLF()||
         'DECLARE VARIABLE SQLSTMT VARCHAR(30000);'||CRLF()||
         'DECLARE VARIABLE ID BIGINT;'||CRLF()||
         'BEGIN'||CRLF()||
         '  ID=GEN_ID(ID,1);'||CRLF()||
         SQLU||
         '  SQLSTMT=SQLSTMT||'' WHERE ID=''||OLD.ID;' ||CRLF();
    TRGU=TRGU||'  IF ((CURRENT_USER<>''REPLICAT'')) THEN INSERT INTO IBE$REPL(ID,USR,SQLSTMT) VALUES (:ID, CURRENT_USER,:SQLSTMT);' ||CRLF();

    TRGU=TRGU||'END';

    SELECT COUNT(*) FROM RDB$TRIGGERS WHERE RDB$TRIGGER_NAME=:TN||'_LOGD' INTO :ANZ;
    IF (ANZ>0) THEN TRGDX='DROP TRIGGER '||TN||'_LOGD'; ELSE TRGDX='';
    TRGD='CREATE TRIGGER '||TN||'_LOGD FOR '||TN||' ACTIVE AFTER DELETE POSITION 255 AS '||CRLF()||
         'DECLARE VARIABLE SQLSTMT VARCHAR(30000);'||CRLF()||
         'DECLARE VARIABLE ID BIGINT;'||CRLF()||
         'BEGIN'||CRLF()||
         '  ID=GEN_ID(ID,1);'||CRLF()||
         '  SQLSTMT=''DELETE FROM '||TN||' WHERE ID=''||OLD.ID;' ||CRLF();
    TRGD=TRGD||'  IF ((CURRENT_USER<>''REPLICAT'')) THEN INSERT INTO IBE$REPL(ID,USR,SQLSTMT) VALUES (:ID, CURRENT_USER,:SQLSTMT);' ||CRLF();

    TRGD=TRGD||'END';

    TN_ALT=TN;
  END
  IF (TRGIX<>'') THEN  EXECUTE STATEMENT TRGIX;
  EXECUTE STATEMENT TRGI;
  IF (TRGUX<>'') THEN  EXECUTE STATEMENT TRGUX;
  EXECUTE STATEMENT TRGU;
  IF (TRGDX<>'') THEN  EXECUTE STATEMENT TRGDX;
  EXECUTE STATEMENT TRGD;


  FOR            --ALLE TABELLEN UND FELDNAMEN RAUSHOLEN, DIE KEINE SYSTEMTABELLEN SIND
  SELECT
    DISTINCT
    R.RDB$RELATION_NAME
  FROM
    RDB$RELATIONS R
  WHERE
    R.RDB$SYSTEM_FLAG=0
  AND
    NOT R.RDB$RELATION_NAME CONTAINING '$'
  ORDER BY R.RDB$RELATION_NAME
  INTO :TN
  DO
  BEGIN
    TN=RTRIM(TN);
    SQLSTMT='GRANT ALL ON '||tn||' TO PUBLIC';
    EXECUTE STATEMENT :SQLSTMT;
  END
END
^


SET TERM ; ^


/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


/* Privileges of users */
GRANT ALL ON ADR TO PUBLIC;
GRANT ALL ON IBE$DAT TO PUBLIC;
GRANT ALL ON IBE$DB TO PUBLIC;
GRANT ALL ON IBE$REPL TO PUBLIC;
GRANT ALL ON IBE$REPL_V TO PUBLIC;
GRANT EXECUTE ON PROCEDURE DROPLOG TO PUBLIC;
GRANT EXECUTE ON PROCEDURE IBE$REPL_IU TO PUBLIC;
GRANT EXECUTE ON PROCEDURE INITLOG TO PUBLIC;

/* Privileges of triggers */
GRANT SELECT ON IBE$DB TO TRIGGER IBE$REPL_BI;
GRANT UPDATE, REFERENCES ON IBE$REPL TO TRIGGER IBE$REPL_BI;

/* Privileges of procedures */
GRANT SELECT, INSERT ON IBE$REPL TO PROCEDURE IBE$REPL_IU;