Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE compare_schemes_3(dev_scheme_name VARCHAR2, prod_scheme_name VARCHAR2) IS
- TYPE table_info_rec IS RECORD(created DATE, tid INT, name_ VARCHAR2(32000));
- TYPE table_info IS TABLE OF table_info_rec;
- TYPE cons_info_rec IS RECORD(from_ VARCHAR2(30000), to_ VARCHAR2(30000), col VARCHAR2(30000), tid INT);
- TYPE cons_info IS TABLE OF cons_info_rec;
- TYPE table_varray IS varray(10000) OF table_info_rec;
- TYPE func_drop_rec IS RECORD(name_ VARCHAR2(30000), type_ VARCHAR2(30000));
- TYPE func_drop IS TABLE OF func_drop_rec;
- TYPE names IS TABLE OF VARCHAR2(30000);
- dev_funcs names;
- prod_funcs func_drop;
- dev_text names;
- prod_text names;
- diff_funcs names;
- drop_prod names;
- drop_cons names;
- str VARCHAR2(100);
- script clob;
- dev table_info;
- dev_cons cons_info;
- prod table_info;
- prod_cons cons_info;
- not_exists table_info;
- check_ table_info;
- cnt_dev INT;
- cnt_prod INT;
- cnt_common INT;
- arr table_varray;
- tmp table_info_rec;
- BEGIN
- script := '';
- -- all devs (double check)
- SELECT created, object_id "tid", object_name "name_"
- INTO dev
- FROM dba_objects
- WHERE object_type='TABLE' AND owner=dev_scheme_name
- ORDER BY created;
- -- all prod
- SELECT created, object_id "tid", object_name "name_"
- INTO prod
- FROM dba_objects
- WHERE object_type='TABLE' AND owner=prod_scheme_name
- ORDER BY created;
- -- not in prod
- SELECT dev.* INTO not_exists FROM
- dev LEFT JOIN prod ON prod.name_=dev.name_ WHERE prod.name_ IS NULL;
- -- not in dev
- SELECT prod.name_ INTO drop_prod FROM
- dev RIGHT JOIN prod ON prod.name_=dev.name_ WHERE dev.name_ IS NULL;
- -- fk in dev
- SELECT cons.TABLE_NAME "from_", cons_pk.TABLE_NAME "to_", cons_cols.column_name "col", obj.object_id "tid"
- INTO dev_cons
- FROM dba_objects obj
- LEFT JOIN all_cons_columns cons_cols ON cons_cols.table_name=obj.object_name AND cons_cols.owner=obj.owner
- LEFT JOIN all_constraints cons ON cons.CONSTRAINT_NAME=cons_cols.CONSTRAINT_NAME AND cons.owner=obj.owner
- LEFT JOIN all_constraints cons_pk ON cons_pk.CONSTRAINT_NAME=cons.R_CONSTRAINT_NAME AND cons_pk.OWNER=obj.owner
- WHERE object_type='TABLE' AND cons.CONSTRAINT_TYPE='R' AND obj.owner=dev_scheme_name;
- -- fk in prod
- SELECT cons.TABLE_NAME "from_", cons_pk.TABLE_NAME "to_", cons_cols.column_name "col", obj.object_id "tid"
- INTO prod_cons
- FROM dba_objects obj
- LEFT JOIN all_cons_columns cons_cols ON cons_cols.table_name=obj.object_name AND cons_cols.owner=obj.owner
- LEFT JOIN all_constraints cons ON cons.CONSTRAINT_NAME=cons_cols.CONSTRAINT_NAME AND cons.owner=obj.owner
- LEFT JOIN all_constraints cons_pk ON cons_pk.CONSTRAINT_NAME=cons.R_CONSTRAINT_NAME AND cons_pk.OWNER=obj.owner
- WHERE object_type='TABLE' AND cons.CONSTRAINT_TYPE='R' AND obj.owner=prod_scheme_name;
- -- common tables
- SELECT dev.* INTO check_ FROM
- dev INNER JOIN prod ON prod.name_=dev.name_;
- -- check diff in common tables
- IF check_.COUNT > 0 THEN
- FOR i IN check_.FIRST .. check_.LAST LOOP
- SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE from_=check_(i).name_;
- SELECT COUNT(*) INTO cnt_prod FROM prod_cons WHERE from_=check_(i).name_;
- IF cnt_dev = cnt_prod THEN
- SELECT COUNT(*) INTO cnt_prod FROM dev_cons full JOIN prod_cons ON
- dev_cons.from_=prod_cons.from_ AND dev_cons.to_=prod_cons.to_ AND dev_cons.col=prod_cons.col
- WHERE dev_cons.from_=check_(i).name_ AND prod_cons.from_=check_(i).name_;
- IF cnt_dev = cnt_prod THEN
- SELECT COUNT(*) INTO cnt_dev FROM all_tab_columns
- WHERE owner=dev_scheme_name AND table_name=check_(i).name_;
- SELECT COUNT(*) INTO cnt_prod FROM
- (SELECT * FROM all_tab_columns WHERE owner=dev_scheme_name AND table_name=check_(i).name_) d full JOIN
- (SELECT * FROM all_tab_columns WHERE owner=prod_scheme_name AND table_name=check_(i).name_) p
- ON d.column_name=p.column_name AND d.data_type=p.data_type AND d.nullable=p.nullable;
- IF cnt_dev = cnt_prod THEN
- CONTINUE;
- END IF;
- END IF;
- END IF;
- INSERT INTO not_exists VALUES (check_(i));
- END LOOP;
- END IF;
- -- move not exists in arr
- IF not_exists.COUNT > 0 THEN
- arr := table_varray();
- arr.extend(not_exists.COUNT);
- FOR i IN not_exists.FIRST .. not_exists.LAST LOOP
- arr(i) := not_exists(i);
- END LOOP;
- END IF;
- -- circular deps
- IF not_exists.COUNT > 1 THEN
- FOR qqq IN not_exists.FIRST .. not_exists.LAST LOOP
- FOR i IN 2 .. not_exists.LAST LOOP
- SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE "to_"=arr(i).name_ AND from_=arr(i-1).name_;
- IF cnt_dev > 0 THEN
- tmp := arr(i);
- arr(i) := arr(i-1);
- arr(i-1) := tmp;
- END IF;
- END LOOP;
- END LOOP;
- FOR i IN 2 .. not_exists.LAST LOOP
- SELECT COUNT(*) INTO cnt_dev FROM dev_cons WHERE "to_"=arr(i).name_ AND from_=arr(i-1).name_;
- IF cnt_dev > 0 THEN
- dbms_output.put_line('Circular links!');
- EXIT;
- END IF;
- END LOOP;
- END IF;
- -- drop prod tables
- IF drop_prod.COUNT > 0 THEN
- FOR i IN drop_prod.FIRST .. drop_prod.LAST LOOP
- SELECT f.constraint_name INTO drop_cons
- FROM all_constraints f INNER JOIN all_constraints p ON p.constraint_name=f.r_constraint_name
- WHERE f.constraint_type='R' AND p.owner=prod_scheme_name AND f.owner=prod_scheme_name AND p.table_name=drop_prod(i);
- IF drop_cons.COUNT > 0 THEN
- FOR j IN drop_cons.FIRST .. drop_cons.LAST LOOP
- script := script || 'alter TABLE ' || drop_prod(i) || ' drop constraint ' || drop_cons(j) || '; ';
- END LOOP;
- END IF;
- script := script || 'drop TABLE ' || drop_prod(i) || '; ';
- END LOOP;
- END IF;
- -- create in prod
- IF not_exists.COUNT > 0 THEN
- dbms_output.put_line('TABLES:');
- FOR i IN 1 .. not_exists.COUNT LOOP
- dbms_output.put_line(arr(i).name_);
- SELECT script || REPLACE(dbms_metadata.get_ddl('TABLE', not_exists(i), dev_scheme_name), '"' || dev_scheme_name || '".', '') INTO script FROM dual;
- END LOOP;
- END IF;
- SELECT name INTO dev_funcs
- FROM all_source WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name GROUP BY name;
- SELECT p.name name_, p.TYPE type_ INTO prod_funcs
- FROM (SELECT name, TYPE FROM all_source WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name GROUP BY name, TYPE) d
- RIGHT JOIN (SELECT name, TYPE FROM all_source WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=prod_scheme_name GROUP BY name, TYPE) p
- ON p.name=d.name AND p.TYPE=d.TYPE WHERE d.name IS NULL;
- IF prod_funcs.COUNT > 0 THEN
- FOR i IN prod_funcs.FIRST .. prod_funcs.LAST LOOP
- script := script || 'drop ' || prod_funcs(i).type_ || ' ' || prod_funcs(i).name_ || ';';
- END LOOP;
- END IF;
- diff_funcs := names();
- IF dev_funcs.COUNT > 0 THEN
- FOR i IN dev_funcs.FIRST .. dev_funcs.LAST LOOP
- SELECT text INTO dev_text FROM all_source
- WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=dev_scheme_name AND name=dev_funcs(i)
- ORDER BY line;
- SELECT text INTO prod_text FROM all_source
- WHERE (TYPE='FUNCTION' OR TYPE='PROCEDURE') AND owner=prod_scheme_name AND name=dev_funcs(i)
- ORDER BY line;
- IF prod_text.COUNT != dev_text.COUNT THEN
- diff_funcs.extend;
- diff_funcs(diff_funcs.LAST) := dev_funcs(i);
- CONTINUE;
- END IF;
- FOR s IN prod_text.FIRST .. prod_text.LAST LOOP
- IF prod_text(s) != dev_text(s) THEN
- diff_funcs.extend;
- diff_funcs(diff_funcs.LAST) := dev_funcs(i);
- -- EXIT;
- END IF;
- END LOOP;
- END LOOP;
- END IF;
- IF diff_funcs.COUNT > 0 THEN
- dbms_output.put_line('FUNCTIONS AND PROCEDURES:');
- FOR i IN diff_funcs.FIRST .. diff_funcs.LAST LOOP
- dbms_output.put_line(diff_funcs(i));
- 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;
- SELECT script || REPLACE(dbms_metadata.get_ddl(str, dev_scheme_name, diff_func(i)), '"' || dev_scheme_name || '".', '') INTO script FROM dual;
- END LOOP;
- END IF;
- dbms_output.put_line(script);
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement