-- MS SQL-Server Table Script - XConnect Ver 2
-- 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

-- 7/7/2006
-- Added fields and tables for XConnect ver 2
-- Table 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


-- VERSION=4
USE XC_DATA
GO

ALTER TABLE dbo.XC_SITESENSORS
ADD
  OFFSET FLOAT(53),
  SHIFT FLOAT(53),
  RIGHT_DIGIT INT;
GO

CREATE TABLE dbo.XC_VER
( VERSION INT );
GO

INSERT INTO dbo.XC_VER VALUES ( 4 );
GO

-- VERSION=5
USE XC_DATA
GO

-- PROMPT Creating Table 'XC_RTUQC'
CREATE TABLE dbo.XC_RTUQC
(
    STATION_ID          VARCHAR(20) NOT NULL   ,
    TIME_TAG            DATETIME NOT NULL      ,
    RX_TX               VARCHAR(1) NOT NULL    ,
    PORT                VARCHAR(30)            ,
    SEQUENCENUM         FLOAT(53)              ,
    OPCODE              FLOAT(53)              ,
    STATUS              INT                    ,
    RETRIES             INT                    ,
    GOOD                INT                    ,
    BAD                 INT                    ,
    CRC                 INT                    ,
    BATTERY_VOLTAGE     FLOAT(53)              ,
    SPARE1              FLOAT(53)              ,
    SPARE2              FLOAT(53)              ,
    CONSTRAINT RTU_PK PRIMARY KEY (
        STATION_ID,
        TIME_TAG,
        RX_TX
    )
);
GO

INSERT INTO dbo.XC_VER VALUES ( 5 );
GO


-- VERSION=6
USE XC_DATA
GO

ALTER TABLE dbo.XC_SITES
ADD
    COEFFICIENT1 FLOAT(53),
    COEFFICIENT2 FLOAT(53),
    COEFFICIENT3 FLOAT(53);
GO

ALTER TABLE dbo.XC_SITESENSORS
ADD
    COEFFICIENT1 FLOAT(53),
    COEFFICIENT2 FLOAT(53),
    COEFFICIENT3 FLOAT(53),
    COEFFICIENT4 FLOAT(53),
    COEFFICIENT5 FLOAT(53);
GO

INSERT INTO dbo.XC_VER VALUES ( 6 );
GO


-- VERSION=100 - Supports XConnect Version 2
USE XC_DATA;
GO

CREATE TABLE dbo.XC_POLLGROUPS(
	GROUP_NUM VARCHAR(2)			NOT NULL ,
	ENABLED VARCHAR(1)			NOT NULL ,
	STATION_LIST TEXT			NULL ,
  BASE_TIME INTEGER       NULL ,
  INTERVAL INTEGER      NULL ,
	POLL_TYPE INT				NULL ,
	CONTROL VARCHAR(128)			NULL ,
	CONTROL_ONLY VARCHAR(1)			NULL ,
	TIME_SYNC INT				NULL ,
	TIME_SYNC_PORT VARCHAR(5)		NULL ,
	TIME_LIMIT DATETIME			NULL ,
	OVERRIDE_ORDER VARCHAR(1)		NULL ,
	DESCRIPTION VARCHAR(30)			NULL ,
	CLIENTS VARCHAR(255)			NULL ,
	LOG_FILE VARCHAR(20)			NULL ,
	LOG_FILE_PATH VARCHAR(50)		NULL ,
	OWNER  VARCHAR(20)			NULL ,
        CONSTRAINT POLLGROUPS_PK PRIMARY KEY (GROUP_NUM)
)
GO

CREATE TABLE dbo.XC_LOG(
	TIME_TAG DATETIME			NOT NULL ,
	SOURCE VARCHAR(20)			NOT NULL ,
	DESCRIPTION VARCHAR(128)		NULL ,
	CONSTRAINT LOG_PK PRIMARY KEY (TIME_TAG, SOURCE)
);
GO

CREATE TABLE dbo.XC_USERS(
	USER_NAME VARCHAR(15)			NOT NULL ,
	PASSWORD VARCHAR(64)			NOT NULL ,
	DESCRIPTION VARCHAR(64)			NULL ,
	ACCESS VARCHAR(30)			NULL ,
	CONSTRAINT USERS_PK PRIMARY KEY (USER_NAME)
);
GO

CREATE TABLE dbo.XC_TEMPLATES(
	NAME VARCHAR(45)			NOT NULL ,
	TYPE INTEGER				NULL ,
        CONSTRAINT TEMPLATES_PK PRIMARY KEY (NAME)
);
GO

CREATE TABLE dbo.XC_RECORDIDS(
	STE_STATION_ID VARCHAR(30)		NOT NULL ,
	RECORD_ID VARCHAR(10)			NOT NULL ,
	SENSORS VARCHAR(255)			NULL ,
        CONSTRAINT RECORDIDS_PK PRIMARY KEY (STE_STATION_ID , RECORD_ID)
);
GO

CREATE TABLE dbo.XC_SITESENSORSDECODE(
	TOPIC VARCHAR(25)			NOT NULL ,
	STATION VARCHAR(30)			NOT NULL ,
	SENSOR VARCHAR(15)			NOT NULL ,
	SEQNUM INTEGER				NOT NULL ,
	ENABLED VARCHAR(1)			NOT NULL ,
	FORMAT VARCHAR(10)			NULL ,
	RELATIVE_TIME VARCHAR(1) 		NULL ,
	RELATIVE_POS VARCHAR(1)			NULL ,
	ADD_BASE VARCHAR(1)			NULL ,
	BYTE_SIZE INTEGER			NULL ,
	BIT_SIZE INTEGER			NULL ,
	BOUND1 INTEGER				NULL ,
	BOUND2 INTEGER				NULL ,
	BLOCK_ID VARCHAR(50)			NULL ,
	BYTE_POS INTEGER			NULL ,
	BIT_POS INTEGER				NULL ,
	NEXT INTEGER				NULL ,
	BIT_NEXT INTEGER			NULL ,
	AMOUNT INTEGER				NULL ,
	DELTA_TIME INTEGER			NULL ,
	OFFSET_TIME DATETIME			NULL ,
	ROUNDOFF_TIME DATETIME			NULL ,
	SKIP_BLANK INTEGER			NULL ,
	SKIP_COMMA INTEGER			NULL ,
	SKIP_LF INTEGER				NULL ,
	IGNORE_CASE  VARCHAR(1)			NULL ,
	CONSTRAINT SITESENSORSDECODE_PK PRIMARY KEY (TOPIC, SENSOR, STATION, SEQNUM)
);
GO

-- XC_SITESENSORS
ALTER TABLE dbo.XC_SITESENSORS
	DROP CONSTRAINT SITSEN_STE_FK;
GO

ALTER TABLE dbo.XC_SITESENSORS
	ALTER COLUMN STE_STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_SITESENSORS
	ALTER COLUMN SENSORNAME VARCHAR(15) NOT NULL;
GO

ALTER TABLE dbo.XC_SITESENSORS
	ALTER COLUMN TEXT_LIMIT VARCHAR(32) NULL;
GO

ALTER TABLE dbo.XC_SITESENSORS
	ALTER COLUMN ALERT_ID INT NULL;
GO

ALTER TABLE dbo.XC_SITESENSORS
ADD
    TAGNAME VARCHAR(15)			NULL ,
    RAW VARCHAR(30)			NULL ,
    LEFT_DIGIT INT 		        NULL ,
    IS_TEXT VARCHAR(1)			NULL ,
    ROUNDOFF_TIME DATETIME		NULL ,
    DATA_STORE_INTERVAL DATETIME	NULL ,
    UNITS VARCHAR(10)			NULL ,
    TEMPLATE_NAME VARCHAR(15)		NULL ,
    STATUS INT				NULL ,
    STD_DEV_LIMIT FLOAT(53)		NULL ,
    STD_DEV_ALARM INT			NULL ,
    STD_DEV_INTERVAL FLOAT(53)		NULL ,
    AVG_LIMIT FLOAT(53)			NULL ,
    AVG_ALARM INT			NULL ,
    AVG_INTERVAL FLOAT(53)		NULL ,
    REFERENCE_1 FLOAT(53)		NULL ,
    REFERENCE_2 FLOAT(53)		NULL ,
    DATA_EXCH_NUM VARCHAR(100)		NULL ;
GO

ALTER TABLE dbo.XC_SITES
	DROP CONSTRAINT STE_PK;
GO

ALTER TABLE dbo.XC_SITES
	ALTER COLUMN STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_SITES
	ADD CONSTRAINT STE_PK PRIMARY KEY (STATION_ID);
GO

ALTER TABLE dbo.XC_SITES
	ALTER COLUMN STATE VARCHAR(32) NULL;
GO

ALTER TABLE dbo.XC_SITES
	ALTER COLUMN SETUP_FILE_NAME VARCHAR(255) NULL;
GO

ALTER TABLE dbo.XC_SITES
	ALTER COLUMN PICTURE_FILE_NAME VARCHAR(255) NULL;
GO

ALTER TABLE dbo.XC_SITES
ADD
    SITE_TYPE INT 			NULL ,
    STATUS INT				NULL ,
    IPADDRESS VARCHAR(20)		NULL ,
    RADIO_PATH_LIST VARCHAR(30)		NULL ,
    PHONE_NUMBER_LIST VARCHAR(40)	NULL ,
    PHONE_NUMBER2 VARCHAR(20)		NULL ,
    USER_NAME VARCHAR(10)		NULL ,
    PASSWORD VARCHAR(10)		NULL ,
    COMPORT_LIST VARCHAR(15)		NULL ,
    ACKDELAY_LIST VARCHAR(10)		NULL ,
    RESPONSE_TIMEOUT_LIST VARCHAR(10)   NULL ,
    POLL_TYPE INT 			NULL ,
    LOGIN_REQD VARCHAR(1)		NULL ,
    QUICK_PROTOCOL VARCHAR(1)		NULL ,
    AUTO_PROTOCOL VARCHAR(1) 		NULL ,
    CONCENTRATOR VARCHAR(1) 		NULL ,
    HOURS_AHEAD FLOAT(53) 		NULL ,
    RDI_BASE_TIME DATETIME 		NULL ,
    RDI_INTERVAL DATETIME 		NULL ,
    RDI_STAYON DATETIME 		NULL ,
    TEMPLATE_NAME VARCHAR(30)		NULL ,
    SITE_LONG_NAME VARCHAR(50)		NULL ,
    SITE_CODE INT			NULL ,
    USGS_ID VARCHAR(10)			NULL ,
    RESERVOIR VARCHAR(32)		NULL ,
    RIVER_MILE FLOAT(53)		NULL ,
    MODEM_INIT_STRING VARCHAR(20)	NULL ,
    MODEM_HANGUP_STRING VARCHAR(20)	NULL ,
    LAST_POLL DATETIME			NULL ;
GO

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

ALTER TABLE dbo.XC_DATA1
	ALTER COLUMN STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_DATA1
	ALTER COLUMN SENSORNAME VARCHAR(15) NOT NULL;
GO

ALTER TABLE dbo.XC_DATA1
ADD
	STD_DEV_FLAG INT		NULL ,
	AVG_FLAG INT			NULL ;
GO

ALTER TABLE dbo.XC_GOESQC
	ALTER COLUMN STATION_ID VARCHAR(30) NULL;
GO

ALTER TABLE dbo.XC_RTUQC
	ALTER COLUMN STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_PPDATA1
	ALTER COLUMN STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_PPDATA1
	ALTER COLUMN SENSORNAME VARCHAR(15) NOT NULL;
GO

ALTER TABLE dbo.XC_PPLIST
	ALTER COLUMN STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_PPLIST
	ALTER COLUMN SENSORNAME VARCHAR(15) NOT NULL;
GO

ALTER TABLE dbo.XC_TXTDATA1
	ALTER COLUMN STATION_ID VARCHAR(30) NOT NULL;
GO

ALTER TABLE dbo.XC_TXTDATA1
	ALTER COLUMN SENSORNAME VARCHAR(15) NOT NULL;
GO

INSERT INTO dbo.XC_VER VALUES ( 100 );
GO

ALTER TABLE dbo.XC_SITES
ADD
  MBDEVICE_ID          CHAR(4) NULL,
  MBCONSEC_REGISTER    CHAR(1) NULL,
  MBBASE_REGISTER      INTEGER NULL,
  MBREGISTER_TYPE      INTEGER NULL,
  MBREGISTER_COUNT     INTEGER NULL,
  MSGPARITY            INTEGER NULL,
  IMEI_ID              CHAR(15) NULL,
  SMS_ID               CHAR(15) NULL;
GO

ALTER TABLE dbo.XC_SITESENSORS
ADD
  MBBASE_REGISTER      INTEGER NULL,
  MBREGISTER_TYPE      INTEGER NULL,
  MBTAG_TYPE           INTEGER NULL,
  MBMSW                INTEGER NULL;
GO

INSERT INTO dbo.XC_VER VALUES ( 101 );
GO

ALTER TABLE dbo.XC_SITES
ADD
  LOG_FILE_NAME        VARCHAR(255) NULL;
GO


ALTER TABLE dbo.XC_SITESENSORSDECODE
ADD
  SKIP_PLUS            INTEGER NULL;
GO

INSERT INTO dbo.XC_VER VALUES ( 102 );
GO

CREATE TABLE dbo.xc_lastvalues(
  STATION_ID           TEXT(30) NOT NULL,
  SENSORNAME           TEXT(15) NOT NULL,
  TIME_TAG             DATETIME NOT NULL,
  IS_TEXT              TEXT(7) NULL,
  NUM_VALUE            FLOAT NULL,
  TEXT_VALUE           TEXT(255) NOT NULL,
  PRIMARY KEY(station_id,sensorname)
)
;
GO

INSERT INTO dbo.XC_VER VALUES ( 103 );
GO
