1 -- CREATE DATABASE `galette` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
4 SET FOREIGN_KEY_CHECKS
=0;
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 url_adh
varchar(255) default NULL,
26 icq_adh
varchar(20) default NULL, -- TODO: remove
27 msn_adh
varchar(150) default NULL, -- TODO: remove
28 jabber_adh
varchar(150) default NULL,
31 prof_adh
varchar(150) default NULL,
32 login_adh
varchar(200) NOT NULL default '',
33 mdp_adh
varchar(255) 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',
43 gpgid
text DEFAULT NULL,
44 fingerprint
varchar(255) DEFAULT NULL,
45 parent_id
int(10) unsigned
DEFAULT NULL,
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
;
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,
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
;
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(255) 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
;
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(255) 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
;
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(100) default '',
96 PRIMARY KEY (id_title
)
97 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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(255) 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
;
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
),
114 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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
126 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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 '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
),
146 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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',
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
;
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 '',
166 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
168 -- Table for dynamic translation of strings;
169 DROP TABLE IF EXISTS galette_l10n
;
170 CREATE TABLE galette_l10n (
171 text_orig
varchar(255) NOT NULL,
172 text_locale
varchar(15) NOT NULL,
173 text_nref
int(10) NOT NULL default '1',
174 text_trans
varchar(255) NOT NULL default '',
175 PRIMARY KEY (text_orig
, text_locale
)
176 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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(250) 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
;
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,
195 tlang
varchar(16) NOT NULL,
196 tcomment
varchar(255) NOT NULL,
198 UNIQUE KEY `localizedtxt`
(tref
, tlang
)
199 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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(100) 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
;
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
;
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(255) 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
;
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(250) 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
;
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
;
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
;
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
;
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,
296 model_title
varchar(250),
297 model_subtitle
varchar(250),
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
;
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
,
307 model_creation_date datetime
NOT NULL,
308 PRIMARY KEY (model_id
)
309 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COLLATE=utf8_unicode_ci
;
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
;
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
;
334 -- new table for temporary links
335 DROP TABLE IF EXISTS galette_tmplinks
;
336 CREATE TABLE galette_tmplinks (
337 hash
varchar(250) NOT NULL,
338 target
smallint(1) NOT NULL,
340 creation_date datetime
NOT NULL,
341 PRIMARY KEY (target
, id)
342 ) ENGINE
=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;
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.95);
351 SET FOREIGN_KEY_CHECKS
=1;