galette/install/scripts/pgsql.sql

   1 -- $Id$
   2 DROP SEQUENCE IF EXISTS galette_adherents_id_seq;
   3 CREATE SEQUENCE galette_adherents_id_seq
   4     START 1
   5     INCREMENT 1
   6     MAXVALUE 2147483647
   7     MINVALUE 1
   8     CACHE 1;
   9
  10 DROP SEQUENCE IF EXISTS galette_cotisations_id_seq;
  11 CREATE SEQUENCE galette_cotisations_id_seq
  12     START 1
  13     INCREMENT 1
  14     MAXVALUE 2147483647
  15     MINVALUE 1
  16     CACHE 1;
  17
  18 -- sequence for statuts
  19 DROP SEQUENCE IF EXISTS galette_statuts_id_seq;
  20 CREATE SEQUENCE galette_statuts_id_seq
  21     START 1
  22     INCREMENT 1
  23     MAXVALUE 2147483647
  24     MINVALUE 1
  25     CACHE 1;
  26
  27 DROP SEQUENCE IF EXISTS galette_transactions_id_seq;
  28 CREATE SEQUENCE galette_transactions_id_seq
  29     START 1
  30     INCREMENT 1
  31     MAXVALUE 2147483647
  32     MINVALUE 1
  33     CACHE 1;
  34
  35 DROP SEQUENCE IF EXISTS galette_preferences_id_seq;
  36 CREATE SEQUENCE galette_preferences_id_seq
  37     START 1
  38     INCREMENT 1
  39     MAXVALUE 2147483647
  40     MINVALUE 1
  41     CACHE 1;
  42
  43 DROP SEQUENCE IF EXISTS galette_logs_id_seq;
  44 CREATE SEQUENCE galette_logs_id_seq
  45     START 1
  46     INCREMENT 1
  47     MAXVALUE 2147483647
  48     MINVALUE 1
  49     CACHE 1;
  50
  51 -- Sequence for dynamic fields description;
  52 DROP SEQUENCE IF EXISTS galette_field_types_id_seq;
  53 CREATE SEQUENCE galette_field_types_id_seq
  54     START 1
  55     INCREMENT 1
  56     MAXVALUE 2147483647
  57     MINVALUE 1
  58     CACHE 1;
  59
  60 -- sequence for contributions types
  61 DROP SEQUENCE IF EXISTS galette_types_cotisation_id_seq;
  62 CREATE SEQUENCE galette_types_cotisation_id_seq
  63     START 1
  64     INCREMENT 1
  65     MAXVALUE 2147483647
  66     MINVALUE 1
  67     CACHE 1;
  68
  69 -- sequence for groups
  70 DROP SEQUENCE IF EXISTS galette_groups_id_seq;
  71 CREATE SEQUENCE galette_groups_id_seq
  72     START 1
  73     INCREMENT 1
  74     MAXVALUE 2147483647
  75     MINVALUE 1
  76     CACHE 1;
  77
  78 -- sequence for mailing history
  79 DROP SEQUENCE IF EXISTS galette_mailing_history_id_seq;
  80 CREATE SEQUENCE galette_mailing_history_id_seq
  81     START 1
  82     INCREMENT 1
  83     MAXVALUE 2147483647
  84     MINVALUE 1
  85     CACHE 1;
  86
  87 -- sequence for title
  88 DROP SEQUENCE IF EXISTS galette_titles_id_seq;
  89 CREATE SEQUENCE galette_titles_id_seq
  90     START 1
  91     INCREMENT 1
  92     MAXVALUE 2147483647
  93     MINVALUE 1
  94     CACHE 1;
  95
  96 -- sequence for reminders
  97 DROP SEQUENCE IF EXISTS galette_reminders_id_seq;
  98 CREATE SEQUENCE galette_reminders_id_seq
  99     START 1
 100     INCREMENT 1
 101     MAXVALUE 2147483647
 102     MINVALUE 1
 103     CACHE 1;
 104
 105 -- sequence for pdf models
 106 DROP SEQUENCE IF EXISTS galette_pdfmodels_id_seq;
 107 CREATE SEQUENCE galette_pdfmodels_id_seq
 108     START 1
 109     INCREMENT 1
 110     MAXVALUE 2147483647
 111     MINVALUE 1
 112     CACHE 1;
 113
 114 -- sequence for import model
 115 DROP SEQUENCE IF EXISTS galette_import_model_id_seq;
 116 CREATE SEQUENCE galette_import_model_id_seq
 117     START 1
 118     INCREMENT 1
 119     MAXVALUE 2147483647
 120     MINVALUE 1
 121     CACHE 1;
 122
 123 -- sequence for payment types
 124 DROP SEQUENCE IF EXISTS galette_paymenttypes_id_seq;
 125 CREATE SEQUENCE galette_paymenttypes_id_seq
 126     START 1
 127     INCREMENT 1
 128     MAXVALUE 2147483647
 129     MINVALUE 1
 130     CACHE 1;
 131
 132 -- sequence for searches
 133 DROP SEQUENCE IF EXISTS galette_searches_id_seq;
 134 CREATE SEQUENCE galette_searches_id_seq
 135     START 1
 136     INCREMENT 1
 137     MAXVALUE 2147483647
 138     MINVALUE 1
 139     CACHE 1;
 140
 141 -- sequence for texts
 142 DROP SEQUENCE IF EXISTS galette_texts_id_seq;
 143 CREATE SEQUENCE galette_texts_id_seq
 144     START 1
 145     INCREMENT 1
 146     MAXVALUE 2147483647
 147     MINVALUE 1
 148     CACHE 1;
 149
 150 -- sequence for fields categories
 151 DROP SEQUENCE IF EXISTS galette_fields_categories_id_seq;
 152 CREATE SEQUENCE galette_fields_categories_id_seq
 153     START 1
 154     INCREMENT 1
 155     MAXVALUE 2147483647
 156     MINVALUE 1
 157     CACHE 1;
 158
 159 -- Schema
 160 -- REMINDER: Create order IS important, dependencies first !!
 161 DROP TABLE IF EXISTS galette_paymenttypes;
 162 CREATE TABLE galette_paymenttypes (
 163   type_id integer DEFAULT nextval('galette_paymenttypes_id_seq'::text) NOT NULL,
 164   type_name character varying(50) NOT NULL,
 165   PRIMARY KEY (type_id)
 166 );
 167
 168 DROP TABLE IF EXISTS galette_statuts CASCADE;
 169 CREATE TABLE galette_statuts (
 170   id_statut integer DEFAULT nextval('galette_statuts_id_seq'::text) NOT NULL,
 171   libelle_statut  character varying(100) DEFAULT '' NOT NULL,
 172   priorite_statut smallint DEFAULT '0' NOT NULL,
 173   PRIMARY KEY (id_statut)
 174 );
 175
 176 DROP TABLE IF EXISTS galette_titles CASCADE;
 177 CREATE TABLE galette_titles (
 178   id_title integer DEFAULT nextval('galette_titles_id_seq'::text) NOT NULL,
 179   short_label character varying(10) DEFAULT '' NOT NULL,
 180   long_label character varying(30) DEFAULT '',
 181   PRIMARY KEY (id_title)
 182 );
 183
 184 DROP TABLE IF EXISTS galette_adherents CASCADE;
 185 CREATE TABLE galette_adherents (
 186     id_adh integer DEFAULT nextval('galette_adherents_id_seq'::text) NOT NULL,
 187     id_statut integer DEFAULT '4' REFERENCES galette_statuts(id_statut) ON DELETE RESTRICT ON UPDATE CASCADE,
 188     nom_adh character varying(50) DEFAULT '' NOT NULL,
 189     prenom_adh character varying(50) DEFAULT '' NOT NULL,
 190     societe_adh character varying(200) DEFAULT NULL,
 191     pseudo_adh character varying(20) DEFAULT '' NOT NULL,
 192     titre_adh integer DEFAULT NULL REFERENCES galette_titles(id_title) ON DELETE RESTRICT ON UPDATE CASCADE,
 193     ddn_adh date DEFAULT '19010101',
 194     sexe_adh smallint DEFAULT '0' NOT NULL,
 195     adresse_adh character varying(150) DEFAULT '' NOT NULL,
 196     adresse2_adh character varying(150) DEFAULT NULL,
 197     cp_adh character varying(10) DEFAULT '' NOT NULL,
 198     ville_adh character varying(50) DEFAULT '' NOT NULL,
 199     pays_adh character varying(50) DEFAULT NULL,
 200     tel_adh character varying(20),
 201     gsm_adh character varying(20),
 202     email_adh character varying(255),
 203     url_adh character varying(200),
 204     icq_adh character varying(20),
 205     msn_adh character varying(150),
 206     jabber_adh character varying(150),
 207     info_adh text,
 208     info_public_adh text,
 209     prof_adh character varying(150),
 210     login_adh character varying(20) DEFAULT '' NOT NULL,
 211     mdp_adh character varying(60) DEFAULT '' NOT NULL,
 212     date_crea_adh date DEFAULT '19010101' NOT NULL,
 213     date_modif_adh date DEFAULT '19010101' NOT NULL,
 214     activite_adh boolean DEFAULT FALSE,
 215     bool_admin_adh boolean DEFAULT FALSE,
 216     bool_exempt_adh boolean DEFAULT FALSE,
 217     bool_display_info boolean DEFAULT FALSE,
 218     date_echeance date,
 219     pref_lang character varying(20) DEFAULT 'fr_FR',
 220     lieu_naissance text DEFAULT '',
 221     gpgid text DEFAULT NULL,
 222     fingerprint character varying(50) DEFAULT NULL,
 223     parent_id integer DEFAULT NULL REFERENCES galette_adherents(id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 224     PRIMARY KEY (id_adh)
 225 );
 226 -- add index for faster search on login_adh (auth)
 227 CREATE UNIQUE INDEX galette_adherents_login_adh_idx ON galette_adherents (login_adh);
 228
 229 DROP TABLE IF EXISTS galette_types_cotisation CASCADE;
 230 CREATE TABLE galette_types_cotisation (
 231   id_type_cotis integer DEFAULT nextval('galette_types_cotisation_id_seq'::text) NOT NULL,
 232   libelle_type_cotis character varying(100) DEFAULT '' NOT NULL,
 233   cotis_extension boolean DEFAULT FALSE,
 234   PRIMARY KEY (id_type_cotis)
 235 );
 236
 237 DROP TABLE IF EXISTS galette_transactions CASCADE;
 238 CREATE TABLE galette_transactions (
 239     trans_id integer DEFAULT nextval('galette_transactions_id_seq'::text)  NOT NULL,
 240     trans_date date DEFAULT '19010101' NOT NULL,
 241     trans_amount real DEFAULT '0',
 242     trans_desc character varying(150) NOT NULL DEFAULT '',
 243     id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 244     PRIMARY KEY (trans_id)
 245 );
 246
 247 DROP TABLE IF EXISTS galette_cotisations;
 248 CREATE TABLE galette_cotisations (
 249     id_cotis integer DEFAULT nextval('galette_cotisations_id_seq'::text)  NOT NULL,
 250     id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 251     id_type_cotis integer REFERENCES galette_types_cotisation (id_type_cotis) ON DELETE RESTRICT ON UPDATE CASCADE,
 252     montant_cotis real DEFAULT '0',
 253     type_paiement_cotis integer REFERENCES galette_paymenttypes (type_id) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL,
 254     info_cotis text,
 255     date_enreg date DEFAULT '19010101' NOT NULL,
 256     date_debut_cotis date DEFAULT '19010101' NOT NULL,
 257     date_fin_cotis date DEFAULT '19010101' NOT NULL,
 258     trans_id integer DEFAULT NULL REFERENCES galette_transactions (trans_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 259     PRIMARY KEY (id_cotis)
 260 );
 261
 262 DROP TABLE IF EXISTS galette_preferences;
 263 CREATE TABLE galette_preferences (
 264   id_pref integer DEFAULT nextval('galette_preferences_id_seq'::text) NOT NULL,
 265   nom_pref character varying(100) DEFAULT '' NOT NULL,
 266   val_pref character varying(200) DEFAULT '' NOT NULL,
 267   PRIMARY KEY (id_pref)
 268 );
 269 -- add index, nom_pref is used as foreign key elsewhere
 270 CREATE UNIQUE INDEX galette_preferences_nom_pref_idx ON galette_preferences (nom_pref);
 271
 272 DROP TABLE IF EXISTS galette_logs;
 273 CREATE TABLE galette_logs (
 274   id_log integer DEFAULT nextval('galette_logs_id_seq'::text) NOT NULL,
 275   date_log timestamp NOT NULL,
 276   ip_log character varying(46) DEFAULT '' NOT NULL,
 277   adh_log character varying(41) DEFAULT '' NOT NULL,
 278   text_log text,
 279   action_log text,
 280   sql_log text,
 281   PRIMARY KEY (id_log)
 282 );
 283
 284 -- Table for dynamic fields description;
 285 DROP TABLE IF EXISTS galette_field_types CASCADE;
 286 CREATE TABLE galette_field_types (
 287   field_id integer DEFAULT nextval('galette_field_types_id_seq'::text) NOT NULL,
 288   field_form character varying(10) NOT NULL,
 289   field_index integer DEFAULT '0' NOT NULL,
 290   field_name character varying(40) DEFAULT '' NOT NULL,
 291   field_perm integer DEFAULT '0' NOT NULL,
 292   field_type integer DEFAULT '0' NOT NULL,
 293   field_required boolean DEFAULT FALSE,
 294   field_pos integer DEFAULT '0' NOT NULL,
 295   field_width integer DEFAULT NULL,
 296   field_height integer DEFAULT NULL,
 297   field_size integer DEFAULT NULL,
 298   field_repeat integer DEFAULT NULL,
 299   field_layout integer DEFAULT NULL,
 300   PRIMARY KEY (field_id)
 301 );
 302 -- add index, field_form is used elsewhere
 303 CREATE INDEX galette_field_types_field_form_idx ON galette_field_types (field_form);
 304
 305 -- Table for dynamic fields data;
 306 DROP TABLE IF EXISTS galette_dynamic_fields;
 307 CREATE TABLE galette_dynamic_fields (
 308   item_id integer DEFAULT '0' NOT NULL, -- could be id_adh, trans_id, id_cotis
 309   field_id integer REFERENCES galette_field_types (field_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 310   field_form character varying(10) NOT NULL, -- not an fkey!
 311   val_index integer DEFAULT '0' NOT NULL,
 312   field_val text DEFAULT '',
 313   PRIMARY KEY (item_id, field_id, field_form, val_index)
 314 );
 315
 316 DROP TABLE IF EXISTS galette_pictures;
 317 CREATE TABLE galette_pictures (
 318   id_adh integer DEFAULT '0' NOT NULL,
 319   picture bytea NOT NULL,
 320   format character varying(30) DEFAULT '' NOT NULL,
 321   PRIMARY KEY (id_adh)
 322 );
 323
 324 -- Table for dynamic translation of strings;
 325 DROP TABLE IF EXISTS galette_l10n;
 326 CREATE TABLE galette_l10n (
 327   text_orig character varying(100) NOT NULL,
 328   text_locale character varying(15) NOT NULL,
 329   text_nref integer DEFAULT '1' NOT NULL,
 330   text_trans character varying(100) DEFAULT '' NOT NULL,
 331   PRIMARY KEY (text_orig, text_locale)
 332 );
 333
 334 -- new table for temporary passwords  2006-02-18;
 335 DROP TABLE IF EXISTS galette_tmppasswds;
 336 CREATE TABLE galette_tmppasswds (
 337   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 338   tmp_passwd character varying(60) NOT NULL,
 339   date_crea_tmp_passwd timestamp NOT NULL,
 340   PRIMARY KEY (id_adh)
 341 );
 342
 343 -- Table for automatic mails and their translations 2007-10-22;
 344 DROP TABLE IF EXISTS galette_texts;
 345 CREATE TABLE galette_texts (
 346   tid integer DEFAULT nextval('galette_texts_id_seq'::text) NOT NULL,
 347   tref character varying(20) NOT NULL,
 348   tsubject character varying(256) NOT NULL,
 349   tbody text NOT NULL,
 350   tlang character varying(16) NOT NULL,
 351   tcomment character varying(64) NOT NULL,
 352   PRIMARY KEY (tid)
 353 );
 354 CREATE UNIQUE INDEX galette_texts_localizedtxt_idx ON galette_texts (tref, tlang);
 355
 356 DROP TABLE IF EXISTS galette_fields_categories CASCADE;
 357 CREATE TABLE galette_fields_categories (
 358   id_field_category integer  DEFAULT nextval('galette_fields_categories_id_seq'::text) NOT NULL,
 359   table_name character varying(30) NOT NULL,
 360   category character varying(50) NOT NULL,
 361   position integer NOT NULL,
 362   PRIMARY KEY (id_field_category)
 363 );
 364
 365 DROP TABLE IF EXISTS galette_fields_config;
 366 CREATE TABLE galette_fields_config (
 367   table_name character varying(30) NOT NULL,
 368   field_id character varying(30) NOT NULL,
 369   required boolean NOT NULL,
 370   visible integer NOT NULL,
 371   position integer NOT NULL,
 372   list_visible boolean NOT NULL,
 373   list_position integer NOT NULL,
 374   id_field_category integer REFERENCES galette_fields_categories ON DELETE RESTRICT ON UPDATE CASCADE,
 375   PRIMARY KEY (table_name, field_id)
 376 );
 377
 378 -- Table for mailing history storage
 379 DROP TABLE IF EXISTS galette_mailing_history;
 380 CREATE TABLE galette_mailing_history (
 381   mailing_id integer DEFAULT nextval('galette_mailing_history_id_seq'::text) NOT NULL,
 382   mailing_sender integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 383   mailing_subject character varying(255) NOT NULL,
 384   mailing_body text NOT NULL,
 385   mailing_date timestamp NOT NULL,
 386   mailing_recipients text NOT NULL,
 387   mailing_sent boolean DEFAULT FALSE,
 388   mailing_sender_name character varying(100) DEFAULT NULL,
 389   mailing_sender_address character varying(255) DEFAULT NULL,
 390   PRIMARY KEY (mailing_id)
 391 );
 392
 393 -- table for groups
 394 DROP TABLE IF EXISTS galette_groups CASCADE;
 395 CREATE TABLE galette_groups (
 396   id_group integer DEFAULT nextval('galette_groups_id_seq'::text) NOT NULL,
 397   group_name character varying(50) NOT NULL CONSTRAINT name UNIQUE,
 398   creation_date timestamp NOT NULL,
 399   parent_group integer DEFAULT NULL REFERENCES galette_groups(id_group) ON DELETE RESTRICT ON UPDATE CASCADE,
 400   PRIMARY KEY (id_group)
 401 );
 402
 403 -- table for groups managers
 404 DROP TABLE IF EXISTS galette_groups_managers CASCADE;
 405 CREATE TABLE galette_groups_managers (
 406   id_group integer REFERENCES galette_groups(id_group) ON DELETE RESTRICT ON UPDATE CASCADE,
 407   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 408   PRIMARY KEY (id_group,id_adh)
 409 );
 410
 411 -- table for groups members
 412 DROP TABLE IF EXISTS galette_groups_members CASCADE;
 413 CREATE TABLE galette_groups_members (
 414   id_group integer REFERENCES galette_groups(id_group) ON DELETE RESTRICT ON UPDATE CASCADE,
 415   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 416   PRIMARY KEY (id_group,id_adh)
 417 );
 418
 419 -- Table for reminders
 420 DROP TABLE IF EXISTS galette_reminders;
 421 CREATE TABLE galette_reminders (
 422   reminder_id integer DEFAULT nextval('galette_reminders_id_seq'::text) NOT NULL,
 423   reminder_type integer NOT NULL,
 424   reminder_dest integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 425   reminder_date timestamp NOT NULL,
 426   reminder_success boolean DEFAULT FALSE,
 427   reminder_nomail boolean DEFAULT TRUE,
 428   reminder_comment text,
 429   PRIMARY KEY (reminder_id)
 430 );
 431
 432 DROP TABLE IF EXISTS galette_pdfmodels CASCADE;
 433 CREATE TABLE galette_pdfmodels (
 434   model_id integer DEFAULT nextval('galette_pdfmodels_id_seq'::text) NOT NULL,
 435   model_name character varying(50) NOT NULL,
 436   model_type integer NOT NULL,
 437   model_header text,
 438   model_footer text,
 439   model_body text,
 440   model_styles text,
 441   model_title character varying(100),
 442   model_subtitle character varying(100),
 443   model_parent integer DEFAULT NULL REFERENCES galette_pdfmodels (model_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 444   PRIMARY KEY (model_id)
 445 );
 446
 447 -- Table for import models
 448 DROP TABLE IF EXISTS galette_import_model;
 449 CREATE TABLE galette_import_model (
 450   model_id integer DEFAULT nextval('galette_import_model_id_seq'::text) NOT NULL,
 451   model_fields text,
 452   model_creation_date timestamp NOT NULL,
 453   PRIMARY KEY (model_id)
 454 );
 455
 456 -- Table for saved searches
 457 DROP TABLE IF EXISTS galette_searches;
 458 CREATE TABLE galette_searches (
 459   search_id integer DEFAULT nextval('galette_searches_id_seq'::text) NOT NULL,
 460   name character varying(100) DEFAULT NULL,
 461   form character varying(50) NOT NULL,
 462   parameters jsonb NOT NULL,
 463   parameters_sum bytea NOT NULL,
 464   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 465   creation_date timestamp NOT NULL,
 466   PRIMARY KEY (search_id)
 467 );
 468 -- add index on table to look for existing searches
 469 CREATE INDEX galette_searches_idx ON galette_searches (form, parameters_sum, id_adh);
 470
 471 -- new table for temporary links
 472 DROP TABLE IF EXISTS galette_tmplinks;
 473 CREATE TABLE galette_tmplinks (
 474   hash character varying(60) NOT NULL,
 475   target smallint NOT NULL,
 476   id integer NOT NULL,
 477   creation_date timestamp NOT NULL,
 478   PRIMARY KEY (target, id)
 479 );
 480
 481 -- table for database version
 482 DROP TABLE IF EXISTS galette_database;
 483 CREATE TABLE galette_database (
 484   version decimal NOT NULL
 485 );
 486 INSERT INTO galette_database (version) VALUES(0.94);