/* +------------------------------------------------------------------------ */
/* | DESCRIPTION:                                                            */
/* |    The script version-enables all accessible tables in the current      */
/* |    data
 store.                                                          */
/* |                                                                         */
/* | CALL:                                                                   */
/* |    EnableVersioning;                                                    */
/* |                                                                         */
/* | PARAMETER:                                                              */
/* |    None.                                                                */
/* |                                                                         */
/* | NOTE:                                                                   */
/* |    Before executing the script the following should be noted:           */
/* |       - It is recommended to always connect to the Oracle user (FDO     */
/* |         data store) to be processed directly. Ensure that this user has */
/* |         been granted the Workspace Manager role WM_ADMIN_ROLE.          */
/* |       - It should be ensured that the Oracle user to be processed is    */
/* |         not currently used by any user or can be accessed by any user   */
/* |         during the execution of the script as this may result in the    */
/* |         failure of the script due – for example – session conflicts.    */
/* |       - It is recommended that the script output is saved to a log file.*/
/* |         The saved information can help to resolve any issues the script */
/* |         may encounter.                                                  */
/* |                                                                         */
/* |    The script will issue an exception if …                              */
/* |       … the Oracle user does not contain a FDO Meta Schema. For such an */
/* |         Oracle user, it is up to the user to version-enable the tables. */
/* |         However, the script can be used as a guideline to implement the */
/* |         task.                                                           */
/* |                                                                         */
/* | IMPORTANT                                                               */
/* |    If it is ensured that the user invoking the script has the necessary */
/* |    privileges and is the only user accessing the Oracle user being pro- */
/* |    cessed no error other than the described exception should be encoun- */
/* |    tered. However, if the script documents an error, the Workspace      */
/* |    Manager documentation should be consulted first before attempting    */
/* |    any action to resolve the issue as a not well-founded action may     */
/* |    have severe consequences on the usability of the data in the Oracle  */
/* |    user.                                                                */
/* |                                                                         */
/* +------------------------------------------------------------------------ */

set serveroutput on size 50000

declare
	/* ------------------------------------------------------------------ */
	/* Declare the cursors needed to execute the request:                 */
	/*   chk_f_schema_cr:                                                 */
	/*     Cursor to check whether or not the FDO Meta Schema exists.     */
	/*   enable_class_tab_cr:                                             */
	/*     Cursor to select all accessible class tables to be version-    */
	/*     enabled.                                                       */
	/*   enable_obj_tab_cr:                                               */
	/*     Cursor to select all object tables to be version-enabled.      */
	/*   inacc_class_tab_cr:                                              */
	/*     Cursor to report all class tables residing outside the current */
	/*     Oracle user and hence cannot be version-enabled.               */
	/* ------------------------------------------------------------------ */

	cursor chk_f_schema_cr is
		select object_name from user_objects
		  where object_name in ('F_CLASSDEFINITION', 'F_ATTRIBUTEDEFINITION', 'F_OPTIONS');


	cursor enable_class_tab_cr is
		select tablename from f_classdefinition
		  where tableowner is NULL and
		        schemaname not in ('F_MetaClass') and
			tablename in (select table_name from user_tables) and
			tablename not in (select table_name from user_wm_versioned_tables)
;

	cursor enable_obj_tab_cr is
		select distinct(tablename) from f_attributedefinition
		  where classid in (select classid from f_classdefinition
			              where schemaname not in ('F_MetaClass')) and
                        tablename not in (select tablename from f_classdefinition) and
                        tablename in (select table_name from user_tables) and
			tablename not in (select table_name from user_wm_versioned_tables)
;

	cursor inacc_class_tab_cr is
		select tablename, tableowner from f_classdefinition
		  where tableowner is not NULL and
		        schemaname not in ('F_MetaClass')
;

	/* ------------------------------------------------------------------ */
	/* Declare some other required variables:                             */

	/* ------------------------------------------------------------------ */

	fdo_meta_schema_count	number;

	/* ------------------------------------------------------------------ */
	/* Declare necessary exceptions.                                      */

	/* ------------------------------------------------------------------ */

	fdo_meta_schema_exp	exception;

begin
	dbms_output.put_line('Version-enabling accessible tables');

	/* ------------------------------------------------------------------ */
	/* Check if the data store contains the FDO Meta Schema. If the FDO   */
	/* Meta Schema is not present issue an exception and terminate the    */
	/* script.                                                            */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Checking for FDO Meta Schema');
	fdo_meta_schema_count := 0;	
	for chk_f_schema_rec in chk_f_schema_cr
	loop
		fdo_meta_schema_count := fdo_meta_schema_count + 1;
	end loop;
	if fdo_meta_schema_count <> 3
	then
		raise fdo_meta_schema_exp;
	end if;

	/* ------------------------------------------------------------------ */
	/* Version-enable all accessible class tables.                        */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Processing class tables');
	for enable_class_tab_rec in enable_class_tab_cr
	loop
		dbms_output.put_line(' ...... > version-enabling class table ' || enable_class_tab_rec.tablename);
		dbms_wm.EnableVersioning
(enable_class_tab_rec.tablename);
 
	end loop;

	/* ------------------------------------------------------------------ */
	/* Version-enable all accessible object property tables.              */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Processing object property tables');
	for enable_obj_tab_rec in enable_obj_tab_cr
	loop
		dbms_output.put_line(' ...... > version-enabling object table ' || enable_obj_tab_rec.tablename);
		dbms_wm.EnableVersioning
(enable_obj_tab_rec.tablename);
 
	end loop;

	/* ------------------------------------------------------------------ */
	/* Determine and report all class tables that reside outside the cur- */
	/* rent Oracle user.                                                  */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Reporting class tables that cannot be version-enabled');
	for inacc_class_tab_rec in inacc_class_tab_cr
	loop
		dbms_output.put_line(' ...... > failed to version-enabling class table ' || inacc_class_tab_rec.tableowner || '.' || inacc_class_tab_rec.tablename);
 
	end loop;

	/* ------------------------------------------------------------------ */
	/* Update the corresponding entries in the table F_Options.           */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Updating table F_OPTIONS');
	update F_OPTIONS set value = '2' where name = 'LT_MODE' or name = 'LOCKING_MODE';
	commit;

	dbms_output.put_line('Version-enabling of accessible tables finished');

exception
	/* ------------------------------------------------------------------ */
	/* Handle all exceptions.                                             */
	/* ------------------------------------------------------------------ */
	when fdo_meta_schema_exp
	then
		dbms_output.put_line(' ----------------------- EXECUTION ERROR ----------------------- ');
		dbms_output.put_line(' >>> Data store does not contain FDO Meta Schema; cannot execute ');
 		dbms_output.put_line(' >>> request to version-enable tables.');
		dbms_output.put_line(' >>> Version-enabling of accessible tables terminated');
		
end;
/

