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     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   cotis_extension boolean DEFAULT FALSE,
 240   PRIMARY KEY (id_type_cotis)
 241 );
 242
 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)
 251 );
 252
 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,
 260     info_cotis text,
 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)
 266 );
 267
 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)
 274 );
 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);
 277
 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
 284   text_log text,
 285   action_log text,
 286   sql_log text,
 287   PRIMARY KEY (id_log)
 288 );
 289
 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_information text DEFAULT NULL,
 306   PRIMARY KEY (field_id)
 307 );
 308 -- add index, field_form is used elsewhere
 309 CREATE INDEX galette_field_types_field_form_idx ON galette_field_types (field_form);
 310
 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)
 320 );
 321
 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,
 327   PRIMARY KEY (id_adh)
 328 );
 329
 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)
 338 );
 339
 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,
 346   PRIMARY KEY (id_adh)
 347 );
 348
 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,
 355   tbody text NOT NULL,
 356   tlang character varying(16) NOT NULL,
 357   tcomment character varying(255) NOT NULL,
 358   PRIMARY KEY (tid)
 359 );
 360 CREATE UNIQUE INDEX galette_texts_localizedtxt_idx ON galette_texts (tref, tlang);
 361
 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)
 369 );
 370
 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)
 382 );
 383
 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)
 397 );
 398
 399 -- table for groups
 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,
 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)
 407 );
 408
 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)
 415 );
 416
 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)
 423 );
 424
 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)
 436 );
 437
 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,
 443   model_header text,
 444   model_footer text,
 445   model_body text,
 446   model_styles text,
 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)
 451 );
 452
 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,
 457   model_fields text,
 458   model_creation_date timestamp NOT NULL,
 459   PRIMARY KEY (model_id)
 460 );
 461
 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)
 472 );
 473
 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,
 479   id integer NOT NULL,
 480   creation_date timestamp NOT NULL,
 481   PRIMARY KEY (target, id)
 482 );
 483
 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)
 492 );
 493 -- add index on table to look for type
 494 CREATE INDEX galette_socials_idx ON galette_socials (type);
 495
 496 -- table for database version
 497 DROP TABLE IF EXISTS galette_database;
 498 CREATE TABLE galette_database (
 499   version decimal NOT NULL
 500 );
 501 INSERT INTO galette_database (version) VALUES(0.96);