xfstestsdb/xfstestsdb/scripts/xfstestsdb.sql

/* Copyright 2023 (c) Anna Schumaker */

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;


/************************************************************
 *                                                          *
 *        Table for storing individual xfstests runs        *
 *                                                          *
 ************************************************************/

CREATE TABLE xfstests_runs (
	runid INTEGER PRIMARY KEY,
	timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	device TEXT NOT NULL
);

CREATE VIEW tagged_runs AS
	SELECT xfstests_runs.rowid as runid, device,
		datetime(xfstests_runs.timestamp, 'localtime') as timestamp,
		tags.tag as tag, xunits.name as xunit
	FROM xfstests_runs
	LEFT JOIN tags USING (runid)
	LEFT JOIN xunits USING (runid);


/*****************************************
 *                                       *
 *        Table for managing tags        *
 *                                       *
 *****************************************/

CREATE TABLE tags (
	runid INTEGER NOT NULL,
	tag TEXT NOT NULL,
	PRIMARY KEY (runid, tag),
	FOREIGN KEY (runid) REFERENCES xfstests_runs (runid)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);


/************************************************
 *                                              *
 *        Table for managing xunit files        *
 *                                              *
 ************************************************/

CREATE TABLE xunits (
	xunitid INTEGER PRIMARY KEY,
	runid INTEGER NOT NULL,
	timestamp TIMESTAMP,
	name TEXT NOT NULL,
	hostname TEXT NOT NULL,
	tests INTEGER NOT NULL,
	failed INTEGER NOT NULL,
	skipped INTEGER NOT NULL,
	time INTEGER NOT NULL,
	UNIQUE (runid, name),
	FOREIGN KEY (runid) REFERENCES xfstests_runs (runid)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

CREATE VIEW xunits_view AS
	SELECT runid, device, name, hostname, tests, failed, skipped, time,
		(tests - (skipped + failed)) as passed,
		datetime(xunits.timestamp, 'localtime') as timestamp
	FROM xunits
	JOIN xfstests_runs USING (runid);


/*****************************************************
 *                                                   *
 *        Table for managing xunit properties        *
 *                                                   *
 *****************************************************/

CREATE TABLE xunit_properties (
	propid INTEGER PRIMARY KEY,
	key TEXT NOT NULL,
	value TEXT NOT NULL,
	UNIQUE (key, value)
);

CREATE TABLE link_xunit_props (
	xunitid INTEGER NOT NULL,
	propid INTEGER NOT NULL,
	FOREIGN KEY (xunitid) REFERENCES xunits (xunitid)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (propid) REFERENCES xunit_properties (propid),
	PRIMARY KEY (xunitid, propid)
);

CREATE VIEW xunit_properties_view AS
	SELECT runid, xunitid, name as xunit, key, value
	FROM xunits
	JOIN link_xunit_props USING (xunitid)
	JOIN xunit_properties ON link_xunit_props.propid = xunit_properties.rowid;

CREATE TRIGGER insert_xunit_properties
	INSTEAD OF INSERT ON xunit_properties_view
	BEGIN
		INSERT OR IGNORE INTO xunit_properties (key, value)
			VALUES (NEW.key, NEW.value);
		INSERT INTO link_xunit_props (xunitid, propid)
			VALUES ((SELECT xunitid FROM xunits
				WHERE runid = NEW.runid AND name = NEW.xunit),
				(SELECT rowid FROM xunit_properties
				 WHERE key = NEW.key AND value = NEW.value));
	END;

CREATE TRIGGER cleanup_xunit_properties
	AFTER DELETE ON link_xunit_props
	BEGIN
		DELETE FROM xunit_properties WHERE NOT EXISTS
			(SELECT 1 FROM link_xunit_props
			 WHERE propid = xunit_properties.rowid);
	END;


/***********************************************
 *                                             *
 *        Table for managing test cases        *
 *                                             *
 ***********************************************/

CREATE TABLE messages (
	messageid INTEGER PRIMARY KEY,
	message TEXT NOT NULL,
	UNIQUE (message)
);

CREATE TABLE testcases (
	xunitid INTEGER NOT NULL,
	testcase TEXT NOT NULL,
	status TEXT NOT NULL,
	time INTEGER NOT NULL,
	messageid INTEGER DEFAULT NULL,
	stdoutid INTEGER DEFAULT NULL,
	stderrid INTEGER DEFAULT NULL,
	PRIMARY KEY (xunitid, testcase),
	FOREIGN KEY (xunitid) REFERENCES xunits (xunitid)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (messageid) REFERENCES messages (messageid),
	FOREIGN KEY (stdoutid) REFERENCES messages (messageid),
	FOREIGN KEY (stderrid) REFERENCES messages (messageid),
	CHECK (status IN ("passed", "failure", "skipped"))
);

CREATE VIEW testcases_view AS
	SELECT runid, xunitid, device, xunits.name as xunit, testcase, status,
		testcases.time, msg.message as message,
		stdout.message as stdout, stderr.message as stderr
	FROM testcases
	JOIN xunits USING (xunitid)
	JOIN xfstests_runs USING (runid)
	LEFT JOIN messages msg USING (messageid)
	LEFT JOIN messages stdout ON stdout.messageid = testcases.stdoutid
	LEFT JOIN messages stderr ON stderr.messageid = testcases.stderrid;

CREATE TRIGGER insert_testcase
	INSTEAD OF INSERT ON testcases_view
	BEGIN
		INSERT OR IGNORE INTO messages (message) VALUES (NEW.message);
		INSERT OR IGNORE INTO messages (message) VALUES (NEW.stdout);
		INSERT OR IGNORE INTO messages (message) VALUES (NEW.stderr);
		INSERT INTO testcases (xunitid, testcase, status, time,
					messageid, stdoutid, stderrid)
			VALUES ((SELECT xunitid FROM xunits
				 WHERE runid = NEW.runid AND name = NEW.xunit),
				NEW.testcase, NEW.status, NEW.time,
				(SELECT messageid FROM messages WHERE message = NEW.message),
				(SELECT messageid FROM messages WHERE message = NEW.stdout),
				(SELECT messageid FROM messages WHERE message = NEW.stderr));
	END;

CREATE TRIGGER cleanup_testcase_messages
	AFTER DELETE ON testcases
	BEGIN
		DELETE FROM messages WHERE NOT EXISTS
			(SELECT 1 FROM testcases WHERE
			 messageid = messages.rowid
				OR stdoutid = messages.rowid
				OR stderrid = messages.rowid);
	END;