From 761f1bcb9547d5b3b0f5e4b96a2054e8a0f6d376 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Sat, 28 Mar 2009 15:42:51 +0100 Subject: [PATCH] Polishes sectors list creation, adds alternative name of sectors. --- upgrade/newdirectory-0.0.1/12_secteurs.sql | 66 ++++------------------ upgrade/newdirectory-0.0.1/README | 3 + .../newdirectory-0.0.1/alternate_subsubsectors.php | 49 ++++++++++++++++ 3 files changed, 62 insertions(+), 56 deletions(-) create mode 100755 upgrade/newdirectory-0.0.1/alternate_subsubsectors.php diff --git a/upgrade/newdirectory-0.0.1/12_secteurs.sql b/upgrade/newdirectory-0.0.1/12_secteurs.sql index f659ddb..67c9bdd 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 ENUM('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'; diff --git a/upgrade/newdirectory-0.0.1/README b/upgrade/newdirectory-0.0.1/README index ac47951..7fc2876 100644 --- a/upgrade/newdirectory-0.0.1/README +++ b/upgrade/newdirectory-0.0.1/README @@ -1 +1,4 @@ Il faudra déplacer le yourself qui est mis dans profile_display dans le script 00_names.sql vers l'account. + +Le 12_secteurs.sql est à faire avant le alternate_subsubsectors.php et pour que ce dernier fonctionne, il faut que le fichier +arbo-UTF8.xml soit dans le même dossier. diff --git a/upgrade/newdirectory-0.0.1/alternate_subsubsectors.php b/upgrade/newdirectory-0.0.1/alternate_subsubsectors.php new file mode 100755 index 0000000..fc6b856 --- /dev/null +++ b/upgrade/newdirectory-0.0.1/alternate_subsubsectors.php @@ -0,0 +1,49 @@ +#!/usr/bin/php5 +debug = 0; //do not store backtraces + +$data = implode('', file('arbo-UTF8.xml')); +$parser = xml_parser_create(); +xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0); +xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, 1); +xml_parse_into_struct($parser, $data, $values, $tags); +xml_parser_free($parser); + +// loop through the structures +foreach ($values as $val) { + if ($val['tag'] == 'grand-domaine' && $val['type'] == 'open') { + $res = XDB::execute('INSERT INTO profile_job_sector_enum (name) + VALUES ({?})', + ucfirst(strtolower($val['attributes']['intitule']))); + $sectorid = XDB::insertId(); + } + if ($val['tag'] == 'domaine' && $val['type'] == 'open') { + $res = XDB::execute('INSERT INTO profile_job_subsector_enum (sectorid, name) + VALUES ({?}, {?})', + $sectorid, $val['attributes']['intitule']); + $subsectorid = XDB::insertId(); + } + if ($val['tag'] == 'domaine-intermediaire' && $val['type'] == 'open') { + $res = XDB::execute('INSERT INTO profile_job_subsector_enum (sectorid, name, flags) + VALUES ({?}, {?}, \'optgroup\')', + $sectorid, $val['attributes']['intitule']); + } + if ($val['tag'] == 'fiche' && $val['type'] == 'open') { + $res = XDB::execute('INSERT INTO profile_job_subsubsector_enum (sectorid, subsectorid, name) + VALUES ({?}, {?}, {?})', + $sectorid, $subsectorid, $val['attributes']['intitule']); + $subsubsectorid = XDB::insertId(); + $id = 0; + } + if ($val['tag'] == 'appellation' && $val['type'] == 'complete') { + $res = XDB::execute('INSERT INTO profile_job_alternates (id, subsubsectorid, name) + VALUES ({?}, {?}, {?})', + $id, $subsubsectorid, $val['attributes']['intitule']); + ++$id; + } +} + +/* vim:set et sw=4 sts=4 ts=4: */ +?> -- 2.1.4