1 CREATE TABLE IF NOT EXISTS `profile_names_display`
(
2 `user_id`
int(11) NOT NULL COMMENT 'id of alumni',
3 `display`
varchar(100) NOT NULL COMMENT 'name to display',
4 `sort`
varchar(100) NOT NULL COMMENT 'name used for sorting',
5 `tooltip`
varchar(150) NOT NULL COMMENT 'text to explain display name if needed',
6 `firstname`
varchar(50) NOT NULL COMMENT 'first name',
7 `lastname`
varchar(50) NOT NULL COMMENT 'last name',
8 `yourself`
varchar(100) NOT NULL COMMENT 'name we used to speak to him/her',
9 PRIMARY KEY (`user_id`
),
11 ) CHARSET
=utf8
COMMENT='Alumnis'' names to display';
13 INSERT INTO `profile_names_display`
(
16 CONCAT(`prenom`
, ' ',IF(`nom_usage`
!= '',CONCAT(`nom_usage`
,' (',`nom`
,')'),`nom`
)),
17 CONCAT(IF(`nom_usage`
!= '',`nom_usage`
,`nom`
),', ',`prenom`
),
20 IF(`nom_usage`
!= '',`nom_usage`
,`nom`
),
22 FROM `auth_user_md5`
);
24 UPDATE `profile_names_display`
AS n
INNER JOIN `auth_user_md5`
AS u
ON n.lastname
= u.prenom
25 SET n.tooltip
= CONCAT('Prénom : ', n.firstname
,' - Nom : ', n.lastname
);
27 CREATE TABLE IF NOT EXISTS `profile_names_search`
(
28 `user_id`
int(11) NOT NULL COMMENT 'id of alumni',
29 `sn_id`
smallint(6) NOT NULL COMMENT 'id of this search name in all alumni''s search names',
30 `search_name`
varchar(50) NOT NULL COMMENT 'name to search for',
31 `name_type`
enum('firstname','lastname','surname') NOT NULL default 'lastname' COMMENT 'type of name',
32 `search_score`
smallint(6) NOT NULL COMMENT 'used to sort search results',
33 `pub`
enum('always public','public','private') NOT NULL default 'private' COMMENT 'searchable on public site or only on private',
34 PRIMARY KEY (`name_type`
,`search_name`
,`user_id`
),
35 KEY `user_id`
(`user_id`
)
36 ) CHARSET
=utf8
COMMENT='Names of alumni (search table)';
38 INSERT INTO `profile_names_search`
( SELECT `user_id`
, 0, `nom`
, 'lastname', 10, 'always public' FROM `auth_user_md5`
WHERE `nom`
!= '');
39 INSERT INTO `profile_names_search`
( SELECT `user_id`
, 1, `nom_usage`
, 'lastname', 10, 'public' FROM `auth_user_md5`
WHERE `nom`
!= `nom_usage`
AND `nom_usage`
!= '');
40 INSERT INTO `profile_names_search`
( SELECT `user_id`
, 2, `nom_ini`
, 'lastname', 7, 'private' FROM `auth_user_md5`
WHERE `nom`
!= `nom_ini`
AND `nom_ini`
!= '' AND `nom_ini`
!= `nom_usage`
);
41 INSERT INTO `profile_names_search`
( SELECT `user_id`
, 3, `prenom`
, 'firstname', 8, 'always public' FROM `auth_user_md5`
WHERE `prenom`
!= '');
42 INSERT INTO `profile_names_search`
( SELECT `user_id`
, 4, `prenom_ini`
, 'firstname', 5, 'private' FROM `auth_user_md5`
WHERE `prenom_ini`
!= `prenom`
AND `prenom_ini`
!= '');
43 INSERT INTO `profile_names_search`
( SELECT `user_id`
, 5, `profile_nick`
, 'surname', 7, 'private' FROM `auth_user_quick`
WHERE `profile_nick`
!= '');
45 -- vim:set syntax=mysql: