Rem DO NOT ADD HEADER IN THIS FILE SINCE chronos_schema_upgrade_cond.sql IS
Rem REFERENCING TO THIS FILE DEPENDING ON THE CONDITION.
Rem
Rem $Header: chronos_schema_upgrade.sql 18-jul-2005.11:00:22 chyu Exp $
Rem
Rem chronos_schema_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Updates the chronos tables for an upgrade from 10.1.0.3 to 10.1.0.4
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        07/18/05 - adding the upgrade header 
Rem    eporter     04/14/05 - Fix difference with raw table 
Rem    eporter     03/07/05 - Fix difference with upgraded and created tables 
Rem    eporter     02/16/05 - eporter_bug-4167002
Rem    skini       12/23/04 - Add EM_REPOS_USER to workaround SYS bug
Rem    eporter     11/23/04 - eporter_backport_10.1.0.3.0_3849061
Rem    eporter     11/18/04 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


ALTER TABLE &EM_REPOS_USER..MGMT_RT_METRICS_RAW ADD visitor_ip_num NUMBER DEFAULT NULL;

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1HOUR ADD visitor_subnet_num NUMBER DEFAULT 0;
ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1HOUR MODIFY visitor_subnet_num NOT NULL;

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1DAY ADD visitor_subnet_num NUMBER DEFAULT 0;
ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_1DAY MODIFY visitor_subnet_num NOT NULL;

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_DIST_1HOUR ADD visitor_subnet_num NUMBER DEFAULT 0;
ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_DIST_1HOUR MODIFY visitor_subnet_num NOT NULL;

ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_DIST_1DAY ADD visitor_subnet_num NUMBER DEFAULT 0;
ALTER TABLE &EM_REPOS_USER..MGMT_RT_DOMAIN_DIST_1DAY MODIFY visitor_subnet_num NOT NULL;


CREATE TABLE MGMT_RT_REGION_ENTRIES
(
  id                    NUMBER                  NOT NULL,
  min_ip                NUMBER                  NOT NULL,
  max_ip                NUMBER                  NOT NULL,
  domain                VARCHAR2(1024)          NOT NULL,
  ref_count             NUMBER                  NOT NULL
) MONITORING;


CREATE TABLE MGMT_RT_REGION_MAPPING
(
  id                    NUMBER                  NOT NULL,
  region_guid           RAW(16)                 NOT NULL
) MONITORING;


Rem
Rem Indices for MGMT_RT_REGION_ENTRIES table
Rem
ALTER TABLE &EM_REPOS_USER..MGMT_RT_REGION_ENTRIES
    ADD CONSTRAINT MGMT_RT_REGION_ENTRIES_PK
    PRIMARY KEY (id);
ALTER TABLE &EM_REPOS_USER..MGMT_RT_REGION_ENTRIES
    ADD CONSTRAINT MGMT_RT_REGION_ENTRIES_UN_DOM
    UNIQUE (domain);

create index IDX_REGION_MIN_IP on MGMT_RT_REGION_ENTRIES (MIN_IP);

Rem
Rem Indices for MGMT_RT_REGION_MAPPING table
Rem
ALTER TABLE &EM_REPOS_USER..MGMT_RT_REGION_MAPPING
    ADD CONSTRAINT MGMT_RT_REGION_MAPPING_PK
    PRIMARY KEY (id, region_guid);

create index MGMT_RT_REGION_MAPPING_ID_IDX on MGMT_RT_REGION_MAPPING (id);

ALTER TABLE &EM_REPOS_USER..MGMT_RT_REGION_MAPPING
   ADD CONSTRAINT MGMT_RT_REGION_MAPPING_FK
       FOREIGN KEY (region_guid)
       REFERENCES MGMT_RT_REGIONS (region_guid)
     ON DELETE CASCADE;

Rem Create a trigger so that raw entries will be deleted when they are no longer
Rem being used.  Having lots of entries will lower performance.

CREATE TRIGGER MGMT_RT_REGION_MAPPING_TR 
    AFTER DELETE ON MGMT_RT_REGION_MAPPING FOR EACH ROW
BEGIN
    UPDATE MGMT_RT_REGION_ENTRIES 
      SET ref_count = (ref_count - 1)
      WHERE id = :old.id;
    DELETE FROM MGMT_RT_REGION_ENTRIES
      WHERE ref_count <= 0;
END;
/

create index mgmt_rt_metrics_raw_ip_num_idx on mgmt_rt_metrics_raw (visitor_ip_num);