Commit | Line | Data |
---|---|---|
f73d9281 FB |
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', | |
60db728a SJ |
36 | `jid` TINYINT(1) UNSIGNED DEFAULT NULL COMMENT 'job id in profile', |
37 | `jtid` INT UNSIGNED DEFAULT NULL COMMENT 'term id', | |
f73d9281 FB |
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`), | |
60db728a | 41 | FOREIGN KEY (`pid`, `jid`) REFERENCES `profile_job` (`pid`, `id`) ON DELETE CASCADE ON UPDATE CASCADE, |
f73d9281 FB |
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`), | |
60db728a | 50 | FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE, |
f73d9281 FB |
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`), | |
60db728a | 59 | FOREIGN KEY (`eid`) REFERENCES `profile_job_enum` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
f73d9281 FB |
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: |