Commit | Line | Data |
---|---|---|
d0293d9b FB |
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 ( | |
043bbacf SJ |
8 | id INT(2) NOT NULL AUTO_INCREMENT, |
9 | field VARCHAR(255) DEFAULT NULL, | |
10 | PRIMARY KEY(id), | |
11 | UNIQUE KEY(field) | |
950bf4f6 | 12 | ) ENGINE=InnoDB, CHARSET=utf8; |
043bbacf | 13 | |
d0293d9b | 14 | CREATE TABLE profile_education_degree_enum ( |
043bbacf SJ |
15 | id INT(2) NOT NULL AUTO_INCREMENT, |
16 | degree VARCHAR(255) DEFAULT NULL, | |
c7eac294 SJ |
17 | abbreviation VARCHAR(255) DEFAULT '' NOT NULL, |
18 | level TINYINT (1) UNSIGNED DEFAULT 0 NOT NULL, | |
043bbacf | 19 | PRIMARY KEY(id), |
483cbf2c | 20 | UNIQUE KEY(degree) |
950bf4f6 | 21 | ) ENGINE=InnoDB, CHARSET=utf8; |
043bbacf | 22 | |
d0293d9b | 23 | CREATE TABLE profile_education_degree ( |
043bbacf SJ |
24 | eduid INT(4) NOT NULL DEFAULT 0, |
25 | degreeid INT(2) NOT NULL DEFAULT 0, | |
26 | PRIMARY KEY(eduid, degreeid) | |
950bf4f6 | 27 | ) ENGINE=InnoDB, CHARSET=utf8; |
043bbacf | 28 | |
d0293d9b | 29 | CREATE TABLE profile_education_enum ( |
043bbacf SJ |
30 | id INT(4) NOT NULL AUTO_INCREMENT, |
31 | name VARCHAR(255) DEFAULT NULL, | |
c7eac294 | 32 | abbreviation VARCHAR(255) DEFAULT '' NOT NULL, |
043bbacf SJ |
33 | url VARCHAR(255) DEFAULT NULL, |
34 | country CHAR(2) NOT NULL DEFAULT 'FR', | |
35 | PRIMARY KEY(id), | |
483cbf2c | 36 | UNIQUE KEY(name) |
950bf4f6 | 37 | ) ENGINE=InnoDB, CHARSET=utf8; |
043bbacf | 38 | |
d0293d9b | 39 | CREATE TABLE profile_education ( |
fb2c09c9 | 40 | id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, |
ce0b2c6f | 41 | pid INT(11) NOT NULL DEFAULT 0, |
043bbacf SJ |
42 | eduid INT(4) NOT NULL DEFAULT 0, |
43 | degreeid INT(4) NOT NULL DEFAULT 0, | |
44 | fieldid INT(2) NOT NULL DEFAULT 0, | |
c7eac294 | 45 | entry_year INT(4) DEFAULT NULL, |
58acfe8b | 46 | grad_year INT(4) DEFAULT NULL, |
1504ac45 | 47 | program VARCHAR(255) DEFAULT NULL, |
c7eac294 | 48 | flags SET('primary') DEFAULT '' NOT NULL, |
ce0b2c6f FB |
49 | PRIMARY KEY(id, pid), |
50 | INDEX pid (pid) | |
950bf4f6 | 51 | ) ENGINE=InnoDB, CHARSET=utf8; |
043bbacf SJ |
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 | |
100e66fc | 67 | FROM #x4dat#.applis_def AS a |
043bbacf SJ |
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 | |
100e66fc | 72 | FROM #x4dat#.applis_def; |
043bbacf | 73 | |
ce0b2c6f | 74 | INSERT INTO profile_education (id, pid, eduid, degreeid) |
043bbacf | 75 | SELECT a.ordre, a.uid, a.aid, d.id |
100e66fc | 76 | FROM #x4dat#.applis_ins AS a |
043bbacf SJ |
77 | INNER JOIN profile_education_degree_enum AS d ON (a.type = d.degree); |
78 | ||
3a703848 SJ |
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 | ||
86c104d1 SJ |
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'); | |
3a703848 SJ |
92 | |
93 | UPDATE watch_profile SET field = 'edus' WHERE field = 'appli1'; | |
94 | ||
100e66fc | 95 | |
3a703848 SJ |
96 | DELETE FROM watch_profile WHERE field = 'appli2'; |
97 | ||
86c104d1 SJ |
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'); | |
043bbacf SJ |
102 | |
103 | # vim:set syntax=mysql: | |
104 |