Writing Epic tests

Tests are written as normal PL/pgSQL procedures (other languages may actually work with Epic functions, but I haven't tried it yet).

Test Requirements

There are only a couple of things to do to ensure they work well with the framework:

Always raise exceptions

Always raise exception at the end of test procs to rollback any changes. Even if the test passes, RAISE EXCEPTION '[OK]'. When you use any of the test.run_* functions to exercise your tests, they will complain if your test does not raise an exception. You may use the functions pass([msg]), fail([msg]), todo([msg]), skip([msg]) and finish(result[, msg]) instead of RAISE EXCEPTION if you like.

Put your test in the "test" schema

Your test name must be in the test schema or the testnames VIEW won't pick it up, which means `run_module` and `run_all` won't pick it up either.

Start your test name with "test_"

Your test name must start with "test_" or the testnames VIEW won't pick it up, which means `run_module` and `run_all` won't pick it up either. I like test_[schema]_[target proc] as a naming convention, but do what you like.

Include a module comment

Module comments allow you to group and run related tests together. To declare a given test as a member of a module, include a SQL comment following this form:

-- module: modulename

...replacing module_name with the name of your module. The names are up to you, and do not have to follow actual file names.

Example test

CREATE OR REPLACE FUNCTION test.test_inner_set_user_state() RETURNS VOID AS $$
-- module: test_users
DECLARE
  user_id   integer;
  user_rec  users%ROWTYPE;
BEGIN
  <<MAIN>>
  BEGIN
    -- Create dummy records
    INSERT INTO users (login_name) VALUES ('test1') RETURNING user_id INTO user_id;
    
    -- Run the proc
    PERFORM "inner".set_user_state(user_id);
    
    -- The proc MUST set users.state to 'active';
    SELECT INTO user_rec * FROM users WHERE user_id = user_id;
    PERFORM test.assert_equal(user_rec.state, 'active');
  END MAIN;

  -- ALWAYS RAISE EXCEPTION at the end of test procs to rollback!
  RAISE EXCEPTION '[OK]';
END;
$$ LANGUAGE plpgsql;

Assertion Functions

Epic.sql includes some functions to make tests easier to write (and shorter).

test.assert(assertion boolean, msg text)

This is the 'catch-all' to assert anything that can be evaluated to a boolean. For example:

PERFORM test.assert(substring(a from b), b||" not found in "||a);

If the given assertion evaluates to False, the given message is raised as a user exception (P0001). Otherwise, returns VOID. The assertion argument may not be NULL.

test.assert_void(call text)

Raises an exception if the call does not return void; otherwise, returns VOID. The given call may be a table, view, procedure call, or a full SELECT. This is something of a necessary evil; it would be nice to just use assert_equal, but plpgsql doesn't allow one to pass void arguments to a function.

test.assert_equal(elem_1 anyelement, elem_2 anyelement)

Raises an exception if elem_1 is not equal to elem_2; otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...".

test.assert_not_equal(elem_1 anyelement, elem_2 anyelement)

Raises an exception if elem_1 is equal to elem_2; otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...".

test.assert_greater_than(elem_1 anyelement, elem_2 anyelement)

Raises an exception if not (elem_1 > elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...".

test.assert_greater_than_or_equal(elem_1 anyelement, elem_2 anyelement)

Raises an exception if not (elem_1 >= elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...".

test.assert_less_than(elem_1 anyelement, elem_2 anyelement)

Raises an exception if not (elem_1 < elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...".

test.assert_less_than_or_equal(elem_1 anyelement, elem_2 anyelement)

Raises an exception if not (elem_1 <= elem_2); otherwise, returns VOID. The two arguments must be of the same type, although they may both be NULL. If they are not of the same type, you will receive "ERROR: invalid input syntax ...".

test.assert_column(call text, expected anyarray[, colname text])

Raises an exception if SELECT colname FROM call != expected; otherwise, returns VOID.

The call argument can be any table, view, or procedure that returns records.

The colname argument should be the name of a column in the given call string. If NULL or omitted, it will be taken from the first column of call's output.

The expected argument MUST be an array of the same type as colname. To pass an empty array, try {}::int[] (or whatever type you're comparing).

The call and expected arguments MUST be sorted in the same order. If the call results are not sorted already, you can usually append 'ORDER BY 1' to it to sort by the selected column.

Example:

PERFORM test.assert_column('get_favorite_user_ids(' || user_id || ') ORDER BY 1',
                           ARRAY[24, 10074, 87321],
                           'user_id');

test.assert_raises(call text[, errm text[, state text]])

Raises an exception if the given call does not raise errm (if provided) or state (if provided); otherwise, returns VOID.

The call argument can be any table, view, or procedure that returns records, or a full SELECT statement.

Example:

PERFORM test.assert_raises('get_transaction_by_id("a")', 'Bad argument', NULL);

If errm or state are NULL or omitted, that value will not be tested. This allows you to test by message alone (since the 5-char SQLSTATE values are cryptic), or trap a range of errors by SQLSTATE without regard for the exact message.

If you don't know the message you want to trap, call this function with errm = "" and state = "". The resultant error will tell you the SQLSTATE and SQLERRM that were raised.

test.assert_rows(call_1 text, call_2 text)

Asserts that two sets of rows have equal values. Neither call_1 nor call_2 need to be sorted. Either may include a trailing semicolon. Either may be a bare tablename, view, or procedure call (without a SELECT), or a full SELECT statement.

PERFORM test.assert_rows(
  'SELECT tablename FROM pg_tables;',
  'SELECT relname FROM pg_class where relkind = ''r''');

For testing a single row, it is common for the second arg to be sans a FROM clause, and simply SELECT values:

PERFORM test.assert_rows(
  'SELECT birth_year, birth_month, birth_day FROM users WHERE user_id = 144;',
  'SELECT 2007, 12, 31');

test.assert_values(call_1 text, call_2 text, columns text)

Raises an exception if SELECT columns FROM call_1 != SELECT columns FROM call_2.

Either call argument can be any table, view, or procedure that returns records, or a full SELECT statement.

Example:

row_1 := global('get_favorite_user_ids(' || user_id || ')')
PERFORM test.assert_values(row_1.tablename, 'users WHERE user_id = 355', 'last_name');

test.assert_empty((calls text[] | call text))

Raises an exception if the given calls(s) have any rows. In addition to testing proper DELETE functionality, this is also useful to ensure that you're not running the tests in a production environment. Since all tests roll back, you're not at risk for damaging your data; however, if you designed your tests to be run on empty tables, they may run excruciatingly slowly on large populated tables. The assert_empty test can help you document and avoid that scenario.

test.assert_not_empty((calls text[] | call text))

Raises an exception if the given calls(s) have no rows.

Call arguments

Several Epic functions take one or more 'call' args, each of which is a string (or array of strings) containing a SQL statement or fragment. Since no Postgres procedural languages allow us to pass anonymous records to functions, we must either make a separate version of, say, assert_rows for each and every table in our database under test (!) or we must pass SQL in strings to be EXECUTE'd. Epic chooses the latter. [It's possible to pass anonymous records to C functions, but why unleash yet another compiled library on an overworked world?]

By passing SQL strings, we actually gain quite a bit of expressiveness. The call arguments to Epic functions may each take any of:

• A complete SELECT statement: SELECT a, b, c FROM x WHERE y ORDER BY z
• A bare tablename or JOIN: users LEFT JOIN widgets ON users.user_id = widgets.creator_id
• A stored procedure call that returns a scalar or type: my.get_widget_dimensions(7364)
• A stored procedure call that returns a SETOF records: my.get_widgets_by_creator_id(11429)
• A SELECT <data> statement with no FROM clause: SELECT 1, 'Fred', '2008-01-15'::timestamptz

global variables

However, since we still cannot pass anonymous records, we have a conundrum:

DECLARE
  v_record1    record;
  v_record2    record;
BEGIN
  v_record1 := test._make_widget();
  v_record2 := my.get_widget(v_record1.widget_id);
  
  FOR colname in SELECT 'widget_id', 'creator_id', 
    'name', 'color', 'height', 'width', 'depth', 'price'
  LOOP
    PERFORM test.assert_values(v_record1, v_record2, colname);
  END LOOP;
  
  PERFORM test.assert_equal(v_record2.derived_value, 7348);

The above doesn't work, because we cannot pass the v_record variables (without knowing their concrete type). The v_record variables are essentially local to the function in which they are declared. We could pass the SQL to another function, but then we lose the ability to use the dot notation in our function for e.g. v_record2.derived_value). What we would like to have is either a way to pass them to another function, or to have "global" variables which we can access from any function. But plpgsql doesn't have a way to DECLARE variables outside of functions. What to do?

The solution Epic uses is TEMP tables. The above can be rewritten clumsily as:

DECLARE
  v_record1    record;
  v_record2    record;
BEGIN
  CREATE TEMP TABLE abc AS SELECT * FROM test._make_widget();
  v_record1 := SELECT * FROM abc;
  CREATE TEMP TABLE xyz AS SELECT * FROM my.get_widget(v_record1.widget_id);
  v_record2 := SELECT * FROM xyz;
  
  FOR colname in SELECT 'widget_id', 'creator_id', 
    'name', 'color', 'height', 'width', 'depth', 'price'
  LOOP
    PERFORM test.assert_values(abc, xyz, colname);
  END LOOP;
  
  PERFORM test.assert_equal(v_record2.derived_value, 7348);

But that's hardly better. Epic makes that much shorter and cleaner with its global function:

DECLARE
  g2           text;
  v_record1    record;
  v_record2    record;
BEGIN
  v_record1 := get(global('_make_widget()'));
  g2 := global('get_widget(' || v_record1.widget_id || ')');
  
  FOR colname in SELECT 'widget_id', 'creator_id', 
    'name', 'color', 'height', 'width', 'depth', 'price'
  LOOP
    PERFORM test.assert_values(v_record1.__name__, g2, colname);
  END LOOP;
  
  v_record2 := get(g2);
  PERFORM test.assert_equal(v_record2.derived_value, 7348);

test.global(call text, name text)

The global function takes a call argument (a SQL string); it both creates the TEMP table and returns its name. You can therefore pass it to any function which takes a call argument.

If the name argument is NULL or omitted, a default name is constructed (using a sequence). In most cases, you should use this mechanism. It's mostly provided so that you can re-use an existing global TEMP table and reduce consumption in a large test.

test.get(p_tablename text, p_offset int)

Returns a record (the first one, if p_offset is 0, NULL, or omitted) from the given table. The returned record includes an extra attribute, .__name__, which is the exact value of the p_tablename arg. This allows you to call get(global('function()')) and have access to the intermediate TEMP table name, and pass it to functions that take a 'call text' argument, such as assert_column, assert_values, and assert_empty (since no procedural languages support passing records as args).

test.constructor(tablename text)

Returns the SQL statement used to construct the given global table.

test.len(tablename text)

Return the number of rows in the given table.

test.iter(tablename text)

Return SQL to retrieve all rows in the given table.

test.attributes(tablename text)

Return * (minus system columns) FROM pg_attribute for the given table. If the given table has no attributes, an exception is raised.

test.typename(elem anyelement)

Return the typename of the given element.

Timing

There's a timing(call[, number]) function in Epic to help you test execution times. Simply pass it any call as a string; it will be EXECUTEd in a loop from 1..number, and the total time returned as an interval. If the number argument is NULL or missing, it defaults to 1,000,000.

You can use this in a regression test to assert that future changes to a stored procedure don't violate an SLA. You can also use it to create live tests so you know when, for example, a table has become so large that lookups on it are slower than you can live with. Finally, you can use it directly to test that an optimization change actually makes the given call faster. Of course, you can use EXPLAIN ANALYZE for any of those, too, but that's harder to automate.