From 80f48f250b49f87a67c83ea71b0635d6645f26c3 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 4 Feb 2007 03:43:22 +0000 Subject: [PATCH] - added literal_column() to specify a column clause that should not undergo any quoting - straight text sent to select() added as literal_column - fix for issue in [ticket:450] --- lib/sqlalchemy/ansisql.py | 4 +--- lib/sqlalchemy/databases/oracle.py | 4 ++-- lib/sqlalchemy/schema.py | 6 +++--- lib/sqlalchemy/sql.py | 15 ++++++++++----- test/sql/quote.py | 5 ++--- 5 files changed, 18 insertions(+), 16 deletions(-) diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py index 40ea5b00ac..63ee5f7fdc 100644 --- a/lib/sqlalchemy/ansisql.py +++ b/lib/sqlalchemy/ansisql.py @@ -932,9 +932,7 @@ class ANSIIdentifierPreparer(object): def format_column(self, column, use_table=False): """Prepare a quoted column name """ - # TODO: isinstance alert ! get ColumnClause and Column to better - # differentiate themselves - if isinstance(column, schema.SchemaItem): + if not getattr(column, 'is_literal', False): if use_table: return self.format_table(column.table, use_schema=False) + "." + self.__generic_obj_format(column, column.name) else: diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py index 8730ecdb1e..7221d56620 100644 --- a/lib/sqlalchemy/databases/oracle.py +++ b/lib/sqlalchemy/databases/oracle.py @@ -394,7 +394,7 @@ class OracleCompiler(ansisql.ANSICompiler): orderby = self.strings[orderby] class SelectVisitor(sql.ClauseVisitor): def visit_select(self, select): - select.append_column(sql.column("ROW_NUMBER() OVER (ORDER BY %s)" % orderby).label("ora_rn")) + select.append_column(sql.literal_column("ROW_NUMBER() OVER (ORDER BY %s)" % orderby).label("ora_rn")) select.accept_visitor(SelectVisitor()) limitselect = sql.select([c for c in select.c if c.key!='ora_rn']) if select.offset is not None: @@ -429,7 +429,7 @@ class OracleCompiler(ansisql.ANSICompiler): orderby = select.oid_column orderby.accept_visitor(self) orderby = self.strings[orderby] - select.append_column(sql.column("ROW_NUMBER() OVER (ORDER BY %s)" % orderby).label("ora_rn")) + select.append_column(sql.literal_column("ROW_NUMBER() OVER (ORDER BY %s)" % orderby).label("ora_rn")) limitselect = sql.select([c for c in select.c if c.key!='ora_rn']) if select.offset is not None: limitselect.append_whereclause("ora_rn>%d" % select.offset) diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index 44ba85453a..ae712252ba 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -193,9 +193,9 @@ class Table(SchemaItem, sql.TableClause): quote_schema=False : indicates that the Namespace identifier must be properly escaped and quoted before being sent to the database. This flag overrides all other quoting behavior. - case_sensitive=True : indicates quoting should be used if the identifier needs it. + case_sensitive=True : indicates quoting should be used if the identifier contains mixed case. - case_sensitive_schema=True : indicates quoting should be used if the identifier needs it. + case_sensitive_schema=True : indicates quoting should be used if the identifier contains mixed case. """ super(Table, self).__init__(name) self._metadata = metadata @@ -362,7 +362,7 @@ class Column(SchemaItem, sql._ColumnClause): to the database. This flag should normally not be required as dialects can auto-detect conditions where quoting is required. - case_sensitive=True : indicates quoting should be used if the identifier needs it. + case_sensitive=True : indicates quoting should be used if the identifier contains mixed case. """ name = str(name) # in case of incoming unicode super(Column, self).__init__(name, None, type) diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index a59b91b4c0..259ef00b67 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -10,7 +10,7 @@ from sqlalchemy import types as sqltypes import string, re, random, sets -__all__ = ['text', 'table', 'column', 'func', 'select', 'update', 'insert', 'delete', 'join', 'and_', 'or_', 'not_', 'between_', 'case', 'cast', 'union', 'union_all', 'except_', 'except_all', 'intersect', 'intersect_all', 'null', 'desc', 'asc', 'outerjoin', 'alias', 'subquery', 'literal', 'bindparam', 'exists', 'extract','AbstractDialect', 'ClauseParameters', 'ClauseVisitor', 'Executor', 'Compiled', 'ClauseElement', 'ColumnElement', 'ColumnCollection', 'FromClause', 'TableClause', 'Select', 'Alias', 'CompoundSelect','Join', 'Selectable'] +__all__ = ['text', 'table', 'column', 'literal_column', 'func', 'select', 'update', 'insert', 'delete', 'join', 'and_', 'or_', 'not_', 'between_', 'case', 'cast', 'union', 'union_all', 'except_', 'except_all', 'intersect', 'intersect_all', 'null', 'desc', 'asc', 'outerjoin', 'alias', 'subquery', 'literal', 'bindparam', 'exists', 'extract','AbstractDialect', 'ClauseParameters', 'ClauseVisitor', 'Executor', 'Compiled', 'ClauseElement', 'ColumnElement', 'ColumnCollection', 'FromClause', 'TableClause', 'Select', 'Alias', 'CompoundSelect','Join', 'Selectable'] def desc(column): """return a descending ORDER BY clause element, e.g.: @@ -220,10 +220,14 @@ def label(name, obj): return _Label(name, obj) def column(text, table=None, type=None, **kwargs): - """returns a textual column clause, relative to a table. this is also the primitive version of + """return a textual column clause, relative to a table. this is also the primitive version of a schema.Column which is a subclass. """ return _ColumnClause(text, table, type, **kwargs) +def literal_column(text, table=None, type=None, **kwargs): + """return a textual column clause with the 'literal' flag set. this column will not be quoted""" + return _ColumnClause(text, table, type, is_literal=True, **kwargs) + def table(name, *columns): """returns a table clause. this is a primitive version of the schema.Table object, which is a subclass of this object.""" @@ -1251,13 +1255,14 @@ legal_characters = util.Set(string.ascii_letters + string.digits + '_') class _ColumnClause(ColumnElement): """represents a textual column clause in a SQL statement. May or may not be bound to an underlying Selectable.""" - def __init__(self, text, selectable=None, type=None, _is_oid=False, case_sensitive=True): + def __init__(self, text, selectable=None, type=None, _is_oid=False, case_sensitive=True, is_literal=False): self.key = self.name = text self.table = selectable self.type = sqltypes.to_instance(type) self._is_oid = _is_oid self.__label = None - self.case_sensitive = False #text.isalpha() and not text.islower() + self.case_sensitive = case_sensitive + self.is_literal = is_literal def _get_label(self): if self.__label is None: if self.table is not None and self.table.named_with_column(): @@ -1524,7 +1529,7 @@ class Select(_SelectBaseMixin, FromClause): def append_column(self, column): if _is_literal(column): - column = _ColumnClause(str(column), self) + column = literal_column(str(column), table=self) self._raw_columns.append(column) diff --git a/test/sql/quote.py b/test/sql/quote.py index 403ae2d426..bd8b206d68 100644 --- a/test/sql/quote.py +++ b/test/sql/quote.py @@ -98,12 +98,11 @@ class QuoteTest(PersistTest): assert str(select([x.c.ImATable_col1])) == '''SELECT "SomeAlias"."ImATable_col1" \nFROM (SELECT "ImATable".col1 AS "ImATable_col1" \nFROM "ImATable") AS "SomeAlias"''' # note that 'foo' and 'FooCol' are literals already quoted - x = select([sql.column("'foo'").label("somelabel")], from_obj=[table]).alias("AnAlias") + x = select([sql.literal_column("'foo'").label("somelabel")], from_obj=[table]).alias("AnAlias") x = x.select() - #print x assert str(x) == '''SELECT "AnAlias".somelabel \nFROM (SELECT 'foo' AS somelabel \nFROM "ImATable") AS "AnAlias"''' - x = select([sql.column("'FooCol'").label("SomeLabel")], from_obj=[table]) + x = select([sql.literal_column("'FooCol'").label("SomeLabel")], from_obj=[table]) x = x.select() assert str(x) == '''SELECT "SomeLabel" \nFROM (SELECT 'FooCol' AS "SomeLabel" \nFROM "ImATable")''' -- 2.47.2