0035629: add per DB object info to check for model DB changes

diff -r 31bd41f81ea7 src-db/database/model/excludeFilter.xml
--- a/src-db/database/model/excludeFilter.xml	Mon Mar 27 10:52:11 2017 +0200
+++ b/src-db/database/model/excludeFilter.xml	Mon Mar 27 12:41:10 2017 +0200
@@ -5,6 +5,7 @@
     <excludedTable name="C_TEMP_SELECTION"/>
     <excludedTable name="C_TEMP_SELECTION2"/>
     <excludedTable name="AD_CONTEXT_INFO"/>
+    <excludedTable name="DB_CHECKSUMS"/>
     <excludedView name="DUAL"/>
     <excludedView name="USER_CONS_COLUMNS"/>
     <excludedView name="USER_TABLES"/>
diff -r 31bd41f81ea7 src-db/database/model/prescript-Oracle.sql
--- a/src-db/database/model/prescript-Oracle.sql	Mon Mar 27 10:52:11 2017 +0200
+++ b/src-db/database/model/prescript-Oracle.sql	Mon Mar 27 12:41:10 2017 +0200
@@ -107,8 +107,18 @@
 END GET_UUID;
 /-- END
 
-create or replace
-FUNCTION AD_DB_MODIFIED(p_Update CHAR) RETURN CHAR
+
+declare
+  cnt int;
+begin
+  select count(*) into cnt from user_tables where table_name ='DB_CHECKSUMS';
+  if (cnt = 0) then
+    execute immediate 'create table db_checksums (obj_type varchar2(500),obj_name varchar2(500),old_value clob,old_crc varchar2(32),new_value clob,new_crc varchar2(32))';
+  end if;
+end;
+/-- END
+
+create or replace FUNCTION AD_DB_MODIFIED(p_Update CHAR) RETURN CHAR
 
 AS
 /*************************************************************************
@@ -133,17 +143,93 @@
   v_Modified char(1);
   TYPE RECORD IS REF CURSOR;
   c1 RECORD;
-  PRAGMA AUTONOMOUS_TRANSACTION; --To allow DML within a function in a select        
+  PRAGMA AUTONOMOUS_TRANSACTION; --To allow DML within a function in a select      
+  currentTrigger varchar2(60):=null;  
+  currentTriggerTxt clob;
+  currentTriggerCrc varchar(32);
+  cnt int;
 BEGIN
+    if p_update = 'Y' then
+      delete from db_checksums;
+    end if;
 v_md5:='';
-for c1 in (select text from user_source where not (type = 'TRIGGER' and name like 'AU\_%' escape '\') order by name,line) loop
+
+for c1 in (select name, text from user_source where not (type = 'TRIGGER' and name like 'AU\_%' escape '\') order by name,line) loop
+     if currentTrigger is null then
+       currentTrigger := c1.name;
+       currentTriggerTxt := ' ';
+       currentTriggerCrc :=null;
+     end if;
+     if c1.name != currentTrigger then
+       if p_update = 'Y' then
+         insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('trg', currentTrigger, currentTriggerTxt,  v_md5);
+       else
+         select count(*) into cnt from db_checksums where obj_type = 'trg' and obj_name =  currentTrigger;
+         if cnt = 0 then
+           insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+         else
+           update db_checksums 
+             set new_value = currentTriggerTxt, 
+                 new_crc =  v_md5
+            where obj_type = 'trg' and obj_name = currentTrigger;
+         end if;
+       end if;
+       
+       currentTrigger := c1.name;
+       currentTriggerTxt := ' ';
+     end if;
+     currentTriggerTxt := currentTriggerTxt || c1.text;
+     
      v_md5 := dbms_obfuscation_toolkit.md5(input_string => v_md5||c1.text);
 end loop;
-for c1 in (select * from user_tab_cols order by table_name, column_id) loop
+     if p_update = 'Y' then
+         insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+       else
+         select count(*) into cnt from db_checksums where obj_type = 'trg' and obj_name =  currentTrigger;
+         if cnt = 0 then
+           insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+         else
+           update db_checksums 
+             set new_value = currentTriggerTxt, 
+                 new_crc =  v_md5
+            where obj_type = 'trg' and obj_name = currentTrigger;
+         end if;
+       end if;
+
+
+for c1 in (select * from user_tab_cols where table_name!='DB_CHECKSUMS' order by table_name, column_id) loop
      v_md5 := dbms_obfuscation_toolkit.md5(input_string => v_md5||c1.column_name||c1.data_type||c1.data_length||c1.nullable);
+     if p_update = 'Y' then
+         insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('col', c1.table_name||'.'||c1.column_name, c1.column_name||c1.data_type||c1.data_length||c1.nullable, v_md5);
+       else
+         select count(*) into cnt from db_checksums where obj_type = 'col' and obj_name =  c1.table_name||'.'||c1.column_name;
+         if cnt = 0 then
+           insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('col', c1.table_name||'.'||c1.column_name, c1.column_name||c1.data_type||c1.data_length||c1.nullable, v_md5);
+         else
+           update db_checksums 
+             set new_value = c1.column_name||c1.data_type||c1.data_length||c1.nullable,   
+                 new_crc =  v_md5
+            where obj_type = 'col' and obj_name = c1.table_name||'.'||c1.column_name;
+         end if;
+       end if;
+       
 end loop;
 for c1 in (select * from user_views order by view_name) loop
      v_md5 := dbms_obfuscation_toolkit.md5(input_string => v_md5||c1.view_name||c1.text);
+     
+       if p_update = 'Y' then
+         insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('view', c1.view_name, c1.view_name||c1.text,  v_md5);
+       else
+         select count(*) into cnt from db_checksums where obj_type = 'view' and obj_name =  c1.view_name;
+         if cnt = 0 then
+           insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('view', c1.view_name, c1.view_name||c1.text,  v_md5);
+         else
+           update db_checksums 
+             set new_value =  c1.view_name||c1.text,   
+                 new_crc =  v_md5
+            where obj_type = 'view' and obj_name = c1.view_name;
+         end if;
+       end if; 
 end loop;
 
 
@@ -157,20 +243,17 @@
   else
     v_Modified := 'Y';
   end if;     
-   BEGIN
+
    IF p_Update = 'Y' THEN
      UPDATE AD_SYSTEM_INFO
        SET LAST_DBUPDATE = NOW(),
            DB_CHECKSUM = v_md5;
    END IF;
-   END;
    COMMIT;
+
    RETURN v_Modified;
-   EXCEPTION 
-     WHEN OTHERS THEN
-       RETURN 'N';
-END AD_DB_MODIFIED
-;
+
+END AD_DB_MODIFIED;
 /-- END
 
 BEGIN