From 100e66fc8ab2b48621b287d50079558d83187278 Mon Sep 17 00:00:00 2001 From: Florent Bruneau Date: Sat, 20 Feb 2010 15:24:18 +0100 Subject: [PATCH] Prepare db migration. Note: * migration must not alter the old db, so we should create a new db and a user with only read privileges on previous db. * migration procedure will contain at least 4 steps: 1 - copy old tables that will still exist in the new db 2 - create new tables 3 - perform data insertion 4 - remove temporary tables Signed-off-by: Florent Bruneau --- upgrade/account/{03_carnet.sql => 02_carnet.sql} | 0 upgrade/account/02_forums.sql | 47 ---------------- upgrade/account/{04_carva.sql => 03_carva.sql} | 0 upgrade/account/{05_emails.sql => 04_emails.sql} | 0 upgrade/account/06_logs.sql | 25 -------- upgrade/account/07_payment.sql | 41 -------------- upgrade/account/08_groups.sql | 67 ---------------------- upgrade/account/09_photos.sql | 11 ---- upgrade/account/20_naming_convention.sql | 23 ++++++++ upgrade/account/99_insertion.sql | 28 ++++----- upgrade/account/copy_tables.php | 72 ++++++++++++++++++++++++ upgrade/account/upgrade.sh | 11 +++- upgrade/inc/pervasive.sh | 5 ++ upgrade/newdirectory-0.0.1/00_names.sql | 16 +++--- upgrade/newdirectory-0.0.1/01_addrcomment.sql | 2 - upgrade/newdirectory-0.0.1/02_networking.sql | 6 +- upgrade/newdirectory-0.0.1/05_nationalities.sql | 15 ----- upgrade/newdirectory-0.0.1/06_education.sql | 7 ++- upgrade/newdirectory-0.0.1/11_jobs.sql | 10 ++-- upgrade/newdirectory-0.0.1/13_mentoring.sql | 4 +- upgrade/newdirectory-0.0.1/15_addresses.sql | 3 +- 21 files changed, 144 insertions(+), 249 deletions(-) rename upgrade/account/{03_carnet.sql => 02_carnet.sql} (100%) delete mode 100644 upgrade/account/02_forums.sql rename upgrade/account/{04_carva.sql => 03_carva.sql} (100%) rename upgrade/account/{05_emails.sql => 04_emails.sql} (100%) delete mode 100644 upgrade/account/06_logs.sql delete mode 100644 upgrade/account/07_payment.sql delete mode 100644 upgrade/account/08_groups.sql delete mode 100644 upgrade/account/09_photos.sql create mode 100644 upgrade/account/20_naming_convention.sql create mode 100755 upgrade/account/copy_tables.php delete mode 100644 upgrade/newdirectory-0.0.1/01_addrcomment.sql delete mode 100644 upgrade/newdirectory-0.0.1/05_nationalities.sql diff --git a/upgrade/account/03_carnet.sql b/upgrade/account/02_carnet.sql similarity index 100% rename from upgrade/account/03_carnet.sql rename to upgrade/account/02_carnet.sql diff --git a/upgrade/account/02_forums.sql b/upgrade/account/02_forums.sql deleted file mode 100644 index 319d3d2..0000000 --- a/upgrade/account/02_forums.sql +++ /dev/null @@ -1,47 +0,0 @@ -# Move forums tables to x4dat -#RENAME forums.list -# TO x4dat.forums; -#RENAME forums.abos -# TO x4dat.forum_subs; -#RENAME forums.innd -# TO x4dat.forum_innd; -#RENAME forums.porfils -# TO x4dat.forum_profiles; -#DROP DATABASE forums; - -## Dev version of previous line -# (non destructive) -CREATE TABLE #x4dat#.forums - LIKE #forums#.list; -INSERT INTO #x4dat#.forums - SELECT * - FROM #forums#.list; - -CREATE TABLE #x4dat#.forum_subs - LIKE #forums#.abos; -INSERT INTO #x4dat#.forum_subs - SELECT * - FROM #forums#.abos; - -CREATE TABLE #x4dat#.forum_innd - LIKE #forums#.innd; -INSERT INTO #x4dat#.forum_innd - SELECT * - FROM #forums#.innd; - -CREATE TABLE #x4dat#.forum_profiles - LIKE forums#.profils; -INSERT INTO #x4dat#.forum_profiles - SELECT * - FROM forums.profils; - - -# Conform to naming convention. - ALTER TABLE forums -CHANGE COLUMN nom name VARCHAR(64) NOT NULL; - - ALTER TABLE forum_profiles -CHANGE COLUMN nom name VARCHAR(64) NOT NULL, - ADD COLUMN last_seen TIMESTAMP NOT NULL DEFAULT '0000-00-00'; - -# vim:set syntax=mysql: diff --git a/upgrade/account/04_carva.sql b/upgrade/account/03_carva.sql similarity index 100% rename from upgrade/account/04_carva.sql rename to upgrade/account/03_carva.sql diff --git a/upgrade/account/05_emails.sql b/upgrade/account/04_emails.sql similarity index 100% rename from upgrade/account/05_emails.sql rename to upgrade/account/04_emails.sql diff --git a/upgrade/account/06_logs.sql b/upgrade/account/06_logs.sql deleted file mode 100644 index 4722f7f..0000000 --- a/upgrade/account/06_logs.sql +++ /dev/null @@ -1,25 +0,0 @@ -CREATE TABLE log_actions - LIKE logger.actions; - INSERT INTO log_actions - SELECT * - FROM logger.actions; - -CREATE TABLE log_events - LIKE logger.events; - INSERT INTO log_events - SELECT * - FROM logger.events; - -CREATE TABLE log_last_sessions - LIKE logger.last_sessions; - INSERT INTO log_last_sessions - SELECT * - FROM logger.last_sessions; - -CREATE TABLE log_sessions - LIKE logger.sessions; - INSERT INTO log_sessions - SELECT * - FROM logger.sessions; - -# vim:set ft=mysql: diff --git a/upgrade/account/07_payment.sql b/upgrade/account/07_payment.sql deleted file mode 100644 index 52acbb0..0000000 --- a/upgrade/account/07_payment.sql +++ /dev/null @@ -1,41 +0,0 @@ -CREATE TABLE payment_codeC - LIKE paiement.codeC; - INSERT INTO payment_codeC - SELECT * - FROM paiement.codeC; - -CREATE TABLE payment_codeRCB - LIKE paiement.codeRCB; - INSERT INTO payment_codeRCB - SELECT * - FROM paiement.codeRCB; - -CREATE TABLE payment_methods - LIKE paiement.methodes; - INSERT INTO payment_methods - SELECT * - FROM paiement.methodes; - -CREATE TABLE payments - LIKE paiement.paiements; - INSERT INTO payments - SELECT * - FROM paiement.paiements; - -CREATE TABLE payment_transactions - LIKE paiement.transactions; - INSERT INTO payment_transactions - SELECT * - FROM paiement.transactions; - -# Conform to naming convention - ALTER TABLE payments -CHANGE COLUMN montant_def amount_def DECIMAL(10,2) NOT NULL DEFAULT 0.00, -CHANGE COLUMN montant_min amount_min DECIMAL(10,2) NOT NULL DEFAULT 0.00, -CHANGE COLUMN montant_max amount_max DECIMAL(10,2) NOT NULL DEFAULT 0.00; - - ALTER TABLE payment_transactions -CHANGE COLUMN montant amount VARCHAR(15) NOT NULL DEFAULT '0.00', -CHANGE COLUMN cle pkey VARCHAR(5) NOT NULL; - -# vim:set ft=mysql: diff --git a/upgrade/account/08_groups.sql b/upgrade/account/08_groups.sql deleted file mode 100644 index 58e13b6..0000000 --- a/upgrade/account/08_groups.sql +++ /dev/null @@ -1,67 +0,0 @@ -CREATE TABLE group_announces - LIKE groupex.announces; - INSERT INTO group_announces - SELECT * - FROM groupex.announces; - -CREATE TABLE group_announces_photo - LIKE groupex.announces_photo; - INSERT INTO group_announces_photo - SELECT * - FROM groupex.announces_photo; - -CREATE TABLE group_announces_read - LIKE groupex.announces_read; - INSERT INTO group_announces_read - SELECT * - FROM groupex.announces_read; - -CREATE TABLE groups - LIKE groupex.asso; - INSERT INTO groups - SELECT * - FROM groupex.asso; - -CREATE TABLE group_dom - LIKE groupex.dom; - INSERT INTO group_dom - SELECT * - FROM groupex.dom; - -CREATE TABLE group_events - LIKE groupex.evenements; - INSERT INTO group_events - SELECT * - FROM groupex.evenements; - -CREATE TABLE group_event_items - LIKE groupex.evenements_items; - INSERT INTO group_event_items - SELECT * - FROM groupex.evenements_items; - -CREATE TABLE group_event_participants - LIKE groupex.evenements_participants; - INSERT INTO group_event_participants - SELECT * - FROM groupex.evenements_participants; - -CREATE TABLE group_members - LIKE groupex.membres; - INSERT INTO group_members - SELECT * - FROM groupex.membres; - -CREATE TABLE group_member_sub_requests - LIKE groupex.membres_sub_requests; - INSERT INTO group_member_sub_requests - SELECT * - FROM groupex.membres_sub_requests; - -CREATE TABLE group_auth - LIKE x4dat.groupesx_auth; - INSERT INTO group_auth - SELECT * - FROM x4dat.groupesx_auth; - -# vim:set ft=mysql: diff --git a/upgrade/account/09_photos.sql b/upgrade/account/09_photos.sql deleted file mode 100644 index b09a02f..0000000 --- a/upgrade/account/09_photos.sql +++ /dev/null @@ -1,11 +0,0 @@ -CREATE TABLE profile_photos - LIKE photo; - INSERT INTO profile_photos - SELECT * - FROM photo; - - ALTER TABLE profile_photos -CHANGE COLUMN uid pid INT(6) not null; - - -# vim:set ft=mysql: diff --git a/upgrade/account/20_naming_convention.sql b/upgrade/account/20_naming_convention.sql new file mode 100644 index 0000000..fcbbf58 --- /dev/null +++ b/upgrade/account/20_naming_convention.sql @@ -0,0 +1,23 @@ +# forums + ALTER TABLE forums +CHANGE COLUMN nom name VARCHAR(64) NOT NULL; + + ALTER TABLE forum_profiles +CHANGE COLUMN nom name VARCHAR(64) NOT NULL, + ADD COLUMN last_seen TIMESTAMP NOT NULL DEFAULT '0000-00-00'; + +# payment + ALTER TABLE payments +CHANGE COLUMN montant_def amount_def DECIMAL(10,2) NOT NULL DEFAULT 0.00, +CHANGE COLUMN montant_min amount_min DECIMAL(10,2) NOT NULL DEFAULT 0.00, +CHANGE COLUMN montant_max amount_max DECIMAL(10,2) NOT NULL DEFAULT 0.00; + + ALTER TABLE payment_transactions +CHANGE COLUMN montant amount VARCHAR(15) NOT NULL DEFAULT '0.00', +CHANGE COLUMN cle pkey VARCHAR(5) NOT NULL; + +# photo + ALTER TABLE profile_photos +CHANGE COLUMN uid pid INT(6) not null; + +# vim:set ft=mysql: diff --git a/upgrade/account/99_insertion.sql b/upgrade/account/99_insertion.sql index f0cb142..9d9b18f 100644 --- a/upgrade/account/99_insertion.sql +++ b/upgrade/account/99_insertion.sql @@ -22,8 +22,8 @@ insert into accounts IF(q.core_mail_fmt = 'html', 'html', 'text') AS email_format, q.skin AS skin, q.last_version AS last_version - from auth_user_md5 as u - left join auth_user_quick as q on (q.user_id = u.user_id) + from #x4dat#.auth_user_md5 as u + left join #x4dat#.auth_user_quick as q on (q.user_id = u.user_id) where hruid is not null; # Insert carnet-relative data @@ -34,16 +34,16 @@ insert into watch IF(ws3.cid IS NULL, '', 'death'), ',', IF(ws4.cid IS NULL, '', 'birthday')) AS actions, q.watch_last as last - from auth_user_quick as q - left join watch_sub as ws1 on (ws1.uid = q.user_id and ws1.cid = 1) - left join watch_sub as ws2 on (ws2.uid = q.user_id and ws2.cid = 2) - left join watch_sub as ws3 on (ws3.uid = q.user_id and ws3.cid = 3) - left join watch_sub as ws4 on (ws4.uid = q.user_id and ws4.cid = 4); + from #x4dat#.auth_user_quick as q + left join #x4dat#.watch_sub as ws1 on (ws1.uid = q.user_id and ws1.cid = 1) + left join #x4dat#.watch_sub as ws2 on (ws2.uid = q.user_id and ws2.cid = 2) + left join #x4dat#.watch_sub as ws3 on (ws3.uid = q.user_id and ws3.cid = 3) + left join #x4dat#.watch_sub as ws4 on (ws4.uid = q.user_id and ws4.cid = 4); # Insert carvas insert into carvas select user_id, redirecturl - from auth_user_quick + from #x4dat#.auth_user_quick where LENGTH(redirecturl) > 0; # Insert all existing profiles @@ -62,30 +62,30 @@ insert into profiles IF(q.emails_alias_pub = 'public', 'public', 'private') AS alias_pub, u.nationalite AS nationality1, u.nationalite2 AS nationality2, u.nationalite3 AS nationality3, u.date AS last_change - from auth_user_md5 AS u - left join auth_user_quick AS q ON (u.user_id = q.user_id) + from #x4dat#.auth_user_md5 AS u + left join #x4dat#.auth_user_quick AS q ON (u.user_id = q.user_id) where u.hruid is not null; # Add associations account <-> profile insert into account_profiles select user_id AS uid, user_id AS pid, 'owner' AS perms - from auth_user_md5 + from #x4dat#.auth_user_md5 where hruid is not null; # Update banana last_seen timetamp update forum_profiles as fp -inner join auth_user_quick as q ON (q.user_id = fp.uid) +inner join #x4dat#.auth_user_quick as q ON (q.user_id = fp.uid) set fp.uid = fp.uid, fp.tree_unread = fp.tree_unread, fp.tree_read = fp.tree_read, fp.last_seen = q.banana_last; insert ignore into forum_profiles (uid, last_seen) select user_id as uid, banana_last as last_seen - from auth_user_quick + from #x4dat#.auth_user_quick where banana_last >= DATE_SUB(NOW(), INTERVAL 6 MONTH); # Mail storage has been moved out of account settings insert into email_options select user_id as uid, mail_storage as storage - from auth_user_md5; + from #x4dat#.auth_user_md5; # vim:set syntax=mysql: diff --git a/upgrade/account/copy_tables.php b/upgrade/account/copy_tables.php new file mode 100755 index 0000000..a7c57f5 --- /dev/null +++ b/upgrade/account/copy_tables.php @@ -0,0 +1,72 @@ +#!/usr/bin/php5 -q + diff --git a/upgrade/account/upgrade.sh b/upgrade/account/upgrade.sh index d55aee2..c497ddb 100644 --- a/upgrade/account/upgrade.sh +++ b/upgrade/account/upgrade.sh @@ -3,11 +3,16 @@ . ../inc/pervasive.sh ########################################################### +echo "Setting up new database: target db is $DATABASE, source prefix is $DBPREFIX" +echo -n "* copying tables" +./copy_tables.php || die "ERROR" +echo " OK" + for sql in *.sql do - echo -n $sql - (sed -e "s/#\([0-9a-z]*\)#/${DBPREFIX}\1/g" < $sql | $MYSQL $DATABASE &>/dev/null) || echo -n " ERROR" - echo . + echo -n "* running $sql" + (sed -e "s/#\([0-9a-z]*\)#/${DBPREFIX}\1/g" < $sql | $MYSQL $DATABASE &>/dev/null) || die "ERROR" + echo " OK" done ########################################################### diff --git a/upgrade/inc/pervasive.sh b/upgrade/inc/pervasive.sh index f5b279e..0e15c34 100755 --- a/upgrade/inc/pervasive.sh +++ b/upgrade/inc/pervasive.sh @@ -15,6 +15,11 @@ if [[ -n "${DBPREFIX}" ]]; then fi declare -r DATABASE="${DBPREFIX}x4dat" +function die() { + echo $1 + exit 1 +} + function mailman_stop() { echo -n "stops mailman" /etc/init.d/mailman stop &>/dev/null diff --git a/upgrade/newdirectory-0.0.1/00_names.sql b/upgrade/newdirectory-0.0.1/00_names.sql index 6260e32..39d1b1d 100644 --- a/upgrade/newdirectory-0.0.1/00_names.sql +++ b/upgrade/newdirectory-0.0.1/00_names.sql @@ -20,8 +20,8 @@ INSERT INTO profile_display (pid, yourself, public_name, private_name, director CONCAT(IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), ' ', u.prenom), CONCAT(u.prenom, ' ', IF(u.nom_usage != '', u.nom_usage, u.nom)), CONCAT(IF(u.nom_usage != '', u.nom_usage, u.nom), ' ', u.prenom) - FROM auth_user_md5 AS u - LEFT JOIN auth_user_quick AS q ON (u.user_id = q.user_id); + FROM #x4dat#.auth_user_md5 AS u + LEFT JOIN #x4dat#.auth_user_quick AS q ON (u.user_id = q.user_id); DROP TABLE IF EXISTS profile_name_enum; @@ -73,37 +73,37 @@ CREATE TABLE IF NOT EXISTS profile_name ( INSERT INTO profile_name (pid, name, typeid) SELECT u.user_id, u.nom, e.id - FROM auth_user_md5 AS u + FROM #x4dat#.auth_user_md5 AS u INNER JOIN profile_name_enum AS e ON (e.name = 'Nom patronymique') WHERE nom != ''; INSERT INTO profile_name (pid, name, typeid) SELECT u.user_id, u.nom_ini, e.id - FROM auth_user_md5 AS u + FROM #x4dat#.auth_user_md5 AS u INNER JOIN profile_name_enum AS e ON (e.name = 'Nom initial') WHERE nom_ini != ''; INSERT INTO profile_name (pid, name, typeid) SELECT u.user_id, u.prenom, e.id - FROM auth_user_md5 AS u + FROM #x4dat#.auth_user_md5 AS u INNER JOIN profile_name_enum AS e ON (e.name = 'Prénom') WHERE prenom != ''; INSERT INTO profile_name (pid, name, typeid) SELECT u.user_id, u.prenom_ini, e.id - FROM auth_user_md5 AS u + FROM #x4dat#.auth_user_md5 AS u INNER JOIN profile_name_enum AS e ON (e.name = 'Prénom initial') WHERE prenom_ini != ''; INSERT INTO profile_name (pid, name, typeid) SELECT u.user_id, u.nom_usage, e.id - FROM auth_user_md5 AS u + FROM #x4dat#.auth_user_md5 AS u INNER JOIN profile_name_enum AS e ON (e.name = 'Nom usuel') WHERE nom_usage != ''; INSERT INTO profile_name (pid, name, typeid) SELECT q.user_id, q.profile_nick, e.id - FROM auth_user_quick AS q + FROM #x4dat#.auth_user_quick AS q INNER JOIN profile_name_enum AS e ON (e.name = 'Surnom') WHERE profile_nick != ''; diff --git a/upgrade/newdirectory-0.0.1/01_addrcomment.sql b/upgrade/newdirectory-0.0.1/01_addrcomment.sql deleted file mode 100644 index 7de4690..0000000 --- a/upgrade/newdirectory-0.0.1/01_addrcomment.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE `adresses` ADD `comment` varchar(100) NOT NULL; - diff --git a/upgrade/newdirectory-0.0.1/02_networking.sql b/upgrade/newdirectory-0.0.1/02_networking.sql index 8b3e0d6..fa01af8 100644 --- a/upgrade/newdirectory-0.0.1/02_networking.sql +++ b/upgrade/newdirectory-0.0.1/02_networking.sql @@ -23,7 +23,7 @@ INSERT INTO `profile_networking_enum` (`network_type`, `name`, `icon`, `filter`) INSERT INTO `profile_networking` (`uid`, `nwid`, `network_type`, `address`, `pub`) SELECT `user_id`, 0, 0, `profile_web`, `profile_web_pub` - FROM `auth_user_quick` + FROM #x4dat#.`auth_user_quick` WHERE `profile_web` <> ""; -- Modify watch_profile to update 'field' from web to networking @@ -39,8 +39,4 @@ ALTER TABLE `watch_profile` 'networking', 'appli1', 'appli2', 'addresses', 'section', 'binets', 'medals', 'cv', 'jobs', 'photo'); --- Drop old web URL columns -ALTER TABLE `auth_user_quick` DROP COLUMN `profile_web`; -ALTER TABLE `auth_user_quick` DROP COLUMN `profile_web_pub`; - # vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/05_nationalities.sql b/upgrade/newdirectory-0.0.1/05_nationalities.sql deleted file mode 100644 index a7a585e..0000000 --- a/upgrade/newdirectory-0.0.1/05_nationalities.sql +++ /dev/null @@ -1,15 +0,0 @@ -ALTER TABLE auth_user_md5 ADD COLUMN nationalite2 CHAR(2) DEFAULT NULL, - ADD COLUMN nationalite3 CHAR(2) DEFAULT NULL, - ADD KEY nationalite2 (nationalite2), - ADD KEY nationalite3 (nationalite3), - MODIFY nationalite CHAR(2) DEFAULT NULL; - -UPDATE auth_user_md5 SET nationalite=NULL WHERE nationalite="00" OR nationalite=''; - -DELETE FROM geoloc_pays WHERE a2="00"; - -ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nationalite', 'nationalite2', 'nationalite3', - 'nick', 'web', 'networking', 'appli1', 'appli2', 'addresses', - 'section', 'binets', 'medals', 'cv', 'jobs', 'photo'); - -# vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/06_education.sql b/upgrade/newdirectory-0.0.1/06_education.sql index 708e8ef..60d75e5 100644 --- a/upgrade/newdirectory-0.0.1/06_education.sql +++ b/upgrade/newdirectory-0.0.1/06_education.sql @@ -58,16 +58,16 @@ INSERT INTO profile_education_degree_enum (degree) INSERT INTO profile_education_degree (eduid, degreeid) SELECT a.id, d.id - FROM applis_def AS a + FROM #x4dat#.applis_def AS a INNER JOIN profile_education_degree_enum AS d ON (FIND_IN_SET(d.degree, a.type)); INSERT INTO profile_education_enum (id, name, url) SELECT id, text, url - FROM applis_def; + FROM #x4dat#.applis_def; INSERT INTO profile_education (id, uid, eduid, degreeid) SELECT a.ordre, a.uid, a.aid, d.id - FROM applis_ins AS a + FROM #x4dat#.applis_ins AS a INNER JOIN profile_education_degree_enum AS d ON (a.type = d.degree); UPDATE watch_profile AS w1 @@ -86,6 +86,7 @@ ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nation UPDATE watch_profile SET field = 'edus' WHERE field = 'appli1'; + DELETE FROM watch_profile WHERE field = 'appli2'; ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nationalite', 'nationalite2', diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql index b10dbc6..7c175a4 100644 --- a/upgrade/newdirectory-0.0.1/11_jobs.sql +++ b/upgrade/newdirectory-0.0.1/11_jobs.sql @@ -33,16 +33,16 @@ CREATE TABLE IF NOT EXISTS profile_job ( INSERT IGNORE INTO profile_job_enum (name, url) SELECT entreprise, web - FROM entreprises; + FROM #x4dat#.entreprises; 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, 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); -- vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/13_mentoring.sql b/upgrade/newdirectory-0.0.1/13_mentoring.sql index 90987c1..9191616 100644 --- a/upgrade/newdirectory-0.0.1/13_mentoring.sql +++ b/upgrade/newdirectory-0.0.1/13_mentoring.sql @@ -11,7 +11,7 @@ CREATE TABLE IF NOT EXISTS profile_mentor ( INSERT INTO profile_mentor (uid, expertise) SELECT uid, expertise - FROM mentor; + FROM #x4dat#.mentor; CREATE TABLE IF NOT EXISTS profile_mentor_country ( uid INT(11) NOT NULL DEFAULT 0, @@ -22,7 +22,7 @@ CREATE TABLE IF NOT EXISTS profile_mentor_country ( INSERT INTO profile_mentor_country (uid, 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, diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql index 2311f47..276bea9 100644 --- a/upgrade/newdirectory-0.0.1/15_addresses.sql +++ b/upgrade/newdirectory-0.0.1/15_addresses.sql @@ -42,7 +42,7 @@ INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, l IF(FIND_IN_SET('courrier', 'statut'), 'mail,', ''), IF(FIND_IN_SET('active', 'statut'), 'current,', ''), IF(FIND_IN_SET('temporaire', 'statut'), 'temporary', '')) - FROM adresses; + FROM #x4dat#.adresses; CREATE TABLE IF NOT EXISTS geoloc_countries ( iso_3166_1_a2 CHAR(2) NOT NULL, @@ -78,6 +78,7 @@ INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, wor phonePrefix, phoneFormat, licensePlate) SELECT a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate FROM geoloc_pays; +DROP TABLE geoloc_pays; CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, -- 2.1.4