From 4e426a0910615590be0293ed6d0407669b8d9dc8 Mon Sep 17 00:00:00 2001 From: Florent Bruneau Date: Tue, 30 Dec 2008 14:46:41 +0100 Subject: [PATCH] Import some fields from auth_user_md5 in profiles. Signed-off-by: Florent Bruneau --- upgrade/account/01_profiles.sql | 12 +++++++++++- upgrade/account/99_insertion.sql | 11 +++++++---- 2 files changed, 18 insertions(+), 5 deletions(-) diff --git a/upgrade/account/01_profiles.sql b/upgrade/account/01_profiles.sql index e5f9cd1..e8eb099 100644 --- a/upgrade/account/01_profiles.sql +++ b/upgrade/account/01_profiles.sql @@ -12,18 +12,28 @@ create table profiles ( birthdate date default null, birthdate_ref date default null, deathdate date default null, + + section tinyint(2) unsigned default null, + cv text default null, freetext mediumtext default null, freetext_pub enum('private', 'public') not null default 'private', medals_pub enum('private', 'public') not null default 'private', alias_pub enum('private', 'public') not null default 'private', + nationality1 char(2) default null, + nationality2 char(2) default null, + nationality3 char(2) default null, + # Last modification date (for notifications) last_change date not null, primary key pid (pid), unique key hrpid (hrpid), unique key xorg_id (xorg_id), - key ax_id (ax_id) + key ax_id (ax_id), + key nationality1 (nationality1), + key nationality2 (nationality2), + key nationality3 (nationality3) ); # vim:set syntax=mysql: diff --git a/upgrade/account/99_insertion.sql b/upgrade/account/99_insertion.sql index a7ecadb..a5df54e 100644 --- a/upgrade/account/99_insertion.sql +++ b/upgrade/account/99_insertion.sql @@ -39,11 +39,14 @@ insert into profiles select u.user_id AS pid, u.hruid AS hrpid, u.matricule AS xorg_id, u.matricule_ax AS ax_id, u.naissance AS birthdate, u.naissance_ini AS birthdate_ref, IF(u.deces = 0, NULL, u.deces) AS deathdate, - u.date AS last_change, + IF(u.section = 0, NULL, u.section) AS section, + IF(LENGTH(u.cv) > 0, u.cv, NULL) AS cv, IF(LENGTH(q.profile_freetext) > 0, q.profile_freetext, NULL) AS freetext, - q.profile_freetext_pub AS freetext_pub, - q.profile_medals_pub AS medals_pub, - q.emails_alias_pub AS alias_pub + IF(q.profile_freetext_pub = 'public', 'public', 'private') AS freetext_pub, + IF(q.profile_medals_pub = 'public', 'public', 'private') AS medals_pub, + 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) where u.hruid is not null; -- 2.1.4