CREATE DATABASE `testgrid`;

USE `testgrid`;

GRANT ALL PRIVILEGES ON testgrid.* TO 'tve'@'localhost' IDENTIFIED BY 'tve' WITH GRANT OPTION;

CREATE TABLE `log` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `type` TEXT,
  `subsystem` TEXT NOT NULL,
  `name` TEXT NOT NULL,
  `time` BIGINT NOT NULL,
  `session` BIGINT,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `preferences` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `user` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `value` TEXT,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `property` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `schema` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `record` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `property` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `value` TEXT,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `requirement_property` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `workunit` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `requirement_record` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `property` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `value` TEXT,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `schema` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `session` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `session` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `user` BIGINT NOT NULL,
  `online` BIT DEFAULT 0 NOT NULL,
  `lastalive` BIGINT NOT NULL,
  `timeout` BIGINT NOT NULL,
  `address` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `sessionresult` ( 
  `session` BIGINT NOT NULL,
  `count` BIGINT,
  PRIMARY KEY(`session`)
)
ENGINE = InnoDB
;

CREATE TABLE `suiteincome` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `maxalive` BIGINT NOT NULL DEFAULT 0,
  `data` BLOB,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `suiteoutcome` ( 
  `session` BIGINT NOT NULL,
  `workunit` BIGINT NOT NULL,
  `schema` TEXT NOT NULL,
  `outtime` BIGINT NOT NULL,
  `state` TEXT,
  PRIMARY KEY(`session`),
  UNIQUE (`session`)
)
ENGINE = InnoDB
;

CREATE TABLE `suiteresult` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `workunit` BIGINT NOT NULL,
  `schema` BIGINT NOT NULL,
  `state` TEXT NOT NULL,
  `duration` BIGINT,
  `stdout` BLOB,
  `stderr` BLOB,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `test` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `workunit` BIGINT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `result` TEXT NOT NULL,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `testresult` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `suiteresult` BIGINT NOT NULL,
  `test` BIGINT NOT NULL,
  `duration` BIGINT NOT NULL,
  `state` TEXT NOT NULL,
  `trace` TEXT,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE TABLE `user` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `login` VARCHAR(255) NOT NULL,
  `name` TEXT,
  `email` TEXT,
  `password` TEXT NOT NULL,
  `permissions` INTEGER NOT NULL,
  `lock` BIT DEFAULT 1 NOT NULL,
  PRIMARY KEY(`id`),
  UNIQUE (`login`)
)
ENGINE = InnoDB
;

CREATE TABLE `workunit` ( 
  `id` BIGINT AUTO_INCREMENT  NOT NULL,
  `type` TEXT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `entrypoint` TEXT NOT NULL,
  `commit` BIGINT NOT NULL,
  `priority` INTEGER NOT NULL,
  `timeout` BIGINT NOT NULL,
  `data` BLOB NOT NULL,
  `state` TEXT NOT NULL,
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
;

CREATE INDEX `idx_user` ON `preferences`(`user`);
CREATE INDEX `idx_schema` ON `property`(`schema`);
CREATE INDEX `idx_property` ON `record`(`property`);
CREATE INDEX `idx_workunit` ON `requirement_property`(`workunit`);
CREATE INDEX `idx_property` ON `requirement_record`(`property`);
CREATE INDEX `idx_session` ON `schema`(`session`);
CREATE INDEX `idx_user` ON `session`(`user`);
CREATE INDEX `idx_session` ON `sessionresult`(`session`); 
CREATE INDEX `idx_session` ON `suiteoutcome`(`session`);
CREATE INDEX `idx_workunit` ON `suiteoutcome`(`workunit`);
CREATE INDEX `idx_schema` ON `suiteresult`(`schema`);
CREATE INDEX `idx_workunit` ON `suiteresult`(`workunit`);
CREATE INDEX `idx_workunit` ON `test`(`workunit`); 
CREATE INDEX `idx_suiteresult` ON `testresult`(`suiteresult`);
CREATE INDEX `idx_test` ON `testresult`(`test`);

ALTER TABLE `preferences` ADD CONSTRAINT `FK_preferences_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE;
ALTER TABLE `property` ADD CONSTRAINT `FK_property_schema` FOREIGN KEY (`schema`) REFERENCES `schema` (`id`) ON DELETE CASCADE;
ALTER TABLE `record` ADD CONSTRAINT `FK_record_property` FOREIGN KEY (`property`) REFERENCES `property` (`id`) ON DELETE CASCADE;
ALTER TABLE `requirement_property` ADD CONSTRAINT `FK_requirement_property_workunit` FOREIGN KEY (`workunit`) REFERENCES `workunit` (`id`) ON DELETE CASCADE;
ALTER TABLE `requirement_record` ADD CONSTRAINT `FK_requirement_record_requirement_property` FOREIGN KEY (`property`) REFERENCES `requirement_property` (`id`) ON DELETE CASCADE;
ALTER TABLE `schema` ADD CONSTRAINT `FK_schema_session` FOREIGN KEY (`session`) REFERENCES `session` (`id`) ON DELETE CASCADE;
ALTER TABLE `session` ADD CONSTRAINT `FK_session_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE;
ALTER TABLE `sessionresult` ADD CONSTRAINT `FK_sessionresult_session` FOREIGN KEY (`session`) REFERENCES `session` (`id`) ON DELETE CASCADE;
ALTER TABLE `suiteoutcome` ADD CONSTRAINT `FK_suiteoutcome_session` FOREIGN KEY (`session`) REFERENCES `session` (`id`) ON DELETE CASCADE;
ALTER TABLE `suiteoutcome` ADD CONSTRAINT `FK_suiteoutcome_workunit` FOREIGN KEY (`workunit`) REFERENCES `workunit` (`id`) ON DELETE CASCADE;
ALTER TABLE `suiteresult` ADD CONSTRAINT `FK_suiteresult_workunit` FOREIGN KEY (`workunit`) REFERENCES `workunit` (`id`) ON DELETE CASCADE;
ALTER TABLE `suiteresult` ADD CONSTRAINT `FK_suiteresult_schema` FOREIGN KEY (`schema`) REFERENCES `schema` (`id`) ON DELETE CASCADE;
ALTER TABLE `test` ADD CONSTRAINT `FK_test_workunit` FOREIGN KEY (`workunit`) REFERENCES `workunit` (`id`) ON DELETE CASCADE;
ALTER TABLE `testresult` ADD CONSTRAINT `FK_testresult_suiteresult` FOREIGN KEY (`suiteresult`) REFERENCES `suiteresult` (`id`) ON DELETE CASCADE;
ALTER TABLE `testresult` ADD CONSTRAINT `FK_testresult_test` FOREIGN KEY (`test`) REFERENCES `test` (`id`) ON DELETE CASCADE;

INSERT INTO `user` ( `login`, `name`, `email`, `password`, `permissions`, `lock` ) VALUES ( 'testgrid', 'Testing Grid', 'testgrid@swsoft.nsu.ru', '+KvUl9NCjZkcJJysqr8qwxKcNzg=', 15, 0 );