SQL console: room for improvement

The new SQL console in IDEA is a nice addition. However, the current state is quite raw and unpolished.
While having familiar completion for columns etc is very nice, I'm still using external tools at the moment for any SQL querying.

Let me try to compile a list of "blocking" problems:

1) Console in implemented as a pane tab

Whatever type of text I edit, I expect to do it in the editor pane of the IDEA.
Having it in a small tab at the bottom sucks, since:
a) I'm limited to this really small region of my screen
b) When looking at query results, the effective "editing area" is reduced even further

I'd expect to edit SQL in a regular editor tab. This is where I perform all of my other editing after all.
The results of a query execution, however, would fit perfectly well in a bottom-docked results pane.

Of course there's the question of "but is it really an editor file?".
I don't care - make it some kind of virtual file, perhaps showing the name of the datasource in the editor tab?

2) Results need a table

Please present results in a simple table component. 'Nuff said.

3) Completion for tables/columns

Having completion for all the functions supported by my current SQL dialect is nice.
However, most of them (>90%) I will invoke very rarely.

At the moment the completion suggestions for functions easily dwarf/pollute the completion suggestions for table/column names.
Here's an idea: provide SQL function completion only after invoking code completion the second time.

Similar to the new extended smart completion in Diana, you could initially show a message like "Invoke code completion again to receive SQL function suggestions".

Of course I have tons of other requests and ideas, but I think above 3 are simply critical for an acceptable user experience.

37 comments
Comment actions Permalink

I'm quite new in the area of SQL, but wanted to add my issues here (using 8M1):

- I miss a command history like I have with the simple MySQL command line
client (Cursor Up/Down)

- I don't want to clear the executed command myself to type another one

- yesterday the automatic quote addition made my more work than necessary,
because I wanted to surround a date like 2008-07-15 with quotes, but IDEA
automatically inserted two quotes instead, so I had to remove the second one

- it is not easy to use select statement output to be used as insert
statement input - too much manual correction is necessary (I had to copy
some rows from one table to another with a slightly different column definition)

Tom

0
Comment actions Permalink

Tabular output is essential. I do like having the types listed in the current output though (perhaps they could be listed in a column heading on the table).

Ability to highlight rows, highlight PKs and FKs would be nice.

Not having the SQL plugin run as a Java application would be nice - it looks weird now - can this be moved to its own little tab on the edge of the screen?

0
Comment actions Permalink

These issues will be addressed by the next EAP.
Feedback is welcome.

0
Comment actions Permalink

Also mine, especially the command history is IMHO a must-have.

Tom

0
Comment actions Permalink

Check out the latest EAP

Tom wrote:

Also mine, especially the command history is IMHO a must-have.

Tom

0
Comment actions Permalink

Nice work with the results tab(s). Although the title(s) should better read
"Result" (for a single result) or "Result 1", "Result 2", ... (with space).

  • I recommend to select the full input statement after having executed it,

so I just can start typing a new one, but if I prefer, I also can edit it again.

  • I can't find a (working) history.


Tom

0
Comment actions Permalink

History is invoked by CtrlUp / CtrlDown.
It is advertised in the status bar on each execution.


Tom wrote:

Nice work with the results tab(s). Although the title(s) should better
read "Result" (for a single result) or "Result 1", "Result 2", ... (with
space).

  • I recommend to select the full input statement after having executed

it, so I just can start typing a new one, but if I prefer, I also can
edit it again.

  • I can't find a (working) history.


Tom

0
Comment actions Permalink

History is invoked by CtrlUp / CtrlDown.
It is advertised in the status bar on each execution.


Thanks, I did not notice it.

What about the other RFE?

  • I recommend to select the full input statement after having executed

it, so I just can start typing a new one, but if I prefer, I also can
edit it again.


Tom

0
Comment actions Permalink

Will be there in the next EAP:
- Clear on execute replaced with selection.
- Result tabs renamed & finally can be closed without console restart.


Tom wrote:
>> History is invoked by CtrlUp / CtrlDown.
>> It is advertised in the status bar on each execution.


Thanks, I did not notice it.

What about the other RFE?

>> * I recommend to select the full input statement after having executed
>> it, so I just can start typing a new one, but if I prefer, I also can
>> edit it again.


Tom

0
Comment actions Permalink

Gregory,

Do you still plan to have the query editor inside the bottom pane?

At the moment I'm starting to use the console, and would prefer to always have the query editor in an editor tab - and only see query results in the bottom pane. Are you considering making this the default UI? If not, will it be possible to configure IDEA such the I can get the UI I wish for? :)

By the way: good work in recent build - things are really improving.

0
Comment actions Permalink

Taras,

From the next EAP the editor state will be preserved in addition to tab
layout. And you will get the desired behavior, I think.


Taras Tielkes wrote:

Gregory,

Do you still plan to have the query editor inside the bottom pane?

At the moment I'm starting to use the console, and would prefer to
always have the query editor in an editor tab - and only see query
results in the bottom pane. Are you considering making this the
default UI? If not, will it be possible to configure IDEA such the I
can get the UI I wish for? :)

By the way: good work in recent build - things are really improving.

0
Comment actions Permalink

Gregory, I assume you're referring to the EAP after #8858, correct?

0
Comment actions Permalink

Taras Tielkes wrote:

Gregory, I assume you're referring to the EAP after #8858, correct?


You are right.

0
Comment actions Permalink

Hi Gregory,

I don't see any change in the new EAP.

My request is quite simple - I want IDEA to behave the same way that 95% of SQL tools out there behave:

  • use the main editor area to type/edit SQL

  • use a bottom-docked result pane to view query results


My main gripes with the build I'm running now (#8909) are:

  • While I can use a regular editor tab to edit the query, I cannot get rid of the second editor tool window docked in the bottom tab - so now I have two (synched) editors where I want only one

  • The SQL console hijacks the existing "Run..." functionality - I cannot easily run some unit test without changing the run config. While the SQL console support might be using the "Run..." feature under the covers in its implementation, it should be properly hidden and not show up in the IDEA UI

  • The indentation behavior (IDEA-19897) drives me totally nuts - perhaps this is something I can configure myself?


Apart from those, I should say that the SQL support is very welcome - I also see a lot of potential for unique features post Diana release.

Edited by: Taras Tielkes on Oct 14, 2008 8:30 PM

0
Comment actions Permalink

Hi Gregory,

Here's some more feedback on what's most desired:

  • Usage of Ctrl-Enter to execute something is quite confusing and non-intuitive. I would expect Ctrl-Enter (and Ctrl-Shift-Enter) to work the way they do in other editors, that is: inserting (smart) linebreaks.

  • Very often (>95%) I have different queries in the SQL console, and I want to only execute a specific one. Most SQL tools out there have an option to execute only the current selection. Ctrl-W will give me a proper selection quickly enough, how do I tell IDEA to execute the current selection only?

  • Column sorting: IDEA should examine the type of a resultset column to determine how to sort: IDEA-20245 (I assume this metadata is available through JDBC?)

  • The initial width of the resultset columns should be such that all data is shown: IDEA-20253

  • Very annoying indentation: IDEA-19897 / IDEADEV-31337


Edited by: Taras Tielkes on Oct 27, 2008 12:52 PM

0
Comment actions Permalink

I haven't used the new SQL functionality in IDEA.
However, I would like to point out that the excellent ;) SQL tool SQuirrel SQL uses Ctrl-Enter to execute queries.

If this is an issue for some people, I would think it could be fixed by having the keystroke configurable.
I know Sybase SQL Advantage uses Ctrl-E for this operation.

I like Ctrl-Enter as I use 2 hands to do it and execute my SQL with a flourish! :)

0
Comment actions Permalink

Taras,

Taras Tielkes wrote:

Hi Gregory,

Here's some more feedback on what's most desired:

  • Usage of Ctrl-Enter to execute something is quite confusing and non-intuitive. I would expect Ctrl-Enter (and Ctrl-Shift-Enter) to work the way they do in other editors, that is: inserting (smart) linebreaks.

Current shortcuts are inspired by instant messenger tools indeed.
Any suggestions?

  • Very often (>95%) I have different queries in the SQL console, and I want to only execute a specific one. Most SQL tools out there have an option to execute only the current selection. Ctrl-W will give me a proper selection quickly enough, how do I tell IDEA to execute the current selection only?

This can be merged with "execute current" functionality.

  • Column sorting: IDEA should examine the type of a resultset column to determine how to sort: IDEA-20245 (I assume this metadata is available through JDBC?)

Fixed

  • The initial width of the resultset columns show be such that all data is shown: IDEA-20253

Fixed

  • Annoying indentation: IDEA-19897 / IDEADEV-31337

TBD

0
Comment actions Permalink

Yes, I am also used to using Ctrl-Enter to run sql statemtent, so I don't think this is confusing and non-intuitive; I frankly find it intuitive...
And comparing this to an editor would be wrong..


BTJ

0
Comment actions Permalink

+1

I also like the CTRL+ENTER way of executing queries, but I haven't checked if it's customizable?

0
Comment actions Permalink

More feedback:

  • Keyboard shortcuts: obviously preferences differ. Please make it configurable.

  • I want to be able to execute the query contained by the current selection. A common use case is to have a sequence of queries in the console and execute them one by one. Or execute one using the results of another. Or test a subquery of an outer query by selecting only that and executing it. By the way, how does "execute current" exactly work?

  • Executing a query selects the whole editor content. What for??! If I want that I'll use Ctrl-A, thank you :)

  • Show the size of the resultset somewhere, if possible.

  • Can I disable Ctrl-Up/Ctrl-Down somehow? After having a SQL console with a few hundred lines of separate queries I managed to lose everything but one by invoking one of these actions. Besides, if you allow execution of query contained by current selection, the need for this becomes unclear.

  • Why does the console hijack the "Run" button? I might want to start an application or unit test while querying the underlying database.


Edited by: Taras Tielkes on Oct 31, 2008 1:44 AM

0
Comment actions Permalink

Some more feedback based on Diana #8987

  • You should be able to double-click the datasource in the Database pane to open the SQL Editor

  • You should be able to right-click the datasource in the Database pane to rename it

  • "Run JDBC Console" with a black command line icon implies a console/command line view - initially I didn't click this as I was looking for description like "Editor" or "Client" or something to imply a GUI

  • The icon for "Open In Editor" in the JDBC Console is the same as the icon for "Generate and Copy DDL" in the Database pane, which is confusing

  • "Generate and Copy DDL" in the Database pane doesn't appear to do anything

  • (As mentioned previously) "Open In Editor" should hide the small Input tab on the bottom

  • You should be able to Double-Click the Input tab to maximise it to a full editor

  • You should be able to drag the tabs around, onto different rows etc, in the JDBC Console

  • "Show query results in new tab" should be enabled by default

  • You should be able to refresh the tables in the background so that you can keep typing whilst the Table Structure is loading

  • Drop down box containing history so that you can see what is there and select it

  • Bookmarks for common Queries

  • Context menu for the results containing

- Select All
- Copy
- Copy with Headers

  • There doesn't appear to be an option to open/import an existing SQL file or to save the editor contents to file

  • If you copy a Datasource config and modify the database url, the old table structure is still listed


Phew!
Now you have a week to implement it all and create us a new build ;D

0
Comment actions Permalink

I haven't checked the whole thread yet, but I would also like:
1) completion for table/column names
2) a way to display the contents of the table. I expected a double click on the table,but it expands the table and shows the columns, or otherwise a right-click and 'show table contents'.... but so far I haven't been able to find an option to do so. Am I missing something?

PS: I very much like pgAdmin (from postgres), maybe you can borrow some nice ideas from there?

0
Comment actions Permalink

Hi Erik,

1) I get full resolving for table/column names. Can you provide a bit more information?

-tt

0
Comment actions Permalink

I was typing something like:

select * from billing.cre<ctrl+space>, and nothing happens. You can see it in the screenshot. I was expecting creditnote, or something...

I also tried without the 'billing' part, but also no success.



Attachment(s):
Picture 2.png
0
Comment actions Permalink

Hello opticyclic,

  • You should be able to double-click the datasource in the Database

pane to open the SQL Editor


Since a database is a tree node, it's a common action for double click to
expand the tree.

  • "Generate and Copy DDL" in the Database pane doesn't appear to do

anything


It copies DDL to the clipboard.

Alexander.


0
Comment actions Permalink

Erik, what database are you using, and what does "billing" refer to.

Of couse, you might want to create a JIRA ticket with all relevant details..

0
Comment actions Permalink

I'm using Postgres 8.2, and 'billing' is the name of the schema....

0
Comment actions Permalink

I just tried this with 8995 8.x RC1.
I added a database connection for Sybase ASE database I have which has 1000s of tables.
When I click Refresh Tables it looks to be processing a table per second which is very very slow. After about 2 minutes, it was still 1% done, so I killed it.

I assume you guys have used DBVisualizer? That is the tool I use and it is a really nice application; They have free and pay versions but the free version does everything I could possibly want to do. It can populate the tree table list of 1000s of tables in 2 seconds! It seems IntelliJ is taking 2 seconds just to load one table. That is a showstopper right there. But even if that major performance problem wasn't there, the feature set in DBVisualizer is much more mature and complete and polished, etc.

I'm trying to see why I would ever want to use this database/sql feature inside IntelliJ instead of just using DBVisualizer, but I'm struggling to see a use case for it.

I do like that my .sql files are syntax-highlighed now.
One small comment is I think Database tab makes more sense on the left side rather than the right side. And somehow, the SQL editor panel and result panel should be able to take over the editor panel space, at least optionally. Otherwise, there is not enough vertical space to do anything.

0
Comment actions Permalink

Hello Alex,

I just tried this with 8995 8.x RC1.

I added a database connection for Sybase ASE database I have which has
1000s of tables.

When I click Refresh Tables it looks to be processing a table per
second which is very very slow. After about 2 minutes, it was still 1%
done, so I killed it.


Looks like http://www.jetbrains.net/jira/browse/IDEADEV-32325 , it's now
fixed.

I do like that my .sql files are syntax-highlighed now.


Just notice that Sybase dialect (if any) is not yet fully supported. If syntax
error messages annoy you, you can switch dialect to "Generic SQL".

One small comment is I think Database tab makes more sense on the left
side rather than the right side.


User can move it to the right.

And somehow, the SQL editor panel and
result panel should be able to take over the editor panel space, at
least optionally. Otherwise, there is not enough vertical space to do
anything.


The "Input" SQL editor may be opened in regular editor panel: check "Open
in editor" button in JDBC console toolbar. After that you can decrease the
size of Input panel in the console to zero.

Alexander.


0
Comment actions Permalink

Hello Alex,

I just tried this with 8995 8.x RC1.

I added a database connection for Sybase ASE database I have which has
1000s of tables.

When I click Refresh Tables it looks to be processing a table per
second which is very very slow. After about 2 minutes, it was still 1%
done, so I killed it.


Looks like http://www.jetbrains.net/jira/browse/IDEADEV-32325 , it's now
fixed.


Hi Alexander,

I don't think Jira is the same issue. That issue is with seeing the results table for a query like "select * from xyz". My problem is that it is taking forever to do "Refresh Tables" to populate the tree with the tables. I will try a later build when I get a chance.

-Alex

0

Please sign in to leave a comment.