dd.sql

/****************************************************************
 *
 * PostgreSQL database objects data definition for fmsgd
 *
 ****************************************************************/

-- database with encoding UTF8 should already be created and connected

create table if not exists msg (
    id            	bigserial       	primary key,
	version			int					not null,
    pid           	bigint          	references msg (id),
	no_reply		boolean				not null default false,
	is_important	boolean				not null default false,
	is_deflate		boolean				not null default false,
    time_sent     	double precision,             -- time sending host recieved message for sending, message timestamp field, NULL means message not ready for sending i.e. draft
    from_addr     	varchar(255)    	not null,
    add_to_from   	varchar(255),
    topic         	varchar(255)    	not null, 
    type          	varchar(255)    	not null,
    sha256        	bytea           	unique,
    psha256       	bytea,
	size			int					not null, -- spec allows uint32 but we don't enforced by FMSG_MAX_MSG_SIZE
    filepath      	text            	not null
);
create index on msg ((lower(from_addr)));

create table if not exists msg_to (
	id				bigserial			primary key,
	msg_id			bigint				not null references msg (id),
	addr			varchar(255)		not null,
    time_delivered  double precision,   -- if sending, time sending host recieved delivery confirmation, if receiving, time successfully received message
    time_last_attempt double precision, -- only used when sending, time of last delivery attempt if failed; otherwise null
    time_read       double precision,   -- time recipient read the message; null if unread
    response_code   smallint,		    -- only used when sending, response code of last delivery attempt if failed; otherwise null
    attempt_count   int             not null default 0, -- number of failed delivery attempts; used for exponential back-off
	unique (msg_id, addr)
);
create index on msg_to ((lower(addr)));

create table if not exists msg_add_to (
	id				bigserial			primary key,
	msg_id			bigint				not null references msg (id),
	addr			varchar(255)		not null,
    time_delivered  double precision,   -- if sending, time sending host recieved delivery confirmation, if receiving, time successfully received message
    time_last_attempt double precision, -- only used when sending, time of last delivery attempt if failed; otherwise null
    time_read       double precision,   -- time recipient read the message; null if unread
    response_code   smallint,		    -- only used when sending, response code of last delivery attempt if failed; otherwise null
    attempt_count   int             not null default 0, -- number of failed delivery attempts; used for exponential back-off
	unique (msg_id, addr)
);
create index on msg_add_to ((lower(addr)));

create table if not exists msg_attachment (
    msg_id        	bigint          references msg (id),
    position      	smallint        not null default 0,
    flags         	smallint        not null default 0,
    type          	varchar(255)    not null default 'application/octet-stream',
    filename      	varchar(255)    not null,
    filesize      	int             not null, 
    filepath      	text			not null,
    primary key (msg_id, filename)
);

-- keep protocol parent hash populated for locally-created replies that set
-- the relational parent id. A reply cannot reference a draft parent, and any
-- explicit psha256 must match the referenced parent's sha256.
create or replace function populate_msg_psha256_from_pid() returns trigger as $$
declare
    parent_time_sent double precision;
    parent_sha256 bytea;
begin
    if NEW.pid is null then
        return NEW;
    end if;

    select parent.time_sent, parent.sha256
    into parent_time_sent, parent_sha256
    from msg parent
    where parent.id = NEW.pid;

    if not found then
        raise exception 'parent message % does not exist', NEW.pid;
    end if;

    if parent_time_sent is null then
        raise exception 'cannot set pid %: parent message is a draft', NEW.pid;
    end if;

    if parent_sha256 is null or octet_length(parent_sha256) = 0 then
        raise exception 'cannot set pid %: parent message has no sha256', NEW.pid;
    end if;

    if NEW.psha256 is null or octet_length(NEW.psha256) = 0 then
        NEW.psha256 = parent_sha256;
    elsif NEW.psha256 <> parent_sha256 then
        raise exception 'psha256 does not match parent message % sha256', NEW.pid;
    end if;

    return NEW;
end;
$$ language plpgsql;

drop trigger if exists trg_msg_populate_psha256 on msg;
create trigger trg_msg_populate_psha256
    before insert or update of pid, psha256 on msg
    for each row execute function populate_msg_psha256_from_pid();

-- once a message has replies, it must remain referenceable by protocol hash.
create or replace function prevent_referenced_msg_from_becoming_unreferenceable() returns trigger as $$
begin
    if exists (select 1 from msg child where child.pid = NEW.id) then
        if NEW.time_sent is null then
            raise exception 'cannot make message % a draft: it has replies', NEW.id;
        end if;

        if NEW.sha256 is null or octet_length(NEW.sha256) = 0 then
            raise exception 'cannot clear sha256 for message %: it has replies', NEW.id;
        end if;

        if OLD.sha256 is distinct from NEW.sha256 then
            raise exception 'cannot change sha256 for message %: it has replies', NEW.id;
        end if;
    end if;
    return NEW;
end;
$$ language plpgsql;

drop trigger if exists trg_msg_prevent_unreferenceable_parent on msg;
create trigger trg_msg_prevent_unreferenceable_parent
    before update of time_sent, sha256 on msg
    for each row execute function prevent_referenced_msg_from_becoming_unreferenceable();

-- notify when a new msg_to row is inserted with null time_delivered so the
-- sender can pick it up immediately instead of waiting for the next poll.
create or replace function notify_msg_to_insert() returns trigger as $$
begin
    if NEW.time_delivered is null then
        perform pg_notify('new_msg_to', NEW.msg_id::text || ',' || NEW.addr)
        from msg where id = NEW.msg_id and time_sent is not null;
    end if;
    return NEW;
end;
$$ language plpgsql;

drop trigger if exists trg_msg_to_insert on msg_to;
create trigger trg_msg_to_insert
    after insert on msg_to
    for each row execute function notify_msg_to_insert();

-- notify when a new msg_add_to row is inserted with null time_delivered so the
-- sender can pick it up immediately instead of waiting for the next poll.
drop trigger if exists trg_msg_add_to_insert on msg_add_to;
create trigger trg_msg_add_to_insert
    after insert on msg_add_to
    for each row execute function notify_msg_to_insert();