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;
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',
12 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms';
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';
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';
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 (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
41 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms for jobs in profiles';
43 CREATE TABLE `profile_mentor_term`
(
44 `pid`
int NOT NULL COMMENT 'profile id',
45 `jtid`
int unsigned
NOT NULL COMMENT 'term id',
46 PRIMARY KEY (`pid`
, `jtid`
),
47 INDEX `jtid`
(`jtid`
),
48 FOREIGN KEY (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
49 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms for mentorship in profiles';
51 CREATE TABLE `profile_job_entreprise_term`
(
52 `eid`
int unsigned
NOT NULL COMMENT 'entreprise id',
53 `jtid`
int unsigned
NOT NULL COMMENT 'term id',
54 PRIMARY KEY (`eid`
, `jtid`
),
55 INDEX `jtid`
(`jtid`
),
56 FOREIGN KEY (`eid`
) REFERENCES `profile_job_enum`
(`
id`
) ON DELETE CASCADE ON UPDATE CASCADE,
57 FOREIGN KEY (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
58 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms associated to entreprises';
60 -- Adds the root term --
61 INSERT INTO `profile_job_term_enum`
(`jtid`
, `
name`
) VALUES (0, '');
62 UPDATE `profile_job_term_enum`
SET `jtid`
= 0 WHERE `
name`
= '';
64 -- vim:set syntax=mysql: