How to mock objects in Oracle / How to mock objects in MySQL

The most significant difference between the major relational databases in terms of unit testing, is whether Data Definition Language (DDL) is transactional.  In plain language, can a transaction rollback the creation of database objects?  Microsoft SQL Server’s excellent open-source unit-testing framework, tSQLt, is just not possible in databases like Oracle where DDL ends transactions.  However, all is not lost.

Knowing in advance that you want to mock objects in order to run your unit tests, you should design your database schema accordingly.  The trick is to never reference a table or view by its name in Data Manipulation Language (DML), meaning SELECT, INSERT, UPDATE, and DELETE statements.  Reference a synonym instead.  The synonym provides an interface layer that allows the object to be mocked out with a minimum of side-effects.

Example

If we start with the following schema, where the DML references synonyms:

CREATE TABLE customer_private
(customer_id INT 
,name VARCHAR(50)
,PRIMARY KEY (customer_id)
);

CREATE TABLE order_private
(order_id INT PRIMARY KEY
,customer_id INT REFERENCES customer_private (customer_id)
,order_date DATE NOT NULL
,price NUMERIC(10,2) NOT NULL
);

CREATE OR REPLACE SYNONYM customers FOR customer_private;
CREATE OR REPLACE SYNONYM orders FOR order_private;

CREATE OR REPLACE VIEW customer_orders AS
  SELECT c.customer_id
  , c.name
  , coalesce(sum(o.price),0) total_sales
  FROM customers c
  LEFT OUTER JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.name;

The examples in this post were tested on Oracle 12c. The Common Table Expression requires 9iR2 the rest should be compatible back to Oracle 7.

Then we can arrange our test with synonyms:

CREATE TABLE unit_test_customer AS 
SELECT * 
FROM customer_private
WHERE 0 = 1;

CREATE OR REPLACE SYNONYM customers FOR unit_test_customer;

CREATE TABLE unit_test_order AS 
SELECT * 
FROM order_private
WHERE 0 = 1;

CREATE OR REPLACE SYNONYM orders FOR unit_test_order;

INSERT INTO unit_test_customer (customer_id, name) VALUES (1,'Alice');
INSERT INTO unit_test_customer (customer_id, name) VALUES (2,'Bob');
INSERT INTO unit_test_customer (customer_id, name) VALUES (3,'Cathy');

INSERT INTO unit_test_order (order_id, customer_id, order_date, price) VALUES (1,2,sysdate,1);
INSERT INTO unit_test_order (order_id, customer_id, order_date, price) VALUES (2,3,sysdate,10);
INSERT INTO unit_test_order (order_id, customer_id, order_date, price) VALUES (3,3,sysdate,100);

Assert our test (of course you’d use a framework):

WITH expected as (
  SELECT 1 customer_id, 'Alice' name ,0 total_price FROM dual UNION ALL
  SELECT 2            , 'Bob'        ,1             FROM dual UNION ALL
  SELECT 3            ,'Cathy'       ,110           FROM dual
)
, comparison as (
  (SELECT * FROM customer_orders actual MINUS SELECT * FROM expected ) UNION ALL
  (SELECT * FROM expected MINUS SELECT * FROM customer_orders actual ) 
  )   
SELECT CASE WHEN (SELECT count(*) FROM COMPARISON) > 0 THEN 'Fail'
       ELSE 'Pass'
       END
FROM dual;

Finally, cleanup is straightforward:

CREATE OR REPLACE SYNONYM customers FOR customer_private;
CREATE OR REPLACE SYNONYM orders FOR order_private;

Conclusion

Changing the synonym does cause a commit, which ends your transaction isolation as soon as you start.  Which means that your database is effectively in single-user mode while the unit tests run.  The unit tests themselves can’t run in parallel either, as their mocking will interfere with each other. None of which will be overly onerous if you’re running tests in a fresh database as part of your code pipeline, but which could cause trouble if you have a static, shared test database.

I don’t think unit tests can run reliably without controlled input data.  So I highly recommend this technique if you use a database where DDL is not transactional.

This work is licensed under a Creative Commons Attribution 4.0 International License.

Comments