-- MS SQL-Server Table Script
-- Created from Oracle Script 
-- By Stanley Ye (sye@sutron.com)

-- Update:
-- 9/12/02
-- Change varchar2 to varchar
-- Change Number(...) to DECIMAL(...)

-- 9/18/02
-- Change <Field>Station_ID</Field> to primary key from Name with length 20
-- Change <Field>Unit_ID</Field> to normal Fields
-- Increase length of <Field>SETUP_FILE_NAME</Field> and <Field>PICTURE_FILE_NAME</Field> to 255

-- 9/24/02
-- Add <Field>CITY</Field> and <Field>DISTRICT</Field> with both length 32
-- Change <Field>REPORTING_TIME</Field> to DATE
-- Add <Field>PARITY</Field>, <Field>BUFFER_EMPTY</Field> to <Table>XC_GOESQC<Table>
 
-- 9/25/02
-- Add <Field>ACTUAL_LEN<Field> with NUMBER to <Table>XC_GOESQC<Table>

-- 9/30/02
-- Change NESDISID to <Field>NESDIS_ID<Field> in <Table>XC_GOESQC<Table>

-- 10/08/02
-- Add <Field>BATTERY_VOLTAGE<Field> in <Table>XC_GOESQC<Table>

-- 2/19/03
-- <Field>DESCRIPTION</Field> in <Table>XC_PP_INSTRUCTIONS</Table> can be NULL now

-- 04/10/03
-- Change DECIMAL(...) to INT or FLOAT(53)

CREATE TABLE XC_SITES
 (STATION_ID VARCHAR(20) NOT NULL
 ,ENABLED VARCHAR(1) NOT NULL
 ,UNIT_ID VARCHAR(15)
 ,SITE_COMMENT VARCHAR(32)
 ,INSTALLATION_DATE DATETIME
 ,AGENCY VARCHAR(32)
 ,COUNTRY VARCHAR(32)
 ,DISTRICT VARCHAR(32)
 ,CITY VARCHAR(32)
 ,STATE VARCHAR(20)
 ,COUNTY VARCHAR(32)
 ,BASIN VARCHAR(32)
 ,LATITUDE FLOAT(53)
 ,LONGITUDE FLOAT(53)
 ,ELEVATION FLOAT(53)
 ,WEIGHTING_FACTOR FLOAT(53)
 ,SATELLITE_ID VARCHAR(8)
 ,PRIMARY_CHANNEL INT
 ,RANDOM_CHANNEL INT
 ,REPORTING_TIME DATETIME
 ,REPORTING_INTERVAL FLOAT(53)
 ,LAST_UPDATE DATETIME
 ,TZONE_CODE VARCHAR(3)
 ,GMT_OFFSET FLOAT
 ,SHEF_ID VARCHAR(8)
 ,ALTERNATE_CHAR_ID_1 VARCHAR(10)
 ,ALTERNATE_CHAR_ID_2 VARCHAR(10)
 ,GPSTRING1 VARCHAR(20)
 ,GPSTRING2 VARCHAR(20)
 ,GPNUMBER1 FLOAT(53)
 ,GPNUMBER2 FLOAT(53)
 ,SETUP_FILE_NAME VARCHAR(55)
 ,PICTURE_FILE_NAME VARCHAR(55)
, CONSTRAINT STE_PK PRIMARY KEY(STATION_ID)
, CONSTRAINT AVCON_5863_ENABL_000 CHECK (ENABLED IN ('Y', 'N'))
, CONSTRAINT AVCON_5863_TIME__000 CHECK (GMT_OFFSET 
IN (0, -12, 11, -11, 12, 1, -10, 2, -9, 3, -8, 4, -7, 5, -6, 6, -5, 
7, -4, 8, -3, 9, -2, 10, -1))
);
GO

CREATE TABLE XC_SITESENSORS
 (STE_STATION_ID VARCHAR(20) NOT NULL
 ,SENSORNAME VARCHAR(12) NOT NULL
 ,ENABLED VARCHAR(1) NOT NULL
 ,DATA_TABLE VARCHAR(10) NOT NULL
 ,DESCRIPTION VARCHAR(32)
 ,MANUFACTURER VARCHAR(20)
 ,MODEL_NUMBER VARCHAR(20)
 ,SHEF_PE_CODE VARCHAR(7)
 ,PARAMETER_CODE VARCHAR(12)
 ,ALT_PARAMETER_CODE_1 VARCHAR(12)
 ,ALT_PARAMETER_CODE_2 VARCHAR(12)
 ,EQUATION VARCHAR(128)
 ,RATING_TABLE VARCHAR(128)
 ,HIGH_HIGH_LIMIT FLOAT(53)
 ,HIGH_HIGH_ALARM INT
 ,HIGH_LIMIT FLOAT(53)
 ,HIGH_ALARM INT
 ,LOW_LIMIT FLOAT(53)
 ,LOW_ALARM INT
 ,LOW_LOW_LIMIT FLOAT(53)
 ,LOW_LOW_ALARM INT
 ,ROC_LIMIT FLOAT(53)
 ,ROC_ALARM INT
 ,ROC_INTERVAL FLOAT(53)
 ,DEADBAND FLOAT(53)
 ,NO_CHANGE_INTERVAL FLOAT(53)
 ,NO_CHANGE_ALARM INT
 ,TEXT_LIMIT VARCHAR(9)
 ,TEXT_ALARM INT
 ,GPNUMBER1 FLOAT(53)
 ,GPNUMBER2 FLOAT(53)
 ,GPSTRING1 VARCHAR(20)
 ,GPSTRING2 VARCHAR(20)
 ,ALERT_ID FLOAT(53)
 ,ALERT_TYPE VARCHAR(10)
, CONSTRAINT SITSEN_PK PRIMARY KEY (SENSORNAME,STE_STATION_ID)
, CONSTRAINT AVCON_5866_DATA__000 CHECK (DATA_TABLE 
IN ('XC_DATA7', 'XC_DATA8', 'XC_DATA9', 'XC_DATA10', 'XC_DATA11', 
'XC_DATA1', 'XC_DATA2', 'XC_DATA12', 'XC_DATA3', 'XC_DATA4', 
'XC_DATA5', 'XC_DATA6'))
, CONSTRAINT AVCON_5866_ALERT_000 CHECK (ALERT_TYPE IN ('NORMAL', 'WIND RUN', 'WIND DIR'))
, CONSTRAINT SITSEN_STE_FK FOREIGN KEY (STE_STATION_ID) REFERENCES XC_SITES (STATION_ID)
 );
GO

CREATE TABLE XC_DATA1
 (STATION_ID VARCHAR(20) NOT NULL
 ,SENSORNAME VARCHAR(12) NOT NULL
 ,TIME_TAG DATETIME NOT NULL
 ,SOURCE VARCHAR(1) NOT NULL
 ,ORIG_VALUE FLOAT(53) NOT NULL
 ,ED_VALUE FLOAT(53)
 ,FLAG1 VARCHAR(1)
 ,FLAG2 VARCHAR(1)
 ,FLAG3 VARCHAR(1)
 ,FLAG4 VARCHAR(1)
 ,HIGH_HIGH_FLAG INT
 ,HIGH_FLAG INT
 ,LOW_FLAG INT
 ,LOW_LOW_FLAG INT
 ,ROC_FLAG INT
 ,NO_CHG_FLAG INT
, CONSTRAINT DAT1_PK PRIMARY KEY 
  (TIME_TAG
  ,SOURCE
  ,STATION_ID
  ,SENSORNAME)
 );
 GO

CREATE TABLE XC_GOESQC
 (NESDIS_ID VARCHAR(8) NOT NULL
 ,TIME_TAG DATETIME NOT NULL
 ,SOURCE VARCHAR(1) NOT NULL
 ,STATION_ID VARCHAR(20)
 ,FAILURE_CODE VARCHAR(1)
 ,SIG_STRENGTH FLOAT(53)
 ,FREQ_OFFSET FLOAT(53)
 ,MOD_INDEX VARCHAR(1)
 ,MOD_QUAL VARCHAR(1)
 ,CHANNEL INT
 ,SAT_LOC VARCHAR(1)
 ,UC_STATUS VARCHAR(2)
 ,MESSAGE_LEN INT
 ,ACTUAL_LEN INT
 ,PARITY INT
 ,BUFFER_EMPTY INT
 ,BATTERY_VOLTAGE FLOAT(53)
 ,SPARE1 FLOAT(53)
 ,SPARE2 FLOAT(53)
, CONSTRAINT GOESQC_PK PRIMARY KEY 
  (NESDIS_ID
  ,TIME_TAG
  ,SOURCE)
 );
GO 

CREATE TABLE XC_PP_INSTRUCTIONS
 (PPI_NAME VARCHAR(5) NOT NULL
 ,TIME_PERIOD FLOAT(53) NOT NULL
 ,DESCRIPTION VARCHAR(32)
 ,DLL VARCHAR(128)
, CONSTRAINT PPI_PK PRIMARY KEY 
  (PPI_NAME
  ,TIME_PERIOD)
 );
 GO

CREATE TABLE XC_SITSENS_PPI
 (SITSEN_SENSORNAME VARCHAR(12) NOT NULL
 ,SITSEN_STE_STATION_ID VARCHAR(20) NOT NULL
 ,PPI_PPI_NAME VARCHAR(5) NOT NULL
 ,PPI_TIME_PERIOD FLOAT(53) NOT NULL
 ,DATA_LABEL VARCHAR(12) NOT NULL
 ,DESCRIPTION VARCHAR(30)
 ,ALTERNATE_DATA_LABEL_1 VARCHAR(12)
 ,ALTERNATE_DATA_LABEL_2 VARCHAR(12)
 ,OPTION_STRING VARCHAR(12)
, CONSTRAINT SSPPI_PK PRIMARY KEY 
  (SITSEN_SENSORNAME
  ,SITSEN_STE_STATION_ID
  ,PPI_PPI_NAME
  ,PPI_TIME_PERIOD)
, CONSTRAINT
 SSPPI_PPI_FK FOREIGN KEY 
  (PPI_PPI_NAME
  ,PPI_TIME_PERIOD) REFERENCES XC_PP_INSTRUCTIONS
  (PPI_NAME
  ,TIME_PERIOD)
, CONSTRAINT
 SSPPI_SITSEN_FK FOREIGN KEY 
  (SITSEN_SENSORNAME
  ,SITSEN_STE_STATION_ID) REFERENCES XC_SITESENSORS
  (SENSORNAME
  ,STE_STATION_ID)
 );
GO

CREATE TABLE XC_PPDATA1
 (SSPPI_SITSEN_SENSORNAME VARCHAR(12) NOT NULL
 ,SSPPI_SITSEN_STE_STATION_ID VARCHAR(20) NOT NULL
 ,SSPPI_PPI_PPI_NAME VARCHAR(5) NOT NULL
 ,SSPPI_PPI_TIME_PERIOD FLOAT(53) NOT NULL
 ,TIME_TAG DATETIME NOT NULL
 ,PPI_VALUE FLOAT(53) NOT NULL
 ,FLAG1 VARCHAR(1)
 ,FLAG2 VARCHAR(1)
 ,FLAG3 VARCHAR(1)
 ,VALUE_TIME_TAG DATETIME
, CONSTRAINT PPDAT1_PK PRIMARY KEY 
  (TIME_TAG
  ,SSPPI_SITSEN_SENSORNAME
  ,SSPPI_SITSEN_STE_STATION_ID
  ,SSPPI_PPI_PPI_NAME
  ,SSPPI_PPI_TIME_PERIOD)
 );
GO

CREATE TABLE XC_TXTDATA1
 (STATION_ID VARCHAR(20) NOT NULL
 ,SENSORNAME VARCHAR(12) NOT NULL
 ,TIME_TAG DATETIME NOT NULL
 ,SOURCE VARCHAR(1) NOT NULL
 ,TEXT_VALUE VARCHAR(256) NOT NULL
 ,FLAG1 VARCHAR(1)
 ,FLAG2 VARCHAR(1)
 ,FLAG3 VARCHAR(1)
 ,FLAG4 VARCHAR(1)
 ,MESSAGE_STATUS VARCHAR(2)
 ,TXT_ALARM_FLAG INT
, CONSTRAINT TXD1_PK PRIMARY KEY 
  (TIME_TAG
  ,SOURCE
  ,STATION_ID
  ,SENSORNAME)
 );
GO
