| 1 | DROP TABLE IF EXISTS profile_education_field_enum; |
| 2 | DROP TABLE IF EXISTS profile_education_degree_enum; |
| 3 | DROP TABLE IF EXISTS profile_education_degree; |
| 4 | DROP TABLE IF EXISTS profile_education_enum; |
| 5 | DROP TABLE IF EXISTS profile_education; |
| 6 | |
| 7 | CREATE TABLE profile_education_field_enum ( |
| 8 | id INT(2) NOT NULL AUTO_INCREMENT, |
| 9 | field VARCHAR(255) DEFAULT NULL, |
| 10 | PRIMARY KEY(id), |
| 11 | UNIQUE KEY(field) |
| 12 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 13 | |
| 14 | CREATE TABLE profile_education_degree_enum ( |
| 15 | id INT(2) NOT NULL AUTO_INCREMENT, |
| 16 | degree VARCHAR(255) DEFAULT NULL, |
| 17 | abbreviation VARCHAR(255) DEFAULT '' NOT NULL, |
| 18 | level TINYINT (1) UNSIGNED DEFAULT 0 NOT NULL, |
| 19 | PRIMARY KEY(id), |
| 20 | UNIQUE KEY(degree) |
| 21 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 22 | |
| 23 | CREATE TABLE profile_education_degree ( |
| 24 | eduid INT(4) NOT NULL DEFAULT 0, |
| 25 | degreeid INT(2) NOT NULL DEFAULT 0, |
| 26 | PRIMARY KEY(eduid, degreeid) |
| 27 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 28 | |
| 29 | CREATE TABLE profile_education_enum ( |
| 30 | id INT(4) NOT NULL AUTO_INCREMENT, |
| 31 | name VARCHAR(255) DEFAULT NULL, |
| 32 | abbreviation VARCHAR(255) DEFAULT '' NOT NULL, |
| 33 | url VARCHAR(255) DEFAULT NULL, |
| 34 | country CHAR(2) NOT NULL DEFAULT 'FR', |
| 35 | PRIMARY KEY(id), |
| 36 | UNIQUE KEY(name) |
| 37 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 38 | |
| 39 | CREATE TABLE profile_education ( |
| 40 | id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, |
| 41 | pid INT(11) NOT NULL DEFAULT 0, |
| 42 | eduid INT(4) NOT NULL DEFAULT 0, |
| 43 | degreeid INT(4) NOT NULL DEFAULT 0, |
| 44 | fieldid INT(2) NOT NULL DEFAULT 0, |
| 45 | entry_year INT(4) DEFAULT NULL, |
| 46 | grad_year INT(4) DEFAULT NULL, |
| 47 | program VARCHAR(255) DEFAULT NULL, |
| 48 | flags SET('primary') DEFAULT '' NOT NULL, |
| 49 | PRIMARY KEY(id, pid), |
| 50 | INDEX pid (pid) |
| 51 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 52 | |
| 53 | INSERT INTO profile_education_field_enum (field) |
| 54 | VALUES ('Aéronautique'), ('Agronomie'), ('Assurance'), ('Biologie'), |
| 55 | ('Chimie'), ('Droit'), ('Économie'), ('Électronique/électricité'), |
| 56 | ('Environnement/développement durable'), ('Finance'), ('Géographie'), |
| 57 | ('Histoire'), ('Informatique'), ('Langues'), ('Mathématiques'), |
| 58 | ('Mathématiques appliquées'), ('Mécanique'), ('Médecine'), |
| 59 | ('Philosophie'), ('Physique'), ('Sciences politiques'); |
| 60 | |
| 61 | INSERT INTO profile_education_degree_enum (degree) |
| 62 | VALUES ('Diplôme'), ('Ingénieur'), ('Corps'), ('MS'), ('PhD'), |
| 63 | ('DEA'), ('ME'), ('MBA'), ('MiF'), ('MPA'), ('Licence'); |
| 64 | |
| 65 | INSERT INTO profile_education_degree (eduid, degreeid) |
| 66 | SELECT a.id, d.id |
| 67 | FROM #x4dat#.applis_def AS a |
| 68 | INNER JOIN profile_education_degree_enum AS d ON (FIND_IN_SET(d.degree, a.type)); |
| 69 | |
| 70 | INSERT INTO profile_education_enum (id, name, url) |
| 71 | SELECT id, text, url |
| 72 | FROM #x4dat#.applis_def; |
| 73 | |
| 74 | INSERT INTO profile_education (id, pid, eduid, degreeid) |
| 75 | SELECT a.ordre, a.uid, a.aid, d.id |
| 76 | FROM #x4dat#.applis_ins AS a |
| 77 | INNER JOIN profile_education_degree_enum AS d ON (a.type = d.degree); |
| 78 | |
| 79 | UPDATE watch_profile AS w1 |
| 80 | INNER JOIN watch_profile AS w2 ON (w1.uid = w2.uid AND w1.field = 'appli1' AND w2.field = 'appli2') |
| 81 | SET w1.ts = IF(w1.ts > w2.ts, w1.ts, w2.ts), w2.ts = IF(w1.ts > w2.ts, w1.ts, w2.ts); |
| 82 | |
| 83 | INSERT IGNORE INTO watch_profile (uid, ts, field) |
| 84 | SELECT uid, ts, 'appli1' |
| 85 | FROM watch_profile |
| 86 | WHERE field = 'appli2'; |
| 87 | |
| 88 | ALTER TABLE watch_profile |
| 89 | MODIFY field ENUM('nom', 'freetext', 'mobile', 'nationality1', 'nationality2', 'nationality3', |
| 90 | 'nick', 'networking', 'appli1', 'appli2', 'edus', 'addresses', 'section', |
| 91 | 'binets', 'medals', 'cv', 'jobs', 'photo'); |
| 92 | |
| 93 | UPDATE watch_profile SET field = 'edus' WHERE field = 'appli1'; |
| 94 | |
| 95 | |
| 96 | DELETE FROM watch_profile WHERE field = 'appli2'; |
| 97 | |
| 98 | ALTER TABLE watch_profile |
| 99 | MODIFY field ENUM('nom', 'freetext', 'mobile', 'nationality1', 'nationality2', 'nationality3', |
| 100 | 'nick', 'networking', 'edus', 'addresses', 'section', 'binets', 'medals', |
| 101 | 'cv', 'jobs', 'photo'); |
| 102 | |
| 103 | # vim:set syntax=mysql: |
| 104 | |