-----------------------------------------------------------------------------------------
--
-- This DDL is for Sutron XConnect Database on ACCESS
-- 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
--
-----------------------------------------------------------------------------------------
--
-----------------------------------------------------------------------------------------
-- TABLE DEFINITIONS
-----------------------------------------------------------------------------------------
CREATE TABLE xc_ver(
	version              INTEGER NOT NULL,
	PRIMARY KEY(version)
)
;
CREATE TABLE xc_sites(
	station_id           TEXT(30) NOT NULL,
	enabled              TEXT(1) NOT NULL,
	unit_id              TEXT(15) NULL,
	site_type            INTEGER NULL,
	status               INTEGER NULL,
	ipaddress            TEXT(20) NULL,
	radio_path_list      TEXT(30) NULL,
	phone_number_list    TEXT(40) NULL,
	phone_number2        TEXT(20) NULL,
	user_name            TEXT(10) NULL,
	password             TEXT(10) NULL,
	comport_list         TEXT(15) NULL,
	ackdelay_list        TEXT(10) NULL,
  response_timeout_list TEXT(10) NULL,
	poll_type            INTEGER NULL,
	login_reqd           TEXT(1) NULL,
	quick_protocol       TEXT(1) NULL,
	auto_protocol        TEXT(1) NULL,
	concentrator         TEXT(1) NULL,
	hours_ahead          FLOAT NULL,
	rdi_base_time        DATETIME NULL,
	rdi_interval         DATETIME NULL,
	rdi_stayon           DATETIME NULL,
	template_name        TEXT(50) NULL,
	site_comment         TEXT(32) NULL,
	site_long_name       TEXT(50) NULL,
	site_code            INTEGER NULL,
	usgs_id              TEXT(10) NULL,
	installation_date    DATETIME NULL,
	agency               TEXT(32) NULL,
	country              TEXT(32) NULL,
	district             TEXT(32) NULL,
	city                 TEXT(32) NULL,
	state                TEXT(32) NULL,
	county               TEXT(32) NULL,
	reservoir            TEXT(32) NULL,
	basin                TEXT(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         TEXT(8) NULL,
	primary_channel      INTEGER NULL,
	random_channel       INTEGER NULL,
	reporting_time       DATETIME NULL,
	reporting_interval   FLOAT NULL,
	last_update          DATETIME NULL,
	tzone_code           TEXT(3) NULL,
	gmt_offset           FLOAT NULL,
	shef_id              TEXT(8) NULL,
	alternate_char_id_1  TEXT(10) NULL,
	alternate_char_id_2  TEXT(10) NULL,
	gpstring1            TEXT(20) NULL,
	gpstring2            TEXT(20) NULL,
	gpnumber1            FLOAT NULL,
	gpnumber2            FLOAT NULL,
	setup_file_name      TEXT(255) NULL,
  log_file_name        TEXT(255) NULL,
	picture_file_name    TEXT(255) NULL,
	modem_init_string    TEXT(20) NULL,
	modem_hangup_string  TEXT(20) NULL,
	last_poll            DATETIME NULL,
	template_flag        INTEGER NULL,
	mbdevice_id          TEXT(4) NULL,
	mbconsec_register    TEXT(1) NULL,
	mbbase_register      INTEGER NULL,
	mbregister_type      INTEGER NULL,
	mbregister_count     INTEGER NULL,
  msgparity            INTEGER NULL,
  imei_id              TEXT(15) NULL,
  sms_id               TEXT(15) NULL,
	PRIMARY KEY(station_id)
)
;
CREATE TABLE xc_sitesensors(
	ste_station_id       TEXT(30) NOT NULL,
	sensorname           TEXT(15) NOT NULL,
	enabled              TEXT(1) NOT NULL,
	data_table           TEXT(12) NOT NULL,
	tagname              TEXT(15) NULL,
  rawlist              TEXT(30) NULL,
	right_digit          INTEGER NULL,
	left_digit           INTEGER NULL,
	is_text              TEXT(7) NULL,
  roundoff_time        INTEGER NULL,
  data_store_interval  INTEGER NULL,
	units                TEXT(10) NULL,
	template_name        TEXT(50) NULL,
	status               INTEGER NULL,
	description          TEXT(32) NULL,
	manufacturer         TEXT(20) NULL,
	model_number         TEXT(20) NULL,
	shef_pe_code         TEXT(7) NULL,
	parameter_code       TEXT(12) NULL,
	alt_parameter_code_1 TEXT(12) NULL,
	alt_parameter_code_2 TEXT(12) NULL,
	equation             TEXT(128) NULL,
	rating_table         TEXT(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           TEXT(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            TEXT(20) NULL,
	gpstring2            TEXT(20) NULL,
	alert_id             INTEGER NULL,
	alert_type           TEXT(10) NULL,
	reference_1          FLOAT NULL,
	reference_2          FLOAT NULL,
	data_exch_num        TEXT(100) NULL,
	mike11_label_1       TEXT(50) NULL,
	mike11_label_2       TEXT(50) NULL,
	no_alarm_interval    FLOAT NULL,
	mbbase_register      INTEGER NULL,
	mbregister_type      INTEGER NULL,
  mbtag_type           INTEGER NULL,
	mbmsw                INTEGER NULL,
	PRIMARY KEY(ste_station_id,sensorname)
)
;
CREATE TABLE xc_data1(
	station_id           TEXT(30) NOT NULL,
	sensorname           TEXT(15) NOT NULL,
	time_tag             DATETIME NOT NULL,
	source               TEXT(1) NOT NULL,
	orig_value           FLOAT NOT NULL,
	ed_value             FLOAT NULL,
	flag1                TEXT(1) NULL,
	flag2                TEXT(1) NULL,
	flag3                TEXT(1) NULL,
	flag4                TEXT(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,
	PRIMARY KEY(station_id,sensorname,time_tag,source)
)
;
CREATE TABLE xc_txtdata1(
	station_id           TEXT(30) NOT NULL,
	sensorname           TEXT(15) NOT NULL,
	time_tag             DATETIME NOT NULL,
	source               TEXT(1) NOT NULL,
	text_value           TEXT(255) NOT NULL,
	flag1                TEXT(1) NULL,
	flag2                TEXT(1) NULL,
	flag3                TEXT(1) NULL,
	flag4                TEXT(1) NULL,
	message_status       TEXT(2) NULL,
	txt_alarm_flag       INTEGER NULL,
	PRIMARY KEY(station_id,sensorname,time_tag,source)
)
;
CREATE TABLE xc_pplist(
	station_id           TEXT(30) NOT NULL,
	sensorname           TEXT(15) NOT NULL,
	data_label           TEXT(20) NOT NULL,
	function_name        TEXT(10) NOT NULL,
	PRIMARY KEY(station_id,sensorname,data_label,function_name)
)
;
CREATE TABLE xc_ppdata1(
	station_id           TEXT(30) NOT NULL,
	sensorname           TEXT(15) NOT NULL,
	data_label           TEXT(20) NOT NULL,
	function_name        TEXT(10) NOT NULL,
	time_tag             DATETIME NOT NULL,
	time_period          FLOAT NOT NULL,
	pp_value             FLOAT NOT NULL,
	flag1                TEXT(1) NULL,
	flag2                TEXT(1) NULL,
	flag3                TEXT(1) NULL,
	value_time_tag       DATETIME NULL,
	process_time_tag     DATETIME NOT NULL,
	alternate_label      TEXT(20) NULL,
	PRIMARY KEY(station_id,sensorname,data_label,function_name,time_tag,time_period)
)
;
CREATE TABLE xc_goesqc(
	nesdis_id            TEXT(8) NOT NULL,
	time_tag             DATETIME NOT NULL,
	source               TEXT(1) NOT NULL,
	station_id           TEXT(30) NULL,
	failure_code         TEXT(1) NULL,
	sig_strength         FLOAT NULL,
	freq_offset          FLOAT NULL,
	mod_index            TEXT(1) NULL,
	mod_qual             TEXT(1) NULL,
	channel              INTEGER NULL,
	sat_loc              TEXT(1) NULL,
	uc_status            TEXT(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,
	PRIMARY KEY(nesdis_id,time_tag,source)
)
;
CREATE TABLE xc_rtuqc(
	station_id           TEXT(30) NOT NULL,
	time_tag             DATETIME NOT NULL,
	rx_tx                TEXT(1) NOT NULL,
	port                 TEXT(30) NULL,
	sequencenum          INTEGER NULL,
	opcode               INTEGER 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,
	PRIMARY KEY(station_id,time_tag,rx_tx)
)
;
CREATE TABLE xc_sitesensorsdecode(
	station              TEXT(30) NOT NULL,
	sensor               TEXT(15) NOT NULL,
	topic                TEXT(25) NOT NULL,
	seqnum               INTEGER NOT NULL,
	enabled              TEXT(1) NOT NULL,
	format               TEXT(10) NULL,
	relative_time        TEXT(1) NULL,
	relative_pos         TEXT(1) NULL,
	add_base             TEXT(1) NULL,
	byte_size            INTEGER NULL,
	bit_size             INTEGER NULL,
	bound1               INTEGER NULL,
	bound2               INTEGER NULL,
	block_id             TEXT(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          TEXT(1) NULL,
	template_name        TEXT(50) NULL,
	PRIMARY KEY(station,sensor,topic,seqnum)
)
;
CREATE TABLE xc_pollgroups(
	group_num            TEXT(2) NOT NULL,
	enabled              TEXT(1) NOT NULL,
	station_list         MEMO NULL,
	base_time            INTEGER NULL,
	interval             INTEGER NULL,
	poll_type            INTEGER NULL,
	control              TEXT(128) NULL,
	control_only         TEXT(1) NULL,
	time_sync            INTEGER NULL,
	time_sync_port       TEXT(5) NULL,
	time_limit           DATETIME NULL,
	override_order       TEXT(1) NULL,
	description          TEXT(30) NULL,
	clients              TEXT(255) NULL,
	log_file             TEXT(20) NULL,
	log_file_path        TEXT(50) NULL,
	owner                TEXT(20) NULL,
	reset_rtable         TEXT(1) NULL,
	PRIMARY KEY(group_num)
)
;
CREATE TABLE xc_recordids(
	ste_station_id       TEXT(30) NOT NULL,
	record_id            TEXT(10) NOT NULL,
	sensors              TEXT(255) NULL,
	PRIMARY KEY(ste_station_id,record_id)
)
;
CREATE TABLE xc_log(
	time_tag             DATETIME NOT NULL,
	source               TEXT(20) NOT NULL,
	description          TEXT(128) NULL,
	PRIMARY KEY(time_tag,source)
)
;
CREATE TABLE xc_users(
	user_name            TEXT(15) NOT NULL,
	password             TEXT(64) NULL,
	description          TEXT(64) NULL,
  user_access          TEXT(30) NULL,
	PRIMARY KEY(user_name)
)
;
CREATE TABLE xc_templates(
  template_name        TEXT(50) NOT NULL,
  template_type        INTEGER NOT NULL,
	PRIMARY KEY(name,type)
)

CREATE TABLE 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)
)
;
-----------------------------------------------------------------------------------------
-- INSERT VERSIONTABLE INFORMATION
-----------------------------------------------------------------------------------------
INSERT INTO xc_ver(version)
VALUES(103);
-----------------------------------------------------------------------------------------
-- FOREIGN KEY DEFINITIONS
-----------------------------------------------------------------------------------------
ALTER TABLE xc_sitesensors
ADD CONSTRAINT fk003a
FOREIGN KEY (ste_station_id)
REFERENCES xc_sites (station_id)
;
-----------------------------------------------------------------------------------------
-- INDEX DEFINITIONS
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-- SYNONYM DEFINITIONS
-----------------------------------------------------------------------------------------
--CREATE VIEW xc_ver AS
--SELECT * FROM xcmanager.xc_ver
--;
--CREATE VIEW xc_sites AS
--SELECT * FROM xcmanager.xc_sites
--;
--CREATE VIEW xc_sitesensors AS
--SELECT * FROM xcmanager.xc_sitesensors
--;
--CREATE VIEW xc_data1 AS
--SELECT * FROM xcmanager.xc_data1
--;
--CREATE VIEW xc_txtdata1 AS
--SELECT * FROM xcmanager.xc_txtdata1
--;
--CREATE VIEW xc_pplist AS
--SELECT * FROM xcmanager.xc_pplist
--;
--CREATE VIEW xc_ppdata1 AS
--SELECT * FROM xcmanager.xc_ppdata1
--;
--CREATE VIEW xc_goesqc AS
--SELECT * FROM xcmanager.xc_goesqc
--;
--CREATE VIEW xc_rtuqc AS
--SELECT * FROM xcmanager.xc_rtuqc
--;
--CREATE VIEW xc_sitesensorsdecode AS
--SELECT * FROM xcmanager.xc_sitesensorsdecode
--;
--CREATE VIEW xc_pollgroups AS
--SELECT * FROM xcmanager.xc_pollgroups
--;
--CREATE VIEW xc_recordids AS
--SELECT * FROM xcmanager.xc_recordids
--;
--CREATE VIEW xc_log AS
--SELECT * FROM xcmanager.xc_log
--;
--CREATE VIEW xc_users AS
--SELECT * FROM xcmanager.xc_users
--;
--CREATE VIEW xc_templates AS
--SELECT * FROM xcmanager.xc_templates
--;

