X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;ds=sidebyside;f=upgrade%2Fnewdirectory-0.0.1%2F11_jobs.sql;h=adb5cdaaf867ed7d2ab74ec4eeef636f76e49d49;hb=c76545c351fae4e2298624ff9ee5bf854dc5a5b6;hp=b2f787dfa79d50987f6512ef365a5a7d137a2601;hpb=e298f94d450bece05e7ccfde220768f8ffdc95b5;p=platal.git

diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql
index b2f787d..adb5cda 100644
--- a/upgrade/newdirectory-0.0.1/11_jobs.sql
+++ b/upgrade/newdirectory-0.0.1/11_jobs.sql
@@ -1,3 +1,49 @@
+DROP TABLE IF EXISTS profile_job_sector_enum;
+
+CREATE TABLE profile_job_sector_enum (
+  id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
+  name VARCHAR(256) NOT NULL DEFAULT '',
+  PRIMARY KEY(id),
+  UNIQUE INDEX(name(128))
+) ENGINE=InnoDB, CHARSET=utf8;
+
+
+DROP TABLE IF EXISTS profile_job_subsector_enum;
+
+CREATE TABLE profile_job_subsector_enum (
+  id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
+  sectorid TINYINT(2) UNSIGNED NOT NULL,
+  flags SET('optgroup') DEFAULT '' NOT NULL,
+  name VARCHAR(256) NOT NULL DEFAULT '',
+  PRIMARY KEY(id)
+) ENGINE=InnoDB, CHARSET=utf8;
+
+
+DROP TABLE IF EXISTS profile_job_subsubsector_enum;
+
+CREATE TABLE profile_job_subsubsector_enum (
+  id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
+  sectorid TINYINT(2) UNSIGNED NOT NULL,
+  subsectorid TINYINT(3) UNSIGNED NOT NULL,
+  name VARCHAR(256) NOT NULL DEFAULT '',
+  flags SET('display') NOT NULL DEFAULT 'display',
+  PRIMARY KEY(id)
+) ENGINE=InnoDB, CHARSET=utf8;
+
+
+DROP TABLE IF EXISTS profile_job_alternates;
+
+CREATE TABLE profile_job_alternates (
+  id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
+  subsubsectorid SMALLINT(3) UNSIGNED NOT NULL,
+  name VARCHAR(256) NOT NULL DEFAULT '',
+  PRIMARY KEY(id, subsubsectorid),
+  UNIQUE INDEX(name(128))
+) ENGINE=InnoDB, CHARSET=utf8;
+
+
+ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user';
+
 DROP TABLE IF EXISTS profile_job_enum;
 
 CREATE TABLE profile_job_enum (
@@ -20,7 +66,7 @@ CREATE TABLE profile_job (
   pid INT(11) NOT NULL DEFAULT 0,
   jobid INT(6) UNSIGNED NOT NULL DEFAULT 0,
   sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
-  subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
+  subsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
   subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
   description VARCHAR(255) NOT NULL DEFAULT '',
   url VARCHAR(255) NOT NULL DEFAULT '',
@@ -31,20 +77,42 @@ CREATE TABLE profile_job (
   INDEX pid (pid)
 ) ENGINE=InnoDB, CHARSET=utf8;
 
+# Fill sector table with old sectors.
+INSERT INTO profile_job_sector_enum (name)
+     SELECT label
+       FROM #x4dat#.emploi_secteur;
+
+INSERT INTO profile_job_subsector_enum (sectorid, name)
+     SELECT s.id, ss.label
+       FROM #x4dat#.emploi_ss_secteur AS ss
+ INNER JOIN #x4dat#.emploi_secteur AS s4 ON (ss.secteur = s4.id)
+ INNER JOIN profile_job_sector_enum AS s ON (s.name = s4.label);
+
+# Since subsubsectors are not supported by the old sectors set, define
+# a fake value per subsector.
+INSERT INTO profile_job_subsubsector_enum (id, sectorid, subsectorid, name)
+     SELECT ss.id, ss.sectorid, ss.id, CONCAT(s.name, " (", ss.name, ")")
+       FROM profile_job_subsector_enum AS ss
+ INNER JOIN profile_job_sector_enum AS s ON (s.id = ss.sectorid)
+   ORDER BY ss.id;
+
 INSERT IGNORE INTO  profile_job_enum (name, url)
             SELECT  entreprise, web
               FROM  #x4dat#.entreprises
              WHERE  entreprise != '';
 
-INSERT INTO  profile_job (id, pid, jobid, email, pub, email_pub, description)
-     SELECT  e.entrid, e.uid, j.id, e.email, IF(e.pub = '', 'private', e.pub), IF(e.email_pub = '', 'private', e.email_pub),
-             CONCAT_WS(', ', IF(e.poste = '', NULL, e.poste), IF(e.fonction = 0, NULL, f.fonction_fr),
-                       IF(e.ss_secteur IS NULL , IF(e.secteur IS NULL, NULL, s.label), ss.label))
+INSERT INTO  profile_job (id, pid, jobid, email, pub, email_pub, sectorid, subsectorid, subsubsectorid, description)
+     SELECT  e.entrid, e.uid, j.id, e.email,
+             IF(e.pub = '', 'private', e.pub), IF(e.email_pub = '', 'private', e.email_pub),
+             IF(s.id IS NOT NULL, s.id, 0), IF(ss.id IS NOT NULL, ss.id, 0),
+             IF(ss.id IS NOT NULL, ss.id, 0), e.poste
        FROM  #x4dat#.entreprises       AS e
  INNER JOIN  profile_job_enum  AS j  ON (e.entreprise = j.name)
   LEFT JOIN  #x4dat#.fonctions_def     AS f  ON (f.id = e.fonction)
-  LEFT JOIN  #x4dat#.emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur)
-  LEFT JOIN  #x4dat#.emploi_secteur    AS s  ON (s.id = e.secteur)
+  LEFT JOIN  #x4dat#.emploi_ss_secteur AS ss4 ON (ss4.id = e.ss_secteur)
+  LEFT JOIN  #x4dat#.emploi_secteur    AS s4  ON (s4.id = e.secteur)
+  LEFT JOIN  profile_job_sector_enum   AS s   ON (s.name = s4.label)
+  LEFT JOIN  profile_job_subsector_enum AS ss ON (ss.name = ss4.label AND ss.sectorid = s.id)
       WHERE  e.entreprise != '';
 
 -- vim:set syntax=mysql: