Shared fragments are a real game-changer for CQL.
Remember, these are designed to let you write part of a query and then substitute in parameters. So it's like a parameterized view in normal SQL terms. But actually it's more powerful than that, fragments also provide features that are more like Java generics. Let's do some examples.
Suppose we have a procedure which looks something like this:
With shared fragments you could write something like this:
We now have a shared fragment called
split_commas which can be anywhere like maybe in a standard include file. There are some immediate benefits:
- the fragment is compiled on its own before usage so any errors are reported in the fragment
- in contrast, with macros you get errors when you try to use the macro and they are all charged to the line the macro appears on so it's hopeless figuring out what's wrong
- the text of the shared fragment will be the same, so it can be re-used in all locations, this can be a big binary size savings
- in contrast, macros are pre-processed before CQL ever sees the text so it doesn't "know" it's the same code
- fragments compose cleanly as we'll see; and they have typed arguments
- fragments can be independently tested outside of the context in which they appear
- make a test context and explore the fragment, no worries about it breaking on edge cases later
The first fragment called
split_commas does exactly what it sounds like, it takes a string argument and makes a list of the strings in it.
The second fragment uses the first to split a string and then it converts all the strings to long integers.
Now instead of the above we could write:
And of course since
ids_from_string is somewhere shared (
stringsplit.sql) so these fragments can be used
all over your code and you'll only pay for the text one time. This gives you great flexibility, very much
like parameterized views. You can have any number of these fragments, they will share code, they compose like crazy
and there is no schema cost!
A series of useful fragments for generating data would go a long way but there are other applications
of fragments and you might want to operate on various data sources without hard coding them all. This is
where the generic form of fragments comes in. Consider a case where you want to be able to filter
by say name and age. You could create this fragment:
Now imagine that we had added the shared fragment annotation to
get_stuff (just like the above).
We could then write the following:
Or with some sugar to forward arguments and assume the CTE name matches, more economically:
The arg syntax
(*) simply indicates that the arg names in the caller should match to the same names in the callee. In
call foo(*) expands to
call foo(from arguments like foo arguments).
* is rather more economical than that.
In this example
filter_stuff doesn't know where its data will be coming from, you bind its table parameter
to a compatible data source of your choice. For example, this would also be legal:
It's often desirable to have some options in the generated SQL without having to fork your entire query. Shared
fragments address this as well with the conditional form. In this form the top level of the fragment is an
IF statement and there are a number of alternatives. Here are some simple modifications to the above that illustrate
some of the possibilities.
In the above if the input pattern is NULL then it is not considered, it won't be part of the generated SQL at all. Note that
source (same name) appears in both branches and therefore must be the same type as it will be fulfilled by one actual table
Now the above could have been achieved with something like this:
But that would have no useful selectivity. But in general you might be able to avoid joins and so forth with your constraints. Consider something like this hypothetical:
Here we save the DISTINCT and the JOIN if there is no pattern which might be important. Of course there are probably better ways to match keywords but this is just an illustration of what's possible.
There are numerous ways this flexibility can be used, again a simple example, a real schema transform would be more complex.
All of this requires a bunch of checking, at least this:
- the LIKE forms can only appear in a shared fragment
- the CALL forms must refer to shared fragments
- the CALL args must be compatible
- the number and type of the provided tables in USING must be correct
- the shared fragment must be a single select statement or an IF statement with an ELSE
- the statement lists of the IF/ELSE combo must all be single select statements
- all the choices in the IF block must return the same shape (this is normal for procedures)
- the shared fragment can't have any out arguments
- the provided fragment arguments cannot themselves use the nested SELECT construct
I think this is a total game changer for SQL authoring and should go a long way to making it easier to get your work done on SQLite. A good base set of shared fragments as part any suite of procedures seems like a good idea.
There are more details in the section on shared fragments in Chapter 14 of The Guide.
These features are in the current build as of today (12/14/2021).
Happy Holidays and stay safe.