Test Guide

Testing views

When creating database views, the most common failures are missing or duplicate records, and wrong data values.

A wrong record count is mostly caused by incorrect joins. If you forget to define an outer join, your view will miss records. If you don't use the propper join conditions, you might get duplicate records. If you forget the join conditions for one of the tables, you'll get a lot of wrong records.

You can avoid these mismatches by asserting that your view contains the correct number of records.
Most of the time there is one table in the view that is the driving table. It's the core table of the view. Compare the record count of that table with the record count of your view.

When creating a view for order lines with additional item and customer info, the driver could be oe_order_lines_all. It's the number of order lines in the system that should decide how many rows your view returns, not the number of items or customers you have. Your view should return exactly the same record count as table oe_order_headers_all.


How to test for missing or extra records in a view?

Find the table that is the core of your view (the driving table).

Assert that the record count of the driving table matches the record count of your view.

simpleCompare(
"select count(*) from oe_order_lines_all",
"select count(*) from xx_order_lines_view");

You can create this test at the very begin of your view design phase. Then start by creating your basic view:

create view xx_order_lines_view as
select
  line_id
from
  oe_order_lines_all
with read only;

The tests will pass. You can now safely add extra fields and tables to the view. Run the tests again. If you forget an outer join, or create a cardinal join, your tests will fail.

Validating the correctness of a field value in your view is straightforward. You write a quey that asserts wether your view returns the value you expect.

If you want to validate the value for the customer, you could locate an example order line in your application and write down the customer name (the expectation). Then write a query to verify that your view returns this customer.

select
  count(*)
from
  xx_order_lines_view
where
    line_id = 25845
and customer_name = 'SOURCEFORGE';


How to test for correct field values in a view?

Locate an example that has a known value for the field.

Assert that the view returns this expected value.

simpleCompare(
"select 1 from dual",
"select
   count(*)
 from
   xx_order_lines_view
 where
     line_id = 23548
 and item_description = 'Feature Request'");

Add regression tests during the lifecycle of your view. Whenever you find a wrong value in your view, or when someone raises a bug against your view, create a test that fails for the given error.

If the error report says that your view reports that the order line was not closed, but the application shows that it's closed, you can create a regression test:

select
  count(*)
from
  xx_order_lines_view
where
  line_id = 12487
and closed_status = 'Y';

The test will pass when your bug is resolved. And because you add this test to the test suite, this bug will never have a chance of showing up again.


How to avoid regression?

Create a regression test for each bug or error reported for your view.

Add the test to your test suite.

simpleCompare(
"select 0 from dual",
"select
   count(*)
 from
   xx_order_lines_view
 where
     line_id = 48574
 and payment_date < order_date");

Testing functions

The smartest way to test a database function is to write a query that validates if the function returns expected results.

If you have a function that converts amounts from one currency to another, you can assert this as follows:

select
  count(*)
from
  dual
where
    xx_currency_spot_convert(
      1200, 'USD', 'EUR', '01-JAN-2001') = 1193;

The test will pass when the function returns the correct value for these parameters.


How to test a stored function?

Locate an example that has a known return value for a given set of parameters.

Assert that the function returns this expected value.

simpleCompare(
"select 1 from dual",
"select
   count(*)
 from
   dual
 where
     xx_currency_spot_convert(
      821.256, 'USD', 'USD', '19-JAN-2003') = 821.256")

Powered by Drupal - Design by Artinet