Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="notification_schema_upgrade.sql+"/>
Rem
Rem $Header: preferences_schema_upgrade.sql 25-aug-2005.02:43:58 paachary Exp $
Rem
Rem preferences_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      preferences_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    paachary    08/24/05 - Fixing the upgrade issue of default columns for Systems
Rem                           Making a call to v102010/preferences_post_creation.sql
Rem                           This file contains the insert scripts for all the subtabs
Rem                           Refer Bug 4567891.
Rem    jpyang      08/05/05 - move insert to data_upgrade.sql 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    jpyang      06/14/05 - fix column_type 
Rem    jpyang      05/24/05 - fix merge error 
Rem    jpyang      05/24/05 - set host default columns 
Rem    jpyang      05/11/05 - fix upgrade issue 
Rem    asawant     03/01/05 - Add subtab_prefs and subtab_prefs_array 
Rem    asawant     02/14/05 - Add more upgrade 
Rem    dcawley     02/11/05 - dcawley_user_model_mig_to_10_2
Rem    dcawley     02/09/05 - Created
Rem

rem
rem  PURPOSE AND OTHER RELEVAN REMARKS
rem
rem       The MGMT_USER_SUBTAB_COL_PREFS table holds the list of
rem     columns that a user chooses to see for a given sub tab. The primary
rem     key is composed of the user_name, subtab_name, and display_order (which
rem     also reflects the access pattern and thus a good index). The column
rem     identifier may be stored in either column_id_guid or column_id, which
rem     allows integrators the flexibility of storing either data type. It is
rem     illegal (though not enforced automatically) to populate both identifier
rem     columns in the same row (one should be NULL).
rem       This table replaces the earlier MGMT_USER_TYPE_METRIC_PREFS. A view
rem     with the same name as the earlier table has been added for integrator
rem     backward compatability.
rem    
rem
rem  COLUMNS
rem
rem     USER_NAME         - the name of the user that owns the preference.
rem                         A "*" is used to store the default preferences
rem                         for all users.
rem
rem     SUBTAB_NAME       - the name of the subtab the prefs apply to.
rem
rem     DISPLAY_ORDER     - the order in which a given metric is shown in
rem                         the UI.
rem
rem     COLUMN_ID_GUID    - the column identifier when using GUIDs for
rem                         colum_id (e.g. metric_guid). When this is used
rem                         the column_id SHOULD BE NULL.
rem
rem     COLUMN_ID         - the column identifier when not using GUIDs for
rem                         column_id (e.g. property names, generic columns).
rem                         When this is used, the column_id_guid SHOULD BE
rem                         NULL.
rem
rem     COLUMN_TYPE       - the column type stored. Sub-Folder owners using
rem                         this table may overwrite with their own codes here.
rem                         The following are the codes used by the framework:
rem                              0 - metric_guid
rem                              1 - properties
rem                              2 - generic columns (e.g. "policy violations")
rem
rem  NOTES
rem

CREATE TABLE MGMT_USER_SUBTAB_COL_PREFS
    (user_name          VARCHAR2(256)   NOT NULL,
     subtab_name        VARCHAR2(64)   NOT NULL,
     display_order      NUMBER(2) DEFAULT 0 NOT NULL,
     column_id_guid     RAW(16) DEFAULT NULL,
     column_id          VARCHAR2(256) DEFAULT NULL,
     column_type        NUMBER(1) DEFAULT 0 NOT NULL)
  MONITORING;

ALTER TABLE MGMT_USER_SUBTAB_COL_PREFS
   ADD CONSTRAINT user_subtab_col_prefs_pk
       PRIMARY KEY (user_name, subtab_name, display_order)
       USING INDEX;

CREATE OR REPLACE TYPE MGMT_SUBTAB_PREF AS OBJECT 
(
  column_id       VARCHAR2(256),
  column_id_guid  VARCHAR2(32),
  column_type     NUMBER(1),
  display_order   NUMBER(2)
);
/

CREATE OR REPLACE TYPE MGMT_SUBTAB_PREF_ARRAY AS TABLE OF MGMT_SUBTAB_PREF;
/

DECLARE
  WILDCARD_USER_NAME_ALIAS        CONSTANT VARCHAR2(2) := '*';

  MGMT_USER_PREF_METRIC_COL_TP    CONSTANT NUMBER(1) := 0;
  MGMT_USER_PREF_PROPERTY_COL_TP  CONSTANT NUMBER(1) := 1;
  MGMT_USER_PREF_GENERIC_COL_TP   CONSTANT NUMBER(1) := 2;
  
  -- Constants used to refer to the generic columns in subtabs (if modifying
  -- these values, please update their java counterparts)
  MGMT_USER_PREF_AVAIL_COL         CONSTANT VARCHAR2(20) := 'availability';
  MGMT_USER_PREF_ALERTS_COL        CONSTANT VARCHAR2(20) := 'alerts';
  MGMT_USER_PREF_POL_VIOL_COL      CONSTANT VARCHAR2(20) := 'policyviolation';
  MGMT_USER_PREF_POL_COMP_COLUMN   CONSTANT VARCHAR2(20) := 'policycompliance';

  -- local variables
  -- default generic columns
  default_columns MGMT_SUBTAB_PREF_ARRAY := MGMT_SUBTAB_PREF_ARRAY();
  columns_count NUMBER; 
BEGIN

  -- set default values 
  default_columns.extend();
  default_columns(1) := MGMT_SUBTAB_PREF(column_id => MGMT_USER_PREF_AVAIL_COL,
                                             column_id_guid => NULL,
                                             column_type => MGMT_USER_PREF_GENERIC_COL_TP,
                                             display_order => 1);
  default_columns.extend();
  default_columns(2) := MGMT_SUBTAB_PREF(column_id => MGMT_USER_PREF_ALERTS_COL,
                                         column_id_guid => NULL,
                                         column_type => MGMT_USER_PREF_GENERIC_COL_TP,
                                         display_order => 2);
  default_columns.extend();
  default_columns(3) := MGMT_SUBTAB_PREF(column_id => MGMT_USER_PREF_POL_VIOL_COL,
                                         column_id_guid => NULL,
                                          column_type => MGMT_USER_PREF_GENERIC_COL_TP,
                                          display_order => 3);
  default_columns.extend();
  default_columns(4) := MGMT_SUBTAB_PREF(column_id => MGMT_USER_PREF_POL_COMP_COLUMN,
                                         column_id_guid => NULL,
                                         column_type => MGMT_USER_PREF_GENERIC_COL_TP,
                                             display_order => 4);
  -- Move over current user preferences for all users, but not the default
  -- for the subtab (the new defaults will be inserted below). Leave space
  -- in column ordering so that the generic columns may be added.

  columns_count := default_columns.COUNT + 1; 

  INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, display_order,
                                         column_id_guid)
       SELECT user_name, target_type, display_order + columns_count, metric_guid
         FROM mgmt_user_type_metric_prefs
        WHERE user_name != WILDCARD_USER_NAME_ALIAS;

  -- Add generic columns to saved preferences of users for specific target
  -- types.

  FOR i IN 1..default_columns.COUNT
  LOOP

  INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, display_order,
                                         column_id, column_type)
     SELECT UNIQUE user_name, target_type, i, default_columns(i).column_id,
                   default_columns(i).column_type 
       FROM mgmt_user_type_metric_prefs
      WHERE user_name != WILDCARD_USER_NAME_ALIAS;
  END LOOP;
  COMMIT;
END;
/

  -- Making a call to v102010/preferences_post_creation.sql for 
  -- this is done to have consistency in data when installing fresh and installing the upgrade.
  -- Inserting default / generic columns to folder tabs (Groups, Systems etc).
  @&EM_SQL_ROOT/core/v102010/preferences_post_creation.sql
   
DECLARE
  -- Constants used to store prefs for subtabs
  MGMT_USER_PREF_ALL_TARGETS      CONSTANT VARCHAR2(20) := 'All Targets';
  MGMT_USER_PREF_SYSTEMS          CONSTANT VARCHAR2(20) := 'Systems';
  MGMT_USER_PREF_SERVICES         CONSTANT VARCHAR2(20) := 'Services';
  
  -- local variables
  num_new_folders NUMBER := 2;
BEGIN

  -- update user folders to include systems and services
  -- update the display order for All Targets so System and Services can be added before 
  UPDATE mgmt_user_folders SET display_order = display_order + num_new_folders
   WHERE folder_name = MGMT_USER_PREF_ALL_TARGETS;

  -- insert System folder for all users
  INSERT INTO mgmt_user_folders(user_name, folder_name, display_order)
    SELECT UNIQUE user_name, MGMT_USER_PREF_SYSTEMS, display_order - num_new_folders
       FROM mgmt_user_folders
      WHERE folder_name = MGMT_USER_PREF_ALL_TARGETS;

  -- insert Services folder for all users
  INSERT INTO mgmt_user_folders(user_name, folder_name, display_order)
    SELECT UNIQUE user_name, MGMT_USER_PREF_SERVICES, display_order - 1 
       FROM mgmt_user_folders
      WHERE folder_name = MGMT_USER_PREF_ALL_TARGETS;
  COMMIT;
END;
/

Rem Now drop the MGMT_USER_TYPE_METRIC_PREFS table, as a view with this name
Rem will be created...
DROP TABLE mgmt_user_type_metric_prefs;