Re: [Sqlalchemy-tickets] [sqlalchemy] #2812: has_table/drop/etc. not really checking the "quote" flag for an uppercase engine and it probably cant

[sqlalchemy <mi...@zz...>]

#2812: has_table/drop/etc. not really checking the "quote" flag for an uppercase
engine and it probably cant
---------------------------+-------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.8.xx
     Component:  schema    |   Severity:  major - 1-3 hours
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+-------------------------------

Comment (by zzzeek):

 here's a quick proof of concept that's making me wonder, do we just create
 a "case sensitive" string object right as we create Table, Sequence,
 Column, Index, and just leave it at that?   we'd need to see how
 identifierpreparer and others feel about that.    this allows PG and
 Oracle to work:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/engine/default.py
 b/lib/sqlalchemy/engine/default.py
 index 90c7f59..f7dc3e0 100644
 --- a/lib/sqlalchemy/engine/default.py
 +++ b/lib/sqlalchemy/engine/default.py
 @@ -27,6 +27,13 @@ AUTOCOMMIT_REGEXP = re.compile(
              re.I | re.UNICODE)


 +class quoted_name(util.text_type):
 +    def lower(self):
 +        return self
 +
 +    def upper(self):
 +        return self
 +
  class DefaultDialect(interfaces.Dialect):
      """Default implementation of Dialect"""

 @@ -160,6 +167,12 @@ class DefaultDialect(interfaces.Dialect):
          self._encoder = codecs.getencoder(self.encoding)
          self._decoder =
 processors.to_unicode_processor_factory(self.encoding)

 +    def _as_quoted(self, name, quote):
 +        if quote:
 +            return quoted_name(name)
 +        else:
 +            return name
 +
      @util.memoized_property
      def _type_memos(self):
          return weakref.WeakKeyDictionary()
 diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py
 index a17c8ee..4d0de05 100644
 --- a/lib/sqlalchemy/sql/ddl.py
 +++ b/lib/sqlalchemy/sql/ddl.py
 @@ -667,7 +667,9 @@ class SchemaGenerator(DDLBase):
              self.dialect.validate_identifier(table.schema)
          return not self.checkfirst or \
                  not self.dialect.has_table(self.connection,
 -                                    table.name, schema=table.schema)
 +                                    self.dialect._as_quoted(table.name,
 table.quote),
 +
 schema=self.dialect._as_quoted(table.schema, table.quote_schema)
 +                                  )

      def _can_create_sequence(self, sequence):
          return self.dialect.supports_sequences and \
 @@ -678,8 +680,9 @@ class SchemaGenerator(DDLBase):
                          not self.checkfirst or
                          not self.dialect.has_sequence(
                                  self.connection,
 -                                sequence.name,
 -                                schema=sequence.schema)
 +                                self.dialect._as_quoted(sequence.name,
 sequence.quote),
 +
 schema=self.dialect._as_quoted(sequence.schema, sequence.quote_schema)
 +                            )
                       )
              )

 @@ -787,18 +790,23 @@ class SchemaDropper(DDLBase):
          self.dialect.validate_identifier(table.name)
          if table.schema:
              self.dialect.validate_identifier(table.schema)
 -        return not self.checkfirst or
 self.dialect.has_table(self.connection,
 -                                            table.name,
 schema=table.schema)
 +        return not self.checkfirst or \
 +              self.dialect.has_table(self.connection,
 +                        self.dialect._as_quoted(table.name, table.quote),
 +                        schema=self.dialect._as_quoted(table.schema,
 table.quote_schema)
 +                      )

      def _can_drop_sequence(self, sequence):
          return self.dialect.supports_sequences and \
              ((not self.dialect.sequences_optional or
                   not sequence.optional) and
                  (not self.checkfirst or
 -                self.dialect.has_sequence(
 -                                self.connection,
 -                                sequence.name,
 -                                schema=sequence.schema))
 +                    self.dialect.has_sequence(
 +                        self.connection,
 +                        self.dialect._as_quoted(sequence.name,
 sequence.quote),
 +                        schema=self.dialect._as_quoted(sequence.schema,
 sequence.quote_schema)
 +                    )
 +                )
              )

      def visit_index(self, index):
 diff --git a/test/sql/test_quote.py b/test/sql/test_quote.py
 index c92f1ac..ae9602e 100644
 --- a/test/sql/test_quote.py
 +++ b/test/sql/test_quote.py
 @@ -61,6 +61,42 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):

          assert 'MixedCase' in t2.c

 +    @testing.provide_metadata
 +    def test_has_table_case_sensitive(self):
 +        if testing.db.dialect.requires_name_normalize:
 +            testing.db.execute("CREATE TABLE TAB1 (id INTEGER)")
 +            testing.db.execute('CREATE TABLE "tab2" (id INTEGER)')
 +            testing.db.execute('CREATE TABLE "TAB3" (id INTEGER)')
 +            testing.db.execute('CREATE TABLE "TAB4" (id INTEGER)')
 +        else:
 +            testing.db.execute("CREATE TABLE tab1 (id INTEGER)")
 +            testing.db.execute('CREATE TABLE "tab2" (id INTEGER)')
 +            testing.db.execute('CREATE TABLE "TAB3" (id INTEGER)')
 +            testing.db.execute('CREATE TABLE "TAB4" (id INTEGER)')
 +
 +        t1 = Table('tab1', self.metadata,
 +                        Column('id', Integer, primary_key=True),
 +                        #quote=False
 +                        )
 +        t2 = Table('tab2', self.metadata,
 +                        Column('id', Integer, primary_key=True),
 +                         quote=True
 +                         )
 +        t3 = Table('TAB3', self.metadata,
 +                        Column('id', Integer, primary_key=True),
 +                        #quote=False
 +                        )
 +        t4 = Table('TAB4', self.metadata,
 +                        Column('id', Integer, primary_key=True),
 +                        quote=True)
 +
 +        quoted_rule = testing.db.dialect._as_quoted
 +        assert testing.db.has_table(quoted_rule(t1.name, t1.quote))
 +        assert testing.db.has_table(quoted_rule(t2.name, t2.quote))
 +        assert testing.db.has_table(quoted_rule(t3.name, t3.quote))
 +        assert testing.db.has_table(quoted_rule(t4.name, t4.quote))
 +
 +
      def test_basic(self):
          table1.insert().execute(
              {'lowercase': 1, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2812#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python