-----------------------------------------------------------------------------------------
--
-- This DDL is for Sutron XConnect Database on MSSQL
-- Database Version: 2.0C       Build: 100        Date: 2010/03/22
-- Company: Sutron Corporation
--  Writer: GC
--
-----------------------------------------------------------------------------------------
-- Modification for Version 100 on 2010/03/22
-- This DDL is based on the previous version of XC database schema.
--
-----------------------------------------------------------------------------------------
--
-- Object Counts:
--    Table ............ 015
--    Foreign Key ...... 001
--    Index ............ 000
--    Synonym .......... 015
--
-----------------------------------------------------------------------------------------
use XC_DATA
go

set DATEFORMAT 'MDY';
-----------------------------------------------------------------------------------------
-- TABLE DEFINITIONS
-----------------------------------------------------------------------------------------
CREATE TABLE dbo.xc_ver(
	version              INTEGER NOT NULL,
	CONSTRAINT ver_pk PRIMARY KEY(version),
)
;
CREATE TABLE dbo.xc_sites(
  station_id           VARCHAR(30) NOT NULL,
  enabled              VARCHAR(1) NOT NULL,
  unit_id              VARCHAR(15) NULL,
	site_type            INTEGER NULL,
	status               INTEGER 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            INTEGER NULL,
  login_reqd           VARCHAR(1) NULL,
  quick_protocol       VARCHAR(1) NULL,
  auto_protocol        VARCHAR(1) NULL,
  concentrator         VARCHAR(1) NULL,
	hours_ahead          FLOAT NULL,
	rdi_base_time        DATETIME NULL,
	rdi_interval         DATETIME NULL,
	rdi_stayon           DATETIME NULL,
  template_name        VARCHAR(50) NULL,
  site_comment         VARCHAR(32) NULL,
  site_long_name       VARCHAR(50) NULL,
	site_code            INTEGER NULL,
  usgs_id              VARCHAR(10) NULL,
	installation_date    DATETIME NULL,
  agency               VARCHAR(32) NULL,
  country              VARCHAR(32) NULL,
  district             VARCHAR(32) NULL,
  city                 VARCHAR(32) NULL,
  state                VARCHAR(32) NULL,
  county               VARCHAR(32) NULL,
  reservoir            VARCHAR(32) NULL,
  basin                VARCHAR(32) NULL,
	river_mile           FLOAT NULL,
	latitude             FLOAT NULL,
	longitude            FLOAT NULL,
	elevation            FLOAT NULL,
	weighting_factor     FLOAT NULL,
	coefficient1         FLOAT NULL,
	coefficient2         FLOAT NULL,
	coefficient3         FLOAT NULL,
  satellite_id         VARCHAR(8) NULL,
	primary_channel      INTEGER NULL,
	random_channel       INTEGER NULL,
	reporting_time       DATETIME NULL,
	reporting_interval   FLOAT NULL,
	last_update          DATETIME NULL,
  tzone_code           VARCHAR(3) NULL,
	gmt_offset           FLOAT NULL,
  shef_id              VARCHAR(8) NULL,
  alternate_char_id_1  VARCHAR(10) NULL,
  alternate_char_id_2  VARCHAR(10) NULL,
  gpstring1            VARCHAR(20) NULL,
  gpstring2            VARCHAR(20) NULL,
	gpnumber1            FLOAT NULL,
	gpnumber2            FLOAT NULL,
  setup_file_name      VARCHAR(255) NULL,
  log_file_name        VARCHAR(255) NULL,
  picture_file_name    VARCHAR(255) NULL,
  modem_init_string    VARCHAR(20) NULL,
  modem_hangup_string  VARCHAR(20) NULL,
	last_poll            DATETIME NULL,
	template_flag        INTEGER NULL DEFAULT 1,
  mbdevice_id          VARCHAR(4) NULL,
  mbconsec_register    VARCHAR(1) NULL,
	mbbase_register      INTEGER NULL,
	mbregister_type      INTEGER NULL,
	mbregister_count     INTEGER NULL,
  msgparity            INTEGER NULL,
  imei_id              VARCHAR(15) NULL,
  sms_id               VARCHAR(15) NULL,
	CONSTRAINT sites_pk PRIMARY KEY(station_id),
)
;
CREATE TABLE dbo.xc_sitesensors(
  ste_station_id       VARCHAR(30) NOT NULL,
  sensorname           VARCHAR(15) NOT NULL,
  enabled              VARCHAR(1) NOT NULL,
  data_table           VARCHAR(12) NOT NULL,
  tagname              VARCHAR(15) NULL,
  rawlist              VARCHAR(30) NULL,
	right_digit          INTEGER NULL,
	left_digit           INTEGER NULL,
  is_text              VARCHAR(1) NULL,
  roundoff_time        INTEGER NULL,
  data_store_interval  INTEGER NULL,
  units                VARCHAR(10) NULL,
  template_name        VARCHAR(50) NULL,
	status               INTEGER NULL,
  description          VARCHAR(32) NULL,
  manufacturer         VARCHAR(20) NULL,
  model_number         VARCHAR(20) NULL,
  shef_pe_code         VARCHAR(7) NULL,
  parameter_code       VARCHAR(12) NULL,
  alt_parameter_code_1 VARCHAR(12) NULL,
  alt_parameter_code_2 VARCHAR(12) NULL,
  equation             VARCHAR(128) NULL,
  rating_table         VARCHAR(128) NULL,
	offset               FLOAT NULL,
	shift                FLOAT NULL,
	high_high_limit      FLOAT NULL,
	high_high_alarm      INTEGER NULL,
	high_limit           FLOAT NULL,
	high_alarm           INTEGER NULL,
	low_limit            FLOAT NULL,
	low_alarm            INTEGER NULL,
	low_low_limit        FLOAT NULL,
	low_low_alarm        INTEGER NULL,
	roc_limit            FLOAT NULL,
	roc_alarm            INTEGER NULL,
	roc_interval         FLOAT NULL,
	deadband             FLOAT NULL,
	no_change_interval   FLOAT NULL,
	no_change_alarm      INTEGER NULL,
  text_limit           VARCHAR(32) NULL,
	text_alarm           INTEGER NULL,
	std_dev_limit        FLOAT NULL,
	std_dev_alarm        INTEGER NULL,
	std_dev_interval     FLOAT NULL,
	avg_limit            FLOAT NULL,
	avg_alarm            INTEGER NULL,
	avg_interval         FLOAT NULL,
	coefficient1         FLOAT NULL,
	coefficient2         FLOAT NULL,
	coefficient3         FLOAT NULL,
	coefficient4         FLOAT NULL,
	coefficient5         FLOAT NULL,
	gpnumber1            FLOAT NULL,
	gpnumber2            FLOAT NULL,
  gpstring1            VARCHAR(20) NULL,
  gpstring2            VARCHAR(20) NULL,
	alert_id             INTEGER NULL,
  alert_type           VARCHAR(10) NULL,
	reference_1          FLOAT NULL,
	reference_2          FLOAT NULL,
  data_exch_num        VARCHAR(100) NULL,
  mike11_label_1       VARCHAR(50) NULL,
  mike11_label_2       VARCHAR(50) NULL,
	no_alarm_interval    FLOAT NULL,
	mbbase_register      INTEGER NULL,
	mbregister_type      INTEGER NULL,
  mbtag_type           INTEGER NULL,
	mbmsw                INTEGER NULL,
	CONSTRAINT sitesensors_pk PRIMARY KEY(ste_station_id,sensorname),
)
;
CREATE TABLE dbo.xc_data1(
  station_id           VARCHAR(30) NOT NULL,
  sensorname           VARCHAR(15) NOT NULL,
	time_tag             DATETIME NOT NULL,
  source               VARCHAR(1) NOT NULL,
	orig_value           FLOAT NOT NULL,
	ed_value             FLOAT NULL,
  flag1                VARCHAR(1) NULL,
  flag2                VARCHAR(1) NULL,
  flag3                VARCHAR(1) NULL,
  flag4                VARCHAR(1) NULL,
	high_high_flag       INTEGER NULL,
	high_flag            INTEGER NULL,
	low_flag             INTEGER NULL,
	low_low_flag         INTEGER NULL,
	roc_flag             INTEGER NULL,
	no_chg_flag          INTEGER NULL,
	alarm_flag           INTEGER NULL,
	std_dev_flag         INTEGER NULL,
	avg_flag             INTEGER NULL,
	CONSTRAINT data1_pk PRIMARY KEY(station_id,sensorname,time_tag,source),
)
;
CREATE TABLE dbo.xc_txtdata1(
  station_id           VARCHAR(30) NOT NULL,
  sensorname           VARCHAR(15) NOT NULL,
	time_tag             DATETIME NOT NULL,
  source               VARCHAR(1) NOT NULL,
  text_value           VARCHAR(255) NOT NULL,
  flag1                VARCHAR(1) NULL,
  flag2                VARCHAR(1) NULL,
  flag3                VARCHAR(1) NULL,
  flag4                VARCHAR(1) NULL,
  message_status       VARCHAR(2) NULL,
	txt_alarm_flag       INTEGER NULL,
	CONSTRAINT txtdata1_pk PRIMARY KEY(station_id,sensorname,time_tag,source),
)
;
CREATE TABLE dbo.xc_pplist(
  station_id           VARCHAR(30) NOT NULL,
  sensorname           VARCHAR(15) NOT NULL,
  data_label           VARCHAR(20) NOT NULL,
  function_name        VARCHAR(10) NOT NULL,
	CONSTRAINT pplist_pk PRIMARY KEY(station_id,sensorname,data_label,function_name),
)
;
CREATE TABLE dbo.xc_ppdata1(
  station_id           VARCHAR(30) NOT NULL,
  sensorname           VARCHAR(15) NOT NULL,
  data_label           VARCHAR(20) NOT NULL,
  function_name        VARCHAR(10) NOT NULL,
	time_tag             DATETIME NOT NULL,
	time_period          FLOAT NOT NULL,
	pp_value             FLOAT NOT NULL,
  flag1                VARCHAR(1) NULL,
  flag2                VARCHAR(1) NULL,
  flag3                VARCHAR(1) NULL,
	value_time_tag       DATETIME NULL,
	process_time_tag     DATETIME NOT NULL,
  alternate_label      VARCHAR(20) NULL,
	CONSTRAINT ppdata1_pk PRIMARY KEY(station_id,sensorname,data_label,function_name,time_tag,time_period),
)
;
CREATE TABLE dbo.xc_goesqc(
  nesdis_id            VARCHAR(8) NOT NULL,
	time_tag             DATETIME NOT NULL,
  source               VARCHAR(1) NOT NULL,
  station_id           VARCHAR(30) NULL,
  failure_code         VARCHAR(1) NULL,
	sig_strength         FLOAT NULL,
	freq_offset          FLOAT NULL,
  mod_index            VARCHAR(1) NULL,
  mod_qual             VARCHAR(1) NULL,
	channel              INTEGER NULL,
  sat_loc              VARCHAR(1) NULL,
  uc_status            VARCHAR(2) NULL,
	message_len          INTEGER NULL,
	actual_len           INTEGER NULL,
	parity               INTEGER NULL,
	buffer_empty         INTEGER NULL,
	battery_voltage      FLOAT NULL,
  spare1               FLOAT NULL,
  spare2               FLOAT NULL,
	CONSTRAINT goesqc_pk PRIMARY KEY(nesdis_id,time_tag,source),
)
;
CREATE TABLE dbo.xc_rtuqc(
  station_id           VARCHAR(30) NOT NULL,
	time_tag             DATETIME NOT NULL,
  rx_tx                VARCHAR(1) NOT NULL,
  port                 VARCHAR(30) NULL,
  sequencenum          FLOAT NULL,
  opcode               FLOAT NULL,
	status               INTEGER NULL,
	retries              INTEGER NULL,
	good                 INTEGER NULL,
	bad                  INTEGER NULL,
	crc                  INTEGER NULL,
	battery_voltage      FLOAT NULL,
  spare1               FLOAT NULL,
  spare2               FLOAT NULL,
	CONSTRAINT rtuqc_pk PRIMARY KEY(station_id,time_tag,rx_tx),
)
;
CREATE TABLE dbo.xc_sitesensorsdecode(
  station              VARCHAR(30) NOT NULL,
  sensor               VARCHAR(15) NOT NULL,
  topic                VARCHAR(25) 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          INTEGER NULL,
  roundoff_time        INTEGER NULL,
	skip_blank           INTEGER NULL,
	skip_comma           INTEGER NULL,
  skip_lf              INTEGER NULL,
  skip_plus            INTEGER NULL,
  ignore_case          VARCHAR(1) NULL,
  template_name        VARCHAR(50) NULL,
	CONSTRAINT sitesensorsdecode_pk PRIMARY KEY(station,sensor,topic,seqnum),
)
;
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            INTEGER NULL,
  control              VARCHAR(128) NULL,
  control_only         VARCHAR(1) NULL,
	time_sync            INTEGER 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,
  reset_rtable         VARCHAR(1) NULL,
	CONSTRAINT pollgroups_pk PRIMARY KEY(group_num),
)
;
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),
)
;
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),
)
;
CREATE TABLE dbo.xc_users(
  user_name            VARCHAR(15) NOT NULL,
  password             VARCHAR(64) NULL,
  description          VARCHAR(64) NULL,
  user_access          VARCHAR(30) NULL,
	CONSTRAINT users_pk PRIMARY KEY(user_name),
)
;
CREATE TABLE dbo.xc_templates(
  template_name        VARCHAR(50) NOT NULL,
  template_type        INTEGER NOT NULL,
	CONSTRAINT templates_pk PRIMARY KEY(template_name,template_type),
)
;
CREATE TABLE dbo.xc_lastvalues(
  station_id           VARCHAR(30) NOT NULL,
  sensorname           VARCHAR(15) NOT NULL,
  time_tag             DATETIME NOT NULL,
  is_text              VARCHAR(1) NULL,
  num_value            FLOAT NULL,
  text_value           VARCHAR(255) NOT NULL,
  PRIMARY KEY(station_id,sensorname)
)
;
-----------------------------------------------------------------------------------------
-- INSERT VERSIONTABLE INFORMATION
-----------------------------------------------------------------------------------------
INSERT INTO dbo.xc_ver(version)
VALUES(103);
-----------------------------------------------------------------------------------------
-- FOREIGN KEY DEFINITIONS
-----------------------------------------------------------------------------------------
--Foreign key for Table xc_ver
--Foreign key for Table xc_sites
--Foreign key for Table xc_sitesensors
ALTER TABLE dbo.xc_sitesensors
ADD CONSTRAINT fk003a
FOREIGN KEY (ste_station_id)
REFERENCES dbo.xc_sites (station_id)
;
--Foreign key for Table xc_data1
--Foreign key for Table xc_txtdata1
--Foreign key for Table xc_pplist
--Foreign key for Table xc_ppdata1
--Foreign key for Table xc_goesqc
--Foreign key for Table xc_rtuqc
--Foreign key for Table xc_sitesensorsdecode
--Foreign key for Table xc_pollgroups
--Foreign key for Table xc_recordids
--Foreign key for Table xc_log
--Foreign key for Table xc_users
--Foreign key for Table xc_templates
-----------------------------------------------------------------------------------------
-- INDEX DEFINITIONS
-----------------------------------------------------------------------------------------
--Index for Table xc_ver
--Index for Table xc_sites
--Index for Table xc_sitesensors
--Index for Table xc_data1
--Index for Table xc_txtdata1
--Index for Table xc_pplist
--Index for Table xc_ppdata1
--Index for Table xc_goesqc
--Index for Table xc_rtuqc
--Index for Table xc_sitesensorsdecode
--Index for Table xc_pollgroups
--Index for Table xc_recordids
--Index for Table xc_log
--Index for Table xc_users
--Index for Table xc_templates
-----------------------------------------------------------------------------------------
-- SYNONYM DEFINITIONS
-----------------------------------------------------------------------------------------
--CREATE VIEW xc_ver AS
--SELECT * FROM dbo.xc_ver
--;
--CREATE VIEW xc_sites AS
--SELECT * FROM dbo.xc_sites
--;
--CREATE VIEW xc_sitesensors AS
--SELECT * FROM dbo.xc_sitesensors
--;
--CREATE VIEW xc_data1 AS
--SELECT * FROM dbo.xc_data1
--;
--CREATE VIEW xc_txtdata1 AS
--SELECT * FROM dbo.xc_txtdata1
--;
--CREATE VIEW xc_pplist AS
--SELECT * FROM dbo.xc_pplist
--;
--CREATE VIEW xc_ppdata1 AS
--SELECT * FROM dbo.xc_ppdata1
--;
--CREATE VIEW xc_goesqc AS
--SELECT * FROM dbo.xc_goesqc
--;
--CREATE VIEW xc_rtuqc AS
--SELECT * FROM dbo.xc_rtuqc
--;
--CREATE VIEW xc_sitesensorsdecode AS
--SELECT * FROM dbo.xc_sitesensorsdecode
--;
--CREATE VIEW xc_pollgroups AS
--SELECT * FROM dbo.xc_pollgroups
--;
--CREATE VIEW xc_recordids AS
--SELECT * FROM dbo.xc_recordids
--;
--CREATE VIEW xc_log AS
--SELECT * FROM dbo.xc_log
--;
--CREATE VIEW xc_users AS
--SELECT * FROM dbo.xc_users
--;
--CREATE VIEW xc_templates AS
--SELECT * FROM dbo.xc_templates
--;

