
# These tables are modified from the examples given in
# GlueX-doc-1872 and GlueX-doc-1882.
#
# They implement the online systems configuration database.
#
# Changes:
#
# 2012-01-04  Initial revision  David Lawrence
#


#++++++++++++++++++++ Equipment List +++++++++++++++++++

#==========================
# authors
#==========================
CREATE TABLE IF NOT EXISTS authors (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(256),
	email VARCHAR(256) UNIQUE,

	modified TIMESTAMP,
	created DATETIME
);

#==========================
# boards
#==========================
CREATE TABLE IF NOT EXISTS boards (
	id INT AUTO_INCREMENT PRIMARY KEY,
	boardtype_id INT,
	serial_number VARCHAR(512),
	property_control VARCHAR(512),
	# other things like MAC address or memory offset ??

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT,
	
	UNIQUE(boardtype_id,serial_number)
);


#==========================
# board_type
#==========================
CREATE TABLE IF NOT EXISTS board_type (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(1024),
	description VARCHAR(1024),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# board_status
#==========================
CREATE TABLE IF NOT EXISTS board_status (
	board_id INT,
	status VARCHAR(256),
	location_id INT,
	comment TEXT,

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# locations
#==========================
CREATE TABLE IF NOT EXISTS locations (
	id INT AUTO_INCREMENT PRIMARY KEY,
	location VARCHAR(1024),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#++++++++++++++++++++ Equipment Configuration +++++++++++++++++++

#==========================
# thresholds
#==========================
CREATE TABLE IF NOT EXISTS thresholds (
	id INT AUTO_INCREMENT PRIMARY KEY,
	version INT,
	vals VARCHAR(1024),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# pedestals
#==========================
CREATE TABLE IF NOT EXISTS pedestals (
	id INT AUTO_INCREMENT PRIMARY KEY,
	version INT,
	vals VARCHAR(1045),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# firmware
#==========================
CREATE TABLE IF NOT EXISTS firmware (
	id INT AUTO_INCREMENT PRIMARY KEY,
	version INT,
	boardtype_id INT, 
	program blob,

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#++++++++++++++++++++ Run Configuration +++++++++++++++++++

#==========================
# daq
#==========================
CREATE TABLE IF NOT EXISTS daq (
	id INT AUTO_INCREMENT PRIMARY KEY,
	readout_mode VARCHAR(1024),
	window_size INT,
	nsa_nsb VARCHAR(45),
	pulses_num VARCHAR(45),
	block_readout VARCHAR(45),
	loop_back VARCHAR(45),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# board_configurations
#==========================
CREATE TABLE IF NOT EXISTS board_configurations (
	id INT,
	board_id INT,
	thresholds_id INT,
	pedestals_id INT,
	firmware_id INT,
	crate INT,
	slot INT,

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# board_configuration_name
#==========================
CREATE TABLE IF NOT EXISTS board_configuration_name (
	board_configuration_id INT,
	name VARCHAR(512),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);

#==========================
# run_configurations
#==========================
CREATE TABLE IF NOT EXISTS run_configurations (
	id                        INT PRIMARY KEY,
	run                       INT,
	trigger_configuration_id  INT,
	board_configuration_id  INT,

	created TIMESTAMP
);

#==========================
# trigger_configurations
#==========================
CREATE TABLE IF NOT EXISTS trigger_configurations (
	id INT AUTO_INCREMENT PRIMARY KEY,
	type VARCHAR(45),
	parameters INT,
	prescale VARCHAR(45),

	modified TIMESTAMP,
	modifier_id INT,
	created DATETIME,
	creator_id INT
);


# <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
# Some initial entries to populate the tables

INSERT INTO authors (name, email, created) VALUES('initalization script', 'halld_online@jlab.org', NOW());

INSERT INTO locations(location, modifier_id, creator_id, created) VALUES('unknown', 1, 1, NOW());
INSERT INTO locations(location, modifier_id, creator_id, created) VALUES('halld', 1, 1, NOW());
INSERT INTO locations(location, modifier_id, creator_id, created) VALUES('counting_house', 1, 1, NOW());
INSERT INTO locations(location, modifier_id, creator_id, created) VALUES('test_area', 1, 1, NOW());
INSERT INTO locations(location, modifier_id, creator_id, created) VALUES('offsite', 1, 1, NOW());
INSERT INTO locations(location, modifier_id, creator_id, created) VALUES('other', 1, 1, NOW());

INSERT INTO board_type(name, description, modifier_id, creator_id, created) VALUES('fADC250', 'JLab designed 16-channel 250MHz flash ADC for VME/VXS', 1, 1, NOW());
INSERT INTO board_type(name, description, modifier_id, creator_id, created) VALUES('fADC125', 'IUCF/JLab designed 72-channel 125MHz flash ADC for VME', 1, 1, NOW());
INSERT INTO board_type(name, description, modifier_id, creator_id, created) VALUES('F1TDC', 'JLab designed 32 or 48-channel TDC for VME', 1, 1, NOW());



