From 1f407c2a47150ddd550d3167015a9315e018d7bc Mon Sep 17 00:00:00 2001 From: Joseph Schorr Date: Tue, 17 Jan 2017 10:02:17 -0500 Subject: Add support for prefixes on CREATE INDEX statements in MySQL Added a new parameter ``mysql_prefix`` supported by the :class:`.Index` construct, allows specification of MySQL-specific prefixes such as "FULLTEXT". Pull request courtesy Joseph Schorr. Change-Id: I5a21fa466fdfd4d9e39e1fb4ecec1eab93b92c36 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/339 --- doc/build/changelog/changelog_11.rst | 7 +++++++ lib/sqlalchemy/dialects/mysql/base.py | 25 +++++++++++++++++++++++++ test/dialect/mysql/test_compiler.py | 8 ++++++++ 3 files changed, 40 insertions(+) diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index ead292570..31df8720f 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -21,6 +21,13 @@ .. changelog:: :version: 1.1.5 + .. change:: mysql_index_prefix + :tags: feature, mysql + + Added a new parameter ``mysql_prefix`` supported by the :class:`.Index` + construct, allows specification of MySQL-specific prefixes such as + "FULLTEXT". Pull request courtesy Joseph Schorr. + .. change:: 3854 :tags: bug, orm :tickets: 3854 diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 1654ff327..8b0d00a63 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -391,6 +391,25 @@ BLOB. .. versionadded:: 0.8.2 ``mysql_length`` may now be specified as a dictionary for use with composite indexes. +Index Prefixes +~~~~~~~~~~~~~~ + +MySQL storage engines permit you to specify an index prefix when creating +an index. SQLAlchemy provides this feature via the +``mysql_prefix`` parameter on :class:`.Index`:: + + Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT') + +The value passed to the keyword argument will be simply passed through to the +underlying CREATE INDEX, so it *must* be a valid index prefix for your MySQL +storage engine. + +.. versionadded:: 1.1.5 + +.. seealso:: + + `CREATE INDEX `_ - MySQL documentation + Index Types ~~~~~~~~~~~~~ @@ -1039,6 +1058,11 @@ class MySQLDDLCompiler(compiler.DDLCompiler): text = "CREATE " if index.unique: text += "UNIQUE " + + index_prefix = index.kwargs.get('mysql_prefix', None) + if index_prefix: + text += index_prefix + ' ' + text += "INDEX %s ON %s " % (name, table) length = index.dialect_options['mysql']['length'] @@ -1468,6 +1492,7 @@ class MySQLDialect(default.DefaultDialect): (sa_schema.Index, { "using": None, "length": None, + "prefix": None, }) ] diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py index 88f9235e1..b72507104 100644 --- a/test/dialect/mysql/test_compiler.py +++ b/test/dialect/mysql/test_compiler.py @@ -40,6 +40,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile(schema.CreateIndex(idx), 'CREATE INDEX test_idx1 ON testtbl (data)') + def test_create_index_with_prefix(self): + m = MetaData() + tbl = Table('testtbl', m, Column('data', String(255))) + idx = Index('test_idx1', tbl.c.data, mysql_length=10, mysql_prefix='FULLTEXT') + + self.assert_compile(schema.CreateIndex(idx), + 'CREATE FULLTEXT INDEX test_idx1 ON testtbl (data(10))') + def test_create_index_with_length(self): m = MetaData() tbl = Table('testtbl', m, Column('data', String(255))) -- cgit v1.2.1 From bd6ba3ac8274381e578c8e6c8018a7878fe94208 Mon Sep 17 00:00:00 2001 From: Lele Long Date: Sat, 3 Dec 2016 13:10:07 -0500 Subject: Parse (but don't record) COMMENT portion of MySQL table key The MySQL dialect now will not warn when a reflected column has a "COMMENT" keyword on it, but note however the comment is not yet reflected; this is on the roadmap for a future release. Pull request courtesy Lele Long. Fixes: #3867 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/324 Change-Id: I869e29aba6766d0eda1e59af09a3e8e3748a3942 --- doc/build/changelog/changelog_11.rst | 9 +++++++++ lib/sqlalchemy/dialects/mysql/reflection.py | 1 + test/dialect/mysql/test_reflection.py | 12 ++++++++++++ 3 files changed, 22 insertions(+) diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index ead292570..4c3cc949e 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -139,6 +139,15 @@ :class:`.Engine`, concealing the URL password. Pull request courtesy Valery Yundin. + .. change:: 3867 + :tags: bug, mysql + :tickets: 3867 + + The MySQL dialect now will not warn when a reflected column has a + "COMMENT" keyword on it, but note however the comment is not yet + reflected; this is on the roadmap for a future release. Pull request + courtesy Lele Long. + .. change:: pg_timestamp_zero_prec :tags: bug, postgresql diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py index d020fb296..f5f09b80b 100644 --- a/lib/sqlalchemy/dialects/mysql/reflection.py +++ b/lib/sqlalchemy/dialects/mysql/reflection.py @@ -359,6 +359,7 @@ class MySQLTableDefinitionParser(object): r'(?: +USING +(?P\S+))?' r'(?: +KEY_BLOCK_SIZE *[ =]? *(?P\S+))?' r'(?: +WITH PARSER +(?P\S+))?' + r'(?: +COMMENT +(?P(\x27\x27|\x27([^\x27])*?\x27)+))?' r',?$' % quotes ) diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py index ddf158167..2ccb299bc 100644 --- a/test/dialect/mysql/test_reflection.py +++ b/test/dialect/mysql/test_reflection.py @@ -552,6 +552,18 @@ class RawReflectionTest(fixtures.TestBase): ' PRIMARY KEY (`id`) USING BTREE KEY_BLOCK_SIZE = 16') assert not regex.match( ' PRIMARY KEY (`id`) USING BTREE KEY_BLOCK_SIZE = = 16') + assert regex.match( + " KEY (`id`) USING BTREE COMMENT 'comment'") + # `SHOW CREATE TABLE` returns COMMENT '''comment' + # after creating table with COMMENT '\'comment' + assert regex.match( + " KEY (`id`) USING BTREE COMMENT '''comment'") + assert regex.match( + " KEY (`id`) USING BTREE COMMENT 'comment'''") + assert regex.match( + " KEY (`id`) USING BTREE COMMENT 'prefix''suffix'") + assert regex.match( + " KEY (`id`) USING BTREE COMMENT 'prefix''text''suffix'") def test_fk_reflection(self): regex = self.parser._re_constraint -- cgit v1.2.1