Autocomplete for SQLly directives

The SQLly editor now autocompletes -- sqlly directives: every directive, setting and value, plus your real column names quoted the way each one needs.

By

SQLly directives are SQL comments that pivot, chart and format your results. They're great once you've written one, and writing one meant remembering a syntax that's easy to forget. As of v1.37, the editor autocompletes them: every directive, every setting, every value, and your actual column names.

Too much syntax to remember

Directives keep a result's presentation in the .sql file: -- sqlly pivot: row:Region; column:Year; value:Sales:sum, and the query opens as a cross-tab for anyone who runs it. There are a lot of them now: grid layout, formatting, conditional colors, computed columns, charts, maps, parameters, layouts.

Is it value:Sales:sum or value=Sales sum? Does grid: use = while pivot: uses :? (Yes. That one's on me.) Writing a directive from scratch usually meant a trip to the directives docs. The lock button that writes them for you helps, but sometimes you just want to type one line.

Now the editor walks you through it

The suggestion list follows the cursor through the directive and offers whatever can come next:

You've typedYou're offered
-- on its own lineA ready-made starter line for each kind of directive
-- sqlly Every directive: view:, grid:, pivot:, chart:, format:…
-- sqlly grid: That directive's settings, minus the ones already on the line
-- sqlly grid: sort=Your result's columns
-- sqlly grid: sort=Total asc or desc

Accepting a setting opens its values straight away, so a whole directive can be built by hitting Tab a few times. The values are the real ones: chart kinds, pivot aggregates, filter operators, map modes, number and date formats, time zones, colors. Ctrl+Space brings the list back anywhere inside a directive.

The SQLly editor with a chart directive typed up to label:, and the suggestion list showing the column names from the query's last run
After one run, label: offers the columns the query actually returned.

Your column names, spelled the way each directive wants

Wherever a directive takes a column, the list shows the columns the tab's last run returned. That's the useful part, and also the fiddly part, because the directive families don't all spell names the same way. A computed-column formula writes !"Unit Price", the grid directive has its own quoting rules, and some families can't spell certain names at all. Each one gets names written its way, and a name a family can't spell isn't offered, rather than offered broken.

Formulas get the same treatment: in a column: directive the list completes column references, Excel worksheet functions and @ variables. On a param: line it offers the query's placeholders you haven't declared yet, then every parameter type.

It can't suggest something that doesn't work

SQLly already underlines a directive that's misspelled or has a bad value. Autocomplete that suggests something that then gets underlined would be worse than none. So the tests check every key and value the menu can offer against the real directive parsers. If a suggestion doesn't parse, a test fails. There are a little over a thousand lines of those tests, which tells you how many settings there are.

One more small thing: view: and the canvas size only work on a query's first line, so the starters only offer them there.

It's in v1.37. Type -- on an empty line and see what shows up. The full rundown is in the directives docs.