Code completion for Columns or Table Aliases

[cndab]

This issue template helps us understand your SQLTools issues better.

You don't need to stick to this template, but please try to guide us to reproduce the errors or understand your feature requests.

Before submitting an issue, please consider these things first:

• Are you running the latest version? If not, try to upgrade.
• Did you check the Setup Guide?
• Did you check the logs in console (Ctrl+` or select View → Show Console)?

Issue Type

Feature Request | Bug/Error | Question |Code completion for Table Aliases Other

Description

First of all Thank You!! for this awesome sublime plugin!! I am connecting to a database which has one schema and that schema has multiple tables and each table has multiple columns. so for ex a simple select statement would be like - Select * from schema.table T where t.columname = 'Something'; The issue I am seeing is that its doing the fuzzy search when I type "schema." and its trying to give me a list of tables (which is GREAT!), however, when I type "T." in the select statement or in the join or in where statement, it does not fuzzy search the columns in that table. Do i need to update some kind of setting for it to show me available columns for that view/table or is it supposed to work that way? I have added the "auto-complete-triggers" in my 'Preferences.sublime-settings - User" and I only have the CLI settings in my SQLtools.sublime-settings -USER" file. I searched the closed issues and saw that this feature was added per Issue #67. Any ideas whats going on? Thanks!

Version

• SQLTools Version: v0.9.11
• OS: Windows
• RDBMS: Oracle/SQLPlus
• Sublime Version: ST3

You can get this information by executing ST: About from Sublime Command Palette.

Steps to Reproduce (For bugfixes)

1. [First Step]
2. [Second Step]
3. [and so on...]

Expected behavior: [What you expected to happen]

Actual behavior: [What actually happened]

[tkopets]

If you manually show the completions with Ctrl+Space, do you see the popup with the list of relevant completions (list of columns in your case)? If yes, then most probably something is wrong with how you configured auto_complete_triggers.

If the list of completions is not shown, do you see any errors in the Sublime Text console?

One more issue with completions might be is that only the current paragraph of text (text between newlines) is parsed, so try to make sure you don't have empty newlines within your single SQL statement - that breaks the parsing of current SQL and that's why completions might not work.

[cndab]

Unfortunately I do not see list of relevant completions when I use Ctrl+Space. I see this statement in the console "'Token' object has no attribute 'tokens'". Does that help? I just have one line of code, no empty new lines.

[tkopets]

Please attach the complete console log. When doing so, use github code blocks for easier reading.

[cndab]

Oh sorry about that. Here you go. DPI scale: 1 startup, version: 3143 windows x64 channel: stable executable: /C/Program Files/Sublime Text 3/sublime_text.exe working dir: /C/Program Files/Sublime Text 3 packages path: /C/Users/u0158391/AppData/Roaming/Sublime Text 3/Packages state path: /C/Users/u0158391/AppData/Roaming/Sublime Text 3/Local zip path: /C/Program Files/Sublime Text 3/Packages zip path: /C/Users/u0158391/AppData/Roaming/Sublime Text 3/Installed Packages ignored_packages: ["Vintage"] pre session restore time: 0.358846 font face "Fira Code" could not be found, defaulting to "Consolas" font face "fira code" could not be found, defaulting to "Consolas" startup time: 0.405846 first paint time: 0.405846 reloading plugin Default.auto_indent_tag reloading plugin Default.block reloading plugin Default.comment reloading plugin Default.convert_syntax reloading plugin Default.copy_path reloading plugin Default.delete_word reloading plugin Default.detect_indentation reloading plugin Default.duplicate_line reloading plugin Default.echo reloading plugin Default.exec reloading plugin Default.fold reloading plugin Default.font reloading plugin Default.goto_line reloading plugin Default.history_list reloading plugin Default.indentation reloading plugin Default.install_package_control reloading plugin Default.kill_ring reloading plugin Default.mark reloading plugin Default.new_templates reloading plugin Default.open_context_url reloading plugin Default.open_in_browser reloading plugin Default.pane reloading plugin Default.paragraph reloading plugin Default.paste_from_history reloading plugin Default.profile reloading plugin Default.quick_panel reloading plugin Default.run_syntax_tests reloading plugin Default.save_on_focus_lost reloading plugin Default.scroll reloading plugin Default.set_unsaved_view_name reloading plugin Default.settings reloading plugin Default.show_scope_name reloading plugin Default.side_bar reloading plugin Default.sort reloading plugin Default.swap_line reloading plugin Default.switch_file reloading plugin Default.symbol reloading plugin Default.transform reloading plugin Default.transpose reloading plugin Default.trim_trailing_white_space reloading plugin Default.ui reloading plugin CSS.css_completions reloading plugin Diff.diff reloading plugin HTML.encode_html_entities reloading plugin HTML.html_completions reloading plugin 0_package_control_loader.00-package_control reloading plugin 0_package_control_loader.01-pygments reloading plugin 0_package_control_loader.50-dateutil reloading plugin 0_package_control_loader.50-markupsafe reloading plugin 0_package_control_loader.50-python-markdown reloading plugin 0_package_control_loader.51-python-jinja2 reloading plugin 0_package_control_loader.55-mdpopups reloading plugin A File Icon.A File Icon reloading plugin ColorSchemeEditor.ColorSchemeEditor-ST2 reloading plugin Colorsublime.colorsublime-plugin reloading plugin Material Theme.Icons reloading plugin Material Theme.MT Traceback (most recent call last): File "C:\Program Files\Sublime Text 3\sublime_plugin.py", line 109, in reload_plugin m = importlib.import_module(modulename) File "./python3.3/importlib/__init__.py", line 90, in import_module File "<frozen importlib._bootstrap>", line 1584, in _gcd_import File "<frozen importlib._bootstrap>", line 1565, in _find_and_load File "<frozen importlib._bootstrap>", line 1532, in _find_and_load_unlocked File "C:\Program Files\Sublime Text 3\sublime_plugin.py", line 915, in load_module exec(compile(source, source_path, 'exec'), mod.__dict__) File "MT in C:\Users\u0158391\AppData\Roaming\Sublime Text 3\Installed Packages\Material Theme.sublime-package", line 1, in <module> File "C:\Program Files\Sublime Text 3\sublime_plugin.py", line 915, in load_module exec(compile(source, source_path, 'exec'), mod.__dict__) File "utils in C:\Users\u0158391\AppData\Roaming\Sublime Text 3\Installed Packages\Material Theme.sublime-package", line 3, in <module> File "C:\Program Files\Sublime Text 3\sublime_plugin.py", line 915, in load_module exec(compile(source, source_path, 'exec'), mod.__dict__) File "utils.config in C:\Users\u0158391\AppData\Roaming\Sublime Text 3\Installed Packages\Material Theme.sublime-package", line 8, in <module> File "C:\Users\u0158391\AppData\Roaming\SUBLIM~1\Packages\mdpopups\st3\mdpopups\__init__.py", line 27, in <module> from . import frontmatter File "C:\Users\u0158391\AppData\Roaming\SUBLIM~1\Packages\mdpopups\st3\mdpopups\frontmatter.py", line 2, in <module> import yaml ImportError: No module named 'yaml' reloading plugin Package Control.1_reloader reloading plugin Package Control.2_bootstrap reloading plugin Package Control.Package Control reloading plugin PackageResourceViewer.package_resource_viewer reloading plugin PackageResourceViewer.package_resources reloading plugin SideBarEnhancements.SideBar reloading plugin SideBarEnhancements.SideBarAPI reloading plugin SideBarEnhancements.SideBarDefaultDisable reloading plugin SqlBeautifier.sqlbeautifier reloading plugin Themr.themr reloading plugin uroboroSQL Formatter.urobosql_formatter reloading plugin PlainTasks.APlainTasksCommon reloading plugin PlainTasks.PlainTasks reloading plugin PlainTasks.PlainTasksDates reloading plugin PlainTasks.PlainTasksToHTML reloading plugin PlainTasks.plist_parser reloading plugin SQLTools.SQLTools plugins loaded Package Control: Skipping automatic upgrade, last run at 2018-02-08 09:04:16, next run at 2018-02-08 10:04:16 or after 'Token' object has no attribute 'tokens' 'Token' object has no attribute 'tokens' 'Token' object has no attribute 'tokens' 'Token' object has no attribute 'tokens' 'Token' object has no attribute 'tokens' 'Token' object has no attribute 'tokens' 'Token' object has no attribute 'tokens'

[tkopets]

That's one giant line of text!!

[cndab]

May be this will help. I have pasted the console output in the attached file. Before I thought you wanted me to paste it in the "insert code" format which I did. thanks for your help. ConsoleLog.txt

[cndab]

Another reason why the code completion might not be working for me is because I have to refer the schema AND the table like so - Schema.Table. So the '.' logic in my case only works for '.table' which is how it should work currently? Because in the issue # 67 and other examples I have seen, people are DIRECTLY referring to the table and the first time the period comes in to play is AFTER the alias (Alias.column) which is not the case in my queries. May be I am completely off on this. Just a thought.

[tkopets]

SQL completions should suggest both table names and column names when appropriate. So, for example (cursor is marked with |):

SELECT * FROM HR.| -- If HR is a schema, it's tables will be shown. if HR is a table - it's columns will be shown. SELECT HR.DEPARTMENTS.| -- Columns of table DEPARTMENTS will be shown. SELECT * FROM HR.DEPARTMENTS dpt WHERE dpt.| -- Columns of table DEPARTMENTS (via dpt alias) will be shown.

The message in your Sublime Text console 'Token' object has no attribute 'tokens' suggests that SQLTools (via sqlparse library) was not able to parse your SQL for some reason.

Maybe there is something specific about your exact SQL statement that breaks it. Can you post your SQL?

[cndab]

Here is a simple query where "AP_Claim" is the table which DOES come up in the code completion list as soon as I write "Clarity_Report." . However, the alias "AP." does not show the columns.

SELECT AP.CLAIM_ID,
             AP.TOT_NET_PAYABLE,
             AP.TOT_BILLED_AMT
FROM CLARITY_REPORT.AP_CLAIM AP

WHERE AP.CLAIM_ID = 10641971; 

[tkopets]

Where is your cursor located when you are expecting the completions? If it is somewhere in the WHERE clause it wouldn't work as your statement contains the newline (see my comment above https://github.com/mtxr/SQLTools/issues/190#issuecomment-364175147). And SQLTools only parses the text within a newlines.

Also, please try to execute this SQL (returns list of all columns) and tell me if that executes OK on your DB. I don't need the query results, just the fact that it returns some data.

select case when upper(table_name) = table_name then table_name else chr(34) || table_name || chr(34) end || '.' || case when upper(column_name) = column_name then column_name else chr(34) || column_name || chr(34) end as obj from (select c.table_name, c.column_name, t.owner from all_tab_columns c inner join all_tables t on c.owner = t.owner and c.table_name = t.table_name union all select c.table_name, c.column_name, t.owner from all_tab_columns c inner join all_views t on c.owner = t.owner and c.table_name = t.view_name) o where owner not in ('ANONYMOUS','APPQOSSYS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'XS$NULL','PUBLIC');

[cndab]

The cursor is located after the '.'. Yup, I remember you mentioning about the new line. I ran your query and I got this "Command execution time exceeded 'thread_timeout'. Process killed!"