-- set log_bin (and all log_*) to false in /etc/mysql/cnf
-- set bulk_insert_buffer_size to a real big value
-- create user 'distsim'@'localhost' identified by 'distsim';
-- grant all privileges on *.* to 'distsim'@'localhost';

------------------------------------------------------------------------
-- simulation database
------------------------------------------------------------------------

CREATE TABLE studies (
	id INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(63),
	version VARCHAR(63),
	command VARCHAR(511)) ENGINE InnoDB;

CREATE TABLE in_files (
	id INTEGER AUTO_INCREMENT PRIMARY KEY,
	content LONGBLOB) ENGINE InnoDB;

CREATE TABLE groups (
	id INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(63),
	studyId INTEGER) ENGINE InnoDB;

ALTER TABLE groups
	ADD FOREIGN KEY (studyId) REFERENCES studies (id);


CREATE TABLE packages (
	name VARCHAR(63),
	version VARCHAR(63),
	architecture VARCHAR(63),
	url VARCHAR(255),
	PRIMARY KEY(name, version, architecture)) ENGINE InnoDB;

CREATE TABLE study_packages (
	studyId INTEGER, localPath VARCHAR(255),
	packageName VARCHAR(63),
	packageVersion VARCHAR(63)) ENGINE InnoDB;

ALTER TABLE study_packages
	ADD FOREIGN KEY (studyId) REFERENCES studies (id);

ALTER TABLE study_packages
	ADD FOREIGN KEY (packageName, packageVersion) REFERENCES packages(name, version);


CREATE TABLE group_out_files (
	groupId INTEGER,
	remotePath VARCHAR(255)) ENGINE InnoDB;

ALTER TABLE group_out_files
	ADD FOREIGN KEY (groupId) REFERENCES groups (id);


CREATE TABLE group_in_files (
	groupId INTEGER,
	remotePath VARCHAR(255),
	fileId INTEGER,
	PRIMARY KEY (groupId, remotePath)) ENGINE InnoDB;

ALTER TABLE group_in_files
	ADD FOREIGN KEY (groupId) REFERENCES groups (id);

ALTER TABLE group_in_files
	ADD FOREIGN KEY (fileId) REFERENCES in_files (id);


CREATE TABLE hosts (
	id INTEGER PRIMARY KEY,
	name VARCHAR(63),
	architecture VARCHAR(63),
	description VARCHAR(511)) ENGINE InnoDB;

CREATE TABLE simulations (
	id INTEGER AUTO_INCREMENT PRIMARY KEY,
	groupId INTEGER,
	assigned INTEGER, defined DATETIME) ENGINE InnoDB;

ALTER TABLE simulations
	ADD FOREIGN KEY (groupId) REFERENCES groups (id);

ALTER TABLE simulations
	ADD FOREIGN KEY (assigned) REFERENCES hosts (id);


CREATE TABLE group_string_params (
	groupId INTEGER,
	name VARCHAR(63),
	value VARCHAR(511)) ENGINE InnoDB;

ALTER TABLE group_string_params
	ADD FOREIGN KEY (groupId) REFERENCES groups (id);


CREATE TABLE group_numeric_params (
	groupId INTEGER,
	name VARCHAR(63),
	min VARCHAR(255),
	max VARCHAR(255),
	step VARCHAR(255),
	PRIMARY KEY (groupId, name)) ENGINE InnoDB;

ALTER TABLE group_numeric_params
	ADD FOREIGN KEY (groupId) REFERENCES groups (id);


CREATE TABLE params (
	simulationId INTEGER,
	name VARCHAR(63),
	value VARCHAR(511),
	PRIMARY KEY (simulationId, name)) ENGINE InnoDB;

ALTER TABLE params
	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);


--CREATE DATABASE simulation_results;

CREATE TABLE out_files (
	simulationId INTEGER,
	path VARCHAR(255),
	content LONGBLOB,
	PRIMARY KEY (simulationId, path)) ENGINE InnoDB;

ALTER TABLE out_files
	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);


CREATE TABLE progress (
	simulationId INTEGER PRIMARY KEY,
	state ENUM('RUNNING', 'FINISHED', 'CANCELED', 'WAITING'),
	started DATETIME,
	expectedEnd DATETIME,
	percentageDone INTEGER) ENGINE InnoDB;

ALTER TABLE progress
	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);

CREATE TABLE stdout (
	simulationId INTEGER,
	chunk INTEGER,
	content VARCHAR(1024)) ENGINE InnoDB;

ALTER TABLE stdout
	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);

CREATE TABLE stderr (
	simulationId INTEGER,
	chunk INTEGER,
	content VARCHAR(1024)) ENGINE InnoDB;

ALTER TABLE stderr
	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);


CREATE TABLE binarystore (
	simulationId INTEGER NOT NULL,
	contributionId INTEGER NOT NULL,
	type INTEGER NOT NULL,
	path VARCHAR(2048) NOT NULL,
	content LONGBLOB NOT NULL,
	PRIMARY KEY (simulationId, contributionId)) ENGINE InnoDB;

ALTER TABLE binarystore
	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);

-- index to speed up evaluations
ALTER TABLE binarystore
	ADD INDEX idx_simulationId_path (simulationId, path);

-- insert some dummy values to be able to use the db without wrapper
INSERT INTO studies SET name='test', id=1, version=0;
INSERT INTO groups SET name='test', id=1, studyId=1;
INSERT INTO hosts SET name='test', id=1, architecture='test', description='test';

CREATE TABLE class_hierarchy (
--	simulationId INTEGER,
	subclass VARCHAR(255),
	superclass VARCHAR(255),
	PRIMARY KEY (subclass)) ENGINE InnoDB;
--	PRIMARY KEY (simulationId, subclass)) ENGINE InnoDB;

--ALTER TABLE class_hierarchy
--	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);


CREATE TABLE class_members (
--	simulationId INTEGER,
	class VARCHAR(255),
	member VARCHAR(255),
	PRIMARY KEY (class, member)) ENGINE InnoDB;
--	PRIMARY KEY (simulationId, class, member)) ENGINE InnoDB;

--ALTER TABLE class_members
--	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);

CREATE TABLE mappings (
	id INTEGER,
	mapping LONGTEXT,
	PRIMARY KEY (id)) ENGINE InnoDB;

INSERT INTO mappings SET id=0, mapping='empty';

--ALTER TABLE mappings
--	ADD FOREIGN KEY (simulationId) REFERENCES simulations (id);
