2 DROP SEQUENCE IF EXISTS galette_adherents_id_seq
;
3 CREATE SEQUENCE galette_adherents_id_seq
10 DROP SEQUENCE IF EXISTS galette_cotisations_id_seq
;
11 CREATE SEQUENCE galette_cotisations_id_seq
18 -- sequence for statuts
19 DROP SEQUENCE IF EXISTS galette_statuts_id_seq
;
20 CREATE SEQUENCE galette_statuts_id_seq
27 DROP SEQUENCE IF EXISTS galette_transactions_id_seq
;
28 CREATE SEQUENCE galette_transactions_id_seq
35 DROP SEQUENCE IF EXISTS galette_preferences_id_seq
;
36 CREATE SEQUENCE galette_preferences_id_seq
43 DROP SEQUENCE IF EXISTS galette_logs_id_seq
;
44 CREATE SEQUENCE galette_logs_id_seq
51 -- Sequence for dynamic fields description;
52 DROP SEQUENCE IF EXISTS galette_field_types_id_seq
;
53 CREATE SEQUENCE galette_field_types_id_seq
60 -- sequence for contributions types
61 DROP SEQUENCE IF EXISTS galette_types_cotisation_id_seq
;
62 CREATE SEQUENCE galette_types_cotisation_id_seq
69 -- sequence for groups
70 DROP SEQUENCE IF EXISTS galette_groups_id_seq
;
71 CREATE SEQUENCE galette_groups_id_seq
78 -- sequence for mailing history
79 DROP SEQUENCE IF EXISTS galette_mailing_history_id_seq
;
80 CREATE SEQUENCE galette_mailing_history_id_seq
88 DROP SEQUENCE IF EXISTS galette_titles_id_seq
;
89 CREATE SEQUENCE galette_titles_id_seq
96 -- sequence for reminders
97 DROP SEQUENCE IF EXISTS galette_reminders_id_seq
;
98 CREATE SEQUENCE galette_reminders_id_seq
105 -- sequence for pdf models
106 DROP SEQUENCE IF EXISTS galette_pdfmodels_id_seq
;
107 CREATE SEQUENCE galette_pdfmodels_id_seq
114 -- sequence for import model
115 DROP SEQUENCE IF EXISTS galette_import_model_id_seq
;
116 CREATE SEQUENCE galette_import_model_id_seq
123 -- sequence for payment types
124 DROP SEQUENCE IF EXISTS galette_paymenttypes_id_seq
;
125 CREATE SEQUENCE galette_paymenttypes_id_seq
132 -- sequence for searches
133 DROP SEQUENCE IF EXISTS galette_searches_id_seq
;
134 CREATE SEQUENCE galette_searches_id_seq
141 -- sequence for texts
142 DROP SEQUENCE IF EXISTS galette_texts_id_seq
;
143 CREATE SEQUENCE galette_texts_id_seq
150 -- sequence for fields categories
151 DROP SEQUENCE IF EXISTS galette_fields_categories_id_seq
;
152 CREATE SEQUENCE galette_fields_categories_id_seq
159 -- sequence for socials
160 DROP SEQUENCE IF EXISTS galette_socials_id_seq
;
161 CREATE SEQUENCE galette_socials_id_seq
169 -- REMINDER: Create order IS important, dependencies first !!
170 DROP TABLE IF EXISTS galette_paymenttypes
;
171 CREATE TABLE galette_paymenttypes (
172 type_id
integer DEFAULT nextval('galette_paymenttypes_id_seq'::text) NOT NULL,
173 type_name
character varying(50) NOT NULL,
174 PRIMARY KEY (type_id
)
177 DROP TABLE IF EXISTS galette_statuts
CASCADE;
178 CREATE TABLE galette_statuts (
179 id_statut
integer DEFAULT nextval('galette_statuts_id_seq'::text) NOT NULL,
180 libelle_statut
character varying(255) DEFAULT '' NOT NULL,
181 priorite_statut
smallint DEFAULT '0' NOT NULL,
182 PRIMARY KEY (id_statut
)
185 DROP TABLE IF EXISTS galette_titles
CASCADE;
186 CREATE TABLE galette_titles (
187 id_title
integer DEFAULT nextval('galette_titles_id_seq'::text) NOT NULL,
188 short_label
character varying(10) DEFAULT '' NOT NULL,
189 long_label
character varying(100) DEFAULT '',
190 PRIMARY KEY (id_title
)
193 DROP TABLE IF EXISTS galette_adherents
CASCADE;
194 CREATE TABLE galette_adherents (
195 id_adh
integer DEFAULT nextval('galette_adherents_id_seq'::text) NOT NULL,
196 id_statut
integer DEFAULT '4' REFERENCES galette_statuts(id_statut
) ON DELETE RESTRICT ON UPDATE CASCADE,
197 nom_adh
character varying(255) DEFAULT '' NOT NULL,
198 prenom_adh
character varying(255) DEFAULT '' NOT NULL,
199 societe_adh
character varying(200) DEFAULT NULL,
200 pseudo_adh
character varying(255) DEFAULT '' NOT NULL,
201 titre_adh
integer DEFAULT NULL REFERENCES galette_titles(id_title
) ON DELETE RESTRICT ON UPDATE CASCADE,
202 ddn_adh
date DEFAULT '19010101',
203 sexe_adh
smallint DEFAULT '0' NOT NULL,
204 adresse_adh
text DEFAULT '' NOT NULL,
205 adresse2_adh
character varying(150) DEFAULT NULL, -- TODO: remove
206 cp_adh
character varying(10) DEFAULT '' NOT NULL,
207 ville_adh
character varying(200) DEFAULT '' NOT NULL,
208 pays_adh
character varying(200) DEFAULT NULL,
209 tel_adh
character varying(50),
210 gsm_adh
character varying(50),
211 email_adh
character varying(255),
213 info_public_adh
text,
214 prof_adh
character varying(150),
215 login_adh
character varying(255) DEFAULT '' NOT NULL,
216 mdp_adh
character varying(255) DEFAULT '' NOT NULL,
217 date_crea_adh
date DEFAULT '19010101' NOT NULL,
218 date_modif_adh
date DEFAULT '19010101' NOT NULL,
219 activite_adh
boolean DEFAULT FALSE,
220 bool_admin_adh
boolean DEFAULT FALSE,
221 bool_exempt_adh
boolean DEFAULT FALSE,
222 bool_display_info
boolean DEFAULT FALSE,
224 pref_lang
character varying(20) DEFAULT 'fr_FR',
225 lieu_naissance
text DEFAULT '',
226 gpgid
text DEFAULT NULL,
227 fingerprint
character varying(255) DEFAULT NULL,
228 parent_id
integer DEFAULT NULL REFERENCES galette_adherents(id_adh
) ON DELETE RESTRICT ON UPDATE CASCADE,
229 num_adh
character varying(255) DEFAULT NULL,
232 -- add index for faster search on login_adh (auth)
233 CREATE UNIQUE INDEX galette_adherents_login_adh_idx
ON galette_adherents (login_adh
);
235 DROP TABLE IF EXISTS galette_types_cotisation
CASCADE;
236 CREATE TABLE galette_types_cotisation (
237 id_type_cotis
integer DEFAULT nextval('galette_types_cotisation_id_seq'::text) NOT NULL,
238 libelle_type_cotis
character varying(255) DEFAULT '' NOT NULL,
239 cotis_extension
boolean DEFAULT FALSE,
240 PRIMARY KEY (id_type_cotis
)
243 DROP TABLE IF EXISTS galette_transactions
CASCADE;
244 CREATE TABLE galette_transactions (
245 trans_id
integer DEFAULT nextval('galette_transactions_id_seq'::text) NOT NULL,
246 trans_date
date DEFAULT '19010101' NOT NULL,
247 trans_amount
real DEFAULT '0',
248 trans_desc
character varying(255) NOT NULL DEFAULT '',
249 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE RESTRICT ON UPDATE CASCADE,
250 PRIMARY KEY (trans_id
)
253 DROP TABLE IF EXISTS galette_cotisations
;
254 CREATE TABLE galette_cotisations (
255 id_cotis
integer DEFAULT nextval('galette_cotisations_id_seq'::text) NOT NULL,
256 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE RESTRICT ON UPDATE CASCADE,
257 id_type_cotis
integer REFERENCES galette_types_cotisation (id_type_cotis
) ON DELETE RESTRICT ON UPDATE CASCADE,
258 montant_cotis
real DEFAULT '0',
259 type_paiement_cotis
integer REFERENCES galette_paymenttypes (type_id
) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL,
261 date_enreg
date DEFAULT '19010101' NOT NULL,
262 date_debut_cotis
date DEFAULT '19010101' NOT NULL,
263 date_fin_cotis
date DEFAULT '19010101' NOT NULL,
264 trans_id
integer DEFAULT NULL REFERENCES galette_transactions (trans_id
) ON DELETE RESTRICT ON UPDATE CASCADE,
265 PRIMARY KEY (id_cotis
)
268 DROP TABLE IF EXISTS galette_preferences
;
269 CREATE TABLE galette_preferences (
270 id_pref
integer DEFAULT nextval('galette_preferences_id_seq'::text) NOT NULL,
271 nom_pref
character varying(100) DEFAULT '' NOT NULL,
272 val_pref
character varying(255) DEFAULT '' NOT NULL,
273 PRIMARY KEY (id_pref
)
275 -- add index, nom_pref is used as foreign key elsewhere
276 CREATE UNIQUE INDEX galette_preferences_nom_pref_idx
ON galette_preferences (nom_pref
);
278 DROP TABLE IF EXISTS galette_logs
;
279 CREATE TABLE galette_logs (
280 id_log
integer DEFAULT nextval('galette_logs_id_seq'::text) NOT NULL,
281 date_log
timestamp NOT NULL,
282 ip_log
character varying(46) DEFAULT '' NOT NULL,
283 adh_log
character varying(255) DEFAULT '' NOT NULL, -- see galette_adherents.login_adh
290 -- Table for dynamic fields description;
291 DROP TABLE IF EXISTS galette_field_types
CASCADE;
292 CREATE TABLE galette_field_types (
293 field_id
integer DEFAULT nextval('galette_field_types_id_seq'::text) NOT NULL,
294 field_form
character varying(10) NOT NULL,
295 field_index
integer DEFAULT '0' NOT NULL,
296 field_name
character varying(255) DEFAULT '' NOT NULL,
297 field_perm
integer DEFAULT '0' NOT NULL,
298 field_type
integer DEFAULT '0' NOT NULL,
299 field_required
boolean DEFAULT FALSE,
300 field_pos
integer DEFAULT '0' NOT NULL,
301 field_width
integer DEFAULT NULL,
302 field_height
integer DEFAULT NULL,
303 field_size
integer DEFAULT NULL,
304 field_repeat
integer DEFAULT NULL,
305 field_layout
integer DEFAULT NULL,
306 PRIMARY KEY (field_id
)
308 -- add index, field_form is used elsewhere
309 CREATE INDEX galette_field_types_field_form_idx
ON galette_field_types (field_form
);
311 -- Table for dynamic fields data;
312 DROP TABLE IF EXISTS galette_dynamic_fields
;
313 CREATE TABLE galette_dynamic_fields (
314 item_id
integer DEFAULT '0' NOT NULL, -- could be id_adh, trans_id, id_cotis
315 field_id
integer REFERENCES galette_field_types (field_id
) ON DELETE RESTRICT ON UPDATE CASCADE,
316 field_form
character varying(10) NOT NULL, -- not an fkey!
317 val_index
integer DEFAULT '0' NOT NULL,
318 field_val
text DEFAULT '',
319 PRIMARY KEY (item_id
, field_id
, field_form
, val_index
)
322 DROP TABLE IF EXISTS galette_pictures
;
323 CREATE TABLE galette_pictures (
324 id_adh
integer DEFAULT '0' NOT NULL,
325 picture bytea
NOT NULL,
326 format
character varying(30) DEFAULT '' NOT NULL,
330 -- Table for dynamic translation of strings;
331 DROP TABLE IF EXISTS galette_l10n
;
332 CREATE TABLE galette_l10n (
333 text_orig
character varying(255) NOT NULL,
334 text_locale
character varying(15) NOT NULL,
335 text_nref
integer DEFAULT '1' NOT NULL,
336 text_trans
character varying(255) DEFAULT '' NOT NULL,
337 PRIMARY KEY (text_orig
, text_locale
)
340 -- new table for temporary passwords 2006-02-18;
341 DROP TABLE IF EXISTS galette_tmppasswds
;
342 CREATE TABLE galette_tmppasswds (
343 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE CASCADE ON UPDATE CASCADE,
344 tmp_passwd
character varying(250) NOT NULL,
345 date_crea_tmp_passwd
timestamp NOT NULL,
349 -- Table for automatic mails and their translations 2007-10-22;
350 DROP TABLE IF EXISTS galette_texts
;
351 CREATE TABLE galette_texts (
352 tid
integer DEFAULT nextval('galette_texts_id_seq'::text) NOT NULL,
353 tref
character varying(20) NOT NULL,
354 tsubject
character varying(256) NOT NULL,
356 tlang
character varying(16) NOT NULL,
357 tcomment
character varying(255) NOT NULL,
360 CREATE UNIQUE INDEX galette_texts_localizedtxt_idx
ON galette_texts (tref
, tlang
);
362 DROP TABLE IF EXISTS galette_fields_categories
CASCADE;
363 CREATE TABLE galette_fields_categories (
364 id_field_category
integer DEFAULT nextval('galette_fields_categories_id_seq'::text) NOT NULL,
365 table_name character varying(30) NOT NULL,
366 category
character varying(100) NOT NULL,
367 position integer NOT NULL,
368 PRIMARY KEY (id_field_category
)
371 DROP TABLE IF EXISTS galette_fields_config
;
372 CREATE TABLE galette_fields_config (
373 table_name character varying(30) NOT NULL,
374 field_id
character varying(30) NOT NULL,
375 required
boolean NOT NULL,
376 visible
integer NOT NULL,
377 position integer NOT NULL,
378 list_visible
boolean NOT NULL,
379 list_position
integer NOT NULL,
380 id_field_category
integer REFERENCES galette_fields_categories
ON DELETE RESTRICT ON UPDATE CASCADE,
381 PRIMARY KEY (table_name, field_id
)
384 -- Table for mailing history storage
385 DROP TABLE IF EXISTS galette_mailing_history
;
386 CREATE TABLE galette_mailing_history (
387 mailing_id
integer DEFAULT nextval('galette_mailing_history_id_seq'::text) NOT NULL,
388 mailing_sender
integer REFERENCES galette_adherents (id_adh
) ON DELETE RESTRICT ON UPDATE CASCADE,
389 mailing_subject
character varying(255) NOT NULL,
390 mailing_body
text NOT NULL,
391 mailing_date
timestamp NOT NULL,
392 mailing_recipients
text NOT NULL,
393 mailing_sent
boolean DEFAULT FALSE,
394 mailing_sender_name
character varying(255) DEFAULT NULL,
395 mailing_sender_address
character varying(255) DEFAULT NULL,
396 PRIMARY KEY (mailing_id
)
400 DROP TABLE IF EXISTS galette_groups
CASCADE;
401 CREATE TABLE galette_groups (
402 id_group
integer DEFAULT nextval('galette_groups_id_seq'::text) NOT NULL,
403 group_name
character varying(250) NOT NULL CONSTRAINT name UNIQUE,
404 creation_date
timestamp NOT NULL,
405 parent_group
integer DEFAULT NULL REFERENCES galette_groups(id_group
) ON DELETE RESTRICT ON UPDATE CASCADE,
406 PRIMARY KEY (id_group
)
409 -- table for groups managers
410 DROP TABLE IF EXISTS galette_groups_managers
CASCADE;
411 CREATE TABLE galette_groups_managers (
412 id_group
integer REFERENCES galette_groups(id_group
) ON DELETE RESTRICT ON UPDATE CASCADE,
413 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE RESTRICT ON UPDATE CASCADE,
414 PRIMARY KEY (id_group
,id_adh
)
417 -- table for groups members
418 DROP TABLE IF EXISTS galette_groups_members
CASCADE;
419 CREATE TABLE galette_groups_members (
420 id_group
integer REFERENCES galette_groups(id_group
) ON DELETE RESTRICT ON UPDATE CASCADE,
421 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE RESTRICT ON UPDATE CASCADE,
422 PRIMARY KEY (id_group
,id_adh
)
425 -- Table for reminders
426 DROP TABLE IF EXISTS galette_reminders
;
427 CREATE TABLE galette_reminders (
428 reminder_id
integer DEFAULT nextval('galette_reminders_id_seq'::text) NOT NULL,
429 reminder_type
integer NOT NULL,
430 reminder_dest
integer REFERENCES galette_adherents (id_adh
) ON DELETE CASCADE ON UPDATE CASCADE,
431 reminder_date
timestamp NOT NULL,
432 reminder_success
boolean DEFAULT FALSE,
433 reminder_nomail
boolean DEFAULT TRUE,
434 reminder_comment
text,
435 PRIMARY KEY (reminder_id
)
438 DROP TABLE IF EXISTS galette_pdfmodels
CASCADE;
439 CREATE TABLE galette_pdfmodels (
440 model_id
integer DEFAULT nextval('galette_pdfmodels_id_seq'::text) NOT NULL,
441 model_name
character varying(50) NOT NULL,
442 model_type
integer NOT NULL,
447 model_title
character varying(250),
448 model_subtitle
character varying(250),
449 model_parent
integer DEFAULT NULL REFERENCES galette_pdfmodels (model_id
) ON DELETE RESTRICT ON UPDATE CASCADE,
450 PRIMARY KEY (model_id
)
453 -- Table for import models
454 DROP TABLE IF EXISTS galette_import_model
;
455 CREATE TABLE galette_import_model (
456 model_id
integer DEFAULT nextval('galette_import_model_id_seq'::text) NOT NULL,
458 model_creation_date
timestamp NOT NULL,
459 PRIMARY KEY (model_id
)
462 -- Table for saved searches
463 DROP TABLE IF EXISTS galette_searches
;
464 CREATE TABLE galette_searches (
465 search_id
integer DEFAULT nextval('galette_searches_id_seq'::text) NOT NULL,
466 name character varying(100) DEFAULT NULL,
467 form
character varying(50) NOT NULL,
468 parameters jsonb
NOT NULL,
469 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE CASCADE ON UPDATE CASCADE,
470 creation_date
timestamp NOT NULL,
471 PRIMARY KEY (search_id
)
474 -- new table for temporary links
475 DROP TABLE IF EXISTS galette_tmplinks
;
476 CREATE TABLE galette_tmplinks (
477 hash
character varying(250) NOT NULL,
478 target
smallint NOT NULL,
480 creation_date
timestamp NOT NULL,
481 PRIMARY KEY (target
, id)
484 -- table for social networks
485 DROP TABLE IF EXISTS galette_socials
;
486 CREATE TABLE galette_socials (
487 id_social
integer DEFAULT nextval('galette_socials_id_seq'::text) NOT NULL,
488 id_adh
integer REFERENCES galette_adherents (id_adh
) ON DELETE CASCADE ON UPDATE CASCADE,
489 type character varying(250) NOT NULL,
490 url
character varying(255) DEFAULT NULL,
491 PRIMARY KEY (id_social
)
493 -- add index on table to look for type
494 CREATE INDEX galette_socials_idx
ON galette_socials (type);
496 -- table for database version
497 DROP TABLE IF EXISTS galette_database
;
498 CREATE TABLE galette_database (
499 version decimal NOT NULL
501 INSERT INTO galette_database (version) VALUES(0.96);