128 lines
5.7 KiB
PL/PgSQL
128 lines
5.7 KiB
PL/PgSQL
-- pzsort schema
|
|
-- Run as: psql -U pzsort -d pzsort -f schema.sql
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- workshop_meta
|
|
-- Cheap, refreshed often via Steam ISteamRemoteStorage/GetPublishedFileDetails.
|
|
-- Keyed by Steam publishedfileid (text to avoid bigint surprises).
|
|
-- time_updated is the cache-invalidation key for mod_parsed.
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS workshop_meta (
|
|
workshop_id TEXT PRIMARY KEY,
|
|
title TEXT,
|
|
description TEXT,
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
creator_steamid TEXT,
|
|
time_created BIGINT, -- unix ts from Steam
|
|
time_updated BIGINT NOT NULL, -- unix ts; cache invalidation key
|
|
file_size BIGINT,
|
|
preview_url TEXT,
|
|
consumer_app_id INTEGER, -- 108600 for PZ
|
|
visibility INTEGER, -- 0=public, 1=friends, 2=private
|
|
banned BOOLEAN NOT NULL DEFAULT FALSE,
|
|
last_checked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS workshop_meta_last_checked_idx
|
|
ON workshop_meta (last_checked_at);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- mod_parsed
|
|
-- Expensive: requires DepotDownloader fetch. Only refreshed when
|
|
-- workshop_meta.time_updated changes vs parsed_at_time_updated.
|
|
-- One workshop item can yield N rows (multi-mod packages).
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS mod_parsed (
|
|
workshop_id TEXT NOT NULL REFERENCES workshop_meta(workshop_id) ON DELETE CASCADE,
|
|
mod_id TEXT NOT NULL, -- mod.info `id=`
|
|
name TEXT NOT NULL DEFAULT '',
|
|
category TEXT NOT NULL DEFAULT 'undefined',
|
|
requirements TEXT[] NOT NULL DEFAULT '{}',
|
|
load_after TEXT[] NOT NULL DEFAULT '{}',
|
|
load_before TEXT[] NOT NULL DEFAULT '{}',
|
|
incompatible_mods TEXT[] NOT NULL DEFAULT '{}',
|
|
load_first TEXT NOT NULL DEFAULT 'off',
|
|
load_last TEXT NOT NULL DEFAULT 'off',
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
maps TEXT[] NOT NULL DEFAULT '{}', -- map folder names
|
|
raw_mod_info TEXT, -- original file for debugging
|
|
version_min TEXT, -- e.g. 41.55
|
|
parsed_at_time_updated BIGINT NOT NULL, -- snapshot of workshop_meta.time_updated at parse
|
|
parsed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (workshop_id, mod_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS mod_parsed_mod_id_idx ON mod_parsed (mod_id);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- download_jobs
|
|
-- Work queue for the DepotDownloader worker. One job per workshop_id.
|
|
-- Worker dequeues (status='queued') ORDER BY priority DESC, created_at ASC.
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS download_jobs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workshop_id TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued', -- queued|downloading|parsing|done|failed
|
|
priority INTEGER NOT NULL DEFAULT 0, -- higher first
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
error TEXT,
|
|
requested_by TEXT, -- IP hash or user token; for rate limiting
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS download_jobs_dequeue_idx
|
|
ON download_jobs (status, priority DESC, created_at ASC)
|
|
WHERE status = 'queued';
|
|
|
|
CREATE INDEX IF NOT EXISTS download_jobs_workshop_idx
|
|
ON download_jobs (workshop_id);
|
|
|
|
-- Trigger: keep updated_at fresh
|
|
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS download_jobs_touch ON download_jobs;
|
|
CREATE TRIGGER download_jobs_touch
|
|
BEFORE UPDATE ON download_jobs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION touch_updated_at();
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- collections
|
|
-- Cache for ISteamRemoteStorage/GetCollectionDetails results.
|
|
-- Collections expand to N child workshop_ids; we cache that mapping.
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
collection_id TEXT PRIMARY KEY,
|
|
title TEXT,
|
|
child_workshop_ids TEXT[] NOT NULL DEFAULT '{}',
|
|
last_fetched_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- sort_requests
|
|
-- Optional: log of submitted sort jobs for debugging + abuse triage.
|
|
-- Not required for sort to function. Keep TTL short via a cron.
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS sort_requests (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
input_workshop_ids TEXT[] NOT NULL,
|
|
input_collection_id TEXT,
|
|
cache_hits INTEGER NOT NULL DEFAULT 0,
|
|
cache_misses INTEGER NOT NULL DEFAULT 0,
|
|
requested_by TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS sort_requests_created_idx
|
|
ON sort_requests (created_at);
|