| 1 | DROP TABLE IF EXISTS profile_display; |
| 2 | |
| 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, |
| 12 | PRIMARY KEY(pid) |
| 13 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 14 | |
| 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); |
| 25 | |
| 26 | |
| 27 | DROP TABLE IF EXISTS profile_name_enum; |
| 28 | |
| 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, |
| 36 | PRIMARY KEY (id), |
| 37 | UNIQUE (name) |
| 38 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 39 | |
| 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', |
| 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); |
| 60 | |
| 61 | |
| 62 | DROP TABLE IF EXISTS profile_name; |
| 63 | |
| 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), |
| 71 | KEY (id), |
| 72 | INDEX pid (pid) |
| 73 | ) ENGINE=InnoDB, CHARSET=utf8, COMMENT = 'Names of alumni (search table)'; |
| 74 | |
| 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') |
| 79 | WHERE nom != ''; |
| 80 | |
| 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') |
| 85 | WHERE nom_ini != ''; |
| 86 | |
| 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') |
| 91 | WHERE nom_ini = ''; |
| 92 | |
| 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') |
| 97 | WHERE prenom != ''; |
| 98 | |
| 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 != ''; |
| 104 | |
| 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 = ''; |
| 110 | |
| 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 != ''; |
| 116 | |
| 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 != ''; |
| 122 | |
| 123 | DELETE FROM search_autocomplete |
| 124 | WHERE name = 'name' OR name = 'firstname' OR name = 'nickname'; |
| 125 | -- vim:set syntax=mysql: |