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