X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F09_education.sql;h=058ad93764c8ab96592b5a083b223afa647f6844;hb=843a2191e4c76f0beb00c754f06974de76791238;hp=53099e61ef06f9be154e41cdea9d90cd5fa2c0b6;hpb=7c4e0f19204fa7ac1dfa0915ab11e6bee8c2edf0;p=platal.git diff --git a/upgrade/newdirectory-0.0.1/09_education.sql b/upgrade/newdirectory-0.0.1/09_education.sql index 53099e6..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 @@ -308,6 +335,17 @@ 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' @@ -349,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' @@ -454,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' @@ -495,7 +539,7 @@ 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/', -WHERE name = 'CHEAr' +abbreviation = 'CHEAr' WHERE name = 'CHEAr'; -- Cleans duplicated entries @@ -533,19 +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'), - ('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', ''), @@ -553,7 +594,6 @@ 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'), @@ -584,8 +624,8 @@ INSERT INTO profile_education_enum (name, url, country, abbreviation) -- Médecine is not a university but an educational field -REPLACE INTO profile_education (uid, id, fieldid, eduid, degreeid) - SELECT e.uid, e.id, f.id, 0, d.id +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")