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

package Test::Mapper::DB;

use strict;
use warnings;

# 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 ) = @_;
    my $schema = join("\n",(readline(DATA)));

    $dbh //= {};
    return $dbh->{$dbname} if $dbh->{$dbname};
    File::Touch::touch($dbname) unless -f $dbname;
    my $db = DBI->connect( "dbi:SQLite:dbname=$dbname", "", "" );

    if ($schema) {
        die "No such schema file '$schema' !" unless -f $schema;
        my $qq = File::Slurper::read_text($schema);
        $db->{sqlite_allow_multiple_statements} = 1;
        $db->do($qq) or die "Could not ensure database consistency: " . $db->errstr;
        $db->{sqlite_allow_multiple_statements} = 0;
    }

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

    # Turn on fkeys
    $db->do("PRAGMA foreign_keys = ON") or die "Could not enable foreign keys";

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

    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 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 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 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 test_for_build (
    build_id INTEGER PRIMARY KEY REFERENCES build(id),
    test_id  INTEGER REFERENCES test(id),
    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 resource (
    resource_id INTEGER PRIMARY KEY AUTOINCREMENT,
    environment_id INTEGER NOT NULL REFERENCES environment(id),
    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 tested_by (
    resource_id INTEGER PRIMARY KEY REFERENCES resource(id),
    test_id INTEGER NOT NULL REFERENCES test(id),
    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 INTO to_run (test_file,source_file, environment) VALUES (...);
CREATE VIEW to_run AS SELECT (
     t.filename AS test_file,
     t.test_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.resource_id
JOIN test AS t ON t.id=tb.test_id
JOIN environment ON e.id=r.environment_id
;

-- TODO: make writable view for above intended for cron dumpage

-- Lifecycle:
-- Cron dumps rows into resource/tested_by, when time comes to run we grab the list of stuff:
-- SELECT test_file FROM to_run WHERE source_file IN (...) GROUP BY test_file;
-- We make a run
-- INSERT INTO run (product, environment, build, filename) VALUES SELECT ('fooProduct', 'MsWin32', 'someSha', test_file) FROM to_run WHERE source_file IN (...) GROUP BY test_file;
-- We run a test
-- UPDATE run SET run_by='processid:90210' WHERE product='fooProduct' AND environment='MsWin32' AND build='someSha' AND test_file='actually_being_run.t';
-- It completes. The end.
-- UPDATE run SET result=1, finish=CURRENT_TIMESTAMP WHERE ...
CREATE VIEW run AS SELECT (
    p.product,
    e.environment,
    b.build,
    r.route,
    t.filename,
    tb.run_by,
    tb.result
) FROM product AS p
JOIN build AS b ON p.id=b.product_id
JOIN test_for_build AS tb ON b.id=tb.build_id
JOIN environment AS e on p.id=e.product_id
JOIN route AS r ON p.id=r.product_id
JOIN test AS t ON tb.test_id=t.id
;

-- TODO: writable view to_run to make above lifecycle a real boy