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.
请先登录再写评论。
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
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?
These issues will be addressed by the next EAP.
Feedback is welcome.
Also mine, especially the command history is IMHO a must-have.
Tom
Check out the latest EAP
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
History is invoked by CtrlUp / CtrlDown.
It is advertised in the status bar on each execution.
Tom wrote:
Thanks, I did not notice it.
What about the other RFE?
Tom
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.
>> * 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.
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.
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, I assume you're referring to the EAP after #8858, correct?
Taras Tielkes wrote:
You are right.
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
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
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! :)
Taras,
Taras Tielkes wrote:
Current shortcuts are inspired by instant messenger tools indeed.
Any suggestions?
This can be merged with "execute current" functionality.
Fixed
Fixed
TBD
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
+1
I also like the CTRL+ENTER way of executing queries, but I haven't checked if it's customizable?
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
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
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?
Hi Erik,
1) I get full resolving for table/column names. Can you provide a bit more information?
-tt
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
Hello opticyclic,
Since a database is a tree node, it's a common action for double click to
expand the tree.
It copies DDL to the clipboard.
Alexander.
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..
I'm using Postgres 8.2, and 'billing' is the name of the schema....
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.
Hello Alex,
Looks like http://www.jetbrains.net/jira/browse/IDEADEV-32325 , it's now
fixed.
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".
User can move it to the right.
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.
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