Files

45 lines
2.8 KiB
SQL

-- SPDX-License-Identifier: GPL-3.0-or-later
-- Review/export schema; contains no local cache paths or emulator code.
PRAGMA foreign_keys=ON;
CREATE TABLE metadata(key TEXT PRIMARY KEY,value TEXT NOT NULL);
CREATE TABLE title(id TEXT PRIMARY KEY,name TEXT NOT NULL,identity_status TEXT NOT NULL);
CREATE TABLE package(sha256 TEXT PRIMARY KEY,byte_size INTEGER NOT NULL CHECK(byte_size>=0));
CREATE TABLE release(
id TEXT PRIMARY KEY,title_id TEXT NOT NULL REFERENCES title(id),name TEXT NOT NULL,
category TEXT NOT NULL,media_type TEXT NOT NULL,content_role TEXT NOT NULL,
source_url TEXT NOT NULL UNIQUE,modified_at TEXT,modified_precision TEXT,modified_timezone TEXT,
package_sha256 TEXT NOT NULL REFERENCES package(sha256),language_status TEXT NOT NULL,
metadata_json TEXT NOT NULL CHECK(json_valid(metadata_json)));
CREATE TABLE member(
release_id TEXT NOT NULL REFERENCES release(id),path TEXT NOT NULL,sha256 TEXT NOT NULL,
byte_size INTEGER NOT NULL,kind TEXT NOT NULL,role TEXT NOT NULL,
PRIMARY KEY(release_id,path));
CREATE TABLE download_location(
package_sha256 TEXT NOT NULL REFERENCES package(sha256),url TEXT NOT NULL,priority INTEGER NOT NULL,
PRIMARY KEY(package_sha256,url));
CREATE TABLE asset(id TEXT PRIMARY KEY,name TEXT NOT NULL,kind TEXT NOT NULL,source_url TEXT NOT NULL,
sha256 TEXT,byte_size INTEGER,credit TEXT NOT NULL);
CREATE TABLE asset_delivery(
asset_id TEXT PRIMARY KEY REFERENCES asset(id),manual_url TEXT,thumbnail_url TEXT,
thumbnail_sha256 TEXT,thumbnail_byte_size INTEGER,priority INTEGER NOT NULL);
CREATE TABLE release_asset(
release_id TEXT NOT NULL REFERENCES release(id),asset_id TEXT NOT NULL REFERENCES asset(id),
status TEXT NOT NULL,reason TEXT NOT NULL,PRIMARY KEY(release_id,asset_id));
CREATE TABLE setup(
release_id TEXT PRIMARY KEY REFERENCES release(id),status TEXT NOT NULL,
configuration_json TEXT NOT NULL CHECK(json_valid(configuration_json)),
selected_members_json TEXT NOT NULL CHECK(json_valid(selected_members_json)),
command TEXT,instructions TEXT NOT NULL,reasons_json TEXT NOT NULL CHECK(json_valid(reasons_json)),
validation_json TEXT NOT NULL CHECK(json_valid(validation_json)));
CREATE TABLE source_hint(
id TEXT PRIMARY KEY,release_id TEXT NOT NULL REFERENCES release(id),page_url TEXT NOT NULL,
page_sha256 TEXT NOT NULL,source_name TEXT NOT NULL,raw_json TEXT NOT NULL CHECK(json_valid(raw_json)),
resolution TEXT NOT NULL);
CREATE INDEX release_title ON release(title_id);
CREATE INDEX release_category ON release(category);
CREATE INDEX setup_status ON setup(status);
CREATE VIEW browse AS SELECT r.*,t.name AS title,s.status,s.command,s.instructions,
s.configuration_json,s.selected_members_json,s.reasons_json,s.validation_json
FROM release r JOIN title t ON t.id=r.title_id JOIN setup s ON s.release_id=r.id;
PRAGMA user_version=1;