Thursday, November 20, 2008

Feedback requested: SQL completion in code editors - is it valuable?

I am hoping I can get some feedback from you all.  I have been excited about the support for completion in the NetBeans PHP editor (and other editors to follow).  The response to my quick little blog about this has been pretty positive.

To refresh, here's an example of what it would look like:


But I have also gotten some feedback from more than one source that this may not actually be as valuable as it may first appear.  The reason is that, and this makes sense, the actual work flow when building SQL for your application is as follows:
  • Open SQL tool and write your query
  • Execute, evaluate results, modify, repeat
  • Copy and paste final SQL into your editor
With this flow in mind, it doesn't seem that important to provide completion and other SQL editing features embedded in your application editor, because you need to test to validate that the SQL is correct.

What are your thoughts?  Can you explain to me why SQL editing features in the code editor is valuable, if generally SQL is composed in a SQL tool/editor?

One thing we've talked about is enabling testing from within the code editor.  But it's not clear what this would look like.  We would need to be able to build up an executable SQL statement from SQL that contains substitution variables and may even be a concatenation of multiple strings.  I suppose it's possible, but it seems complicated and error-prone.

I suppose one simplistic approach is the following flow:
  • In your editor, write a full SQL statement as a string
  • right-click and choose "Test SQL" (or run a hot key) to let you test the SQL string right there in the application editor 
  • refine, retest
  • modify the SQL to use substitution variables
At least with this approach you wouldn't have to cut-and-paste, but I'm not sure it buys you much.

So, what are your thoughts?  Your feedback much appreciated.


izenmania said...

It's certainly not a transcendently amazing feature that will revolutionize software development as we know it, but I do think that it's a worthwhile pursuit.

For one thing, you won't always see someone developing a query in a database environment before putting it into code. There are plenty of simple queries that can be reasonably written directly into code. Oftentimes when a basic query like this fails (at least for me), it's because I forgot the name of a field (substituting name for title, or the like). A problem solved with in-code query completion.

Also, as you mention, once expanded the feature can become much more useful. I can see a definite benefit to being able to right click a query and execute it inline. This could be used not just in initial query development, but in perusing someone else's code.

Anonymous said...


I'd love to see this in Netbeans, bit I think this may be a bit more problematic than you think:

SQL completion can be slooooow against large/remote databases (if you're going to support table/column/etc completion)

SQL varies between databases, unlike Java/PHP/etc

Stored procs/functions/etc such as PLSQL and T-SQL are completely different between databases, and many of us use these more than we do straight SQL

So, I suspect that, as has already been said, this is best left in the domain of the SQL tools.


David Van Couvering said...

Thanks, Anonyous, for your warnings about issues. I'm quite aware of all of these. I am not planning to attack them all at once, but I do have ideas on how to approach them.

For performance, we are going to have ways for users to select which tables they are interested in in a large schema, and also once the table data is loaded, it's cached in memory until the user explicitly refreshes.

Regarding different SQLs, it's true. We'll support one vendor at a time, plus support a "generic" SQL that won't be perfect but will be better than nothing. The vendor's own SQL processor will always be the defacto ruler on what is correct, thus the need for testing your SQL.

Stored procs - we are not going to deal with those right away, and again, we'll do them one vendor at a time.

Incremental improvement is the overall goal, not some massive attempt to cover everything at the get go. Something is better than nothing, and then something more, and then something more...

Neil's netbeans stuff said...

David, yes this is a must IMHO. I use it all the time, I have one of those memories where I can tell you what happened 10 years ago and quote the exact time, ask me about 5 minutes ago I have no idea, so this is so valuable.

A general comment on the new database explorer, it is much improved, one thing I find annoying is the fact that you can not sort the column names for each table.

Anonymous said...

Hello again,

(same anonymous as above)

Sorry, I should have worded my post a little more thoughtfully...

Anyway, it's good to know that there are plans ahead to deal with these things. The one about dealing with large schemas is good - I wish the tool we use had that as we typically only use a small fraction of the schema 95% of the time.

I'm not sure if this is moving off subject a little, but one feature that Oracle's SQLDevloper tool has is the ability for it's formatter to convert between SQL embedded in code and "plain" SQL. Ie, you copy+paste Java code with SQL in strings, format it, and hey presto, you have the actual SQL query (minus any variables, etc). After playing around with the query, you can then convert the plain SQL back into embedded SQL in a variety of languages. If parameters could be handled nicely both ways, this, IMHO, would be a very useful function.

David Van Couvering said...

Hello, Anonymous.

Yes, you are not the first one who has requested that feature. I'm chewing on how to do this. Like you said, if we can do it right, it's a very cool feature. I'm trying to figure out how to do it right. Probably not for the next release, but I do want to do this.