Merge branch 'main' into tidbtidb

15 files changed, 226 insertions, 20 deletions

diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index b678f57..752ddb5 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -9,16 +9,16 @@ jobs:
   linux:
     strategy:
       matrix:
-        python-version: [3.6, 3.7, 3.8, 3.9]
+        python-version: ['3.7', '3.8', '3.9', '3.10']
         include:
-          - python-version: 3.6
-            os: ubuntu-18.04 # MySQL 5.7.32
-          - python-version: 3.7
-            os: ubuntu-18.04 # MySQL 5.7.32
-          - python-version: 3.8
-            os: ubuntu-18.04 # MySQL 5.7.32
-          - python-version: 3.9
+          - python-version: '3.7'
+            os: ubuntu-18.04  # MySQL 5.7.32
+          - python-version: '3.8'
+            os: ubuntu-18.04  # MySQL 5.7.32
+          - python-version: '3.9'
             os: ubuntu-20.04  # MySQL 8.0.22
+          - python-version: '3.10'
+            os: ubuntu-22.04  # MySQL 8.0.28
 
     runs-on: ${{ matrix.os }}
     steps:
diff --git a/README.md b/README.md
index e15f505..9e177b7 100644
--- a/README.md
+++ b/README.md
@@ -69,6 +69,8 @@ $ sudo apt-get install mycli # Only on debian or ubuntu
       --ssh-config-host TEXT        Host to connect to ssh server reading from ssh
                                     configuration.
 
+      --ssl                         Enable SSL for connection (automatically
+                                    enabled with other flags).
       --ssl-ca PATH                 CA file in PEM format.
       --ssl-capath TEXT             CA directory.
       --ssl-cert PATH               X509 cert in PEM format.
@@ -133,6 +135,7 @@ Features
 * Log every query and its results to a file (disabled by default).
 * Pretty prints tabular data (with colors!)
 * Support for SSL connections
+* Some features are only exposed as [key bindings](doc/key_bindings.rst)
 
 Contributions:
 --------------
@@ -217,7 +220,7 @@ Thanks to [PyMysql](https://github.com/PyMySQL/PyMySQL) for a pure python adapte
 
 ### Compatibility
 
-Mycli is tested on macOS and Linux.
+Mycli is tested on macOS and Linux, and requires Python 3.7 or better.
 
 **Mycli is not tested on Windows**, but the libraries used in this app are Windows-compatible.
 This means it should work without any modifications. If you're unable to run it
diff --git a/changelog.md b/changelog.md
index 773ca02..1c7ae27 100644
--- a/changelog.md
+++ b/changelog.md
@@ -1,6 +1,5 @@
-
-TBD
-===
+Upcoming
+========
 
 Features:
 ---------
@@ -8,6 +7,27 @@ Features:
 * Detect TiDB instance and show in the prompt and use additional keywords.
 * Fix the completion order to show more commonly use keywords in the top.
 
+
+1.26.1 (2022/09/01)
+===
+
+=======
+Bug Fixes:
+----------
+* Require Python 3.7 in `setup.py`
+
+
+1.26.0 (2022/09/01)
+===================
+
+Features:
+---------
+
+* Add `--ssl` flag to enable ssl/tls.
+* Add `pager` option to `~/.myclirc`, for instance `pager = 'pspg --csv'` (Thanks: [BuonOmo])
+* Add prettify/unprettify keybindings to format the current statement using `sqlglot`.
+
+
 Internal:
 ---------
 * Pin `cryptography` to suppress `paramiko` warning, helping CI complete and presumably affecting some users.
@@ -896,6 +916,7 @@ Bug Fixes:
 
 [Amjith Ramanujam]: https://blog.amjith.com
 [Artem Bezsmertnyi]: https://github.com/mrdeathless
+[BuonOmo]: https://github.com/BuonOmo
 [Carlos Afonso]: https://github.com/afonsocarlos
 [Casper Langemeijer]: https://github.com/langemeijer
 [Daniel West]: http://github.com/danieljwest
diff --git a/doc/key_bindings.rst b/doc/key_bindings.rst
new file mode 100644
index 0000000..0534870
--- /dev/null
+++ b/doc/key_bindings.rst
@@ -0,0 +1,65 @@
+*************
+Key Bindings:
+*************
+
+Most key bindings are simply inherited from `prompt-toolkit <https://python-prompt-toolkit.readthedocs.io/en/master/index.html>`_ .
+
+The following key bindings are special to mycli:
+
+###
+F2
+###
+
+Enable/Disable SmartCompletion Mode.
+
+###
+F3
+###
+
+Enable/Disable Multiline Mode.
+
+###
+F4
+###
+
+Toggle between Vi and Emacs mode.
+
+###
+Tab
+###
+
+Force autocompletion at cursor.
+
+#######
+C-space
+#######
+
+Initialize autocompletion at cursor.
+
+If the autocompletion menu is not showing, display it with the appropriate completions for the context.
+
+If the menu is showing, select the next completion.
+
+#########
+ESC Enter
+#########
+
+Introduce a line break in multi-line mode, or dispatch the command in single-line mode.
+
+The sequence ESC-Enter is often sent by Alt-Enter.
+
+#################################
+C-x p (Emacs-mode) or > (Vi-mode)
+#################################
+
+Prettify and indent current statement, usually into multiple lines.
+
+Only accepts buffers containing single SQL statements.
+
+#################################
+C-x u (Emacs-mode) or < (Vi-mode)
+#################################
+
+Unprettify and dedent current statement, usually into one line.
+
+Only accepts buffers containing single SQL statements.
diff --git a/mycli/AUTHORS b/mycli/AUTHORS
index 328805d..a805465 100644
--- a/mycli/AUTHORS
+++ b/mycli/AUTHORS
@@ -24,6 +24,7 @@ Contributors:
   * Artem Bezsmertnyi
   * bitkeen
   * bjarnagin
+  * BuonOmo
   * caitinggui
   * Carlos Afonso
   * Casper Langemeijer
@@ -92,6 +93,7 @@ Contributors:
   * Zhongyang Guan
   * Arvind Mishra
   * Kevin Schmeichel
+  * Mel Dafert
 
 Created by:
 -----------
diff --git a/mycli/__init__.py b/mycli/__init__.py
index 8de33c0..1512b41 100644
--- a/mycli/__init__.py
+++ b/mycli/__init__.py
@@ -1 +1 @@
-__version__ = '1.25.0'
+__version__ = '1.26.1'
diff --git a/mycli/clitoolbar.py b/mycli/clitoolbar.py
index eec2978..24d1108 100644
--- a/mycli/clitoolbar.py
+++ b/mycli/clitoolbar.py
@@ -30,6 +30,11 @@ def create_toolbar_tokens_func(mycli, show_fish_help):
                 'Vi-mode ({})'.format(_get_vi_mode())
             ))
 
+        if mycli.toolbar_error_message:
+            result.append(
+                ('class:bottom-toolbar', '  ' + mycli.toolbar_error_message))
+            mycli.toolbar_error_message = None
+
         if show_fish_help():
             result.append(
                 ('class:bottom-toolbar', '  Right-arrow to complete suggestion'))
diff --git a/mycli/key_bindings.py b/mycli/key_bindings.py
index 4a24c82..03e4ace 100644
--- a/mycli/key_bindings.py
+++ b/mycli/key_bindings.py
@@ -1,6 +1,6 @@
 import logging
 from prompt_toolkit.enums import EditingMode
-from prompt_toolkit.filters import completion_is_selected
+from prompt_toolkit.filters import completion_is_selected, emacs_mode, vi_mode
 from prompt_toolkit.key_binding import KeyBindings
 
 _logger = logging.getLogger(__name__)
@@ -61,6 +61,48 @@ def mycli_bindings(mycli):
         else:
             b.start_completion(select_first=False)
 
+    @kb.add('>', filter=vi_mode)
+    @kb.add('c-x', 'p', filter=emacs_mode)
+    def _(event):
+        """
+        Prettify and indent current statement, usually into multiple lines.
+
+        Only accepts buffers containing single SQL statements.
+        """
+        _logger.debug('Detected <C-x p>/> key.')
+
+        b = event.app.current_buffer
+        cursorpos_relative = b.cursor_position / len(b.text)
+        pretty_text = mycli.handle_prettify_binding(b.text)
+        if len(pretty_text) > 0:
+            b.text = pretty_text
+            cursorpos_abs = int(round(cursorpos_relative * len(b.text)))
+            while 0 < cursorpos_abs < len(b.text) \
+                  and b.text[cursorpos_abs] in (' ', '\n'):
+                cursorpos_abs -= 1
+            b.cursor_position = min(cursorpos_abs, len(b.text))
+
+    @kb.add('<', filter=vi_mode)
+    @kb.add('c-x', 'u', filter=emacs_mode)
+    def _(event):
+        """
+        Unprettify and dedent current statement, usually into one line.
+
+        Only accepts buffers containing single SQL statements.
+        """
+        _logger.debug('Detected <C-x u>/< key.')
+
+        b = event.app.current_buffer
+        cursorpos_relative = b.cursor_position / len(b.text)
+        unpretty_text = mycli.handle_unprettify_binding(b.text)
+        if len(unpretty_text) > 0:
+            b.text = unpretty_text
+            cursorpos_abs = int(round(cursorpos_relative * len(b.text)))
+            while 0 < cursorpos_abs < len(b.text) \
+                  and b.text[cursorpos_abs] in (' ', '\n'):
+                cursorpos_abs -= 1
+            b.cursor_position = min(cursorpos_abs, len(b.text))
+
     @kb.add('enter', filter=completion_is_selected)
     def _(event):
         """Makes the enter key work as the tab key only when showing the menu.
diff --git a/mycli/main.py b/mycli/main.py
index 0561af8..208572d 100755
--- a/mycli/main.py
+++ b/mycli/main.py
@@ -24,6 +24,7 @@ from cli_helpers.tabular_output import preprocessors
 from cli_helpers.utils import strip_ansi
 import click
 import sqlparse
+import sqlglot
 from mycli.packages.parseutils import is_dropping_database, is_destructive
 from prompt_toolkit.completion import DynamicCompleter
 from prompt_toolkit.enums import DEFAULT_BUFFER, EditingMode
@@ -123,6 +124,7 @@ class MyCli(object):
         self.logfile = logfile
         self.defaults_suffix = defaults_suffix
         self.login_path = login_path
+        self.toolbar_error_message = None
 
         # self.cnf_files is a class variable that stores the list of mysql
         # config files to read in at launch.
@@ -582,6 +584,34 @@ class MyCli(object):
             return True
         return False
 
+    def handle_prettify_binding(self, text):
+        try:
+            statements = sqlglot.parse(text, read='mysql')
+        except Exception as e:
+            statements = []
+        if len(statements) == 1:
+            pretty_text = statements[0].sql(pretty=True, pad=4, dialect='mysql')
+        else:
+            pretty_text = ''
+            self.toolbar_error_message = 'Prettify failed to parse statement'
+        if len(pretty_text) > 0:
+            pretty_text = pretty_text + ';'
+        return pretty_text
+
+    def handle_unprettify_binding(self, text):
+        try:
+            statements = sqlglot.parse(text, read='mysql')
+        except Exception as e:
+            statements = []
+        if len(statements) == 1:
+            unpretty_text = statements[0].sql(pretty=False, dialect='mysql')
+        else:
+            unpretty_text = ''
+            self.toolbar_error_message = 'Unprettify failed to parse statement'
+        if len(unpretty_text) > 0:
+            unpretty_text = unpretty_text + ';'
+        return unpretty_text
+
     def run_cli(self):
         iterations = 0
         sqlexecute = self.sqlexecute
@@ -724,7 +754,7 @@ class MyCli(object):
                         except KeyboardInterrupt:
                             pass
                         if self.beep_after_seconds > 0 and t >= self.beep_after_seconds:
-                            self.echo('\a', err=True, nl=False)
+                            self.bell()
                         if special.is_timing_enabled():
                             self.echo('Time: %0.03fs' % t)
                     except KeyboardInterrupt:
@@ -865,6 +895,11 @@ class MyCli(object):
         self.log_output(s)
         click.secho(s, **kwargs)
 
+    def bell(self):
+        """Print a bell on the stderr.
+        """
+        click.secho('\a', err=True, nl=False)
+
     def get_output_margin(self, status=None):
         """Get the output margin (number of rows for the prompt, footer and
         timing message."""
@@ -938,8 +973,9 @@ class MyCli(object):
             os.environ['LESS'] = '-RXF'
 
         cnf = self.read_my_cnf_files(self.cnf_files, ['pager', 'skip-pager'])
-        if cnf['pager']:
-            special.set_pager(cnf['pager'])
+        cnf_pager = cnf['pager'] or self.config['main']['pager']
+        if cnf_pager:
+            special.set_pager(cnf_pager)
             self.explicit_pager = True
         else:
             self.explicit_pager = False
@@ -1089,6 +1125,8 @@ class MyCli(object):
 @click.option('--ssh-config-path', help='Path to ssh configuration.',
               default=os.path.expanduser('~') + '/.ssh/config')
 @click.option('--ssh-config-host', help='Host to connect to ssh server reading from ssh configuration.')
+@click.option('--ssl', 'ssl_enable', is_flag=True,
+        help='Enable SSL for connection (automatically enabled with other flags).')
 @click.option('--ssl-ca', help='CA file in PEM format.',
               type=click.Path(exists=True))
 @click.option('--ssl-capath', help='CA directory.')
@@ -1147,7 +1185,7 @@ class MyCli(object):
 def cli(database, user, host, port, socket, password, dbname,
         version, verbose, prompt, logfile, defaults_group_suffix,
         defaults_file, login_path, auto_vertical_output, local_infile,
-        ssl_ca, ssl_capath, ssl_cert, ssl_key, ssl_cipher,
+        ssl_enable, ssl_ca, ssl_capath, ssl_cert, ssl_key, ssl_cipher,
         ssl_verify_server_cert, table, csv, warn, execute, myclirc, dsn,
         list_dsn, ssh_user, ssh_host, ssh_port, ssh_password,
         ssh_key_filename, list_ssh_config, ssh_config_path, ssh_config_host,
@@ -1202,6 +1240,7 @@ def cli(database, user, host, port, socket, password, dbname,
     database = dbname or database
 
     ssl = {
+            'enable': ssl_enable,
             'ca': ssl_ca and os.path.expanduser(ssl_ca),
             'cert': ssl_cert and os.path.expanduser(ssl_cert),
             'key': ssl_key and os.path.expanduser(ssl_key),
diff --git a/mycli/myclirc b/mycli/myclirc
index ffd2226..cd58dfe 100644
--- a/mycli/myclirc
+++ b/mycli/myclirc
@@ -89,6 +89,9 @@ keyword_casing = auto
 # disabled pager on startup
 enable_pager = True
 
+# Choose a specific pager
+pager = 'less'
+
 # Custom colors for the completion menu, toolbar, etc.
 [colors]
 completion-menu.completion.current = 'bg:#ffffff #000000'
diff --git a/mycli/packages/completion_engine.py b/mycli/packages/completion_engine.py
index c7db06c..2735f5b 100644
--- a/mycli/packages/completion_engine.py
+++ b/mycli/packages/completion_engine.py
@@ -129,6 +129,8 @@ def suggest_based_on_last_token(token, text_before_cursor, full_text, identifier
         prev_keyword, text_before_cursor = find_prev_keyword(text_before_cursor)
         return suggest_based_on_last_token(prev_keyword, text_before_cursor,
                                            full_text, identifier)
+    elif token is None:
+        return [{'type': 'keyword'}]
     else:
         token_v = token.value.lower()
 
diff --git a/requirements-dev.txt b/requirements-dev.txt
index 3d10e9a..955a9f5 100644
--- a/requirements-dev.txt
+++ b/requirements-dev.txt
@@ -14,3 +14,4 @@ paramiko==2.11.0
 pyperclip>=1.8.1
 importlib_resources>=5.0.0
 pyaes>=1.6.1
+sqlglot>=5.1.3
diff --git a/setup.py b/setup.py
index c9dcc44..2f69672 100755
--- a/setup.py
+++ b/setup.py
@@ -26,6 +26,7 @@ install_requirements = [
     'prompt_toolkit>=3.0.6,<4.0.0',
     'PyMySQL >= 0.9.2',
     'sqlparse>=0.3.0,<0.5.0',
+    'sqlglot>=5.1.3',
     'configobj >= 5.0.5',
     'cli_helpers[styles] >= 2.2.1',
     'pyperclip >= 1.8.1',
@@ -103,16 +104,17 @@ setup(
         'console_scripts': ['mycli = mycli.main:cli'],
     },
     cmdclass={'lint': lint, 'test': test},
-    python_requires=">=3.6",
+    python_requires=">=3.7",
     classifiers=[
         'Intended Audience :: Developers',
         'License :: OSI Approved :: BSD License',
         'Operating System :: Unix',
         'Programming Language :: Python',
         'Programming Language :: Python :: 3',
-        'Programming Language :: Python :: 3.6',
         'Programming Language :: Python :: 3.7',
         'Programming Language :: Python :: 3.8',
+        'Programming Language :: Python :: 3.9',
+        'Programming Language :: Python :: 3.10',
         'Programming Language :: SQL',
         'Topic :: Database',
         'Topic :: Database :: Front-Ends',
diff --git a/test/test_completion_engine.py b/test/test_completion_engine.py
index 8b06ed3..318b632 100644
--- a/test/test_completion_engine.py
+++ b/test/test_completion_engine.py
@@ -542,7 +542,14 @@ def test_favorite_name_suggestion(expression):
     suggestions = suggest_type(expression, expression)
     assert suggestions == [{'type': 'favoritequery'}]
 
+
 def test_order_by():
     text = 'select * from foo order by '
     suggestions = suggest_type(text, text)
     assert suggestions == [{'tables': [(None, 'foo', None)], 'type': 'column'}]
+
+
+def test_quoted_where():
+    text = "'where i=';"
+    suggestions = suggest_type(text, text)
+    assert suggestions == [{'type': 'keyword'}]
diff --git a/test/test_main.py b/test/test_main.py
index c3351ec..64cba0a 100644
--- a/test/test_main.py
+++ b/test/test_main.py
@@ -283,6 +283,20 @@ def test_list_dsn():
         assert result.output == "test : mysql://test/test\n"
 
 
+def test_prettify_statement():
+    statement = 'SELECT 1'
+    m = MyCli()
+    pretty_statement = m.handle_prettify_binding(statement)
+    assert pretty_statement == 'SELECT\n    1;'
+
+
+def test_unprettify_statement():
+    statement = 'SELECT\n    1'
+    m = MyCli()
+    unpretty_statement = m.handle_unprettify_binding(statement)
+    assert unpretty_statement == 'SELECT 1;'
+
+
 def test_list_ssh_config():
     runner = CliRunner()
     with NamedTemporaryFile(mode="w") as ssh_config: