Revision control
++ -- Drop all tables in reverse dependency order to avoid FK constraint errors.
++ DROP TABLE IF EXISTS qmlbench_results CASCADE;
++ DROP TABLE IF EXISTS graphics_apis CASCADE;
++ DROP TABLE IF EXISTS suites CASCADE;
++ DROP TABLE IF EXISTS hosts_qml CASCADE;
++ DROP TABLE IF EXISTS branches_qml CASCADE;
++ DROP TABLE IF EXISTS submodules_qml CASCADE;
++ DROP TABLE IF EXISTS benchmarks_qml CASCADE;
++ DROP TABLE IF EXISTS group_ids CASCADE;
++ DROP TABLE IF EXISTS run_details_qml CASCADE;
++ DROP TABLE IF EXISTS submodule_revisions CASCADE;
++ DROP TABLE IF EXISTS revisions_mm_groups CASCADE;
++ DROP TABLE IF EXISTS staging_qml_results CASCADE;
++
++ -- Lookup tables for run metadata.
++ CREATE TABLE branches_qml (
++ branch_qml_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ branch TEXT NOT NULL,
++ UNIQUE (branch)
++ );
++
++ CREATE TABLE hosts_qml (
++ host_qml_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ host_name TEXT NOT NULL,
++ UNIQUE (host_name)
++ );
++
++ CREATE TABLE graphics_apis (
++ graphics_api_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ graphics_api TEXT NOT NULL,
++ UNIQUE (graphics_api)
++ );
++
++ CREATE TABLE suites (
++ suite_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ suite TEXT NOT NULL,
++ UNIQUE (suite)
++ );
++
++ CREATE TABLE benchmarks_qml (
++ benchmark_qml_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ benchmark TEXT NOT NULL,
++ UNIQUE (benchmark)
++ );
++
++ -- Submodule revisions and grouping.
++ -- Each submodule_revision is a unique (submodule, sha1) pair.
++ -- A group represents a set of submodule_revisions that were used together in a run,
++ -- stored as a many-to-many relationship in revisions_mm_groups.
++ CREATE TABLE submodules_qml (
++ submodule_qml_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ submodule TEXT NOT NULL,
++ UNIQUE (submodule)
++ );
++
++ CREATE TABLE submodule_revisions (
++ submodule_revision_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ submodule_qml_id int REFERENCES submodules_qml(submodule_qml_id) NOT NULL,
++ sha1 TEXT NOT NULL,
++ UNIQUE (submodule_qml_id, sha1)
++ );
++
++ CREATE TABLE group_ids (
++ group_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY
++ );
++
++ CREATE TABLE revisions_mm_groups (
++ submodule_revision_id int REFERENCES submodule_revisions(submodule_revision_id) NOT NULL,
++ group_id int REFERENCES group_ids(group_id) NOT NULL,
++ UNIQUE (submodule_revision_id, group_id)
++ );
++
++ -- Unique combination of host, graphics API, branch, and submodule revision group for a run.
++ CREATE TABLE run_details_qml (
++ run_details_qml_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
++ host_qml_id int REFERENCES hosts_qml(host_qml_id) NOT NULL,
++ graphics_api_id int REFERENCES graphics_apis(graphics_api_id) NOT NULL,
++ branch_qml_id int REFERENCES branches_qml(branch_qml_id) NOT NULL,
++ group_id int REFERENCES group_ids(group_id) NOT NULL,
++ UNIQUE (host_qml_id, graphics_api_id, branch_qml_id, group_id)
++ );
++
++ -- Main results hypertable, partitioned and compressed by run_on.
++ CREATE TABLE qmlbench_results (
++ "run_on" TIMESTAMPTZ NOT NULL,
++ benchmark_qml_id int REFERENCES benchmarks_qml(benchmark_qml_id) NOT NULL,
++ run_details_qml_id int REFERENCES run_details_qml(run_details_qml_id) NOT NULL,
++ suite_id int REFERENCES suites(suite_id) NOT NULL,
++ mean DOUBLE PRECISION,
++ cov DOUBLE PRECISION,
++ std_dev DOUBLE PRECISION,
++ iterations int
++ ) WITH (
++ tsdb.hypertable,
++ tsdb.compress,
++ tsdb.partition_column='run_on',
++ tsdb.compress_segmentby='benchmark_qml_id, run_details_qml_id, suite_id',
++ tsdb.chunk_interval='180 day',
++ tsdb.compress_orderby = 'run_on DESC'
++ );
++
++ -- Staging table for bulk-loading raw results before normalization into qmlbench_results.
++ CREATE TABLE IF NOT EXISTS staging_qml_results (
++ run_on TIMESTAMPTZ,
++ benchmark TEXT,
++ suite TEXT,
++ run_details_qml_id INT,
++ iterations INT,
++ mean DOUBLE PRECISION,
++ cov DOUBLE PRECISION,
++ std_dev DOUBLE PRECISION
++ );
++
++ -- Automatically compress chunks older than 180 days.
++ SELECT add_compression_policy(
++ 'qmlbench_results',
++ INTERVAL '180 days',
++ if_not_exists => true
++ );
++
++ -- Seed a placeholder revision group for historic data that predates submodule tracking.
++ INSERT INTO submodules_qml (submodule) VALUES ('historic data');
++ INSERT INTO submodule_revisions (submodule_qml_id, sha1) VALUES (1, 'historic data');
++ INSERT INTO group_ids DEFAULT VALUES;
++ INSERT INTO revisions_mm_groups (group_id, submodule_revision_id) VALUES (1, 1);
++
++ -- Supports lookups on run_details_qml by the most common filter columns.
++ CREATE INDEX ON run_details_qml (host_qml_id, branch_qml_id, graphics_api_id);