import MySQL completion candidates from pygmentsRW/complete-keywords-functions

 * remove LEN and TOP, which are not MySQL reserved words
 * preserve completion candidates containing space, such as "ORDER BY"

4 files changed, 100 insertions, 39 deletions

diff --git a/changelog.md b/changelog.md
index 6c54ef7..c6269a8 100644
--- a/changelog.md
+++ b/changelog.md
@@ -10,6 +10,7 @@ Features:
 * Allow customization of Pygments SQL syntax-highlighting styles.
 * Add a `\clip` special command to copy queries to the system clipboard.
 * Add a special command `\pipe_once` to pipe output to a subprocess.
+* More complete and up-to-date set of MySQL reserved words for completions.
 
 
 Bug Fixes:
diff --git a/mycli/sqlcompleter.py b/mycli/sqlcompleter.py
index 20611be..05bc983 100644
--- a/mycli/sqlcompleter.py
+++ b/mycli/sqlcompleter.py
@@ -3,6 +3,10 @@ from re import compile, escape
 from collections import Counter
 
 from prompt_toolkit.completion import Completer, Completion
+from pygments.lexers._mysql_builtins import \
+    MYSQL_DATATYPES, \
+    MYSQL_FUNCTIONS, \
+    MYSQL_KEYWORDS
 
 from .packages.completion_engine import suggest_type
 from .packages.parseutils import last_word
@@ -13,33 +17,11 @@ _logger = logging.getLogger(__name__)
 
 
 class SQLCompleter(Completer):
-    keywords = ['ACCESS', 'ADD', 'ALL', 'ALTER TABLE', 'AND', 'ANY', 'AS',
-                'ASC', 'AUTO_INCREMENT', 'BEFORE', 'BEGIN', 'BETWEEN',
-                'BIGINT', 'BINARY', 'BY', 'CASE', 'CHANGE MASTER TO', 'CHAR',
-                'CHARACTER SET', 'CHECK', 'COLLATE', 'COLUMN', 'COMMENT',
-                'COMMIT', 'CONSTRAINT', 'CREATE', 'CURRENT',
-                'CURRENT_TIMESTAMP', 'DATABASE', 'DATE', 'DECIMAL', 'DEFAULT',
-                'DELETE FROM', 'DESC', 'DESCRIBE', 'DROP',
-                'ELSE', 'END', 'ENGINE', 'ESCAPE', 'EXISTS', 'FILE', 'FLOAT',
-                'FOR', 'FOREIGN KEY', 'FORMAT', 'FROM', 'FULL', 'FUNCTION',
-                'GRANT', 'GROUP BY', 'HAVING', 'HOST', 'IDENTIFIED', 'IN',
-                'INCREMENT', 'INDEX', 'INSERT INTO', 'INT', 'INTEGER',
-                'INTERVAL', 'INTO', 'IS', 'JOIN', 'KEY', 'LEFT', 'LEVEL',
-                'LIKE', 'LIMIT', 'LOCK', 'LOGS', 'LONG', 'MASTER',
-                'MEDIUMINT', 'MODE', 'MODIFY', 'NOT', 'NULL', 'NUMBER',
-                'OFFSET', 'ON', 'OPTION', 'OR', 'ORDER BY', 'OUTER', 'OWNER',
-                'PASSWORD', 'PORT', 'PRIMARY', 'PRIVILEGES', 'PROCESSLIST',
-                'PURGE', 'REFERENCES', 'REGEXP', 'RENAME', 'REPAIR', 'RESET',
-                'REVOKE', 'RIGHT', 'ROLLBACK', 'ROW', 'ROWS', 'ROW_FORMAT',
-                'SAVEPOINT', 'SELECT', 'SESSION', 'SET', 'SHARE', 'SHOW',
-                'SLAVE', 'SMALLINT', 'SMALLINT', 'START', 'STOP', 'TABLE',
-                'THEN', 'TINYINT', 'TO', 'TRANSACTION', 'TRIGGER', 'TRUNCATE',
-                'UNION', 'UNIQUE', 'UNSIGNED', 'UPDATE', 'USE', 'USER',
-                'USING', 'VALUES', 'VARCHAR', 'VIEW', 'WHEN', 'WHERE', 'WITH']
-
-    functions = ['AVG', 'CONCAT', 'COUNT', 'DISTINCT', 'FIRST', 'FORMAT',
-                 'FROM_UNIXTIME', 'LAST', 'LCASE', 'LEN', 'MAX', 'MID',
-                 'MIN', 'NOW', 'ROUND', 'SUM', 'TOP', 'UCASE', 'UNIX_TIMESTAMP']
+    keywords = [x.upper() for x in list(MYSQL_DATATYPES) + list(MYSQL_KEYWORDS) +
+                ['ALTER TABLE', 'CHANGE MASTER TO', 'CHARACTER SET', 'DELETE FROM',
+                 'FOREIGN KEY', 'GROUP BY', 'INSERT INTO', 'ORDER BY']]
+
+    functions = [x.upper() for x in MYSQL_FUNCTIONS]
 
     show_items = []
 
diff --git a/test/test_naive_completion.py b/test/test_naive_completion.py
index 14c1bf5..3d9d1b2 100644
--- a/test/test_naive_completion.py
+++ b/test/test_naive_completion.py
@@ -40,8 +40,47 @@ def test_function_name_completion(completer, complete_event):
         Document(text=text, cursor_position=position),
         complete_event))
     assert result == list([
+        Completion(text='MAKEDATE', start_position=-2),
+        Completion(text='MAKETIME', start_position=-2),
+        Completion(text='MAKE_SET', start_position=-2),
         Completion(text='MASTER', start_position=-2),
-        Completion(text='MAX', start_position=-2)])
+        Completion(text='MASTER_AUTO_POSITION', start_position=-2),
+        Completion(text='MASTER_BIND', start_position=-2),
+        Completion(text='MASTER_COMPRESSION_ALGORITHMS', start_position=-2),
+        Completion(text='MASTER_CONNECT_RETRY', start_position=-2),
+        Completion(text='MASTER_DELAY', start_position=-2),
+        Completion(text='MASTER_HEARTBEAT_PERIOD', start_position=-2),
+        Completion(text='MASTER_HOST', start_position=-2),
+        Completion(text='MASTER_LOG_FILE', start_position=-2),
+        Completion(text='MASTER_LOG_POS', start_position=-2),
+        Completion(text='MASTER_PASSWORD', start_position=-2),
+        Completion(text='MASTER_PORT', start_position=-2),
+        Completion(text='MASTER_POS_WAIT', start_position=-2),
+        Completion(text='MASTER_PUBLIC_KEY_PATH', start_position=-2),
+        Completion(text='MASTER_RETRY_COUNT', start_position=-2),
+        Completion(text='MASTER_SERVER_ID', start_position=-2),
+        Completion(text='MASTER_SSL', start_position=-2),
+        Completion(text='MASTER_SSL_CA', start_position=-2),
+        Completion(text='MASTER_SSL_CAPATH', start_position=-2),
+        Completion(text='MASTER_SSL_CERT', start_position=-2),
+        Completion(text='MASTER_SSL_CIPHER', start_position=-2),
+        Completion(text='MASTER_SSL_CRL', start_position=-2),
+        Completion(text='MASTER_SSL_CRLPATH', start_position=-2),
+        Completion(text='MASTER_SSL_KEY', start_position=-2),
+        Completion(text='MASTER_SSL_VERIFY_SERVER_CERT', start_position=-2),
+        Completion(text='MASTER_TLS_CIPHERSUITES', start_position=-2),
+        Completion(text='MASTER_TLS_VERSION', start_position=-2),
+        Completion(text='MASTER_USER', start_position=-2),
+        Completion(text='MASTER_ZSTD_COMPRESSION_LEVEL', start_position=-2),
+        Completion(text='MATCH', start_position=-2),
+        Completion(text='MAX', start_position=-2),
+        Completion(text='MAXVALUE', start_position=-2),
+        Completion(text='MAX_CONNECTIONS_PER_HOUR', start_position=-2),
+        Completion(text='MAX_QUERIES_PER_HOUR', start_position=-2),
+        Completion(text='MAX_ROWS', start_position=-2),
+        Completion(text='MAX_SIZE', start_position=-2),
+        Completion(text='MAX_UPDATES_PER_HOUR', start_position=-2),
+        Completion(text='MAX_USER_CONNECTIONS', start_position=-2)])
 
 
 def test_column_name_completion(completer, complete_event):
diff --git a/test/test_smart_completion_public_schema_only.py b/test/test_smart_completion_public_schema_only.py
index b66c696..e01aa4b 100644
--- a/test/test_smart_completion_public_schema_only.py
+++ b/test/test_smart_completion_public_schema_only.py
@@ -86,9 +86,48 @@ def test_function_name_completion(completer, complete_event):
     position = len('SELECT MA')
     result = completer.get_completions(
         Document(text=text, cursor_position=position), complete_event)
-    assert list(result) == list([Completion(text='MAX', start_position=-2),
-                               Completion(text='MASTER', start_position=-2),
-                               ])
+    assert list(result) == list([
+        Completion(text='MAKEDATE', start_position=-2),
+        Completion(text='MAKETIME', start_position=-2),
+        Completion(text='MAKE_SET', start_position=-2),
+        Completion(text='MASTER_POS_WAIT', start_position=-2),
+        Completion(text='MAX', start_position=-2),
+        Completion(text='MASTER', start_position=-2),
+        Completion(text='MASTER_AUTO_POSITION', start_position=-2),
+        Completion(text='MASTER_BIND', start_position=-2),
+        Completion(text='MASTER_COMPRESSION_ALGORITHMS', start_position=-2),
+        Completion(text='MASTER_CONNECT_RETRY', start_position=-2),
+        Completion(text='MASTER_DELAY', start_position=-2),
+        Completion(text='MASTER_HEARTBEAT_PERIOD', start_position=-2),
+        Completion(text='MASTER_HOST', start_position=-2),
+        Completion(text='MASTER_LOG_FILE', start_position=-2),
+        Completion(text='MASTER_LOG_POS', start_position=-2),
+        Completion(text='MASTER_PASSWORD', start_position=-2),
+        Completion(text='MASTER_PORT', start_position=-2),
+        Completion(text='MASTER_PUBLIC_KEY_PATH', start_position=-2),
+        Completion(text='MASTER_RETRY_COUNT', start_position=-2),
+        Completion(text='MASTER_SERVER_ID', start_position=-2),
+        Completion(text='MASTER_SSL', start_position=-2),
+        Completion(text='MASTER_SSL_CA', start_position=-2),
+        Completion(text='MASTER_SSL_CAPATH', start_position=-2),
+        Completion(text='MASTER_SSL_CERT', start_position=-2),
+        Completion(text='MASTER_SSL_CIPHER', start_position=-2),
+        Completion(text='MASTER_SSL_CRL', start_position=-2),
+        Completion(text='MASTER_SSL_CRLPATH', start_position=-2),
+        Completion(text='MASTER_SSL_KEY', start_position=-2),
+        Completion(text='MASTER_SSL_VERIFY_SERVER_CERT', start_position=-2),
+        Completion(text='MASTER_TLS_CIPHERSUITES', start_position=-2),
+        Completion(text='MASTER_TLS_VERSION', start_position=-2),
+        Completion(text='MASTER_USER', start_position=-2),
+        Completion(text='MASTER_ZSTD_COMPRESSION_LEVEL', start_position=-2),
+        Completion(text='MATCH', start_position=-2),
+        Completion(text='MAXVALUE', start_position=-2),
+        Completion(text='MAX_CONNECTIONS_PER_HOUR', start_position=-2),
+        Completion(text='MAX_QUERIES_PER_HOUR', start_position=-2),
+        Completion(text='MAX_ROWS', start_position=-2),
+        Completion(text='MAX_SIZE', start_position=-2),
+        Completion(text='MAX_UPDATES_PER_HOUR', start_position=-2),
+        Completion(text='MAX_USER_CONNECTIONS', start_position=-2)])
 
 
 def test_suggested_column_names(completer, complete_event):
@@ -111,9 +150,9 @@ def test_suggested_column_names(completer, complete_event):
         Completion(text='id', start_position=0),
         Completion(text='last_name', start_position=0),
     ] +
-        list(map(Completion, completer.functions)) +
+        list(map(Completion, sorted(completer.functions))) +
         [Completion(text='users', start_position=0)] +
-        list(map(Completion, completer.keywords)))
+        list(map(Completion, sorted(completer.keywords))))
 
 
 def test_suggested_column_names_in_function(completer, complete_event):
@@ -200,9 +239,9 @@ def test_suggested_multiple_column_names(completer, complete_event):
         Completion(text='first_name', start_position=0),
         Completion(text='id', start_position=0),
         Completion(text='last_name', start_position=0)] +
-        list(map(Completion, completer.functions)) +
+        list(map(Completion, sorted(completer.functions))) +
         [Completion(text='u', start_position=0)] +
-        list(map(Completion, completer.keywords)))
+        list(map(Completion, sorted(completer.keywords))))
 
 
 def test_suggested_multiple_column_names_with_alias(completer, complete_event):
@@ -321,9 +360,9 @@ def test_auto_escaped_col_names(completer, complete_event):
         Completion(text='`insert`', start_position=0),
         Completion(text='id', start_position=0),
     ] + \
-        list(map(Completion, completer.functions)) + \
+        list(map(Completion, sorted(completer.functions))) + \
         [Completion(text='`select`', start_position=0)] + \
-        list(map(Completion, completer.keywords))
+        list(map(Completion, sorted(completer.keywords)))
 
 
 def test_un_escaped_table_names(completer, complete_event):
@@ -338,9 +377,9 @@ def test_un_escaped_table_names(completer, complete_event):
         Completion(text='`insert`', start_position=0),
         Completion(text='id', start_position=0),
     ] +
-        list(map(Completion, completer.functions)) +
+        list(map(Completion, sorted(completer.functions))) +
         [Completion(text='réveillé', start_position=0)] +
-        list(map(Completion, completer.keywords)))
+        list(map(Completion, sorted(completer.keywords))))
 
 
 def dummy_list_path(dir_name):