---
slug: update
title: One Month Update
author: CG/SQL Team
author_title: Maintainer of CG/SQL
author_url: https://github.com/facebookincubator
author_image_url: https://avatars2.githubusercontent.com/u/69631?s=200&v=4
tags: [facebook, cg-sql, update]
---

It's hard to believe it's been a month since the welcome message went up. We were
happy to see interest right away and even a bunch of forks but most of all
pull requests.  A sweeping change to modernize the cql.y grammar was much
appreciated.  That `$1` stuff was very old school (I'm showing my age now).

## Here's a quick summary of what's been going on:

* @mingodad gave us an implementation of check and collate column attributes (the check attribute on tables should be easy to add from here)
* the `select function` form should never return objects, only SQLite types, enforced
* @attribute(cql:suppress_result_set) was added to save code gen for procedures that don't need the C result set wrappers
* `cql_cursor_diff_col` and `cql_cursor_diff_val` methods were added to report what's different about two cursors (highly useful in test code)
* `cql_cursor_format` was added so you can quickly convert any cursor into columns and values as string for debug output (no matter the shape)
* `sqlite3_changes` was added to the builtin list so you don't have to use `declare select function` to use it anymore
* `cql_get_blob_size` was added so you can see how big your blobs are (useful for diagnostics)
* `trim`, `rtrim` and `ltrim` were added to the builtin list so you can use them without `declare select function`
* the builtin function `ifnull_crash` was added so that nullables that have already checked can be safely typecast to not null
* the bug we saw in demo video number 2 where some foreign keys were not properly linked up in autotest code was fixed (yay videos)
* time functions are now known to be `not null` for a bunch of simple cases such as 'now' arguments
* you can use the `cast(.. as ..)` operator on numeric types outside of the SQL context
* @mingodad replaced all the positional references by named references in cql.y (yes! thank you!)
* several minor bug fixes
* the railroad diagrams were updated

NOTE: I often refer to "sugar" in the below.  This is short for syntatic sugar which, in case you're not familiar with the term, refers to a syntatically more pleasing way of writing a concept that is otherwise totally doable with normal syntax.  Many languages have sugar for forms that are common -- for brevity, clarity, and/or correctness.

## And now a few notes on The Big Stuff

We often add new features to the language to facilitate the writing of tests. The tests have a lot of boilerplate often setting up
and calling the same procedures again and again with slightly different arguments. Long argument lists and long insert column
lists are especially problematic as these can be very error prone. Here good language constructs are very helpful.
We've found good test constructs are often invaluable in production code as well, though in our experience the
tests often have a lot more repitition that needs refactoring than production code. To that end we added some very useful things
in the last month:

### Declare cursors in the shape of a procedure's arguments and use them

The most common way to create a cursor is from a `select` statement but you can also make a cursor that can hold values for you
by declaring it to be `LIKE` something else with a shape.  A classic example is:

```sql
declare C cursor like some_table;
```

Now `C` has the same columns and types as `some_table`

Many procedures have a result type that is also a shape, for instance any procedure that ends with a `select` statement has a result
shape defined by the columns of the select statement.  You could always do this sort of thing:

```sql
declare C cursor like some_proc;
```

Meaning make `C` a cursor whose shape is whatever `some_proc`returns, which is of course exactly the kind of cursor you need to capture
the result of `some_proc`.

Now we add:

```sql
declare C cursor like some_proc arguments;
```

The idea being that the arguments of `some_proc` are also a shape (unless it has none). With this done you want to use that cursor
to call the procedure -- that being sort of the whole point.  So we add this:

```sql
call some_proc(from C);
```

How do we use this effectively?  Hold on just a second -- for that answer we need one more big tool to really help the syntax.

### Loading cursors and inserting columns

Loading up a cursor is done with syntax that is very much like an `insert` statement.  An example might be something like this:

```sql
fetch C(x,y,z) from values(1,2,3);
```

This is simple enough but it becomes more problematic if there are many values and especially if the values have complex names.
To make this a little less error prone CQL now has this sugar form for `fetch`, `insert`, and soon `update cursor` (like maybe
before you see this blog).  The more readable form is:

```sql
fetch C using
  1  x,
  2  y,
  3  z;
```

This form has the values next to their names just like in a select statement, like all sugars, it is automatically rewritten to the normal form.

Likewise

```sql
insert into some_table using
  1            id,
  'fred'       first_name,
  'flintstone' last_name,
  'bedrock'    home_town,
  'dino'       favorite_pet,
  'wilma'      life_partner;
```

becomes

```sql
insert into some_table(id, first_name, last_name, home_town, favorite_pet, life_partner)
  values(1, 'fred', 'flintstone', 'bedrock', 'dino', 'wilma');
```

except the sugar form is much less error prone.  This form doesn't generalize to many values but the single row case is super common.

Since this form is automatically rewritten SQLite will never see the sugar syntax, it will get the normal syntax.

NOTE: the insert rewrite is coming later today, and will likely be live by the time you read this.


### Putting these together

Let's suppose you have to write a test.  You have a procedure `test_subject` that takes some arguments plus
you have another helper procedure `test_setup` that puts seed data in the right places for your subject.
But there are many variations and  a lot of what you do between variations is the same.  How can you write this
economically making it clear what is different between variations without a lot of fuss.
Well you can do something like this:


```sql
-- use defaults for all the named values
-- use 'seed' for everything else that isn't named
create proc default_setup_args(seed integer not null)
begin
  declare args cursor like test_setup arguments;
  fetch args using
    1334    primary_id,
    98012   secondary_id,
    'foo'   useful_name,
    'bar'   other_useful_name,
    1       fast_mode
    @dummy_seed(seed);
  out args;
end;
```

With the above you can easily see which values go to which arguments

Your test setup can now look something like this:

```sql
declare setup_args cursor like test_setup arguments;
fetch setup_args from call default_setup_args(1999);
update cursor setup_args using
   0 fast_mode;  -- override fast mode for this test
call test_setup(from setup_args);
```

To call the test subject you probably need some of those setup arguments and maybe some more things.

```sql
create proc default_subject_args(like default_setup_args, other_thing bool not null)
begin
  declare args cursor like test_subject arguments;
  fetch args using
     primary_id    primary_id,    -- this came from the default_setup_args result
     secondary_id  secondary_id,  -- so did this
     useful_name   name,          -- the field names don't have to match
     fast_mode     fast_mode,
     other_thing   other_thing;
  out args;
end;
```

Then the test code

```sql
declare test_args cursor like test_subject arguments;
fetch test_args from call default_subject_args(0);
call test_subject(from test_args);
```

Importantly, the cursor set operations are all by name so the order doesn't matter.  Which means even if there are many arguments
you don't have to worry that you got them in the wrong order or that they are the wrong type.  Effectively you have
a simple call by name strategy and you can easily read off the arguments.  You could do something similarly brief with
helper functions to provide the default arguments but then you can't readily re-use those arguments in later calls or
for verification so this way seems a lot more useful in a test context.

When it comes time to validate, probably your test subject is returning a cursor from a select that you want to check.
A slightly different call will do the job there.

### Cursor Differencing

With the setup above you can verify results very easily.  Let's change it a little bit:

```sql
-- same as before, with a cursor
declare results cursor for call test_subject(from test_args);

-- get the first row
fetch results;

declare expected cursor like results;
fetch expected using
   setup_args.primary_id     primary_id,
   setup_args.useful_name    name,
   test_args.other_thing     other_thing
   @dummy_seed(1999);   -- dummy values for all other columns

-- make a macro like EXPECT_CURSOR_EQ(x,y) for this
-- if the cursors are different the result is a string with the first
-- different column name and the left and right values ready to print

call ExpectNull(cql_cursor_diff_val(expected, result));
```

ExpectEqual could be

```sql
create proc ExpectNull(t text)
begin
  if t is not null then
    call printf('%s\n', t); -- or whatever
    throw;
  end if;
end;
```

All that testing support comes from:
* cursors in the shape of arguments
* cleaner fetch/insert syntax
* cursors passed as arguments
* cursor differences

It kills a lot of boilerplate resulting in tests that are much clearer.

And that's what's been going on for the last month in CG/SQL land.

If you got this far thanks for reading.  If you didn't get this far,
you aren't reading this anyway so thanking you is moot =P

Stay safe.

Rico for CG/SQL

P.S. most of these fragments don't actually compile because of missing schema and maybe the odd typo.  If there is interest I'll make a demo that
works soup to nuts.
