27 lines
1.4 KiB
MySQL
27 lines
1.4 KiB
MySQL
|
CREATE TABLE computer_file_poll_scopes (
|
||
|
|
computer_id TEXT NOT NULL REFERENCES computers(id) ON DELETE CASCADE,
|
||
|
|
provider_ref TEXT NOT NULL,
|
||
|
|
generation INTEGER NOT NULL CHECK(generation > 0),
|
||
|
|
bot_id TEXT NOT NULL REFERENCES bots(id) ON DELETE CASCADE,
|
||
|
|
last_polled TIMESTAMPTZ NOT NULL DEFAULT '-infinity',
|
||
|
|
lease_token TEXT,
|
||
|
|
lease_until TIMESTAMPTZ,
|
||
|
|
PRIMARY KEY(computer_id,provider_ref,generation,bot_id)
|
||
|
|
);
|
||
|
|
CREATE INDEX computer_file_poll_order ON computer_file_poll_scopes(computer_id,provider_ref,generation,last_polled,bot_id);
|
||
|
|
INSERT INTO computer_file_poll_scopes(computer_id,provider_ref,generation,bot_id)
|
||
|
|
SELECT DISTINCT computer_id,provider_ref,generation,bot_id FROM computer_file_dispatches;
|
||
|
|
-- Queue admission is atomic with the durable dispatch identity, including callers
|
||
|
|
-- on older API versions during a rolling upgrade.
|
||
|
|
CREATE FUNCTION enqueue_file_poll_scope() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
BEGIN
|
||
|
|
INSERT INTO computer_file_poll_scopes(computer_id,provider_ref,generation,bot_id)
|
||
|
|
VALUES (NEW.computer_id,NEW.provider_ref,NEW.generation,NEW.bot_id)
|
||
|
|
ON CONFLICT DO NOTHING;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$;
|
||
|
|
CREATE TRIGGER enqueue_file_poll_scope AFTER INSERT ON computer_file_dispatches
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION enqueue_file_poll_scope();
|
||
|
|
CREATE UNIQUE INDEX computer_file_poll_lease ON computer_file_poll_scopes(lease_token) WHERE lease_token IS NOT NULL;
|