upgrade/1.0.1/11_job.sql

   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: