Advertisement
Guest User

Untitled

a guest
Sep 20th, 2019
110
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE compare_schemes_3(dev_scheme_name VARCHAR2, prod_scheme_name VARCHAR2) IS
  2.   TYPE table_info_rec IS RECORD(created DATE, tid INT, name_ VARCHAR2(32000));
  3.   TYPE table_info IS TABLE OF table_info_rec;
  4.   TYPE cons_info_rec IS RECORD(from_ VARCHAR2(30000), to_ VARCHAR2(30000), col VARCHAR2(30000), tid INT);
  5.   TYPE cons_info IS TABLE OF cons_info_rec;
  6.   TYPE table_varray IS varray(10000) OF table_info_rec;
  7.   TYPE func_drop_rec IS RECORD(name_ VARCHAR2(30000), type_ VARCHAR2(30000));
  8.   TYPE func_drop IS TABLE OF func_drop_rec;
  9.  
  10.   TYPE names IS TABLE OF VARCHAR2(30000);
  11.  
  12.   dev_funcs names;
  13.   prod_funcs func_drop;
  14.   dev_text names;
  15.   prod_text names;
  16.   diff_funcs names;
  17.  
  18.   drop_prod names;
  19.   drop_cons names;
  20.   str VARCHAR2(100);
  21.   script clob;
  22.  
  23.   dev table_info;
  24.   dev_cons cons_info;
  25.   prod table_info;
  26.   prod_cons cons_info;
  27.  
  28.   not_exists table_info;
  29.   check_ table_info;
  30.  
  31.   cnt_dev INT;
  32.   cnt_prod INT;
  33.   cnt_common INT;
  34.  
  35.   arr table_varray;
  36.   tmp table_info_rec;
  37. BEGIN
  38.  
  39.   script := '';
  40. -- all devs (double check)
  41.   SELECT created, object_id "tid", object_name "name_"
  42.     INTO dev
  43.     FROM dba_objects
  44.     WHERE object_type='TABLE' AND owner=dev_scheme_name
  45.     ORDER BY created;
  46. -- all prod
  47.   SELECT created, object_id "tid", object_name "name_"
  48.     INTO prod
  49.     FROM dba_objects
  50.     WHERE object_type='TABLE' AND owner=prod_scheme_name
  51.     ORDER BY created;
  52.  
  53. -- not in prod
  54.   SELECT dev.* INTO not_exists FROM
  55.     dev LEFT JOIN prod ON prod.name_=dev.name_ WHERE prod.name_ IS NULL;
  56. -- not in dev
  57.   SELECT prod.name_ INTO drop_prod FROM
  58.     dev RIGHT JOIN prod ON prod.name_=dev.name_ WHERE dev.name_ IS NULL;
  59.  
  60. --  fk in dev
  61.   SELECT cons.TABLE_NAME "from_", cons_pk.TABLE_NAME "to_", cons_cols.column_name "col", obj.object_id "tid"
  62.     INTO dev_cons
  63.     FROM dba_objects obj
  64.     LEFT JOIN all_cons_columns cons_cols ON cons_cols.table_name=obj.object_name AND cons_cols.owner=obj.owner
  65.     LEFT JOIN all_constraints cons ON cons.CONSTRAINT_NAME=cons_cols.CONSTRAINT_NAME AND cons.owner=obj.owner
  66.     LEFT JOIN all_constraints cons_pk ON cons_pk.CONSTRAINT_NAME=cons.R_CONSTRAINT_NAME AND cons_pk.OWNER=obj.owner
  67.     WHERE object_type='TABLE' AND cons.CONSTRAINT_TYPE='R' AND obj.owner=dev_scheme_name;
  68. --  fk in prod
  69.   SELECT cons.TABLE_NAME "from_", cons_pk.TABLE_NAME "to_", cons_cols.column_name "col", obj.object_id "tid"
  70.     INTO prod_cons
  71.     FROM dba_objects obj
  72.     LEFT JOIN all_cons_columns cons_cols ON cons_cols.table_name=obj.object_name AND cons_cols.owner=obj.owner
  73.     LEFT JOIN all_constraints cons ON cons.CONSTRAINT_NAME=cons_cols.CONSTRAINT_NAME AND cons.owner=obj.owner
  74.     LEFT JOIN all_constraints cons_pk ON cons_pk.CONSTRAINT_NAME=cons.R_CONSTRAINT_NAME AND cons_pk.OWNER=obj.owner
  75.     WHERE object_type='TABLE' AND cons.CONSTRAINT_TYPE='R' AND obj.owner=prod_scheme_name;
  76.  
  77. -- common tables
  78.   SELECT dev.* INTO check_ FROM
  79.     dev INNER JOIN prod ON prod.name_=dev.name_;
  80.  
  81. -- check diff in common tables
  82.   IF check_.COUNT > 0 THEN
  83.     FOR i IN check_.FIRST .. check_.LAST LOOP
  84.         SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE from_=check_(i).name_;
  85.  
  86.         SELECT COUNT(*) INTO cnt_prod FROM prod_cons WHERE from_=check_(i).name_;
  87.  
  88.         IF cnt_dev = cnt_prod THEN
  89.             SELECT COUNT(*) INTO cnt_prod FROM dev_cons full JOIN prod_cons ON
  90.                 dev_cons.from_=prod_cons.from_ AND dev_cons.to_=prod_cons.to_ AND dev_cons.col=prod_cons.col
  91.                 WHERE dev_cons.from_=check_(i).name_ AND prod_cons.from_=check_(i).name_;
  92.  
  93.             IF cnt_dev = cnt_prod THEN
  94.                 SELECT COUNT(*) INTO cnt_dev FROM all_tab_columns
  95.                     WHERE owner=dev_scheme_name AND table_name=check_(i).name_;
  96.                 SELECT COUNT(*) INTO cnt_prod FROM
  97.                 (SELECT * FROM all_tab_columns WHERE owner=dev_scheme_name AND table_name=check_(i).name_) d full JOIN
  98.                 (SELECT * FROM all_tab_columns WHERE owner=prod_scheme_name AND table_name=check_(i).name_) p
  99.                 ON d.column_name=p.column_name AND d.data_type=p.data_type AND d.nullable=p.nullable;
  100.                 IF cnt_dev = cnt_prod THEN
  101.                     CONTINUE;
  102.                 END IF;
  103.             END IF;
  104.         END IF;
  105.  
  106.         INSERT INTO not_exists VALUES (check_(i));
  107.  
  108.     END LOOP;
  109.   END IF;
  110.  
  111. -- move not exists in arr
  112.   IF not_exists.COUNT > 0 THEN
  113.       arr := table_varray();
  114.       arr.extend(not_exists.COUNT);
  115.       FOR i IN not_exists.FIRST .. not_exists.LAST LOOP
  116.           arr(i) := not_exists(i);
  117.       END LOOP;
  118.   END IF;
  119. -- circular deps
  120.   IF not_exists.COUNT > 1 THEN
  121.     FOR qqq IN not_exists.FIRST .. not_exists.LAST LOOP
  122.         FOR i IN 2 .. not_exists.LAST LOOP
  123.             SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE "to_"=arr(i).name_ AND from_=arr(i-1).name_;
  124.  
  125.             IF cnt_dev > 0 THEN
  126.                 tmp := arr(i);
  127.                 arr(i) := arr(i-1);
  128.                 arr(i-1) := tmp;
  129.             END IF;
  130.         END LOOP;
  131.     END LOOP;
  132.  
  133.     FOR i IN 2 .. not_exists.LAST LOOP
  134.         SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE "to_"=arr(i).name_ AND from_=arr(i-1).name_;
  135.  
  136.         IF cnt_dev > 0 THEN
  137.             dbms_output.put_line('Circular links!');
  138.             EXIT;
  139.         END IF;
  140.     END LOOP;
  141.   END IF;
  142.  
  143. -- drop prod tables
  144.   IF drop_prod.COUNT > 0 THEN
  145.       FOR i IN drop_prod.FIRST .. drop_prod.LAST LOOP
  146.           SELECT f.constraint_name INTO drop_cons
  147.               FROM all_constraints f INNER JOIN all_constraints p ON p.constraint_name=f.r_constraint_name
  148.               WHERE f.constraint_type='R' AND p.owner=prod_scheme_name AND f.owner=prod_scheme_name AND p.table_name=drop_prod(i);
  149.           IF drop_cons.COUNT > 0 THEN
  150.               FOR j IN drop_cons.FIRST .. drop_cons.LAST LOOP
  151.                   script := script || 'alter TABLE ' || drop_prod(i) || ' drop constraint ' || drop_cons(j) || '; ';
  152.               END LOOP;
  153.           END IF;
  154.           script := script || 'drop TABLE ' || drop_prod(i) || '; ';
  155.       END LOOP;
  156.   END IF;
  157.  
  158. -- create in prod
  159.   IF not_exists.COUNT > 0 THEN
  160.       dbms_output.put_line('TABLES:');
  161.       FOR i IN 1 .. not_exists.COUNT LOOP
  162.           dbms_output.put_line(arr(i).name_);
  163.           SELECT script || REPLACE(dbms_metadata.get_ddl('TABLE', not_exists(i), dev_scheme_name), '"' || dev_scheme_name || '".', '') INTO script FROM dual;
  164.       END LOOP;
  165.   END IF;
  166.  
  167.   SELECT name INTO dev_funcs
  168.     FROM all_source WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name GROUP BY name;
  169.  
  170.   SELECT p.name name_, p.TYPE type_ INTO prod_funcs
  171.     FROM (SELECT name, TYPE FROM all_source WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name GROUP BY name, TYPE) d
  172.     RIGHT JOIN (SELECT name, TYPE FROM all_source WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=prod_scheme_name GROUP BY name, TYPE) p
  173.     ON p.name=d.name AND p.TYPE=d.TYPE WHERE d.name IS NULL;
  174.  
  175.   IF prod_funcs.COUNT > 0 THEN
  176.       FOR i IN prod_funcs.FIRST .. prod_funcs.LAST LOOP
  177.           script := script || 'drop ' || prod_funcs(i).type_ || ' ' || prod_funcs(i).name_ || ';';
  178.       END LOOP;
  179.   END IF;
  180.  
  181.   diff_funcs := names();
  182.  
  183.   IF dev_funcs.COUNT > 0 THEN
  184.       FOR i IN dev_funcs.FIRST .. dev_funcs.LAST LOOP
  185.           SELECT text INTO dev_text FROM all_source
  186.               WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name AND name=dev_funcs(i)
  187.               ORDER BY line;
  188.           SELECT text INTO prod_text FROM all_source
  189.               WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=prod_scheme_name AND name=dev_funcs(i)
  190.               ORDER BY line;
  191.  
  192.           IF prod_text.COUNT != dev_text.COUNT THEN
  193.               diff_funcs.extend;
  194.               diff_funcs(diff_funcs.LAST) := dev_funcs(i);
  195.               CONTINUE;
  196.           END IF;
  197.  
  198.           FOR s IN prod_text.FIRST .. prod_text.LAST LOOP
  199.               IF prod_text(s) != dev_text(s) THEN
  200.                   diff_funcs.extend;
  201.                   diff_funcs(diff_funcs.LAST) := dev_funcs(i);
  202.                   -- EXIT;
  203.               END IF;
  204.           END LOOP;
  205.  
  206.       END LOOP;
  207.   END IF;
  208.  
  209.   IF diff_funcs.COUNT > 0 THEN
  210.       dbms_output.put_line('FUNCTIONS AND PROCEDURES:');
  211.       FOR i IN diff_funcs.FIRST .. diff_funcs.LAST LOOP
  212.               dbms_output.put_line(diff_funcs(i));
  213.               SELECT TYPE INTO str FROM all_source WHERE owner=dev_scheme_name AND name=diff_funcs(i) AND (TYPE='FUNCTION' OR TYPE='PROCEDURE') GROUP BY name, TYPE;
  214.               SELECT script || REPLACE(dbms_metadata.get_ddl(str, dev_scheme_name, diff_func(i)), '"' || dev_scheme_name || '".', '') INTO script FROM dual;
  215.       END LOOP;
  216.   END IF;
  217.  
  218.   dbms_output.put_line(script);
  219.  
  220. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement