On RDBMS, SQL and the DRY Principle, and Query Networks

I saw a link a week ago or so on my Twitter feed to an article published by one Lance Gutteridge on 1 June 2018: What I’m Telling Business People About Why Relational Databases Are So Bad. The article is written in a inflammatory style, here’s a sample quote:

Relational databases have been the worst technology to ever poison a field of endeavor

He classifies the ‘badness’ in three main categories:

  • SQL Injection
  • SQL “is a total violation of the DRY principle”
  • Object-Relational Impedance Mismatch

In this article I want to briefly discuss his criticisms under each of these categories, and then move on to discuss some interesting features of SQL queries and joins arising from the fact that SQL plainly does NOT violate the DRY principle. I’ll also discuss how the concept of the network, initially applied to table relationships, can be a very useful design concept in both data modelling and query design.

Part I: Comments on the Lance Gutteridge article
SQL Injection
From Wikipedia, SQL injection:

SQL injection is a code injection technique, used to attack data-driven applications, in which nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

SQL injection has indeed been a real vulnerability for database systems in the past, but it is an avoidable problem today. As the Wikipedia article puts it:

An SQL injection is a well known attack and easily prevented by simple measures.

SQL “is a total violation of the DRY principle”
Dr. Gutteridge notes that relationships are defined in an RDBMS by foreign keys and primary keys on the tables, and that having to make join relations explicitly in SQL is a repetition of information already known, and hence violates the “Don’t Repeat Yourself” principle.

This criticism is easily dealt with: In general the table relationships do not in fact fully determine the joins in a query. A simple, and very common, example arises in order entry systems. Consider the following simplified 3-table data model:

Here we have an order entity with a foreign key link to a customer, and two foreign key links to the address entity. A customer may have multiple addresses that can serve as shipping or billing addresses on any given order. A particular query may require one or other, or both, or neither of the addresses for the order. The primary key/foreign key relationships cannot determine which tables and links to include without the query specifying them.

The usual way to specify this information in ANSI-standard SQL is to use JOIN/ON-clauses like this:

JOIN addresses add_b ON add_b.address_id = ord.billing_address_id

There are also situations in which joins can be expressed more concisely, and we’ll look at some of them in part II, but it’s clear that these clauses do not in any meaningful way violate the DRY principle.

Object-Relational Impedance Mismatch
In one of the few views on which I am inclined to agree with Dr. Gutteridge, he regards the term as “technobabble”, but it does describe a real phenomenon. Dr. Gutteridge expresses it thus:

…the data in a relational database is stored in ways more in keeping with a 1980s programming language than with a modern, object-oriented language

Though this mismatch does exist, it’s unlikely that dropping the relational model is the answer, because it solves a more fundamental problem. An article from 29 November 2017, Important Papers: Codd and the Relational Model, includes the following:

…Codd motivates the search for a better model by arguing that we need “data independence,” which he defines as “the independence of application programs and terminal activities from growth in data types and changes in data representation.” The relational model, he argues, “appears to be superior in several respects to the graph or network model presently in vogue,” partly because, among other benefits, the relational model “provides a means of describing data with its natural structure only.” By this he meant that programs could safely ignore any artificial structures (like trees) imposed upon the data for storage and retrieval purposes only.

I remember when I started my programming career in 1984 most of the work on any application was spent in writing code simply to store and retrieve data in application-specific formats. Within a few years that effort became largely unnecessary with the introduction of the Oracle RDBMS and SQL. Although modern big data requirements mean other approaches to data storage are also needed, the relational model isn’t going away.

In one of the unwitting ironies in Dr. Gutteridge’s article, he states towards the end that:

there are programmers who have never really seen any other kind of database and believe that all databases are relational

while apparently believing that all modern programming language are object-oriented. They aren’t, and while OOP isn’t going away, it has real deficiencies in modelling the real world that have led to growing interest in other paradigms such as functional programming, as well as old fashioned imperative programming. Here’s an interesting review of some of those deficiencies from 23 July 2016:
Goodbye, Object Oriented Programming

Part II: On SQL and DRY – Joins via NATURAL/USING/ON
In this second part we’ll use two subsets of Oracle’s HR demo schema as examples, and we’ll ignore any links in the tables to tables other than those depicted in the ERDs. Let’s see how, in some cases, we can use ANSI join syntax to avoid explicitly listing all the join column names, but that there are drawbacks to doing so.

Tree Data Model – Department 110, Location, Country, Region – NATURAL JOIN
The ERD below shows a simple linear tree structure.

Let’s start by considering a situation where we don’t need to specify the full join clause with fields on both sides.

 DEPARTMENT_NAME                STREET_ADDRESS                           CITY                           COUNTRY_NAME                             REGION_NAME
------------------------------ ---------------------------------------- ------------------------------ ---------------------------------------- -------------------------
Accounting                     2004 Charade Rd                          Seattle                        United States of America                 Americas

  1  SELECT department_name, street_address, city, country_name, region_name
  2    FROM departments
  3      NATURAL JOIN locations
  4      NATURAL JOIN countries
  5      NATURAL JOIN regions
  6*  WHERE department_id = 110

Here in this simple (linear) tree-structured data model we were able to join the three subsequent tables to the driving table, departments, simply by adding the table names after NATURAL JOIN.

So is this a case of the SQL engine reading the data model and constructing the joins without the need for repetition? No, it isn’t. As the documentation tells you, NATURAL JOIN joins by matching fields with the same names on either side. This can be dangerous as the next example shows.

The second example has only two tables, but there is a loop in the structure.

[In the underlying HR schema from which this is extracted there is also a self-join on employees, which we are excluding]
Department 110 employees: NATURAL JOIN gives wrong answer
There are two employees in department 110:

  COUNT(*)
----------
         2

  1  SELECT COUNT(*)
  2    FROM employees
  3*  WHERE department_id = 110

Let’s try to get the employees using NATURAL JOIN, like this:

DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME           MANAGER_ID
------------------------------ ------------------------- -------------------- ----------
Accounting                     Gietz                     William                     205

  1  SELECT department_name, last_name, first_name, manager_id
  2    FROM departments
  3     NATURAL JOIN employees
  4*  WHERE department_id = 110

This returns only one of the two employees because NATURAL JOIN is matching on both department_id and manager_id as they appear in both tables.

Department 110 employees: USING department_id gives right answer
We can get the right answer by joining with the USING keyword, which assumes the column name to join on is the same on both tables, and mentions it explicitly.

DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME
------------------------------ ------------------------- --------------------
Accounting                     Higgins                   Shelley
Accounting                     Gietz                     William

  1  SELECT department_name, last_name, first_name
  2    FROM departments
  3     JOIN employees USING (department_id)
  4*  WHERE department_id = 110

This example shows how USING resolves the earlier NATURAL JOIN error by specifying the field names in common to be used. The next example shows how this does not always work.

Department 110 manager: USING manager_id gives wrong answer

DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME           MANAGER_ID
------------------------------ ------------------------- -------------------- ----------
Accounting                     Gietz                     William                     205

  1  SELECT department_name, last_name, first_name, manager_id
  2    FROM departments dep
  3     JOIN employees USING (manager_id)
  4*  WHERE dep.department_id = 110

From the first query above we know that the manager of department 110 is Shelley Higgins. It’s reported here instead as William Gietz, because his manager is the same as the department’s manager, but Shirley’s is not.

Department 110 manager: ON mgr.employee_id = dep.manager_id gives right answer

DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME
------------------------------ ------------------------- --------------------
Accounting                     Higgins                   Shelley

  1   SELECT department_name, last_name, first_name
  2     FROM departments dep
  3     JOIN employees mgr ON mgr.employee_id = dep.manager_id
  4*  WHERE dep.department_id = 110

Here we we specify the join with the ON-clause linking the columns explicitly on each side of the join. This is the most usual approach to ANSI joins.

Department 110 manager: NATURAL JOIN subqueries
In a recent article (A tribute to Natural Join, 20 August 2018) Frank Pachot suggested that NATURAL JOIN could be more widely used if tables were replaced by subqueries in which all the columns were aliased in such a way that the join columns only would have the same names in the joined tables. The query above, implemented in this way might be written:

DEPARTMENT_NAME                MGR_LAST_NAME             MGR_FIRST_NAME
------------------------------ ------------------------- --------------------
Accounting                     Higgins                   Shelley

  1  SELECT department_name, mgr_last_name, mgr_first_name
  2    FROM
  3  (SELECT department_id, department_name, manager_id
  4     FROM departments) dep
  5    NATURAL JOIN
  6  (SELECT employee_id manager_id, last_name mgr_last_name, first_name mgr_first_name
  7     FROM employees) mgr
  8*  WHERE dep.department_id = 110

This version is much more verbose and it’s much harder to see which are the join columns by scanning the select lists, compared with specifying them in ON clauses.

Conclusions on Joins via NATURAL/USING/ON

  • Very few people use NATURAL JOIN due to the limitation that the join column names, and only those, in each table or subquery have to be the same
  • USING tends to be used in simple ad hoc queries with small numbers of tables, and improves on NATURAL JOIN by listing the join columns explicitly, but again relies on the join column names being the same
  • The most commonly used join mechanism is the ON clause, with column names specified on each side. This avoids the possible pitfalls of the other mechanisms and for complex, real world queries generally results in more maintainable code

Regarding the DRY principle in SQL more generally, I wrote this,
Modularity in SQL: Patterns, Anti-Patterns and the Kitchen Sink, in September 2013 [tl;dr: Functions and complex views are fine as entry-points but using them as building blocks in SQL is usually a bad idea, and subquery factors (WITH clause) are a better approach to SQL modularity].

Part III: On Data Models and Queries Viewed as Networks
In the examples above we saw that when there are two ways of joining a pair of tables it’s no longer possible for the data model alone to determine the join. An entity relationship structure can be represented as a directed network, with entities as nodes and the relationships between them as links. The second example corresponds to a loop in the network, in which there are two ways of getting from the driving node, departments, to the employees node.

Where the relationships between tables are stored in constraints metadata we can use network analysis PL/SQL to show the network structure and then make diagrams to help in understanding schema structures, as I showed here in May 2015:
PL/SQL Pipelined Function for Network Analysis. This diagram, extracted from that article, shows the structure of Oracle’s demo schemas, with what’s known in graph theory as a spanning tree marked in red, and loop-closing links in blue.

Networks - PLSQL, v1.0 - HR

Queries as Networks
In 2009 I was asked to extend the functionality of an Oracle ERP invoice print report in order to support a move to a multi-org ERP structure. The report had a large number (I think around 30) of small queries in various places, such as format triggers and formula columns as well as in the main data model, and I started by combining most of them into a single, fairly complex query plus one smaller, global data query. The report ran much more quickly and I felt was more maintainable since almost all the logic was in one place, and the query could be tested through tools such as Toad. However, as the query was quite complex I was asked to produce some documentation on how it worked. This got me thinking about how ERDs are used to document data models, and whether we could extend those ideas to document queries too.

My initial thought was that a query can be thought of as a route through the data model network, with looping corresponding to repeated table instances in the query. However, it turns out to be much clearer to represent each table instance as its own node on a new network diagram. After I left the company I wrote my ideas up in a general form in a word document on Scribd in May 2009, A Structured Approach to SQL Query Design. Since then I have extended these ideas to include coverage of query constructs such as unions and subquery factors, and use of annotations for clarity. I wrote another article in August 2012 where I apply these extended ideas to some example queries taken from the OTN forum, Query Structure Diagramming. Here’s a diagram from that article:

You can also find examples in several of the articles on combinatorial SQL referenced in Knapsacks and Networks in SQL from December 2017.

How many tables is too many?
Have you ever heard the view expressed, usually by a DBA, that you should not put more than a small number of tables, say 10, in any query? The reasoning given is that the number of join orders for N tables is N!, which for N=10 is 3,628,800 and the query optimiser (CBO) won’t be able to handle that number of permutations. You will probably know from the discussion above why this reasoning is incorrect: The cost optimization problem is really a network path problem, rather than a permutation problem – you look to join (large) tables that are linked to the current rowset rather than than making cartesian joins, so most permutations are never considered.






Oracle and JUnit Data Driven Testing: An Example

In Design Patterns for Database API Testing 1: Web Service Saving 2 – Code I identified a number of antipatterns commonly seen in database testing.

utPLSQL, originally developed by Steve Feuerstein around 15 years ago, may have been the first Oracle unit testing framework and seems to be the most widely used. Its new utPLSQL GitHub Page describes itself thus:

The official new home of utPLSQL, unit testing framework for PL/SQL, based on xUnit.

xUnit refers to the family of unit testing frameworks derived from Java’s JUnit, and present in most object-oriented languages, such as Ruby, Python etc.

It has occurred to me that some of the problems with unit testing in the database world may arise from translating object-oriented ideas on testing too zealously into the database world, where they may not work so well. It could also impinge on the design of base code, in that in the object-oriented world any complexity in unit tests is usually seen as a ‘code smell’ indicating the base units are too complex; testability is seen as a key objective in OO module design. To gain some insight into the differences between database and object-oriented testing, it seemed like a good idea to try to test the same functionality in both Java and Oracle. This article gives the results of this experiment.

I have submitted an abstract for a presentation entitled “Database Functional Testing: Automation, Yes; xUnit-Style Micro-Testing, No” to one of the Oracle User Group conferences for Q1 2017, but it has not yet been approved or rejected.

Update, 10 May 2017: My abstract for the Ireland OUG in March 2017 was not accepted. I did present on a second abstract that was accepted: Dimensional Performance Benchmarking of SQL – IOUG Presentation

Testing Example

In Java unit testing one normally tries to test code in isolation, without database access or other dependencies, whereas in Oracle it is normally database operations that are being tested. As a compromise I chose to implement code that would read a single key column from a CSV file and store counts of the key values in memory, with methods to return these key-value pairs as lists either unordered, ordered by key, or ordered by value (then key).

The constructor method will take three parameters:

  • File name
  • Delimiter
  • Key column position

We’ll use the same file name, which in Java will include the full path, whereas Oracle will assume the path from an Oracle directory object. There will be two scenarios that will test different values for the other two parameters simultaneously (as what I call sub-scenarios in Design Patterns for Database API Testing 1: Web Service Saving 1 – Design).

For the unordered method, we will validate only the scalar count of the records returned, while for the ordered methods we will validate the full ordered lists of tuples returned. For illustrative purposes one test method for each scenario will have a deliberate error in the expected values.

Test Scenario 1 – Tie-break/single-delimiter/interior column

Here the test file has four columns, with the key column being the third, and the delimiter a single character ‘,’. The file contains lines:

0,1,Cc,3
00,1,A,9
000,1,B,27
0000,1,A,81

Note that keys ‘Cc’ and ‘B’ both occur once, and when ordered by value (then key) ‘B’ should appear before ‘Cc’.

Test Scenario 2 – Two copies/double-delimiter/first column

Here the test file has three columns, with the key column being the first, and the delimiter two characters ‘;;’. The file contains two identical lines:

X;;1;;A
X;;1;;A

Java – JUnit

JUnit Code

package colgroup;
/***************************************************************************************************
Name:        TestColGroup.java

Description: Junit testing class for Col_Group class. Uses Parameterized.class to data-drive
                                                                               
Modification History
Who                  When        Which What
-------------------- ----------- ----- -------------------------------------------------------------
B. Furey             22-Oct-2016 1.0   Created                       

***************************************************************************************************/
import static org.junit.Assert.*;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized.Parameters;
import org.junit.runners.Parameterized;

@RunWith(Parameterized.class)
public class TestColGroup {
/***************************************************************************************************

Private instance variables: 2 scenarios, input, and expected records declared here, initially in 
2-level generic arrays, but expected records transferred to List for assertion

***************************************************************************************************/
  private ColGroup colGroup = null;
  private String testFile = "H:/Script/Input/ut_group.csv";
  private String[][] testLines = new String[][] { 
      {"0,1,Cc,3", "00,1,A,9", "000,1,B,27", "0000,1,A,81"}, 
      {"X;;1;;A", "X;;1;;A"}
  };
  private String[] testDelim = new String[] {",", ";;"};
  private int[] testColnum = new int[] {2, 0};
  private List<String> lines;
  private String delim;
  private int colnum;

  private String[][] keysK = new String[][] { 
      {"A", "Bx", "Cc"}, 
      {"X"}
  };
  private int[][] valuesK = new int[][] { 
      {2, 1, 1}, 
      {2}
  };
  private String[][] keysV = new String[][] { 
      {"B", "Cc", "A"},
      {"X"}
  };
  private int[][] valuesV = new int[][] { 
      {1, 1, 2}, 
      {2}
  };
  private int expAsIs;
  private List<Map.Entry<String,Long>> expListK = null;
  private List<Map.Entry<String,Long>> expListV = null;

  private void addMap (int i, String strValK, int lonValK, String strValV, int lonValV) {
    expListK.add (i, new AbstractMap.SimpleEntry<String, Long> (strValK, (long) lonValK));
    expListV.add (i, new AbstractMap.SimpleEntry<String, Long> (strValV, (long) lonValV));
  }

  private int testNum;

  /***************************************************************************************************

  TestColGroup: Constructor function, which sets the instance variables for given scenario (testNum), and
                is called before each test with parameters passed via test_data (see end)

  ***************************************************************************************************/
  public TestColGroup (int   testNum,   // test scenario number
                  int   nGroups) { // number of groups

    System.out.println("Doing TestCG3 before test "+testNum+"...");
    this.lines = Arrays.asList (testLines[testNum]);
    this.delim = testDelim[testNum];
    this.colnum = testColnum[testNum];
    this.expAsIs = nGroups;
    this.testNum = testNum;
    int i = 0;
    expListK = new ArrayList<Map.Entry<String,Long>>(keysK[testNum].length);
    expListV = new ArrayList<Map.Entry<String,Long>>(keysV[testNum].length);
    for (String k : keysK[testNum]) {
      addMap (i, k, valuesK[testNum][i], keysV[testNum][i], valuesV[testNum][i]);
      i++;
    }
  }
  /***************************************************************************************************

  getGroup: Before each test method to write the test file and instantiate base object, using instance
            variables set for the scenario in TestCG3

  ***************************************************************************************************/
  @Before
  public void getGroup() {
    try {
      System.out.println("Doing setup before test "+this.testNum+"...");
      Files.write (Paths.get (testFile), lines, StandardCharsets.UTF_8);
      colGroup = new ColGroup (testFile, delim, colnum);
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
  /***************************************************************************************************

  delFile: After each test method to delete the test file

  ***************************************************************************************************/
  @After
  public void delFile() {
    try {
      System.out.println("Doing teardown after test "+this.testNum+"...");
      Files.delete(Paths.get (testFile));
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
  /***************************************************************************************************

  test*: Test method for each base method; each one is run once for each record defined in test_data
         in @Parameters

  ***************************************************************************************************/
  @Test
  public void testAsIs() {

    List<Map.Entry<String,Long>> actList = colGroup.listAsIs();
    assertEquals ("(as is)", expAsIs, actList.size());
    colGroup.prList("(as is)", actList);
  }
  @Test
  public void testKey() {

    List<Map.Entry<String,Long>> actList = colGroup.sortByKey();
    assertEquals ("keys", expListK, actList);
    colGroup.prList("keys", actList);
  }
  @Test
  public void testValue() {
    List<Map.Entry<String,Long>> actList = colGroup.sortByValue();
    assertEquals ("values", expListV, actList);
    colGroup.prList("values", actList);
  }
  /***************************************************************************************************

  test_data: @Parameters section allows passing of data into tests per scenario; neater to pass in a
             pointer to the instance arrays for most of the data

  ***************************************************************************************************/
  @Parameters
  public static Collection<Object[]> test_data() {
    Object[][] data = new Object[][] { {0, 3}, {1, 2} }; // 2 records, columns = scenario #, # groups
    return Arrays.asList(data);
  }
}

JUnit Output

Unit Test Results.

Designed for use with JUnit and Ant.

All Tests

Class Name Status Type Time(s)
TestColGroup testKey[0] Failure keys expected:<[A=2, Bx=1, Cc=1]> but was:<[A=2, B=1, Cc=1]>

junit.framework.AssertionFailedError: keys expected:<[A=2, Bx=1, Cc=1]> but was:<[A=2, B=1, Cc=1]>
at colgroup.TestColGroup.testKey(TestColGroup.java:150)

0.055
TestColGroup testValue[0] Success 0.004
TestColGroup testAsIs[0] Success 0.002
TestColGroup testKey[1] Success 0.004
TestColGroup testValue[1] Success 0.002
TestColGroup testAsIs[1] Failure (as is) expected:<2> but was:<1>

junit.framework.AssertionFailedError: (as is) expected:<2> but was:<1>
at colgroup.TestColGroup.testAsIs(TestColGroup.java:143)

0.002

JUnit Notes

  • JUnit first creates instances of the test class for each test method and starts running the tests after each instance is created
  • From JUnit 4 it is possible to data-drive testing by means of the @Parameters annotation, as implemented here, whereas I identified lack of data-driving as a common antipattern
  • Test methods are identifiable only by their method names rather than full descriptions
  • Scenarios are identifiable only by a number, which is even worse
  • Execution of a test method (instance) is aborted on failure of any assertion
  • Here there is only one test method per base method, but in general there could be several
  • JUnit aborting on assertion failure means that unit tests should have one or very few assertions, with additional unit tests being generated where necessary
  • Data-driving allows JUnit to generate additional unit tests from a single method at run-time for each scenario
  • A good approach is to start with a single parameterized scenario, then add new scenarios just by adding data records; this is how I usually proceed in Oracle
  • On assertion failure JUnit prints expected and actual values for both scalar and complex values passed to the assertion

Oracle – utPLSQL

utPLSQL Code

CREATE OR REPLACE PACKAGE BODY UT_Col_Group AS
/***************************************************************************************************

Description: utPLSQL unit testing for polyglot group-counting module, Col_Group
                                                                               
Modification History
Who                  When        Which What
-------------------- ----------- ----- -------------------------------------------------------------
Brendan Furey        30-Oct-2016 1.0   Created

***************************************************************************************************/

c_proc_name_asis        CONSTANT VARCHAR2(60) := 'Col_Group.ut_AIP_List_Asis';
c_proc_name_key         CONSTANT VARCHAR2(60) := 'Col_Group.ut_AIP_Sort_By_Key';
c_proc_name_value       CONSTANT VARCHAR2(60) := 'Col_Group.ut_AIP_Sort_By_vALUE';

c_file_2lis             CONSTANT L2_chr_arr := L2_chr_arr (
                                      L1_chr_arr ('0,1,Cc,3', '00,1,A,9', '000,1,B,27', '0000,1,A,81'),
                                      L1_chr_arr ('X;;1;;A', 'X;;1;;A')
);
c_prms_2lis             CONSTANT L2_chr_arr := L2_chr_arr (
                                      L1_chr_arr ('lines.csv', ',', '3'), L1_chr_arr ('lines.csv', ';;', '1')
);
c_scenario_lis          CONSTANT L1_chr_arr := L1_chr_arr ('Tie-break/single-delimiter/interior column', 'Two copies/double-delimiter/first column');

/***************************************************************************************************

ut_Setup, ut_Teardown: Mandatory procedures for utPLSQL but don't do anything here

***************************************************************************************************/
PROCEDURE ut_Setup IS
BEGIN
  NULL;
END ut_Setup;

PROCEDURE ut_Teardown IS
BEGIN
  NULL;
END ut_Teardown;

/***************************************************************************************************

Do_Test: Main local procedure for utPLSQL unit testing Col_Group methods

***************************************************************************************************/

PROCEDURE Do_Test (p_proc_name          VARCHAR2,      -- procedure name
                   p_exp_2lis           L2_chr_arr) IS -- expected values 2-d array

  /***************************************************************************************************

  Setup: Setup procedure for unit testing Col_Group package. Writes test file, then calls
         constructor API to store data in an array, line counts grouped by key

  ***************************************************************************************************/
  PROCEDURE Setup (p_file                 VARCHAR2,      -- file name
                   p_delim                VARCHAR2,      -- delimiter
                   p_colnum               PLS_INTEGER,   -- key column number in file
                   p_dat_lis              L1_chr_arr) IS -- lines to write to test file

  BEGIN

    Utils.Delete_File (p_file);
    Utils.Write_File (p_file, p_dat_lis);

    Col_Group.AIP_Load_File (p_file => p_file, p_delim => p_delim, p_colnum => p_colnum);

  END Setup;

  /***************************************************************************************************

  Call_Proc: Calls the base method according to calling procedure, and uses utPLSQL assert procedure
             to assert list counts, and for ordered methods, record lists in delimited form

  ***************************************************************************************************/
  PROCEDURE Call_Proc (p_exp_lis        L1_chr_arr,  -- expected values list (delimited records)
                       p_scenario       VARCHAR2) IS -- scenario description

    l_arr_lis           chr_int_arr;
  BEGIN

    l_arr_lis := CASE p_proc_name
                   WHEN c_proc_name_asis        THEN Col_Group.AIP_List_Asis
                   WHEN c_proc_name_key         THEN Col_Group.AIP_Sort_By_Key
                   WHEN c_proc_name_value       THEN Col_Group.AIP_Sort_By_Value
                 END;

    IF p_proc_name = c_proc_name_asis THEN

      utAssert.Eq (p_scenario || ': List count', l_arr_lis.COUNT, p_exp_lis(1), TRUE);

    ELSE

      utAssert.Eq (p_scenario || ': List count', l_arr_lis.COUNT, p_exp_lis.COUNT, TRUE);
      FOR i IN 1..LEAST (l_arr_lis.COUNT, p_exp_lis.COUNT) LOOP

        utAssert.Eq ('...Record', Utils.List_Delim (l_arr_lis(i).chr_field, l_arr_lis(i).int_field), p_exp_lis(i), TRUE);

      END LOOP;

    END IF;

  END Call_Proc;

BEGIN

  FOR i IN 1..c_file_2lis.COUNT LOOP

    Setup (p_file              => c_prms_2lis(i)(1),
           p_delim             => c_prms_2lis(i)(2),
           p_colnum            => c_prms_2lis(i)(3),
           p_dat_lis           => c_file_2lis(i));

    Call_Proc (p_exp_2lis(i), c_scenario_lis(i));

  END LOOP;

END Do_Test;

/***************************************************************************************************

ut_AIP_List_Asis: Entry procedure for utPLSQL testing Col_Group.AIP_List_Asis

***************************************************************************************************/
PROCEDURE ut_AIP_List_Asis IS

  c_proc_name           CONSTANT VARCHAR2(61) := c_proc_name_asis;
  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr('3'), L1_chr_arr('2'));

BEGIN

  Do_Test (c_proc_name, c_exp_2lis);

END ut_AIP_List_Asis;

/***************************************************************************************************

ut_AIP_Sort_By_Key: Entry procedure for utPLSQL testing Col_Group.AIP_Sort_By_Key

***************************************************************************************************/
PROCEDURE ut_AIP_Sort_By_Key IS

  c_proc_name           CONSTANT VARCHAR2(61) := c_proc_name_key;
  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('A','2'),
                                                                         Utils.List_Delim ('Bx','1'),
                                                                         Utils.List_Delim ('Cc','1')),
                                                             L1_chr_arr (Utils.List_Delim ('X','2'))
                                               );
BEGIN

  Do_Test (c_proc_name, c_exp_2lis);

END ut_AIP_Sort_By_Key;

/***************************************************************************************************

ut_AIP_Sort_By_Value: Entry procedure for utPLSQL testing Col_Group.AIP_Sort_By_Value

***************************************************************************************************/
PROCEDURE ut_AIP_Sort_By_Value IS

  c_proc_name           CONSTANT VARCHAR2(61) := c_proc_name_value;
  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('B','1'),
                                                                         Utils.List_Delim ('Cc','1'),
                                                                         Utils.List_Delim ('A','2')),
                                                             L1_chr_arr (Utils.List_Delim ('X','2'))
                                               );
BEGIN

  Do_Test (c_proc_name, c_exp_2lis);

END ut_AIP_Sort_By_Value;

END UT_Col_Group;
/

utPLSQL Output

.
>  FFFFFFF   AA     III  L      U     U RRRRR   EEEEEEE
>  F        A  A     I   L      U     U R    R  E
>  F       A    A    I   L      U     U R     R E
>  F      A      A   I   L      U     U R     R E
>  FFFF   A      A   I   L      U     U RRRRRR  EEEE
>  F      AAAAAAAA   I   L      U     U R   R   E
>  F      A      A   I   L      U     U R    R  E
>  F      A      A   I   L       U   U  R     R E
>  F      A      A  III  LLLLLLL  UUU   R     R EEEEEEE
.
 FAILURE: ".COL_GROUP"
.
> Individual Test Case Results:
>
SUCCESS - COL_GROUP.UT_AIP_LIST_ASIS: EQ "Tie-break/single-delimiter/interior column: List count" Expected "3" and got "3"
>
FAILURE - COL_GROUP.UT_AIP_LIST_ASIS: EQ "Two copies/double-delimiter/first column: List count" Expected "2" and got "1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "Tie-break/single-delimiter/interior column: List count" Expected "3" and got "3"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "A|2" and got "A|2"
>
FAILURE - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "Bx|1" and got "B|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "Cc|1" and got "Cc|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "Two copies/double-delimiter/first column: List count" Expected "1" and got "1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "X|2" and got "X|2"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "Tie-break/single-delimiter/interior column: List count" Expected "3" and got "3"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "B|1" and got "B|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "Cc|1" and got "Cc|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "A|2" and got "A|2"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "Two copies/double-delimiter/first column: List count" Expected "1" and got "1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "X|2" and got "X|2"
>
>
> Errors recorded in utPLSQL Error Log:
>
> NONE FOUND

PL/SQL procedure successfully completed.

utPLSQL Notes

  • Code is shared between the three test methods by means of a common local procedure Do_Test
  • Data-driving is achieved by using generic arrays and looping over scenarios
  • Only the simple assertion procedure utAssert.Eq is used; from experience the complex utPLSQL assertions are rarely suitable, and I think it is conceptually simpler to avoid them altogether
  • In general the lists of actual and expected values may have different cardinalities, so looping over them I use the minimum cardinality as loop maximum, and explicitly assert the counts; this means you may not see the detail for the unmatched records – in my own TRAPIT framework I handle this case by adding null records to the smaller list
  • Delimited records are asserted to limit the number of assertions, which would matter more in realistic cases having larger number of columns
  • utPLSQL does not have any data model for scenarios or output groups, with just a single description field available within the assert call to describe the scenario/group/assertion; I recommend bundling scenario and group information into this message for want of anything better
  • utPLSQL allows only one test method per base method, unlike JUnit, so multiple assertions may be necessary; fortunately an assertion failure does not abort the test procedure
  • utPLSQL test procedures run sequentially, in alphabetical order, unlike JUnit

Oracle – TRAPIT

This is the framework I described in articles listed at TRAPIT – TRansactional API Testing in Oracle. I have renamed the framework, dropping the phrase ‘unit testing’ because it has connotations from JUnit of testing small, isolated pieces of code, which is not what it is intended for.

TRAPIT Code

CREATE OR REPLACE PACKAGE BODY TT_Col_Group AS
/***************************************************************************************************

Description: TRAPIT (TRansactional API Testing) package for Col_Group

Further details: 'TRAPIT - TRansactional API Testing in Oracle'
                 
TRAPIT - TRansactional API Testing in Oracle
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- Brendan Furey 22-Oct-2016 1.0 Created Brendan Furey 13-Nov-2016 1.1 Utils_TT -> Utils_TT ***************************************************************************************************/ c_ms_limit CONSTANT PLS_INTEGER := 2; c_proc_name_asis CONSTANT VARCHAR2(60) := 'Col_Group.tt_AIP_List_Asis'; c_proc_name_key CONSTANT VARCHAR2(60) := 'Col_Group.tt_AIP_Sort_By_Key'; c_proc_name_value CONSTANT VARCHAR2(60) := 'Col_Group.tt_AIP_Sort_By_vALUE'; c_file_2lis CONSTANT L2_chr_arr := L2_chr_arr ( L1_chr_arr ('0,1,Cc,3', '00,1,A,9', '000,1,B,27', '0000,1,A,81'), L1_chr_arr ('X;;1;;A', 'X;;1;;A') ); c_prms_2lis CONSTANT L2_chr_arr := L2_chr_arr ( L1_chr_arr ('lines.csv', ',', '3'), L1_chr_arr ('lines.csv', ';;', '1') ); c_scenario_lis CONSTANT L1_chr_arr := L1_chr_arr ('Tie-break/single-delimiter/interior column', 'Two copies/double-delimiter/first column'); c_inp_group_lis CONSTANT L1_chr_arr := L1_chr_arr ('Parameter', 'File'); c_inp_field_2lis CONSTANT L2_chr_arr := L2_chr_arr ( L1_chr_arr ('File Name', 'Delimiter', '*Column'), L1_chr_arr ('Line') ); c_out_group_lis CONSTANT L1_chr_arr := L1_chr_arr ('Sorted Array'); c_out_fields_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr ('Key', '*Count')); /*************************************************************************************************** Do_Test: Main local procedure for TRAPIT testing Col_Group methods ***************************************************************************************************/ PROCEDURE Do_Test (p_proc_name VARCHAR2, p_exp_2lis L2_chr_arr, p_out_group_lis L1_chr_arr, p_out_fields_2lis L2_chr_arr) IS l_timer_set PLS_INTEGER; l_inp_3lis L3_chr_arr := L3_chr_arr(); l_act_2lis L2_chr_arr := L2_chr_arr(); /*************************************************************************************************** Setup: Setup procedure for TRAPIT testing Col_Group package. Writes test file, then calls constructor API to store data in an array, line counts grouped by key ***************************************************************************************************/ PROCEDURE Setup (p_file VARCHAR2, -- file name p_delim VARCHAR2, -- delimiter p_colnum PLS_INTEGER, -- key column number in file p_dat_lis L1_chr_arr, -- lines to write to test file x_inp_2lis OUT L2_chr_arr) IS -- generic inputs list BEGIN Utils.Delete_File (p_file); Utils.Write_File (p_file, p_dat_lis); x_inp_2lis := L2_chr_arr (L1_chr_arr (Utils.List_Delim (p_file, p_delim, p_colnum)), p_dat_lis); Col_Group.AIP_Load_File (p_file => p_file, p_delim => p_delim, p_colnum => p_colnum); END Setup; /*************************************************************************************************** Call_Proc: Calls the base method according to calling procedure, and converts record lists to delimited form, and populates the actual list for later checking ***************************************************************************************************/ PROCEDURE Call_Proc (x_act_lis OUT L1_chr_arr) IS -- actual values list (delimited records) l_arr_lis chr_int_arr; l_act_lis L1_chr_arr := L1_chr_arr(); BEGIN l_arr_lis := CASE p_proc_name WHEN c_proc_name_asis THEN Col_Group.AIP_List_Asis WHEN c_proc_name_key THEN Col_Group.AIP_Sort_By_Key WHEN c_proc_name_value THEN Col_Group.AIP_Sort_By_Value END; Timer_Set.Increment_Time (l_timer_set, Utils_TT.c_call_timer); l_act_lis.EXTEND (l_arr_lis.COUNT); FOR i IN 1..l_arr_lis.COUNT LOOP l_act_lis(i) := Utils.List_Delim (l_arr_lis(i).chr_field, l_arr_lis(i).int_field); END LOOP; x_act_lis := CASE p_proc_name WHEN c_proc_name_asis THEN L1_chr_arr(l_arr_lis.COUNT) ELSE l_act_lis END; END Call_Proc; BEGIN l_timer_set := Utils_TT.Init (p_proc_name); l_act_2lis.EXTEND (c_file_2lis.COUNT); l_inp_3lis.EXTEND (c_file_2lis.COUNT); FOR i IN 1..c_file_2lis.COUNT LOOP Setup (p_file => c_prms_2lis(i)(1), p_delim => c_prms_2lis(i)(2), p_colnum => c_prms_2lis(i)(3), p_dat_lis => c_file_2lis(i), -- data file inputs x_inp_2lis => l_inp_3lis(i)); Timer_Set.Increment_Time (l_timer_set, 'Setup'); Call_Proc (l_act_2lis(i)); END LOOP; Utils_TT.Check_TT_Results (p_proc_name, c_scenario_lis, l_inp_3lis, l_act_2lis, p_exp_2lis, l_timer_set, c_ms_limit, c_inp_group_lis, c_inp_field_2lis, p_out_group_lis, p_out_fields_2lis); END Do_Test; /*************************************************************************************************** tt_AIP_List_Asis: Entry procedure for TRAPIT testing Col_Group.AIP_List_Asis ***************************************************************************************************/ PROCEDURE tt_AIP_List_Asis IS c_proc_name CONSTANT VARCHAR2(61) := c_proc_name_asis; c_exp_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr('3'), L1_chr_arr('2')); c_out_group_lis CONSTANT L1_chr_arr := L1_chr_arr ('Counts'); c_out_fields_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr ('*#Records')); BEGIN Do_Test (c_proc_name, c_exp_2lis, c_out_group_lis, c_out_fields_2lis); END tt_AIP_List_Asis; /*************************************************************************************************** tt_AIP_Sort_By_Key: Entry procedure for TRAPIT testing Col_Group.AIP_Sort_By_Key ***************************************************************************************************/ PROCEDURE tt_AIP_Sort_By_Key IS c_proc_name CONSTANT VARCHAR2(61) := c_proc_name_key; c_exp_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('A','2'), Utils.List_Delim ('Bx','1'), Utils.List_Delim ('Cc','1')), L1_chr_arr (Utils.List_Delim ('X','2')) ); BEGIN Do_Test (c_proc_name, c_exp_2lis, c_out_group_lis, c_out_fields_2lis); END tt_AIP_Sort_By_Key; /*************************************************************************************************** tt_AIP_Sort_By_Value: Entry procedure for TRAPIT testing Col_Group.AIP_Sort_By_Value ***************************************************************************************************/ PROCEDURE tt_AIP_Sort_By_Value IS c_proc_name CONSTANT VARCHAR2(61) := c_proc_name_value; c_exp_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('B','1'), Utils.List_Delim ('Cc','1'), Utils.List_Delim ('A','2')), L1_chr_arr (Utils.List_Delim ('X','2')) ); BEGIN Do_Test (c_proc_name, c_exp_2lis, c_out_group_lis, c_out_fields_2lis); END tt_AIP_Sort_By_Value; END TT_Col_Group; /

TRAPIT Output


PL/SQL procedure successfully completed.


TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAPIT TEST: Col_Group.tt_AIP_List_Asis
=======================================

SCENARIO 1: Tie-break/single-delimiter/interior column {
========================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ,               3

        }
        =

        GROUP File {
        ============

            Line
            -----------
            0,1,Cc,3
            00,1,A,9
            000,1,B,27
            0000,1,A,81

        }
        =

    OUTPUTS
    =======

        GROUP Counts: Actual = 1, Expected = 1 {
        ========================================

            F?  #Records
            --  --------
                       3

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

SCENARIO 2: Two copies/double-delimiter/first column {
======================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ;;              1

        }
        =

        GROUP File {
        ============

            Line
            -------
            X;;1;;A
            X;;1;;A

        }
        =

    OUTPUTS
    =======

        GROUP Counts: Actual = 1, Expected = 1 {
        ========================================

            F?  #Records
            --  --------
            F          1
            >          2

        } 1 failed, of 1: FAILURE
        =========================

} 1 failed, of 1: FAILURE
=========================

TIMING: Actual = 0, Expected <= 2: SUCCESS
==========================================

SUMMARY for Col_Group.tt_AIP_List_Asis
======================================

Scenario                                    # Failed  # Tests  Status
------------------------------------------  --------  -------  -------
Tie-break/single-delimiter/interior column         0        1  SUCCESS
Two copies/double-delimiter/first column           1        1  FAILURE
Timing                                             0        1  SUCCESS
------------------------------------------  --------  -------  -------
Total                                              1        3  FAILURE
------------------------------------------  --------  -------  -------

Timer Set: Col_Group.tt_AIP_List_Asis, Constructed at 13 Nov 2016 09:07:08, written at 09:07:08
===============================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000006), CPU (per call): 0.00 (0.000000), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.07        0.03             2        0.03700        0.01500
Caller         0.00        0.00             2        0.00000        0.00000
(Other)        0.01        0.01             1        0.00900        0.01000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.08        0.04             5        0.01660        0.00800
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: Col_Group.tt_AIP_Sort_By_Key
=========================================

SCENARIO 1: Tie-break/single-delimiter/interior column {
========================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ,               3

        }
        =

        GROUP File {
        ============

            Line
            -----------
            0,1,Cc,3
            00,1,A,9
            000,1,B,27
            0000,1,A,81

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                A        2
            F   B        1
            >   Bx       1
                Cc       1

        } 1 failed, of 3: FAILURE
        =========================

} 1 failed, of 3: FAILURE
=========================

SCENARIO 2: Two copies/double-delimiter/first column {
======================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ;;              1

        }
        =

        GROUP File {
        ============

            Line
            -------
            X;;1;;A
            X;;1;;A

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                X        2

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 2, Expected <= 2: SUCCESS
==========================================

SUMMARY for Col_Group.tt_AIP_Sort_By_Key
========================================

Scenario                                    # Failed  # Tests  Status
------------------------------------------  --------  -------  -------
Tie-break/single-delimiter/interior column         1        3  FAILURE
Two copies/double-delimiter/first column           0        1  SUCCESS
Timing                                             0        1  SUCCESS
------------------------------------------  --------  -------  -------
Total                                              1        5  FAILURE
------------------------------------------  --------  -------  -------

Timer Set: Col_Group.tt_AIP_Sort_By_Key, Constructed at 13 Nov 2016 09:07:08, written at 09:07:08
=================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000006), CPU (per call): 0.00 (0.000000), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.03        0.03             2        0.01400        0.01500
Caller         0.00        0.00             2        0.00150        0.00000
(Other)        0.01        0.02             1        0.01000        0.02000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.04        0.05             5        0.00820        0.01000
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: Col_Group.tt_AIP_Sort_By_vALUE
===========================================

SCENARIO 1: Tie-break/single-delimiter/interior column {
========================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ,               3

        }
        =

        GROUP File {
        ============

            Line
            -----------
            0,1,Cc,3
            00,1,A,9
            000,1,B,27
            0000,1,A,81

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                B        1
                Cc       1
                A        2

        } 0 failed, of 3: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 2: Two copies/double-delimiter/first column {
======================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ;;              1

        }
        =

        GROUP File {
        ============

            Line
            -------
            X;;1;;A
            X;;1;;A

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                X        2

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 1, Expected <= 2: SUCCESS
==========================================

SUMMARY for Col_Group.tt_AIP_Sort_By_vALUE
==========================================

Scenario                                    # Failed  # Tests  Status
------------------------------------------  --------  -------  -------
Tie-break/single-delimiter/interior column         0        3  SUCCESS
Two copies/double-delimiter/first column           0        1  SUCCESS
Timing                                             0        1  SUCCESS
------------------------------------------  --------  -------  -------
Total                                              0        5  SUCCESS
------------------------------------------  --------  -------  -------

Timer Set: Col_Group.tt_AIP_Sort_By_vALUE, Constructed at 13 Nov 2016 09:07:08, written at 09:07:08
===================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000008), CPU (per call): 0.01 (0.000010), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.03        0.03             2        0.01450        0.01500
Caller         0.00        0.00             2        0.00050        0.00000
(Other)        0.01        0.02             1        0.01100        0.02000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.04        0.05             5        0.00820        0.01000
-------  ----------  ----------  ------------  -------------  -------------

Suite Summary
=============

Package.Procedure               Tests  Fails         ELA         CPU
------------------------------  -----  -----  ----------  ----------
Col_Group.tt_AIP_List_Asis          3      1        0.08        0.04
Col_Group.tt_AIP_Sort_By_Key        5      1        0.04        0.05
Col_Group.tt_AIP_Sort_By_vALUE      5      0        0.04        0.05
------------------------------  -----  -----  ----------  ----------
Total                              13      2        0.17        0.14
------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "LIB.UTILS_TT", line 152
ORA-06512: at "LIB.UTILS_TT", line 819
ORA-06512: at line 5


376 rows selected.

TRAPIT Notes

  • The approach to code-sharing and data-driving is similar to that used in the utPLSQL version
  • No assertions are made at all in the client code; the actual values are collected and passed to the library procedure for assertion
  • The famous 'arrange-act-assert' OO pattern is therefore not followed, with no ill effects
  • The output displays all inputs and outputs in 3-level format: Scenario/Group/Record with scenario descriptions, group names and column headings passed in

Oracle - SQL Developer Unit Test

I briefly tried to use this gui framework as well, but soon gave up when I could not see how to handle the object array return values.

Conclusions

  • Some significant differences in the functionality of the frameworks between utPLSQL and JUnit have been noted
  • Following design patterns for testing from the OO world may not always be advisable
  • It may be best to drop the term 'unit testing' for the database altogether, with the understanding that testing only transactional APIs such as web service procedures is a much more efficient approach
  • Consider using a data-driven approach to testing multiple scenarios, in testing both database and Java code, where applicable

All code and output can be seen on polyglot_group on GitHub, where Python, Ruby, Perl and Scala versions are also included.






 

A Note on Dependencies and Database Unit Testing

Ideas on unit testing for the database are often heavily influenced by the world of object oriented programming (OOP), usually Java in practice. This is no doubt because much of modern thinking on development methodologies, including test driven development (TDD), originated in this world. Some of these ideas appear to translate very well into the database world, including that of TDD itself, with automated unit tests. However, some ideas may not translate so well, or even make sense, in database unit testing. For example, Roy Osherove (2011), Unit Test – Definition says:

A good unit test … runs in memory (no DB or File access, for example)

One concept that appears very important in the OOP world is that of dependencies, and of isolation of the code under test from its dependencies. This gives rise to complex mechanisms of ‘mocking’ and ‘dependency injection’ to bring about said isolation. Osherove mentions isolation in the same article as a requirement of good unit testing, and his view appears to be widespread. It’s worth mentioning though that not everyone in the OOP world shares his insistence. The influential Martin Fowler (2014) uses a nice terminology of ‘sociable’ tests (as opposed to ‘isolated’ tests) for tests that rely on other units to fulfill the behaviour under test, and he uses this approach himself when practicable, UnitTest.

In the case of database unit testing, it seems to me to make very little sense to think in terms of isolating code under test from its dependencies. The following two diagrams represent how I see the relationships between base code, dependencies and unit test code across two distinct phases.

Development Phase
UT Phases-dev

Regression Phase
UT Phases-reg

I would welcome any comments or opinions.






 

Code Timing and Object Orientation and Zombies

This is a Word document that I wrote initially in November 2010 and uploaded to Scribd, Code Timing and Object Orientation and Zombies, with a big update in September 2012, about code timing in several languages.

I am going to upload a number of articles to this blog shortly that make use of an updated version of the Oracle code-timing package, and I am aware that many work networks disallow Scribd access, so I thought I would put it here first, Code Timing and Object Orientation and Zombies. It’s also embedded below (if that is accessible).

Here is the introduction:

This article proposes an object-oriented design for simple CPU and elapsed timing of computer programs by individual code section or subroutine. The object data structure is first described using a diagram/tabulation approach first used in A Perl Object for Flattened Master-Detail Data in Excel, followed by a section describing method usage, and including a diagram showing a typical call structure.

The object class is then translated from the Ur-language of design into the programming languages of Oracle, Perl and Java (one might say that our class of class is instantiated into specific object classes for each language). In each case the code is listed, an example driving program is briefly described, the run results are listed, and any interesting features are highlighted.

Oracle’s implementation of object-orientation is rather different from other languages, and a section of the article discusses how one can best obtain the advantages of object orientation in Oracle, suggesting that it’s often better to bypass the ‘official’ object structures. Both approaches have been implemented here to help readers judge for themselves.

Finally, some notes are collated on differences between the languages.