Back To Main Page

1. User Guide

2. Overview

This guide aims to overview how the geography unit testing framework runs and how to create and use unit tests.

A document detailing basic configuration of the dtabase can be found here.

3. Tools

3.1. PostgreSQL

The version of postgresql avialable internally within the Bureau is 8.4.20 as of this writing. Hostility towards non-oracle, non-mysql databases from software approval groups within the bureau prevent newer versions from being implemented.

3.2. PostGIS

Postgis 1.5 was installed as it was the only source available and is compatibile with Postgres 8.4.

3.3. PGUnit

PGUnit is a 10+ year old framework for writing and running unit tests in PostgreSQL. More modern solutions area available but due to software restrictions imposed by the SWG we’re restricted to using an ancient version of PostgreSQL on the lehd2 cluster. PGUnit was chosen because it was written/designed for the available postgres version on hand.

From the original documentation:

3.3.1. What is PGUnit?

PGUnit is a xUnit-style framework for stored procedures in PostgreSQL 8.3+. It allows database developers to write automated tests for existed stored procedures or develop procedures using concepts of Test Driven Development (TDD). All test cases are stored in the database, so you don’t need any external resources (like files, version control, command-line utilities etc.) to save tests.

As in traditional xUnit, tests may be grouped in test case; each test-case may have its own environment initialization code ("fixture preparation code", or setUp block). The main benefit of PGUnit is that setUp block (usually quite CPU intensive) is executed only once, and its effect is stored in a savepoint. Then, all tests are executed from that savepoint, so the fixture initialization overheat is minimal. All tests are still executed independently, because their effects are automatically rolled back after the execution.

Download: dklab_pgunit_2008-11-09.sql

4. Configuration

4.1. Constants

A single file, 00_setenvvars.bash, contains the full set of constants needed for any modules or unit tests. This script is run in each module and sets some standard constants such as:

dbname=otmgeo
dbusername=*your username here*
dbport=5432
congresssession=116
acsdatayear=2016
tigeryear=2018
schemaprefix=*a custom prefix for the data vintage*

4.1.1. Schema Prefix

Setting a schema prefix allows multiple vintages to live beside each other in the database. For example, the following shows that we’re currently storing final versions of the data for an em patch, the OnTheMap 2018 vintage, and QWI 2018 vintage.

otmgeo=> \dn
        List of schemas
        Name        |  Owner
--------------------+----------
 otm2018final       | otmgeo
 patchemfinal       | otmgeo
 qwi2018r1final     | otmgeo

4.1.2. Module

At the end of each module a single line indicates which qa test script should be run. This script contains all unit tests corresponding to the current module.

For example, the module:
01_facesloader.bash
runs the following qa script
./01_facesloader_qa.bash run

4.2. Unit Test

4.2.1. Documentation Block

The unit tests contain a documentation block at the top that can be mostly ignored or minorly edited. It provides instructions for how to run each unit test and what arguments are expected. For example:

Short help
$ ./01_facesloader_qa.bash
Usage:
./01_facesloader_qa.bash <run|print|help>
Full help
$ ./01_facesloader_qa.bash help

 SYNOPSIS
    ./01_facesloader_qa.bash <run|print|help>

 DESCRIPTION
    This program creates and runs the unit tests for the module.
    Multiple unit tests must be separated by commas. All other
    commands are separated by a newline for clarity.

 OPTIONS
    help          Print the extended help
    print         Print unit tests to console
    run           Run unit tests

 EXAMPLES
    01_facesloader_qa.bash run
    01_facesloader_qa.bash print

4.2.2. Load Constants and Unit modules

At the end of the header the unit test module begins by loading the necessary components. For example:

Load the constants
#set environment variables (dbname dbusername dbport schemaprefix)
source ./00_setenvvars.bash

In this case, the only expected constants are the dbname, dbusername, dbport and schemaprefix. They can be called as variables after this statement.

Load the unit tests
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/table_exists.bash

These steps load the needed unit tests for this module. In this case, all that’s happening is that the setup functions are made available, and that a single test to check for a table’s existence is loaded

4.2.3. Set the test module name and generate the sql

# name the test suite
suitename=test_01_facesloader

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(table_exists test1 'table exists' ${schemaprefix}public faces),
$(table_exists test2 'table exists' ${schemaprefix}public facesah)
$(end_unit)
SQL

This block creates a sql function called test_01_facesloader which runs two tests - a check that the faces table exists and the facesah table exists in the prefixed schema. The two lines above call a function called table_exists with 4 arguments.

The argument order may seem confusing but can be viewed in the source code for the unit test itself. For example:

$ head unit_tests/table_exists.bash

#!/bin/bash

table_exists() {
label=$1
description=$2
schema=$3
table=$4
...

4.2.4. Print the Unit Tests

Calling the unit test with the print argument will display the full sql function as seen by the database. This can be useful for testing and debuging. For example:

> ./01_facesloader_qa.bash print

CREATE OR REPLACE FUNCTION pgunit.test_01_facesloader()
  RETURNS pgunit.testfunc[]
AS
$body$
  SELECT pgunit.testcase(
...

4.2.5. Run the Unit Tests

Calling the unit test with the run argument will send the function to the database and run it as a unit test on the data from the associated module. The output of those unit tests will be displayed back to the user upon completion. For example:

> ./01_facesloader_qa.bash run

CREATE FUNCTION
NOTICE:  *^*^*^*^*^
NOTICE:
NOTICE:  PostgreSQL Unit Tests (PGUnit)
NOTICE:
NOTICE:  pgunit.test_01_facesloader
NOTICE:    - OK setUp (12 ms)
NOTICE:    ! FAIL test1 (otm2018public.faces) table exists (5 ms)
NOTICE:    ! FAIL test2 (otm2018public.facesah) table exists (1 ms)
NOTICE:
NOTICE:  Time: 00:00:00

In the above block, the function was created and ran against the current database. Both tests report as failed because the data from the otm2018public schema doesn’t exist anymore. This only serves to demonstrate what to expect when running the unit test frameworks.

5. Tests

Unit tests are stored in a directory called unit_tests/ and are designed for reuseability. Arguments below begin at index 1 since, in bash, index 0 would reference the caller.

5.1. Start and End

All unit test blocks begin with a start_unit call and end with a end_unit call. The name of the set of tests is passed to the start module so that results can be recalled at a later date without needing to run the suite again.

$(start_unit $suitename)
*UNIT TESTS HERE*
$(end_unit)

5.2. Table Existence

This test determines whether a table exists in the database.

Table 1. table_exists.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

schema

The table schema to be checked (can include $variables)

4

table

The table name to be checked (can include $variables)
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/table_exists.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(table_exists test1 'public.faces exists' public faces)
$(end_unit)
SQL

5.3. Column Existence

This test determines whether a column exists within a table.

Table 2. table_exists.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

schema

The table schema to be checked (can include $variables)

4

table

The table name to be checked (can include $variables)

5

column

The column name to be checked (can include $variables)
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/column_exists.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(column_exists test1 'exo_id column exists' working aiannh exo_id)
$(end_unit)
SQL

5.4. Geometry Type

This test determines whether a column matches an expected postgis geometry type.

Table 3. geometry_is.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

schema

The table schema to be checked (can include $variables)

4

table

The table name to be checked (can include $variables)

5

geomtype

The geometry type to be validated via ST_GeometryType()
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/geometry_is.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(geometry_is test15 'working.pointlm is type point' working pointlm ST_Point),
$(end_unit)
SQL

5.5. Where does not exist

This test determines whether there are any occurrences of a given where case.

Table 4. does_not_exist_where.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

schema

The table schema to be checked (can include $variables)

4

table

The table name to be checked (can include $variables)

5

where

The SQL statement to be passed to the where clause
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/does_not_exist_where.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(does_not_exist_where test1 'no undefined sldls in working.sldl' working sldl "sldlst like 'Z%'"),
$(end_unit)
SQL

5.6. Counts are equal between two tables

This test compares two tables or select statements and determines if the counts of the same variable are identical.

Table 5. count_is_equal_where.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

tobecounted

The variable to be compared (e.x. '*' or 'distinct col')

4

firstselection

The SQL statement or schema.table passed to the FROM clause

5

secondselection

the SQL statement or schema.table passed to the FROM clause
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/count_is_equal_where.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(count_is_equal_where test1 'count of distinct stfids match' 'distinct stfid' "public.facesxwalk" "working.block_xwalk_intpt")
$(end_unit)
SQL

5.7. Is equal to value

This test compares an input value against a simple select statement from a given table

Table 6. is_equal_to.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

schema

The table schema to be checked (can include $variables)

4

table

The table name to be checked (can include $variables)

5

value

The value to be compared

6

select

The SQL to be passed to the select statement
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/is_equal_to.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(is_equal_to test1 'all stfid of length 15 in working.cd_assignments' working cd_assignments 15 "DISTINCT char_length(stfid)")
$(end_unit)
SQL

5.8. Is equal to value where

This test compares an input value against a simple select statement from a given table with an additional where clause

Table 7. is_equal_to_where.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

The text based explanation of what the test accomplishes

3

schema

The table schema to be checked (can include $variables)

4

table

The table name to be checked (can include $variables)

5

value

The value to be compared

6

select

The SQL to be passed to the select statement

7

where

The SQL statement to be passed to the where clause
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/is_equal_to_where.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(is_equal_to_where test1 'all stfid of length 15 in working.cd_assignments in state 01' working cd_assignments 15 "char_length(stfid)" "st='01'")
$(end_unit)
SQL

5.9. Is equal to value where (multiple tables)

This test compares an input value against a select statement with a from and where variable

Table 8. is_equal_to_where_multi.bash arguments
Order Name Description

1

label

The high level label given as reference (e.g. test1)

2

description

the text based explanation of what the test accomplishes

3

value

The value to be compared

4

select

The SQL to be passed to the select statement

5

from

The SQL statement to be passed to the from clause

6

where

The SQL statement to be passed to the where clause
Example
#import the test modules used
source ./unit_tests/setup.bash
source ./unit_tests/is_equal_to_where_multi.bash

# create the unit test sql
read -r -d '' testsuitesql <<SQL >/dev/null
$(start_unit $suitename)
$(is_equal_to_where_multi test1 'at least one cd assignment updated' true 1 "working.block_xwalk_intpt AS main, public.facesxwalk AS sub" "main.stfid = sub.stfid AND main.stcd115 <> sub.stcd115")
$(end_unit)
SQL

6. Future

More tests are being added as the use case is needed