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