The Old Joel on Software Forum: Part 4 (of 5) - Usability improvement of an SQL tool

Usability improvement of an SQL tool

How useful would this feature be?. We often keep typing large number of queries such as

SELECT EMPNO,ENAME FROM EMPLOYEE WHERE ENAME='SOMENAME'

I plan to implement "Intellisense" to improve the usability and make it real fast. (Now, whether it can be defined as "usability" is probably controversial). Intellisense is automatically "sensing" what the suer is typing and giving suggestions, like Visual Basic.

Anyway, on typing "FROM", if a list pops up with all the table names and on typing "WHERE", another list pops up with all the column names, how useful will this feature be?

Also i plan to add a proprietory syntax. When the user types and executes the statement "USE EMPLOYEE "

Then he types, SELECT, the list will show all columns belonging to EMPLOYEE. The "USE" command is because when he user types "SELECT", i wont know which table he wants to select from.

In sum, the user types

USE EMPLOYEE; <<EXECUTE>>

SELECT <<At this point, i show him all EMPLOYEE columns>>

Would it be a killer feature?. The implementation, even for ANSI SQL is pretty challenging. Just wondered whether this would be a killer feature that would make people like my freeware program.

Thank you for your suggestion. Please, your suggestion is very important for me to decide how deep i want to go.

Karthik
Thursday, March 11, 2004

Also, if some guru could comment a little bit about the implementation challenges, it would be greeeat!

Karthik
Thursday, March 11, 2004

So far, you have the easy part how about when writing

select * from tablea
inner join ___

Now at the ___ I want to see a list of tables that have defined relationships so when I choose table b then the stuff in "s would come up.

select * from tablea
inner join tableb ""on tablea.id = tableb.fk""

And finally. save I have a query

select firstcol, secondcol, max(thirdcol)
from table
group by ___

___ should fill give me the option to fill so it turns out like
firstcol, secondcol,

select firstcol, secondcol, max(thirdcol)
from table
group by firstcol, secondcol

Impossible I know, but you asked...

eclectic_echidna
Thursday, March 11, 2004

careful about showing the entire column list for a table, some tables are pretty big.
If you wanted to be smart about it, show only the 5-10 most commonly used columns in the first popup, but then as they type the first 2-3 letters narrow it down to those columns starting with...etc

FullNameRequired
Thursday, March 11, 2004

What you really want is something like the live templates feature of IntelliJ IDEA. That would really kick arse.

Live syntax checking would also be great.

Rhys Keepence
Thursday, March 11, 2004

Check out a demo of mssqlxpress at http://www.mssqlxpress.com which has what you're trying to do. Maybe you can get some ideas from it.

Wade Winningham
Thursday, March 11, 2004

TOAD, which is a tool to administer Oracle databases, has such a feature.

I HATE IT every time it pops up.

But then again, I usually know what fields are in a table and how they are called - and am a fast typer.

René Nyffenegger
Friday, March 12, 2004

>I HATE IT every time it pops up.

Naw...Its sometimes annoyning in SQL if you know the datamodel really well, but pretty useful if you do use packages. The publicly callable procedures gets listed as well, with their parameters and types - very useful.

Patrik
Friday, March 12, 2004

The part the bugs me about TOAD's intellisense, is that its wrong sometimes.

For instance, the following is an example query I execute often (though more likely in a join context):
select p.first_name from people p where p.peop_id = 36367;

I only used the abbreviation p, because if I were to join it'd be handy to have a short name for the table.

TOAD remembers the abbreviation that you use often, and can suggest columns based on that. The problem comes up where I use the same abbreviation in another query, for a different table.

select p.price, p.name from potted_plants p where price > 10;

After typing the first "p." TOAD hangs for a few seconds while it thinks up responses, then gives me a list of people table columns.

*very* irritating, mostly because its slow.

Andrew Hurst
Friday, March 12, 2004