9c04c51b4926d3271eb28cb67efdccddd2354c05
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 '',
14 FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE
15 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
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
;
27 -- Initiates profile_public_names.
28 INSERT INTO profile_public_names (pid
)
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
);
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';
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';
103 -- DROP TABLE IF EXISTS profile_name;
104 -- DROP TABLE IF EXISTS profile_name_enum;
106 -- vim:set syntax=mysql: