Commit | Line | Data |
---|---|---|
6e32823c SJ |
1 | ALTER TABLE profile_display ADD COLUMN yourself VARCHAR(255) NOT NULL, |
2 | ADD COLUMN public_name VARCHAR(255) NOT NULL, | |
3 | ADD COLUMN private_name VARCHAR(255) NOT NULL, | |
4 | ADD COLUMN directory_name VARCHAR(255) NOT NULL, | |
5 | ADD COLUMN short_name VARCHAR(255) NOT NULL, | |
6 | ADD COLUMN sort_name VARCHAR(255) NOT NULL, | |
7 | CHANGE COLUMN uid pid INT(11), | |
8 | CHANGE COLUMN promo_display promo VARCHAR(255); | |
9 | ||
10 | REPLACE INTO profile_display (pid, yourself, public_name, private_name, directory_name, short_name, sort_name) | |
11 | SELECT u.user_id, u.prenom, | |
12 | CONCAT(u.prenom, ' ', IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom)), | |
13 | CONCAT(u.prenom, ' ', IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), | |
14 | IF(q.profile_nick != '', CONCAT(' (alias ', q.profile_nick, ')'), '')), | |
15 | CONCAT(IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), ', ', u.prenom), | |
16 | CONCAT(u.prenom, ' ', IF(u.nom_usage != '', u.nom_usage, u.nom)), | |
17 | CONCAT(IF(u.nom_usage != '', u.nom_usage, u.nom), ' ', u.prenom) | |
18 | FROM auth_user_md5 AS u | |
19 | LEFT JOIN auth_user_quick AS q ON (u.user_id = q.user_id); | |
20 | ||
21 | ||
22 | DROP TABLE IF EXISTS profile_name_search_enum; | |
23 | ||
24 | CREATE TABLE IF NOT EXISTS profile_name_search_enum ( | |
25 | id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT, | |
26 | name VARCHAR(255) NOT NULL, | |
27 | explanations VARCHAR(255) NOT NULL, | |
28 | flags SET('has_particle', 'not_displayed', 'always_displayed', 'public') NOT NULL, | |
29 | PRIMARY KEY (id), | |
30 | UNIQUE (name) | |
31 | ) CHARSET=utf8; | |
32 | ||
33 | INSERT INTO profile_name_search_enum (name, flags) | |
34 | VALUES ('Nom patronymique', 'has_particle,always_displayed,public'), | |
35 | ('Nom marital', 'has_particle,always_displayed,public'), | |
36 | ('Nom usuel', 'has_particle,always_displayed,public'), | |
37 | ('Prénom', 'always_displayed,public'), | |
38 | ('Pseudonyme (nom de plume)', 'always_displayed,public'), | |
39 | ('Surnom', ''), | |
40 | ('Prénom usuel', 'public'), | |
41 | ('Autre prénom', ''), | |
42 | ('Autre nom', ''), | |
43 | ('Nom initial', 'has_particle,not_displayed,public'), | |
44 | ('Prénom initial', 'has_particle,not_displayed,public'); | |
45 | ||
46 | ||
47 | DROP TABLE IF EXISTS profile_name_search; | |
48 | ||
49 | CREATE TABLE IF NOT EXISTS profile_name_search ( | |
50 | id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id of this name for this user', | |
51 | pid INT(11) NOT NULL COMMENT 'id of alumni', | |
52 | name VARCHAR(255) NOT NULL COMMENT 'name to search for', | |
53 | particle VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'particle of the name to search for', | |
54 | typeid TINYINT(2) UNSIGNED NOT NULL COMMENT 'type of name', | |
55 | PRIMARY KEY (pid, id) | |
56 | ) CHARSET=utf8 COMMENT = 'Names of alumni (search table)'; | |
57 | ||
58 | INSERT INTO profile_name_search (pid, name, typeid) | |
59 | SELECT u.user_id, u.nom, e.id | |
60 | FROM auth_user_md5 AS u | |
61 | INNER JOIN profile_name_search_enum AS e ON (e.name = 'Nom patronymique') | |
62 | WHERE nom != ''; | |
63 | ||
64 | INSERT INTO profile_name_search (pid, name, typeid) | |
65 | SELECT u.user_id, u.nom_ini, e.id | |
66 | FROM auth_user_md5 AS u | |
67 | INNER JOIN profile_name_search_enum AS e ON (e.name = 'Nom initial') | |
68 | WHERE nom_ini != ''; | |
69 | ||
70 | INSERT INTO profile_name_search (pid, name, typeid) | |
71 | SELECT u.user_id, u.prenom, e.id | |
72 | FROM auth_user_md5 AS u | |
73 | INNER JOIN profile_name_search_enum AS e ON (e.name = 'Prénom') | |
74 | WHERE prenom != ''; | |
75 | ||
76 | INSERT INTO profile_name_search (pid, name, typeid) | |
77 | SELECT u.user_id, u.prenom_ini, e.id | |
78 | FROM auth_user_md5 AS u | |
79 | INNER JOIN profile_name_search_enum AS e ON (e.name = 'Prénom initial') | |
80 | WHERE prenom_ini != ''; | |
81 | ||
82 | INSERT INTO profile_name_search (pid, name, typeid) | |
83 | SELECT u.user_id, u.nom_usage, e.id | |
84 | FROM auth_user_md5 AS u | |
85 | INNER JOIN profile_name_search_enum AS e ON (e.name = 'Nom usuel') | |
86 | WHERE nom_usage != ''; | |
87 | ||
88 | INSERT INTO profile_name_search (pid, name, typeid) | |
89 | SELECT q.user_id, q.profile_nick, e.id | |
90 | FROM auth_user_quick AS q | |
91 | INNER JOIN profile_name_search_enum AS e ON (e.name = 'Surnom') | |
92 | WHERE profile_nick != ''; | |
0fb9c4f7 | 93 | |
c4035ce6 | 94 | -- vim:set syntax=mysql: |