meta data for this page
/******************************************************************************/
/*** 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;