Unit Testing Framework

Aqua Data Studio works with procedural language PostgreSQL pgTAP, PostgreSQL pgUnit, and SQLServer tSQLt. Giving users the functionality to execute tests and report their results.

This tool offers users the ease of running and writing unit tests for SQLServer and PostgreSQL.

The Unit Test Framework should be installed on the DB server.

PostgreSQL pgTAP

pgTAP is a unit testing framework for PostgreSQL written in PL/pgSQL and PL/SQL. It incorporates an exhaustive collection of assertion functions. Aqua Data Studio v22.0 supports the Unit Testing Framework, where users can collect all the tests in the database functions and run them together. All the work will be done by the Run test function, which finds and runs test functions in individual transactions, showing test results in grid/tabular format irrespective of the unit test framework used to run the tests.

To add pgTAP on PostgreSQL server:

• Download the pgTAP from here.
• The framework can be installed in a specific database by using the following command:

CREATE EXTENSION IF NOT EXISTS pgtap WITH VERSION '1.1.0' CASCADE;

For Windows - if you get an error of missing files while running the query above, please follow the steps from this page.

Testing a hypothetical users table:

CREATE OR REPLACE FUNCTION setup_insert(
) RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT is( MAX(nick), NULL, 'Should have no users') FROM users;
    INSERT INTO users (nick) VALUES ('theory');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_user(
) RETURNS SETOF TEXT AS $$
   SELECT is( nick, 'theory', 'Should have nick') FROM users;
END;

Once you define your unit testing functions, you can run tests at any time by using the runtests() functions:

SELECT * FROM runtests(test_user);

Each test function will run within its own transaction, and rolled back when the function completes.

pgTAP unit test case example:

CREATE OR REPLACE FUNCTION "public"."pgTap_sampletestclass_sampletestcase"   RETURNS SETOF TEXT LANGUAGE plpgsql AS $$ BEGIN    RETURN NEXT has_table( 'domains' );    RETURN NEXT has_table( 'stuff' );    RETURN NEXT has_table( 'sources' );    RETURN NEXT has_table( 'domain_stuff' ); END; $$;

PostgreSQL pgUnit

PostgreSQL pgUnit allows users to run unit tests as stored procedures, based on the grouping of tests, setup, teardowns, and pre/post conditions. pgUnit supports setup and teardown functions.

Each unit test should have a "test_case_" prefix to be identified as a unit test.

List of prefixes for all types:

• "test_case_": identifies a unit test procedure
• "test_precondition_": identifies a test precondition function
• "test_postcondition_": identifies a test postcondition function
• "test_setup_": identifies a test setup procedure
• "test_teardown_": identifies a test teardown procedure

The underscore ( _ ) character is used to separate the test name into a hierarchical structure to share the setup, teardown or the preconditions between several unit tests.

Use the "test_run_all" stores procedure to run the entire test suite.

select * from test_run_all();

Pick one or an entire group of tests base don their prefix using the "test_run_suite" stored procedure.

select * from test_run_suite('finance');

The statement will pick up all unit tests starting with the "test_case_finance" prefix together with the associated support functions and procedures.

To add pgUnit on PostgreSQL server:

• The framework can be installed in a specific database on schema using the following command:

CREATE EXTENSION DBLINK;

• Run the PGUnit.sql file from the github link in the query analyzer to setup the pgunit framework in that database and schema.
• To remove the pgunit framework from the database PGUnitDrop.sql should be executed in the query analyzer.

To learn more from pgUnit, please click here.

pgUnit test case example:

CREATE OR REPLACE FUNCTION "public"."test_case_sampleclass_sampletestcase" returns       void as $$ declare  id BIGINT; begin  SELECT customer.createUser(1, 100) INTO id;  perform test_assertNotNull('user not created', id);  perform test_assertTrue('user id range improper', id >= 10000); end; $$ language plpgsql;

SQLServer tSQLt

SQLServer tSQLt works with Aqua Data Studio supported versions of SQLServer. It allows you to implement unit tests in tSQLt without having to use different tools for coding and unit tests. SQLServer tSQLt provides the ease to manage and create unit tests by automatically running within transactions, grouping tests within a schema, output can be generated in plain text or XML, and it gives the ability to mock tables and views to isolate the code being tested.

To add SQLServer tSQLt

• Download tSQLt Unit Test Framework from here.
• Unzip the file to a location on your hard drive.
• Execute the PrepareServer.sql file in the query analyzer window selecting a SQL server.
• Execute tSQLt.class.sql from the zip file to add tSQLt in the database.

To learn more from tSQLt Unit Test Framework visit the following page.

SQLServer tSQLt unit test case example:

CREATE or ALTER PROCEDURE [sampleclass].[test sampletestcase] AS BEGIN DECLARE @actual MONEY;    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;    DECLARE @amount MONEY; SET @amount = 2.00;      SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);      DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)    EXEC tSQLt.assertEquals @expected, @actual;   END;

Aqua Data Studio has made the tool "Manage Unit Tests" easy to use for SQLServer and PostgreSQL database platforms. Users can find the feature under the Tools main application menu or by selecting Tools > Manage Unit Test from the right-click menu in the database Schema browser.

Choose the Database or Server in the connection dialog. In the Manage Unit Tests UI, users can choose which database to work on, SQLServer or PostgreSQL.

This option is only enabled for SQLServer and PostgreSQL connections.

The Manage Unit Tests workspace offers different options.

Create Unit Test Class - Brings up a window to type in a name for the Test Class. Once it-s created, it will automatically appear in the Unit Test tree.

Create Unit Test Case - Brings up a window to type in a name for the Test Case and to add a Description. Once created, the Edit Unit Test Function will open. In this section you can edit the name but not the description.

Run Test - To run the test for the selected Test Class or Test Case.

Rerun Test - In case users unselects a Test Class or Test Case, use the Rerun Test icon to try testing it again.

Stop - To stop a Test.

Refresh - To have a fresh look into the window and start selecting which Test Class and Test Case to Run again.

Reconnect - To reconnect to the Database.

Help - To be redirected to the Online Help page.

In the Framework section, choose if you want to use a pgTap or a pgUnit.

In the Database section, select between the Databases you have available for a Unit Testing.

In the search section, users can look for a specific name and Run a Test faster.

Once users have selected which Test Class and Test Case to Run, click on the Run Test icon.

The Results will appear at the bottom of the window. There is a section where users can select if they want to View as Spreadsheet or to have the Results spreadsheet be sent to an email address. Remember that to use this feature you have to have the mail system enabled. You can configure it under File > Options > Email.