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   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   id_adh int(10) unsigned,
 323   creation_date datetime NOT NULL,
 324   PRIMARY KEY (search_id),
 325   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 327
 328 -- new table for temporary links
 329 DROP TABLE IF EXISTS galette_tmplinks;
 330 CREATE TABLE galette_tmplinks (
 331   hash varchar(250) NOT NULL,
 332   target smallint(1) NOT NULL,
 333   id int(10) unsigned,
 334   creation_date datetime NOT NULL,
 335   PRIMARY KEY (target, id)
 336 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 337
 338 -- table for social networks
 339 DROP TABLE IF EXISTS galette_socials;
 340 CREATE TABLE galette_socials (
 341   id_social int(10) unsigned NOT NULL auto_increment,
 342   id_adh int(10) unsigned NULL,
 343   type varchar(250) NOT NULL,
 344   url varchar(255) DEFAULT NULL,
 345   PRIMARY KEY (id_social),
 346   KEY (type),
 347   FOREIGN KEY (id_adh) REFERENCES  galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 348 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 349
 350 -- table for database version
 351 DROP TABLE IF EXISTS galette_database;
 352 CREATE TABLE galette_database (
 353   version DECIMAL(4,3) NOT NULL
 354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 355 INSERT INTO galette_database(version) VALUES(0.96);
 356
 357 SET FOREIGN_KEY_CHECKS=1;