X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F12_secteurs.sql;h=799870c48881e04eb2db96af8df9e09a4e1106e3;hb=c12cc82e1d97b51e19b294942ea9450a34d929cd;hp=f659ddb060134d1b12ee478fd77ec67584f5742c;hpb=950a769bb3f3154de64960ffc5cac111263bc8b0;p=platal.git

diff --git a/upgrade/newdirectory-0.0.1/12_secteurs.sql b/upgrade/newdirectory-0.0.1/12_secteurs.sql
index f659ddb..799870c 100644
--- a/upgrade/newdirectory-0.0.1/12_secteurs.sql
+++ b/upgrade/newdirectory-0.0.1/12_secteurs.sql
@@ -1,38 +1,12 @@
-DROP TABLE IF EXISTS tmp_sectors;
-
-CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sectors (
-  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-  sect CHAR(1) NOT NULL,
-  subsector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
-  subsubsector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
-  name VARCHAR(256) NOT NULL DEFAULT '',
-  PRIMARY KEY(id)
-) CHARSET=utf8;
-
-LOAD DATA LOCAL INFILE 'rome-v3.csv' INTO TABLE  tmp_sectors FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
-(sect, subsector, subsubsector, name);
-
-ALTER TABLE tmp_sectors ADD INDEX (sect);
-ALTER TABLE tmp_sectors ADD INDEX (subsector);
-ALTER TABLE tmp_sectors ADD INDEX (subsubsector);
-ALTER TABLE tmp_sectors ADD INDEX (name);
-
 DROP TABLE IF EXISTS profile_job_sector_enum;
 
 CREATE TABLE IF NOT EXISTS profile_job_sector_enum (
   id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(256) NOT NULL DEFAULT '',
-  sect CHAR(1) NOT NULL,
   PRIMARY KEY(id),
   UNIQUE KEY(name)
 ) CHARSET=utf8;
 
-INSERT INTO  profile_job_sector_enum (name, sect)
-     SELECT  name, sect
-       FROM  tmp_sectors
-      WHERE  subsector = 0 AND subsubsector = 0
-   ORDER BY  id;
-
 DROP TABLE IF EXISTS profile_job_subsector_enum;
 
 CREATE TABLE IF NOT EXISTS profile_job_subsector_enum (
@@ -40,28 +14,9 @@ CREATE TABLE IF NOT EXISTS profile_job_subsector_enum (
   sectorid TINYINT(2) UNSIGNED NOT NULL,
   flags SET('optgroup') DEFAULT '' NOT NULL,
   name VARCHAR(256) NOT NULL DEFAULT '',
-  sect CHAR(1) NOT NULL,
-  subsector TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
   PRIMARY KEY(id)
 ) CHARSET=utf8;
 
-INSERT INTO  profile_job_subsector_enum (sectorid, name, sect, subsector)
-     SELECT  s.id, t.name, t.sect, t.subsector
-       FROM  tmp_sectors             AS t
- INNER JOIN  profile_job_sector_enum AS s ON (t.sect = s.sect)
-      WHERE  t.subsector != 0 AND t.subsubsector = 0
-   ORDER BY  t.id;
-
-    UPDATE  profile_job_subsector_enum AS su
-INNER JOIN  profile_job_sector_enum    AS se
-       SET  flags = 'optgroup'
-     WHERE  (se.name = "Industrie" AND (su.name = "Logistique"
-                                        OR su.name = "Transport aérien et activités aéroportuaires"
-                                        OR su.name = "Transport maritime et fluvial et activités portuaires"
-                                        OR su.name = "Transport terrestre"))
-            OR (se.name = "Transport et logistique" AND (su.name = "Études et supports techniques à l'industrie"
-                                                         OR su.name = "Production industrielle"));
-
 DROP TABLE IF EXISTS profile_job_subsubsector_enum;
 
 CREATE TABLE IF NOT EXISTS profile_job_subsubsector_enum (
@@ -69,20 +24,19 @@ CREATE TABLE IF NOT EXISTS profile_job_subsubsector_enum (
   sectorid TINYINT(2) UNSIGNED NOT NULL,
   subsectorid TINYINT(3) UNSIGNED NOT NULL,
   name VARCHAR(256) NOT NULL DEFAULT '',
-  PRIMARY KEY(id),
-  UNIQUE KEY(name)
+  flags SET('display') NOT NULL DEFAULT 'display',
+  PRIMARY KEY(id)
 ) CHARSET=utf8;
 
-INSERT INTO  profile_job_subsubsector_enum (sectorid, subsectorid, name)
-     SELECT  s.sectorid, s.id, t.name
-       FROM  tmp_sectors                AS t
- INNER JOIN  profile_job_subsector_enum AS s ON (t.sect = s.sect AND t.subsector = s.subsector)
-      WHERE  t.subsector != 0 AND t.subsubsector != 0
-   ORDER BY  t.id;
+DROP TABLE IF EXISTS profile_job_alternates;
 
-ALTER TABLE profile_job_sector_enum DROP COLUMN sect;
-ALTER TABLE profile_job_subsector_enum DROP COLUMN sect;
-ALTER TABLE profile_job_subsector_enum DROP COLUMN subsector;
+CREATE TABLE IF NOT EXISTS 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 KEY(name)
+) CHARSET=utf8;
 
 
 ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user';