proposal.note.sql

proposal.note.sql

-- Table: proposal.note

-- DROP TABLE proposal.note;

CREATE TABLE proposal.note
(
  id bigserial NOT NULL,
  reference uuid NOT NULL,
  proposal_reference uuid NOT NULL,
  entry_time timestamp with time zone NOT NULL,
  legacy_originator_id integer,
  type_id integer NOT NULL,
  content text NOT NULL,
  legacy_read_by integer,
  time_read timestamp with time zone,
  "from" character varying(100),
  "to" character varying(100),
  originator_reference uuid,
  read_by_reference uuid,
  from_organisation_reference uuid,
  to_organisation_reference uuid,
  CONSTRAINT pk_note PRIMARY KEY (id),
  CONSTRAINT note_proposal_reference_fkey FOREIGN KEY (proposal_reference)
      REFERENCES proposal.proposal (reference) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT note_reference_key UNIQUE (reference)
);

-- Index: proposal.note_entry_time_type_id_idx

-- DROP INDEX proposal.note_entry_time_type_id_idx;

CREATE INDEX note_entry_time_type_id_idx
  ON proposal.note
  USING btree
  (entry_time, type_id)
  WHERE legacy_read_by IS NULL;

-- Index: proposal.note_from_organisation_reference_entry_time_read_by_null_idx

-- DROP INDEX proposal.note_from_organisation_reference_entry_time_read_by_null_idx;

CREATE INDEX note_from_organisation_reference_entry_time_read_by_null_idx
  ON proposal.note
  USING btree
  (from_organisation_reference, entry_time)
  WHERE legacy_read_by IS NULL;

-- Index: proposal.note_proposal_reference_idx

-- DROP INDEX proposal.note_proposal_reference_idx;

CREATE INDEX note_proposal_reference_idx
  ON proposal.note
  USING btree
  (proposal_reference);

-- Index: proposal.note_proposal_reference_type_id_entry_time_idx

-- DROP INDEX proposal.note_proposal_reference_type_id_entry_time_idx;

CREATE INDEX note_proposal_reference_type_id_entry_time_idx
  ON proposal.note
  USING btree
  (proposal_reference, type_id, entry_time)
  WHERE legacy_read_by IS NULL;

-- Index: proposal.note_to_organisation_reference_entry_time_read_by_null_idx

-- DROP INDEX proposal.note_to_organisation_reference_entry_time_read_by_null_idx;

CREATE INDEX note_to_organisation_reference_entry_time_read_by_null_idx
  ON proposal.note
  USING btree
  (to_organisation_reference, entry_time)
  WHERE legacy_read_by IS NULL;

-- Index: proposal.note_type_id_entry_time_idx

-- DROP INDEX proposal.note_type_id_entry_time_idx;

CREATE INDEX note_type_id_entry_time_idx
  ON proposal.note
  USING btree
  (type_id, entry_time)
  WHERE legacy_read_by IS NULL;