upgrade/1.0.1/00_job.sql

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