Render and reflect MySQL WITH PARSER index options

Support added for the "WITH PARSER" syntax of CREATE FULLTEXT INDEX
in MySQL, using the ``mysql_with_parser`` keyword argument.  Reflection
is also supported, which accommodates MySQL's special comment format
for reporting on this option as well.  Additionally, the "FULLTEXT" and
"SPATIAL" index prefixes are now reflected back into the ``mysql_prefix``
index option.

Change-Id: I0209291978125d8cee1bb5ed386d4f66578697a0
Fixes: #4219

2 files changed, 46 insertions, 2 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index b5d4cb4b7..c8a3d3322 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -525,6 +525,19 @@ http://dev.mysql.com/doc/refman/5.0/en/create-index.html
 
 http://dev.mysql.com/doc/refman/5.0/en/create-table.html
 
+Index Parsers
+~~~~~~~~~~~~~
+
+CREATE FULLTEXT INDEX in MySQL also supports a "WITH PARSER" option.  This
+is available using the keyword argument ``mysql_with_parser``::
+
+    Index(
+        'my_index', my_table.c.data,
+        mysql_prefix='FULLTEXT', mysql_with_parser="ngram")
+
+.. versionadded:: 1.3
+
+
 .. _mysql_foreign_keys:
 
 MySQL Foreign Keys
@@ -1276,6 +1289,10 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             columns = ', '.join(columns)
         text += '(%s)' % columns
 
+        parser = index.dialect_options['mysql']['with_parser']
+        if parser is not None:
+            text += " WITH PARSER %s" % (parser, )
+
         using = index.dialect_options['mysql']['using']
         if using is not None:
             text += " USING %s" % (preparer.quote(using))
@@ -1693,6 +1710,7 @@ class MySQLDialect(default.DefaultDialect):
             "using": None,
             "length": None,
             "prefix": None,
+            "with_parser": None
         })
     ]
 
@@ -2090,20 +2108,31 @@ class MySQLDialect(default.DefaultDialect):
             connection, table_name, schema, **kw)
 
         indexes = []
+
         for spec in parsed_state.keys:
+            dialect_options = {}
             unique = False
             flavor = spec['type']
             if flavor == 'PRIMARY':
                 continue
             if flavor == 'UNIQUE':
                 unique = True
-            elif flavor in (None, 'FULLTEXT', 'SPATIAL'):
+            elif flavor in ('FULLTEXT', 'SPATIAL'):
+                dialect_options["mysql_prefix"] = flavor
+            elif flavor is None:
                 pass
             else:
                 self.logger.info(
                     "Converting unknown KEY type %s to a plain KEY", flavor)
                 pass
+
+            if spec['parser']:
+                dialect_options['mysql_with_parser'] = spec['parser']
+
             index_d = {}
+            if dialect_options:
+                index_d["dialect_options"] = dialect_options
+
             index_d['name'] = spec['name']
             index_d['column_names'] = [s[0] for s in spec['columns']]
             index_d['unique'] = unique
diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py
index f9b8c8d3b..e15211044 100644
--- a/lib/sqlalchemy/dialects/mysql/reflection.py
+++ b/lib/sqlalchemy/dialects/mysql/reflection.py
@@ -77,6 +77,13 @@ class MySQLTableDefinitionParser(object):
             spec = m.groupdict()
             # convert columns into name, length pairs
             spec['columns'] = self._parse_keyexprs(spec['columns'])
+            if spec['version_sql']:
+                m2 = self._re_key_version_sql.match(spec['version_sql'])
+                if m2 and m2.groupdict()['parser']:
+                    spec['parser'] = m2.groupdict()['parser']
+            if spec['parser']:
+                spec['parser'] = self.preparer.unformat_identifiers(
+                    spec['parser'])[0]
             return 'key', spec
 
         # FOREIGN KEY CONSTRAINT
@@ -364,7 +371,7 @@ class MySQLTableDefinitionParser(object):
 
         # (PRIMARY|UNIQUE|FULLTEXT|SPATIAL) INDEX `name` (USING (BTREE|HASH))?
         # (`col` (ASC|DESC)?, `col` (ASC|DESC)?)
-        # KEY_BLOCK_SIZE size | WITH PARSER name
+        # KEY_BLOCK_SIZE size | WITH PARSER name  /*!50100 WITH PARSER name */
         self._re_key = _re_compile(
             r'  '
             r'(?:(?P<type>\S+) )?KEY'
@@ -375,10 +382,18 @@ class MySQLTableDefinitionParser(object):
             r'(?: +KEY_BLOCK_SIZE *[ =]? *(?P<keyblock>\S+))?'
             r'(?: +WITH PARSER +(?P<parser>\S+))?'
             r'(?: +COMMENT +(?P<comment>(\x27\x27|\x27([^\x27])*?\x27)+))?'
+            r'(?: +/\*(?P<version_sql>.+)\*/ +)?'
             r',?$'
             % quotes
         )
 
+        # https://forums.mysql.com/read.php?20,567102,567111#msg-567111
+        # It means if the MySQL version >= \d+, execute what's in the comment
+        self._re_key_version_sql = _re_compile(
+            r'\!\d+ '
+            r'(?: *WITH PARSER +(?P<parser>\S+) *)?'
+        )
+
         # CONSTRAINT `name` FOREIGN KEY (`local_col`)
         # REFERENCES `remote` (`remote_col`)
         # MATCH FULL | MATCH PARTIAL | MATCH SIMPLE