Fix import scripts.

[platal.git] / upgrade / newdirectory-0.0.1 / 06_education.sql

diff --git a/upgrade/newdirectory-0.0.1/06_education.sql b/upgrade/newdirectory-0.0.1/06_education.sql
index 16982fa..631941c 100644 (file)
--- a/upgrade/newdirectory-0.0.1/06_education.sql
+++ b/upgrade/newdirectory-0.0.1/06_education.sql
@@ -1,42 +1,54 @@
-CREATE TABLE IF NOT EXISTS profile_education_field_enum (
+DROP TABLE IF EXISTS profile_education_field_enum;
+DROP TABLE IF EXISTS profile_education_degree_enum;
+DROP TABLE IF EXISTS profile_education_degree;
+DROP TABLE IF EXISTS profile_education_enum;
+DROP TABLE IF EXISTS profile_education;
+
+CREATE TABLE profile_education_field_enum (

   id INT(2) NOT NULL AUTO_INCREMENT,
   field VARCHAR(255) DEFAULT NULL,
   PRIMARY KEY(id),
   UNIQUE KEY(field)
   id INT(2) NOT NULL AUTO_INCREMENT,
   field VARCHAR(255) DEFAULT NULL,
   PRIMARY KEY(id),
   UNIQUE KEY(field)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

-CREATE TABLE IF NOT EXISTS profile_education_degree_enum (
+CREATE TABLE profile_education_degree_enum (

   id INT(2) NOT NULL AUTO_INCREMENT,
   degree VARCHAR(255) DEFAULT NULL,
   id INT(2) NOT NULL AUTO_INCREMENT,
   degree VARCHAR(255) DEFAULT NULL,

+  abbreviation VARCHAR(255) DEFAULT '' NOT NULL,
+  level TINYINT (1) UNSIGNED DEFAULT 0 NOT NULL,

   PRIMARY KEY(id),
   UNIQUE KEY(degree)
   PRIMARY KEY(id),
   UNIQUE KEY(degree)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

-CREATE TABLE IF NOT EXISTS profile_education_degree (
+CREATE TABLE profile_education_degree (

   eduid INT(4) NOT NULL DEFAULT 0,
   degreeid INT(2) NOT NULL DEFAULT 0,
   PRIMARY KEY(eduid, degreeid)
   eduid INT(4) NOT NULL DEFAULT 0,
   degreeid INT(2) NOT NULL DEFAULT 0,
   PRIMARY KEY(eduid, degreeid)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

-CREATE TABLE IF NOT EXISTS profile_education_enum (
+CREATE TABLE profile_education_enum (

   id INT(4) NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) DEFAULT NULL,
   id INT(4) NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) DEFAULT NULL,

+  abbreviation VARCHAR(255) DEFAULT '' NOT NULL,

   url VARCHAR(255) DEFAULT NULL,
   country CHAR(2) NOT NULL DEFAULT 'FR',
   PRIMARY KEY(id),
   UNIQUE KEY(name)
   url VARCHAR(255) DEFAULT NULL,
   country CHAR(2) NOT NULL DEFAULT 'FR',
   PRIMARY KEY(id),
   UNIQUE KEY(name)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

-CREATE TABLE IF NOT EXISTS profile_education (
-  id INT(2) NOT NULL DEFAULT 0,
+CREATE TABLE profile_education (
+  id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,

   uid INT(11) NOT NULL DEFAULT 0,
   eduid INT(4) NOT NULL DEFAULT 0,
   degreeid INT(4) NOT NULL DEFAULT 0,
   fieldid INT(2) NOT NULL DEFAULT 0,
   uid INT(11) NOT NULL DEFAULT 0,
   eduid INT(4) NOT NULL DEFAULT 0,
   degreeid INT(4) NOT NULL DEFAULT 0,
   fieldid INT(2) NOT NULL DEFAULT 0,

+  entry_year INT(4) DEFAULT NULL,

   grad_year INT(4) DEFAULT NULL,
   program VARCHAR(255) DEFAULT NULL,
   grad_year INT(4) DEFAULT NULL,
   program VARCHAR(255) DEFAULT NULL,

-  PRIMARY KEY(id, uid)
-) CHARSET=utf8;
+  flags SET('primary') DEFAULT '' NOT NULL,
+  PRIMARY KEY(id, uid),
+  INDEX uid (uid)
+) ENGINE=InnoDB, CHARSET=utf8;

 
 INSERT INTO  profile_education_field_enum (field)
      VALUES  ('Aéronautique'), ('Agronomie'), ('Assurance'), ('Biologie'),
 
 INSERT INTO  profile_education_field_enum (field)
      VALUES  ('Aéronautique'), ('Agronomie'), ('Assurance'), ('Biologie'),


@@ -52,16 +64,16 @@ INSERT INTO  profile_education_degree_enum (degree)
 
 INSERT INTO  profile_education_degree (eduid, degreeid)
      SELECT  a.id, d.id
 
 INSERT INTO  profile_education_degree (eduid, degreeid)
      SELECT  a.id, d.id

-       FROM  applis_def AS a
+       FROM  #x4dat#.applis_def AS a

  INNER JOIN  profile_education_degree_enum AS d ON (FIND_IN_SET(d.degree, a.type));
 
 INSERT INTO  profile_education_enum (id, name, url)
      SELECT  id, text, url
  INNER JOIN  profile_education_degree_enum AS d ON (FIND_IN_SET(d.degree, a.type));
 
 INSERT INTO  profile_education_enum (id, name, url)
      SELECT  id, text, url

-       FROM  applis_def;
+       FROM  #x4dat#.applis_def;

 
 INSERT INTO  profile_education (id, uid, eduid, degreeid)
      SELECT  a.ordre, a.uid, a.aid, d.id
 
 INSERT INTO  profile_education (id, uid, eduid, degreeid)
      SELECT  a.ordre, a.uid, a.aid, d.id

-       FROM  applis_ins AS a
+       FROM  #x4dat#.applis_ins AS a

  INNER JOIN  profile_education_degree_enum AS d ON (a.type = d.degree);
 
      UPDATE  watch_profile AS w1
  INNER JOIN  profile_education_degree_enum AS d ON (a.type = d.degree);
 
      UPDATE  watch_profile AS w1


@@ -80,6 +92,7 @@ ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nation
 
 UPDATE watch_profile SET field = 'edus' WHERE field = 'appli1';
 
 
 UPDATE watch_profile SET field = 'edus' WHERE field = 'appli1';
 

+

 DELETE FROM watch_profile WHERE field = 'appli2';
 
 ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nationalite', 'nationalite2',
 DELETE FROM watch_profile WHERE field = 'appli2';
 
 ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nationalite', 'nationalite2',