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, | |
11 | PRIMARY KEY(id), | |
12 | UNIQUE KEY(degree) | |
13 | ) CHARSET=utf8; | |
14 | ||
15 | CREATE TABLE IF NOT EXISTS profile_education_degree ( | |
16 | eduid INT(4) NOT NULL DEFAULT 0, | |
17 | degreeid INT(2) NOT NULL DEFAULT 0, | |
18 | PRIMARY KEY(eduid, degreeid) | |
19 | ) CHARSET=utf8; | |
20 | ||
21 | CREATE TABLE IF NOT EXISTS profile_education_enum ( | |
22 | id INT(4) NOT NULL AUTO_INCREMENT, | |
23 | name VARCHAR(255) DEFAULT NULL, | |
24 | url VARCHAR(255) DEFAULT NULL, | |
25 | country CHAR(2) NOT NULL DEFAULT 'FR', | |
26 | PRIMARY KEY(id), | |
27 | UNIQUE KEY(name) | |
28 | ) CHARSET=utf8; | |
29 | ||
30 | CREATE TABLE IF NOT EXISTS profile_education ( | |
31 | id INT(2) NOT NULL DEFAULT 0, | |
32 | uid INT(11) NOT NULL DEFAULT 0, | |
33 | eduid INT(4) NOT NULL DEFAULT 0, | |
34 | degreeid INT(4) NOT NULL DEFAULT 0, | |
35 | fieldid INT(2) NOT NULL DEFAULT 0, | |
36 | grad_year INT(4) NOT NULL DEFAULT 0, | |
1504ac45 | 37 | program VARCHAR(255) DEFAULT NULL, |
043bbacf SJ |
38 | PRIMARY KEY(id, uid) |
39 | ) CHARSET=utf8; | |
40 | ||
41 | INSERT INTO profile_education_field_enum (field) | |
42 | VALUES ('Aéronautique'), ('Agronomie'), ('Assurance'), ('Biologie'), | |
43 | ('Chimie'), ('Droit'), ('Économie'), ('Électronique/électricité'), | |
44 | ('Environnement/développement durable'), ('Finance'), ('Géographie'), | |
45 | ('Histoire'), ('Informatique'), ('Langues'), ('Mathématiques'), | |
46 | ('Mathématiques appliquées'), ('Mécanique'), ('Médecine'), | |
47 | ('Philosophie'), ('Physique'), ('Sciences politiques'); | |
48 | ||
49 | INSERT INTO profile_education_degree_enum (degree) | |
50 | VALUES ('Diplôme'), ('Ingénieur'), ('Corps'), ('MS'), ('PhD'), | |
51 | ('DEA'), ('ME'), ('MBA'), ('MiF'), ('MPA'), ('Licence'); | |
52 | ||
53 | INSERT INTO profile_education_degree (eduid, degreeid) | |
54 | SELECT a.id, d.id | |
55 | FROM applis_def AS a | |
56 | INNER JOIN profile_education_degree_enum AS d ON (FIND_IN_SET(d.degree, a.type)); | |
57 | ||
58 | INSERT INTO profile_education_enum (id, name, url) | |
59 | SELECT id, text, url | |
60 | FROM applis_def; | |
61 | ||
62 | INSERT INTO profile_education (id, uid, eduid, degreeid) | |
63 | SELECT a.ordre, a.uid, a.aid, d.id | |
64 | FROM applis_ins AS a | |
65 | INNER JOIN profile_education_degree_enum AS d ON (a.type = d.degree); | |
66 | ||
67 | ||
68 | # vim:set syntax=mysql: | |
69 |