Test-Mapper/lib/Test/Mapper/DB.pm

package Test::Mapper::DB;

use strict;
use warnings;

use DBI;
use File::Touch;

# ABSTRACT: Persistent storage for what test maps where for what build.

# We hold possibly multiple DBHes to support testing multiple products.
my $dbh = {};

sub dbh {
    my ( $dbname ) = @_;

    return $dbh->{$dbname} if $dbh->{$dbname};

    File::Touch::touch($dbname) unless -f $dbname;
    my $db = DBI->connect( "dbi:SQLite:dbname=$dbname", "", "" );

    # Turn on fkeys before schema creation so FK constraints take effect
    $db->do("PRAGMA foreign_keys = ON") or die "Could not enable foreign keys";

    # Turn on WAL mode for concurrent reads
    $db->do("PRAGMA journal_mode = WAL") or die "Could not enable WAL mode";

    my $schema = join( "", readline(DATA) );
    if ($schema) {
        $db->{sqlite_allow_multiple_statements} = 1;
        $db->do($schema) or die "Could not ensure database consistency: " . $db->errstr;
        $db->{sqlite_allow_multiple_statements} = 0;
    }

    $dbh->{$dbname} = $db;

    return $db;
}

1;

__DATA__

-- Should be pretty self-explanatory, DRY
CREATE TABLE IF NOT EXISTS product (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS environment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    environment TEXT NOT NULL,
    product_id INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    UNIQUE(environment, product_id) ON CONFLICT IGNORE
);

CREATE TABLE IF NOT EXISTS build (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    build TEXT NOT NULL,
    product_id INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    UNIQUE(build, product_id) ON CONFLICT IGNORE
);

CREATE TABLE IF NOT EXISTS route (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    route TEXT NOT NULL,
    product_id INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    UNIQUE(route, product_id) ON CONFLICT IGNORE
);

CREATE TABLE IF NOT EXISTS test (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    product_id INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    UNIQUE(filename, product_id) ON CONFLICT IGNORE
);

-- run_by is used to coordinate running, what hasn't been taken yet etc
-- result is a result code that visualization programs can use for historical charts
-- The idea here is that 'people just wanna see what failed', and this helps narrow that down
-- start/finish is so you can figure historical data/charts
CREATE TABLE IF NOT EXISTS test_for_build (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    build_id INTEGER NOT NULL REFERENCES build(id) ON DELETE CASCADE,
    test_id  INTEGER NOT NULL REFERENCES test(id) ON DELETE CASCADE,
    run_by   TEXT,
    result   INTEGER,
    start    INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finish   INTEGER DEFAULT NULL,
    UNIQUE(build_id, test_id) ON CONFLICT ROLLBACK
);

-- Actually do the mapping of test -> what files changing trigger its running
-- One tends to use differing resources on different environments.
-- It is however assumed that the tests (or the cron inserting resources/tests) discriminates in this regard.
CREATE TABLE IF NOT EXISTS resource (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    environment_id INTEGER NOT NULL REFERENCES environment(id) ON DELETE CASCADE,
    filename       TEXT NOT NULL,
    UNIQUE(environment_id, filename) ON CONFLICT IGNORE
);

-- Indexed by resource_id, as that's how a build is done "I have x changed resources, wat run?"
CREATE TABLE IF NOT EXISTS tested_by (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    resource_id INTEGER NOT NULL REFERENCES resource(id) ON DELETE CASCADE,
    test_id     INTEGER NOT NULL REFERENCES test(id) ON DELETE CASCADE,
    UNIQUE(resource_id, test_id) ON CONFLICT IGNORE
);

-- Lifecycle:
-- Have list of files from `git log`
-- Run analysis script that returns hash test_file => [source_file, ...]
-- INSERT OR IGNORE INTO tested_by (resource_id, test_id) VALUES (...);
CREATE VIEW IF NOT EXISTS to_run AS
    SELECT
        t.filename   AS test_file,
        t.id         AS test_id,
        r.filename   AS source_file,
        e.environment AS environment
    FROM resource AS r
    JOIN tested_by AS tb ON tb.resource_id = r.id
    JOIN test AS t ON t.id = tb.test_id
    JOIN environment AS e ON e.id = r.environment_id
;

-- Build run summary: what tests were queued/run/completed for each build.
-- Use: SELECT * FROM run WHERE build='abc123' AND result IS NULL (unclaimed);
--      SELECT * FROM run WHERE build='abc123' AND result IS NOT NULL (finished);
CREATE VIEW IF NOT EXISTS run AS
    SELECT
        p.product,
        b.build,
        t.filename  AS test_file,
        tfb.run_by,
        tfb.result,
        tfb.start,
        tfb.finish
    FROM test_for_build AS tfb
    JOIN build AS b ON b.id = tfb.build_id
    JOIN test  AS t ON t.id = tfb.test_id
    JOIN product AS p ON p.id = b.product_id
;