CREATE SCHEMA IF NOT EXISTS test_infra; SET SCHEMA 'test_infra'; CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER LANGUAGE plpgsql AS $func$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END $func$; -- tbl edge_jobs CREATE TABLE IF NOT EXISTS edge_jobs ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), elapsed BIGINT, started TIMESTAMPTZ, finished TIMESTAMPTZ, version TEXT, path TEXT, job TEXT, workflow TEXT, number TEXT CONSTRAINT unique_number UNIQUE, -- runID + unique string run TEXT, -- runID tests_run INTEGER, tests_failed INTEGER, passed BOOLEAN, repos TEXT, repo_commit TEXT ); CREATE OR REPLACE TRIGGER edge_jobs_timestamp BEFORE UPDATE ON edge_jobs FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp(); -- tbl edge_job_metadatas CREATE TABLE IF NOT EXISTS edge_job_metadatas ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), edge_job UUID NOT NULL REFERENCES edge_jobs(id) ON DELETE CASCADE, key TEXT, value TEXT ); CREATE INDEX IF NOT EXISTS idx_edge_job_fkey ON edge_job_metadatas (edge_job); CREATE INDEX IF NOT EXISTS idx_edge_job_fkey_inc_key_value ON edge_job_metadatas (edge_job) INCLUDE (key, value); CREATE OR REPLACE TRIGGER edge_job_metadatas_timestamp BEFORE UPDATE ON edge_job_metadatas FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp(); -- tbl edge_job_tests CREATE TABLE IF NOT EXISTS edge_job_tests ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), edge_job UUID NOT NULL REFERENCES edge_jobs(id) ON DELETE CASCADE, name TEXT, suite TEXT, time BIGINT, -- convert to some other time / duration type? failed BOOLEAN, failure_text TEXT ); CREATE INDEX IF NOT EXISTS idx_edge_job_fkey ON edge_job_tests (edge_job); CREATE OR REPLACE TRIGGER edge_job_tests_timestamp BEFORE UPDATE ON edge_job_tests FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();