galette/install/scripts/mysql.sql

   1 -- CREATE DATABASE `galette` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_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   cp_adh varchar(10) NOT NULL default '',
  19   ville_adh varchar(200) NOT NULL default '',
  20   region_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=utf8mb4 COLLATE=utf8mb4_unicode_520_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) NOT NULL,
  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 utf8mb4 COLLATE utf8mb4_unicode_520_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) NOT NULL,
  74   trans_desc varchar(255) NOT NULL default '',
  75   id_adh int(10) unsigned default NULL,
  76   type_paiement_trans int(10) unsigned NULL DEFAULT NULL,
  77   PRIMARY KEY (trans_id),
  78   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
  79   FOREIGN KEY (type_paiement_trans) REFERENCES galette_paymenttypes (type_id) ON DELETE RESTRICT ON UPDATE CASCADE
  80 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
  81
  82 DROP TABLE IF EXISTS galette_statuts;
  83 CREATE TABLE galette_statuts (
  84   id_statut int(10) unsigned NOT NULL auto_increment,
  85   libelle_statut varchar(255) NOT NULL default '',
  86   priorite_statut tinyint(4) NOT NULL default '0',
  87   PRIMARY KEY (id_statut)
  88 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
  89
  90 DROP TABLE IF EXISTS galette_titles;
  91 CREATE TABLE galette_titles (
  92   id_title int(10) unsigned NOT NULL auto_increment,
  93   short_label varchar(10) NOT NULL default '',
  94   long_label varchar(100) default '',
  95   PRIMARY KEY (id_title)
  96 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
  97
  98 DROP TABLE IF EXISTS galette_types_cotisation;
  99 CREATE TABLE galette_types_cotisation (
 100   id_type_cotis int(10) unsigned NOT NULL auto_increment,
 101   libelle_type_cotis varchar(255) NOT NULL default '',
 102   amount decimal(15,2) NULL DEFAULT NULL,
 103   cotis_extension tinyint(1) NOT NULL default 0,
 104   PRIMARY KEY (id_type_cotis)
 105 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_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(255) NOT NULL default '',
 112   PRIMARY KEY (id_pref),
 113   UNIQUE (nom_pref)
 114 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_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(255) NOT NULL default '', -- see galette_adherents.login_adh
 122   text_log text,
 123   action_log text,
 124   sql_log text,
 125   PRIMARY KEY (id_log)
 126 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_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(255) NOT NULL default '',
 135     field_perm int(10) NOT NULL default 1,
 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_min_size int(10) default NULL,
 142     field_size int(10) default NULL,
 143     field_repeat int(10) default NULL,
 144     field_information TEXT default NULL,
 145     field_width_in_forms tinyint(1) NOT NULL default 1,
 146     field_information_above tinyint(1) NOT NULL default 0,
 147     PRIMARY KEY (field_id),
 148     INDEX (field_form)
 149 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 150
 151 -- Table for dynamic fields data;
 152 DROP TABLE IF EXISTS galette_dynamic_fields;
 153 CREATE TABLE galette_dynamic_fields (
 154     item_id int(10) NOT NULL default '0',
 155     field_id int(10) unsigned NOT NULL default '0',
 156     field_form varchar(10) NOT NULL,
 157     val_index int(10) NOT NULL default '0',
 158     field_val text,
 159     PRIMARY KEY (item_id, field_id, field_form, val_index),
 160     FOREIGN KEY (field_id) REFERENCES galette_field_types (field_id) ON DELETE RESTRICT ON UPDATE CASCADE
 161 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 162
 163 DROP TABLE IF EXISTS galette_pictures;
 164 CREATE TABLE galette_pictures (
 165     id_adh int(10) unsigned NOT NULL default '0',
 166     picture mediumblob NOT NULL,
 167     format varchar(10) NOT NULL default '',
 168     PRIMARY KEY (id_adh)
 169 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 170
 171 -- Table for dynamic translation of strings;
 172 DROP TABLE IF EXISTS galette_l10n;
 173 CREATE TABLE galette_l10n (
 174     text_orig varchar(255) NOT NULL,
 175     text_locale varchar(15) NOT NULL,
 176     text_nref int(10) NOT NULL default '1',
 177     text_trans varchar(255) NOT NULL default '',
 178     PRIMARY KEY (text_orig, text_locale)
 179 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 180
 181 -- new table for temporary passwords 2006-02-18;
 182 DROP TABLE IF EXISTS galette_tmppasswds;
 183 CREATE TABLE galette_tmppasswds (
 184     id_adh int(10) unsigned NOT NULL,
 185     tmp_passwd varchar(250) NOT NULL,
 186     date_crea_tmp_passwd datetime NOT NULL,
 187     PRIMARY KEY (id_adh),
 188     FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 189 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 190
 191 -- Add new table for automatic mails and their translations;
 192 DROP TABLE IF EXISTS galette_texts;
 193 CREATE TABLE galette_texts (
 194   tid smallint(6) NOT NULL auto_increment,
 195   tref varchar(20) NOT NULL,
 196   tsubject varchar(256) NOT NULL,
 197   tbody text NOT NULL,
 198   tlang varchar(16) NOT NULL,
 199   tcomment varchar(255) NOT NULL,
 200   PRIMARY KEY (tid),
 201   UNIQUE KEY `localizedtxt` (tref, tlang)
 202 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 203
 204 DROP TABLE IF EXISTS galette_fields_categories;
 205 CREATE TABLE galette_fields_categories (
 206   id_field_category int(2) NOT NULL AUTO_INCREMENT,
 207   table_name varchar(30) NOT NULL,
 208   category varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
 209   position int(2) NOT NULL,
 210   PRIMARY KEY (id_field_category)
 211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 212
 213 DROP TABLE IF EXISTS galette_fields_config;
 214 CREATE TABLE galette_fields_config (
 215   table_name varchar(30) COLLATE utf8mb4_unicode_520_ci NOT NULL,
 216   field_id varchar(30) COLLATE utf8mb4_unicode_520_ci NOT NULL,
 217   required tinyint(1) NOT NULL,
 218   visible tinyint(1) NOT NULL,
 219   position int(2) NOT NULL,
 220   id_field_category int(2) NOT NULL,
 221   list_visible tinyint(1) NOT NULL,
 222   list_position int(2) NOT NULL,
 223   width_in_forms tinyint(1) NOT NULL DEFAULT 1,
 224   PRIMARY KEY (table_name, field_id),
 225   FOREIGN KEY (id_field_category) REFERENCES galette_fields_categories (id_field_category) ON DELETE RESTRICT ON UPDATE CASCADE
 226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 227
 228 -- Table for mailing history storage;
 229 DROP TABLE IF EXISTS galette_mailing_history;
 230 CREATE TABLE galette_mailing_history (
 231   mailing_id smallint(6) NOT NULL auto_increment,
 232   mailing_sender int(10) unsigned,
 233   mailing_subject varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
 234   mailing_body text NOT NULL,
 235   mailing_date datetime NOT NULL,
 236   mailing_recipients text NOT NULL,
 237   mailing_sent tinyint(1) NOT NULL,
 238   mailing_sender_name varchar(255) DEFAULT NULL,
 239   mailing_sender_address varchar(255) DEFAULT NULL,
 240   PRIMARY KEY (mailing_id),
 241   FOREIGN KEY (mailing_sender) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
 242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 243
 244 -- table for groups
 245 DROP TABLE IF EXISTS galette_groups;
 246 CREATE TABLE galette_groups (
 247   id_group int(10) NOT NULL AUTO_INCREMENT,
 248   group_name varchar(250) COLLATE utf8mb4_unicode_520_ci NOT NULL,
 249   creation_date datetime NOT NULL,
 250   parent_group int(10) DEFAULT NULL,
 251   PRIMARY KEY (id_group),
 252   FOREIGN KEY (parent_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 254
 255 -- table for groups managers
 256 DROP TABLE IF EXISTS galette_groups_managers;
 257 CREATE TABLE galette_groups_managers (
 258   id_group int(10) NOT NULL,
 259   id_adh int(10) unsigned NOT NULL,
 260   PRIMARY KEY (id_group,id_adh),
 261   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 262   FOREIGN KEY (id_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 264
 265 -- table for groups member
 266 DROP TABLE IF EXISTS galette_groups_members;
 267 CREATE TABLE galette_groups_members (
 268   id_group int(10) NOT NULL,
 269   id_adh int(10) unsigned NOT NULL,
 270   PRIMARY KEY (id_group,id_adh),
 271   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 272   FOREIGN KEY (id_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 274
 275 -- Table for reminders
 276 DROP TABLE IF EXISTS galette_reminders;
 277 CREATE TABLE galette_reminders (
 278   reminder_id smallint(6) NOT NULL auto_increment,
 279   reminder_type int(10) NOT NULL,
 280   reminder_dest int(10) unsigned,
 281   reminder_date datetime NOT NULL,
 282   reminder_success tinyint(1) NOT NULL DEFAULT 0,
 283   reminder_nomail tinyint(1) NOT NULL DEFAULT 1,
 284   reminder_comment text,
 285   PRIMARY KEY (reminder_id),
 286   FOREIGN KEY (reminder_dest) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 288
 289 -- Table for PDF models
 290 DROP TABLE IF EXISTS galette_pdfmodels;
 291 CREATE TABLE galette_pdfmodels (
 292   model_id int(10) unsigned NOT NULL auto_increment,
 293   model_name varchar(50) NOT NULL,
 294   model_type tinyint(2) NOT NULL,
 295   model_header text,
 296   model_footer text,
 297   model_body text,
 298   model_styles text,
 299   model_title varchar(250),
 300   model_subtitle varchar(250),
 301   model_parent int(10) unsigned DEFAULT NULL REFERENCES galette_pdfmodels (model_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 302   PRIMARY KEY (model_id)
 303 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 304
 305 -- Table for import models
 306 DROP TABLE IF EXISTS galette_import_model;
 307 CREATE TABLE galette_import_model (
 308   model_id smallint(6) NOT NULL auto_increment,
 309   model_fields text,
 310   model_creation_date datetime NOT NULL,
 311   PRIMARY KEY (model_id)
 312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 313
 314 -- Table for payment types
 315 DROP TABLE IF EXISTS galette_paymenttypes;
 316 CREATE TABLE galette_paymenttypes (
 317   type_id int(10) unsigned NOT NULL auto_increment,
 318   type_name varchar(255) NOT NULL,
 319   PRIMARY KEY (type_id)
 320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 321
 322 -- table for saved searches
 323 DROP TABLE IF EXISTS galette_searches;
 324 CREATE TABLE galette_searches (
 325   search_id int(10) unsigned NOT NULL auto_increment,
 326   name varchar(100) DEFAULT NULL,
 327   form varchar(50) NOT NULL,
 328   parameters text NOT NULL,
 329   id_adh int(10) unsigned,
 330   creation_date datetime NOT NULL,
 331   PRIMARY KEY (search_id),
 332   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 334
 335 -- new table for temporary links
 336 DROP TABLE IF EXISTS galette_tmplinks;
 337 CREATE TABLE galette_tmplinks (
 338   hash varchar(250) NOT NULL,
 339   target smallint(1) NOT NULL,
 340   id int(10) unsigned,
 341   creation_date datetime NOT NULL,
 342   PRIMARY KEY (target, id)
 343 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 344
 345 -- table for social networks
 346 DROP TABLE IF EXISTS galette_socials;
 347 CREATE TABLE galette_socials (
 348   id_social int(10) unsigned NOT NULL auto_increment,
 349   id_adh int(10) unsigned NULL,
 350   type varchar(250) NOT NULL,
 351   url varchar(255) DEFAULT NULL,
 352   PRIMARY KEY (id_social),
 353   KEY (type),
 354   FOREIGN KEY (id_adh) REFERENCES  galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 355 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 356
 357 -- table for documents
 358 DROP TABLE IF EXISTS galette_documents;
 359 CREATE TABLE galette_documents (
 360   id_document int(10) unsigned NOT NULL auto_increment,
 361   type varchar(250) NOT NULL,
 362   visible tinyint(1) NOT NULL,
 363   filename varchar(255) DEFAULT NULL,
 364   comment text,
 365   creation_date datetime NOT NULL,
 366   PRIMARY KEY (id_document),
 367   KEY (type)
 368 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
 369
 370 -- table for payments schedules
 371 DROP TABLE IF EXISTS galette_payments_schedules;
 372 CREATE TABLE galette_payments_schedules (
 373   id_schedule int(10) unsigned NOT NULL auto_increment,
 374   id_cotis int(10) unsigned NOT NULL,
 375   id_paymenttype int(10) unsigned NOT NULL,
 376   creation_date datetime NOT NULL,
 377   scheduled_date datetime NOT NULL,
 378   amount decimal(15, 2) NOT NULL,
 379   paid tinyint(1) DEFAULT FALSE,
 380   comment text,
 381   PRIMARY KEY (id_schedule),
 382   FOREIGN KEY (id_cotis) REFERENCES galette_cotisations (id_cotis) ON DELETE CASCADE ON UPDATE CASCADE,
 383   FOREIGN KEY (id_paymenttype) REFERENCES galette_paymenttypes (type_id) ON DELETE CASCADE ON UPDATE CASCADE
 384 );
 385
 386 -- table for database version
 387 DROP TABLE IF EXISTS galette_database;
 388 CREATE TABLE galette_database (
 389   version DECIMAL(4,3) NOT NULL
 390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 391 INSERT INTO galette_database(version) VALUES(1.10);
 392
 393 SET FOREIGN_KEY_CHECKS=1;