-----------------------------------------------------------------------------------------
--
-- This DDL is for Sutron XConnect Database on ORACLE
-- XConnect Version: 2.0       Database Version: 100        Date: 2009/08/14
-- Company: Sutron Corporation
--  Writer: GC
--
-----------------------------------------------------------------------------------------
-- Modification for Database Version 100 on 2009/08/14
-- This DDL is the base DB version for XConnect version 2.0.
--
-----------------------------------------------------------------------------------------
--
-- Object Counts:
--    Table ............ 015
--    Foreign Key ...... 001
--    Index ............ 000
--    Synonym .......... 015
--
-----------------------------------------------------------------------------------------
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd';
-----------------------------------------------------------------------------------------
-- TABLE DEFINITIONS
-----------------------------------------------------------------------------------------
CREATE TABLE xcmanager.xc_ver(
	version              NUMBER(4) NOT NULL,
	CONSTRAINT ver_pk PRIMARY KEY(version)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_sites(
	station_id           CHAR(30) NOT NULL,
	enabled              CHAR(1) NOT NULL,
	unit_id              CHAR(15) NULL,
	site_type            NUMBER(4) NULL,
	status               NUMBER(4) NULL,
	ipaddress            CHAR(20) NULL,
	radio_path_list      CHAR(30) NULL,
	phone_number_list    CHAR(40) NULL,
	phone_number2        CHAR(20) NULL,
	user_name            CHAR(10) NULL,
	password             CHAR(10) NULL,
	comport_list         CHAR(15) NULL,
	ackdelay_list        CHAR(10) NULL,
	response_timeout_list CHAR(10) NULL,
	poll_type            NUMBER(1) NULL,
	login_reqd           CHAR(1) NULL,
	quick_protocol       CHAR(1) NULL,
	auto_protocol        CHAR(1) NULL,
	concentrator         CHAR(1) NULL,
	hours_ahead          NUMBER(12,2) NULL,
	rdi_base_time        DATE NULL,
	rdi_interval         DATE NULL,
	rdi_stayon           DATE NULL,
	template_name        CHAR(50) NULL,
	site_comment         CHAR(32) NULL,
	site_long_name       CHAR(50) NULL,
	site_code            NUMBER(8) NULL,
	usgs_id              CHAR(10) NULL,
	installation_date    DATE NULL,
	agency               CHAR(32) NULL,
	country              CHAR(32) NULL,
	district             CHAR(32) NULL,
	city                 CHAR(32) NULL,
	state                CHAR(32) NULL,
	county               CHAR(32) NULL,
	reservoir            CHAR(32) NULL,
	basin                CHAR(32) NULL,
	river_mile           NUMBER(16,4) NULL,
	latitude             NUMBER(10,4) NULL,
	longitude            NUMBER(10,4) NULL,
	elevation            NUMBER(16,4) NULL,
	weighting_factor     NUMBER(16,4) NULL,
	coefficient1         NUMBER(16,4) NULL,
	coefficient2         NUMBER(16,4) NULL,
	coefficient3         NUMBER(16,4) NULL,
	satellite_id         CHAR(8) NULL,
	primary_channel      NUMBER(6) NULL,
	random_channel       NUMBER(6) NULL,
	reporting_time       DATE NULL,
	reporting_interval   NUMBER(12,2) NULL,
	last_update          DATE NULL,
	tzone_code           CHAR(3) NULL,
	gmt_offset           NUMBER(12,2) NULL,
	shef_id              CHAR(8) NULL,
	alternate_char_id_1  CHAR(10) NULL,
	alternate_char_id_2  CHAR(10) NULL,
	gpstring1            CHAR(20) NULL,
	gpstring2            CHAR(20) NULL,
	gpnumber1            NUMBER(16,4) NULL,
	gpnumber2            NUMBER(16,4) NULL,
	setup_file_name      VARCHAR(255) NULL,
	picture_file_name    VARCHAR(255) NULL,
	modem_init_string    CHAR(20) NULL,
	modem_hangup_string  CHAR(20) NULL,
	last_poll            DATE NULL,
	template_flag        NUMBER(1) DEFAULT 1 NULL,
  mbdevice_id          CHAR(4) NULL,
  mbconsec_register    CHAR(1) NULL,
  mbbase_register      NUMBER(8) NULL,
  mbregister_type      NUMBER(8) NULL,
  mbregister_count     NUMBER(8) NULL,
  msgparity            NUMBER(8) NULL,
  imei_id              CHAR(15) NULL,
  sms_id               CHAR(15) NULL,
  log_file_name        VARCHAR(255) NULL,
	CONSTRAINT sites_pk PRIMARY KEY(station_id)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_sitesensors(
	ste_station_id       CHAR(30) NOT NULL,
	sensorname           CHAR(15) NOT NULL,
	enabled              CHAR(1) NOT NULL,
	data_table           CHAR(12) NOT NULL,
	tagname              CHAR(15) NULL,
	"raw"             CHAR(30) NULL,
	right_digit          NUMBER(6) NULL,
	left_digit           NUMBER(6) NULL,
	is_text              CHAR(7) NULL,
	roundoff_time        DATE NULL,
	data_store_interval  DATE NULL,
	units                CHAR(10) NULL,
	template_name        CHAR(50) NULL,
	status               NUMBER(6) NULL,
	description          CHAR(32) NULL,
	manufacturer         CHAR(20) NULL,
	model_number         CHAR(20) NULL,
	shef_pe_code         CHAR(7) NULL,
	parameter_code       CHAR(12) NULL,
	alt_parameter_code_1 CHAR(12) NULL,
	alt_parameter_code_2 CHAR(12) NULL,
	equation             VARCHAR(128) NULL,
	rating_table         VARCHAR(128) NULL,
	offset               NUMBER(16,4) NULL,
	shift                NUMBER(16,4) NULL,
	high_high_limit      NUMBER(16,4) NULL,
	high_high_alarm      NUMBER(1) NULL,
	high_limit           NUMBER(16,4) NULL,
	high_alarm           NUMBER(1) NULL,
	low_limit            NUMBER(16,4) NULL,
	low_alarm            NUMBER(1) NULL,
	low_low_limit        NUMBER(16,4) NULL,
	low_low_alarm        NUMBER(1) NULL,
	roc_limit            NUMBER(16,4) NULL,
	roc_alarm            NUMBER(1) NULL,
	roc_interval         NUMBER(16,4) NULL,
	deadband             NUMBER(16,4) NULL,
	no_change_interval   NUMBER(16,4) NULL,
	no_change_alarm      NUMBER(1) NULL,
	text_limit           CHAR(32) NULL,
	text_alarm           NUMBER(1) NULL,
	std_dev_limit        NUMBER(16,4) NULL,
	std_dev_alarm        NUMBER(1) NULL,
	std_dev_interval     NUMBER(16,4) NULL,
	avg_limit            NUMBER(16,4) NULL,
	avg_alarm            NUMBER(1) NULL,
	avg_interval         NUMBER(16,4) NULL,
	coefficient1         NUMBER(16,4) NULL,
	coefficient2         NUMBER(16,4) NULL,
	coefficient3         NUMBER(16,4) NULL,
	coefficient4         NUMBER(16,4) NULL,
	coefficient5         NUMBER(16,4) NULL,
	gpnumber1            NUMBER(16,4) NULL,
	gpnumber2            NUMBER(16,4) NULL,
	gpstring1            CHAR(20) NULL,
	gpstring2            CHAR(20) NULL,
	alert_id             NUMBER(6) NULL,
	alert_type           CHAR(10) NULL,
	reference_1          NUMBER(16,4) NULL,
	reference_2          NUMBER(16,4) NULL,
	data_exch_num        VARCHAR(100) NULL,
	mike11_label_1       CHAR(50) NULL,
	mike11_label_2       CHAR(50) NULL,
	no_alarm_interval    NUMBER(12,2) NULL,
  mbbase_register      NUMBER(8) NULL,
  mbregister_type      NUMBER(8) NULL,
  mbtag_type           NUMBER(8) NULL,
  mbmsw                NUMBER(8) NULL,
	CONSTRAINT sitesensors_pk PRIMARY KEY(ste_station_id,sensorname)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_data1(
	station_id           CHAR(30) NOT NULL,
	sensorname           CHAR(15) NOT NULL,
	time_tag             DATE NOT NULL,
	source               CHAR(1) NOT NULL,
	orig_value           NUMBER(16,4) NOT NULL,
	ed_value             NUMBER(16,4) NULL,
	flag1                CHAR(1) NULL,
	flag2                CHAR(1) NULL,
	flag3                CHAR(1) NULL,
	flag4                CHAR(1) NULL,
	high_high_flag       NUMBER(1) NULL,
	high_flag            NUMBER(1) NULL,
	low_flag             NUMBER(1) NULL,
	low_low_flag         NUMBER(1) NULL,
	roc_flag             NUMBER(1) NULL,
	no_chg_flag          NUMBER(1) NULL,
	alarm_flag           NUMBER(1) NULL,
	std_dev_flag         NUMBER(1) NULL,
	avg_flag             NUMBER(1) NULL,
	CONSTRAINT data1_pk PRIMARY KEY(station_id,sensorname,time_tag,source)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_txtdata1(
	station_id           CHAR(30) NOT NULL,
	sensorname           CHAR(15) NOT NULL,
	time_tag             DATE NOT NULL,
	source               CHAR(1) NOT NULL,
	text_value           VARCHAR(255) NOT NULL,
	flag1                CHAR(1) NULL,
	flag2                CHAR(1) NULL,
	flag3                CHAR(1) NULL,
	flag4                CHAR(1) NULL,
	message_status       CHAR(2) NULL,
	txt_alarm_flag       NUMBER(1) NULL,
	CONSTRAINT txtdata1_pk PRIMARY KEY(station_id,sensorname,time_tag,source)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_pplist(
	station_id           CHAR(30) NOT NULL,
	sensorname           CHAR(15) NOT NULL,
	data_label           CHAR(20) NOT NULL,
	function_name        CHAR(10) NOT NULL,
	CONSTRAINT pplist_pk PRIMARY KEY(station_id,sensorname,data_label,function_name)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_ppdata1(
	station_id           CHAR(30) NOT NULL,
	sensorname           CHAR(15) NOT NULL,
	data_label           CHAR(20) NOT NULL,
	function_name        CHAR(10) NOT NULL,
	time_tag             DATE NOT NULL,
	time_period          NUMBER(16,4) NOT NULL,
	pp_value             NUMBER(16,4) NOT NULL,
	flag1                CHAR(1) NULL,
	flag2                CHAR(1) NULL,
	flag3                CHAR(1) NULL,
	value_time_tag       DATE NULL,
	process_time_tag     DATE NOT NULL,
	alternate_label      CHAR(20) NULL,
	CONSTRAINT ppdata1_pk PRIMARY KEY(station_id,sensorname,data_label,function_name,time_tag,time_period)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_goesqc(
	nesdis_id            CHAR(8) NOT NULL,
	time_tag             DATE NOT NULL,
	source               CHAR(1) NOT NULL,
	station_id           CHAR(30) NULL,
	failure_code         CHAR(1) NULL,
	sig_strength         NUMBER(12,2) NULL,
	freq_offset          NUMBER(12,2) NULL,
	mod_index            CHAR(1) NULL,
	mod_qual             CHAR(1) NULL,
	channel              NUMBER(4) NULL,
	sat_loc              CHAR(1) NULL,
	uc_status            CHAR(2) NULL,
	message_len          NUMBER(6) NULL,
	actual_len           NUMBER(6) NULL,
	parity               NUMBER(6) NULL,
	buffer_empty         NUMBER(6) NULL,
	battery_voltage      NUMBER(12,2) NULL,
	spare1               NUMBER(6) NULL,
	spare2               NUMBER(6) NULL,
	CONSTRAINT goesqc_pk PRIMARY KEY(nesdis_id,time_tag,source)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_rtuqc(
	station_id           CHAR(30) NOT NULL,
	time_tag             DATE NOT NULL,
	rx_tx                CHAR(1) NOT NULL,
	port                 CHAR(30) NULL,
	sequencenum          NUMBER(10) NULL,
	opcode               NUMBER(10) NULL,
	status               NUMBER(2) NULL,
	retries              NUMBER(2) NULL,
	good                 NUMBER(6) NULL,
	bad                  NUMBER(6) NULL,
	crc                  NUMBER(6) NULL,
	battery_voltage      NUMBER(12,2) NULL,
	spare1               NUMBER(6) NULL,
	spare2               NUMBER(6) NULL,
	CONSTRAINT rtuqc_pk PRIMARY KEY(station_id,time_tag,rx_tx)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_sitesensorsdecode(
	station              CHAR(30) NOT NULL,
	sensor               CHAR(15) NOT NULL,
	topic                CHAR(25) NOT NULL,
	seqnum               NUMBER(4) NOT NULL,
	enabled              CHAR(1) NOT NULL,
	format               CHAR(10) NULL,
	relative_time        CHAR(1) NULL,
	relative_pos         CHAR(1) NULL,
	add_base             CHAR(1) NULL,
	byte_size            NUMBER(8) NULL,
	bit_size             NUMBER(8) NULL,
	bound1               NUMBER(4) NULL,
	bound2               NUMBER(4) NULL,
	block_id             CHAR(50) NULL,
	byte_pos             NUMBER(4) NULL,
	bit_pos              NUMBER(4) NULL,
	next                 NUMBER(4) NULL,
	bit_next             NUMBER(4) NULL,
	amount               NUMBER(4) NULL,
	delta_time           NUMBER(4) NULL,
	offset_time          DATE NULL,
	roundoff_time        DATE NULL,
	skip_blank           NUMBER(4) NULL,
	skip_comma           NUMBER(4) NULL,
	skip_lf              NUMBER(4) NULL,
	ignore_case          CHAR(1) NULL,
	template_name        CHAR(50) NULL,
	CONSTRAINT sitesensorsdecode_pk PRIMARY KEY(station,sensor,topic,seqnum)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_pollgroups(
	group_num            CHAR(2) NOT NULL,
	enabled              CHAR(1) NOT NULL,
	station_list         VARCHAR(2000) NULL,
	base_time            NUMBER(2) NULL,
	interval             NUMBER(2) NULL,
	poll_type            NUMBER(2) NULL,
	control              VARCHAR(128) NULL,
	control_only         CHAR(1) NULL,
	time_sync            NUMBER(2) NULL,
	time_sync_port       CHAR(5) NULL,
	time_limit           DATE NULL,
	override_order       CHAR(1) NULL,
	description          CHAR(30) NULL,
	clients              VARCHAR(255) NULL,
	log_file             CHAR(20) NULL,
	log_file_path        CHAR(50) NULL,
	owner                CHAR(20) NULL,
	reset_rtable         CHAR(1) NULL,
	CONSTRAINT pollgroups_pk PRIMARY KEY(group_num)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_recordids(
	ste_station_id       CHAR(30) NOT NULL,
	record_id            CHAR(10) NOT NULL,
	sensors              VARCHAR(255) NULL,
	CONSTRAINT recordids_pk PRIMARY KEY(ste_station_id,record_id)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_log(
	time_tag             DATE NOT NULL,
	source               CHAR(20) NOT NULL,
	description          VARCHAR(128) NULL,
	CONSTRAINT log_pk PRIMARY KEY(time_tag,source)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_users(
	user_name            CHAR(15) NOT NULL,
	password             VARCHAR(64) NULL,
	description          VARCHAR(64) NULL,
	"access"          CHAR(30) NULL,
	CONSTRAINT users_pk PRIMARY KEY(user_name)
) TABLESPACE XC_DATA
;
CREATE TABLE xcmanager.xc_templates(
	name                 CHAR(50) NOT NULL,
	type                 NUMBER(4) NOT NULL,
	CONSTRAINT templates_pk PRIMARY KEY(name,type)
) TABLESPACE XC_DATA
;
-----------------------------------------------------------------------------------------
-- INSERT VERSIONTABLE INFORMATION
-----------------------------------------------------------------------------------------
INSERT INTO xcmanager.xc_ver(version)
VALUES(102);
-----------------------------------------------------------------------------------------
-- FOREIGN KEY DEFINITIONS
-----------------------------------------------------------------------------------------
--Foreign key for Table xc_ver
--Foreign key for Table xc_sites
--Foreign key for Table xc_sitesensors
ALTER TABLE xcmanager.xc_sitesensors
ADD CONSTRAINT fk003a
FOREIGN KEY (ste_station_id)
REFERENCES xcmanager.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 SYNONYM xc_ver FOR xcmanager.xc_ver
--;
--CREATE SYNONYM xc_sites FOR xcmanager.xc_sites
--;
--CREATE SYNONYM xc_sitesensors FOR xcmanager.xc_sitesensors
--;
--CREATE SYNONYM xc_data1 FOR xcmanager.xc_data1
--;
--CREATE SYNONYM xc_txtdata1 FOR xcmanager.xc_txtdata1
--;
--CREATE SYNONYM xc_pplist FOR xcmanager.xc_pplist
--;
--CREATE SYNONYM xc_ppdata1 FOR xcmanager.xc_ppdata1
--;
--CREATE SYNONYM xc_goesqc FOR xcmanager.xc_goesqc
--;
--CREATE SYNONYM xc_rtuqc FOR xcmanager.xc_rtuqc
--;
--CREATE SYNONYM xc_sitesensorsdecode FOR xcmanager.xc_sitesensorsdecode
--;
--CREATE SYNONYM xc_pollgroups FOR xcmanager.xc_pollgroups
--;
--CREATE SYNONYM xc_recordids FOR xcmanager.xc_recordids
--;
--CREATE SYNONYM xc_log FOR xcmanager.xc_log
--;
--CREATE SYNONYM xc_users FOR xcmanager.xc_users
--;
--CREATE SYNONYM xc_templates FOR xcmanager.xc_templates
--;

