A quick tutorial on LIKE forms
CG/SQL Team
Maintainer of CG/SQLEveryone knows the usual expression syntax x LIKE y
to do a string match. But the CQL compiler also uses
LIKE
in a different way that's powerful and important. CQL has the notion of data shapes and you use
LIKE
to refer to them. The simplest source of a data shape, and maybe the most common, is a table.
Maybe something like this:
Now suppose you want to write a procedure that can insert a row into that table, You could write
This is all fine and well but what if T had 50 columns? That gets old fast. And how can you be sure that you inserted the columns into T in the right order? This second example also compiles even though it's clearly wrong:
And of course you can imagine things get only more complicated with more columns in T.
We started adding the LIKE
form to ease these issues and to ensure some consistency in the APIs while preventing
simple transpostion errors. So you can instead write:
so here the like T
in the argument list simply means "make arguments that are the same as the columns of table T" -- well,
almost. It also adds an _
to the end of each name so you end up exactly the same declaration as the long form above.
But you won't miss any arguments, and they'll be in the right order.
And notice that we used from arguments
to indicate that we wanted the values to come from the arguments in order. Again
this saves you from a lot of typing and a lot of error checking. You can't get the arguments in the wrong order.
These are the most basic patterns. But there are quite a few more.
Let's suppose you want to write a procedure that returns in row with the highest age in the above. Maybe you write something like this:
Here we made a cursor M
that is the same as the cursor C
and then we are going to generate a single row result
from the cursor. Note that if you use a cursor name like M
in an expression it refers to the hidden boolean
that says if the cursor has a row in it or not. So M
begins empty and we will load it if it's empty or if the age
is higher than what we've already got.
Let's show a few more of the forms. Suppose we don't want to return name
, just the id
and the age
. We can
change things up a tiny bit.
So two things to notice. We used an ad hoc shape, making a fake select
statement that returns the shape we want. This
select doesn't run but it does define types and columns easily. Two not null integers in this case. Now M
is not the
same as C
so we can't use the simplest form fetch M from C
we have to use the more general form.
Fully expanded, what we wrote becomes:
But as you can see, we didn't have to type all those column names. And that's kind of the point of the LIKE
construct.
So we've covered a bunch of the shape sources already:
- a table name
- a cursor name
- a select statement that gives the shape in an ad hoc fashion
There are three more
- a view name
- the return shape of a procedure that returns a result set
- the arguments of a procedure
View names are pretty simple, and they work the same as table names so we don't need to discuss those. Let's look at some of the other uses with procedures.
Suppose we have a procedure that can return a result set shape but we want to be able to mock its results so we can fake whatever result we need for testing.
We'll complicate this a bit adding a new table (keeping short table names for the sample to save typing)
And here's a procedure:
Now we want to be able to make any fake result we want, so maybe want a temp table. No problem:
The above is very generic and will maintain well. You can see we made a temp table that will have
exactly the same shape as whatever my_proc
returns. In this case it becomes:
And the rest are patterns we've seem before.
The last source of shapes are procedure arguments. There's lots of good cases for those, I wrote an entry on those previously but I'll give a simple example here too.
Suppose we have this weird procedure:
What if we wanted to log any errors that happen here? Maybe make a verison that logs. We can do it like this:
The nice thing about this logging wrapper procedure is that if delete_stuff
changes, the wrapper will change with it.
That covers all of the shape sources and as we saw we can use them to create things like cursors, tables, and argument lists.
We can use them to specify a subset of columns that might be of interest when fetching or updating cursors. And we can use
them in one last way -- to restrict arguments to a particular shape. Let's see how that works by making the previous logger
a little different. Here we added an argument which tells if we should look. And that might look like it would
spoil the from arguments
part of the forwarding, but there is the final way to use LIKE
.
So this form lets you use some of your arguments, the ones that match a certain shape. And as we saw in
the previous article you can also use from C
to pass arguments where C
is a cursor and in that case
you can also specify that arguments be matched by name from C like shape
. In both those cases the
formal parameter names of the called procedure are matched against the names of the shape and passed in
the order of the formals. So this is like "call by name", the fields of the cursor or the order of
arguments in the argument list might be different than the formals but you'll get the correct items
in the correct order regardless, because it matches by name.
These forms can save you a lot of typing... and are excellent at avoiding errors and improving maintainability.
Where they appear in SQL statements, everything is expanded before it goes to SQLite so SQLite will see
normal syntax forms. Which is good because obviously SQLite doesn't know anything about this enhanced
LIKE
business.
In the examples above there were only one or two columns with very short names, but in real world code there can easily be dozens of columns with very long names. In those cases, these forms really shine.