Commit | Line | Data |
---|---|---|
0e1dfbad SJ |
1 | DROP TABLE IF EXISTS profile_public_names; |
2 | CREATE TABLE IF NOT EXISTS profile_public_names ( | |
3 | pid INT(11) UNSIGNED NOT NULL DEFAULT 0, | |
4 | particles SET('initial', 'main', 'marital', 'ordinary') NOT NULL DEFAULT '', | |
5 | lastname_initial VARCHAR(255) NOT NULL DEFAULT '', | |
6 | lastname_main VARCHAR(255) NOT NULL DEFAULT '', | |
7 | lastname_marital VARCHAR(255) NOT NULL DEFAULT '', | |
8 | lastname_ordinary VARCHAR(255) NOT NULL DEFAULT '', | |
9 | firstname_initial VARCHAR(255) NOT NULL DEFAULT '', | |
10 | firstname_main VARCHAR(255) NOT NULL DEFAULT '', | |
11 | firstname_ordinary VARCHAR(255) NOT NULL DEFAULT '', | |
12 | pseudonym VARCHAR(255) NOT NULL DEFAULT '', | |
13 | PRIMARY KEY (pid), | |
14 | FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE | |
15 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
16 | ||
17 | DROP TABLE IF EXISTS profile_private_names; | |
18 | CREATE TABLE IF NOT EXISTS profile_private_names ( | |
19 | pid INT(11) UNSIGNED NOT NULL DEFAULT 0, | |
20 | type ENUM('lastname', 'firstname', 'nickname') NOT NULL DEFAULT 'nickname', | |
21 | id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'id of the name among those sharing the same pid / general_type', | |
22 | name VARCHAR(255) NOT NULL DEFAULT '', | |
23 | PRIMARY KEY (pid, type, id), | |
24 | FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE | |
25 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
26 | ||
27 | -- Initiates profile_public_names. | |
28 | INSERT INTO profile_public_names (pid) | |
29 | SELECT pid | |
30 | FROM profiles; | |
31 | ||
32 | -- Insert lastnames. | |
33 | UPDATE profile_public_names AS ppn | |
34 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
35 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
36 | SET ppn.lastname_initial = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)), | |
37 | ppn.particles = IF(pn.particle = '', '', 'initial') | |
38 | WHERE pne.type = 'name_ini'; | |
39 | UPDATE profile_public_names AS ppn | |
40 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
41 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
42 | SET ppn.lastname_main = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)), | |
43 | ppn.particles = IF(pn.particle = '', ppn.particles, CONCAT_WS(',', ppn.particles, 'main')) | |
44 | WHERE pne.type = 'lastname'; | |
45 | UPDATE profile_public_names | |
46 | SET particles = TRIM(BOTH ',' FROM particles); | |
47 | UPDATE profile_public_names AS ppn | |
48 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
49 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
50 | SET ppn.lastname_marital = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)), | |
51 | ppn.particles = IF(pn.particle = '', ppn.particles, CONCAT_WS(',', ppn.particles, 'marital')) | |
52 | WHERE pne.type = 'lastname_marital'; | |
53 | UPDATE profile_public_names | |
54 | SET particles = TRIM(BOTH ',' FROM particles); | |
55 | UPDATE profile_public_names AS ppn | |
56 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
57 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
58 | SET ppn.lastname_ordinary = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)), | |
59 | ppn.particles = IF(pn.particle = '', ppn.particles, CONCAT_WS(',', ppn.particles, 'ordinary')) | |
60 | WHERE pne.type = 'lastname_ordinary'; | |
61 | UPDATE profile_public_names | |
62 | SET particles = TRIM(BOTH ',' FROM particles); | |
63 | ||
64 | -- Insert other names. | |
65 | UPDATE profile_public_names AS ppn | |
66 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
67 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
68 | SET ppn.firstname_initial = pn.name | |
69 | WHERE pne.type = 'firstname_ini'; | |
70 | UPDATE profile_public_names AS ppn | |
71 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
72 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
73 | SET ppn.firstname_main = pn.name | |
74 | WHERE pne.type = 'firstname'; | |
75 | UPDATE profile_public_names AS ppn | |
76 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
77 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
78 | SET ppn.firstname_ordinary = pn.name | |
79 | WHERE pne.type = 'firstname_other'; | |
80 | UPDATE profile_public_names AS ppn | |
81 | INNER JOIN profile_name AS pn ON (pn.pid = ppn.pid) | |
82 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
83 | SET ppn.pseudonym = pn.name | |
84 | WHERE pne.type = 'pseudonym'; | |
85 | ||
86 | -- Insert privates names. | |
87 | INSERT INTO profile_private_names (pid, type, id, name) | |
88 | SELECT pn.pid, 'nickname', 0, pn.name | |
89 | FROM profile_name AS pn | |
90 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
91 | WHERE pne.type = 'nickname'; | |
92 | INSERT INTO profile_private_names (pid, type, id, name) | |
93 | SELECT pn.pid, 'lastname', 0, pn.name | |
94 | FROM profile_name AS pn | |
95 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
96 | WHERE pne.type = 'name_other'; | |
97 | INSERT INTO profile_private_names (pid, type, id, name) | |
98 | SELECT pn.pid, 'firstname', 0, pn.name | |
99 | FROM profile_name AS pn | |
100 | INNER JOIN profile_name_enum AS pne ON (pn.typeid = pne.id) | |
101 | WHERE pne.type = 'firstname_other'; | |
102 | ||
5d79569a SJ |
103 | DROP TABLE IF EXISTS profile_name; |
104 | DROP TABLE IF EXISTS profile_name_enum; | |
0e1dfbad SJ |
105 | |
106 | -- vim:set syntax=mysql: |