// Application Specific Tables
// in JET Syntax

CREATE DATABASE LocalDB.mdb;

CREATE TABLE appl (
	id       AUTOINCREMENT PRIMARY KEY,
	label    TEXT(40) WITH COMPRESSION,
	ver_nbr  TEXT(10) WITH COMPRESSION,
	dev_name TEXT(50) WITH COMPRESSION
  )
;

INSERT INTO appl (label, ver_nbr, dev_name) VALUES ('Testbed', '1.0', 'mlportersr');

// Font Definitions
CREATE TABLE ctrl_font_definition (
    id          AUTOINCREMENT PRIMARY KEY,
	owner_name  TEXT(50) WITH COMPRESSION,
	ctrl_name   TEXT(50) WITH COMPRESSION,
	font_name   TEXT(20) WITH COMPRESSION,
	font_size   INTEGER,
	ctrl_comp   TEXT(20) WITH COMPRESSION
  )
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('testbed.vi', 'progress', 'Segoe UI', 24, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Display Data.vi', 'stop', 'Segoe UI', 15, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Display Data.vi', 'Save Data', 'Segoe UI', 15, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Display Data.vi', 'Data Source', 'Segoe UI', 15, 'Caption')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Display Data.vi', 'Data Source', 'Segoe UI', 15, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Display Data.vi', 'Sample Period', 'Segoe UI', 15, 'Caption')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Display Data.vi', 'Sample Period', 'Segoe UI', 15, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Temperature Controller.vi', 'Fan', 'Segoe UI', 15, 'Caption')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Temperature Controller.vi', 'Cooler', 'Segoe UI', 15, 'Caption')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Temperature Controller.vi', 'Data', 'Segoe UI', 15, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Acquire Ramp Data.vi', 'Acquired Data', 'Segoe UI', 15, 'Contents')
;

INSERT INTO ctrl_font_definition
  (owner_name, ctrl_name, font_name, font_size, ctrl_comp)
VALUES
  ('Startup Processes.lvlib:Acquire Sine Data.vi', 'Acquired Data', 'Segoe UI', 15, 'Contents')
;

// Dynamic Launching

CREATE TABLE launch_cond (
    id                AUTOINCREMENT PRIMARY KEY,
    launch_condition  TEXT(128) WITH COMPRESSION
  )
;

INSERT INTO launch_cond (launch_condition) VALUES ('Startup');

CREATE TABLE launch_item (
    id              AUTOINCREMENT PRIMARY KEY,
    appl_id         INTEGER NOT NULL,
    launch_cond_id  INTEGER NOT NULL,
    label           TEXT(40) WITH COMPRESSION,
    item_path       TEXT(128) WITH COMPRESSION,
    launch_mode     TEXT(40) WITH COMPRESSION,
    launch_order    INTEGER,
    CONSTRAINT launch_cond_FK FOREIGN KEY (launch_cond_id) REFERENCES launch_cond(id),
    CONSTRAINT launch_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id)
  )
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'Exception Handler','_libraries\Startup Processes\Exception Handler.vi','Run Method - Non-Reentrant',0
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'User Interface','_libraries\Startup Processes\Display Data.vi','Run Method - Non-Reentrant',1
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'Sine Source','_libraries\Startup Processes\Acquire Sine Data.vi','Async - Non-Reentrant',3
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'Ramp Source','_libraries\Startup Processes\Acquire Ramp Data.vi','Async - Non-Reentrant',4
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'Hen House TC','_libraries\Startup Processes\Temperature Controller.vi','Async - Reentrant',5
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'Dog House TC','_libraries\Startup Processes\Temperature Controller.vi','Async - Reentrant',6
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO launch_item (appl_id, launch_cond_id, label, item_path, launch_mode, launch_order)
   SELECT id, 1,'Out House TC','_libraries\Startup Processes\Temperature Controller.vi','Async - Reentrant',7
     FROM appl
	WHERE label = 'Testbed'
;

// Error Handling

CREATE TABLE event_type (
    id     AUTOINCREMENT PRIMARY KEY,
    label  TEXT(40) WITH COMPRESSION
  )
;

INSERT INTO event_type (label) VALUES ('Error');
INSERT INTO event_type (label) VALUES ('Warning');
INSERT INTO event_type (label) VALUES ('Note');

CREATE TABLE event (
	id             AUTOINCREMENT PRIMARY KEY,
    appl_id        INTEGER NOT NULL,
    event_type_id  INTEGER NOT NULL,
    evt_dttm       DATETIME DEFAULT now() NOT NULL,
    evt_code       INTEGER,
    evt_source     MEMO WITH COMPRESSION,
    CONSTRAINT event_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id),
    CONSTRAINT event_event_type_FK FOREIGN KEY (event_type_id) REFERENCES event_type(id)
  )
;


// Misc Setups

CREATE TABLE misc_setting (
    id         AUTOINCREMENT PRIMARY KEY,
	appl_id    INTEGER NOT NULL,
    p_section  TEXT(50),
    p_key      TEXT(50) WITH COMPRESSION,
    p_value    TEXT(255) WITH COMPRESSION,
    CONSTRAINT miscsettings_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id)
  )
;

INSERT INTO misc_setting (appl_id, p_section, p_key, p_value)
   SELECT id, 'Data Acquisition','Sample Period','1000'
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO misc_setting (appl_id, p_section, p_key, p_value)
   SELECT id, 'Error Handling','Action','Ok + Logging'
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO misc_setting (appl_id, p_section, p_key, p_value)
   SELECT id, 'Error Handling','Ok Button Label','Continue'
     FROM appl
	WHERE label = 'Testbed'
;

INSERT INTO misc_setting (appl_id, p_section, p_key, p_value)
   SELECT id, 'Error Handling','Stop Button Label','Stop'
     FROM appl
	WHERE label = 'Testbed'
;

// Temperature Control

CREATE TABLE temp_control_parm (
	id               AUTOINCREMENT PRIMARY KEY,
	appl_id          INTEGER NOT NULL,
	tc_id            INTEGER NOT NUll,
	sample_interval  INTEGER,
	err_high_level   FLOAT,
	wrn_high_level   FLOAT,
	wrn_low_level    FLOAT,
	err_low_level    FLOAT,
	CONSTRAINT tcsettings_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id),
	CONSTRAINT tcsettings_tc_FK FOREIGN KEY (tc_id) REFERENCES launch_item(id)
)
;

INSERT INTO temp_control_parm (appl_id, tc_id, sample_interval, err_high_level, wrn_high_level, wrn_low_level, err_low_level)
   SELECT a.id, l.id, 10000, 90, 70, 30, 20
     FROM appl a, launch_item l
	WHERE a.label = 'Testbed'
	  AND l.label = 'Hen House TC'
;

INSERT INTO temp_control_parm (appl_id, tc_id, sample_interval, err_high_level, wrn_high_level, wrn_low_level, err_low_level)
   SELECT a.id, l.id, 5000, 90, 70, 30, 20
     FROM appl a, launch_item l
	WHERE a.label = 'Testbed'
	  AND l.label = 'Dog House TC'
;

INSERT INTO temp_control_parm (appl_id, tc_id, sample_interval, err_high_level, wrn_high_level, wrn_low_level, err_low_level)
   SELECT a.id, l.id, 2000, 90, 70, 30, 20
     FROM appl a, launch_item l
	WHERE a.label = 'Testbed'
	  AND l.label = 'Out House TC'
;

