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