Merge branch 'fusionax' into account

[platal.git] / upgrade / newdirectory-0.0.1 / 12_secteurs.sql

diff --git a/upgrade/newdirectory-0.0.1/12_secteurs.sql b/upgrade/newdirectory-0.0.1/12_secteurs.sql
index f659ddb..799870c 100644 (file)
--- 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';