galette/install/scripts/upgrade-to-0.96-mysql.sql

   1 CREATE TABLE galette_socials (
   2      id_social int(10) unsigned NOT NULL auto_increment,
   3      id_adh int(10) unsigned NULL,
   4      type varchar(250) NOT NULL,
   5      url varchar(255) DEFAULT NULL,
   6      PRIMARY KEY (id_social),
   7      KEY (type),
   8      FOREIGN KEY (id_adh) REFERENCES  galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
   9 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  10
  11 -- migrate socials from preferences
  12 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'google+', val_pref FROM galette_preferences WHERE nom_pref = 'pref_googleplus' AND val_pref != '';
  13 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'facebook', val_pref FROM galette_preferences WHERE nom_pref = 'pref_facebook' AND val_pref != '';
  14 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'twitter', val_pref FROM galette_preferences WHERE nom_pref = 'pref_twitter' AND val_pref != '';
  15 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'linkedin', val_pref FROM galette_preferences WHERE nom_pref = 'pref_linkedin' AND val_pref != '';
  16 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'viadeo', val_pref FROM galette_preferences WHERE nom_pref = 'pref_viadeo' AND val_pref != '';
  17 -- cleanup preferences
  18 DELETE FROM galette_preferences WHERE
  19     nom_pref = 'pref_googleplus'
  20     OR nom_pref = 'pref_facebook'
  21     OR nom_pref = 'pref_twitter'
  22     OR nom_pref = 'pref_linkedin'
  23     OR nom_pref = 'pref_viadeo';
  24 -- update pdf card address
  25 UPDATE galette_preferences SET val_pref = 0 WHERE nom_pref = 'pref_card_address' AND val_pref IN ('1', '2', '3', '4');
  26
  27 -- migrate members socials
  28 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'website', url_adh FROM galette_adherents WHERE url_adh != '';
  29 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'icq', icq_adh FROM galette_adherents WHERE icq_adh != '';
  30 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'msn', msn_adh FROM galette_adherents WHERE msn_adh != '';
  31 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'jabber', jabber_adh FROM galette_adherents WHERE jabber_adh != '';
  32 -- cleanup members table
  33 ALTER TABLE galette_adherents DROP column url_adh;
  34 ALTER TABLE galette_adherents DROP column icq_adh;
  35 ALTER TABLE galette_adherents DROP column msn_adh;
  36 ALTER TABLE galette_adherents DROP column jabber_adh;
  37 -- cleanup fields config
  38 DELETE FROM galette_fields_config WHERE field_id IN ('url_adh', 'icq_adh', 'msn_adh', 'jabber_adh');
  39
  40 -- add num_adh column
  41 ALTER TABLE galette_adherents ADD COLUMN num_adh varchar(255) DEFAULT NULL;
  42
  43 UPDATE galette_database SET version = 0.960;