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

   1 ALTER TABLE galette_adherents ADD pref_lang varchar(20) default 'french' AFTER date_echeance;
   2 ALTER TABLE galette_adherents ADD lieu_naissance varchar(20) default '';
   3 ALTER TABLE galette_adherents ADD gpgid varchar(8);
   4 ALTER TABLE galette_adherents ADD fingerprint varchar(50);
   5
   6 INSERT INTO galette_types_cotisation VALUES (7, 'Cotisation annuelle (a payer)');
   7 ALTER TABLE galette_adherents ADD  UNIQUE (login_adh);
   8 ALTER TABLE `galette_adherents` CHANGE `mdp_adh` `mdp_adh` VARCHAR(40);
   9
  10 -- Add new or missing preferences;
  11 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_pays', '-');
  12 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_website', '');
  13 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_mail_method', '0');
  14 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_mail_smtp', '0');
  15 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_membership_ext', '12');
  16 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_beg_membership', '');
  17 INSERT INTO galette_preferences (nom_pref, val_pref) VALUES ('pref_email_reply_to', '');
  18
  19 -- New tables for dynamic fields;
  20 DROP TABLE IF EXISTS galette_field_types;
  21 CREATE TABLE galette_field_types (
  22     field_id int(10) unsigned NOT NULL auto_increment,
  23     field_form varchar(10) NOT NULL,
  24     field_index int(10) NOT NULL default '0',
  25     field_name varchar(40) NOT NULL default '',
  26     field_perm int(10) NOT NULL default '0',
  27     field_type int(10) NOT NULL default '0',
  28     field_required enum('1') default NULL,
  29     field_pos int(10) NOT NULL default '0',
  30     field_width int(10) default NULL,
  31     field_height int(10) default NULL,
  32     field_size int(10) default NULL,
  33     field_repeat int(10) default NULL,
  34     field_layout int(10) default NULL,
  35     PRIMARY KEY (field_id),
  36     INDEX (field_form)
  37 ) ENGINE=MyISAM;
  38
  39 DROP TABLE IF EXISTS galette_dynamic_fields;
  40 CREATE TABLE galette_dynamic_fields (
  41     item_id int(10) NOT NULL default '0',
  42     field_id int(10) NOT NULL default '0',
  43     field_form varchar(10) NOT NULL,
  44     val_index int(10) NOT NULL default '0',
  45     field_val text DEFAULT '',
  46     KEY  (item_id)
  47 ) ENGINE=MyISAM;
  48
  49 -- Table for member photographs;
  50 DROP TABLE IF EXISTS galette_pictures;
  51 CREATE TABLE `galette_pictures` (
  52     `id_adh` int(10) unsigned NOT NULL default '0',
  53     `picture` mediumblob NOT NULL,
  54     `format` varchar(10) NOT NULL default '',
  55     PRIMARY KEY  (`id_adh`)
  56 ) ENGINE=MyISAM;
  57
  58 -- Add two fileds for log;
  59 ALTER TABLE galette_logs ADD action_log text;
  60 ALTER TABLE galette_logs ADD sql_log text;
  61
  62 -- Change table cotisations to store date_fin_cotis instead of duration;
  63 ALTER TABLE galette_cotisations ADD date_enreg date NOT NULL default '0000-00-00';
  64 ALTER TABLE galette_cotisations ADD date_debut_cotis date NOT NULL default '0000-00-00';
  65 ALTER TABLE galette_cotisations ADD date_fin_cotis date NOT NULL default '0000-00-00';
  66 UPDATE galette_cotisations
  67     SET date_enreg=date_cotis,
  68         date_debut_cotis=date_cotis,
  69         date_fin_cotis=DATE_ADD(date_cotis, INTERVAL duree_mois_cotis MONTH);
  70 ALTER TABLE galette_cotisations DROP duree_mois_cotis;
  71 ALTER TABLE galette_cotisations DROP date_cotis;
  72
  73 -- Add column to galette_types_cotisations;
  74 ALTER TABLE galette_types_cotisation ADD cotis_extension enum('1') default NULL;
  75 UPDATE galette_types_cotisation SET cotis_extension='1' WHERE
  76     id_type_cotis <= 3 OR id_type_cotis = 7;
  77
  78 -- Table for dynamic translation of strings;
  79 DROP TABLE IF EXISTS galette_l10n;
  80 CREATE TABLE galette_l10n (
  81     text_orig varchar(40) NOT NULL,
  82     text_locale varchar(15) NOT NULL,
  83     text_nref int(10) NOT NULL default '1',
  84     text_trans varchar(100) NOT NULL default '',
  85     UNIQUE INDEX (text_orig(20), text_locale(5))
  86 ) ENGINE=MyISAM;
  87
  88 -- Table for transactions;
  89 DROP TABLE IF EXISTS galette_transactions;
  90 CREATE TABLE galette_transactions (
  91   trans_id int(10) unsigned NOT NULL auto_increment,
  92   trans_date date NOT NULL default '0000-00-00',
  93   trans_amount float default '0',
  94   trans_desc varchar(30) NOT NULL default '',
  95   id_adh int(10) unsigned default NULL,
  96   PRIMARY KEY  (trans_id)
  97 ) ENGINE=MyISAM;
  98
  99 ALTER TABLE galette_cotisations ADD trans_id int(10) unsigned DEFAULT NULL;
 100
 101 -- new table for temporary passwords  2006-02-18;
 102 DROP TABLE IF EXISTS galette_tmppasswds;
 103 CREATE TABLE galette_tmppasswds (
 104     id_adh int(10) NOT NULL,
 105     tmp_passwd varchar(40) NOT NULL,
 106     date_crea_tmp_passwd datetime NOT NULL,
 107     PRIMARY KEY (id_adh)
 108 ) ENGINE=MyISAM;
 109
 110 -- 0.63 now uses md5 hash for passwords
 111 UPDATE galette_adherents SET mdp_adh = md5(mdp_adh) WHERE length(mdp_adh) <> 32;