demovote-schema/postgresql/tables/participants.sql

CREATE TYPE vote.status_participant AS ENUM('En attente', 'Payé chèque', 'Payé espèces', 'Orga', 'Virement bancaire', 'Virement Paypal', 'Guest');
CREATE TYPE vote.mode_paiement AS ENUM('Chèque', 'Virement', 'Paypal', 'Espèces', 'Autre');

CREATE TABLE IF NOT EXISTS vote.participants
(
    date_creation timestamp without time zone NOT NULL DEFAULT now(),
    date_modification timestamp without time zone,
    numero_participant integer NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    flag_actif boolean DEFAULT true,
    nom character varying(128) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
    prenom character varying(128) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
    pseudonyme character varying(128) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
    groupe character varying(128) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
    mot_de_passe character varying(256) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
    flag_expire boolean DEFAULT false,
    date_expiration timestamp without time zone DEFAULT NULL,
    delai_deconnexion integer NOT NULL DEFAULT 15,
    adresse character varying(256) COLLATE pg_catalog."default" DEFAULT '',
    code_postal character varying(16) COLLATE pg_catalog."default" DEFAULT '',
    ville character varying(128) COLLATE pg_catalog."default" DEFAULT '',
    pays character varying(128) COLLATE pg_catalog."default" DEFAULT 'France',
    numero_telephone character varying(32) COLLATE pg_catalog."default" DEFAULT '',
    email character varying(128) COLLATE pg_catalog."default" DEFAULT '',
    status vote.status_participant DEFAULT 'En attente',
    flag_machine boolean DEFAULT true,
    commentaire text,
    flag_jour1 boolean DEFAULT false,
    flag_jour2 boolean DEFAULT false,
    flag_jour3 boolean DEFAULT false,
    flag_dodo_sur_place boolean DEFAULT true,
    flag_amigabus boolean DEFAULT false,
    mode_paiement vote.mode_paiement DEFAULT 'Chèque',
    date_inscription timestamp without time zone NOT NULL DEFAULT now(),
    somme_recue decimal(10,2) DEFAULT '0.00',
    flag_arrive boolean DEFAULT false
)
TABLESPACE vote;
ALTER TABLE IF EXISTS vote.participants OWNER to vote;

CREATE INDEX IF NOT EXISTS ix_nomParticipant ON vote.participants USING btree (nom) TABLESPACE vote;
CREATE UNIQUE INDEX IF NOT EXISTS ix_pseudonymeParticipant ON vote.participants USING btree (pseudonyme) TABLESPACE vote;

CREATE FUNCTION vote.dateModificationParticipant() RETURNS TRIGGER AS $$
BEGIN
  NEW.date_modification = now();
  return NEW;
END;
$$ LANGUAGE 'plpgsql';
ALTER FUNCTION vote.dateModificationParticipant() OWNER TO vote;

CREATE OR REPLACE TRIGGER dateModificationParticipant BEFORE UPDATE ON vote.participants FOR EACH ROW EXECUTE FUNCTION vote.dateModificationParticipant();