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   UNIQUE KEY `localizedtxt` (tref, tlang)
 199 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 200
 201 DROP TABLE IF EXISTS galette_fields_categories;
 202 CREATE TABLE galette_fields_categories (
 203   id_field_category int(2) NOT NULL AUTO_INCREMENT,
 204   table_name varchar(30) NOT NULL,
 205   category varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 206   position int(2) NOT NULL,
 207   PRIMARY KEY (id_field_category)
 208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 209
 210 DROP TABLE IF EXISTS galette_fields_config;
 211 CREATE TABLE galette_fields_config (
 212   table_name varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 213   field_id varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 214   required tinyint(1) NOT NULL,
 215   visible tinyint(1) NOT NULL,
 216   position int(2) NOT NULL,
 217   id_field_category int(2) NOT NULL,
 218   list_visible tinyint(1) NOT NULL,
 219   list_position int(2) NOT NULL,
 220   PRIMARY KEY (table_name, field_id),
 221   FOREIGN KEY (id_field_category) REFERENCES galette_fields_categories (id_field_category) ON DELETE RESTRICT ON UPDATE CASCADE
 222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 223
 224 -- Table for mailing history storage;
 225 DROP TABLE IF EXISTS galette_mailing_history;
 226 CREATE TABLE galette_mailing_history (
 227   mailing_id smallint(6) NOT NULL auto_increment,
 228   mailing_sender int(10) unsigned,
 229   mailing_subject varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 230   mailing_body text NOT NULL,
 231   mailing_date datetime NOT NULL,
 232   mailing_recipients text NOT NULL,
 233   mailing_sent tinyint(1) NOT NULL,
 234   mailing_sender_name varchar(100) DEFAULT NULL,
 235   mailing_sender_address varchar(255) DEFAULT NULL,
 236   PRIMARY KEY (mailing_id),
 237   FOREIGN KEY (mailing_sender) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE
 238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 239
 240 -- table for groups
 241 DROP TABLE IF EXISTS galette_groups;
 242 CREATE TABLE galette_groups (
 243   id_group int(10) NOT NULL AUTO_INCREMENT,
 244   group_name varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 245   creation_date datetime NOT NULL,
 246   parent_group int(10) DEFAULT NULL,
 247   PRIMARY KEY (id_group),
 248   UNIQUE KEY `name` (group_name),
 249   FOREIGN KEY (parent_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 251
 252 -- table for groups managers
 253 DROP TABLE IF EXISTS galette_groups_managers;
 254 CREATE TABLE galette_groups_managers (
 255   id_group int(10) NOT NULL,
 256   id_adh int(10) unsigned NOT NULL,
 257   PRIMARY KEY (id_group,id_adh),
 258   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 259   FOREIGN KEY (id_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 261
 262 -- table for groups member
 263 DROP TABLE IF EXISTS galette_groups_members;
 264 CREATE TABLE galette_groups_members (
 265   id_group int(10) NOT NULL,
 266   id_adh int(10) unsigned NOT NULL,
 267   PRIMARY KEY (id_group,id_adh),
 268   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 269   FOREIGN KEY (id_group) REFERENCES galette_groups (id_group) ON DELETE RESTRICT ON UPDATE CASCADE
 270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 271
 272 -- Table for reminders
 273 DROP TABLE IF EXISTS galette_reminders;
 274 CREATE TABLE galette_reminders (
 275   reminder_id smallint(6) NOT NULL auto_increment,
 276   reminder_type int(10) NOT NULL,
 277   reminder_dest int(10) unsigned,
 278   reminder_date datetime NOT NULL,
 279   reminder_success tinyint(1) NOT NULL DEFAULT 0,
 280   reminder_nomail tinyint(1) NOT NULL DEFAULT 1,
 281   reminder_comment text,
 282   PRIMARY KEY (reminder_id),
 283   FOREIGN KEY (reminder_dest) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 285
 286 -- Table for PDF models
 287 DROP TABLE IF EXISTS galette_pdfmodels;
 288 CREATE TABLE galette_pdfmodels (
 289   model_id int(10) unsigned NOT NULL auto_increment,
 290   model_name varchar(50) NOT NULL,
 291   model_type tinyint(2) NOT NULL,
 292   model_header text,
 293   model_footer text,
 294   model_body text,
 295   model_styles text,
 296   model_title varchar(100),
 297   model_subtitle varchar(100),
 298   model_parent int(10) unsigned DEFAULT NULL REFERENCES galette_pdfmodels (model_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 299   PRIMARY KEY (model_id)
 300 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 301
 302 -- Table for import models
 303 DROP TABLE IF EXISTS galette_import_model;
 304 CREATE TABLE galette_import_model (
 305   model_id smallint(6) NOT NULL auto_increment,
 306   model_fields text,
 307   model_creation_date datetime NOT NULL,
 308   PRIMARY KEY (model_id)
 309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 310
 311 -- Table for payment types
 312 DROP TABLE IF EXISTS galette_paymenttypes;
 313 CREATE TABLE galette_paymenttypes (
 314   type_id int(10) unsigned NOT NULL auto_increment,
 315   type_name varchar(255) NOT NULL,
 316   PRIMARY KEY (type_id)
 317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 318
 319 -- table for saved searches
 320 DROP TABLE IF EXISTS galette_searches;
 321 CREATE TABLE galette_searches (
 322   search_id int(10) unsigned NOT NULL auto_increment,
 323   name varchar(100) DEFAULT NULL,
 324   form varchar(50) NOT NULL,
 325   parameters text NOT NULL,
 326   parameters_sum binary(20),
 327   id_adh int(10) unsigned,
 328   creation_date datetime NOT NULL,
 329   PRIMARY KEY (search_id),
 330   KEY (form, parameters_sum, id_adh),
 331   FOREIGN KEY (id_adh) REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE
 332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 333
 334 -- new table for temporary links
 335 DROP TABLE IF EXISTS galette_tmplinks;
 336 CREATE TABLE galette_tmplinks (
 337   hash varchar(60) NOT NULL,
 338   target smallint(1) NOT NULL,
 339   id int(10) unsigned,
 340   creation_date datetime NOT NULL,
 341   PRIMARY KEY (target, id)
 342 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 343
 344 -- table for database version
 345 DROP TABLE IF EXISTS galette_database;
 346 CREATE TABLE galette_database (
 347   version DECIMAL(4,3) NOT NULL
 348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 349 INSERT INTO galette_database(version) VALUES(0.94);
 350
 351 SET FOREIGN_KEY_CHECKS=1;