OSQL Testing

OSQL support unit testing through the validate statement. It contains a description and one or more check clauses.

For example, let's define a function prime that takes a number as input and return true if it is a prime:

create function prime(Integer n) -> boolean
  as select true where notany(select d
       from Integer d
      where d in range(2,floor(sqrt(n)))
        and 0 = mod(n,d));

Now we can test that we get the expected return for various parameters with a validate statement:

validate "my prime"
   check prime(0) => true
   check prime(13) => true
   check prime(4) => null
   check prime(20) => null
   check prime(31) => true;

Exercise

Try changing the statement so that it fails.

Validating streams

When working with streams, the functions vectorof(Stream b)->Vector v and extract(Stream s)->Bag are useful for converting the finite stream elements into vectors or bags, respectively. For example, consider a function that returns a stream of sliding windows over stream s:

create function slider(Stream s) -> stream of Vector
  as winagg(s,1,5);

The following statement validates the output from slider on finite streams of integers converted to vectors:

validate "slider with vectorof"
   check vectorof(slider(diota(0,1,10))) =>
    [[1],[6]];

The following converts the streams to bags instead of vectors:

validate "slider with extract"
   check extract(slider(diota(0,1,10))) => bag([1],[6])
   check extract(slider(diota(0,1,10))) => bag([6],[1]);

Note

A vector is an ordered sequence of objects while a bag is an unordered collection. This makes vectorof the preferred choice for testing stream output where the order is known. If the order is undeterministic, as may be the case with pivot streams, use extract.

The inverse of extract is streamof(Bag b)->Stream. The inverse of vectorof if vstream(Stream s)->Vector

For infinite real-time streams, a useful function is timeout that runs a stream a specified number of seconds.

timeout(heartbeat(1),2.5);

This allows to convert an infinite stream to a finite stream for real-time validation:

validate "heartbeat"
   check count(extract(timeout(heartbeat(1),2.5))) =>
    3;

Other useful testing functions

For more complex tests where the output is large, it can be useful to store the testdata in a CSV or JSON file.

Setting up or running a test may also involve populating database tables. Tables can be cleared by SQL's delete from mytable that deletes all rows in mytable.

The system function time_function can be used to compare the performance of different implementations. However, since external factors influence execution times, it must be interpreted with care. Try running time_function on the prime function a couple of times. Note that time_functions prints the total time to the console but returns the average time. It is the average time that is shown below.

time_function("Running prime(9856323)","prime",[9856323],100000);

When it is not possible to wait for a certain input, the sleep(Number seconds) is an option.

now();
sleep(1);
now();