200 lines
9.2 KiB
SQL
Executable File
200 lines
9.2 KiB
SQL
Executable File
-- new reorganized autogenerated sql
|
|
-- create statements first
|
|
|
|
CREATE TABLE IF NOT EXISTS `newidea` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`dateCreated` datetime NOT NULL,
|
|
`lastModified` datetime NOT NULL,
|
|
`version` int(11) NOT NULL,
|
|
`description` longtext,
|
|
`externalSupervisorInfo` longtext,
|
|
`prerequisites` longtext,
|
|
`title` longtext NOT NULL,
|
|
`type` varchar(255) DEFAULT NULL,
|
|
`practicalHow` longtext NOT NULL,
|
|
`theoryHow` longtext NOT NULL,
|
|
`what` longtext NOT NULL,
|
|
`why` longtext NOT NULL,
|
|
`applicationPeriod_id` bigint(20) DEFAULT NULL,
|
|
`creator_id` bigint(20) NOT NULL,
|
|
`language_id` bigint(20) NOT NULL,
|
|
`match_id` bigint(20) DEFAULT NULL,
|
|
`project_id` bigint(20) DEFAULT NULL,
|
|
`projectClass_id` bigint(20) NOT NULL,
|
|
`researchArea_id` bigint(20) NOT NULL,
|
|
`suggestedReviewer_id` bigint(20) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK6E051897C1813915` (`project_id`),
|
|
KEY `FK6E051897B9431B73` (`match_id`),
|
|
KEY `FK6E051897BEC322C1` (`applicationPeriod_id`),
|
|
KEY `FK6E051897E20156A5` (`suggestedReviewer_id`),
|
|
KEY `FK6E051897B2B6081F` (`projectClass_id`),
|
|
KEY `FK6E0518974E257FBF` (`researchArea_id`),
|
|
KEY `FK6E051897E44F4DBE` (`creator_id`),
|
|
KEY `FK6E0518973BE9881F` (`language_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `newidea_first_meeting` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`description` longtext NOT NULL,
|
|
`firstMeetingDate` datetime NOT NULL,
|
|
`idea_id` bigint(20) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idea_id` (`idea_id`),
|
|
KEY `FK9393AA04FCDADF61` (`idea_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `newidea_Keyword` (
|
|
`newidea_id` bigint(20) NOT NULL,
|
|
`keywords_id` bigint(20) NOT NULL,
|
|
PRIMARY KEY (`newidea_id`,`keywords_id`),
|
|
KEY `FK3707EE21BD1521C1` (`newidea_id`),
|
|
KEY `FK3707EE21AE316F00` (`keywords_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE IF NOT EXISTS `newidea_match` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`dateCreated` datetime NOT NULL,
|
|
`lastModified` datetime NOT NULL,
|
|
`version` int(11) NOT NULL,
|
|
`status` varchar(255) DEFAULT NULL,
|
|
`changedBy_id` bigint(20) DEFAULT NULL,
|
|
`idea_id` bigint(20) NOT NULL,
|
|
`supervisor_id` bigint(20) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK87EA481DFCDADF61` (`idea_id`),
|
|
KEY `FK87EA481DBCA56165` (`supervisor_id`),
|
|
KEY `FK87EA481DA89FFB7F` (`changedBy_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `newidea_student` (
|
|
`confirmed` bit(1) DEFAULT NULL,
|
|
`dateCreated` datetime NOT NULL,
|
|
`role_id` bigint(20) NOT NULL DEFAULT '0',
|
|
`idea_id` bigint(20) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`idea_id`,`role_id`),
|
|
KEY `FK9458BA93FCDADF61` (`idea_id`),
|
|
KEY `FK9458BA932B6C61BA` (`role_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
|
|
-- then alter statements to add foreign constraints
|
|
ALTER TABLE `newidea`
|
|
ADD CONSTRAINT `FK6E0518973BE9881F` FOREIGN KEY (`language_id`) REFERENCES `Language` (`id`),
|
|
ADD CONSTRAINT `FK6E0518974E257FBF` FOREIGN KEY (`researchArea_id`) REFERENCES `researcharea` (`id`),
|
|
ADD CONSTRAINT `FK6E051897B2B6081F` FOREIGN KEY (`projectClass_id`) REFERENCES `project_class` (`id`),
|
|
ADD CONSTRAINT `FK6E051897B9431B73` FOREIGN KEY (`match_id`) REFERENCES `newidea_match` (`id`),
|
|
ADD CONSTRAINT `FK6E051897BEC322C1` FOREIGN KEY (`applicationPeriod_id`) REFERENCES `ApplicationPeriod` (`id`),
|
|
ADD CONSTRAINT `FK6E051897C1813915` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
|
|
ADD CONSTRAINT `FK6E051897E20156A5` FOREIGN KEY (`suggestedReviewer_id`) REFERENCES `role` (`id`),
|
|
ADD CONSTRAINT `FK6E051897E44F4DBE` FOREIGN KEY (`creator_id`) REFERENCES `user` (`id`);
|
|
|
|
ALTER TABLE `newidea_first_meeting`
|
|
ADD CONSTRAINT `FK9393AA04FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`);
|
|
|
|
ALTER TABLE `newidea_Keyword`
|
|
ADD CONSTRAINT `FK3707EE21AE316F00` FOREIGN KEY (`keywords_id`) REFERENCES `Keyword` (`id`),
|
|
ADD CONSTRAINT `FK3707EE21BD1521C1` FOREIGN KEY (`newidea_id`) REFERENCES `newidea` (`id`);
|
|
|
|
ALTER TABLE `newidea_match`
|
|
ADD CONSTRAINT `FK87EA481DA89FFB7F` FOREIGN KEY (`changedBy_id`) REFERENCES `user` (`id`),
|
|
ADD CONSTRAINT `FK87EA481DBCA56165` FOREIGN KEY (`supervisor_id`) REFERENCES `role` (`id`),
|
|
ADD CONSTRAINT `FK87EA481DFCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`);
|
|
|
|
ALTER TABLE `newidea_student`
|
|
ADD CONSTRAINT `FK9458BA932B6C61BA` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
|
|
ADD CONSTRAINT `FK9458BA93FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`);
|
|
|
|
-- the sql below is impossible to run as it requires creating tables that have mutual restraints
|
|
-- CREATE TABLE `newidea` (
|
|
-- `id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
-- `dateCreated` datetime NOT NULL,
|
|
-- `lastModified` datetime NOT NULL,
|
|
-- `version` int(11) NOT NULL,
|
|
-- `description` varchar(4000) DEFAULT NULL,
|
|
-- `externalSupervisorInfo` varchar(255) DEFAULT NULL,
|
|
-- `title` varchar(255) NOT NULL,
|
|
-- `type` varchar(255) DEFAULT NULL,
|
|
-- `practicalHow` varchar(4000) NOT NULL,
|
|
-- `theoryHow` varchar(4000) NOT NULL,
|
|
-- `what` varchar(4000) NOT NULL,
|
|
-- `why` varchar(4000) NOT NULL,
|
|
-- `applicationPeriod_id` bigint(20) DEFAULT NULL,
|
|
-- `creator_id` bigint(20) NOT NULL,
|
|
-- `language_id` bigint(20) NOT NULL,
|
|
-- `match_id` bigint(20) DEFAULT NULL,
|
|
-- `project_id` bigint(20) DEFAULT NULL,
|
|
-- `projectClass_id` bigint(20) NOT NULL,
|
|
-- `researchArea_id` bigint(20) NOT NULL,
|
|
-- `suggestedReviewer_id` bigint(20) DEFAULT NULL,
|
|
-- `prerequisites` varchar(4000) DEFAULT NULL,
|
|
-- PRIMARY KEY (`id`),
|
|
-- KEY `FK6E051897C1813915` (`project_id`),
|
|
-- KEY `FK6E051897B9431B73` (`match_id`),
|
|
-- KEY `FK6E051897BEC322C1` (`applicationPeriod_id`),
|
|
-- KEY `FK6E051897E20156A5` (`suggestedReviewer_id`),
|
|
-- KEY `FK6E051897B2B6081F` (`projectClass_id`),
|
|
-- KEY `FK6E0518974E257FBF` (`researchArea_id`),
|
|
-- KEY `FK6E051897E44F4DBE` (`creator_id`),
|
|
-- KEY `FK6E0518973BE9881F` (`language_id`),
|
|
-- CONSTRAINT `FK6E0518973BE9881F` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`),
|
|
-- CONSTRAINT `FK6E0518974E257FBF` FOREIGN KEY (`researchArea_id`) REFERENCES `researcharea` (`id`),
|
|
-- CONSTRAINT `FK6E051897B2B6081F` FOREIGN KEY (`projectClass_id`) REFERENCES `project_class` (`id`),
|
|
-- CONSTRAINT `FK6E051897B9431B73` FOREIGN KEY (`match_id`) REFERENCES `newidea_match` (`id`),
|
|
-- CONSTRAINT `FK6E051897BEC322C1` FOREIGN KEY (`applicationPeriod_id`) REFERENCES `ApplicationPeriod` (`id`),
|
|
-- CONSTRAINT `FK6E051897C1813915` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
|
|
-- CONSTRAINT `FK6E051897E20156A5` FOREIGN KEY (`suggestedReviewer_id`) REFERENCES `role` (`id`),
|
|
-- CONSTRAINT `FK6E051897E44F4DBE` FOREIGN KEY (`creator_id`) REFERENCES `user` (`id`)
|
|
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
--
|
|
-- CREATE TABLE `newidea_first_meeting` (
|
|
-- `id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
-- `description` longtext NOT NULL,
|
|
-- `firstMeetingDate` datetime NOT NULL,
|
|
-- `idea_id` bigint(20) NOT NULL,
|
|
-- PRIMARY KEY (`id`),
|
|
-- UNIQUE KEY `idea_id` (`idea_id`),
|
|
-- KEY `FK9393AA04FCDADF61` (`idea_id`),
|
|
-- CONSTRAINT `FK9393AA04FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
|
|
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
--
|
|
-- CREATE TABLE `newidea_keyword` (
|
|
-- `newidea_id` bigint(20) NOT NULL,
|
|
-- `keywords_id` bigint(20) NOT NULL,
|
|
-- PRIMARY KEY (`newidea_id`,`keywords_id`),
|
|
-- KEY `FK3707EE21BD1521C1` (`newidea_id`),
|
|
-- KEY `FK3707EE21AE316F00` (`keywords_id`),
|
|
-- CONSTRAINT `FK3707EE21AE316F00` FOREIGN KEY (`keywords_id`) REFERENCES `Keyword` (`id`),
|
|
-- CONSTRAINT `FK3707EE21BD1521C1` FOREIGN KEY (`newidea_id`) REFERENCES `newidea` (`id`)
|
|
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
--
|
|
-- CREATE TABLE `newidea_match` (
|
|
-- `id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
-- `dateCreated` datetime NOT NULL,
|
|
-- `lastModified` datetime NOT NULL,
|
|
-- `version` int(11) NOT NULL,
|
|
-- `status` varchar(255) DEFAULT NULL,
|
|
-- `changedBy_id` bigint(20) DEFAULT NULL,
|
|
-- `idea_id` bigint(20) NOT NULL,
|
|
-- `supervisor_id` bigint(20) DEFAULT NULL,
|
|
-- `comment` longtext,
|
|
-- PRIMARY KEY (`id`),
|
|
-- KEY `FK87EA481DFCDADF61` (`idea_id`),
|
|
-- KEY `FK87EA481DBCA56165` (`supervisor_id`),
|
|
-- KEY `FK87EA481DA89FFB7F` (`changedBy_id`),
|
|
-- CONSTRAINT `FK87EA481DA89FFB7F` FOREIGN KEY (`changedBy_id`) REFERENCES `user` (`id`),
|
|
-- CONSTRAINT `FK87EA481DBCA56165` FOREIGN KEY (`supervisor_id`) REFERENCES `role` (`id`),
|
|
-- CONSTRAINT `FK87EA481DFCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
|
|
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
--
|
|
-- CREATE TABLE `newidea_student` (
|
|
-- `confirmed` bit(1) DEFAULT NULL,
|
|
-- `dateCreated` datetime NOT NULL,
|
|
-- `role_id` bigint(20) NOT NULL DEFAULT '0',
|
|
-- `idea_id` bigint(20) NOT NULL DEFAULT '0',
|
|
-- PRIMARY KEY (`idea_id`,`role_id`),
|
|
-- KEY `FK9458BA93FCDADF61` (`idea_id`),
|
|
-- KEY `FK9458BA932B6C61BA` (`role_id`),
|
|
-- CONSTRAINT `FK9458BA932B6C61BA` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
|
|
-- CONSTRAINT `FK9458BA93FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
|
|
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |