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

   1 -- sequence for socials
   2 CREATE SEQUENCE galette_socials_id_seq
   3     START 1
   4     INCREMENT 1
   5     MAXVALUE 2147483647
   6     MINVALUE 1
   7     CACHE 1;
   8
   9 CREATE TABLE galette_socials (
  10  id_social integer DEFAULT nextval('galette_socials_id_seq'::text) NOT NULL,
  11  id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
  12  type character varying(250) NOT NULL,
  13  url character varying(255) DEFAULT NULL,
  14  PRIMARY KEY (id_social)
  15 );
  16 -- add index on table to look for type
  17 CREATE INDEX galette_socials_idx ON galette_socials (type);
  18
  19 -- migrate socials from preferences
  20 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'google+', val_pref FROM galette_preferences WHERE nom_pref = 'pref_googleplus' AND val_pref != '';
  21 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'facebook', val_pref FROM galette_preferences WHERE nom_pref = 'pref_facebook' AND val_pref != '';
  22 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'twitter', val_pref FROM galette_preferences WHERE nom_pref = 'pref_twitter' AND val_pref != '';
  23 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'linkedin', val_pref FROM galette_preferences WHERE nom_pref = 'pref_linkedin' AND val_pref != '';
  24 INSERT INTO galette_socials (id_adh, type, url) SELECT null, 'viadeo', val_pref FROM galette_preferences WHERE nom_pref = 'pref_viadeo' AND val_pref != '';
  25 -- cleanup preferences
  26 DELETE FROM galette_preferences WHERE
  27     nom_pref = 'pref_googleplus'
  28     OR nom_pref = 'pref_facebook'
  29     OR nom_pref = 'pref_twitter'
  30     OR nom_pref = 'pref_linkedin'
  31     OR nom_pref = 'pref_viadeo';
  32 -- update pdf card address
  33 UPDATE galette_preferences SET val_pref = 0 WHERE nom_pref = 'pref_card_address' AND val_pref IN ('1', '2', '3', '4');
  34
  35 -- migrate members socials
  36 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'website', url_adh FROM galette_adherents WHERE url_adh != '';
  37 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'icq', icq_adh FROM galette_adherents WHERE icq_adh != '';
  38 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'msn', msn_adh FROM galette_adherents WHERE msn_adh != '';
  39 INSERT INTO galette_socials (id_adh, type, url) SELECT id_adh, 'jabber', jabber_adh FROM galette_adherents WHERE jabber_adh != '';
  40 -- cleanup members table
  41 ALTER TABLE galette_adherents DROP column url_adh;
  42 ALTER TABLE galette_adherents DROP column icq_adh;
  43 ALTER TABLE galette_adherents DROP column msn_adh;
  44 ALTER TABLE galette_adherents DROP column jabber_adh;
  45 -- cleanup fields config table
  46 DELETE FROM galette_fields_config WHERE field_id IN ('url_adh', 'icq_adh', 'msn_adh', 'jabber_adh');
  47
  48 -- add num_adh column
  49 ALTER TABLE galette_adherents ADD COLUMN num_adh character varying (255) DEFAULT NULL;
  50
  51 DROP INDEX galette_searches_idx;
  52 ALTER TABLE galette_searches DROP COLUMN parameters_sum;
  53
  54 -- drop groups unique name constraint
  55 ALTER TABLE galette_groups DROP CONSTRAINT name;
  56
  57 UPDATE galette_database SET version = 0.960;