[Sqlalchemy-commits] sqlalchemy: - The argument to "ESCAPE" of a LIKE operator or sim...
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] sqlalchemy: - The argument to "ESCAPE" of a LIKE operator or sim...
|
From: <co...@sq...> - 2010-06-24 16:19:43
|
details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/cb6481ba499d changeset: 6647:cb6481ba499d user: zzzeek date: Thu Jun 24 12:19:15 2010 -0400 description: - The argument to "ESCAPE" of a LIKE operator or similar is passed through render_literal_value(), which may implement escaping of backslashes. [ticket:1400] - Postgresql render_literal_value() is overridden which escapes backslashes, currently applies to the ESCAPE clause of LIKE and similar expressions. Ultimately this will have to detect the value of "standard_conforming_strings" for full behavior. [ticket:1400] - MySQL render_literal_value() is overridden which escapes backslashes, currently applies to the ESCAPE clause of LIKE and similar expressions. This behavior is derived from detecting the value of NO_BACKSLASH_ESCAPES. [ticket:1400] diffstat: CHANGES | 21 +++++++++++ lib/sqlalchemy/dialects/mysql/base.py | 21 ++++++++++- lib/sqlalchemy/dialects/postgresql/base.py | 18 +++++++++- lib/sqlalchemy/sql/compiler.py | 16 ++++++-- lib/sqlalchemy/types.py | 1 + test/dialect/test_mysql.py | 53 ++++++++++++++++++++++++++++++ test/sql/test_query.py | 16 ++++++-- 7 files changed, 133 insertions(+), 13 deletions(-) diffs (290 lines): diff -r 484c62976755 -r cb6481ba499d CHANGES --- a/CHANGES Sat Jun 19 14:08:20 2010 -0400 +++ b/CHANGES Thu Jun 24 12:19:15 2010 -0400 @@ -59,6 +59,10 @@ is emitted once as per the warning filter settings, and large string values don't pollute the output. [ticket:1822] + + - The argument to "ESCAPE" of a LIKE operator or similar + is passed through render_literal_value(), which may + implement escaping of backslashes. [ticket:1400] - Fixed bug in Enum type which blew away native_enum flag when used with TypeDecorators or other adaption @@ -78,11 +82,28 @@ among others, fixes [ticket:1829] regarding declarative mixins +- postgresql + - render_literal_value() is overridden which escapes + backslashes, currently applies to the ESCAPE clause + of LIKE and similar expressions. + Ultimately this will have to detect the value of + "standard_conforming_strings" for full behavior. + [ticket:1400] + - mysql - MySQL dialect doesn't emit CAST() for MySQL version detected < 4.0.2. This allows the unicode check on connect to proceed. [ticket:1826] + - MySQL dialect now detects NO_BACKSLASH_ESCAPES sql + mode, in addition to ANSI_QUOTES. + + - render_literal_value() is overridden which escapes + backslashes, currently applies to the ESCAPE clause + of LIKE and similar expressions. This behavior + is derived from detecting the value of + NO_BACKSLASH_ESCAPES. [ticket:1400] + - oracle: - Fixed ora-8 compatibility flags such that they don't cache a stale value from before the first diff -r 484c62976755 -r cb6481ba499d lib/sqlalchemy/dialects/mysql/base.py --- a/lib/sqlalchemy/dialects/mysql/base.py Sat Jun 19 14:08:20 2010 -0400 +++ b/lib/sqlalchemy/dialects/mysql/base.py Thu Jun 24 12:19:15 2010 -0400 @@ -1196,6 +1196,12 @@ return 'CAST(%s AS %s)' % (self.process(cast.clause), type_) + def render_literal_value(self, value, type_): + value = super(MySQLCompiler, self).render_literal_value(value, type_) + if self.dialect._backslash_escapes: + value = value.replace('\\', '\\\\') + return value + def get_select_precolumns(self, select): if isinstance(select._distinct, basestring): return select._distinct.upper() + " " @@ -1639,6 +1645,12 @@ ischema_names = ischema_names preparer = MySQLIdentifierPreparer + # default SQL compilation settings - + # these are modified upon initialize(), + # i.e. first connect + _backslash_escapes = True + _server_ansiquotes = False + def __init__(self, use_ansiquotes=None, **kwargs): default.DefaultDialect.__init__(self, **kwargs) @@ -1760,7 +1772,7 @@ self._connection_charset = self._detect_charset(connection) self._server_casing = self._detect_casing(connection) self._server_collations = self._detect_collations(connection) - self._server_ansiquotes = self._detect_ansiquotes(connection) + self._detect_ansiquotes(connection) if self._server_ansiquotes: # if ansiquotes == True, build a new IdentifierPreparer # with the new setting @@ -2019,8 +2031,11 @@ mode_no = int(mode) mode = (mode_no | 4 == mode_no) and 'ANSI_QUOTES' or '' - return 'ANSI_QUOTES' in mode - + self._server_ansiquotes = 'ANSI_QUOTES' in mode + + # as of MySQL 5.0.1 + self._backslash_escapes = 'NO_BACKSLASH_ESCAPES' not in mode + def _show_create_table(self, connection, table, charset=None, full_name=None): """Run SHOW CREATE TABLE for a ``Table``.""" diff -r 484c62976755 -r cb6481ba499d lib/sqlalchemy/dialects/postgresql/base.py --- a/lib/sqlalchemy/dialects/postgresql/base.py Sat Jun 19 14:08:20 2010 -0400 +++ b/lib/sqlalchemy/dialects/postgresql/base.py Thu Jun 24 12:19:15 2010 -0400 @@ -324,12 +324,23 @@ def visit_ilike_op(self, binary, **kw): escape = binary.modifiers.get("escape", None) return '%s ILIKE %s' % (self.process(binary.left), self.process(binary.right)) \ - + (escape and ' ESCAPE \'%s\'' % escape or '') + + (escape and + (' ESCAPE ' + self.render_literal_value(escape, None)) + or '') def visit_notilike_op(self, binary, **kw): escape = binary.modifiers.get("escape", None) return '%s NOT ILIKE %s' % (self.process(binary.left), self.process(binary.right)) \ - + (escape and ' ESCAPE \'%s\'' % escape or '') + + (escape and + (' ESCAPE ' + self.render_literal_value(escape, None)) + or '') + + def render_literal_value(self, value, type_): + value = super(PGCompiler, self).render_literal_value(value, type_) + # TODO: need to inspect "standard_conforming_strings" + if self.dialect._backslash_escapes: + value = value.replace('\\', '\\\\') + return value def visit_sequence(self, seq): if seq.optional: @@ -625,6 +636,9 @@ inspector = PGInspector isolation_level = None + # TODO: need to inspect "standard_conforming_strings" + _backslash_escapes = True + def __init__(self, isolation_level=None, **kwargs): default.DefaultDialect.__init__(self, **kwargs) self.isolation_level = isolation_level diff -r 484c62976755 -r cb6481ba499d lib/sqlalchemy/sql/compiler.py --- a/lib/sqlalchemy/sql/compiler.py Sat Jun 19 14:08:20 2010 -0400 +++ b/lib/sqlalchemy/sql/compiler.py Thu Jun 24 12:19:15 2010 -0400 @@ -494,28 +494,36 @@ return '%s LIKE %s' % ( self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and ' ESCAPE \'%s\'' % escape or '') + + (escape and + (' ESCAPE ' + self.render_literal_value(escape, None)) + or '') def visit_notlike_op(self, binary, **kw): escape = binary.modifiers.get("escape", None) return '%s NOT LIKE %s' % ( self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and ' ESCAPE \'%s\'' % escape or '') + + (escape and + (' ESCAPE ' + self.render_literal_value(escape, None)) + or '') def visit_ilike_op(self, binary, **kw): escape = binary.modifiers.get("escape", None) return 'lower(%s) LIKE lower(%s)' % ( self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and ' ESCAPE \'%s\'' % escape or '') + + (escape and + (' ESCAPE ' + self.render_literal_value(escape, None)) + or '') def visit_notilike_op(self, binary, **kw): escape = binary.modifiers.get("escape", None) return 'lower(%s) NOT LIKE lower(%s)' % ( self.process(binary.left, **kw), self.process(binary.right, **kw)) \ - + (escape and ' ESCAPE \'%s\'' % escape or '') + + (escape and + (' ESCAPE ' + self.render_literal_value(escape, None)) + or '') def _operator_dispatch(self, operator, element, fn, **kw): if util.callable(operator): diff -r 484c62976755 -r cb6481ba499d lib/sqlalchemy/types.py --- a/lib/sqlalchemy/types.py Sat Jun 19 14:08:20 2010 -0400 +++ b/lib/sqlalchemy/types.py Thu Jun 24 12:19:15 2010 -0400 @@ -1796,6 +1796,7 @@ NULLTYPE = NullType() BOOLEANTYPE = Boolean() +STRINGTYPE = String() # using VARCHAR/NCHAR so that we dont get the genericized "String" # type which usually resolves to TEXT/CLOB diff -r 484c62976755 -r cb6481ba499d test/dialect/test_mysql.py --- a/test/dialect/test_mysql.py Sat Jun 19 14:08:20 2010 -0400 +++ b/test/dialect/test_mysql.py Thu Jun 24 12:19:15 2010 -0400 @@ -1017,7 +1017,21 @@ eq_( gen(True, ['high_priority', sql.text('sql_cache')]), 'SELECT high_priority sql_cache DISTINCT q') + + def test_backslash_escaping(self): + self.assert_compile( + sql.column('foo').like('bar', escape='\\'), + "foo LIKE %s ESCAPE '\\\\'" + ) + dialect = mysql.dialect() + dialect._backslash_escapes=False + self.assert_compile( + sql.column('foo').like('bar', escape='\\'), + "foo LIKE %s ESCAPE '\\'", + dialect=dialect + ) + def test_limit(self): t = sql.table('t', sql.column('col1'), sql.column('col2')) @@ -1221,7 +1235,46 @@ ")ENGINE=InnoDB" ) +class SQLModeDetectionTest(TestBase): + __only_on__ = 'mysql' + + def _options(self, modes): + class SetOptions(object): + def first_connect(self, con, record): + self.connect(con, record) + def connect(self, con, record): + cursor = con.cursor() + cursor.execute("set sql_mode='%s'" % (",".join(modes))) + return engines.testing_engine(options={"listeners":[SetOptions()]}) + + def test_backslash_escapes(self): + engine = self._options(['NO_BACKSLASH_ESCAPES']) + c = engine.connect() + assert not engine.dialect._backslash_escapes + c.close() + engine.dispose() + engine = self._options([]) + c = engine.connect() + assert engine.dialect._backslash_escapes + c.close() + engine.dispose() + + def test_ansi_quotes(self): + engine = self._options(['ANSI_QUOTES']) + c = engine.connect() + assert engine.dialect._server_ansiquotes + c.close() + engine.dispose() + + def test_combination(self): + engine = self._options(['ANSI_QUOTES,NO_BACKSLASH_ESCAPES']) + c = engine.connect() + assert engine.dialect._server_ansiquotes + assert not engine.dialect._backslash_escapes + c.close() + engine.dispose() + class RawReflectionTest(TestBase): def setup(self): dialect = mysql.dialect() diff -r 484c62976755 -r cb6481ba499d test/sql/test_query.py --- a/test/sql/test_query.py Sat Jun 19 14:08:20 2010 -0400 +++ b/test/sql/test_query.py Thu Jun 24 12:19:15 2010 -0400 @@ -376,13 +376,21 @@ ) for expr, result in ( - (select([users.c.user_id]).where(users.c.user_name.startswith('apple')), [(1,)]), - (select([users.c.user_id]).where(users.c.user_name.contains('i % t')), [(5,)]), - (select([users.c.user_id]).where(users.c.user_name.endswith('anas')), [(3,)]), + (select([users.c.user_id]).\ + where(users.c.user_name.startswith('apple')), [(1,)]), + (select([users.c.user_id]).\ + where(users.c.user_name.contains('i % t')), [(5,)]), + (select([users.c.user_id]).\ + where( + users.c.user_name.endswith('anas') + ), [(3,)]), + (select([users.c.user_id]).\ + where( + users.c.user_name.contains('i % t', escape='\\') + ), [(5,)]), ): eq_(expr.execute().fetchall(), result) - @testing.fails_on("firebird", "see dialect.test_firebird:MiscTest.test_percents_in_text") @testing.fails_on("oracle", "neither % nor %% are accepted") @testing.fails_on("+pg8000", "can't interpret result column from '%%'") |
