migrate_items.sql

begin;

create table item (
    item_id int8 not null primary key default nextval('version_identifier'),
    account_id int8 not null references account,
    name_hmac bytea not null unique,
    creation_time timestamp not null default current_timestamp,
    modified_time timestamp not null default current_timestamp,
    deletion_time timestamp,
    value bytea,
    timeline_visible boolean default false
);

create unique index item_name_hmac_idx
    on item (name_hmac);

create table item_history (
    item_history_id int8 not null primary key default nextval('version_identifier'),
    item_id int8 not null references item,
    account_id int8 not null references account,
    creation_time timestamp not null default current_timestamp,
    modified_time timestamp not null default current_timestamp,
    deletion_time timestamp,
    value bytea
);

create table timeline (
    timeline_id int8 not null primary key default nextval('version_identifier'),
    creator_id int8 not null references account,
    receiver_id int8 not null references account,
    item_id int8 not null references item,
    creation_time timestamp not null default current_timestamp,
    modified_time timestamp not null default current_timestamp,
    deletion_time timestamp,
    value bytea
);

create table item_session_key (
    item_session_key_id int8 not null primary key
        default nextval('version_identifier'),
    item_id int8 not null references item on delete cascade,
    account_id int8 not null references account,
    creation_time timestamp not null default current_timestamp,
    supercede_time timestamp
);

CREATE UNIQUE INDEX item_session_key_active_idx
    ON item_session_key (item_id)
    WHERE supercede_time IS NULL;

create table item_session_key_share (
    item_session_key_share_id int8 not null primary key
        default nextval('version_identifier'),
    item_session_key_id int8 not null references item_session_key on delete cascade,
    account_id int8 not null references account,
    to_account_id int8 not null references account (account_id),
    session_key_ciphertext varchar not null,
    deletion_time timestamp
);

COMMENT ON TABLE item_session_key_share IS
'Make one of a item''s session keys readable to an account';
COMMENT ON COLUMN item_session_key_share.session_key_ciphertext IS
'This is the output of encrypting the AES256 session key to the public key
owned by to_account_id.';
COMMENT ON COLUMN item_session_key_share.deletion_time IS
'When an item is unshared, deletion_time';

CREATE OR REPLACE FUNCTION notifyUpdatedItem() RETURNS TRIGGER AS $$
  DECLARE 
    notify_row RECORD;
  BEGIN
    FOR notify_row IN 
      SELECT s.item_session_key_share_id, 
        s.account_id, s.to_account_id, k.item_id, 
	  a.username AS toUser, b.username AS fromUser 
        FROM item_session_key_share s 
        JOIN item_session_key k ON 
          (s.item_session_key_id = k.item_session_key_id)
        JOIN account a ON 
          (s.to_account_id = a.account_id)
        JOIN account b ON 
          (s.account_id = b.account_id)
        WHERE k.item_id = NEW.item_id AND k.supercede_time IS NULL 
    LOOP
      PERFORM pg_notify('SharedItemUpdated', 
        CAST(notify_row.to_account_id AS text)|| ' ' ||
        CAST(notify_row.account_id AS text) || ' ' ||
        encode(NEW.name_hmac, 'escape') || ' ' ||  
        notify_row.toUser || ' ' || 
	notify_row.fromUser);

    END LOOP;
    RETURN NULL;
  -- XXXddahl: EXCEPTION RAISE pg_notify notification???
  END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdatedItemNotify AFTER UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE notifyUpdatedItem();

CREATE OR REPLACE FUNCTION populateItemHistoryInsertUpdate() RETURNS TRIGGER AS $$
  BEGIN
    INSERT INTO item_history (item_id, account_id, creation_time, value)
    VALUES (NEW.item_id, NEW.account_id, NEW.creation_time, NEW.value);  	
    RETURN NULL;
  END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER PopulateItemHistoryInsert AFTER INSERT ON item FOR EACH ROW EXECUTE PROCEDURE populateItemHistoryInsertUpdate();

CREATE TRIGGER PopulateItemHistoryUpdate AFTER UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE populateItemHistoryInsertUpdate();

-- We need to insert a timeline record for each item / item_key_share pair insert/update event

CREATE OR REPLACE FUNCTION populateTimeline() RETURNS TRIGGER AS $$
  DECLARE 
    item_row RECORD;
  BEGIN
    FOR item_row IN 
      SELECT s.item_session_key_share_id, 
        s.account_id, s.to_account_id, k.item_id, 
	  a.username AS toUser, b.username AS fromUser 
        FROM item_session_key_share s 
        JOIN item_session_key k ON 
          (s.item_session_key_id = k.item_session_key_id)
        JOIN account a ON 
          (s.to_account_id = a.account_id)
        JOIN account b ON 
          (s.account_id = b.account_id)
        WHERE k.item_id = NEW.item_id AND k.supercede_time IS NULL
	  AND NEW.timeline_visible = TRUE
    LOOP
      -- Insert a timeline row for each session_key_share
      INSERT INTO timeline (item_id, creator_id, receiver_id, creation_time, value)
      VALUES (NEW.item_id, item_row.account_id, item_row.to_account_id, NEW.creation_time, NEW.value);

    END LOOP;
    RETURN NULL;
  END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER PopulateTimelineForEachItemInsert AFTER INSERT ON item FOR EACH ROW EXECUTE PROCEDURE populateTimeline();

CREATE TRIGGER PopulateTimelineForEachItemUpdate AFTER UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE populateTimeline();

commit;