kivitendo/sql/Pg-upgrade2/email_journal.sql @ 7b1da9c3
| 24ab7ec0 | Moritz Bunkus | -- @tag: email_journal
|
|
-- @description: Journal für verschickte E-Mails
|
|||
-- @depends: release_3_3_0
|
|||
-- Note: sender_id may be NULL to indicate a mail sent by the system
|
|||
-- without a user being logged in – e.g. by the task server.
|
|||
CREATE TABLE email_journal (
|
|||
id SERIAL NOT NULL,
|
|||
sender_id INTEGER,
|
|||
"from" TEXT NOT NULL,
|
|||
recipients TEXT NOT NULL,
|
|||
sent_on TIMESTAMP NOT NULL DEFAULT now(),
|
|||
subject TEXT NOT NULL,
|
|||
body TEXT NOT NULL,
|
|||
headers TEXT NOT NULL,
|
|||
status TEXT NOT NULL,
|
|||
extended_status TEXT NOT NULL,
|
|||
itime TIMESTAMP NOT NULL DEFAULT now(),
|
|||
mtime TIMESTAMP NOT NULL DEFAULT now(),
|
|||
PRIMARY KEY (id),
|
|||
FOREIGN KEY (sender_id) REFERENCES employee (id),
|
|||
CONSTRAINT valid_status CHECK (status IN ('ok', 'failed'))
|
|||
);
|
|||
CREATE TABLE email_journal_attachments (
|
|||
id SERIAL NOT NULL,
|
|||
position INTEGER NOT NULL,
|
|||
email_journal_id INTEGER NOT NULL,
|
|||
name TEXT NOT NULL,
|
|||
mime_type TEXT NOT NULL,
|
|||
content BYTEA NOT NULL,
|
|||
itime TIMESTAMP NOT NULL DEFAULT now(),
|
|||
mtime TIMESTAMP NOT NULL DEFAULT now(),
|
|||
PRIMARY KEY (id),
|
|||
FOREIGN KEY (email_journal_id) REFERENCES email_journal (id) ON DELETE CASCADE
|
|||
);
|
|||
CREATE TRIGGER mtime_email_journal BEFORE UPDATE ON email_journal FOR EACH ROW EXECUTE PROCEDURE set_mtime();
|
|||
CREATE TRIGGER mtime_email_journal_attachments BEFORE UPDATE ON email_journal_attachments FOR EACH ROW EXECUTE PROCEDURE set_mtime();
|