Advertisement
Guest User

Полный sql-скрипт

a guest
Apr 21st, 2011
1,093
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 15.34 KB | None | 0 0
  1. /* Create Table... */
  2. CREATE TABLE RPL_BLOBS(ID INTEGER NOT NULL,
  3. SENDER_ID INTEGER,
  4. SESSION_ID INTEGER,
  5. RPL_BLOB BLOB);
  6.  
  7.  
  8. CREATE TABLE RPL_DATABASES(ID INTEGER,
  9. ALIAS MY_DOCNUMBER,
  10. LAST_SESSION INTEGER,
  11. PASSKEY MY_NAME,
  12. ITS_ME MY_BOOLEAN,
  13. ITS_SERVER MY_BOOLEAN,
  14. DBPATH MY_NAME,
  15. DBUSER MY_DOCNUMBER,
  16. DBPASS MY_DOCNUMBER,
  17. DBHOST MY_DOCNUMBER);
  18.  
  19.  
  20. CREATE TABLE RPL_FIELDS(ID INTEGER NOT NULL,
  21. TABLE_ID INTEGER,
  22. FIELD_NAME MY_DOCNUMBER);
  23.  
  24.  
  25. CREATE TABLE RPL_LOG(ID INTEGER NOT NULL,
  26. SESSION_ID INTEGER,
  27. RPL_SQL VARCHAR(32000));
  28.  
  29.  
  30. CREATE TABLE RPL_RECEIVED(ID INTEGER NOT NULL,
  31. DB_ID INTEGER,
  32. BLOB_ID INTEGER,
  33. RECEIVED_TS TIMESTAMP,
  34. SESSION_ID INTEGER);
  35.  
  36.  
  37. CREATE TABLE RPL_SESSIONS(ID INTEGER NOT NULL,
  38. BEGIN_TS TIMESTAMP,
  39. END_TS TIMESTAMP);
  40.  
  41. CREATE TABLE RPL_TABLES(ID INTEGER NOT NULL,
  42. TABLE_NAME MY_DOCNUMBER,
  43. RPL_ALLFIELDS MY_BOOLEAN,
  44. IS_DOCHEADER MY_BOOLEAN);
  45.  
  46.  
  47. COMMENT ON TABLE RPL_TABLES IS
  48. '
  49. Список всех неслужебных таблиц можно получить так:
  50. insert into rpl_tables(table_name)
  51. select rdb$relation_name
  52.    from rdb$relations
  53.    where rdb$system_flag = 0
  54.    and ((rdb$relation_type = 0)or (rdb$relation_type is null))';
  55.  
  56.  
  57. CREATE GENERATOR GEN_RPL_BLOBS_ID;
  58.  
  59. CREATE GENERATOR GEN_RPL_DATABASES_ID;
  60.  
  61. CREATE GENERATOR GEN_RPL_FIELDS_ID;
  62.  
  63. CREATE GENERATOR GEN_RPL_LOG_ID;
  64.  
  65. CREATE GENERATOR GEN_RPL_RECEIVED_ID;
  66.  
  67. CREATE GENERATOR GEN_RPL_SESSIONS_ID;
  68.  
  69. CREATE GENERATOR GEN_RPL_TABLES_ID;
  70.  
  71. ALTER TABLE RPL_RECEIVED ADD CONSTRAINT PK_RPL_RECEIVED PRIMARY KEY (ID);
  72.  
  73. SET TERM ^ ;
  74.  
  75. CREATE PROCEDURE RPL_ALLFIELDS(TABLE_NAME MY_DOCNUMBER,
  76. IS_DOCHEADER MY_BOOLEAN)
  77.  AS
  78. DECLARE variable FIELD_TYPE INTEGER;
  79. DECLARE variable FIELD_NULL INTEGER;
  80. DECLARE variable FIELD_NAME VARCHAR(64);
  81. DECLARE variable FIELD_COUNT INTEGER;
  82. DECLARE variable FIELD_NUM INTEGER;
  83. DECLARE variable OUT_SQL VARCHAR(32000);
  84. BEGIN
  85.   TABLE_NAME = UPPER(TRIM(:TABLE_NAME));
  86.   SELECT COUNT(*)
  87.     FROM rdb$relation_fields
  88.     WHERE rdb$relation_name = :TABLE_NAME
  89.     INTO field_count;
  90. /*
  91. trigger after insert
  92. */
  93.   out_sql = 'create or alter trigger ' || :TABLE_NAME || '_REPL for ' || :TABLE_NAME || ' ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  94.             'active after insert or update or delete position 255' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  95.             'as' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  96.             'declare variable sql_text varchar(1024);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  97.             'begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  98.             '  if ((select rdb$get_context(''USER_SESSION'', ''replicating_now'') from rdb$database)is not null) then exit;' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  99.             '  if (inserting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  100.             '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  101.             '      values (''insert into ' || :TABLE_NAME || ' (' ;
  102.  
  103.   field_num = 1;
  104.   FOR SELECT rdb$field_name
  105.     FROM rdb$relation_fields
  106.     WHERE rdb$relation_name = :TABLE_NAME
  107.     ORDER BY rdb$field_position
  108.     INTO field_name
  109.   do BEGIN
  110.     IF (:field_num = :field_count)
  111.       THEN out_sql = :out_sql || TRIM(:field_name);
  112.       ELSE out_sql = :out_sql || TRIM(:field_name) || ',' ;
  113.     field_num = :field_num + 1;
  114.   END
  115.   out_sql = :out_sql || ')values('' ||' || ASCII_CHAR(13) || ASCII_CHAR(10);
  116.  
  117.   field_num = 1;
  118.   FOR SELECT TRIM(rf.rdb$field_name), f.rdb$field_type, rf.rdb$null_flag
  119.     FROM rdb$relation_fields rf
  120.     LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
  121.     WHERE rdb$relation_name = :TABLE_NAME
  122.     ORDER BY rdb$field_position
  123.     INTO field_name, field_type, field_null
  124.   do BEGIN
  125.     IF (:field_type IN (12,13,35,37)) THEN BEGIN
  126.     --needed quotes
  127.       IF (:field_null = 1)
  128.         THEN field_name = ''''' || new.' || :field_name || ' || ''''';
  129.         ELSE field_name = 'coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'')';
  130.     END ELSE BEGIN
  131.     --clear digits
  132.       IF (:field_null = 1)
  133.         THEN field_name = 'new.' || :field_name;
  134.         ELSE field_name = 'coalesce(new.' || :field_name || ', ''null'')';
  135.     END
  136.  
  137.     IF (:field_num = :field_count)
  138.       THEN out_sql = :out_sql || '      ' || :field_name || '||'');'' ' || ');' || ASCII_CHAR(13) || ASCII_CHAR(10);
  139.       ELSE out_sql = :out_sql || '      ' || :field_name || '||'',''||' || ASCII_CHAR(13) || ASCII_CHAR(10);
  140.     field_num = :field_num + 1;
  141.   END
  142.  
  143.   out_sql = :out_sql || '  end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
  144.  
  145. /*
  146. trigger after update
  147. */
  148.   out_sql = :out_sql ||
  149.             '  if (updating)';
  150.   IF (:is_docheader = 1) THEN
  151.     out_sql = :out_sql || ' then if (new.commited <> old.commited)';
  152.   out_sql = :out_sql ||
  153.             ' then begin ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  154.             '    sql_text = ''update '|| :TABLE_NAME || ' set '';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  155.   field_num = 1;
  156.   FOR SELECT TRIM(rf.rdb$field_name), f.rdb$field_type, rf.rdb$null_flag
  157.     FROM rdb$relation_fields rf
  158.     LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
  159.     WHERE rdb$relation_name = :TABLE_NAME
  160.     ORDER BY rdb$field_position
  161.     INTO field_name, field_type, field_null
  162.   do BEGIN
  163.     IF (:is_docheader = 0) THEN
  164.       out_sql = :out_sql || '    if (old.' || :field_name || ' is distinct from new.' || :field_name || ') then' || ASCII_CHAR(13) || ASCII_CHAR(10);
  165.     IF (:field_type IN (12,13,35,37)) THEN BEGIN
  166.     --needed quotes
  167.       IF (:field_null = 1)
  168.         THEN out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  169.         ELSE out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'') || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  170.     END ELSE BEGIN
  171.     --clear digits
  172.       IF (:field_null = 1)
  173.         THEN out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  174.         ELSE out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce(new.' || :field_name || ', ''null'') || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  175.     END
  176.   END
  177.   out_sql = :out_sql || '    sql_text = substring(:sql_text from 1 for char_length(:sql_text) - 1);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  178.                         '    sql_text = :sql_text || '' where id = '' || old.id || '';'' ;' || ASCII_CHAR(13) || ASCII_CHAR(10);
  179.   out_sql = :out_sql || '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  180.                         '      values (:sql_text);' || ASCII_CHAR(13) || ASCII_CHAR(10);
  181.   out_sql = :out_sql || '  end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
  182.  
  183. /*
  184. trigger after delete
  185. */
  186.  
  187.   out_sql =  :out_sql ||
  188.             '  if (deleting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  189.             '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  190.             '      values (''delete from ' || :TABLE_NAME || ' where id = '' ||  old.id  || '';'');' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  191.             '  end' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  192.             'end' || ASCII_CHAR(13) || ASCII_CHAR(10);
  193.   EXECUTE statement :out_sql;
  194. END
  195. ^
  196.  
  197. CREATE PROCEDURE RPL_SOMEFIELDS(TABLE_NAME MY_DOCNUMBER,
  198. IS_DOCHEADER MY_BOOLEAN)
  199.  AS
  200. DECLARE variable table_id INTEGER;
  201. DECLARE variable field_type INTEGER;
  202. DECLARE variable field_null INTEGER;
  203. DECLARE variable field_name VARCHAR(64);
  204. DECLARE variable field_count INTEGER;
  205. DECLARE variable field_num INTEGER;
  206. DECLARE variable out_sql VARCHAR(32000);
  207. BEGIN
  208. /*
  209. table rpl_log:
  210. ID         identifier
  211. SESSION_ID fc to rpl_sessions
  212. REC_SQL    generated sql
  213. */
  214.   TABLE_NAME = UPPER(:TABLE_NAME);
  215.   SELECT id FROM rpl_tables WHERE UPPER(TABLE_NAME) = :TABLE_NAME
  216.     INTO table_id;
  217.   SELECT COUNT(f.id)
  218.     FROM rpl_fields f
  219.     WHERE f.table_id = :table_id
  220.     INTO field_count;
  221. /*
  222. trigger after insert
  223. */
  224.   out_sql = 'create or alter trigger ' || :TABLE_NAME || '_REPL for ' || :TABLE_NAME || ' ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  225.             'active after insert or update or delete position 255' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  226.             'as' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  227.             'declare variable sql_text varchar(1024);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  228.             'begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  229.             '  if ((select rdb$get_context(''USER_SESSION'', ''replicating_now'') from rdb$database)is not null) then exit;' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  230.             '  if (inserting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  231.             '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  232.             '      values (''insert into ' || :TABLE_NAME || ' (' ;
  233.  
  234.   field_num = 1;
  235.   FOR SELECT rdb$field_name
  236.     FROM rdb$relation_fields r, rpl_fields f
  237.     WHERE rdb$relation_name = :TABLE_NAME
  238.     AND rdb$field_name = UPPER(f.field_name)
  239.     AND f.table_id = :table_id
  240.     ORDER BY rdb$field_position
  241.     INTO field_name
  242.   do BEGIN
  243.     IF (:field_num = :field_count)
  244.       THEN out_sql = :out_sql || TRIM(:field_name);
  245.       ELSE out_sql = :out_sql || TRIM(:field_name) || ',' ;
  246.     field_num = :field_num + 1;
  247.   END
  248.   out_sql = :out_sql || ')values('' ||' || ASCII_CHAR(13) || ASCII_CHAR(10);
  249.  
  250.   field_num = 1;
  251.   FOR SELECT UPPER(rp.field_name), f.rdb$field_type, rf.rdb$null_flag
  252.     FROM rpl_fields rp
  253.     LEFT JOIN rdb$relation_fields rf ON UPPER(rp.field_name) = rf.rdb$field_name
  254.     LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
  255.     WHERE rdb$relation_name = :TABLE_NAME
  256.     AND rp.table_id = :table_id
  257.     ORDER BY rdb$field_position
  258.     INTO field_name, field_type, field_null
  259.   do BEGIN
  260.     IF (:field_type IN (12,13,35,37)) THEN BEGIN
  261.     --needed quotes
  262.       IF (:field_null = 1)
  263.         THEN field_name = ''''' || new.' || :field_name || ' || ''''';
  264.         ELSE field_name = 'coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'')';
  265.     END ELSE BEGIN
  266.     --clear digits
  267.       IF (:field_null = 1)
  268.         THEN field_name = 'new.' || :field_name;
  269.         ELSE field_name = 'coalesce(new.' || :field_name || ', ''null'')';
  270.     END
  271.  
  272.     IF (:field_num = :field_count)
  273.       THEN out_sql = :out_sql || '      ' || :field_name || '||'');'' ' || ');' || ASCII_CHAR(13) || ASCII_CHAR(10);
  274.       ELSE out_sql = :out_sql || '      ' || :field_name || '||'',''||' || ASCII_CHAR(13) || ASCII_CHAR(10);
  275.     field_num = :field_num + 1;
  276.   END
  277.  
  278.   out_sql = :out_sql || '  end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
  279.  
  280. /*
  281. trigger after update
  282. */
  283.   out_sql = :out_sql ||
  284.             '  if (updating)';
  285.   IF (:is_docheader = 1) THEN
  286.     out_sql = :out_sql || ' then if (new.commited <> old.commited)';
  287.   out_sql = :out_sql ||
  288.             ' then begin ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  289.             '    sql_text = ''update '|| :TABLE_NAME || ' set '';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  290.   field_num = 1;
  291.   FOR SELECT UPPER(rp.field_name), f.rdb$field_type, rf.rdb$null_flag
  292.     FROM rpl_fields rp
  293.     LEFT JOIN rdb$relation_fields rf ON UPPER(rp.field_name) = rf.rdb$field_name
  294.     LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
  295.     WHERE rdb$relation_name = :TABLE_NAME
  296.     AND rp.table_id = :table_id
  297.     ORDER BY rdb$field_position
  298.     INTO field_name, field_type, field_null
  299.   do BEGIN
  300.     out_sql = :out_sql || '    if (old.' || :field_name || ' is distinct from new.' || :field_name || ') then' || ASCII_CHAR(13) || ASCII_CHAR(10);
  301.     IF (:field_type IN (12,13,35,37)) THEN BEGIN
  302.     --needed quotes
  303.       IF (:field_null = 1)
  304.         THEN out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  305.         ELSE out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  306.     END ELSE BEGIN
  307.     --clear digits
  308.       IF (:field_null = 1)
  309.         THEN out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  310.         ELSE out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
  311.     END
  312.   END
  313.   out_sql = :out_sql || '    sql_text = substring(:sql_text from 1 for char_length(:sql_text) - 1);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  314.                         '    sql_text = :sql_text || '' where id = '' || old.id || '';'' ;' || ASCII_CHAR(13) || ASCII_CHAR(10);
  315.   out_sql = :out_sql || '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  316.                         '      values (:sql_text);' || ASCII_CHAR(13) || ASCII_CHAR(10);
  317.   out_sql = :out_sql || '  end ' || ASCII_CHAR(13) || ASCII_CHAR(10);
  318.  
  319. /*
  320. trigger after delete
  321. */
  322.  
  323.   out_sql =  :out_sql ||
  324.             '  if (deleting) then begin' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  325.             '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  326.             '      values (''delete from ' || :TABLE_NAME || ' where id = '' ||  old.id  || '';'');' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  327.             '  end' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
  328.             'end' || ASCII_CHAR(13) || ASCII_CHAR(10);
  329.   EXECUTE statement :out_sql;
  330. END
  331. ^
  332.  
  333. CREATE PROCEDURE RPL_INSTALL AS
  334. DECLARE variable TABLE_NAME my_docnumber;
  335. DECLARE variable docheader my_boolean;
  336. BEGIN
  337.   FOR SELECT TABLE_NAME, is_docheader
  338.     FROM rpl_tables rt
  339.     WHERE rt.rpl_allfields = 1
  340.     INTO TABLE_NAME, docheader
  341.   do
  342.     EXECUTE PROCEDURE rpl_allfields(:TABLE_NAME, :docheader);
  343.  
  344.   FOR SELECT TABLE_NAME FROM rpl_tables rt
  345.     WHERE rt.rpl_allfields = 0
  346.     INTO TABLE_NAME
  347.   do
  348.     EXECUTE PROCEDURE rpl_somefields(:TABLE_NAME, :docheader);
  349. END
  350. ^
  351.  
  352. CREATE TRIGGER RPL_BLOBS_BI FOR RPL_BLOBS
  353. ACTIVE BEFORE INSERT POSITION 0
  354. AS
  355. BEGIN
  356.   IF (NEW.id IS NULL) THEN
  357.     NEW.id = gen_id(gen_rpl_blobs_id,1);
  358. END
  359. ^
  360.  
  361. CREATE TRIGGER RPL_DATABASES_BI FOR RPL_DATABASES
  362. ACTIVE BEFORE INSERT POSITION 0
  363. AS
  364. BEGIN
  365.   IF (NEW.id IS NULL) THEN
  366.     NEW.id = gen_id(gen_rpl_databases_id,1);
  367. END
  368. ^
  369.  
  370. CREATE TRIGGER RPL_FIELDS_BI FOR RPL_FIELDS
  371. ACTIVE BEFORE INSERT POSITION 0
  372. AS
  373. BEGIN
  374.   IF (NEW.id IS NULL) THEN
  375.     NEW.id = gen_id(gen_rpl_fields_id,1);
  376. END
  377. ^
  378.  
  379. CREATE TRIGGER RPL_LOG_BI FOR RPL_LOG
  380. ACTIVE BEFORE INSERT POSITION 0
  381. AS
  382. BEGIN
  383.   IF (NEW.id IS NULL) THEN
  384.     NEW.id = gen_id(gen_rpl_log_id,1);
  385.   IF (NEW.session_id IS NULL) THEN
  386.     NEW.session_id = gen_id(gen_rpl_sessions_id, 0);
  387. END
  388. ^
  389.  
  390. CREATE TRIGGER RPL_RECEIVED_BI FOR RPL_RECEIVED
  391. ACTIVE BEFORE INSERT POSITION 0
  392. AS
  393. BEGIN
  394.   IF (NEW.id IS NULL) THEN
  395.     NEW.id = gen_id(gen_rpl_received_id,1);
  396. END
  397. ^
  398.  
  399. CREATE TRIGGER RPL_SESSIONS_BI FOR RPL_SESSIONS
  400. ACTIVE BEFORE INSERT POSITION 0
  401. AS
  402. BEGIN
  403.   IF (NEW.id IS NULL) THEN
  404.     NEW.id = gen_id(gen_rpl_sessions_id,1);
  405.   IF (NEW.begin_ts IS NULL) THEN
  406.     NEW.begin_ts = CURRENT_TIMESTAMP;
  407. END
  408. ^
  409.  
  410. CREATE TRIGGER RPL_TABLES_BI FOR RPL_TABLES
  411. ACTIVE BEFORE INSERT POSITION 0
  412. AS
  413. BEGIN
  414.   IF (NEW.id IS NULL) THEN
  415.     NEW.id = gen_id(gen_rpl_tables_id,1);
  416. END
  417. ^
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement