1 DROP TABLE IF EXISTS profile_display
;
3 CREATE TABLE profile_display (
4 pid
INT(11) NOT NULL DEFAULT 0,
5 yourself
VARCHAR(255) NOT NULL,
6 public_name
VARCHAR(255) NOT NULL,
7 private_name
VARCHAR(255) NOT NULL,
8 directory_name
VARCHAR(255) NOT NULL,
9 short_name
VARCHAR(255) NOT NULL,
10 sort_name
VARCHAR(255) NOT NULL,
11 promo
VARCHAR(255) DEFAULT '' NOT NULL,
13 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
15 INSERT INTO profile_display (pid
, yourself
, public_name
, private_name
, directory_name
, short_name
, sort_name
)
16 SELECT u.user_id
, u.prenom
,
17 CONCAT(u.prenom
, ' ', IF(u.nom_usage
!= '', CONCAT(u.nom_usage
, ' (', u.nom
, ')') , u.nom
)),
18 CONCAT(u.prenom
, ' ', IF(u.nom_usage
!= '', CONCAT(u.nom_usage
, ' (', u.nom
, ')') , u.nom
),
19 IF(q.profile_nick
!= '', CONCAT(' (alias ', q.profile_nick
, ')'), '')),
20 CONCAT(IF(u.nom_usage
!= '', CONCAT(u.nom_usage
, ' (', u.nom
, ')') , u.nom
), ' ', u.prenom
),
21 CONCAT(u.prenom
, ' ', IF(u.nom_usage
!= '', u.nom_usage
, u.nom
)),
22 CONCAT(IF(u.nom_usage
!= '', u.nom_usage
, u.nom
), ' ', u.prenom
)
23 FROM #x4dat#.auth_user_md5
AS u
24 LEFT JOIN #x4dat#.auth_user_quick
AS q
ON (u.user_id
= q.user_id
);
27 DROP TABLE IF EXISTS profile_name_enum
;
29 CREATE TABLE IF NOT EXISTS profile_name_enum (
30 id TINYINT(2) UNSIGNED
NOT NULL AUTO_INCREMENT
,
31 name VARCHAR(255) NOT NULL,
32 explanations
VARCHAR(255) NOT NULL,
33 type VARCHAR(255) NOT NULL,
34 flags
SET('has_particle', 'not_displayed', 'always_displayed', 'public', 'allow_duplicates') NOT NULL,
35 score
TINYINT(2) UNSIGNED
NOT NULL DEFAULT 10,
38 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
40 INSERT INTO profile_name_enum (name, flags
, explanations
, type, score
)
41 VALUES ('Nom patronymique', 'has_particle,always_displayed,public',
42 'Le nom de famille avec lequel tu es né', 'lastname', 10),
43 ('Nom marital', 'has_particle,always_displayed,public',
44 'Ton nom d\'épouse ou d\'époux', 'lastname_marital', 10),
45 ('Nom usuel', 'has_particle,always_displayed,public',
46 'Le nom de famille que tu utilises usuellement s\'il est différent du nom patronymique, ce peut-être une version racourcie de celui-ci, ton nom marital, une combinaison de ces deux noms...',
47 'lastname_ordinary', 10),
48 ('Prénom', 'always_displayed,public', 'Ton prénom', 'firstname', 10),
49 ('Pseudonyme (nom de plume)', 'always_displayed,public',
50 'Pseudonyme pour les artistes, gens de lettres', 'pseudonym', 10),
51 ('Surnom', 'allow_duplicates', 'Surnom à l\'École ou ailleurs', 'nickname', 2),
52 ('Prénom usuel', 'public', 'Si tu utilises une version raccourcie, francisée... de ton prénom',
53 'firstname_ordinary', 10),
54 ('Autre prénom', 'allow_duplicates', 'Si tu as d\'autres prénoms et que tu souhaites les faire apparaître',
55 'firstname_other', 1),
56 ('Autre nom', 'allow_duplicates', 'Si tu as d\'autres noms et que tu souhaites les faire apparaître',
58 ('Nom initial', 'has_particle,not_displayed,public', '', 'name_ini', 10),
59 ('Prénom initial', 'has_particle,not_displayed,public', '', 'firstname_ini', 10);
62 DROP TABLE IF EXISTS profile_name
;
64 CREATE TABLE IF NOT EXISTS profile_name (
65 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
COMMENT 'id of this name for this user',
66 pid
INT(11) NOT NULL COMMENT 'id of alumni',
67 name VARCHAR(255) NOT NULL COMMENT 'name to search for',
68 particle
VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'particle of the name to search for',
69 typeid
TINYINT(2) UNSIGNED
NOT NULL COMMENT 'type of name',
70 PRIMARY KEY (pid
, id),
73 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT = 'Names of alumni (search table)';
75 INSERT INTO profile_name (pid
, name, typeid
)
76 SELECT u.user_id
, u.nom
, e.
id
77 FROM #x4dat#.auth_user_md5
AS u
78 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Nom patronymique')
81 INSERT INTO profile_name (pid
, name, typeid
)
82 SELECT u.user_id
, u.nom_ini
, e.
id
83 FROM #x4dat#.auth_user_md5
AS u
84 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Nom initial')
87 INSERT INTO profile_name (pid
, name, typeid
)
88 SELECT u.user_id
, u.nom
, e.
id
89 FROM #x4dat#.auth_user_md5
AS u
90 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Nom initial')
93 INSERT INTO profile_name (pid
, name, typeid
)
94 SELECT u.user_id
, u.prenom
, e.
id
95 FROM #x4dat#.auth_user_md5
AS u
96 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Prénom')
99 INSERT INTO profile_name (pid
, name, typeid
)
100 SELECT u.user_id
, u.prenom_ini
, e.
id
101 FROM #x4dat#.auth_user_md5
AS u
102 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Prénom initial')
103 WHERE prenom_ini
!= '';
105 INSERT INTO profile_name (pid
, name, typeid
)
106 SELECT u.user_id
, u.prenom
, e.
id
107 FROM #x4dat#.auth_user_md5
AS u
108 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Prénom initial')
109 WHERE prenom_ini
= '';
111 INSERT INTO profile_name (pid
, name, typeid
)
112 SELECT u.user_id
, u.nom_usage
, e.
id
113 FROM #x4dat#.auth_user_md5
AS u
114 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Nom usuel')
115 WHERE nom_usage
!= '';
117 INSERT INTO profile_name (pid
, name, typeid
)
118 SELECT q.user_id
, q.profile_nick
, e.
id
119 FROM #x4dat#.auth_user_quick
AS q
120 INNER JOIN profile_name_enum
AS e
ON (e.
name = 'Surnom')
121 WHERE profile_nick
!= '';
123 DELETE FROM search_autocomplete
124 WHERE name = 'name' OR name = 'firstname' OR name = 'nickname';
125 -- vim:set syntax=mysql: