Merge pull request #342 from dbcli/j-bennet/completion-in-named-query

Autocompletion in named queries.

5 files changed, 72 insertions, 6 deletions

diff --git a/pgcli/packages/sqlcompletion.py b/pgcli/packages/sqlcompletion.py
index f4c58eb6..76ec0b1a 100644
--- a/pgcli/packages/sqlcompletion.py
+++ b/pgcli/packages/sqlcompletion.py
@@ -1,5 +1,6 @@
 from __future__ import print_function
 import sys
+import re
 import sqlparse
 from sqlparse.sql import Comparison, Identifier, Where
 from .parseutils import last_word, extract_tables, find_prev_keyword
@@ -27,6 +28,20 @@ def suggest_type(full_text, text_before_cursor):
 
     identifier = None
 
+    def strip_named_query(txt):
+        """
+        This will strip "save named query" command in the beginning of the line:
+        '\ns zzz SELECT * FROM abc'   -> 'SELECT * FROM abc'
+        '  \ns zzz SELECT * FROM abc' -> 'SELECT * FROM abc'
+        """
+        pattern = re.compile(r'^\s*\\ns\s+[A-z0-9\-_]+\s+')
+        if pattern.match(txt):
+            txt = pattern.sub('', txt)
+        return txt
+
+    full_text = strip_named_query(full_text)
+    text_before_cursor = strip_named_query(text_before_cursor)
+
     # If we've partially typed a word then word_before_cursor won't be an empty
     # string. In that case we want to remove the partially typed string before
     # sending it to the sqlparser. Otherwise the last token will always be the
diff --git a/tests/features/crud_database.feature b/tests/features/crud_database.feature
index 8b40a0ec..e0a3fd64 100644
--- a/tests/features/crud_database.feature
+++ b/tests/features/crud_database.feature
@@ -11,8 +11,6 @@ Feature: manipulate databases:
       then we see database dropped
       when we connect to postgres
       then we see database connected
-      when we send "ctrl + d"
-      then pgcli exits
 
   Scenario: connect and disconnect from test database
      Given we have pgcli installed
@@ -22,5 +20,3 @@ Feature: manipulate databases:
       then we see database connected
       when we connect to postgres
       then we see database connected
-      when we send "ctrl + d"
-      then pgcli exits
diff --git a/tests/features/crud_table.feature b/tests/features/crud_table.feature
index 2631372b..e49ee42c 100644
--- a/tests/features/crud_table.feature
+++ b/tests/features/crud_table.feature
@@ -21,5 +21,3 @@ Feature: manipulate tables:
       then we see table dropped
       when we connect to postgres
       then we see database connected
-      when we send "ctrl + d"
-      then pgcli exits
diff --git a/tests/features/steps/step_definitions.py b/tests/features/steps/step_definitions.py
index 05dbcb51..ac814cc7 100644
--- a/tests/features/steps/step_definitions.py
+++ b/tests/features/steps/step_definitions.py
@@ -28,6 +28,7 @@ def step_run_cli(context):
     Run the process using pexpect.
     """
     context.cli = pexpect.spawnu('pgcli')
+    context.exit_sent = False
 
 
 @when('we wait for prompt')
diff --git a/tests/test_sqlcompletion.py b/tests/test_sqlcompletion.py
index 3272aa14..355bfad1 100644
--- a/tests/test_sqlcompletion.py
+++ b/tests/test_sqlcompletion.py
@@ -1,10 +1,12 @@
 from pgcli.packages.sqlcompletion import suggest_type
 import pytest
 
+
 def sorted_dicts(dicts):
     """input is a list of dicts"""
     return sorted(tuple(x.items()) for x in dicts)
 
+
 def test_select_suggests_cols_with_visible_table_scope():
     suggestions = suggest_type('SELECT  FROM tabl', 'SELECT ')
     assert sorted_dicts(suggestions) == sorted_dicts([
@@ -13,6 +15,7 @@ def test_select_suggests_cols_with_visible_table_scope():
             {'type': 'keyword'}
             ])
 
+
 def test_select_suggests_cols_with_qualified_table_scope():
     suggestions = suggest_type('SELECT  FROM sch.tabl', 'SELECT ')
     assert sorted_dicts(suggestions) == sorted_dicts([
@@ -42,6 +45,7 @@ def test_where_suggests_columns_functions(expression):
             {'type': 'keyword'}
             ])
 
+
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM tabl WHERE foo IN (',
     'SELECT * FROM tabl WHERE foo IN (bar, ',
@@ -54,6 +58,7 @@ def test_where_in_suggests_columns(expression):
             {'type': 'keyword'}
             ])
 
+
 def test_where_equals_any_suggests_columns_or_keywords():
     text = 'SELECT * FROM tabl WHERE foo = ANY('
     suggestions = suggest_type(text, text)
@@ -62,11 +67,13 @@ def test_where_equals_any_suggests_columns_or_keywords():
             {'type': 'function', 'schema': []},
             {'type': 'keyword'}])
 
+
 def test_lparen_suggests_cols():
     suggestion = suggest_type('SELECT MAX( FROM tbl', 'SELECT MAX(')
     assert suggestion == [
         {'type': 'column', 'tables': [(None, 'tbl', None)]}]
 
+
 def test_select_suggests_cols_and_funcs():
     suggestions = suggest_type('SELECT ', 'SELECT ')
     assert sorted_dicts(suggestions) == sorted_dicts([
@@ -75,6 +82,7 @@ def test_select_suggests_cols_and_funcs():
          {'type': 'keyword'}
          ])
 
+
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM ',
     'INSERT INTO ',
@@ -90,6 +98,7 @@ def test_expression_suggests_tables_views_and_schemas(expression):
         {'type': 'view', 'schema': []},
         {'type': 'schema'}])
 
+
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM sch.',
     'INSERT INTO sch.',
@@ -104,21 +113,25 @@ def test_expression_suggests_qualified_tables_views_and_schemas(expression):
         {'type': 'table', 'schema': 'sch'},
         {'type': 'view', 'schema': 'sch'}])
 
+
 def test_truncate_suggests_tables_and_schemas():
     suggestions = suggest_type('TRUNCATE ', 'TRUNCATE ')
     assert sorted_dicts(suggestions) == sorted_dicts([
         {'type': 'table', 'schema': []},
         {'type': 'schema'}])
 
+
 def test_truncate_suggests_qualified_tables():
     suggestions = suggest_type('TRUNCATE sch.', 'TRUNCATE sch.')
     assert sorted_dicts(suggestions) == sorted_dicts([
         {'type': 'table', 'schema': 'sch'}])
 
+
 def test_distinct_suggests_cols():
     suggestions = suggest_type('SELECT DISTINCT ', 'SELECT DISTINCT ')
     assert suggestions == [{'type': 'column', 'tables': []}]
 
+
 def test_col_comma_suggests_cols():
     suggestions = suggest_type('SELECT a, b, FROM tbl', 'SELECT a, b,')
     assert sorted_dicts(suggestions) == sorted_dicts([
@@ -127,6 +140,7 @@ def test_col_comma_suggests_cols():
         {'type': 'keyword'}
         ])
 
+
 def test_table_comma_suggests_tables_and_schemas():
     suggestions = suggest_type('SELECT a, b FROM tbl1, ',
             'SELECT a, b FROM tbl1, ')
@@ -135,6 +149,7 @@ def test_table_comma_suggests_tables_and_schemas():
         {'type': 'view', 'schema': []},
         {'type': 'schema'}])
 
+
 def test_into_suggests_tables_and_schemas():
     suggestion = suggest_type('INSERT INTO ', 'INSERT INTO ')
     assert sorted_dicts(suggestion) == sorted_dicts([
@@ -142,18 +157,22 @@ def test_into_suggests_tables_and_schemas():
         {'type': 'view', 'schema': []},
         {'type': 'schema'}])
 
+
 def test_insert_into_lparen_suggests_cols():
     suggestions = suggest_type('INSERT INTO abc (', 'INSERT INTO abc (')
     assert suggestions == [{'type': 'column', 'tables': [(None, 'abc', None)]}]
 
+
 def test_insert_into_lparen_partial_text_suggests_cols():
     suggestions = suggest_type('INSERT INTO abc (i', 'INSERT INTO abc (i')
     assert suggestions == [{'type': 'column', 'tables': [(None, 'abc', None)]}]
 
+
 def test_insert_into_lparen_comma_suggests_cols():
     suggestions = suggest_type('INSERT INTO abc (id,', 'INSERT INTO abc (id,')
     assert suggestions == [{'type': 'column', 'tables': [(None, 'abc', None)]}]
 
+
 def test_partially_typed_col_name_suggests_col_names():
     suggestions = suggest_type('SELECT * FROM tabl WHERE col_n',
             'SELECT * FROM tabl WHERE col_n')
@@ -163,6 +182,7 @@ def test_partially_typed_col_name_suggests_col_names():
         {'type': 'keyword'}
         ])
 
+
 def test_dot_suggests_cols_of_a_table_or_schema_qualified_table():
     suggestions = suggest_type('SELECT tabl. FROM tabl', 'SELECT tabl.')
     assert sorted_dicts(suggestions) == sorted_dicts([
@@ -211,6 +231,7 @@ def test_dot_col_comma_suggests_cols_or_schema_qualified_table():
         {'type': 'view', 'schema': 't2'},
         {'type': 'function', 'schema': 't2'}])
 
+
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM (',
     'SELECT * FROM foo WHERE EXISTS (',
@@ -220,6 +241,7 @@ def test_sub_select_suggests_keyword(expression):
     suggestion = suggest_type(expression, expression)
     assert suggestion == [{'type': 'keyword'}]
 
+
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM (S',
     'SELECT * FROM foo WHERE EXISTS (S',
@@ -229,6 +251,7 @@ def test_sub_select_partial_text_suggests_keyword(expression):
     suggestion = suggest_type(expression, expression)
     assert suggestion == [{'type': 'keyword'}]
 
+
 def test_outer_table_reference_in_exists_subquery_suggests_columns():
     q = 'SELECT * FROM foo f WHERE EXISTS (SELECT 1 FROM bar WHERE f.'
     suggestions = suggest_type(q, q)
@@ -238,6 +261,7 @@ def test_outer_table_reference_in_exists_subquery_suggests_columns():
         {'type': 'view', 'schema': 'f'},
         {'type': 'function', 'schema': 'f'}]
 
+
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM (SELECT * FROM ',
     'SELECT * FROM foo WHERE EXISTS (SELECT * FROM ',
@@ -250,6 +274,7 @@ def test_sub_select_table_name_completion(expression):
         {'type': 'view', 'schema': []},
         {'type': 'schema'}])
 
+
 def test_sub_select_col_name_completion():
     suggestions = suggest_type('SELECT * FROM (SELECT  FROM abc',
             'SELECT * FROM (SELECT ')
@@ -259,6 +284,7 @@ def test_sub_select_col_name_completion():
         {'type': 'keyword'}
         ])
 
+
 @pytest.mark.xfail
 def test_sub_select_multiple_col_name_completion():
     suggestions = suggest_type('SELECT * FROM (SELECT a, FROM abc',
@@ -269,6 +295,7 @@ def test_sub_select_multiple_col_name_completion():
         {'type': 'keyword'}
         ])
 
+
 def test_sub_select_dot_col_name_completion():
     suggestions = suggest_type('SELECT * FROM (SELECT t. FROM tabl t',
             'SELECT * FROM (SELECT t.')
@@ -278,6 +305,7 @@ def test_sub_select_dot_col_name_completion():
         {'type': 'view', 'schema': 't'},
         {'type': 'function', 'schema': 't'}])
 
+
 @pytest.mark.parametrize('join_type', ['', 'INNER', 'LEFT', 'RIGHT OUTER'])
 @pytest.mark.parametrize('tbl_alias', ['', 'foo'])
 def test_join_suggests_tables_and_schemas(tbl_alias, join_type):
@@ -288,6 +316,7 @@ def test_join_suggests_tables_and_schemas(tbl_alias, join_type):
         {'type': 'view', 'schema': []},
         {'type': 'schema'}])
 
+
 def test_left_join_with_comma():
     text = 'select * from foo f left join bar b,'
     suggestions = suggest_type(text, text)
@@ -296,6 +325,7 @@ def test_left_join_with_comma():
          {'type': 'view', 'schema': []},
          {'type': 'schema'}])
 
+
 def test_join_alias_dot_suggests_cols1():
     suggestions = suggest_type('SELECT * FROM abc a JOIN def d ON a.',
             'SELECT * FROM abc a JOIN def d ON a.')
@@ -305,6 +335,7 @@ def test_join_alias_dot_suggests_cols1():
         {'type': 'view', 'schema': 'a'},
         {'type': 'function', 'schema': 'a'}])
 
+
 def test_join_alias_dot_suggests_cols2():
     suggestion = suggest_type('SELECT * FROM abc a JOIN def d ON a.',
             'SELECT * FROM abc a JOIN def d ON a.id = d.')
@@ -314,30 +345,35 @@ def test_join_alias_dot_suggests_cols2():
         {'type': 'view', 'schema': 'd'},
         {'type': 'function', 'schema': 'd'}])
 
+
 def test_on_suggests_aliases():
     suggestions = suggest_type(
         'select a.x, b.y from abc a join bcd b on ',
         'select a.x, b.y from abc a join bcd b on ')
     assert suggestions == [{'type': 'alias', 'aliases': ['a', 'b']}]
 
+
 def test_on_suggests_tables():
     suggestions = suggest_type(
         'select abc.x, bcd.y from abc join bcd on ',
         'select abc.x, bcd.y from abc join bcd on ')
     assert suggestions == [{'type': 'alias', 'aliases': ['abc', 'bcd']}]
 
+
 def test_on_suggests_aliases_right_side():
     suggestions = suggest_type(
         'select a.x, b.y from abc a join bcd b on a.id = ',
         'select a.x, b.y from abc a join bcd b on a.id = ')
     assert suggestions == [{'type': 'alias', 'aliases': ['a', 'b']}]
 
+
 def test_on_suggests_tables_right_side():
     suggestions = suggest_type(
         'select abc.x, bcd.y from abc join bcd on ',
         'select abc.x, bcd.y from abc join bcd on ')
     assert suggestions == [{'type': 'alias', 'aliases': ['abc', 'bcd']}]
 
+
 @pytest.mark.parametrize('col_list', ['', 'col1, '])
 def test_join_using_suggests_common_columns(col_list):
     text = 'select * from abc inner join def using (' + col_list
@@ -371,6 +407,7 @@ def test_2_statements_2nd_current():
          {'type': 'view', 'schema': []},
          {'type': 'schema'}])
 
+
 def test_2_statements_1st_current():
     suggestions = suggest_type('select * from ; select * from b',
                                'select * from ')
@@ -387,6 +424,7 @@ def test_2_statements_1st_current():
             {'type': 'keyword'}
             ])
 
+
 def test_3_statements_2nd_current():
     suggestions = suggest_type('select * from a; select * from ; select * from c',
                                'select * from a; select * from ')
@@ -519,3 +557,21 @@ def test_suggest_where_keyword(text):
                            {'schema': [], 'type': 'function'},
                            {'type': 'keyword'}
                            ]
+
+@pytest.mark.parametrize('text, before, expected', [
+    ('\\ns abc SELECT ', 'SELECT ', [
+        {'type': 'column', 'tables': []},
+        {'type': 'function', 'schema': []},
+        {'type': 'keyword'}
+    ]),
+    ('\\ns abc SELECT foo ', 'SELECT foo ', [{'type': 'keyword'}]),
+    ('\\ns abc SELECT t1. FROM tabl1 t1', 'SELECT t1.', [
+        {'type': 'table', 'schema': 't1'},
+        {'type': 'view', 'schema': 't1'},
+        {'type': 'column', 'tables': [(None, 'tabl1', 't1')]},
+        {'type': 'function', 'schema': 't1'}
+    ])
+])
+def test_named_query_completion(text, before, expected):
+    suggestions = suggest_type(text, before)
+    assert sorted_dicts(expected) == sorted_dicts(suggestions)