second pass: increasing coverage, and commenting the "why" certain things are being tested (when I know) b/c it wasn't initially clear to me why case was being toggled everywhere, etc.

1 files changed, 357 insertions, 140 deletions

diff --git a/test/sql/test_quote.py b/test/sql/test_quote.py
index 625c4ef5c..766d9d485 100644
--- a/test/sql/test_quote.py
+++ b/test/sql/test_quote.py
@@ -39,6 +39,14 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
         table1.drop()
         table2.drop()
 
+    def test_reflect(self):
+        meta2 = MetaData(testing.db)
+        t2 = Table('WorstCase1', meta2, autoload=True, quote=True)
+        assert 'lowercase' in t2.c
+        assert 'UPPERCASE' in t2.c
+        assert 'MixedCase' in t2.c
+        assert 'ASC' in t2.c
+
     def test_basic(self):
         table1.insert().execute(
             {'lowercase': 1, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
@@ -66,7 +74,7 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
         result = select(columns).execute().fetchall()
         assert(result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)])
 
-    def test_labels(self):
+    def test_basic_use_labels(self):
         table1.insert().execute(
             {'lowercase': 1, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
             {'lowercase': 2, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
@@ -93,38 +101,214 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
         result = select(columns, use_labels=True).execute().fetchall()
         assert(result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)])
 
-    def test_numeric(self):
+    @testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.requires.subqueries
+    def test_labels(self):
+        """test the quoting of labels.
+
+        If labels aren't quoted, a query in postgresql in particular will
+        fail since it produces:
+
+        SELECT
+            LaLa.lowercase, LaLa."UPPERCASE", LaLa."MixedCase", LaLa."ASC"
+        FROM (
+            SELECT DISTINCT
+                "WorstCase1".lowercase AS lowercase,
+                "WorstCase1"."UPPERCASE" AS UPPERCASE,
+                "WorstCase1"."MixedCase" AS MixedCase,
+                "WorstCase1"."ASC" AS ASC
+            FROM "WorstCase1"
+        ) AS LaLa
+
+        where the "UPPERCASE" column of "LaLa" doesn't exist.
+        """
+
+        x = table1.select(distinct=True).alias('LaLa').select().scalar()
+        self.assert_compile(
+            table1.select(distinct=True).alias('LaLa').select(),
+            'SELECT '
+                '"LaLa".lowercase, '
+                '"LaLa"."UPPERCASE", '
+                '"LaLa"."MixedCase", '
+                '"LaLa"."ASC" '
+            'FROM ('
+                'SELECT DISTINCT '
+                    '"WorstCase1".lowercase AS lowercase, '
+                    '"WorstCase1"."UPPERCASE" AS "UPPERCASE", '
+                    '"WorstCase1"."MixedCase" AS "MixedCase", '
+                    '"WorstCase1"."ASC" AS "ASC" '
+                'FROM "WorstCase1"'
+            ') AS "LaLa"'
+        )
+
+    def test_lower_case_names(self):
+        # Create table with quote defaults
         metadata = MetaData()
-        t1 = Table('35table', metadata,
-            Column('25column', Integer))
-        result = 'CREATE TABLE "35table" ("25column" INTEGER)'
-        self.assert_compile(schema.CreateTable(t1), result)
+        t1 = Table('t1', metadata,
+            Column('col1', Integer),
+            schema='foo')
 
-    def test_reflect(self):
-        meta2 = MetaData(testing.db)
-        t2 = Table('WorstCase2', meta2, autoload=True, quote=True)
-        assert 'MixedCase' in t2.c
+        # Note that the names are not quoted b/c they are all lower case
+        result = 'CREATE TABLE foo.t1 (col1 INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
 
-    def test_quote_flag(self):
+        # Create the same table with quotes set to True now
         metadata = MetaData()
         t1 = Table('t1', metadata,
             Column('col1', Integer, quote=True),
-            quote=True, schema="foo", quote_schema=True)
+            schema='foo', quote=True, quote_schema=True)
+
+        # Note that the names are now quoted
+        result = 'CREATE TABLE "foo"."t1" ("col1" INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+    def test_upper_case_names(self):
+        # Create table with quote defaults
+        metadata = MetaData()
+        t1 = Table('TABLE1', metadata,
+            Column('COL1', Integer),
+            schema='FOO')
+
+        # Note that the names are quoted b/c they are not all lower case
+        result = 'CREATE TABLE "FOO"."TABLE1" ("COL1" INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+        # Create the same table with quotes set to False now
+        metadata = MetaData()
+        t1 = Table('TABLE1', metadata,
+            Column('COL1', Integer, quote=False),
+            schema='FOO', quote=False, quote_schema=False)
+
+        # Note that the names are now unquoted
+        result = 'CREATE TABLE FOO.TABLE1 (COL1 INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+    def test_mixed_case_names(self):
+        # Create table with quote defaults
+        metadata = MetaData()
+        t1 = Table('Table1', metadata,
+            Column('Col1', Integer),
+            schema='Foo')
+
+        # Note that the names are quoted b/c they are not all lower case
+        result = 'CREATE TABLE "Foo"."Table1" ("Col1" INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
 
-        self.assert_compile(t1.select(),
+        # Create the same table with quotes set to False now
+        metadata = MetaData()
+        t1 = Table('Table1', metadata,
+            Column('Col1', Integer, quote=False),
+            schema='Foo', quote=False, quote_schema=False)
+
+        # Note that the names are now unquoted
+        result = 'CREATE TABLE Foo.Table1 (Col1 INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+    def test_numeric_initial_char(self):
+        # Create table with quote defaults
+        metadata = MetaData()
+        t1 = Table('35table', metadata,
+            Column('25column', Integer),
+            schema='45schema')
+
+        # Note that the names are quoted b/c the initial
+        # character is in ['$','0', '1' ... '9']
+        result = 'CREATE TABLE "45schema"."35table" ("25column" INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+        # Create the same table with quotes set to False now
+        metadata = MetaData()
+        t1 = Table('35table', metadata,
+            Column('25column', Integer, quote=False),
+            schema='45schema', quote=False, quote_schema=False)
+
+        # Note that the names are now unquoted
+        result = 'CREATE TABLE 45schema.35table (25column INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+    def test_illegal_initial_char(self):
+        # Create table with quote defaults
+        metadata = MetaData()
+        t1 = Table('$table', metadata,
+            Column('$column', Integer),
+            schema='$schema')
+
+        # Note that the names are quoted b/c the initial
+        # character is in ['$','0', '1' ... '9']
+        result = 'CREATE TABLE "$schema"."$table" ("$column" INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+        # Create the same table with quotes set to False now
+        metadata = MetaData()
+        t1 = Table('$table', metadata,
+            Column('$column', Integer, quote=False),
+            schema='$schema', quote=False, quote_schema=False)
+
+        # Note that the names are now unquoted
+        result = 'CREATE TABLE $schema.$table ($column INTEGER)'
+        self.assert_compile(schema.CreateTable(t1), result)
+
+    def test_reserved_words(self):
+        # Create table with quote defaults
+        metadata = MetaData()
+        table = Table('foreign', metadata,
+            Column('col1', Integer),
+            Column('from', Integer),
+            Column('order', Integer),
+            schema='create')
+
+        # Note that the names are quoted b/c they are reserved words
+        x = select([table.c.col1, table.c['from'], table.c.order])
+        self.assert_compile(x,
             'SELECT '
-                '"foo"."t1"."col1" '
-            'FROM '
-                '"foo"."t1"'
+                '"create"."foreign".col1, '
+                '"create"."foreign"."from", '
+                '"create"."foreign"."order" '
+            'FROM "create"."foreign"'
         )
 
-        self.assert_compile(t1.select().apply_labels(),
+        # Create the same table with quotes set to False now
+        metadata = MetaData()
+        table = Table('foreign', metadata,
+            Column('col1', Integer),
+            Column('from', Integer, quote=False),
+            Column('order', Integer, quote=False),
+            schema='create', quote=False, quote_schema=False)
+
+        # Note that the names are now unquoted
+        x = select([table.c.col1, table.c['from'], table.c.order])
+        self.assert_compile(x,
             'SELECT '
-                '"foo"."t1"."col1" AS "foo_t1_col1" '
-            'FROM '
-                '"foo"."t1"'
+                'create.foreign.col1, '
+                'create.foreign.from, '
+                'create.foreign.order '
+            'FROM create.foreign'
+        )
+
+    def test_subquery(self):
+        # Lower case names, should not quote
+        metadata = MetaData()
+        t1 = Table('t1', metadata,
+            Column('col1', Integer),
+            schema='foo')
+        a = t1.select().alias('anon')
+        b = select([1], a.c.col1 == 2, from_obj=a)
+        self.assert_compile(b,
+            'SELECT 1 '
+            'FROM ('
+                'SELECT '
+                    'foo.t1.col1 AS col1 '
+                'FROM '
+                    'foo.t1'
+            ') AS anon '
+            'WHERE anon.col1 = :col1_1'
         )
 
+        # Lower case names, quotes on, should quote
+        metadata = MetaData()
+        t1 = Table('t1', metadata,
+            Column('col1', Integer, quote=True),
+            schema='foo', quote=True, quote_schema=True)
         a = t1.select().alias('anon')
         b = select([1], a.c.col1 == 2, from_obj=a)
         self.assert_compile(b,
@@ -138,177 +322,210 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
             'WHERE anon."col1" = :col1_1'
         )
 
+        # Not lower case names, should quote
         metadata = MetaData()
-        t1 = Table('TableOne', metadata,
-            Column('ColumnOne', Integer),
-            schema="FooBar")
-
-        self.assert_compile(t1.select(),
-            'SELECT '
-                '"FooBar"."TableOne"."ColumnOne" '
-            'FROM '
-                '"FooBar"."TableOne"'
+        t1 = Table('T1', metadata,
+            Column('Col1', Integer),
+            schema='Foo')
+        a = t1.select().alias('Anon')
+        b = select([1], a.c.Col1 == 2, from_obj=a)
+        self.assert_compile(b,
+            'SELECT 1 '
+            'FROM ('
+                'SELECT '
+                    '"Foo"."T1"."Col1" AS "Col1" '
+                'FROM '
+                    '"Foo"."T1"'
+            ') AS "Anon" '
+            'WHERE '
+                '"Anon"."Col1" = :Col1_1'
         )
 
+        # Not lower case names, quotes off, should not quote
         metadata = MetaData()
-        t1 = Table('TableOne', metadata,
-            Column('ColumnOne', Integer, quote=False),
-            schema="FooBar", quote=False, quote_schema=False)
+        t1 = Table('T1', metadata,
+            Column('Col1', Integer, quote=False),
+            schema='Foo', quote=False, quote_schema=False)
+        a = t1.select().alias('Anon')
+        b = select([1], a.c.Col1 == 2, from_obj=a)
+        self.assert_compile(b,
+            'SELECT 1 '
+            'FROM ('
+                'SELECT '
+                    'Foo.T1.Col1 AS Col1 '
+                'FROM '
+                    'Foo.T1'
+            ') AS "Anon" '
+            'WHERE '
+                '"Anon".Col1 = :Col1_1'
+        )
 
-        self.assert_compile(t1.select(),
+    def test_join(self):
+        # Lower case names, should not quote
+        metadata = MetaData()
+        t1 = Table('t1', metadata,
+           Column('col1', Integer))
+        t2 = Table('t2', metadata,
+           Column('col1', Integer),
+           Column('t1col1', Integer, ForeignKey('t1.col1')))
+        self.assert_compile(t2.join(t1).select(),
             'SELECT '
-                'FooBar.TableOne.ColumnOne '
+                't2.col1, t2.t1col1, t1.col1 '
             'FROM '
-                'FooBar.TableOne'
+                't2 '
+            'JOIN '
+                't1 ON t1.col1 = t2.t1col1'
         )
 
-        # TODO: is this what we really want here ?
-        # what if table/schema *are* quoted?
-        self.assert_compile(t1.select().apply_labels(),
+        # Lower case names, quotes on, should quote
+        metadata = MetaData()
+        t1 = Table('t1', metadata,
+           Column('col1', Integer, quote=True),
+           quote=True)
+        t2 = Table('t2', metadata,
+           Column('col1', Integer, quote=True),
+           Column('t1col1', Integer, ForeignKey('t1.col1'), quote=True),
+           quote=True)
+        self.assert_compile(t2.join(t1).select(),
             'SELECT '
-                'FooBar.TableOne.ColumnOne AS FooBar_TableOne_ColumnOne '
+                '"t2"."col1", "t2"."t1col1", "t1"."col1" '
             'FROM '
-                'FooBar.TableOne'
+                '"t2" '
+            'JOIN '
+                '"t1" ON "t1"."col1" = "t2"."t1col1"'
         )
 
-        a = t1.select().alias('anon')
-        b = select([1], a.c.ColumnOne == 2, from_obj=a)
-        self.assert_compile(b,
-            'SELECT 1 '
-            'FROM ('
-                'SELECT '
-                    'FooBar.TableOne.ColumnOne AS ColumnOne '
-                'FROM '
-                    'FooBar.TableOne'
-            ') AS anon '
-            'WHERE '
-                'anon.ColumnOne = :ColumnOne_1'
+        # Not lower case names, should quote
+        metadata = MetaData()
+        t1 = Table('T1', metadata,
+           Column('Col1', Integer))
+        t2 = Table('T2', metadata,
+           Column('Col1', Integer),
+           Column('T1Col1', Integer, ForeignKey('T1.Col1')))
+        self.assert_compile(t2.join(t1).select(),
+            'SELECT '
+                '"T2"."Col1", "T2"."T1Col1", "T1"."Col1" '
+            'FROM '
+                '"T2" '
+            'JOIN '
+                '"T1" ON "T1"."Col1" = "T2"."T1Col1"'
         )
 
-    def test_table_quote_flag(self):
+        # Not lower case names, quotes off, should not quote
         metadata = MetaData()
-        t1 = Table('TableOne', metadata,
-           Column('id', Integer),
+        t1 = Table('T1', metadata,
+           Column('Col1', Integer, quote=False),
            quote=False)
-        t2 = Table('TableTwo', metadata,
-           Column('id', Integer),
-           Column('t1_id', Integer, ForeignKey('TableOne.id')),
+        t2 = Table('T2', metadata,
+           Column('Col1', Integer, quote=False),
+           Column('T1Col1', Integer, ForeignKey('T1.Col1'), quote=False),
            quote=False)
-
         self.assert_compile(t2.join(t1).select(),
             'SELECT '
-                'TableTwo.id, TableTwo.t1_id, TableOne.id '
+                'T2.Col1, T2.T1Col1, T1.Col1 '
             'FROM '
-                'TableTwo '
+                'T2 '
             'JOIN '
-                'TableOne ON TableOne.id = TableTwo.t1_id'
+                'T1 ON T1.Col1 = T2.T1Col1'
         )
 
-    @testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
-    @testing.requires.subqueries
-    def test_labels3(self):
-        """test the quoting of labels.
-
-        If labels aren't quoted, a query in postgresql in particular will
-        fail since it produces:
-
-        SELECT
-            LaLa.lowercase, LaLa."UPPERCASE", LaLa."MixedCase", LaLa."ASC"
-        FROM (
-            SELECT DISTINCT
-                "WorstCase1".lowercase AS lowercase,
-                "WorstCase1"."UPPERCASE" AS UPPERCASE,
-                "WorstCase1"."MixedCase" AS MixedCase,
-                "WorstCase1"."ASC" AS ASC
-            FROM "WorstCase1"
-        ) AS LaLa
-
-        where the "UPPERCASE" column of "LaLa" doesn't exist.
-        """
-
-        x = table1.select(distinct=True).alias("LaLa").select().scalar()
-        self.assert_compile(
-            table1.select(distinct=True).alias("LaLa").select(),
+    def test_label_and_alias(self):
+        # Lower case names, should not quote
+        metadata = MetaData()
+        table = Table('t1', metadata,
+            Column('col1', Integer))
+        x = select([table.c.col1.label('label1')]).alias('alias1')
+        self.assert_compile(select([x.c.label1]),
             'SELECT '
-                '"LaLa".lowercase, '
-                '"LaLa"."UPPERCASE", '
-                '"LaLa"."MixedCase", '
-                '"LaLa"."ASC" '
+                'alias1.label1 '
             'FROM ('
-                'SELECT DISTINCT '
-                    '"WorstCase1".lowercase AS lowercase, '
-                    '"WorstCase1"."UPPERCASE" AS "UPPERCASE", '
-                    '"WorstCase1"."MixedCase" AS "MixedCase", '
-                    '"WorstCase1"."ASC" AS "ASC" '
-                'FROM "WorstCase1"'
-            ') AS "LaLa"'
+                'SELECT '
+                    't1.col1 AS label1 '
+                'FROM t1'
+            ') AS alias1'
         )
 
-    def test_labels2(self):
+        # Not lower case names, should quote
         metadata = MetaData()
-        table = Table("ImATable", metadata,
-            Column("col1", Integer))
-
-        x = select([table.c.col1.label("ImATable_col1")]).alias("SomeAlias")
-        self.assert_compile(select([x.c.ImATable_col1]),
+        table = Table('T1', metadata,
+            Column('Col1', Integer))
+        x = select([table.c.Col1.label('Label1')]).alias('Alias1')
+        self.assert_compile(select([x.c.Label1]),
             'SELECT '
-                '"SomeAlias"."ImATable_col1" '
+                '"Alias1"."Label1" '
             'FROM ('
                 'SELECT '
-                    '"ImATable".col1 AS "ImATable_col1" '
-                'FROM "ImATable"'
-            ') AS "SomeAlias"'
+                    '"T1"."Col1" AS "Label1" '
+                'FROM "T1"'
+            ') AS "Alias1"'
         )
 
-        # note that 'foo' is already quoted
-        columns = [sql.literal_column("'foo'").label("somelabel")]
-        x = select(columns, from_obj=[table]).alias("AnAlias")
+    def test_literal_column_already_with_quotes(self):
+        # Lower case names
+        metadata = MetaData()
+        table = Table('t1', metadata,
+            Column('col1', Integer))
+
+        # Note that 'col1' is already quoted (literal_column)
+        columns = [sql.literal_column("'col1'").label('label1')]
+        x = select(columns, from_obj=[table]).alias('alias1')
         x = x.select()
         self.assert_compile(x,
             'SELECT '
-                '"AnAlias".somelabel '
+                'alias1.label1 '
             'FROM ('
                 'SELECT '
-                    '\'foo\' AS somelabel '
-                'FROM "ImATable"'
-            ') AS "AnAlias"'
+                    '\'col1\' AS label1 '
+                'FROM t1'
+            ') AS alias1'
         )
 
-        # note that 'FooCol' is already quoted
-        columns = [sql.literal_column("'FooCol'").label("SomeLabel")]
-        x = select(columns, from_obj=[table])
+        # Not lower case names
+        metadata = MetaData()
+        table = Table('T1', metadata,
+            Column('Col1', Integer))
+
+        # Note that 'Col1' is already quoted (literal_column)
+        columns = [sql.literal_column("'Col1'").label('Label1')]
+        x = select(columns, from_obj=[table]).alias('Alias1')
         x = x.select()
         self.assert_compile(x,
             'SELECT '
-                '"SomeLabel" '
+                '"Alias1"."Label1" '
             'FROM ('
                 'SELECT '
-                    '\'FooCol\' AS "SomeLabel" '
-                'FROM "ImATable"'
-            ')'
+                    '\'Col1\' AS "Label1" '
+                'FROM "T1"'
+            ') AS "Alias1"'
         )
 
-    def test_reserved_words(self):
+    def test_apply_labels(self):
+        # Not lower case names, should quote
         metadata = MetaData()
-        table = Table("ImATable", metadata,
-            Column("col1", Integer),
-            Column("from", Integer),
-            Column("louisville", Integer),
-            Column("order", Integer))
+        t1 = Table('T1', metadata,
+            Column('Col1', Integer),
+            schema='Foo')
 
-        columns = [
-            table.c.col1,
-            table.c['from'],
-            table.c.louisville,
-            table.c.order]
-        x = select(columns)
-        self.assert_compile(x,
+        self.assert_compile(t1.select().apply_labels(),
+            'SELECT '
+                '"Foo"."T1"."Col1" AS "Foo_T1_Col1" '
+            'FROM '
+                '"Foo"."T1"'
+        )
+
+        # Not lower case names, quotes off
+        metadata = MetaData()
+        t1 = Table('T1', metadata,
+            Column('Col1', Integer, quote=False),
+            schema='Foo', quote=False, quote_schema=False)
+
+        # TODO: is this what we really want here ?
+        # what if table/schema *are* quoted?
+        self.assert_compile(t1.select().apply_labels(),
             'SELECT '
-                '"ImATable".col1, '
-                '"ImATable"."from", '
-                '"ImATable".louisville, '
-                '"ImATable"."order" '
-            'FROM "ImATable"'
+                'Foo.T1.Col1 AS Foo_T1_Col1 '
+            'FROM '
+                'Foo.T1'
         )