33 lines
2.0 KiB
SQL
33 lines
2.0 KiB
SQL
 
|
-- 0012_archive: phase 10. The archive location (Gitea issue 7). Runs as ps_migrate; 0001's default privileges give
|
||
|
|
-- ps_app SELECT, INSERT, UPDATE, DELETE on the new table.
|
||
|
|
|
||
|
|
-- One row per filed seal to copy to the archive folder. The archive-copy job queues every unbroken seal with a data
|
||
|
|
-- book that has no row yet, then copies the pending rows and the failed rows that are due (next_try_at).
|
||
|
|
-- folder is the copy's folder relative to ARCHIVE_ROOT, set when the copy is done. manifest_sha256 is the sha256 of
|
||
|
|
-- the manifest.json written there. A copy is never removed; a refile after a reopen has its own seal and row.
|
||
|
|
CREATE TABLE archive_copies (
|
||
|
|
id bigserial PRIMARY KEY,
|
||
|
|
seal_id bigint NOT NULL UNIQUE REFERENCES packet_seals (id),
|
||
|
|
packet_id bigint NOT NULL REFERENCES packets (id),
|
||
|
|
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'done', 'failed')),
|
||
|
|
attempts int NOT NULL DEFAULT 0,
|
||
|
|
last_error text,
|
||
|
|
next_try_at timestamptz,
|
||
|
|
folder text,
|
||
|
|
manifest_sha256 text,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
done_at timestamptz,
|
||
|
|
CHECK (status <> 'done' OR (folder IS NOT NULL AND done_at IS NOT NULL))
|
||
|
|
);
|
||
|
|
CREATE INDEX archive_copies_due ON archive_copies (next_try_at, id) WHERE status <> 'done';
|
||
|
|
CREATE INDEX archive_copies_packet ON archive_copies (packet_id);
|
||
|
|
|
||
|
|
-- The job, so "Retry now" and FILE's request are accepted before the worker has started once. The notification
|
||
|
|
-- rule for archive_failed is seeded by the code (notify/seeding.py) like every other event type.
|
||
|
|
INSERT INTO job_schedule (job) VALUES ('archive-copy') ON CONFLICT (job) DO NOTHING;
|
||
|
|
|
||
|
|
-- Explicit, after the default privileges: the app reads, queues and updates copies; it never deletes one.
|
||
|
|
REVOKE DELETE, TRUNCATE ON archive_copies FROM ps_app;
|
||
|
|
GRANT SELECT, INSERT, UPDATE ON archive_copies TO ps_app;
|
||
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ps_app;
|