Commit | Line | Data |
---|---|---|
97c79f14 FB |
1 | DROP TABLE IF EXISTS profile_job_sector_enum; |
2 | ||
3 | CREATE TABLE profile_job_sector_enum ( | |
4 | id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT, | |
5 | name VARCHAR(256) NOT NULL DEFAULT '', | |
6 | PRIMARY KEY(id), | |
7 | UNIQUE INDEX(name(128)) | |
8 | ) ENGINE=InnoDB, CHARSET=utf8; | |
9 | ||
10 | ||
11 | DROP TABLE IF EXISTS profile_job_subsector_enum; | |
12 | ||
13 | CREATE TABLE profile_job_subsector_enum ( | |
14 | id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, | |
15 | sectorid TINYINT(2) UNSIGNED NOT NULL, | |
16 | flags SET('optgroup') DEFAULT '' NOT NULL, | |
17 | name VARCHAR(256) NOT NULL DEFAULT '', | |
18 | PRIMARY KEY(id) | |
19 | ) ENGINE=InnoDB, CHARSET=utf8; | |
20 | ||
21 | ||
22 | DROP TABLE IF EXISTS profile_job_subsubsector_enum; | |
23 | ||
24 | CREATE TABLE profile_job_subsubsector_enum ( | |
25 | id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, | |
26 | sectorid TINYINT(2) UNSIGNED NOT NULL, | |
27 | subsectorid TINYINT(3) UNSIGNED NOT NULL, | |
28 | name VARCHAR(256) NOT NULL DEFAULT '', | |
29 | flags SET('display') NOT NULL DEFAULT 'display', | |
30 | PRIMARY KEY(id) | |
31 | ) ENGINE=InnoDB, CHARSET=utf8; | |
32 | ||
33 | ||
34 | DROP TABLE IF EXISTS profile_job_alternates; | |
35 | ||
36 | CREATE TABLE profile_job_alternates ( | |
37 | id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, | |
38 | subsubsectorid SMALLINT(3) UNSIGNED NOT NULL, | |
39 | name VARCHAR(256) NOT NULL DEFAULT '', | |
40 | PRIMARY KEY(id, subsubsectorid), | |
41 | UNIQUE INDEX(name(128)) | |
42 | ) ENGINE=InnoDB, CHARSET=utf8; | |
43 | ||
44 | ||
45 | ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user'; | |
46 | ||
c7139c07 SJ |
47 | DROP TABLE IF EXISTS profile_job_enum; |
48 | ||
d0293d9b | 49 | CREATE TABLE profile_job_enum ( |
c7139c07 SJ |
50 | id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT, |
51 | name VARCHAR(255) NOT NULL DEFAULT '', | |
52 | acronym VARCHAR(255) NOT NULL DEFAULT '', | |
53 | url VARCHAR(255) NOT NULL DEFAULT '', | |
b814a8b8 | 54 | email VARCHAR(255) NOT NULL DEFAULT '', |
c7139c07 SJ |
55 | holdingid INT(6) UNSIGNED DEFAULT NULL, |
56 | NAF_code CHAR(5) NOT NULL DEFAULT '', | |
57 | AX_code BIGINT(10) UNSIGNED NOT NULL, | |
58 | PRIMARY KEY (id), | |
59 | UNIQUE KEY (name) | |
950bf4f6 | 60 | ) ENGINE=InnoDB, CHARSET=utf8; |
c7139c07 SJ |
61 | |
62 | DROP TABLE IF EXISTS profile_job; | |
63 | ||
d0293d9b | 64 | CREATE TABLE profile_job ( |
c7139c07 | 65 | id TINYINT(1) UNSIGNED NOT NULL, |
ce0b2c6f | 66 | pid INT(11) NOT NULL DEFAULT 0, |
c7139c07 | 67 | jobid INT(6) UNSIGNED NOT NULL DEFAULT 0, |
c7139c07 | 68 | sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, |
97c79f14 | 69 | subsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, |
c7139c07 SJ |
70 | subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, |
71 | description VARCHAR(255) NOT NULL DEFAULT '', | |
72 | url VARCHAR(255) NOT NULL DEFAULT '', | |
73 | email VARCHAR(255) NOT NULL DEFAULT '', | |
74 | pub ENUM('private', 'ax', 'public') DEFAULT 'private', | |
75 | email_pub ENUM('private', 'ax', 'public') DEFAULT 'private', | |
e7cc6cb4 SJ |
76 | PRIMARY KEY (pid, id), |
77 | INDEX pid (pid) | |
950bf4f6 | 78 | ) ENGINE=InnoDB, CHARSET=utf8; |
c7139c07 | 79 | |
97c79f14 FB |
80 | # Fill sector table with old sectors. |
81 | INSERT INTO profile_job_sector_enum (name) | |
82 | SELECT label | |
83 | FROM #x4dat#.emploi_secteur; | |
84 | ||
85 | INSERT INTO profile_job_subsector_enum (sectorid, name) | |
86 | SELECT s.id, ss.label | |
87 | FROM #x4dat#.emploi_ss_secteur AS ss | |
88 | INNER JOIN #x4dat#.emploi_secteur AS s4 ON (ss.secteur = s4.id) | |
89 | INNER JOIN profile_job_sector_enum AS s ON (s.name = s4.label); | |
90 | ||
91 | # Since subsubsectors are not supported by the old sectors set, define | |
92 | # a fake value per subsector. | |
93 | INSERT INTO profile_job_subsubsector_enum (id, sectorid, subsectorid, name) | |
94 | SELECT ss.id, ss.sectorid, ss.id, CONCAT(s.name, " (", ss.name, ")") | |
95 | FROM profile_job_subsector_enum AS ss | |
96 | INNER JOIN profile_job_sector_enum AS s ON (s.id = ss.sectorid) | |
97 | ORDER BY ss.id; | |
98 | ||
f9cddbef SJ |
99 | INSERT IGNORE INTO profile_job_enum (name, url) |
100 | SELECT entreprise, web | |
e7cc6cb4 SJ |
101 | FROM #x4dat#.entreprises |
102 | WHERE entreprise != ''; | |
c7139c07 | 103 | |
97c79f14 FB |
104 | INSERT INTO profile_job (id, pid, jobid, email, pub, email_pub, sectorid, subsectorid, subsubsectorid, description) |
105 | SELECT e.entrid, e.uid, j.id, e.email, | |
106 | IF(e.pub = '', 'private', e.pub), IF(e.email_pub = '', 'private', e.email_pub), | |
107 | IF(s.id IS NOT NULL, s.id, 0), IF(ss.id IS NOT NULL, ss.id, 0), | |
108 | IF(ss.id IS NOT NULL, ss.id, 0), e.poste | |
100e66fc | 109 | FROM #x4dat#.entreprises AS e |
563f86f5 | 110 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name) |
100e66fc | 111 | LEFT JOIN #x4dat#.fonctions_def AS f ON (f.id = e.fonction) |
97c79f14 FB |
112 | LEFT JOIN #x4dat#.emploi_ss_secteur AS ss4 ON (ss4.id = e.ss_secteur) |
113 | LEFT JOIN #x4dat#.emploi_secteur AS s4 ON (s4.id = e.secteur) | |
114 | LEFT JOIN profile_job_sector_enum AS s ON (s.name = s4.label) | |
115 | LEFT JOIN profile_job_subsector_enum AS ss ON (ss.name = ss4.label AND ss.sectorid = s.id) | |
e7cc6cb4 | 116 | WHERE e.entreprise != ''; |
c7139c07 SJ |
117 | |
118 | -- vim:set syntax=mysql: |