29 lines
1.9 KiB
SQL
29 lines
1.9 KiB
SQL
PRAGMA foreign_keys=ON;
|
|
CREATE TABLE IF NOT EXISTS experiments (
|
|
id TEXT PRIMARY KEY CHECK(length(id)=32), owner TEXT NOT NULL, name TEXT NOT NULL,
|
|
spec TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS experiments_owner_updated ON experiments(owner, updated_at DESC);
|
|
CREATE TABLE IF NOT EXISTS jobs (
|
|
id TEXT PRIMARY KEY CHECK(length(id)=32), experiment_id TEXT, owner TEXT NOT NULL,
|
|
name TEXT NOT NULL, spec TEXT NOT NULL, kind TEXT NOT NULL DEFAULT 'model' CHECK(kind IN ('model','planet')), status TEXT NOT NULL CHECK(status IN ('queued','running','completed','failed','cancelled','interrupted')),
|
|
phase TEXT NOT NULL DEFAULT 'queued', progress REAL NOT NULL DEFAULT 0,
|
|
completed_cycles INTEGER NOT NULL DEFAULT 0, total_cycles INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL, started_at TEXT, finished_at TEXT, updated_at TEXT NOT NULL,
|
|
error TEXT, cancel_requested INTEGER NOT NULL DEFAULT 0, pid INTEGER, log_offset INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS map_versions (
|
|
id TEXT PRIMARY KEY CHECK(length(id)=32), owner TEXT NOT NULL, field TEXT NOT NULL,
|
|
resolution TEXT NOT NULL, name TEXT NOT NULL, rows INTEGER NOT NULL, cols INTEGER NOT NULL,
|
|
digest TEXT NOT NULL CHECK(length(digest)=64), data BLOB NOT NULL, created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS map_versions_owner_resolution ON map_versions(owner,resolution,created_at DESC);
|
|
CREATE TABLE IF NOT EXISTS job_maps (
|
|
job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE, field TEXT NOT NULL,
|
|
version_id TEXT NOT NULL, digest TEXT NOT NULL CHECK(length(digest)=64), data BLOB NOT NULL,
|
|
PRIMARY KEY(job_id,field)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS jobs_owner_created ON jobs(owner, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS jobs_state_created ON jobs(status, created_at);
|
|
CREATE INDEX IF NOT EXISTS jobs_owner_state ON jobs(owner, status, created_at DESC);
|