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.
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;
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.