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