(most recent on top)

== Add expression index for recipient_group to directives (2019-10)

For each tag that is saved in the `aggregate_identifier` in the directives
table, an index is needed if fast substring searches shall be done.
Note that `intelmq-fody-backend` version>=0.6.4 offers those searches
for the event statistics.

The PostgreSQL extension `pg_trgm` is
packaged in `postgresql-contrib-9.5` for Ubuntu 16.04 LTS.

=== forward


CREATE EXTENSION pg_trgm;
CREATE INDEX directives_recipient_group_idx
          ON directives USING gist (
            (json_object(aggregate_identifier) ->> 'recipient_group')
            gist_trgm_ops
          );

=== backward

DROP INDEX directives_recipient_group_idx;
DROP EXTENSION pg_trgm CASCADE;


== Directive Insertion time-stamp

=== forward

ALTER TABLE directives ADD COLUMN inserted_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE directives ALTER COLUMN inserted_at SET DEFAULT CURRENT_TIMESTAMP;
UPDATE directives
   SET inserted_at = (SELECT "time.observation" FROM events
                      WHERE id = events_id)
 WHERE inserted_at IS NULL;
ALTER TABLE directives ALTER COLUMN inserted_at SET NOT NULL;


=== backward

ALTER TABLE DROP COLUMN inserted_at;


== Adapt directives_grouping_idx to actually used grouping columns

=== forward

DROP INDEX directives_grouping_idx;
CREATE INDEX directives_grouping_idx
          ON directives (recipient_address, template_name,
                         notification_format, event_data_format,
                         aggregate_identifier);

== backward

DROP INDEX directives_grouping_idx;
CREATE INDEX directives_grouping_idx
          ON directives (medium, recipient_address, template_name,
                         notification_format, event_data_format,
                         aggregate_identifier, endpoint);


== New notification handling

TODO


== adding ticket_number #28

=== forward

CREATE TABLE ticket_day (
	initialized_for_day DATE
);
GRANT SELECT, UPDATE ON ticket_day TO eventdb_send_notifications;

ALTER TABLE notifications ALTER COLUMN intelmq_ticket TYPE VARCHAR(18);

DROP SEQUENCE intelmq_ticket_seq;
CREATE SEQUENCE intelmq_ticket_seq MINVALUE 10000001;
ALTER SEQUENCE intelmq_ticket_seq OWNER TO eventdb_send_notifications;

=== backwards
DROP SEQUENCE intelmq_ticket_seq;
CREATE SEQUENCE intelmq_ticket_seq;
GRANT USAGE ON intelmq_ticket_seq TO eventdb_send_notifications;

DROP TABLE ticket_day;

-- will only work if all old entries can still be converted
ALTER TABLE notifications ALTER COLUMN intelmq_ticket TYPE BIGINT;

