| 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 | |
| 103 | DROP TABLE IF EXISTS profile_name; |
| 104 | DROP TABLE IF EXISTS profile_name_enum; |
| 105 | |
| 106 | -- vim:set syntax=mysql: |