108 lines
5.4 KiB
TypeScript
108 lines
5.4 KiB
TypeScript
import { BaseSchema } from '@adonisjs/lucid/schema'
|
|
|
|
/**
|
|
* Stored completeness for the "Complete" filter (CONCEPT.md D22); computing
|
|
* it per request took ~30 s on 34k releases. Triggers keep it current when
|
|
* files, par2 lists, sizes or announced totals change.
|
|
*/
|
|
export default class extends BaseSchema {
|
|
async up() {
|
|
this.defer(async (db) => {
|
|
await db.rawQuery('CREATE INDEX IF NOT EXISTS binaries_file_id_idx ON binaries (file_id)')
|
|
await db.rawQuery('CREATE INDEX IF NOT EXISTS releases_contents_trgm ON releases USING gin ((contents::text) gin_trgm_ops)')
|
|
await db.rawQuery('ALTER TABLE releases ADD COLUMN complete boolean NOT NULL DEFAULT false')
|
|
await db.rawQuery('CREATE INDEX releases_complete_idx ON releases (complete)')
|
|
|
|
// All files announced by [i/M] are there, or every par2-listed file
|
|
// (>= 64 KiB) is present by exact size or real name.
|
|
await db.rawQuery(`
|
|
CREATE OR REPLACE FUNCTION release_complete(rid bigint) RETURNS boolean STABLE LANGUAGE sql AS $$
|
|
SELECT coalesce((
|
|
SELECT CASE
|
|
WHEN r.file_total IS NOT NULL THEN r.file_count >= r.file_total
|
|
WHEN EXISTS (SELECT 1 FROM release_files rf WHERE rf.release_id = r.id) THEN NOT EXISTS (
|
|
SELECT 1 FROM release_files rf
|
|
WHERE rf.release_id = r.id AND rf.size >= 65536
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM files f JOIN binaries b ON b.file_id = f.id
|
|
WHERE f.release_id = r.id AND (b.size = rf.size OR f.real_name = rf.name)))
|
|
ELSE false END
|
|
FROM releases r WHERE r.id = rid), false)
|
|
$$`)
|
|
|
|
await db.rawQuery(`
|
|
CREATE OR REPLACE FUNCTION refresh_release_complete(rid bigint) RETURNS void LANGUAGE sql AS $$
|
|
UPDATE releases SET complete = release_complete(rid) WHERE id = rid AND complete IS DISTINCT FROM release_complete(rid)
|
|
$$`)
|
|
|
|
// files: after the totals trigger has updated file_count (trigger names sort alphabetically).
|
|
await db.rawQuery(`
|
|
CREATE OR REPLACE FUNCTION files_release_complete() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF TG_OP IN ('UPDATE', 'DELETE') AND OLD.release_id IS NOT NULL THEN PERFORM refresh_release_complete(OLD.release_id); END IF;
|
|
IF TG_OP IN ('UPDATE', 'INSERT') AND NEW.release_id IS NOT NULL THEN PERFORM refresh_release_complete(NEW.release_id); END IF;
|
|
RETURN NULL;
|
|
END $$`)
|
|
await db.rawQuery(`
|
|
CREATE TRIGGER files_zz_release_complete
|
|
AFTER INSERT OR DELETE OR UPDATE OF release_id, size, real_name ON files
|
|
FOR EACH ROW EXECUTE FUNCTION files_release_complete()`)
|
|
|
|
await db.rawQuery(`
|
|
CREATE OR REPLACE FUNCTION release_files_release_complete() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
PERFORM refresh_release_complete(NEW.release_id);
|
|
RETURN NULL;
|
|
END $$`)
|
|
await db.rawQuery(`
|
|
CREATE TRIGGER release_files_release_complete
|
|
AFTER INSERT ON release_files FOR EACH ROW EXECUTE FUNCTION release_files_release_complete()`)
|
|
|
|
// An exact size from content inspection can make a par2-listed file "present".
|
|
await db.rawQuery(`
|
|
CREATE OR REPLACE FUNCTION binaries_release_complete() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF NEW.file_id IS NOT NULL THEN
|
|
PERFORM refresh_release_complete(f.release_id) FROM files f WHERE f.id = NEW.file_id AND f.release_id IS NOT NULL;
|
|
END IF;
|
|
RETURN NULL;
|
|
END $$`)
|
|
await db.rawQuery(`
|
|
CREATE TRIGGER binaries_release_complete
|
|
AFTER UPDATE OF size ON binaries FOR EACH ROW
|
|
WHEN (OLD.size IS DISTINCT FROM NEW.size) EXECUTE FUNCTION binaries_release_complete()`)
|
|
|
|
await db.rawQuery(`
|
|
CREATE OR REPLACE FUNCTION releases_file_total_complete() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
PERFORM refresh_release_complete(NEW.id);
|
|
RETURN NULL;
|
|
END $$`)
|
|
await db.rawQuery(`
|
|
CREATE TRIGGER releases_file_total_complete
|
|
AFTER UPDATE OF file_total ON releases FOR EACH ROW
|
|
WHEN (OLD.file_total IS DISTINCT FROM NEW.file_total) EXECUTE FUNCTION releases_file_total_complete()`)
|
|
|
|
await db.rawQuery('UPDATE releases SET complete = release_complete(id) WHERE file_count > 0')
|
|
})
|
|
}
|
|
|
|
async down() {
|
|
this.defer(async (db) => {
|
|
await db.rawQuery('DROP TRIGGER IF EXISTS releases_file_total_complete ON releases')
|
|
await db.rawQuery('DROP TRIGGER IF EXISTS binaries_release_complete ON binaries')
|
|
await db.rawQuery('DROP TRIGGER IF EXISTS release_files_release_complete ON release_files')
|
|
await db.rawQuery('DROP TRIGGER IF EXISTS files_zz_release_complete ON files')
|
|
await db.rawQuery('DROP FUNCTION IF EXISTS releases_file_total_complete()')
|
|
await db.rawQuery('DROP FUNCTION IF EXISTS binaries_release_complete()')
|
|
await db.rawQuery('DROP FUNCTION IF EXISTS release_files_release_complete()')
|
|
await db.rawQuery('DROP FUNCTION IF EXISTS files_release_complete()')
|
|
await db.rawQuery('DROP FUNCTION IF EXISTS refresh_release_complete(bigint)')
|
|
await db.rawQuery('DROP FUNCTION IF EXISTS release_complete(bigint)')
|
|
await db.rawQuery('ALTER TABLE releases DROP COLUMN IF EXISTS complete')
|
|
await db.rawQuery('DROP INDEX IF EXISTS releases_contents_trgm')
|
|
await db.rawQuery('DROP INDEX IF EXISTS binaries_file_id_idx')
|
|
})
|
|
}
|
|
}
|