// 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 ('Unknown', '0.0', 'kilroy');
INSERT INTO appl (label, ver_nbr, dev_name) VALUES ('Testbed', '1.0', 'mlportersr');


// 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','Async - 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','Async - 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,'Data Acquisition','_libraries\Startup Processes\Acquire Data.vi','Async - Non-Reentrant',2
     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'
;
