Hacker News Reader

Comment by breckinloggins | Hacker News Reader

One thing that would be great here would be autocompletion. I often forget exactly what the syntax of "CREATE TABLE" is or which options I can use. The same goes for infrequently used SQL functions (now is that date_diff or datediff?).

You could combine autocompletion with a doc panel that automatically updates to show the "quick reference" for the keyword you're currently typing.

View on HN

Slightly off-topic, but one thing that always impressed me about Microsoft's LINQ as an alternative to SQL queries is that they considered autocompletion up-front in the language syntax.

For SQL, it's a big challenge - you may type in 'SELECT', but your IDE still has no idea what relation(s) you're going to access, and so it can't reasonably autocomplete the possible fields you might want to use.

It's an interesting example of a language that was arguably designed for human readability in the small ('give me this from that cross-referenced with this other thing'), but in some sense became a weakness of sorts later on.

One way to approach it would be to have a grab bag of templates. You drag a template like "SELECT ... FROM ... WHERE ..." out into the editor and then tab through it filling in the blanks.

I've always thought that SQL's notion of readability was somewhere between superficial and disingenuous - you can read the query and it sounds like it ought to make sense, but there's so many occasions where you won't actually be able to understand why it isn't working unless you know how to translate in your head from the order in which the clauses are written, to the order in which they're carried out.

True - but so much of the value of autocomplete comes from just being able to try things out:

'from customers, orders select ...'

Would allow showing a whole bunch of columns, and you could quickly determine whether those tables had the information you're after, or not.

It might seem strange/lazy that the developer can explore the environment (schema) this way without 'knowing' what's out there, but in practice that's exactly what we all do with software development, web search, etc - we try things out and see what comes back, rather than knowing in the first place. Data development has a way to go, but I think things will improve..

Actually, one alternative approach would be: use a search index to autocomplete after even a basic 'SELECT' statement (no other context), but then fill the FROM clause based on the selection.

i.e. - type:

'select city'

prompts with:

'city (table airports)'

'city (table populations)'

'city (table companies)'

And then, upon selection, you have a statement: 'select city from <selected-table>'.

There's a lot more here though. Worth pondering for a while I think.

Would you elaborate on the weaknesses of LINQ you mentioned?

To clarify: it was the SQL, not LINQ, language syntax (field selection prior to relation selection) that I was referring to as a potential weakness (but only once considered in the light of developer tools).

Absolutely. The "get something from somewhere" syntax that SQL employs is completely at odds with it being declarative. LINQ simply does a better job of capturing the abstractions of relational algebra cleanly.

Not that it really matters to someone who is already familiar with SQL, however.

SQL has the weakness, not LINQ.

In LINQ, you do "FROM x SELECT y". So the editor can help you autocomplete the y, since it knows the context.

In SQL, you do "SELECT y FROM x". Thus the editor cannot help you with y.

Excellent point. I guess when SQL was designed not many autocompleting IDEs were around though...

I don't have that feature exactly, but I can offer this - if you have a table of regularly-formatted text data, you can paste it into the "Text-to-DDL" feature to have it automatically generate your CREATE TABLE and INSERT INTO statements.

Awesome, thanks!

Reply on news.ycombinator.com