| 1 | DROP TABLE IF EXISTS profile_job_entreprise_term; |
| 2 | DROP TABLE IF EXISTS profile_mentor_term; |
| 3 | DROP TABLE IF EXISTS profile_job_term; |
| 4 | DROP TABLE IF EXISTS profile_job_term_search; |
| 5 | DROP TABLE IF EXISTS profile_job_term_relation; |
| 6 | DROP TABLE IF EXISTS profile_job_term_enum; |
| 7 | |
| 8 | CREATE TABLE `profile_job_term_enum` ( |
| 9 | `jtid` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'term id', |
| 10 | `name` varchar(255) NOT NULL COMMENT 'name used in hierarchical context', |
| 11 | `full_name` varchar(255) NOT NULL COMMENT 'name to use whithout context', |
| 12 | PRIMARY KEY (`jtid`) |
| 13 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms'; |
| 14 | |
| 15 | CREATE TABLE `profile_job_term_relation` ( |
| 16 | `jtid_1` int unsigned NOT NULL COMMENT 'first term id', |
| 17 | `jtid_2` int unsigned NOT NULL COMMENT 'second term id', |
| 18 | `rel` enum('narrower','related') NOT NULL DEFAULT 'narrower' COMMENT 'relation between the second to the first term (second is narrower than first)', |
| 19 | `computed` enum('original','computed') NOT NULL DEFAULT 'original' COMMENT 'relations can be computed from two original relations', |
| 20 | PRIMARY KEY (`jtid_1`, `computed`, `jtid_2`), |
| 21 | FOREIGN KEY (`jtid_1`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 22 | INDEX `jtid_2` (`jtid_2`), |
| 23 | FOREIGN KEY (`jtid_2`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE |
| 24 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms relations'; |
| 25 | |
| 26 | CREATE TABLE `profile_job_term_search` ( |
| 27 | `search` varchar(50) NOT NULL COMMENT 'search token for a term', |
| 28 | `jtid` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'term id', |
| 29 | PRIMARY KEY (`search`, `jtid`), |
| 30 | INDEX `jtid` (`jtid`), |
| 31 | FOREIGN KEY (`jtid`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE |
| 32 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='search tokens of job terms'; |
| 33 | |
| 34 | CREATE TABLE `profile_job_term` ( |
| 35 | `pid` INT(11) UNSIGNED DEFAULT NULL COMMENT 'profile id', |
| 36 | `jid` TINYINT(1) UNSIGNED DEFAULT NULL COMMENT 'job id in profile', |
| 37 | `jtid` INT UNSIGNED DEFAULT NULL COMMENT 'term id', |
| 38 | `computed` enum('original','computed') NOT NULL DEFAULT 'original' COMMENT 'terms can be added by user or computed from entreprise', |
| 39 | PRIMARY KEY (`pid`, `jid`, `jtid`), |
| 40 | INDEX `jtid` (`jtid`), |
| 41 | FOREIGN KEY (`pid`, `jid`) REFERENCES `profile_job` (`pid`, `id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 42 | FOREIGN KEY (`jtid`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE |
| 43 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms for jobs in profiles'; |
| 44 | |
| 45 | CREATE TABLE `profile_mentor_term` ( |
| 46 | `pid` INT(11) UNSIGNED DEFAULT NULL COMMENT 'profile id', |
| 47 | `jtid` int unsigned NOT NULL COMMENT 'term id', |
| 48 | PRIMARY KEY (`pid`, `jtid`), |
| 49 | INDEX `jtid` (`jtid`), |
| 50 | FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE, |
| 51 | FOREIGN KEY (`jtid`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE |
| 52 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms for mentorship in profiles'; |
| 53 | |
| 54 | CREATE TABLE `profile_job_entreprise_term` ( |
| 55 | `eid` int(6) unsigned default NULL COMMENT 'entreprise id', |
| 56 | `jtid` int unsigned NOT NULL COMMENT 'term id', |
| 57 | PRIMARY KEY (`eid`, `jtid`), |
| 58 | INDEX `jtid` (`jtid`), |
| 59 | FOREIGN KEY (`eid`) REFERENCES `profile_job_enum` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 60 | FOREIGN KEY (`jtid`) REFERENCES `profile_job_term_enum` (`jtid`) ON DELETE CASCADE ON UPDATE CASCADE |
| 61 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT='job terms associated to entreprises'; |
| 62 | |
| 63 | -- Adds the root term -- |
| 64 | INSERT INTO `profile_job_term_enum` (`jtid`, `name`) VALUES (0, ''); |
| 65 | UPDATE `profile_job_term_enum` SET `jtid` = 0 WHERE `name` = ''; |
| 66 | |
| 67 | -- vim:set syntax=mysql: |