-- Oracle Table Script
-- This script is only to update table DDL from older version

-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- Usage:
-- 1) Check XC_VER
-- 2) The largest integer in XC_VER is the current version
-- 3) Execute from the next version.  e.g. 4 is the largest number in XC_VER, start from --VERSION = 5
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


-- 11/3/03
-- Add <FIELD>SHIFT</FIELD>, <FIELD>OFFSET</FIELD> and <FIELD>RIGHT_DIGIT</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Create <TABLE>XC_VER</TABLE> as version 4

-- 12/26/03
-- Add <TABLE>XC_RTUQC</TABLE>
-- Make <TABLE>XC_VER</TABLE> as version 5

-- 02/20/04
-- Add <TABLE>XC_SITES</TABLE> <FIELDS>COEFFICIENT 1-3</FIELDS>
-- Add <TABLE>XC_SITESENSORS</TABLE> <FIELDS>COEFFICIENT 1-5</FIELDS>
-- Make <TABLE>XC_VER</TABLE> as version 6

-- 10/25/06
-- Add <FIELD>STD_DEV_LIMIT</FIELD>, <FIELD>STD_DEV_ALARM</FIELD> and <FIELD>STD_DEV_INTERVAL</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>AVG_LIMIT</FIELD>, <FIELD>AVG_ALARM</FIELD> and <FIELD>AVG_INTERVAL</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>REFERENCE_1</FIELD>, <FIELD>REFERENCE_2</FIELD> and <FIELD>DATA_EXCH_NUM</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>DATA_EXCH_NUM to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>MIKE11_LABEL_1</FIELD>, <FIELD>MIKE11_LABEL_2</FIELD> to <TABLE>XC_SITESENSORS</TABLE>
-- Add <FIELD>STD_DEV_FLAG</FIELD>, <FIELD>AVG_FLAG</FIELD> to <TABLE>XC_DATA1</TABLE>
-- Make <TABLE>XC_VER</TABLE> as version 7

-- 06/03/09
-- Add <Field>NO_ALARM_INTERVAL</Field> to <Table>XC_SITESENSORS</Table>
-- Make <Table>XC_VER</Table> as version 8

-- 02/10/10
-- XConnect version 2 started.
-- Add -->
-- Make <Table>XC_VER</Table> as version 100

-- 08/07/2010
-- Add <Field>TEMPLATE_FLAG</Field>, <Field>MBDEVICE_ID</Field>, <Field>MBCONSEC_REGISTER</Field> to <Table>XC_SITES</Table>
-- Add <Field>MBBASE_REGISTER</Field>, <Field>MBREGISTER_TYPE</Field>, <Field>MBREGISTER_COUNT</Field> to <Table>XC_SITES</Table>
-- Add <Field>MSGPARITY</Field> to <Table>XC_SITES</Table>
-- Add <Field>NO_ALARM_INTERVAL</Field>, <Field>MBBASE_REGISTER</Field>, <Field>MBREGISTER_TYPE</Field> to <Table>XC_SITESENSORS</Table>
-- Add <Field>MBTAG_TYPE</Field>, <Field>MBMSW</Field> to <Table>XC_SITESENSORS</Table>
-- Add <Field>IMEI_ID</Field>, <Field>SMS_ID</Field> to <Table>XC_SITESENSORS</Table>
-- Make <Table>XC_VER</Table> as version 101

-- 06/12/2013
-- Add <Field>LOG_FILE_NAME</Field> to <Table>XC_SITES</Table>
-- Add <Field>SKIP_PLUS</Field> to <Table>XC_SITESSENSORSDECODE</Table>
-- Make <Table>XC_VER</Table> as version 102

-- 07/03/2013
-- Add <Table>XC_LASTVALUES</Table>
-- Changed <Field>OFFSET_TIME</Field>, <Field>ROUNDOFF_TIME</Field> in <Table>XC_SITESENSORSDECODE</Table> changd for DATETIME to INTEGER type
-- Renamed <Field>RAW</Field> to <Field>RawList</Field> in <Table>XC_SITESENSORSDECODE</Table>
-- Renamed <Field>USER</Field> to <Field>USER_ACCESS</Field> in <Table>XC_USERS</Table>
-- Renamed <Field>NAME</Field> to <Field>TEMPLATE_NAME</Field> and <Field>TYPE</Field> to <Field>TEMPLATE_TYPE</Field> in <Table>XC_TEMPLATES</Table>
-- Make <Table>XC_VER</Table> as version 103



SET ECHO ON
SPOOL TableUpdate.OUT

undefine tbl_owner
define tbl_owner = &1

-- VERSION=100

-- ROMPT Creating Table 'XC_POLLGROUPS'
CREATE TABLE &&tbl_owner..XC_POLLGROUPS
(
    GROUP_NUM VARCHAR2 (2) NOT NULL,
    ENABLED VARCHAR2 (1) NOT NULL,
    STATION_LIST LONG NULL,
    BASE_TIME NUMBER (6) NULL,
    INTERVAL NUMBER (6) NULL,
    POLL_TYPE NUMBER (6) NULL,
    CONTROL VARCHAR2 (128) NULL,
    CONTROL_ONLY VARCHAR2 (1) NULL,
    TIME_SYNC NUMBER (6) NULL,
    TIME_SYNC_PORT VARCHAR2 (5) NULL,
    TIME_LIMIT DATE NULL,
    OVERRIDE_ORDER VARCHAR2 (1) NULL,
    DESCRIPTION VARCHAR2 (30) NULL,
    CLIENTS VARCHAR2 (255) NULL,
    LOG_FILE VARCHAR2 (20) NULL,
    LOG_FILE_PATH VARCHAR2 (50) NULL,
    OWNER VARCHAR2 (20) NULL
);

-- ROMPT Creating Table 'XC_LOG'
CREATE TABLE &&tbl_owner..XC_LOG
(
    TIME_TAG DATE NOT NULL,
    SOURCE VARCHAR2 (20) NOT NULL,
    DESCRIPTION VARCHAR2 (128) NULL
);

-- ROMPT Creating Table 'XC_RECORDIDS'
CREATE TABLE &&tbl_owner..XC_RECORDIDS
(
    STE_STATION_ID VARCHAR2 (30) NOT NULL,
    RECORD_ID VARCHAR2 (10) NOT NULL,
    SENSORS VARCHAR2 (255) NULL
);

-- ROMPT Creating Table 'XC_SITESENSORSDECODE'
CREATE TABLE &&tbl_owner..XC_SITESENSORSDECODE
(
    TOPIC VARCHAR2 (25) NOT NULL,
    STATION VARCHAR2 (30) NOT NULL,
    SENSOR VARCHAR2 (15) NOT NULL,
    SEQNUM NUMBER (6) NOT NULL,
    ENABLED VARCHAR2 (1) NOT NULL,
    FORMAT VARCHAR2 (10) NULL,
    RELATIVE_TIME VARCHAR2 (1) NULL,
    RELATIVE_POS VARCHAR2 (1) NULL,
    ADD_BASE VARCHAR2 (1) NULL,
    BYTE_SIZE NUMBER (6) NULL,
    BIT_SIZE NUMBER (6) NULL,
    BOUND1 NUMBER (6) NULL,
    BOUND2 NUMBER (6) NULL,
    BLOCK_ID VARCHAR2 (50) NULL,
    BYTE_POS NUMBER (6) NULL,
    BIT_POS NUMBER (6) NULL,
    NEXT NUMBER (6) NULL,
    BIT_NEXT NUMBER (6) NULL,
    AMOUNT NUMBER (6) NULL,
    DELTA_TIME NUMBER (6) NULL,
    OFFSET_TIME NUMBER (6) NULL,
    ROUNDOFF_TIME NUMBER (6) NULL,
    SKIP_BLANK NUMBER (6) NULL,
    SKIP_COMMA NUMBER (6) NULL,
    SKIP_LF NUMBER (6) NULL,
    IGNORE_CASE VARCHAR2 (1) NULL
);

-- ROMPT Creating Table 'XC_TEMPLATES'
CREATE TABLE &&tbl_owner..XC_TEMPLATES
(
    NAME VARCHAR2 (45) NOT NULL,
    TYPE NUMBER (6) NULL
);

-- ROMPT Creating Table 'XC_USERS'
CREATE TABLE &&tbl_owner..XC_USERS
(
    USER_NAME VARCHAR2 (15) NOT NULL,
    PASSWORD VARCHAR2 (64) NOT NULL,
    DESCRIPTION VARCHAR2 (64) NULL,
    USER_ACCESS VARCHAR2 (30) NULL
);

-- PROMPT Creating Primary Key on 'XC_LOG'
ALTER TABLE &&tbl_owner..XC_LOG
 ADD CONSTRAINT LOG_PK PRIMARY KEY (TIME_TAG, SOURCE);
-- PROMPT Creating Primary Key on 'XC_USERS'
ALTER TABLE &&tbl_owner..XC_USERS
 ADD CONSTRAINT USERS_PK PRIMARY KEY (USER_NAME);
-- PROMPT Creating Primary Key on 'XC_TEMPLATES'
ALTER TABLE &&tbl_owner..XC_TEMPLATES
 ADD CONSTRAINT TEMPLATES_PK PRIMARY KEY (NAME);
-- PROMPT Creating Primary Key on 'XC_RECORDIDS'
ALTER TABLE &&tbl_owner..XC_RECORDIDS
 ADD CONSTRAINT RECORDIDS_PK PRIMARY KEY (STE_STATION_ID , RECORD_ID);
-- PROMPT Creating Primary Key on 'XC_SITESENSORSDECODE'
ALTER TABLE &&tbl_owner..XC_SITESENSORSDECODE
 ADD CONSTRAINT SITESENSORSDECODE_PK PRIMARY KEY (TOPIC, SENSOR, STATION, SEQNUM);
-- PROMPT Creating Primary Key on 'XC_POLLGROUPS'
ALTER TABLE &&tbl_owner..XC_POLLGROUPS
 ADD CONSTRAINT POLLGROUPS_PK PRIMARY KEY (GROUP_NUM);

ALTER TABLE &&tbl_owner..XC_SITESENSORS
	DROP CONSTRAINT SITSEN_STE_FK;

ALTER TABLE &&tbl_owner..XC_SITESENSORS
	MODIFY STE_STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_SITESENSORS
	MODIFY SENSORNAME VARCHAR2(15);

ALTER TABLE &&tbl_owner..XC_SITESENSORS
	MODIFY TEXT_LIMIT VARCHAR2(32);

ALTER TABLE &&tbl_owner..XC_SITESENSORS
	MODIFY ALERT_ID NUMBER(6);

ALTER TABLE &&tbl_owner..XC_SITESENSORS
ADD (
    TAGNAME VARCHAR2(15) NULL,
    RAWLIST VARCHAR2(30) NULL,
    LEFT_DIGIT NUMBER(6) NULL,
    IS_TEXT VARCHAR2(1) NULL,
    ROUNDOFF_TIME NUMBER (6) NULL,
    DATA_STORE_INTERVAL NUMBER (6) NULL,
    UNITS VARCHAR2(10) NULL,
    TEMPLATE_NAME VARCHAR2(15) NULL,
    STATUS NUMBER(6) NULL,
    STD_DEV_LIMIT FLOAT(126) NULL,
    STD_DEV_ALARM NUMBER(6) NULL,
    STD_DEV_INTERVAL FLOAT(126) NULL,
    AVG_LIMIT FLOAT(126) NULL,
    AVG_ALARM NUMBER(6) NULL,
    AVG_INTERVAL FLOAT(126) NULL,
    REFERENCE_1 FLOAT(126)  NULL,
    REFERENCE_2 FLOAT(126)  NULL,
    DATA_EXCH_NUM VARCHAR2(100) NULL,
    MIKE11_LABEL_1 VARCHAR(50) NULL,
    MIKE11_LABEL_2 VARCHAR(50) NULL,
    NO_ALARM_INTERVAL FLOAT (126) NULL,
    MBBASE_REGISTER NUMBER (6) NULL,
    MBREGISTER_TYPE NUMBER (6) NULL,
    MBTAG_TYPE NUMBER (6) NULL,
    MBMSW NUMBER (6) NULL
);

ALTER TABLE &&tbl_owner..XC_SITES
	DROP CONSTRAINT STE_PK;

ALTER TABLE &&tbl_owner..XC_SITES
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_SITES
	ADD CONSTRAINT STE_PK PRIMARY KEY (STATION_ID);

ALTER TABLE &&tbl_owner..XC_SITES
	MODIFY STATE VARCHAR2(32);

ALTER TABLE &&tbl_owner..XC_SITES
	MODIFY SETUP_FILE_NAME VARCHAR2(255);

ALTER TABLE &&tbl_owner..XC_SITES
	MODIFY PICTURE_FILE_NAME VARCHAR2(255);

ALTER TABLE &&tbl_owner..XC_SITES
ADD (
    SITE_TYPE NUMBER(6) NULL,
    STATUS NUMBER(6) NULL,
    IPADDRESS VARCHAR2(20) NULL,
    RADIO_PATH_LIST VARCHAR2(30) NULL,
    PHONE_NUMBER_LIST VARCHAR2(40) NULL,
    PHONE_NUMBER2 VARCHAR2(20) NULL,
    USER_NAME VARCHAR2(10) NULL,
    PASSWORD VARCHAR2(10) NULL,
    COMPORT_LIST VARCHAR2(15) NULL,
    ACKDELAY_LIST VARCHAR2(10) NULL,
    RESPONSE_TIMEOUT_LIST VARCHAR2(10) NULL,
    POLL_TYPE NUMBER(6) NULL,
    LOGIN_REQD VARCHAR2(1) NULL,
    QUICK_PROTOCOL VARCHAR2(1) NULL,
    AUTO_PROTOCOL VARCHAR2(1) NULL,
    CONCENTRATOR VARCHAR2(1) NULL,
    HOURS_AHEAD FLOAT(126)    NULL,
    RDI_BASE_TIME DATE NULL,
    RDI_INTERVAL DATE NULL,
    RDI_STAYON DATE NULL,
    TEMPLATE_NAME VARCHAR2(30) NULL,
    SITE_LONG_NAME VARCHAR2(50) NULL,
    SITE_CODE NUMBER(6) NULL,
    USGS_ID VARCHAR2(10) NULL,
    RESERVOIR VARCHAR2(32)  NULL,
    RIVER_MILE FLOAT(126) NULL,
    MODEM_INIT_STRING VARCHAR2(20) NULL,
    MODEM_HANGUP_STRING VARCHAR2(20) NULL,
    LAST_POLL DATE NULL,
    TEMPLATE_FLAG NUMBER (6) NULL DEFAULT 1,
    MBDEVICE_ID  VARCHAR(4) NULL,
    MBCONSEC_REGISTER VARCHAR(1) NULL,
    MBBASE_REGISTER NUMBER (6) NULL,
    MBREGISTER_TYPE  NUMBER (6) NULL,
    MBREGISTER_COUNT NUMBER (6) NULL,
    MSGPARITY NUMBER (6) NULL,
    IMEI_ID VARCHAR2 (15) NULL,
    SMS_ID VARCHAR2 (15) NULL
 );

ALTER TABLE &&tbl_owner..XC_SITESENSORS
	ADD CONSTRAINT SITSEN_STE_FK FOREIGN KEY (STE_STATION_ID) REFERENCES &&tbl_owner..XC_SITES (STATION_ID);

ALTER TABLE &&tbl_owner..XC_DATA1
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_DATA1
	MODIFY SENSORNAME VARCHAR2(15);

ALTER TABLE &&tbl_owner..XC_DATA1
ADD	(
  STD_DEV_FLAG NUMBER(6) NULL ,
  AVG_FLAG NUMBER(6) NULL);

ALTER TABLE &&tbl_owner..XC_GOESQC
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_RTUQC
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_PPDATA1
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_PPDATA1
	MODIFY SENSORNAME VARCHAR2(15);

ALTER TABLE &&tbl_owner..XC_PPLIST
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_PPLIST
	MODIFY SENSORNAME VARCHAR2(15);

ALTER TABLE &&tbl_owner..XC_TXTDATA1
	MODIFY STATION_ID VARCHAR2(30);

ALTER TABLE &&tbl_owner..XC_TXTDATA1
	MODIFY SENSORNAME VARCHAR2(15);

INSERT INTO &&tbl_owner..XC_VER VALUES ( 100 );

-- XC_POLLGROUPS
DROP PUBLIC SYNONYM XC_POLLGROUPS;
CREATE PUBLIC SYNONYM XC_POLLGROUPS FOR &&tbl_owner..XC_POLLGROUPS;

GRANT SELECT ON &&tbl_owner..XC_POLLGROUPS TO XCUSERS;

GRANT SELECT ON &&tbl_owner..XC_POLLGROUPS TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_POLLGROUPS TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_POLLGROUPS TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_POLLGROUPS TO XCMANAGERS;

-- XC_LOG
DROP PUBLIC SYNONYM XC_LOG;
CREATE PUBLIC SYNONYM XC_LOG FOR &&tbl_owner..XC_LOG;

GRANT SELECT ON &&tbl_owner..XC_LOG TO XCUSERS;

GRANT SELECT ON &&tbl_owner..XC_LOG TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_LOG TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_LOG TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_LOG TO XCMANAGERS;

-- XC_RECORDIDS
DROP PUBLIC SYNONYM XC_RECORDIDS;
CREATE PUBLIC SYNONYM XC_RECORDIDS FOR &&tbl_owner..XC_RECORDIDS;

GRANT SELECT ON &&tbl_owner..XC_RECORDIDS TO XCUSERS;

GRANT SELECT ON &&tbl_owner..XC_RECORDIDS TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_RECORDIDS TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_RECORDIDS TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_RECORDIDS TO XCMANAGERS;

-- XC_SITESENSORSDECODE
DROP PUBLIC SYNONYM XC_SITESENSORSDECODE;
CREATE PUBLIC SYNONYM XC_SITESENSORSDECODE FOR &&tbl_owner..XC_SITESENSORSDECODE;

GRANT SELECT ON &&tbl_owner..XC_SITESENSORSDECODE TO XCUSERS;

GRANT SELECT ON &&tbl_owner..XC_SITESENSORSDECODE TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_SITESENSORSDECODE TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_SITESENSORSDECODE TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_SITESENSORSDECODE TO XCMANAGERS;

-- XC_TEMPLATES
DROP PUBLIC SYNONYM XC_TEMPLATES;
CREATE PUBLIC SYNONYM XC_TEMPLATES FOR &&tbl_owner..XC_TEMPLATES;

GRANT SELECT ON &&tbl_owner..XC_TEMPLATES TO XCUSERS;

GRANT SELECT ON &&tbl_owner..XC_TEMPLATES TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_TEMPLATES TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_TEMPLATES TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_TEMPLATES TO XCMANAGERS;

-- XC_USERS
DROP PUBLIC SYNONYM XC_USERS;
CREATE PUBLIC SYNONYM XC_USERS FOR &&tbl_owner..XC_USERS;

GRANT SELECT ON &&tbl_owner..XC_USERS TO XCUSERS;

GRANT SELECT ON &&tbl_owner..XC_USERS TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_USERS TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_USERS TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_USERS TO XCMANAGERS;

-- Version 101

ALTER TABLE &&tbl_owner..XC_SITES
ADD (
    TEMPLATE_FLAG NUMBER (6) NULL DEFAULT 1,
    MBDEVICE_ID  VARCHAR(4) NULL,
    MBCONSEC_REGISTER VARCHAR(1) NULL,
    MBBASE_REGISTER NUMBER (6) NULL,
    MBREGISTER_TYPE  NUMBER (6) NULL,
    MBREGISTER_COUNT NUMBER (6) NULL,
    MSGPARITY NUMBER (6) NULL,
    IMEI_ID VARCHAR2 (15) NULL,
    SMS_ID VARCHAR2 (15) NULL
 );

ALTER TABLE &&tbl_owner..XC_SITESENSORS
ADD (
    NO_ALARM_INTERVAL FLOAT (126) NULL,
    MBBASE_REGISTER NUMBER (6) NULL,
    MBREGISTER_TYPE NUMBER (6) NULL,
    MBTAG_TYPE NUMBER (6) NULL,
    MBMSW NUMBER (6) NULL
);

INSERT INTO &&tbl_owner..XC_VER VALUES ( 101 );

-- Version 102

ALTER TABLE &&tbl_owner..XC_SITES
ADD ( LOG_FILE_NAME VARCHAR2(255) NULL);

ALTER TABLE &&tbl_owner..XC_SITESENSORSDECODE
ADD (SKIP_PLUS NUMBER(6) NULL);

INSERT INTO &&tbl_owner..XC_VER VALUES ( 102 );


-- Version 103

-- PROMPT Creating Table 'XC_LASTVALUES'
CREATE TABLE &&tbl_owner..XC_LASTVALUES
 (
    STATION_ID VARCHAR2(30) NOT NULL,
    SENSORNAME VARCHAR2(15) NOT NULL,
    TIME_TAG DATE NOT NULL,
    IS_TEXT VARCHAR2 (1) NULL,
    NUM_VALUE FLOAT(126),
    TEXT_VALUE VARCHAR2(256) NOT NULL
 );

INSERT INTO &&tbl_owner..XC_VER VALUES ( 103 );

-- XC_LASTVALUES
DROP PUBLIC SYNONYM XC_LASTVALUES;
CREATE PUBLIC SYNONYM XC_LASTVALUES FOR &&tbl_owner..XC_LASTVALUES;

GRANT SELECT ON &&tbl_owner..XC_LASTVALUES TO XCLASTVALUES;

GRANT SELECT ON &&tbl_owner..XC_LASTVALUES TO XCMANAGERS;
GRANT INSERT ON &&tbl_owner..XC_LASTVALUES TO XCMANAGERS;
GRANT UPDATE ON &&tbl_owner..XC_LASTVALUES TO XCMANAGERS;
GRANT DELETE ON &&tbl_owner..XC_LASTVALUES TO XCMANAGERS;


COMMIT;


SPOOL OFF
