CQL includes a number of features to make it easier to create what you might call "Test" procedures. These primarily are concerned with loading up the database with dummy data, and/or validating the result of normal procedures that query the database. There are several interesting language features in these dimensions.
Test code can be needlessly brittle, especially when creating dummy data; any column changes typically cause all sorts of data insertion code to need to be repaired. In many cases the actual data values are completely uninteresting to the test -- any values would do. There are several strategies you can use to get good dummy data into your database in a more maintainable way.
Simple Inserts With Dummy Data
The simplest form uses a variant of the insert statement that fills in any missing columns with a seed value. An example might be something like the below:
This statement causes all values including columns that are nullable or have a default value to get the value
123 for any numeric type and
'column_name_123' for any text.
If you omit the
@dummy_nullables then any nullable fields will be null as usual. And likewise if you omit
@dummy_defaults then any fields with a default value will use thatt value as usual. You might want any combination of those for your tests (null values are handy in your tests and default behavior is also handy).
@dummy_seed expression provided can be anything that resolves to a non-null integer value, so it can be pretty flexible. You might use a
while loop to insert a bunch of
rows with the seed value being computed from the
while loop variable.
The form above is sort of like
insert * into table in that it is giving dummy values for all columns but you can also specify some of the columns while using the seed value for others. Importantly, you can specify values you particularly want to control either for purposes of creating a more tailored test or because you need them
to match existing or created rows in a table referenced by a foreign key.
As an example:
Will provide dummy values for everything but the
Sometimes what you want to do is create a dummy result set without necessarly popuplating the database at all. If you have code that consumes a result set of a particular shape it's easy enough to create a fake result set with a pattern something like this:
The first part of the above creates a series of numbers from 1 to
lim. The second uses those values to create dummy columns.
Any result shape can be generated in this fashion.
You get data like this from the above:
The result of the select statement is itself quite flexible and if more dummy data is what you wanted, this form can be combined with
INSERT ... FROM SELECT... to create dummy data in real tables. And of course once you have a core query you could use it in a variety of ways
combined with cursors or any other strategy to
select out pieces and
insert them into various tables.
Using Temporary Tables
If you need an API to create very flexible dummy data with values of your choice you can use temporary tables and a series of helper procedures.
First, create a table to hold the results. You can of course make this table however you need to but the
like construct in the table creation
is especially helpful; it creates columns in the table that match the name and type of the named object. For instance
like my_proc is shorthand for the column names ands of the shape that
my_proc returns. This is perfect for
emulating the results of
Next, you will need a procedure that accepts and writes a single row to your temp table. You can of course write this all explicitly but the testing
support features provide more support to make things easier; In this example, arguments of the procedure will exactly match the output of the procedure we emulating,
one argument for each column the proc returns. The
insert statement gets its values from the arguments.
This allows you to create the necessary helper methods automatically even if the procedure changes over time.
Next we need a procedure to get our result set.
And finally, some cleanup.
Again the temp table could be combined with
INSERT INTO ...FROM SELECT... to create dummy data in real tables.
insert statements in
try/catch can be very useful if there may be dummy data conflicts. In test code searching for a new suitable seed is pretty easy. Alternatively
Could be very useful. Many alternatives are possible.
The dummy data features are not suitable for use in production code, only tests. But the LIKE features are generally useful for creating contract-like behavior in procs and there are reasonable uses for them in production code.
Complex Result Set Example
Here's a more complicated example that can be easily rewritten using the sugar features. This method is designed to return a single-row result set that can be used to mock a method. I've replaced the real fields with 'f1, 'f2' etc.
This can be written much more maintainably as:
Naturally real columns have much longer names and there are often a lot more than 10.
Some of the patterns described above are so common that CQL offers a mechanism to automatically generate those test procedures.
Temporary Table Pattern
The attributes dummy_table, dummy_insert, and dummy_select can be used together to create and populate temp tables.
To create a dummy row set for
sample_proc, add the cql:autotest attribute with dummy_table, dummy_insert, and dummy_select values.
dummy_table generates procedures for creating and dropping a temp table with the same shape as
dummy_insert attribute generates a procedure for inserting into the temp table.
dummy_select attribute generates procedures for selecting from the temp table.
It's interesting to note that the generated test code does not ever
need to mention the exact columns it is emulating because it can always use
from arguments in a generic way.
When compiled, the above will create C methods that can create, drop, insert, and select from the temp table. They will have the following signatures:
Single Row ResultSet
In some cases, using four APIs to generate fake data can be verbose. In the case that only a single row of data needs to be faked, the dummy_result_set attribute can be more convenient.
Will generate the following procedure
Which generates this C API:
These few test helpers are useful in a variety of scenarios and can save you a lot of typing and maintenance. They evolve automatically as the code changes, always matching the signature of the attributed procedure.
Generalized Dummy Test Pattern
The most flexible test helper is the
dummy_test form. This is far more advanced than the simple helpers above. While the choices above were designed to help you create fake result sets pretty easily,
dummy_test goes much further letting you set up arbitrary schema and data so that you can run your procedure on actual data. The
dummy_test code generator uses the features above to do its job and like the other autotest options, it works by automatically generating CQL code from your procedure definition. However, you get a lot more code in this mode. It's easiest to study an example so let's begin there.
dummy_test we'll need a more complete example, so let's start with this simple two-table schema with a trigger and some indices. To this we add a very small procedure that we might want to test.
As you can see, we have two tables
foo table has a trigger; both
bar have indices. This schema is very simple, but of course it could be a lot more complicated, and real cases typically are.
The procedure we want to test is creatively called
the_subject. It has lots of test attributes on it. We've already discussed
dummy_result_set above but as you can see they can be mixed in with
dummy_test. Now let's talk about
dummy_test. First you'll notice that annotation has additional sub-attributes; The attribute grammar is sufficiently flexible that, in principle, you could represent an arbitrary LISP program, so the instructions can be very detailed. In this case the attribute provides table and column names, as well as sample data. We'll discuss that when we get to the population code.
First let's dispense with the attributes we already discussed -- since we had all the attributes, the output will include those helpers, too. Here they are again:
That covers what we had before, so, what's new? Actually quite a bit. We'll begin with the easiest:
Probably the most important of all the helpers,
test_the_subject_create_tables will create all the tables you need to run the procedure. Note that in this case, even though the subject code only references
bar, CQL determined that
foo is also needed because of the foreign key.
The symmetric drop procedure is also generated:
Additionally, in this case there were triggers and indices. This caused the creation of helpers for those aspects.
If there are no triggers or indices the corresponding create/drop methods will not be generated.
With these helpers available, when writing test code you can then choose if you want to create just the tables, or the tables and indices, or tables and indices and triggers by invoking the appropriate combination of helper methods. Since all the implicated triggers and indices are automatically included, even if they change over time, maintenance is greatly simplified.
Note that in this case the code simply reads from one of the tables but in general the procedure under test might make modifications as well. Test code frequently has to read back the contents of the tables to verify that they were modified correctly. So these additional helper methods are also included:
Those procedures will allow you to easily create result sets with data from the relevant tables which can then be verified for correctness. Of course if more tables were implicated those would have been included as well.
As you can see the naming always follows the convention
Finally, the most complicated helper is the one that used that large annotation. Recall that we provided the fragment
(dummy_test, (bar, (data), ('plugh')))) to the compiler. This fragment helped to produce this last helper function:
In general the
populate_tables helper will fill all implicated tables with at least two rows of data. It uses the dummy data features discussed earlier to generate the items using a seed. Recall that if
@dummy_seed is present in an
insert statement then any missing columns are generated using that value, either as a string, or as an integer (or true/false for a boolean). Note that the second of the two rows that is generated also specifies
@dummy_defaults. This means that even nullable columns, and columns with a default value will get the non-null seed instead. So you get a mix of null/default/explicit values loaded into your tables.
Of course blindly inserting data doesn't quite work. As you can see, the insert code used the foreign key references in the schema to figure out the necessary insert order and the primary key values for
foo were automatically specified so that they could then be used again in
Lastly, the autotest attribute included explicit test values for the table
bar, and in particular the
data column has the value
'plugh'. So the first row of data for table
bar did not use dummy data for the
data column but rather used
In general, the
dummy_test annotation can include any number of tables, for each table you can specify any of the columns and you can have any number of tuples of values for those columns.
NOTE: if you include primary key and/or foreign key columns among the explicit values it's up to you to ensure that they are valid combinations. SQLite will complain as usual if they are not, but the CQL compiler will simply emit the data you asked for.
Generalizing the example a little bit, we could use the following:
to generate this population:
And of course if the annotation is not flexible enough, you can write your own data population as usual.
The CQL above results in the usual C signatures. For instance:
So it's fairly easy to call from C/C++ test code or from CQL test code.
Cross Procedure Limitations
Generally it's not possible to compute table usages that come from from called procedures. This is because to do so you need to see the body of the called procedure and typically that body is in a different translation -- and is therefore not available. A common workaround for that particular problem is to create a dummy procedure that explicitly uses all the desired tables. This is significantly easier than creating all the schema manually and still gets you triggers and indices automatically. Something like this:
The above can be be done as a macro if desired. But in any case
use_my_stuff simply and directly lists the desired tables.
Using this approach you can have one set of test helpers for an entire unit rather than one per procedure and that
is often desirable. And the maintenance is not too bad. You just use the
use_my_stuff test helpers everywhere.