Fixes unsubscription from group

[platal.git] / upgrade / newdirectory-0.0.1 / 11_jobs.sql

diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql
index b10dbc6..b2f787d 100644 (file)
--- a/upgrade/newdirectory-0.0.1/11_jobs.sql
+++ b/upgrade/newdirectory-0.0.1/11_jobs.sql
@@ -1,6 +1,6 @@
 DROP TABLE IF EXISTS profile_job_enum;
 
-CREATE TABLE IF NOT EXISTS profile_job_enum (
+CREATE TABLE profile_job_enum (
   id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL DEFAULT '',
   acronym VARCHAR(255) NOT NULL DEFAULT '',
@@ -11,13 +11,13 @@ CREATE TABLE IF NOT EXISTS profile_job_enum (
   AX_code BIGINT(10) UNSIGNED NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (name)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
 DROP TABLE IF EXISTS profile_job;
 
-CREATE TABLE IF NOT EXISTS profile_job (
+CREATE TABLE profile_job (
   id TINYINT(1) UNSIGNED NOT NULL,
-  uid INT(11) NOT NULL DEFAULT 0,
+  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,
@@ -27,22 +27,24 @@ CREATE TABLE IF NOT EXISTS profile_job (
   email VARCHAR(255) NOT NULL DEFAULT '',
   pub ENUM('private', 'ax', 'public') DEFAULT 'private',
   email_pub ENUM('private', 'ax', 'public') DEFAULT 'private',
-  PRIMARY KEY (uid, id),
-  INDEX uid (uid)
-) CHARSET=utf8;
+  PRIMARY KEY (pid, id),
+  INDEX pid (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
 
 INSERT IGNORE INTO  profile_job_enum (name, url)
             SELECT  entreprise, web
-              FROM  entreprises;
+              FROM  #x4dat#.entreprises
+             WHERE  entreprise != '';
 
-INSERT INTO  profile_job (id, uid, jobid, email, pub, email_pub, description)
-     SELECT  e.entrid, e.uid, j.id, e.email, e.pub, e.email_pub,
+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))
-       FROM  entreprises       AS e
+       FROM  #x4dat#.entreprises       AS e
  INNER JOIN  profile_job_enum  AS j  ON (e.entreprise = j.name)
-  LEFT JOIN  fonctions_def     AS f  ON (f.id = e.fonction)
-  LEFT JOIN  emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur)
-  LEFT JOIN  emploi_secteur    AS s  ON (s.id = e.secteur);
+  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)
+      WHERE  e.entreprise != '';
 
 -- vim:set syntax=mysql: