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 -- sequence for socials
 160 DROP SEQUENCE IF EXISTS galette_socials_id_seq;
 161 CREATE SEQUENCE galette_socials_id_seq
 162     START 1
 163     INCREMENT 1
 164     MAXVALUE 2147483647
 165     MINVALUE 1
 166     CACHE 1;
 167
 168 -- Schema
 169 -- REMINDER: Create order IS important, dependencies first !!
 170 DROP TABLE IF EXISTS galette_paymenttypes CASCADE;
 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)
 175 );
 176
 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)
 183 );
 184
 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)
 191 );
 192
 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     cp_adh character varying(10) DEFAULT '' NOT NULL,
 206     ville_adh character varying(200) DEFAULT '' NOT NULL,
 207     region_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),
 212     info_adh text,
 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,
 223     date_echeance date,
 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,
 230     PRIMARY KEY (id_adh)
 231 );
 232 -- add index for faster search on login_adh (auth)
 233 CREATE UNIQUE INDEX galette_adherents_login_adh_idx ON galette_adherents (login_adh);
 234
 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   amount real DEFAULT '0',
 240   cotis_extension boolean DEFAULT FALSE,
 241   PRIMARY KEY (id_type_cotis)
 242 );
 243
 244 DROP TABLE IF EXISTS galette_transactions CASCADE;
 245 CREATE TABLE galette_transactions (
 246     trans_id integer DEFAULT nextval('galette_transactions_id_seq'::text)  NOT NULL,
 247     trans_date date DEFAULT '19010101' NOT NULL,
 248     trans_amount real DEFAULT '0',
 249     trans_desc character varying(255) NOT NULL DEFAULT '',
 250     id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 251     type_paiement_trans integer REFERENCES galette_paymenttypes (type_id) ON DELETE RESTRICT ON UPDATE CASCADE NULL,
 252     PRIMARY KEY (trans_id)
 253 );
 254
 255 DROP TABLE IF EXISTS galette_cotisations CASCADE;
 256 CREATE TABLE galette_cotisations (
 257     id_cotis integer DEFAULT nextval('galette_cotisations_id_seq'::text)  NOT NULL,
 258     id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 259     id_type_cotis integer REFERENCES galette_types_cotisation (id_type_cotis) ON DELETE RESTRICT ON UPDATE CASCADE,
 260     montant_cotis real DEFAULT '0',
 261     type_paiement_cotis integer REFERENCES galette_paymenttypes (type_id) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL,
 262     info_cotis text,
 263     date_enreg date DEFAULT '19010101' NOT NULL,
 264     date_debut_cotis date DEFAULT '19010101' NOT NULL,
 265     date_fin_cotis date DEFAULT '19010101' NOT NULL,
 266     trans_id integer DEFAULT NULL REFERENCES galette_transactions (trans_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 267     PRIMARY KEY (id_cotis)
 268 );
 269
 270 DROP TABLE IF EXISTS galette_preferences CASCADE;
 271 CREATE TABLE galette_preferences (
 272   id_pref integer DEFAULT nextval('galette_preferences_id_seq'::text) NOT NULL,
 273   nom_pref character varying(100) DEFAULT '' NOT NULL,
 274   val_pref character varying(255) DEFAULT '' NOT NULL,
 275   PRIMARY KEY (id_pref)
 276 );
 277 -- add index, nom_pref is used as foreign key elsewhere
 278 CREATE UNIQUE INDEX galette_preferences_nom_pref_idx ON galette_preferences (nom_pref);
 279
 280 DROP TABLE IF EXISTS galette_logs CASCADE;
 281 CREATE TABLE galette_logs (
 282   id_log integer DEFAULT nextval('galette_logs_id_seq'::text) NOT NULL,
 283   date_log timestamp NOT NULL,
 284   ip_log character varying(46) DEFAULT '' NOT NULL,
 285   adh_log character varying(255) DEFAULT '' NOT NULL, -- see galette_adherents.login_adh
 286   text_log text,
 287   action_log text,
 288   sql_log text,
 289   PRIMARY KEY (id_log)
 290 );
 291
 292 -- Table for dynamic fields description;
 293 DROP TABLE IF EXISTS galette_field_types CASCADE;
 294 CREATE TABLE galette_field_types (
 295   field_id integer DEFAULT nextval('galette_field_types_id_seq'::text) NOT NULL,
 296   field_form character varying(10) NOT NULL,
 297   field_index integer DEFAULT '0' NOT NULL,
 298   field_name character varying(255) DEFAULT '' NOT NULL,
 299   field_perm integer DEFAULT '0' NOT NULL,
 300   field_type integer DEFAULT '0' NOT NULL,
 301   field_required boolean DEFAULT FALSE,
 302   field_pos integer DEFAULT '0' NOT NULL,
 303   field_width integer DEFAULT NULL,
 304   field_height integer DEFAULT NULL,
 305   field_min_size integer DEFAULT NULL,
 306   field_size integer DEFAULT NULL,
 307   field_repeat integer DEFAULT NULL,
 308   field_information text DEFAULT NULL,
 309   field_width_in_forms integer DEFAULT '1' NOT NULL,
 310   field_information_above boolean DEFAULT FALSE,
 311   PRIMARY KEY (field_id)
 312 );
 313 -- add index, field_form is used elsewhere
 314 CREATE INDEX galette_field_types_field_form_idx ON galette_field_types (field_form);
 315
 316 -- Table for dynamic fields data;
 317 DROP TABLE IF EXISTS galette_dynamic_fields CASCADE;
 318 CREATE TABLE galette_dynamic_fields (
 319   item_id integer DEFAULT '0' NOT NULL, -- could be id_adh, trans_id, id_cotis
 320   field_id integer REFERENCES galette_field_types (field_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 321   field_form character varying(10) NOT NULL, -- not an fkey!
 322   val_index integer DEFAULT '0' NOT NULL,
 323   field_val text DEFAULT '',
 324   PRIMARY KEY (item_id, field_id, field_form, val_index)
 325 );
 326
 327 DROP TABLE IF EXISTS galette_pictures CASCADE;
 328 CREATE TABLE galette_pictures (
 329   id_adh integer DEFAULT '0' NOT NULL,
 330   picture bytea NOT NULL,
 331   format character varying(30) DEFAULT '' NOT NULL,
 332   PRIMARY KEY (id_adh)
 333 );
 334
 335 -- Table for dynamic translation of strings;
 336 DROP TABLE IF EXISTS galette_l10n CASCADE;
 337 CREATE TABLE galette_l10n (
 338   text_orig character varying(255) NOT NULL,
 339   text_locale character varying(15) NOT NULL,
 340   text_nref integer DEFAULT '1' NOT NULL,
 341   text_trans character varying(255) DEFAULT '' NOT NULL,
 342   PRIMARY KEY (text_orig, text_locale)
 343 );
 344
 345 -- new table for temporary passwords  2006-02-18;
 346 DROP TABLE IF EXISTS galette_tmppasswds CASCADE;
 347 CREATE TABLE galette_tmppasswds (
 348   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 349   tmp_passwd character varying(250) NOT NULL,
 350   date_crea_tmp_passwd timestamp NOT NULL,
 351   PRIMARY KEY (id_adh)
 352 );
 353
 354 -- Table for automatic mails and their translations 2007-10-22;
 355 DROP TABLE IF EXISTS galette_texts CASCADE;
 356 CREATE TABLE galette_texts (
 357   tid integer DEFAULT nextval('galette_texts_id_seq'::text) NOT NULL,
 358   tref character varying(20) NOT NULL,
 359   tsubject character varying(256) NOT NULL,
 360   tbody text NOT NULL,
 361   tlang character varying(16) NOT NULL,
 362   tcomment character varying(255) NOT NULL,
 363   PRIMARY KEY (tid)
 364 );
 365 CREATE UNIQUE INDEX galette_texts_localizedtxt_idx ON galette_texts (tref, tlang);
 366
 367 DROP TABLE IF EXISTS galette_fields_categories CASCADE;
 368 CREATE TABLE galette_fields_categories (
 369   id_field_category integer  DEFAULT nextval('galette_fields_categories_id_seq'::text) NOT NULL,
 370   table_name character varying(30) NOT NULL,
 371   category character varying(100) NOT NULL,
 372   position integer NOT NULL,
 373   PRIMARY KEY (id_field_category)
 374 );
 375
 376 DROP TABLE IF EXISTS galette_fields_config CASCADE;
 377 CREATE TABLE galette_fields_config (
 378   table_name character varying(30) NOT NULL,
 379   field_id character varying(30) NOT NULL,
 380   required boolean NOT NULL,
 381   visible integer NOT NULL,
 382   position integer NOT NULL,
 383   list_visible boolean NOT NULL,
 384   list_position integer NOT NULL,
 385   width_in_forms integer DEFAULT '1' NOT NULL,
 386   id_field_category integer REFERENCES galette_fields_categories ON DELETE RESTRICT ON UPDATE CASCADE,
 387   PRIMARY KEY (table_name, field_id)
 388 );
 389
 390 -- Table for mailing history storage
 391 DROP TABLE IF EXISTS galette_mailing_history CASCADE;
 392 CREATE TABLE galette_mailing_history (
 393   mailing_id integer DEFAULT nextval('galette_mailing_history_id_seq'::text) NOT NULL,
 394   mailing_sender integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 395   mailing_subject character varying(255) NOT NULL,
 396   mailing_body text NOT NULL,
 397   mailing_date timestamp NOT NULL,
 398   mailing_recipients text NOT NULL,
 399   mailing_sent boolean DEFAULT FALSE,
 400   mailing_sender_name character varying(255) DEFAULT NULL,
 401   mailing_sender_address character varying(255) DEFAULT NULL,
 402   PRIMARY KEY (mailing_id)
 403 );
 404
 405 -- table for groups
 406 DROP TABLE IF EXISTS galette_groups CASCADE;
 407 CREATE TABLE galette_groups (
 408   id_group integer DEFAULT nextval('galette_groups_id_seq'::text) NOT NULL,
 409   group_name character varying(250) NOT NULL,
 410   creation_date timestamp NOT NULL,
 411   parent_group integer DEFAULT NULL REFERENCES galette_groups(id_group) ON DELETE RESTRICT ON UPDATE CASCADE,
 412   PRIMARY KEY (id_group)
 413 );
 414
 415 -- table for groups managers
 416 DROP TABLE IF EXISTS galette_groups_managers CASCADE;
 417 CREATE TABLE galette_groups_managers (
 418   id_group integer REFERENCES galette_groups(id_group) ON DELETE RESTRICT ON UPDATE CASCADE,
 419   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 420   PRIMARY KEY (id_group,id_adh)
 421 );
 422
 423 -- table for groups members
 424 DROP TABLE IF EXISTS galette_groups_members CASCADE;
 425 CREATE TABLE galette_groups_members (
 426   id_group integer REFERENCES galette_groups(id_group) ON DELETE RESTRICT ON UPDATE CASCADE,
 427   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE RESTRICT ON UPDATE CASCADE,
 428   PRIMARY KEY (id_group,id_adh)
 429 );
 430
 431 -- Table for reminders
 432 DROP TABLE IF EXISTS galette_reminders CASCADE;
 433 CREATE TABLE galette_reminders (
 434   reminder_id integer DEFAULT nextval('galette_reminders_id_seq'::text) NOT NULL,
 435   reminder_type integer NOT NULL,
 436   reminder_dest integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 437   reminder_date timestamp NOT NULL,
 438   reminder_success boolean DEFAULT FALSE,
 439   reminder_nomail boolean DEFAULT TRUE,
 440   reminder_comment text,
 441   PRIMARY KEY (reminder_id)
 442 );
 443
 444 DROP TABLE IF EXISTS galette_pdfmodels CASCADE;
 445 CREATE TABLE galette_pdfmodels (
 446   model_id integer DEFAULT nextval('galette_pdfmodels_id_seq'::text) NOT NULL,
 447   model_name character varying(50) NOT NULL,
 448   model_type integer NOT NULL,
 449   model_header text,
 450   model_footer text,
 451   model_body text,
 452   model_styles text,
 453   model_title character varying(250),
 454   model_subtitle character varying(250),
 455   model_parent integer DEFAULT NULL REFERENCES galette_pdfmodels (model_id) ON DELETE RESTRICT ON UPDATE CASCADE,
 456   PRIMARY KEY (model_id)
 457 );
 458
 459 -- Table for import models
 460 DROP TABLE IF EXISTS galette_import_model CASCADE;
 461 CREATE TABLE galette_import_model (
 462   model_id integer DEFAULT nextval('galette_import_model_id_seq'::text) NOT NULL,
 463   model_fields text,
 464   model_creation_date timestamp NOT NULL,
 465   PRIMARY KEY (model_id)
 466 );
 467
 468 -- Table for saved searches
 469 DROP TABLE IF EXISTS galette_searches CASCADE;
 470 CREATE TABLE galette_searches (
 471   search_id integer DEFAULT nextval('galette_searches_id_seq'::text) NOT NULL,
 472   name character varying(100) DEFAULT NULL,
 473   form character varying(50) NOT NULL,
 474   parameters jsonb NOT NULL,
 475   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 476   creation_date timestamp NOT NULL,
 477   PRIMARY KEY (search_id)
 478 );
 479
 480 -- new table for temporary links
 481 DROP TABLE IF EXISTS galette_tmplinks CASCADE;
 482 CREATE TABLE galette_tmplinks (
 483   hash character varying(250) NOT NULL,
 484   target smallint NOT NULL,
 485   id integer NOT NULL,
 486   creation_date timestamp NOT NULL,
 487   PRIMARY KEY (target, id)
 488 );
 489
 490 -- table for social networks
 491 DROP TABLE IF EXISTS galette_socials CASCADE;
 492 CREATE TABLE galette_socials (
 493   id_social integer DEFAULT nextval('galette_socials_id_seq'::text) NOT NULL,
 494   id_adh integer REFERENCES galette_adherents (id_adh) ON DELETE CASCADE ON UPDATE CASCADE,
 495   type character varying(250) NOT NULL,
 496   url character varying(255) DEFAULT NULL,
 497   PRIMARY KEY (id_social)
 498 );
 499 -- add index on table to look for type
 500 CREATE INDEX galette_socials_idx ON galette_socials (type);
 501
 502 -- table for database version
 503 DROP TABLE IF EXISTS galette_database CASCADE;
 504 CREATE TABLE galette_database (
 505   version decimal NOT NULL
 506 );
 507 INSERT INTO galette_database (version) VALUES(1.10);