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   num_adh varchar(255) DEFAULT NULL,
  43   PRIMARY KEY (id_adh),
  44   UNIQUE (login_adh),
  45   FOREIGN KEY (id_statut) REFERENCES galette_statuts (id_statut) ON DELETE RESTRICT ON UPDATE CASCADE,
  46   FOREIGN KEY (titre_adh) REFERENCES galette_titles (id_title) ON DELETE RESTRICT ON UPDATE CASCADE,
  47   FOREIGN KEY (parent_id) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
  48 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  49
  50 DROP TABLE IF EXISTS galette_cotisations;
  51 CREATE TABLE galette_cotisations (
  52   id_cotis int(10) unsigned NOT NULL auto_increment,
  53   id_adh int(10) unsigned NOT NULL default '0',
  54   id_type_cotis int(10) unsigned NOT NULL default '0',
  55   montant_cotis decimal(15, 2) unsigned default '0',
  56   type_paiement_cotis int(10) unsigned NOT NULL,
  57   info_cotis text,
  58   date_enreg date NOT NULL default '1901-01-01',
  59   date_debut_cotis date NOT NULL default '1901-01-01',
  60   date_fin_cotis date NOT NULL default '1901-01-01',
  61   trans_id int(10) unsigned default NULL,
  62   PRIMARY KEY (id_cotis),
  63   FOREIGN KEY (id_type_cotis) REFERENCES galette_types_cotisation (id_type_cotis) ON DELETE RESTRICT ON UPDATE CASCADE,
  64   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
  65   FOREIGN KEY (trans_id) REFERENCES galette_transactions (trans_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  66   FOREIGN KEY (type_paiement_cotis) REFERENCES galette_paymenttypes (type_id) ON DELETE RESTRICT ON UPDATE CASCADE
  67 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  68
  69 DROP TABLE IF EXISTS galette_transactions;
  70 CREATE TABLE galette_transactions (
  71   trans_id int(10) unsigned NOT NULL auto_increment,
  72   trans_date date NOT NULL default '1901-01-01',
  73   trans_amount decimal(15, 2) default '0',
  74   trans_desc varchar(255) NOT NULL default '',
  75   id_adh int(10) unsigned default NULL,
  76   PRIMARY KEY (trans_id),
  77   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
  78 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  79
  80 DROP TABLE IF EXISTS galette_statuts;
  81 CREATE TABLE galette_statuts (
  82   id_statut int(10) unsigned NOT NULL auto_increment,
  83   libelle_statut varchar(255) NOT NULL default '',
  84   priorite_statut tinyint(4) NOT NULL default '0',
  85   PRIMARY KEY (id_statut)
  86 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  87
  88 DROP TABLE IF EXISTS galette_titles;
  89 CREATE TABLE galette_titles (
  90   id_title int(10) unsigned NOT NULL auto_increment,
  91   short_label varchar(10) NOT NULL default '',
  92   long_label varchar(100) default '',
  93   PRIMARY KEY (id_title)
  94 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  95
  96 DROP TABLE IF EXISTS galette_types_cotisation;
  97 CREATE TABLE galette_types_cotisation (
  98   id_type_cotis int(10) unsigned NOT NULL auto_increment,
  99   libelle_type_cotis varchar(255) NOT NULL default '',
 100   cotis_extension tinyint(1) NOT NULL default 0,
 101   PRIMARY KEY (id_type_cotis)
 102 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 103
 104 DROP TABLE IF EXISTS galette_preferences;
 105 CREATE TABLE galette_preferences (
 106   id_pref int(10) unsigned NOT NULL auto_increment,
 107   nom_pref varchar(100) NOT NULL default '',
 108   val_pref varchar(255) NOT NULL default '',
 109   PRIMARY KEY (id_pref),
 110   UNIQUE (nom_pref)
 111 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 112
 113 DROP TABLE IF EXISTS galette_logs;
 114 CREATE TABLE galette_logs (
 115   id_log int(10) unsigned NOT NULL auto_increment,
 116   date_log datetime NOT NULL,
 117   ip_log varchar(46) NOT NULL default '',
 118   adh_log varchar(255) NOT NULL default '', -- see galette_adherents.login_adh
 119   text_log text,
 120   action_log text,
 121   sql_log text,
 122   PRIMARY KEY (id_log)
 123 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 124
 125 -- Table for dynamic fields description;
 126 DROP TABLE IF EXISTS galette_field_types;
 127 CREATE TABLE galette_field_types (
 128     field_id int(10) unsigned NOT NULL auto_increment,
 129     field_form varchar(10) NOT NULL,
 130     field_index int(10) NOT NULL default '0',
 131     field_name varchar(255) NOT NULL default '',
 132     field_perm int(10) NOT NULL default '0',
 133     field_type int(10) NOT NULL default '0',
 134     field_required tinyint(1) NOT NULL default 0,
 135     field_pos int(10) NOT NULL default '0',
 136     field_width int(10) default NULL,
 137     field_height int(10) default NULL,
 138     field_size int(10) default NULL,
 139     field_repeat int(10) default NULL,
 140     field_layout int(10) default NULL,
 141     PRIMARY KEY (field_id),
 142     INDEX (field_form)
 143 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 144
 145 -- Table for dynamic fields data;
 146 DROP TABLE IF EXISTS galette_dynamic_fields;
 147 CREATE TABLE galette_dynamic_fields (
 148     item_id int(10) NOT NULL default '0',
 149     field_id int(10) unsigned NOT NULL default '0',
 150     field_form varchar(10) NOT NULL,
 151     val_index int(10) NOT NULL default '0',
 152     field_val text,
 153     PRIMARY KEY (item_id, field_id, field_form, val_index),
 154     FOREIGN KEY (field_id) REFERENCES galette_field_types (field_id) ON DELETE RESTRICT ON UPDATE CASCADE
 155 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 156
 157 DROP TABLE IF EXISTS galette_pictures;
 158 CREATE TABLE galette_pictures (
 159     id_adh int(10) unsigned NOT NULL default '0',
 160     picture mediumblob NOT NULL,
 161     format varchar(10) NOT NULL default '',
 162     PRIMARY KEY (id_adh)
 163 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 164
 165 -- Table for dynamic translation of strings;
 166 DROP TABLE IF EXISTS galette_l10n;
 167 CREATE TABLE galette_l10n (
 168     text_orig varchar(255) NOT NULL,
 169     text_locale varchar(15) NOT NULL,
 170     text_nref int(10) NOT NULL default '1',
 171     text_trans varchar(255) NOT NULL default '',
 172     PRIMARY KEY (text_orig, text_locale)
 173 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 174
 175 -- new table for temporary passwords 2006-02-18;
 176 DROP TABLE IF EXISTS galette_tmppasswds;
 177 CREATE TABLE galette_tmppasswds (
 178     id_adh int(10) unsigned NOT NULL,
 179     tmp_passwd varchar(250) NOT NULL,
 180     date_crea_tmp_passwd datetime NOT NULL,
 181     PRIMARY KEY (id_adh),
 182     FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 183 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 184
 185 -- Add new table for automatic mails and their translations;
 186 DROP TABLE IF EXISTS galette_texts;
 187 CREATE TABLE galette_texts (
 188   tid smallint(6) NOT NULL auto_increment,
 189   tref varchar(20) NOT NULL,
 190   tsubject varchar(256) NOT NULL,
 191   tbody text NOT NULL,
 192   tlang varchar(16) NOT NULL,
 193   tcomment varchar(255) NOT NULL,
 194   PRIMARY KEY (tid),
 195   UNIQUE KEY `localizedtxt` (tref, tlang)
 196 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 197
 198 DROP TABLE IF EXISTS galette_fields_categories;
 199 CREATE TABLE galette_fields_categories (
 200   id_field_category int(2) NOT NULL AUTO_INCREMENT,
 201   table_name varchar(30) NOT NULL,
 202   category varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 203   position int(2) NOT NULL,
 204   PRIMARY KEY (id_field_category)
 205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 206
 207 DROP TABLE IF EXISTS galette_fields_config;
 208 CREATE TABLE galette_fields_config (
 209   table_name varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 210   field_id varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 211   required tinyint(1) NOT NULL,
 212   visible tinyint(1) NOT NULL,
 213   position int(2) NOT NULL,
 214   id_field_category int(2) NOT NULL,
 215   list_visible tinyint(1) NOT NULL,
 216   list_position 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(255) 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(250) 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(250),
 294   model_subtitle varchar(250),
 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   id_adh int(10) unsigned,
 324   creation_date datetime NOT NULL,
 325   PRIMARY KEY (search_id),
 326   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 328
 329 -- new table for temporary links
 330 DROP TABLE IF EXISTS galette_tmplinks;
 331 CREATE TABLE galette_tmplinks (
 332   hash varchar(250) NOT NULL,
 333   target smallint(1) NOT NULL,
 334   id int(10) unsigned,
 335   creation_date datetime NOT NULL,
 336   PRIMARY KEY (target, id)
 337 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 338
 339 -- table for social networks
 340 DROP TABLE IF EXISTS galette_socials;
 341 CREATE TABLE galette_socials (
 342   id_social int(10) unsigned NOT NULL auto_increment,
 343   id_adh int(10) unsigned NULL,
 344   type varchar(250) NOT NULL,
 345   url varchar(255) DEFAULT NULL,
 346   PRIMARY KEY (id_social),
 347   KEY (type),
 348   FOREIGN KEY (id_adh) REFERENCES  galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 349 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 350
 351 -- table for database version
 352 DROP TABLE IF EXISTS galette_database;
 353 CREATE TABLE galette_database (
 354   version DECIMAL(4,3) NOT NULL
 355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 356 INSERT INTO galette_database(version) VALUES(0.96);
 357
 358 SET FOREIGN_KEY_CHECKS=1;