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.
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 typed | You're offered |
|---|---|
-- on its own line | A 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.
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.