| 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 | |
| 47 | DROP TABLE IF EXISTS profile_job_enum; |
| 48 | |
| 49 | CREATE TABLE profile_job_enum ( |
| 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 '', |
| 54 | email VARCHAR(255) NOT NULL DEFAULT '', |
| 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) |
| 60 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 61 | |
| 62 | DROP TABLE IF EXISTS profile_job; |
| 63 | |
| 64 | CREATE TABLE profile_job ( |
| 65 | id TINYINT(1) UNSIGNED NOT NULL, |
| 66 | pid INT(11) NOT NULL DEFAULT 0, |
| 67 | jobid INT(6) UNSIGNED NOT NULL DEFAULT 0, |
| 68 | sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, |
| 69 | subsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, |
| 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', |
| 76 | PRIMARY KEY (pid, id), |
| 77 | INDEX pid (pid) |
| 78 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 79 | |
| 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 | |
| 99 | INSERT IGNORE INTO profile_job_enum (name, url) |
| 100 | SELECT entreprise, web |
| 101 | FROM #x4dat#.entreprises |
| 102 | WHERE entreprise != ''; |
| 103 | |
| 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 |
| 109 | FROM #x4dat#.entreprises AS e |
| 110 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name) |
| 111 | LEFT JOIN #x4dat#.fonctions_def AS f ON (f.id = e.fonction) |
| 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) |
| 116 | WHERE e.entreprise != ''; |
| 117 | |
| 118 | -- vim:set syntax=mysql: |