You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@netbeans.apache.org by GitBox <gi...@apache.org> on 2020/11/23 16:14:31 UTC

[GitHub] [netbeans] isarthur opened a new pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

isarthur opened a new pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558


   https://issues.apache.org/jira/browse/NETBEANS-188
   
   We can turn auto completion on or off in the Options window, looks like this:
   
   ![sql-completion](https://user-images.githubusercontent.com/64587659/99985958-6a9c2e80-2dd0-11eb-9a5d-f12d51fee5be.png)
   


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org
For additional commands, e-mail: notifications-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy edited a comment on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy edited a comment on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-739328633

@isarthur Thanks for this contribution! :) I've been testing/using it for work all afternoon - I found two things:

1. I got an Exception when deleting some query text in the SQL Editor (attached). I wasn't able to replicate it a second time. Stacktrace attached:
[sql_completion_stacktrace.txt](https://github.com/apache/netbeans/files/5647566/sql_completion_stacktrace.txt)

2. Changing the `Auto Popup Triggers` didn't seem to take effect for me unless Netbeans was restarted completely?

@matthiasblaesing From another point of view, this PR allows you to *stop* that behavior of getting database metadata for Code Completion completely (by un-checking `Auto Popup Completion Window` in the new SQL Options), which you can't do at the moment?

The status quo appears to be that once you type a `.` character after a schema in a FROM clause, Netbeans queries the database for the Tables and Views in that schema.

As soon as you type a `.` after a table name / alias in the SELECT, Netbeans queries the database for column metadata.

It doesn't matter if you are quick enough to not see the Code Completion dialog pop-up in NB currently, from monitoring the database session I could see those queries still happening. Once this metadata has been retrieved, it appears to be cached and future references to the same table don't cause the queries to be executed again.

The additional burden of this PR is that Schema, Tables, Views, Synonyms will now also be queried for when starting to type a schema / table name in a FROM clause / SELECT. Tested against one of our biggest Oracle clusters (~160 schema / 20k tables) over WAN / VPN, that initial query for schema/tables took on average ~1-5s and occasionally a worst case of 13-15s. Again, this appeared cached once retrieved and future queries didn't incur the same hit. Against more modest local databases, no delay was noticeable.

Of course Netbeans will also do that currently if one hits Ctrl-Space in the FROM clause of a query....

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org
For additional commands, e-mail: notifications-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] ebresie commented on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

ebresie commented on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-742907099


   Since this SQL auto completion has a dependency on database connection attributes , could when connecting to a given database, the tables be read in at that time to a "database connection cache" of some type, then have auto completion use that instead?  
   
   Assume it pulls from keywords and some placeholder for table/views/etc


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org
For additional commands, e-mail: notifications-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] matthiasblaesing closed pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

matthiasblaesing closed pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org
For additional commands, e-mail: notifications-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy edited a comment on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy edited a comment on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-743103282

@ebresie - DB metadata in NB is fetched lazily and cached as required, and this appears to be a very deliberate engineering decision in the existing code. What is unfortunate, is that it doesn't currently appear shared between all components that need it.

e.g. monitoring an Oracle session I can see that if I expand a Table node in the DB explorer, the DB gets queried for the Column metadata. If I then type `[tablename].` in the SQL editor, it gets queried again for Code Completion, rather than the Column metadata that has already been retrieved being re-used. And vice-versa.

There a list of SQL-99 keywords in `org.netbeans.modules.db.api.sql.SQLKeywords` which are used by the SQL Lexer for syntax colouring (and some other places in NB, like preventing JPA entities being created with reserved keyword names in JEE), but not SQL Code Completion. `org.netbeans.modules.db.sql.editor.completion.SQLCompletionQuery` has hard-coded Keywords for the completion contexts it understands (basic CREATE, SELECT, INSERT, DROP, DELETE scenarios etc.), and gets the database metadata for schema/tables/columns...

To me, these are bigger issues, and this PR is a quick win-win (if @isarthur could take a look at those couple of bits I mentioned) as:

1) It solves the JIRA ticket, making the existing (albeit limited) SQL Code Completion popup automatically
2) Enables users to disable to disable SQL Code Completion querying the DB for metadata completely, for those concerned about it (e.g. @matthiasblaesing ).

Apologies all for butting in on this PR. I'll take a step back now.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] matthiasblaesing commented on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

matthiasblaesing commented on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-743342632

To be clear: I won't merge this as is, if someone else does be prepared for issues resulting from this. If code completion is invoked automatically and causes the metadata to fetched, this sounds like a cheap operation, but it is not. If you want to experience the real problem, take a SAP system, a slow WAN link and expend the tables. That is a worst case scenario, but I also observed slow meta data loading on loaded systems.

In my mind, completion while typing should be quick and this means, network round trips are out of the question. You'd need to differentiate whether the user asked for completion explicitly or not. If not asked explicitly I would only complete from grammar and/or cached metadata, if explicitly invoked I would try to fetch metadata.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] matthiasblaesing commented on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

matthiasblaesing commented on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-734996482


   Sorry I don't think this is useful in its current state. I don't see the value of the configuration, at least the trigger character configuration is useless, as most users won't even know what the configuration does and IMHO this is the work the IDE should do. One crucial point is, that SQL completion in its current form could be very expensive - fetching huge sets of tables and/or colums over a WAN link is dog slow, so for an automatic popup only data, that is readily avaiable should be used.
   
   I added some thoughts to the jira issue, maybe that helps.


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org
For additional commands, e-mail: notifications-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy commented on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy commented on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-743103282

@ebresie - DB metadata in NB is fetched lazily and cached as required, and this appears to be a very deliberate engineering decision and the existing code. What is unfortunate, is that it doesn't currently appear shared between all components that need it.

To me, these are bigger issues, and this PR is a quick win-win (if @isarthur could take a look at those couple of bits I mentioned) as:

Apologies all for butting in on this PR. I'll take a step back now.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy edited a comment on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy edited a comment on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-739328633

@isarthur Thanks for this contribution! :) I've been testing/using it for work all afternoon - I found two things:

2. Changing the `Auto Popup Triggers` didn't seem to take effect for me unless Netbeans was restarted completely?

The status quo appears to be that once you type a `.` character after a schema in a FROM clause, Netbeans queries the database for the Tables and Views in that schema.

As soon as you type a `.` after a table name / alias in the SELECT, Netbeans queries the database for column metadata.

The additional burden of this PR is that Schema, Tables, Views, Synonyms will now also be queried for when starting to type a schema / table name in a FROM clause / SELECT. Tested against one of our biggest Oracle clusters (~160 schema / 20k tables), that initial query for schema/tables took on average ~1-5s and occasionally a worst case of 13-15s. Again, this appeared cached once retrieved and future queries didn't incur the same hit.

Of course Netbeans will also do that currently if one hits Ctrl-Space in the FROM clause of a query....

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] matthiasblaesing commented on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

matthiasblaesing commented on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-996985926


   This is now one year old and there is no progress - closing.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@netbeans.apache.org
For additional commands, e-mail: notifications-help@netbeans.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy commented on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy commented on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-739328633

@isarthur Thanks for this contribution! :) I've been testing/using it for work all afternoon - I found two things:

2. Changing the `Auto Popup Triggers` didn't seem to take effect for me unless Netbeans was restarted completely?

The status quo appears to be that once you type a `.` character after a schema in a FROM clause, Netbeans queries the database for the Tables and Views in that schema.

As soon as you type a `.` after a table name / alias in the SELECT, Netbeans queries the database for column metadata.

The additional burden of this PR is that Schema, Tables, Views, Synonyms will now also be queried for when starting to type a schema / table name in a FROM clause / SELECT. Tested against one of our biggest Oracle clusters (~160 schema / 20k tables), that initial query for schema/tables took on average ~1-5s and occasionally a worst case of 13-15s. Again, this appeared cached once retrieved and future queries didn't incur the same hit.

Of course Netbeans will also do that currently if one hits Ctrl-Enter in the FROM clause of a query....

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy edited a comment on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy edited a comment on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-743103282

@ebresie - DB metadata in NB is fetched lazily and cached as required, and this appears to be a very deliberate engineering decision in the existing code. Perhaps it would make sense to fetch more (e.g. table / view names metadata) at connection time, but what is unfortunate, is that the metadata doesn't currently appear shared between all components that need it.

There's a list of SQL-99 keywords in `org.netbeans.modules.db.api.sql.SQLKeywords` which are used by the SQL Lexer for syntax colouring (and some other places in NB, like preventing JPA entities being created with reserved keyword names in JEE), but not SQL Code Completion. `org.netbeans.modules.db.sql.editor.completion.SQLCompletionQuery` has hard-coded Keywords for the completion contexts it understands (basic CREATE, SELECT, INSERT, DROP, DELETE scenarios etc.), and gets the database metadata for schema/tables/columns...

To me, these are bigger issues, and this PR is a quick win-win (if @isarthur could take a look at those couple of bits I mentioned) as:

1) It solves the JIRA ticket, making the existing (albeit limited) SQL Code Completion popup automatically, for those who want it
2) Enables users to disable to disable SQL Code Completion querying the DB for metadata completely, for those concerned about it (e.g. @matthiasblaesing ).

Apologies all for butting in on this PR. I'll take a step back now.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy edited a comment on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy edited a comment on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-743103282

To me, these are bigger issues, and this PR is a quick win-win (if @isarthur could take a look at those couple of bits I mentioned) as:

Apologies all for butting in on this PR. I'll take a step back now.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists

[GitHub] [netbeans] peedeeboy edited a comment on pull request #2558: [NETBEANS-188] Add support for auto code completion in the SQL Editor

Posted by GitBox <gi...@apache.org>.

peedeeboy edited a comment on pull request #2558:
URL: https://github.com/apache/netbeans/pull/2558#issuecomment-743103282

To me, these are bigger issues, and this PR is a quick win-win (if @isarthur could take a look at those couple of bits I mentioned) as:

Apologies all for butting in on this PR. I'll take a step back now.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

For further information about the NetBeans mailing lists, visit:
https://cwiki.apache.org/confluence/display/NETBEANS/Mailing+lists