galette/install/scripts/mysql.sql

   1 -- CREATE DATABASE `galette` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
   2 -- $Id$
   3
   4 SET FOREIGN_KEY_CHECKS=0;
   5
   6 DROP TABLE IF EXISTS galette_adherents;
   7 CREATE TABLE galette_adherents (
   8   id_adh int(10) unsigned NOT NULL auto_increment,
   9   id_statut int(10) unsigned NOT NULL default '4',
  10   nom_adh varchar(50) NOT NULL default '',
  11   prenom_adh varchar(50) NOT NULL default '',
  12   pseudo_adh varchar(20) NOT NULL default '',
  13   societe_adh varchar(200) default NULL,
  14   titre_adh int(10) unsigned default NULL,
  15   ddn_adh date default '1901-01-01',
  16   sexe_adh tinyint(1) NOT NULL default '0',
  17   adresse_adh varchar(150) NOT NULL default '',
  18   adresse2_adh varchar(150) default NULL,
  19   cp_adh varchar(10) NOT NULL default '',
  20   ville_adh varchar(50) NOT NULL default '',
  21   pays_adh varchar(50) default NULL,
  22   tel_adh varchar(20) default NULL,
  23   gsm_adh varchar(20) default NULL,
  24   email_adh varchar(255) default NULL,
  25   url_adh varchar(200) default NULL,
  26   icq_adh varchar(20) default NULL,
  27   msn_adh varchar(150) default NULL,
  28   jabber_adh varchar(150) default NULL,
  29   info_adh text,
  30   info_public_adh text,
  31   prof_adh varchar(150) default NULL,
  32   login_adh varchar(20) NOT NULL default '',
  33   mdp_adh varchar(60) NOT NULL default '',
  34   date_crea_adh date NOT NULL default '1901-01-01',
  35   date_modif_adh date NOT NULL default '1901-01-01',
  36   activite_adh tinyint(1) NOT NULL default 0,
  37   bool_admin_adh tinyint(1) NOT NULL default 0,
  38   bool_exempt_adh tinyint(1) NOT NULL default 0,
  39   bool_display_info tinyint(1) NOT NULL default 0,
  40   date_echeance date default NULL,
  41   pref_lang varchar(20) default 'fr_FR',
  42   lieu_naissance text,
  43   gpgid text DEFAULT NULL,
  44   fingerprint varchar(50) DEFAULT NULL,
  45   parent_id int(10) unsigned DEFAULT NULL,
  46   PRIMARY KEY (id_adh),
  47   UNIQUE (login_adh),
  48   FOREIGN KEY (id_statut) REFERENCES galette_statuts (id_statut) ON DELETE RESTRICT ON UPDATE CASCADE,
  49   FOREIGN KEY (titre_adh) REFERENCES galette_titles (id_title) ON DELETE RESTRICT ON UPDATE CASCADE,
  50   FOREIGN KEY (parent_id) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
  51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  52
  53 DROP TABLE IF EXISTS galette_cotisations;
  54 CREATE TABLE galette_cotisations (
  55   id_cotis int(10) unsigned NOT NULL auto_increment,
  56   id_adh int(10) unsigned NOT NULL default '0',
  57   id_type_cotis int(10) unsigned NOT NULL default '0',
  58   montant_cotis decimal(15, 2) unsigned default '0',
  59   type_paiement_cotis int(10) unsigned NOT NULL,
  60   info_cotis text,
  61   date_enreg date NOT NULL default '1901-01-01',
  62   date_debut_cotis date NOT NULL default '1901-01-01',
  63   date_fin_cotis date NOT NULL default '1901-01-01',
  64   trans_id int(10) unsigned default NULL,
  65   PRIMARY KEY (id_cotis),
  66   FOREIGN KEY (id_type_cotis) REFERENCES galette_types_cotisation (id_type_cotis) ON DELETE RESTRICT ON UPDATE CASCADE,
  67   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
  68   FOREIGN KEY (trans_id) REFERENCES galette_transactions (trans_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  69   FOREIGN KEY (type_paiement_cotis) REFERENCES galette_paymenttypes (type_id) ON DELETE RESTRICT ON UPDATE CASCADE
  70 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  71
  72 DROP TABLE IF EXISTS galette_transactions;
  73 CREATE TABLE galette_transactions (
  74   trans_id int(10) unsigned NOT NULL auto_increment,
  75   trans_date date NOT NULL default '1901-01-01',
  76   trans_amount decimal(15, 2) default '0',
  77   trans_desc varchar(150) NOT NULL default '',
  78   id_adh int(10) unsigned default NULL,
  79   PRIMARY KEY (trans_id),
  80   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
  81 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  82
  83 DROP TABLE IF EXISTS galette_statuts;
  84 CREATE TABLE galette_statuts (
  85   id_statut int(10) unsigned NOT NULL auto_increment,
  86   libelle_statut varchar(100) NOT NULL default '',
  87   priorite_statut tinyint(4) NOT NULL default '0',
  88   PRIMARY KEY (id_statut)
  89 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  90
  91 DROP TABLE IF EXISTS galette_titles;
  92 CREATE TABLE galette_titles (
  93   id_title int(10) unsigned NOT NULL auto_increment,
  94   short_label varchar(10) NOT NULL default '',
  95   long_label varchar(30) default '',
  96   PRIMARY KEY (id_title)
  97 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  98
  99 DROP TABLE IF EXISTS galette_types_cotisation;
 100 CREATE TABLE galette_types_cotisation (
 101   id_type_cotis int(10) unsigned NOT NULL auto_increment,
 102   libelle_type_cotis varchar(100) NOT NULL default '',
 103   cotis_extension tinyint(1) NOT NULL default 0,
 104   PRIMARY KEY (id_type_cotis)
 105 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 106
 107 DROP TABLE IF EXISTS galette_preferences;
 108 CREATE TABLE galette_preferences (
 109   id_pref int(10) unsigned NOT NULL auto_increment,
 110   nom_pref varchar(100) NOT NULL default '',
 111   val_pref varchar(200) NOT NULL default '',
 112   PRIMARY KEY (id_pref),
 113   UNIQUE (nom_pref)
 114 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 115
 116 DROP TABLE IF EXISTS galette_logs;
 117 CREATE TABLE galette_logs (
 118   id_log int(10) unsigned NOT NULL auto_increment,
 119   date_log datetime NOT NULL,
 120   ip_log varchar(46) NOT NULL default '',
 121   adh_log varchar(41) NOT NULL default '',
 122   text_log text,
 123   action_log text,
 124   sql_log text,
 125   PRIMARY KEY (id_log)
 126 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 127
 128 -- Table for dynamic fields description;
 129 DROP TABLE IF EXISTS galette_field_types;
 130 CREATE TABLE galette_field_types (
 131     field_id int(10) unsigned NOT NULL auto_increment,
 132     field_form varchar(10) NOT NULL,
 133     field_index int(10) NOT NULL default '0',
 134     field_name varchar(40) NOT NULL default '',
 135     field_perm int(10) NOT NULL default '0',
 136     field_type int(10) NOT NULL default '0',
 137     field_required tinyint(1) NOT NULL default 0,
 138     field_pos int(10) NOT NULL default '0',
 139     field_width int(10) default NULL,
 140     field_height int(10) default NULL,
 141     field_size int(10) default NULL,
 142     field_repeat int(10) default NULL,
 143     field_layout int(10) default NULL,
 144     PRIMARY KEY (field_id),
 145     INDEX (field_form)
 146 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 147
 148 -- Table for dynamic fields data;
 149 DROP TABLE IF EXISTS galette_dynamic_fields;
 150 CREATE TABLE galette_dynamic_fields (
 151     item_id int(10) NOT NULL default '0',
 152     field_id int(10) unsigned NOT NULL default '0',
 153     field_form varchar(10) NOT NULL,
 154     val_index int(10) NOT NULL default '0',
 155     field_val text,
 156     PRIMARY KEY (item_id, field_id, field_form, val_index),
 157     FOREIGN KEY (field_id) REFERENCES galette_field_types (field_id) ON DELETE RESTRICT ON UPDATE CASCADE
 158 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 159
 160 DROP TABLE IF EXISTS galette_pictures;
 161 CREATE TABLE galette_pictures (
 162     id_adh int(10) unsigned NOT NULL default '0',
 163     picture mediumblob NOT NULL,
 164     format varchar(10) NOT NULL default '',
 165     PRIMARY KEY (id_adh)
 166 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 167
 168 -- Table for dynamic translation of strings;
 169 DROP TABLE IF EXISTS galette_l10n;
 170 CREATE TABLE galette_l10n (
 171     text_orig varchar(100) NOT NULL,
 172     text_locale varchar(15) NOT NULL,
 173     text_nref int(10) NOT NULL default '1',
 174     text_trans varchar(100) NOT NULL default '',
 175     PRIMARY KEY (text_orig, text_locale)
 176 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 177
 178 -- new table for temporary passwords 2006-02-18;
 179 DROP TABLE IF EXISTS galette_tmppasswds;
 180 CREATE TABLE galette_tmppasswds (
 181     id_adh int(10) unsigned NOT NULL,
 182     tmp_passwd varchar(60) NOT NULL,
 183     date_crea_tmp_passwd datetime NOT NULL,
 184     PRIMARY KEY (id_adh),
 185     FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 186 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 187
 188 -- Add new table for automatic mails and their translations;
 189 DROP TABLE IF EXISTS galette_texts;
 190 CREATE TABLE galette_texts (
 191   tid smallint(6) NOT NULL auto_increment,
 192   tref varchar(20) NOT NULL,
 193   tsubject varchar(256) NOT NULL,
 194   tbody text NOT NULL,
 195   tlang varchar(16) NOT NULL,
 196   tcomment varchar(64) NOT NULL,
 197   PRIMARY KEY (tid)
 198 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 199
 200 DROP TABLE IF EXISTS galette_fields_categories;
 201 CREATE TABLE galette_fields_categories (
 202   id_field_category int(2) NOT NULL AUTO_INCREMENT,
 203   table_name varchar(30) NOT NULL,
 204   category varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 205   position int(2) NOT NULL,
 206   PRIMARY KEY (id_field_category)
 207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 208
 209 DROP TABLE IF EXISTS galette_fields_config;
 210 CREATE TABLE galette_fields_config (
 211   table_name varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 212   field_id varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 213   required tinyint(1) NOT NULL,
 214   visible tinyint(1) NOT NULL,
 215   position int(2) NOT NULL,
 216   id_field_category int(2) NOT NULL,
 217   PRIMARY KEY (table_name, field_id),
 218   FOREIGN KEY (id_field_category) REFERENCES galette_fields_categories (id_field_category) ON DELETE RESTRICT ON UPDATE CASCADE
 219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 220
 221 -- Table for mailing history storage;
 222 DROP TABLE IF EXISTS galette_mailing_history;
 223 CREATE TABLE galette_mailing_history (
 224   mailing_id smallint(6) NOT NULL auto_increment,
 225   mailing_sender int(10) unsigned,
 226   mailing_subject varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 227   mailing_body text NOT NULL,
 228   mailing_date datetime NOT NULL,
 229   mailing_recipients text NOT NULL,
 230   mailing_sent tinyint(1) NOT NULL,
 231   mailing_sender_name varchar(100) DEFAULT NULL,
 232   mailing_sender_address varchar(255) DEFAULT NULL,
 233   PRIMARY KEY (mailing_id),
 234   FOREIGN KEY (mailing_sender) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
 235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 236
 237 -- table for groups
 238 DROP TABLE IF EXISTS galette_groups;
 239 CREATE TABLE galette_groups (
 240   id_group int(10) NOT NULL AUTO_INCREMENT,
 241   group_name varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 242   creation_date datetime NOT NULL,
 243   parent_group int(10) DEFAULT NULL,
 244   PRIMARY KEY (id_group),
 245   UNIQUE KEY `name` (group_name),
 246   FOREIGN KEY (parent_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 248
 249 -- table for groups managers
 250 DROP TABLE IF EXISTS galette_groups_managers;
 251 CREATE TABLE galette_groups_managers (
 252   id_group int(10) NOT NULL,
 253   id_adh int(10) unsigned NOT NULL,
 254   PRIMARY KEY (id_group,id_adh),
 255   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 256   FOREIGN KEY (id_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 257 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 258
 259 -- table for groups member
 260 DROP TABLE IF EXISTS galette_groups_members;
 261 CREATE TABLE galette_groups_members (
 262   id_group int(10) NOT NULL,
 263   id_adh int(10) unsigned NOT NULL,
 264   PRIMARY KEY (id_group,id_adh),
 265   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 266   FOREIGN KEY (id_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 268
 269 -- Table for reminders
 270 DROP TABLE IF EXISTS galette_reminders;
 271 CREATE TABLE galette_reminders (
 272   reminder_id smallint(6) NOT NULL auto_increment,
 273   reminder_type int(10) NOT NULL,
 274   reminder_dest int(10) unsigned,
 275   reminder_date datetime NOT NULL,
 276   reminder_success tinyint(1) NOT NULL DEFAULT 0,
 277   reminder_nomail tinyint(1) NOT NULL DEFAULT 1,
 278   reminder_comment text,
 279   PRIMARY KEY (reminder_id),
 280   FOREIGN KEY (reminder_dest) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 282
 283 -- Table for PDF models
 284 DROP TABLE IF EXISTS galette_pdfmodels;
 285 CREATE TABLE galette_pdfmodels (
 286   model_id int(10) unsigned NOT NULL auto_increment,
 287   model_name varchar(50) NOT NULL,
 288   model_type tinyint(2) NOT NULL,
 289   model_header text,
 290   model_footer text,
 291   model_body text,
 292   model_styles text,
 293   model_title varchar(100),
 294   model_subtitle varchar(100),
 295   model_parent int(10) unsigned DEFAULT NULL REFERENCES galette_pdfmodels (model_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 296   PRIMARY KEY (model_id)
 297 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 298
 299 -- Table for import models
 300 DROP TABLE IF EXISTS galette_import_model;
 301 CREATE TABLE galette_import_model (
 302   model_id smallint(6) NOT NULL auto_increment,
 303   model_fields text,
 304   model_creation_date datetime NOT NULL,
 305   PRIMARY KEY (model_id)
 306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 307
 308 -- Table for payment types
 309 DROP TABLE IF EXISTS galette_paymenttypes;
 310 CREATE TABLE galette_paymenttypes (
 311   type_id int(10) unsigned NOT NULL auto_increment,
 312   type_name varchar(255) NOT NULL,
 313   PRIMARY KEY (type_id)
 314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 315
 316 -- table for saved searches
 317 DROP TABLE IF EXISTS galette_searches;
 318 CREATE TABLE galette_searches (
 319   search_id int(10) unsigned NOT NULL auto_increment,
 320   name varchar(100) DEFAULT NULL,
 321   form varchar(50) NOT NULL,
 322   parameters text NOT NULL,
 323   parameters_sum binary(20),
 324   id_adh int(10) unsigned,
 325   creation_date datetime NOT NULL,
 326   PRIMARY KEY (search_id),
 327   KEY (form, parameters_sum, id_adh),
 328   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 330
 331 -- table for database version
 332 DROP TABLE IF EXISTS galette_database;
 333 CREATE TABLE galette_database (
 334   version DECIMAL(4,3) NOT NULL
 335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 336 INSERT INTO galette_database(version) VALUES(0.931);
 337
 338 SET FOREIGN_KEY_CHECKS=1;