X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F09_education.sql;h=058ad93764c8ab96592b5a083b223afa647f6844;hb=843a2191e4c76f0beb00c754f06974de76791238;hp=74de7434bdd85cb0f5d3933d923c511aa27346d7;hpb=cf81c359b80ad4280a0b3719e0a32c8adba2549d;p=platal.git

diff --git a/upgrade/newdirectory-0.0.1/09_education.sql b/upgrade/newdirectory-0.0.1/09_education.sql
index 74de743..058ad93 100644
--- a/upgrade/newdirectory-0.0.1/09_education.sql
+++ b/upgrade/newdirectory-0.0.1/09_education.sql
@@ -26,6 +26,9 @@ INSERT INTO  profile_education_degree_enum (abbreviation, degree, level)
 
 -- Updates universities
 UPDATE profile_education_enum
+SET abbreviation = 'Université Claude Bernard', name = 'Université Claude Bernard (Lyon 1)'
+WHERE name = 'Université Claude Bernard';
+UPDATE profile_education_enum
 SET abbreviation = 'Télécom', name = 'Télécom ParisTech', url = 'http://www.telecom-paristech.fr/'
 WHERE name = 'Télécom';
 UPDATE profile_education_enum
@@ -149,6 +152,9 @@ UPDATE profile_education_enum
 SET abbreviation = 'Sciences Po', name = 'Institut d\'Études Politiques de Paris'
 WHERE name = 'IEP Paris';
 UPDATE profile_education_enum
+SET abbreviation = 'Sciences Po Aix', name = 'Institut d\'Études Politiques d\'Aix-en-Provence'
+WHERE name = 'IEP Aix';
+UPDATE profile_education_enum
 SET abbreviation = 'EHESS', name = 'École des Hautes Études en Sciences Sociales'
 WHERE name = 'EHESS';
 UPDATE profile_education_enum
@@ -158,6 +164,9 @@ UPDATE profile_education_enum
 SET abbreviation = 'Grenoble INP', name = 'Institut Polytechnique de Grenoble', url = 'http://www.grenoble-inp.fr/'
 WHERE name = 'INPG';
 UPDATE profile_education_enum
+SET abbreviation = 'UJF'
+WHERE name = 'Université Joseph-Fourier (Grenoble 1)';
+UPDATE profile_education_enum
 SET abbreviation = 'ESSEC', name = 'École Supérieure des Sciences Économiques et Commerciales'
 WHERE name = 'ESSEC';
 UPDATE profile_education_enum
@@ -205,8 +214,23 @@ WHERE name = 'INSA Rouen';
 UPDATE profile_education_enum
 SET abbreviation = 'IAE de Paris', name = 'Institut d\'Administration des Entreprises de Paris', url = 'http://iae.univ-paris1.fr/'
 WHERE name = 'Institut d\'Administration des Entreprises';
+UPDATE profile_education_enum
+SET abbreviation = 'CNSMDP', name = 'Conservatoire National Supérieur de Musique et de Danse de Paris'
+WHERE name = 'Conservatoire national supérieur de musique et de danse de Paris';
+UPDATE profile_education_enum
+SET name = 'École Normale de Musique de Paris'
+WHERE name = 'École normale de musique de Paris';
+UPDATE profile_education_enum
+SET abbreviation = 'ENSMN'
+WHERE name = 'École Nationale Supérieure des Mines de Nancy';
 
 UPDATE profile_education_enum
+SET abbreviation = 'NDU', country = 'US'
+WHERE name = 'National Defense University';
+UPDATE profile_education_enum
+SET abbreviation = 'Brown', country = 'US'
+WHERE name = 'Brown University';
+UPDATE profile_education_enum
 SET abbreviation = 'Berkeley', name = 'University of California, Berkeley', country = 'US'
 WHERE name = 'Univ Berkeley';
 UPDATE profile_education_enum
@@ -252,6 +276,9 @@ UPDATE profile_education_enum
 SET abbreviation = 'The Wharton School', name = 'The Wharton School of the University of Pennsylvania', country = 'US', url = 'http://www.wharton.upenn.edu/'
 WHERE name = 'Univ Wharton';
 UPDATE profile_education_enum
+SET country = 'US'
+WHERE name = 'University of Pennsylvania';
+UPDATE profile_education_enum
 SET abbreviation = 'Columbia University', name = 'Columbia University in the City of New York', country = 'US'
 WHERE name = 'Univ Columbia';
 UPDATE profile_education_enum
@@ -305,6 +332,20 @@ WHERE name = 'Univ Carnegie Mellon';
 UPDATE profile_education_enum
 SET abbreviation = 'Carolina', name = 'University of North Carolina at Chapel Hill', country = 'US'
 WHERE name = 'Univ of North Carolina at Chapel Hill';
+UPDATE profile_education_enum
+SET abbreviation = 'UM', country = 'US'
+WHERE name = 'University of Miami';
+UPDATE profile_education_enum
+SET country = 'US'
+WHERE name = 'Purdue University';
+UPDATE profile_education_enum
+SET abbreviation = 'CFA Institute', name = 'Chartered Financial Analyst Institute', country = 'US'
+WHERE name = 'CFA Institute';
+
+
+UPDATE profile_education_enum
+SET country = 'HK', abbreviation = 'CityU'
+WHERE name = 'City University of Hong Kong';
 
 UPDATE profile_education_enum
 SET abbreviation = 'TU Berlin', name = 'Technische Universität Berlin', country = 'DE'
@@ -346,6 +387,9 @@ WHERE name = 'Univ Madrid';
 UPDATE profile_education_enum
 SET abbreviation = 'UPF', name = 'Universitat Pompeu Fabra', country = 'ES'
 WHERE name = 'Univ Pompeu Fabra';
+UPDATE profile_education_enum
+SET abbreviation = 'IE', country = 'ES'
+WHERE name = 'Instituto de Empresa';
 
 UPDATE profile_education_enum
 SET abbreviation = 'Chalmers', name = 'Chalmers Tekniska Högskola', country = 'SE'
@@ -419,6 +463,9 @@ WHERE name = 'Univ Moscow (Bauman)';
 UPDATE profile_education_enum
 SET abbreviation = 'Technion', name = 'Israel Institute of Technology', country = 'IL'
 WHERE name = 'Univ Technion';
+UPDATE profile_education_enum
+SET abbreviation = '', name = 'Weizmann Institute of Science', country = 'IL'
+WHERE name = 'Institut Weizmann';
 
 UPDATE profile_education_enum
 SET abbreviation = 'Oxford', name = 'University of Oxford', country = 'GB'
@@ -448,6 +495,9 @@ WHERE name = 'Univ Cardiff';
 UPDATE profile_education_enum
 SET abbreviation = 'UNSW', name = 'The University of New South Wales', country = 'AU'
 WHERE name = 'Univ New South Wales  (Sydney Australia)';
+UPDATE profile_education_enum
+SET abbreviation = 'USyd', country = 'AU'
+WHERE name = 'University of Sydney';
 
 UPDATE profile_education_enum
 SET abbreviation = 'THU', name = 'Tsinghua University', country = 'CN'
@@ -461,6 +511,37 @@ UPDATE profile_education_enum
 SET abbreviation = 'NTNU', name = 'Norwegian University of Science and Technology', country = 'NO', url = 'http://www.ntnu.no/'
 WHERE name = 'Univ Trondheim';
 
+UPDATE profile_education_enum
+SET abbreviation = '', country = 'GB'
+WHERE name = 'University of Surrey';
+UPDATE profile_education_enum
+SET abbreviation = '', country = 'GB'
+WHERE name = 'Heriot-Watt University';
+UPDATE profile_education_enum
+SET abbreviation = 'UCL', country = 'GB'
+WHERE name = 'University College London';
+
+UPDATE profile_education_enum
+SET abbreviation = 'OIV', country = 'FR'
+WHERE name = 'Organisme International de la Vigne et du Vin';
+UPDATE profile_education_enum
+SET abbreviation = 'NSU', name = 'Novosibirsk State University', url = 'http://www.nsu.ru/', country = 'RU'
+WHERE name = 'Université de Novossibirsk (Новосибирский Государственный Университет)';
+
+UPDATE profile_education_enum
+SET abbreviation = 'IITs', country = 'IN'
+WHERE name = 'Indian Institutes of Technology';
+
+UPDATE profile_education_enum
+SET name = 'Institut des Hautes Études de Défense Nationale', country = 'FR', url ='http://www.ihedn.fr/',
+abbreviation = 'IHEDN'
+WHERE name = 'IHEDN';
+
+UPDATE profile_education_enum
+SET name = "Centre des Hautes Études de l'Armement", country = 'FR', url ='http://www.chear.defense.gouv.fr/',
+abbreviation = 'CHEAr'
+WHERE name = 'CHEAr';
+
 -- Cleans duplicated entries
 UPDATE profile_education SET eduid = 0 WHERE eduid = 70;
 DELETE FROM profile_education_enum WHERE id = 70;
@@ -473,7 +554,6 @@ DELETE FROM profile_education_degree WHERE eduid = 106;
 -- Adds new universities needed for the AX directory
 INSERT INTO  profile_education_enum (name, url, country, abbreviation)
      VALUES  ('Institut Supérieur de l\'Aéronautique et de l\'Espace', 'http://www.isae.fr/', 'FR', 'ISAE'),
-             ('Institut des Hautes Études de Défense Nationale', 'http://www.ihedn.fr/', 'FR', 'IHEDN'),
              ('École du Personnel Navigant d\'Essais et de Réception',
               'http://www.defense.gouv.fr/dga/archives/l_epner_ecole_du_personnel_navigant_d_essais_et_de_reception', 'FR', 'EPNER'),
              ('Agrocampus Ouest', 'http://www.agrocampus-ouest.fr/', 'FR', 'ENSAR'),
@@ -497,20 +577,16 @@ INSERT INTO  profile_education_enum (name, url, country, abbreviation)
              ('University of Kentucky', 'http://www.uky.edu/', 'US', 'UK'),
              ('Marine Corps University', 'http://www.mcu.usmc.mil/', 'US', 'MCU'),
              ('Chartered Institute of Management Accountants', 'http://www.cimaglobal.com/', 'GB', 'CIMA'),
-             ('University College London', 'http://www.ucl.ac.uk/', 'GB', 'UCL'),
-             ('Chartered Financial Analyst Institute', 'http://www.cfainstitute.org/', 'US', 'CFA Institute'),
              ('Naval Postgraduate School', 'http://www.nps.edu/', 'US', 'NPS'),
              ('Royal College of Art', 'http://www.rca.ac.uk/', 'GB', 'RCA'),
              ('Uniwersytet Gdański', 'http://www.univ.gda.pl/', 'PL', ''),
              ('College of Europe', 'http://www.coleurope.eu/', '', ''),
-             ('Purdue University', 'http://www.purdue.edu/', 'US', ''),
              ('Queen\'s University', 'http://www.queensu.ca/', 'CA', 'Queen\'s'),
              ('Université de Bretagne Occidentale', 'http://www.univ-brest.fr/', 'FR', 'UBO'),
              ('University of California, Davis', 'http://www.ucdavis.edu/', 'US', 'UC Davis'),
              ('Universität Stuttgarti', 'http://www.uni-stuttgart.de/', 'DE', ''),
              ('Universitatea Politehnica din Bucureşti', 'http://www.pub.ro/', 'RO', 'Politehnica din Bucureşti'),
              ('University of Birmingham', 'http://www.bham.ac.uk/', 'GB', ''),
-             ('University of Pennsylvania', 'http://www.upenn.edu/', 'US', ''),
              ('University of Rome', '', 'IT', ''),
              ('University of Sheffield', 'http://www.shef.ac.uk/', 'GB', ''),
              ('University of Utah', 'http://www.utah.edu/', 'US', ''),
@@ -518,11 +594,10 @@ INSERT INTO  profile_education_enum (name, url, country, abbreviation)
              ('Urbana University', 'http://www.urbana.edu/', 'US', ''),
              ('Université de Technologie de Compiègne', 'http://www.utc.fr/', 'FR', 'UTC'),
              ('Virginia Polytechnic Institute and State University', 'http://www.vt.edu/', 'US', 'Virginia Tech'),
-             ('Université Claude Bernard (Lyon I)', 'http://www.univ-lyon1.fr/', 'FR', 'Université Claude Bernard'),
              ('Cleveland State University', 'http://www.csuohio.edu/', 'US', 'CSU'),
              ('Centre de Perfectionnement aux Affaires du Nord', '', 'FR', 'CPA du Nord'),
              ('Centre de Perfectionnement aux Affaires de Lyon', '', '', 'CPA de Lyon'),
-             ('Amherst College', 'http://www.amherst.edu/', 'US', '')
+             ('Amherst College', 'http://www.amherst.edu/', 'US', ''),
              ('Ottawa University', 'http://www.ottawa.edu/', 'US', 'OU'),
              ('Indiana University', 'http://www.indiana.edu/', 'US', ''),
              ('University of Notre Dame du Lac', 'http://www.nd.edu/', 'US', 'Notre Dame'),
@@ -544,7 +619,21 @@ INSERT INTO  profile_education_enum (name, url, country, abbreviation)
              ('Centre Européen d\'Éducation Permanente', 'http://www.cedep.fr/', 'FR', 'CEDEP'),
              ('Collège Interarmées de Défense', 'http://www.college.interarmees.defense.gouv.fr/', 'FR', ''),
              ('Centre de Formation des Journalistes', 'http://www.cfpj.com/', 'FR', 'CFJ'),
-             ('Institut National des Hautes Études de Sécurité', 'http://www.inhes.interieur.gouv.fr/', 'FR', 'INHES');
+             ('Institut National des Hautes Études de Sécurité', 'http://www.inhes.interieur.gouv.fr/', 'FR', 'INHES'),
+             ('Université d\'Orléans', 'http://www.univ-orleans.fr/', 'FR', '');
+
+
+-- Médecine is not a university but an educational field
+REPLACE INTO  profile_education (pid, id, fieldid, eduid, degreeid)
+      SELECT  e.pid, e.id, f.id, 0, d.id
+        FROM  profile_education             AS e
+  INNER JOIN  profile_education_enum        AS l ON (l.id = e.eduid)
+  INNER JOIN  profile_education_degree_enum AS d ON (d.degree = "Doctorat")
+  INNER JOIN  profile_education_field_enum  AS f ON (f.field = "Médecine")
+       WHERE  l.name = "Médecine";
+
+DELETE FROM  profile_education_enum
+      WHERE  name = "Médecine";
 
 # vim:set syntax=mysql: