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