65 lines
2.1 KiB
SQL
65 lines
2.1 KiB
SQL
/* Copyright 2023 (c) Anna Schumaker */
|
|
|
|
PRAGMA user_version = 2;
|
|
|
|
/*
|
|
* The original `cleanup_xunit_properties` trigger was very slow on
|
|
* large databases. We can do a few things to improve on it:
|
|
* 1. Add an index on the link_xunits_props table to make it easier
|
|
* to check if specific properties are still in use.
|
|
* 2. Rewrite the `cleanup_xunit_properties` trigger to only operate
|
|
* on the propid associated with the deleted xunit.
|
|
*/
|
|
CREATE INDEX link_xunit_props_propid_index ON link_xunit_props (propid);
|
|
|
|
DROP TRIGGER cleanup_xunit_properties;
|
|
CREATE TRIGGER cleanup_xunit_properties
|
|
AFTER DELETE ON link_xunit_props
|
|
BEGIN
|
|
DELETE FROM xunit_properties
|
|
WHERE (propid = OLD.propid)
|
|
AND NOT EXISTS (SELECT 1 FROM link_xunit_props
|
|
WHERE propid = xunit_properties.propid);
|
|
END;
|
|
|
|
/*
|
|
* The original `cleanup_testcase_messages` trigger was very slow. We can
|
|
* do a few things to improve upon it:
|
|
* 1. Add indexes on the testcases table to make it easier to check
|
|
* if specific messageids are still in use.
|
|
* 2. Rewrite the `cleanup_testcase_messages` trigger to only operate
|
|
* on the messageids associated with the deleted testcase.
|
|
*/
|
|
CREATE INDEX testcases_messageid_index ON testcases (messageid);
|
|
CREATE INDEX testcases_stdoutid_index ON testcases (stdoutid);
|
|
CREATE INDEX testcases_stderrid_index ON testcases (stderrid);
|
|
|
|
DROP TRIGGER cleanup_testcase_messages;
|
|
CREATE TRIGGER cleanup_testcase_messages
|
|
AFTER DELETE ON testcases
|
|
BEGIN
|
|
DELETE FROM messages
|
|
WHERE (messageid = OLD.messageid
|
|
OR messageid = OLD.stdoutid
|
|
OR messageid = OLD.stderrid)
|
|
AND NOT EXISTS
|
|
(SELECT 1 FROM testcases WHERE
|
|
messageid = messages.messageid
|
|
OR stdoutid = messages.messageid
|
|
OR stderrid = messages.messageid);
|
|
END;
|
|
|
|
|
|
/*
|
|
* Create a view on the xfstestsdb_runs to find garbage collection candidates.
|
|
*/
|
|
CREATE VIEW xfstests_gc_runs AS
|
|
SELECT runid
|
|
FROM xfstests_runs
|
|
WHERE NOT EXISTS (SELECT 1 FROM testcases
|
|
JOIN xunits USING (xunitid)
|
|
WHERE runid = xfstests_runs.runid)
|
|
OR (timestamp < datetime('now', '-180 days')
|
|
AND NOT EXISTS (SELECT 1 FROM tags
|
|
WHERE runid = xfstests_runs.runid));
|