X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F13_mentoring.sql;h=5f35293f07fd3ad3ae81e3a5f129172d0f67ad18;hb=1bb4b4c087704402f2155e267d0a50dae80d9d52;hp=dbd81271e81aec4b35d91f29fb4f2e3aa15f5653;hpb=f4035298fd421e87b9a58f6a801b53da348613de;p=platal.git

diff --git a/upgrade/newdirectory-0.0.1/13_mentoring.sql b/upgrade/newdirectory-0.0.1/13_mentoring.sql
index dbd8127..5f35293 100644
--- a/upgrade/newdirectory-0.0.1/13_mentoring.sql
+++ b/upgrade/newdirectory-0.0.1/13_mentoring.sql
@@ -2,32 +2,41 @@ DROP TABLE IF EXISTS profile_mentor;
 DROP TABLE IF EXISTS profile_mentor_country;
 DROP TABLE IF EXISTS profile_mentor_sector;
 
-CREATE TABLE IF NOT EXISTS profile_mentor (
-  uid INT(11) NOT NULL DEFAULT 0,
+CREATE TABLE profile_mentor (
+  pid INT(11) NOT NULL DEFAULT 0,
   expertise TEXT NOT NULL,
-  PRIMARY KEY (uid),
-  FULLTEXT INDEX (expertise)
-) CHARSET=utf8;
+  PRIMARY KEY (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
 
-INSERT INTO  profile_mentor (uid, expertise)
+INSERT INTO  profile_mentor (pid, expertise)
      SELECT  uid, expertise
-       FROM  mentor;
+       FROM  #x4dat#.mentor;
 
-CREATE TABLE IF NOT EXISTS profile_mentor_country (
-  uid INT(11) NOT NULL DEFAULT 0,
+CREATE TABLE profile_mentor_country (
+  pid INT(11) NOT NULL DEFAULT 0,
   country CHAR(2) NOT NULL DEFAULT "FR",
-  PRIMARY KEY (uid, country)
-) CHARSET=utf8;
+  PRIMARY KEY (pid, country),
+  INDEX pid (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
 
-INSERT INTO  profile_mentor_country (uid, country)
+INSERT INTO  profile_mentor_country (pid, country)
      SELECT  uid, pid
-       FROM  mentor_pays;
+       FROM  #x4dat#.mentor_pays;
 
-CREATE TABLE IF NOT EXISTS profile_mentor_sector (
-  uid INT(11) NOT NULL DEFAULT 0,
+CREATE TABLE profile_mentor_sector (
+  pid INT(11) NOT NULL DEFAULT 0,
   sectorid TINYINT(2) UNSIGNED NOT NULL,
-  subsectorid TINYINT(3) UNSIGNED NOT NULL,
-  PRIMARY KEY (uid, sectorid, subsectorid)
-) CHARSET=utf8;
+  subsectorid SMALLINT(3) UNSIGNED NOT NULL,
+  PRIMARY KEY (pid, sectorid, subsectorid),
+  INDEX pid (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
+
+INSERT IGNORE INTO profile_mentor_sector (pid, sectorid, subsectorid)
+     SELECT ms4.uid, s.id, ss.id
+       FROM #x4dat#.mentor_secteurs AS ms4
+ INNER JOIN #x4dat#.emploi_secteur AS s4 ON (s4.id = ms4.secteur)
+ INNER JOIN profile_job_sector_enum AS s ON (s.name = s4.label)
+  LEFT JOIN #x4dat#.emploi_ss_secteur AS ss4 ON (ss4.id = ms4.ss_secteur)
+  LEFT JOIN profile_job_subsector_enum AS ss ON (ss.name = ss4.label);
 
 -- vim:set syntax=mysql: