Commit | Line | Data |
---|---|---|
75036b0f SJ |
1 | DROP TABLE IF EXISTS profile_display; |
2 | ||
a51ed9a2 SJ |
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, | |
75036b0f | 12 | PRIMARY KEY(pid) |
a51ed9a2 | 13 | ) CHARSET=utf8; |
6e32823c | 14 | |
a51ed9a2 SJ |
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, ')'), '')), | |
75036b0f | 20 | CONCAT(IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), ' ', u.prenom), |
a51ed9a2 SJ |
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 auth_user_md5 AS u | |
24 | LEFT JOIN auth_user_quick AS q ON (u.user_id = q.user_id); | |
6e32823c SJ |
25 | |
26 | ||
97a98687 | 27 | DROP TABLE IF EXISTS profile_name_enum; |
6e32823c | 28 | |
97a98687 | 29 | CREATE TABLE IF NOT EXISTS profile_name_enum ( |
6e32823c SJ |
30 | id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT, |
31 | name VARCHAR(255) NOT NULL, | |
32 | explanations VARCHAR(255) NOT NULL, | |
6cb58d39 | 33 | type VARCHAR(255) NOT NULL, |
6e32823c | 34 | flags SET('has_particle', 'not_displayed', 'always_displayed', 'public') NOT NULL, |
6443c93d | 35 | score TINYINT(2) UNSIGNED NOT NULL DEFAULT 10, |
6e32823c SJ |
36 | PRIMARY KEY (id), |
37 | UNIQUE (name) | |
38 | ) CHARSET=utf8; | |
39 | ||
6443c93d | 40 | INSERT INTO profile_name_enum (name, flags, explanations, type, score) |
6cb58d39 | 41 | VALUES ('Nom patronymique', 'has_particle,always_displayed,public', |
6443c93d | 42 | 'Le nom de famille avec lequel tu es né', 'lastname', 10), |
70c65f3a | 43 | ('Nom marital', 'has_particle,always_displayed,public', |
6443c93d | 44 | 'Ton nom d\'épouse ou d\'époux', 'lastname_marital', 10), |
70c65f3a | 45 | ('Nom usuel', 'has_particle,always_displayed,public', |
6cb58d39 | 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...', |
6443c93d SJ |
47 | 'lastname_ordinary', 10), |
48 | ('Prénom', 'always_displayed,public', 'Ton prénom', 'firstname', 10), | |
70c65f3a | 49 | ('Pseudonyme (nom de plume)', 'always_displayed,public', |
6443c93d SJ |
50 | 'Pseudonyme pour les artistes, gens de lettres', 'pseudonym', 10), |
51 | ('Surnom', '', 'Surnom à l\'École ou ailleurs', 'nickname', 2), | |
6cb58d39 | 52 | ('Prénom usuel', 'public', 'Si tu utilises une version raccourcie, francisée... de ton prénom', |
6443c93d | 53 | 'firstname_ordinary', 10), |
6cb58d39 | 54 | ('Autre prénom', '', 'Si tu as d\'autres prénoms et que tu souhaites les faire apparaître', |
6443c93d | 55 | 'firstname_other', 1), |
6cb58d39 | 56 | ('Autre nom', '', 'Si tu as d\'autres noms et que tu souhaites les faire apparaître', |
6443c93d SJ |
57 | 'name_other', 1), |
58 | ('Nom initial', 'has_particle,not_displayed,public', '', 'name_ini', 10), | |
59 | ('Prénom initial', 'has_particle,not_displayed,public', '', 'firstname_ini', 10); | |
6e32823c SJ |
60 | |
61 | ||
97a98687 | 62 | DROP TABLE IF EXISTS profile_name; |
6e32823c | 63 | |
97a98687 | 64 | CREATE TABLE IF NOT EXISTS profile_name ( |
6e32823c SJ |
65 | id TINYINT(2) 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', | |
bc07bc7a FB |
70 | PRIMARY KEY (pid, id), |
71 | INDEX pid (pid) | |
6e32823c SJ |
72 | ) CHARSET=utf8 COMMENT = 'Names of alumni (search table)'; |
73 | ||
97a98687 | 74 | INSERT INTO profile_name (pid, name, typeid) |
6e32823c | 75 | SELECT u.user_id, u.nom, e.id |
97a98687 SJ |
76 | FROM auth_user_md5 AS u |
77 | INNER JOIN profile_name_enum AS e ON (e.name = 'Nom patronymique') | |
6e32823c SJ |
78 | WHERE nom != ''; |
79 | ||
97a98687 | 80 | INSERT INTO profile_name (pid, name, typeid) |
6e32823c | 81 | SELECT u.user_id, u.nom_ini, e.id |
97a98687 SJ |
82 | FROM auth_user_md5 AS u |
83 | INNER JOIN profile_name_enum AS e ON (e.name = 'Nom initial') | |
6e32823c SJ |
84 | WHERE nom_ini != ''; |
85 | ||
97a98687 | 86 | INSERT INTO profile_name (pid, name, typeid) |
6e32823c | 87 | SELECT u.user_id, u.prenom, e.id |
97a98687 SJ |
88 | FROM auth_user_md5 AS u |
89 | INNER JOIN profile_name_enum AS e ON (e.name = 'Prénom') | |
6e32823c SJ |
90 | WHERE prenom != ''; |
91 | ||
97a98687 | 92 | INSERT INTO profile_name (pid, name, typeid) |
6e32823c | 93 | SELECT u.user_id, u.prenom_ini, e.id |
97a98687 SJ |
94 | FROM auth_user_md5 AS u |
95 | INNER JOIN profile_name_enum AS e ON (e.name = 'Prénom initial') | |
6e32823c SJ |
96 | WHERE prenom_ini != ''; |
97 | ||
97a98687 | 98 | INSERT INTO profile_name (pid, name, typeid) |
6e32823c | 99 | SELECT u.user_id, u.nom_usage, e.id |
97a98687 SJ |
100 | FROM auth_user_md5 AS u |
101 | INNER JOIN profile_name_enum AS e ON (e.name = 'Nom usuel') | |
6e32823c SJ |
102 | WHERE nom_usage != ''; |
103 | ||
97a98687 | 104 | INSERT INTO profile_name (pid, name, typeid) |
6e32823c | 105 | SELECT q.user_id, q.profile_nick, e.id |
97a98687 SJ |
106 | FROM auth_user_quick AS q |
107 | INNER JOIN profile_name_enum AS e ON (e.name = 'Surnom') | |
6e32823c | 108 | WHERE profile_nick != ''; |
0fb9c4f7 | 109 | |
6443c93d SJ |
110 | DROP TABLE IF EXISTS recherche_soundex; |
111 | ||
112 | DELETE FROM search_autocomplete | |
113 | WHERE name = 'name' OR name = 'firstname' OR name = 'nickname'; | |
c4035ce6 | 114 | -- vim:set syntax=mysql: |