runtests.sql

CREATE TEMPORARY TABLE test_runtests (
    id INT AUTO_INCREMENT PRIMARY KEY
    , proc_name TEXT
);

delimiter $$

DROP PROCEDURE IF exists startup $$
CREATE PROCEDURE startup()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('startup');
END $$

DROP PROCEDURE IF EXISTS setup $$
CREATE PROCEDURE setup()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('setup');
END $$

DROP PROCEDURE IF EXISTS setup_2 $$
CREATE PROCEDURE setup_2()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('setup_2');
END $$

DROP PROCEDURE IF EXISTS setup_3 $$
CREATE PROCEDURE setup_3()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('setup_3');
END $$

DROP PROCEDURE IF EXISTS test_foo $$
CREATE PROCEDURE test_foo()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('test_foo');
END $$

DROP PROCEDURE IF EXISTS test_bar $$
CREATE PROCEDURE TEST_BAR()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('test_bar');
END $$

DROP PROCEDURE IF EXISTS test_baz $$
CREATE PROCEDURE TEST_BAZ()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('test_baz');
END $$


DROP PROCEDURE IF EXISTS test_bam $$
CREATE PROCEDURE TEST_BAM()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('test_bam');
END $$

DROP PROCEDURE IF EXISTS teardown $$
CREATE PROCEDURE teardown()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('teardown');
END $$

DROP PROCEDURE IF EXISTS teardown_2 $$
CREATE PROCEDURE teardown_2()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('teardown_2');
END $$

DROP PROCEDURE IF EXISTS shutdown $$
CREATE PROCEDURE shutdown()
BEGIN
    INSERT INTO test_runtests (proc_name) VALUES ('shutdown');
END $$

DELIMITER ;


-- --------------------------------

DELIMITER //

DROP PROCEDURE IF EXISTS _run_proc_by_prefix //
CREATE PROCEDURE _run_proc_by_prefix(prefix_ TEXT)
BEGIN
    DECLARE no_more_proc_to_call BOOLEAN DEFAULT 0;
    DECLARE proc_to_call TEXT;

    DECLARE c CURSOR FOR
        SELECT ROUTINE_NAME
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE
            ROUTINE_TYPE = 'PROCEDURE'
            AND ROUTINE_SCHEMA = DATABASE()
            AND ROUTINE_NAME LIKE CONCAT(prefix_, '%')
        ORDER BY ROUTINE_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_proc_to_call = 1;

    OPEN c;
    l: LOOP
        FETCH c INTO proc_to_call;
        IF no_more_proc_to_call = 1 THEN
            LEAVE l;
        END IF;

        SET @call_sql = CONCAT('CALL ', proc_to_call, '()');
        PREPARE call_sql FROM @call_sql;
        EXECUTE call_sql;
        DEALLOCATE PREPARE call_sql;
    END LOOP;
    CLOSE c;
end //


DROP PROCEDURE IF exists runtests //
CREATE procedure runtests()
begin
    DECLARE no_more_test_to_call BOOLEAN DEFAULT 0;
    DECLARE test_to_call TEXT;
    DECLARE c CURSOR FOR
        SELECT ROUTINE_NAME
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE
            ROUTINE_TYPE = 'PROCEDURE'
            AND ROUTINE_SCHEMA = DATABASE()
            AND ROUTINE_NAME LIKE 'test%'
        ORDER BY ROUTINE_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_test_to_call = 1;

    CALL _run_proc_by_prefix('startup');

    OPEN c;
    l: LOOP
        FETCH c INTO test_to_call;
        IF no_more_test_to_call = 1 THEN
            LEAVE l;
        END IF;

        CALL _run_proc_by_prefix('setup');
        SET @call_sql = CONCAT('CALL ', test_to_call, '()');
        PREPARE call_sql FROM @call_sql;
        EXECUTE call_sql;
        DEALLOCATE PREPARE call_sql;
        call _run_proc_by_prefix('teardown');
    END LOOP;
    CLOSE c;

    CALL _run_proc_by_prefix('shutdown');
end //

DELIMITER ;


CALL runtests();

SELECT tap.eq(
    (SELECT COUNT(*) FROM test_runtests WHERE proc_name like 'startup%')
    , 1
    , 'startup was called exactly once'
);

SET @tmp = (
    SELECT COUNT(DISTINCT cnt)
    FROM (
        SELECT proc_name, COUNT(*) AS cnt
        FROM test_runtests
        WHERE proc_name LIKE 'setup%'
        GROUP BY proc_name
        ) t
    );

SELECT tap.eq(@tmp, 1, 'every setup was called equal number of times');

SET @tmp = (
    SELECT COUNT(DISTINCT cnt)
    FROM (
        SELECT proc_name, COUNT(*) AS cnt
        FROM test_runtests
        WHERE proc_name LIKE 'teardown%'
        GROUP BY proc_name
        ) t
    );

SELECT tap.eq(@tmp, 1, 'every teardown was called equal number of times');


/*
SELECT tap.eq(
    (SELECT COUNT(*) FROM test_runtests WHERE proc_name = 'setup')
    , (SELECT COUNT(*) FROM test_runtests WHERE proc_name = 'test')
    , 'every setup was called equal number of times as number of tests in suite'
);
*/
-- every teardown called equal number of times as number of tests in suite


SELECT tap.eq(
    (SELECT COUNT(*) FROM test_runtests WHERE proc_name LIKE 'shutdown%')
    , 1
    , 'shutdown was called exactly once'
);

SELECT tap.eq(
    (SELECT COUNT(*) FROM test_runtests WHERE proc_name LIKE 'shutdown%')
    , 1
    , 'shutdown was called exactly once'
);


create temporary table test_runtests2 like test_runtests;
insert into test_runtests2 select * from test_runtests;

SELECT tap.eq(
    (select distinct t2.proc_name
    from
        test_runtests AS t1
        join test_runtests2 AS t2 on t1.id = t2.id - 1
    where
        t1.proc_name = 'setup')
    , 'setup_2'
    , 'setup was called before setup_2');

SELECT tap.eq(
    (SELECT DISTINCT t2.proc_name
    FROM
        test_runtests AS t1
        JOIN test_runtests2 AS t2 on t1.id = t2.id - 1
    WHERE
        t1.proc_name = 'setup_2')
    , 'setup_3'
    , 'setup_2 was called before setup_3');

SELECT tap.eq(
    (SELECT DISTINCT t2.proc_name
    FROM
        test_runtests AS t1
        JOIN test_runtests2 AS t2 on t1.id = t2.id - 1
    WHERE
        t1.proc_name = 'teardown')
    , 'teardown_2'
    , 'teardown was called before teardown_2');


SELECT tap.eq(
    (SELECT COUNT(*)
    FROM
        INFORMATION_SCHEMA.ROUTINES AS isr
        LEFT JOIN test_runtests AS tr ON isr.ROUTINE_NAME = tr.proc_name
    WHERE
        ROUTINE_TYPE = 'PROCEDURE'
        AND ROUTINE_SCHEMA = DATABASE()
        AND ROUTINE_NAME LIKE 'test%'
        AND tr.proc_name IS NULL)
    , 0
    , 'no test was missed');

SELECT tap.eq(
    (SELECT 1
    FROM
        INFORMATION_SCHEMA.ROUTINES AS isr
        JOIN test_runtests AS tr ON isr.ROUTINE_NAME = tr.proc_name
    WHERE
        ROUTINE_TYPE = 'PROCEDURE'
        AND ROUTINE_SCHEMA = DATABASE()
        AND ROUTINE_NAME LIKE 'test%'
        AND tr.proc_name IS NULL
    HAVING COUNT(*) > 1)
    , NULL
    , 'every test was called exactly once');