Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* Create Table... */
- CREATE TABLE RPL_BLOBS(ID INTEGER NOT NULL,
- SENDER_ID INTEGER,
- SESSION_ID INTEGER,
- RPL_BLOB BLOB);
- CREATE TABLE RPL_DATABASES(ID INTEGER,
- ALIAS MY_DOCNUMBER,
- LAST_SESSION INTEGER,
- PASSKEY MY_NAME,
- ITS_ME MY_BOOLEAN,
- ITS_SERVER MY_BOOLEAN,
- DBPATH MY_NAME,
- DBUSER MY_DOCNUMBER,
- DBPASS MY_DOCNUMBER,
- DBHOST MY_DOCNUMBER);
- CREATE TABLE RPL_FIELDS(ID INTEGER NOT NULL,
- TABLE_ID INTEGER,
- FIELD_NAME MY_DOCNUMBER);
- CREATE TABLE RPL_LOG(ID INTEGER NOT NULL,
- SESSION_ID INTEGER,
- RPL_SQL VARCHAR(32000));
- CREATE TABLE RPL_RECEIVED(ID INTEGER NOT NULL,
- DB_ID INTEGER,
- BLOB_ID INTEGER,
- RECEIVED_TS TIMESTAMP,
- SESSION_ID INTEGER);
- CREATE TABLE RPL_SESSIONS(ID INTEGER NOT NULL,
- BEGIN_TS TIMESTAMP,
- END_TS TIMESTAMP);
- CREATE TABLE RPL_TABLES(ID INTEGER NOT NULL,
- TABLE_NAME MY_DOCNUMBER,
- RPL_ALLFIELDS MY_BOOLEAN,
- IS_DOCHEADER MY_BOOLEAN);
- COMMENT ON TABLE RPL_TABLES IS
- '
- Список всех неслужебных таблиц можно получить так:
- insert into rpl_tables(table_name)
- select rdb$relation_name
- from rdb$relations
- where rdb$system_flag = 0
- and ((rdb$relation_type = 0)or (rdb$relation_type is null))';
- CREATE GENERATOR GEN_RPL_BLOBS_ID;
- CREATE GENERATOR GEN_RPL_DATABASES_ID;
- CREATE GENERATOR GEN_RPL_FIELDS_ID;
- CREATE GENERATOR GEN_RPL_LOG_ID;
- CREATE GENERATOR GEN_RPL_RECEIVED_ID;
- CREATE GENERATOR GEN_RPL_SESSIONS_ID;
- CREATE GENERATOR GEN_RPL_TABLES_ID;
- ALTER TABLE RPL_RECEIVED ADD CONSTRAINT PK_RPL_RECEIVED PRIMARY KEY (ID);
- SET TERM ^ ;
- CREATE PROCEDURE RPL_ALLFIELDS(TABLE_NAME MY_DOCNUMBER,
- IS_DOCHEADER MY_BOOLEAN)
- AS
- DECLARE variable FIELD_TYPE INTEGER;
- DECLARE variable FIELD_NULL INTEGER;
- DECLARE variable FIELD_NAME VARCHAR(64);
- DECLARE variable FIELD_COUNT INTEGER;
- DECLARE variable FIELD_NUM INTEGER;
- DECLARE variable OUT_SQL VARCHAR(32000);
- BEGIN
- TABLE_NAME = UPPER(TRIM(:TABLE_NAME));
- SELECT COUNT(*)
- FROM rdb$relation_fields
- WHERE rdb$relation_name = :TABLE_NAME
- INTO field_count;
- /*
- trigger after insert
- */
- out_sql = 'create or alter trigger ' || :TABLE_NAME || '_REPL for ' || :TABLE_NAME || ' ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'active after insert or update or delete position 255' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'as' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'declare variable sql_text varchar(1024);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' if ((select rdb$get_context(''USER_SESSION'', ''replicating_now'') from rdb$database)is not null) then exit;' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' if (inserting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' values (''insert into ' || :TABLE_NAME || ' (' ;
- field_num = 1;
- FOR SELECT rdb$field_name
- FROM rdb$relation_fields
- WHERE rdb$relation_name = :TABLE_NAME
- ORDER BY rdb$field_position
- INTO field_name
- do BEGIN
- IF (:field_num = :field_count)
- THEN out_sql = :out_sql || TRIM(:field_name);
- ELSE out_sql = :out_sql || TRIM(:field_name) || ',' ;
- field_num = :field_num + 1;
- END
- out_sql = :out_sql || ')values('' ||' || ASCII_CHAR(13) || ASCII_CHAR(10);
- field_num = 1;
- FOR SELECT TRIM(rf.rdb$field_name), f.rdb$field_type, rf.rdb$null_flag
- FROM rdb$relation_fields rf
- LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
- WHERE rdb$relation_name = :TABLE_NAME
- ORDER BY rdb$field_position
- INTO field_name, field_type, field_null
- do BEGIN
- IF (:field_type IN (12,13,35,37)) THEN BEGIN
- --needed quotes
- IF (:field_null = 1)
- THEN field_name = ''''' || new.' || :field_name || ' || ''''';
- ELSE field_name = 'coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'')';
- END ELSE BEGIN
- --clear digits
- IF (:field_null = 1)
- THEN field_name = 'new.' || :field_name;
- ELSE field_name = 'coalesce(new.' || :field_name || ', ''null'')';
- END
- IF (:field_num = :field_count)
- THEN out_sql = :out_sql || ' ' || :field_name || '||'');'' ' || ');' || ASCII_CHAR(13) || ASCII_CHAR(10);
- ELSE out_sql = :out_sql || ' ' || :field_name || '||'',''||' || ASCII_CHAR(13) || ASCII_CHAR(10);
- field_num = :field_num + 1;
- END
- out_sql = :out_sql || ' end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
- /*
- trigger after update
- */
- out_sql = :out_sql ||
- ' if (updating)';
- IF (:is_docheader = 1) THEN
- out_sql = :out_sql || ' then if (new.commited <> old.commited)';
- out_sql = :out_sql ||
- ' then begin ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' sql_text = ''update '|| :TABLE_NAME || ' set '';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- field_num = 1;
- FOR SELECT TRIM(rf.rdb$field_name), f.rdb$field_type, rf.rdb$null_flag
- FROM rdb$relation_fields rf
- LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
- WHERE rdb$relation_name = :TABLE_NAME
- ORDER BY rdb$field_position
- INTO field_name, field_type, field_null
- do BEGIN
- IF (:is_docheader = 0) THEN
- out_sql = :out_sql || ' if (old.' || :field_name || ' is distinct from new.' || :field_name || ') then' || ASCII_CHAR(13) || ASCII_CHAR(10);
- IF (:field_type IN (12,13,35,37)) THEN BEGIN
- --needed quotes
- IF (:field_null = 1)
- THEN out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- ELSE out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'') || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- END ELSE BEGIN
- --clear digits
- IF (:field_null = 1)
- THEN out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- ELSE out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce(new.' || :field_name || ', ''null'') || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- END
- END
- out_sql = :out_sql || ' sql_text = substring(:sql_text from 1 for char_length(:sql_text) - 1);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' sql_text = :sql_text || '' where id = '' || old.id || '';'' ;' || ASCII_CHAR(13) || ASCII_CHAR(10);
- out_sql = :out_sql || ' insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' values (:sql_text);' || ASCII_CHAR(13) || ASCII_CHAR(10);
- out_sql = :out_sql || ' end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
- /*
- trigger after delete
- */
- out_sql = :out_sql ||
- ' if (deleting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' values (''delete from ' || :TABLE_NAME || ' where id = '' || old.id || '';'');' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' end' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'end' || ASCII_CHAR(13) || ASCII_CHAR(10);
- EXECUTE statement :out_sql;
- END
- ^
- CREATE PROCEDURE RPL_SOMEFIELDS(TABLE_NAME MY_DOCNUMBER,
- IS_DOCHEADER MY_BOOLEAN)
- AS
- DECLARE variable table_id INTEGER;
- DECLARE variable field_type INTEGER;
- DECLARE variable field_null INTEGER;
- DECLARE variable field_name VARCHAR(64);
- DECLARE variable field_count INTEGER;
- DECLARE variable field_num INTEGER;
- DECLARE variable out_sql VARCHAR(32000);
- BEGIN
- /*
- table rpl_log:
- ID identifier
- SESSION_ID fc to rpl_sessions
- REC_SQL generated sql
- */
- TABLE_NAME = UPPER(:TABLE_NAME);
- SELECT id FROM rpl_tables WHERE UPPER(TABLE_NAME) = :TABLE_NAME
- INTO table_id;
- SELECT COUNT(f.id)
- FROM rpl_fields f
- WHERE f.table_id = :table_id
- INTO field_count;
- /*
- trigger after insert
- */
- out_sql = 'create or alter trigger ' || :TABLE_NAME || '_REPL for ' || :TABLE_NAME || ' ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'active after insert or update or delete position 255' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'as' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'declare variable sql_text varchar(1024);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' if ((select rdb$get_context(''USER_SESSION'', ''replicating_now'') from rdb$database)is not null) then exit;' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' if (inserting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' values (''insert into ' || :TABLE_NAME || ' (' ;
- field_num = 1;
- FOR SELECT rdb$field_name
- FROM rdb$relation_fields r, rpl_fields f
- WHERE rdb$relation_name = :TABLE_NAME
- AND rdb$field_name = UPPER(f.field_name)
- AND f.table_id = :table_id
- ORDER BY rdb$field_position
- INTO field_name
- do BEGIN
- IF (:field_num = :field_count)
- THEN out_sql = :out_sql || TRIM(:field_name);
- ELSE out_sql = :out_sql || TRIM(:field_name) || ',' ;
- field_num = :field_num + 1;
- END
- out_sql = :out_sql || ')values('' ||' || ASCII_CHAR(13) || ASCII_CHAR(10);
- field_num = 1;
- FOR SELECT UPPER(rp.field_name), f.rdb$field_type, rf.rdb$null_flag
- FROM rpl_fields rp
- LEFT JOIN rdb$relation_fields rf ON UPPER(rp.field_name) = rf.rdb$field_name
- LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
- WHERE rdb$relation_name = :TABLE_NAME
- AND rp.table_id = :table_id
- ORDER BY rdb$field_position
- INTO field_name, field_type, field_null
- do BEGIN
- IF (:field_type IN (12,13,35,37)) THEN BEGIN
- --needed quotes
- IF (:field_null = 1)
- THEN field_name = ''''' || new.' || :field_name || ' || ''''';
- ELSE field_name = 'coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'')';
- END ELSE BEGIN
- --clear digits
- IF (:field_null = 1)
- THEN field_name = 'new.' || :field_name;
- ELSE field_name = 'coalesce(new.' || :field_name || ', ''null'')';
- END
- IF (:field_num = :field_count)
- THEN out_sql = :out_sql || ' ' || :field_name || '||'');'' ' || ');' || ASCII_CHAR(13) || ASCII_CHAR(10);
- ELSE out_sql = :out_sql || ' ' || :field_name || '||'',''||' || ASCII_CHAR(13) || ASCII_CHAR(10);
- field_num = :field_num + 1;
- END
- out_sql = :out_sql || ' end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
- /*
- trigger after update
- */
- out_sql = :out_sql ||
- ' if (updating)';
- IF (:is_docheader = 1) THEN
- out_sql = :out_sql || ' then if (new.commited <> old.commited)';
- out_sql = :out_sql ||
- ' then begin ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' sql_text = ''update '|| :TABLE_NAME || ' set '';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- field_num = 1;
- FOR SELECT UPPER(rp.field_name), f.rdb$field_type, rf.rdb$null_flag
- FROM rpl_fields rp
- LEFT JOIN rdb$relation_fields rf ON UPPER(rp.field_name) = rf.rdb$field_name
- LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
- WHERE rdb$relation_name = :TABLE_NAME
- AND rp.table_id = :table_id
- ORDER BY rdb$field_position
- INTO field_name, field_type, field_null
- do BEGIN
- out_sql = :out_sql || ' if (old.' || :field_name || ' is distinct from new.' || :field_name || ') then' || ASCII_CHAR(13) || ASCII_CHAR(10);
- IF (:field_type IN (12,13,35,37)) THEN BEGIN
- --needed quotes
- IF (:field_null = 1)
- THEN out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- ELSE out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- END ELSE BEGIN
- --clear digits
- IF (:field_null = 1)
- THEN out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- ELSE out_sql = :out_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
- END
- END
- out_sql = :out_sql || ' sql_text = substring(:sql_text from 1 for char_length(:sql_text) - 1);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' sql_text = :sql_text || '' where id = '' || old.id || '';'' ;' || ASCII_CHAR(13) || ASCII_CHAR(10);
- out_sql = :out_sql || ' insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' values (:sql_text);' || ASCII_CHAR(13) || ASCII_CHAR(10);
- out_sql = :out_sql || ' end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
- /*
- trigger after delete
- */
- out_sql = :out_sql ||
- ' if (deleting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' values (''delete from ' || :TABLE_NAME || ' where id = '' || old.id || '';'');' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- ' end' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
- 'end' || ASCII_CHAR(13) || ASCII_CHAR(10);
- EXECUTE statement :out_sql;
- END
- ^
- CREATE PROCEDURE RPL_INSTALL AS
- DECLARE variable TABLE_NAME my_docnumber;
- DECLARE variable docheader my_boolean;
- BEGIN
- FOR SELECT TABLE_NAME, is_docheader
- FROM rpl_tables rt
- WHERE rt.rpl_allfields = 1
- INTO TABLE_NAME, docheader
- do
- EXECUTE PROCEDURE rpl_allfields(:TABLE_NAME, :docheader);
- FOR SELECT TABLE_NAME FROM rpl_tables rt
- WHERE rt.rpl_allfields = 0
- INTO TABLE_NAME
- do
- EXECUTE PROCEDURE rpl_somefields(:TABLE_NAME, :docheader);
- END
- ^
- CREATE TRIGGER RPL_BLOBS_BI FOR RPL_BLOBS
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_blobs_id,1);
- END
- ^
- CREATE TRIGGER RPL_DATABASES_BI FOR RPL_DATABASES
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_databases_id,1);
- END
- ^
- CREATE TRIGGER RPL_FIELDS_BI FOR RPL_FIELDS
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_fields_id,1);
- END
- ^
- CREATE TRIGGER RPL_LOG_BI FOR RPL_LOG
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_log_id,1);
- IF (NEW.session_id IS NULL) THEN
- NEW.session_id = gen_id(gen_rpl_sessions_id, 0);
- END
- ^
- CREATE TRIGGER RPL_RECEIVED_BI FOR RPL_RECEIVED
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_received_id,1);
- END
- ^
- CREATE TRIGGER RPL_SESSIONS_BI FOR RPL_SESSIONS
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_sessions_id,1);
- IF (NEW.begin_ts IS NULL) THEN
- NEW.begin_ts = CURRENT_TIMESTAMP;
- END
- ^
- CREATE TRIGGER RPL_TABLES_BI FOR RPL_TABLES
- ACTIVE BEFORE INSERT POSITION 0
- AS
- BEGIN
- IF (NEW.id IS NULL) THEN
- NEW.id = gen_id(gen_rpl_tables_id,1);
- END
- ^
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement