Pour la modélisation, nous avons utilisé l'excellent logiciel brésilien pgModeler dans sa version disponible dans le référentiel des paquets de Debian 13. Ce logiciel est assez complet sur l'aspect DDL PosgreSQL et relativement facile de prise en main. Ecrit en C++ sur la base de l'infrastructure Qt, il est aussi assez performant. Il offre de nombreuses fonctions dont un mode de conception graphique d'où l'on peut ensuite extraire un script SQL. Voici le contenu du script de création du modèle de données adapté à la première version de l'application :
-- ** Database generated with pgModeler (PostgreSQL Database Modeler).
-- ** pgModeler version: 1.2.0-beta1a
-- ** PostgreSQL version: 17.0
-- ** Project Site: pgmodeler.io
-- ** Model Author: ---
-- object: pimail | type: ROLE --
-- DROP ROLE IF EXISTS pimail;
CREATE ROLE pimail WITH
LOGIN
PASSWORD 'xxxxxxx';
-- ddl-end --
COMMENT ON ROLE pimail IS E'Utilisateur du schéma pimail';
-- ddl-end --
-- ** Database creation must be performed outside a multi lined SQL file.
-- ** These commands were put in this file only as a convenience.
-- object: pimail | type: DATABASE --
-- DROP DATABASE IF EXISTS pimail;
CREATE DATABASE pimail;
-- ddl-end --
-- object: pimail | type: SCHEMA --
-- DROP SCHEMA IF EXISTS pimail CASCADE;
CREATE SCHEMA pimail;
-- ddl-end --
ALTER SCHEMA pimail OWNER TO pimail;
-- ddl-end --
SET search_path TO pg_catalog,public,pimail;
-- ddl-end --
-- object: pimail.t_archive | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_archive CASCADE;
CREATE TABLE pimail.t_archive (
c_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ,
c_from text,
c_to text,
c_cc text,
c_received timestamp,
c_messageid varchar(256),
c_subject varchar(256),
c_hdrcrc varchar(64),
c_crc varchar(64),
c_blob bigint,
c_validated bool DEFAULT FALSE,
CONSTRAINT t_archive_pk PRIMARY KEY (c_id)
);
-- ddl-end --
ALTER TABLE pimail.t_archive OWNER TO pimail;
-- ddl-end --
-- object: pimail.t_display | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_display CASCADE;
CREATE TABLE pimail.t_display (
c_email varchar(128) NOT NULL,
c_display varchar(256),
CONSTRAINT t_display_pk PRIMARY KEY (c_email)
);
-- ddl-end --
ALTER TABLE pimail.t_display OWNER TO pimail;
-- ddl-end --
-- object: pimail.t_mailbox | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_mailbox CASCADE;
CREATE TABLE pimail.t_mailbox (
c_id bigint NOT NULL,
c_email varchar(256),
c_display varchar(256),
c_host varchar(128),
c_port smallint,
c_user varchar(128),
c_password varchar(256),
c_authentication varchar(64),
c_transport varchar(64),
CONSTRAINT t_mailbox_pk PRIMARY KEY (c_id),
CONSTRAINT ck_authentication CHECK (c_authentication IN ('plain', 'login','gssapi','digestmd5','md5', 'crammd5')),
CONSTRAINT ck_transport CHECK (c_transport in ('clear','starttls','submission'))
);
-- ddl-end --
ALTER TABLE pimail.t_mailbox OWNER TO pimail;
-- ddl-end --
-- object: pimail.t_redirected | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_redirected CASCADE;
CREATE TABLE pimail.t_redirected (
c_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ,
c_oldfrom varchar(128),
c_newfrom varchar(128),
c_to text,
c_cc text,
c_bcc text,
c_sent timestamp,
c_messageid varchar(256),
c_subject varchar(256),
c_eml bigint,
c_archive bigint,
c_try int4 DEFAULT 0,
c_lexeme tsvector,
c_signaled bool DEFAULT FALSE,
CONSTRAINT t_redirect_pk PRIMARY KEY (c_id)
);
-- ddl-end --
ALTER TABLE pimail.t_redirected OWNER TO pimail;
-- ddl-end --
-- object: pimail.t_attachment | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_attachment CASCADE;
CREATE TABLE pimail.t_attachment (
c_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ,
c_blob bigint,
c_file varchar(256),
c_lexeme tsvector,
c_archive bigint,
CONSTRAINT t_attachment_pk PRIMARY KEY (c_id)
);
-- ddl-end --
ALTER TABLE pimail.t_attachment OWNER TO pimail;
-- ddl-end --
-- object: pimail.t_folder | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_folder CASCADE;
CREATE TABLE pimail.t_folder (
c_id bigint NOT NULL,
c_mbxfolder varchar(128),
c_dspfolder varchar(256),
c_mailbox bigint,
c_parent bigint,
CONSTRAINT t_folder_pk PRIMARY KEY (c_id)
);
-- ddl-end --
ALTER TABLE pimail.t_folder OWNER TO pimail;
-- ddl-end --
-- object: pimail.r_mailbox_folder | type: TABLE --
-- DROP TABLE IF EXISTS pimail.r_mailbox_folder CASCADE;
CREATE TABLE pimail.r_mailbox_folder (
c_id bigint NOT NULL,
c_mailbox bigint,
c_folder bigint,
CONSTRAINT r_mailbox_folder_pk PRIMARY KEY (c_id)
);
-- ddl-end --
ALTER TABLE pimail.r_mailbox_folder OWNER TO pimail;
-- ddl-end --
-- object: ix_hdrcrc | type: INDEX --
-- DROP INDEX IF EXISTS pimail.ix_hdrcrc CASCADE;
CREATE UNIQUE INDEX ix_hdrcrc ON pimail.t_archive
USING btree
(
c_hdrcrc
);
-- ddl-end --
-- object: ix_crc | type: INDEX --
-- DROP INDEX IF EXISTS pimail.ix_crc CASCADE;
CREATE UNIQUE INDEX ix_crc ON pimail.t_archive
USING btree
(
c_crc
);
-- ddl-end --
-- object: pimail.t_mapping | type: TABLE --
-- DROP TABLE IF EXISTS pimail.t_mapping CASCADE;
CREATE TABLE pimail.t_mapping (
c_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ,
c_oldfrom bigint,
c_newfrom bigint,
c_to text,
c_cc text,
c_bcc text,
CONSTRAINT t_target_pk PRIMARY KEY (c_id)
);
-- ddl-end --
ALTER TABLE pimail.t_mapping OWNER TO postgres;
-- ddl-end --
-- object: ix_email | type: INDEX --
-- DROP INDEX IF EXISTS pimail.ix_email CASCADE;
CREATE INDEX ix_email ON pimail.t_mailbox
USING btree
(
c_email
);
-- ddl-end --
-- object: ix_oldfrom | type: INDEX --
-- DROP INDEX IF EXISTS pimail.ix_oldfrom CASCADE;
CREATE UNIQUE INDEX ix_oldfrom ON pimail.t_mapping
USING btree
(
c_oldfrom
);
-- ddl-end --
-- object: fk_redirect_archive | type: CONSTRAINT --
-- ALTER TABLE pimail.t_redirected DROP CONSTRAINT IF EXISTS fk_redirect_archive CASCADE;
ALTER TABLE pimail.t_redirected ADD CONSTRAINT fk_redirect_archive FOREIGN KEY (c_archive)
REFERENCES pimail.t_archive (c_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --
-- object: fk_archive | type: CONSTRAINT --
-- ALTER TABLE pimail.t_attachment DROP CONSTRAINT IF EXISTS fk_archive CASCADE;
ALTER TABLE pimail.t_attachment ADD CONSTRAINT fk_archive FOREIGN KEY (c_archive)
REFERENCES pimail.t_archive (c_id) MATCH SIMPLE
ON DELETE CASCADE ON UPDATE NO ACTION;
-- ddl-end --
-- object: fk_subfolder | type: CONSTRAINT --
-- ALTER TABLE pimail.t_folder DROP CONSTRAINT IF EXISTS fk_subfolder CASCADE;
ALTER TABLE pimail.t_folder ADD CONSTRAINT fk_subfolder FOREIGN KEY (c_parent)
REFERENCES pimail.t_folder (c_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --
-- object: fk_mailbox_folder_mailbox | type: CONSTRAINT --
-- ALTER TABLE pimail.r_mailbox_folder DROP CONSTRAINT IF EXISTS fk_mailbox_folder_mailbox CASCADE;
ALTER TABLE pimail.r_mailbox_folder ADD CONSTRAINT fk_mailbox_folder_mailbox FOREIGN KEY (c_mailbox)
REFERENCES pimail.t_mailbox (c_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --
-- object: fk_mailbox_folder_folder | type: CONSTRAINT --
-- ALTER TABLE pimail.r_mailbox_folder DROP CONSTRAINT IF EXISTS fk_mailbox_folder_folder CASCADE;
ALTER TABLE pimail.r_mailbox_folder ADD CONSTRAINT fk_mailbox_folder_folder FOREIGN KEY (c_folder)
REFERENCES pimail.t_folder (c_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --
-- object: fk_oldfrom | type: CONSTRAINT --
-- ALTER TABLE pimail.t_mapping DROP CONSTRAINT IF EXISTS fk_oldfrom CASCADE;
ALTER TABLE pimail.t_mapping ADD CONSTRAINT fk_oldfrom FOREIGN KEY (c_oldfrom)
REFERENCES pimail.t_mailbox (c_id) MATCH SIMPLE
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- ddl-end --