38
table1.insert().execute({'lowercase':1,'UPPERCASE':2,'MixedCase':3,'a123':4},
39
{'lowercase':2,'UPPERCASE':2,'MixedCase':3,'a123':4},
40
{'lowercase':4,'UPPERCASE':3,'MixedCase':2,'a123':1})
41
table2.insert().execute({'d123':1,'u123':2,'MixedCase':3},
42
{'d123':2,'u123':2,'MixedCase':3},
43
{'d123':4,'u123':3,'MixedCase':2})
45
res1 = select([table1.c.lowercase, table1.c.UPPERCASE, table1.c.MixedCase, table1.c.a123]).execute().fetchall()
47
assert(res1==[(1,2,3,4),(2,2,3,4),(4,3,2,1)])
49
res2 = select([table2.c.d123, table2.c.u123, table2.c.MixedCase]).execute().fetchall()
51
assert(res2==[(1,2,3),(2,2,3),(4,3,2)])
53
def test_numeric(self):
55
t1 = Table('35table', metadata,
56
Column('25column', Integer))
57
self.assert_compile(schema.CreateTable(t1), 'CREATE TABLE "35table" ('
62
42
def test_reflect(self):
63
43
meta2 = MetaData(testing.db)
64
t2 = Table('WorstCase2', meta2, autoload=True, quote=True)
44
t2 = Table('WorstCase1', meta2, autoload=True, quote=True)
45
assert 'lowercase' in t2.c
47
# indicates the DB returns unquoted names as
48
# UPPERCASE, which we then assume are unquoted and go to
49
# lower case. So we cannot accurately reflect quoted UPPERCASE
50
# names from a "name normalize" backend, as they cannot be
51
# distinguished from case-insensitive/unquoted names.
52
if testing.db.dialect.requires_name_normalize:
53
assert 'uppercase' in t2.c
55
assert 'UPPERCASE' in t2.c
57
# ASC OTOH is a reserved word, which is always quoted, so
58
# with that name we keep the quotes on and it stays uppercase
59
# regardless. Seems a little weird, though.
65
62
assert 'MixedCase' in t2.c
67
def test_labels(self):
68
table1.insert().execute({'lowercase':1,'UPPERCASE':2,'MixedCase':3,'a123':4},
69
{'lowercase':2,'UPPERCASE':2,'MixedCase':3,'a123':4},
70
{'lowercase':4,'UPPERCASE':3,'MixedCase':2,'a123':1})
71
table2.insert().execute({'d123':1,'u123':2,'MixedCase':3},
72
{'d123':2,'u123':2,'MixedCase':3},
73
{'d123':4,'u123':3,'MixedCase':2})
75
res1 = select([table1.c.lowercase, table1.c.UPPERCASE, table1.c.MixedCase, table1.c.a123], use_labels=True).execute().fetchall()
77
assert(res1==[(1,2,3,4),(2,2,3,4),(4,3,2,1)])
79
res2 = select([table2.c.d123, table2.c.u123, table2.c.MixedCase], use_labels=True).execute().fetchall()
81
assert(res2==[(1,2,3),(2,2,3),(4,3,2)])
83
def test_quote_flag(self):
85
t1 = Table('TableOne', metadata,
86
Column('ColumnOne', Integer), schema="FooBar")
87
self.assert_compile(t1.select(), '''SELECT "FooBar"."TableOne"."ColumnOne" FROM "FooBar"."TableOne"''')
90
t1 = Table('t1', metadata,
91
Column('col1', Integer, quote=True), quote=True, schema="foo", quote_schema=True)
92
self.assert_compile(t1.select(), '''SELECT "foo"."t1"."col1" FROM "foo"."t1"''')
94
self.assert_compile(t1.select().apply_labels(), '''SELECT "foo"."t1"."col1" AS "foo_t1_col1" FROM "foo"."t1"''')
95
a = t1.select().alias('anon')
96
b = select([1], a.c.col1==2, from_obj=a)
97
self.assert_compile(b,
98
'''SELECT 1 FROM (SELECT "foo"."t1"."col1" AS "col1" FROM '''\
99
'''"foo"."t1") AS anon WHERE anon."col1" = :col1_1'''
102
metadata = MetaData()
103
t1 = Table('TableOne', metadata,
104
Column('ColumnOne', Integer, quote=False), quote=False, schema="FooBar", quote_schema=False)
105
self.assert_compile(t1.select(), "SELECT FooBar.TableOne.ColumnOne FROM FooBar.TableOne")
107
self.assert_compile(t1.select().apply_labels(),
108
"SELECT FooBar.TableOne.ColumnOne AS "\
109
"FooBar_TableOne_ColumnOne FROM FooBar.TableOne" # TODO: is this what we really want here ? what if table/schema
113
a = t1.select().alias('anon')
114
b = select([1], a.c.ColumnOne==2, from_obj=a)
115
self.assert_compile(b,
116
"SELECT 1 FROM (SELECT FooBar.TableOne.ColumnOne AS "\
117
"ColumnOne FROM FooBar.TableOne) AS anon WHERE anon.ColumnOne = :ColumnOne_1"
122
def test_table_quote_flag(self):
123
metadata = MetaData()
124
t1 = Table('TableOne', metadata,
125
Column('id', Integer),
127
t2 = Table('TableTwo', metadata,
128
Column('id', Integer),
129
Column('t1_id', Integer, ForeignKey('TableOne.id')),
133
t2.join(t1).select(),
134
"SELECT TableTwo.id, TableTwo.t1_id, TableOne.id "
135
"FROM TableTwo JOIN TableOne ON TableOne.id = TableTwo.t1_id")
65
table1.insert().execute(
66
{'lowercase': 1, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
67
{'lowercase': 2, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
68
{'lowercase': 4, 'UPPERCASE': 3, 'MixedCase': 2, 'a123': 1})
69
table2.insert().execute(
70
{'d123': 1, 'u123': 2, 'MixedCase': 3},
71
{'d123': 2, 'u123': 2, 'MixedCase': 3},
72
{'d123': 4, 'u123': 3, 'MixedCase': 2})
80
result = select(columns).execute().fetchall()
81
assert(result == [(1, 2, 3, 4), (2, 2, 3, 4), (4, 3, 2, 1)])
88
result = select(columns).execute().fetchall()
89
assert(result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)])
91
def test_use_labels(self):
92
table1.insert().execute(
93
{'lowercase': 1, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
94
{'lowercase': 2, 'UPPERCASE': 2, 'MixedCase': 3, 'a123': 4},
95
{'lowercase': 4, 'UPPERCASE': 3, 'MixedCase': 2, 'a123': 1})
96
table2.insert().execute(
97
{'d123': 1, 'u123': 2, 'MixedCase': 3},
98
{'d123': 2, 'u123': 2, 'MixedCase': 3},
99
{'d123': 4, 'u123': 3, 'MixedCase': 2})
107
result = select(columns, use_labels=True).execute().fetchall()
108
assert(result == [(1, 2, 3, 4), (2, 2, 3, 4), (4, 3, 2, 1)])
115
result = select(columns, use_labels=True).execute().fetchall()
116
assert(result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)])
137
118
@testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
138
119
@testing.requires.subqueries
139
120
def test_labels(self):
140
121
"""test the quoting of labels.
142
if labels arent quoted, a query in postgresql in particular will fail since it produces:
123
If labels aren't quoted, a query in postgresql in particular will
124
fail since it produces:
144
SELECT LaLa.lowercase, LaLa."UPPERCASE", LaLa."MixedCase", LaLa."ASC"
145
FROM (SELECT DISTINCT "WorstCase1".lowercase AS lowercase,
127
LaLa.lowercase, LaLa."UPPERCASE", LaLa."MixedCase", LaLa."ASC"
130
"WorstCase1".lowercase AS lowercase,
146
131
"WorstCase1"."UPPERCASE" AS UPPERCASE,
147
"WorstCase1"."MixedCase" AS MixedCase, "WorstCase1"."ASC" AS ASC \nFROM "WorstCase1") AS LaLa
132
"WorstCase1"."MixedCase" AS MixedCase,
133
"WorstCase1"."ASC" AS ASC
149
where the "UPPERCASE" column of "LaLa" doesnt exist.
137
where the "UPPERCASE" column of "LaLa" doesn't exist.
152
x = table1.select(distinct=True).alias("LaLa").select().scalar()
154
def test_labels2(self):
155
metadata = MetaData()
156
table = Table("ImATable", metadata,
157
Column("col1", Integer))
158
x = select([table.c.col1.label("ImATable_col1")]).alias("SomeAlias")
159
self.assert_compile(select([x.c.ImATable_col1]),
160
'''SELECT "SomeAlias"."ImATable_col1" FROM (SELECT "ImATable".col1 AS "ImATable_col1" FROM "ImATable") AS "SomeAlias"''')
162
# note that 'foo' and 'FooCol' are literals already quoted
163
x = select([sql.literal_column("'foo'").label("somelabel")], from_obj=[table]).alias("AnAlias")
165
self.assert_compile(x,
166
'''SELECT "AnAlias".somelabel FROM (SELECT 'foo' AS somelabel FROM "ImATable") AS "AnAlias"''')
168
x = select([sql.literal_column("'FooCol'").label("SomeLabel")], from_obj=[table])
170
self.assert_compile(x,
171
'''SELECT "SomeLabel" FROM (SELECT 'FooCol' AS "SomeLabel" FROM "ImATable")''')
140
x = table1.select(distinct=True).alias('LaLa').select().scalar()
142
table1.select(distinct=True).alias('LaLa').select(),
145
'"LaLa"."UPPERCASE", '
146
'"LaLa"."MixedCase", '
150
'"WorstCase1".lowercase AS lowercase, '
151
'"WorstCase1"."UPPERCASE" AS "UPPERCASE", '
152
'"WorstCase1"."MixedCase" AS "MixedCase", '
153
'"WorstCase1"."ASC" AS "ASC" '
158
def test_lower_case_names(self):
159
# Create table with quote defaults
160
metadata = MetaData()
161
t1 = Table('t1', metadata,
162
Column('col1', Integer),
165
# Note that the names are not quoted b/c they are all lower case
166
result = 'CREATE TABLE foo.t1 (col1 INTEGER)'
167
self.assert_compile(schema.CreateTable(t1), result)
169
# Create the same table with quotes set to True now
170
metadata = MetaData()
171
t1 = Table('t1', metadata,
172
Column('col1', Integer, quote=True),
173
schema='foo', quote=True, quote_schema=True)
175
# Note that the names are now quoted
176
result = 'CREATE TABLE "foo"."t1" ("col1" INTEGER)'
177
self.assert_compile(schema.CreateTable(t1), result)
179
def test_upper_case_names(self):
180
# Create table with quote defaults
181
metadata = MetaData()
182
t1 = Table('TABLE1', metadata,
183
Column('COL1', Integer),
186
# Note that the names are quoted b/c they are not all lower case
187
result = 'CREATE TABLE "FOO"."TABLE1" ("COL1" INTEGER)'
188
self.assert_compile(schema.CreateTable(t1), result)
190
# Create the same table with quotes set to False now
191
metadata = MetaData()
192
t1 = Table('TABLE1', metadata,
193
Column('COL1', Integer, quote=False),
194
schema='FOO', quote=False, quote_schema=False)
196
# Note that the names are now unquoted
197
result = 'CREATE TABLE FOO.TABLE1 (COL1 INTEGER)'
198
self.assert_compile(schema.CreateTable(t1), result)
200
def test_mixed_case_names(self):
201
# Create table with quote defaults
202
metadata = MetaData()
203
t1 = Table('Table1', metadata,
204
Column('Col1', Integer),
207
# Note that the names are quoted b/c they are not all lower case
208
result = 'CREATE TABLE "Foo"."Table1" ("Col1" INTEGER)'
209
self.assert_compile(schema.CreateTable(t1), result)
211
# Create the same table with quotes set to False now
212
metadata = MetaData()
213
t1 = Table('Table1', metadata,
214
Column('Col1', Integer, quote=False),
215
schema='Foo', quote=False, quote_schema=False)
217
# Note that the names are now unquoted
218
result = 'CREATE TABLE Foo.Table1 (Col1 INTEGER)'
219
self.assert_compile(schema.CreateTable(t1), result)
221
def test_numeric_initial_char(self):
222
# Create table with quote defaults
223
metadata = MetaData()
224
t1 = Table('35table', metadata,
225
Column('25column', Integer),
228
# Note that the names are quoted b/c the initial
229
# character is in ['$','0', '1' ... '9']
230
result = 'CREATE TABLE "45schema"."35table" ("25column" INTEGER)'
231
self.assert_compile(schema.CreateTable(t1), result)
233
# Create the same table with quotes set to False now
234
metadata = MetaData()
235
t1 = Table('35table', metadata,
236
Column('25column', Integer, quote=False),
237
schema='45schema', quote=False, quote_schema=False)
239
# Note that the names are now unquoted
240
result = 'CREATE TABLE 45schema.35table (25column INTEGER)'
241
self.assert_compile(schema.CreateTable(t1), result)
243
def test_illegal_initial_char(self):
244
# Create table with quote defaults
245
metadata = MetaData()
246
t1 = Table('$table', metadata,
247
Column('$column', Integer),
250
# Note that the names are quoted b/c the initial
251
# character is in ['$','0', '1' ... '9']
252
result = 'CREATE TABLE "$schema"."$table" ("$column" INTEGER)'
253
self.assert_compile(schema.CreateTable(t1), result)
255
# Create the same table with quotes set to False now
256
metadata = MetaData()
257
t1 = Table('$table', metadata,
258
Column('$column', Integer, quote=False),
259
schema='$schema', quote=False, quote_schema=False)
261
# Note that the names are now unquoted
262
result = 'CREATE TABLE $schema.$table ($column INTEGER)'
263
self.assert_compile(schema.CreateTable(t1), result)
173
265
def test_reserved_words(self):
174
metadata = MetaData()
175
table = Table("ImATable", metadata,
176
Column("col1", Integer),
177
Column("from", Integer),
178
Column("louisville", Integer),
179
Column("order", Integer))
180
x = select([table.c.col1, table.c['from'], table.c.louisville, table.c.order])
182
self.assert_compile(x,
183
'''SELECT "ImATable".col1, "ImATable"."from", "ImATable".louisville, "ImATable"."order" FROM "ImATable"''')
266
# Create table with quote defaults
267
metadata = MetaData()
268
table = Table('foreign', metadata,
269
Column('col1', Integer),
270
Column('from', Integer),
271
Column('order', Integer),
274
# Note that the names are quoted b/c they are reserved words
275
x = select([table.c.col1, table.c['from'], table.c.order])
276
self.assert_compile(x,
278
'"create"."foreign".col1, '
279
'"create"."foreign"."from", '
280
'"create"."foreign"."order" '
281
'FROM "create"."foreign"'
284
# Create the same table with quotes set to False now
285
metadata = MetaData()
286
table = Table('foreign', metadata,
287
Column('col1', Integer),
288
Column('from', Integer, quote=False),
289
Column('order', Integer, quote=False),
290
schema='create', quote=False, quote_schema=False)
292
# Note that the names are now unquoted
293
x = select([table.c.col1, table.c['from'], table.c.order])
294
self.assert_compile(x,
296
'create.foreign.col1, '
297
'create.foreign.from, '
298
'create.foreign.order '
299
'FROM create.foreign'
302
def test_subquery(self):
303
# Lower case names, should not quote
304
metadata = MetaData()
305
t1 = Table('t1', metadata,
306
Column('col1', Integer),
308
a = t1.select().alias('anon')
309
b = select([1], a.c.col1 == 2, from_obj=a)
310
self.assert_compile(b,
314
'foo.t1.col1 AS col1 '
318
'WHERE anon.col1 = :col1_1'
321
# Lower case names, quotes on, should quote
322
metadata = MetaData()
323
t1 = Table('t1', metadata,
324
Column('col1', Integer, quote=True),
325
schema='foo', quote=True, quote_schema=True)
326
a = t1.select().alias('anon')
327
b = select([1], a.c.col1 == 2, from_obj=a)
328
self.assert_compile(b,
332
'"foo"."t1"."col1" AS "col1" '
336
'WHERE anon."col1" = :col1_1'
339
# Not lower case names, should quote
340
metadata = MetaData()
341
t1 = Table('T1', metadata,
342
Column('Col1', Integer),
344
a = t1.select().alias('Anon')
345
b = select([1], a.c.Col1 == 2, from_obj=a)
346
self.assert_compile(b,
350
'"Foo"."T1"."Col1" AS "Col1" '
355
'"Anon"."Col1" = :Col1_1'
358
# Not lower case names, quotes off, should not quote
359
metadata = MetaData()
360
t1 = Table('T1', metadata,
361
Column('Col1', Integer, quote=False),
362
schema='Foo', quote=False, quote_schema=False)
363
a = t1.select().alias('Anon')
364
b = select([1], a.c.Col1 == 2, from_obj=a)
365
self.assert_compile(b,
369
'Foo.T1.Col1 AS Col1 '
374
'"Anon".Col1 = :Col1_1'
378
# Lower case names, should not quote
379
metadata = MetaData()
380
t1 = Table('t1', metadata,
381
Column('col1', Integer))
382
t2 = Table('t2', metadata,
383
Column('col1', Integer),
384
Column('t1col1', Integer, ForeignKey('t1.col1')))
385
self.assert_compile(t2.join(t1).select(),
387
't2.col1, t2.t1col1, t1.col1 '
391
't1 ON t1.col1 = t2.t1col1'
394
# Lower case names, quotes on, should quote
395
metadata = MetaData()
396
t1 = Table('t1', metadata,
397
Column('col1', Integer, quote=True),
399
t2 = Table('t2', metadata,
400
Column('col1', Integer, quote=True),
401
Column('t1col1', Integer, ForeignKey('t1.col1'), quote=True),
403
self.assert_compile(t2.join(t1).select(),
405
'"t2"."col1", "t2"."t1col1", "t1"."col1" '
409
'"t1" ON "t1"."col1" = "t2"."t1col1"'
412
# Not lower case names, should quote
413
metadata = MetaData()
414
t1 = Table('T1', metadata,
415
Column('Col1', Integer))
416
t2 = Table('T2', metadata,
417
Column('Col1', Integer),
418
Column('T1Col1', Integer, ForeignKey('T1.Col1')))
419
self.assert_compile(t2.join(t1).select(),
421
'"T2"."Col1", "T2"."T1Col1", "T1"."Col1" '
425
'"T1" ON "T1"."Col1" = "T2"."T1Col1"'
428
# Not lower case names, quotes off, should not quote
429
metadata = MetaData()
430
t1 = Table('T1', metadata,
431
Column('Col1', Integer, quote=False),
433
t2 = Table('T2', metadata,
434
Column('Col1', Integer, quote=False),
435
Column('T1Col1', Integer, ForeignKey('T1.Col1'), quote=False),
437
self.assert_compile(t2.join(t1).select(),
439
'T2.Col1, T2.T1Col1, T1.Col1 '
443
'T1 ON T1.Col1 = T2.T1Col1'
446
def test_label_and_alias(self):
447
# Lower case names, should not quote
448
metadata = MetaData()
449
table = Table('t1', metadata,
450
Column('col1', Integer))
451
x = select([table.c.col1.label('label1')]).alias('alias1')
452
self.assert_compile(select([x.c.label1]),
462
# Not lower case names, should quote
463
metadata = MetaData()
464
table = Table('T1', metadata,
465
Column('Col1', Integer))
466
x = select([table.c.Col1.label('Label1')]).alias('Alias1')
467
self.assert_compile(select([x.c.Label1]),
472
'"T1"."Col1" AS "Label1" '
477
def test_literal_column_already_with_quotes(self):
479
metadata = MetaData()
480
table = Table('t1', metadata,
481
Column('col1', Integer))
483
# Note that 'col1' is already quoted (literal_column)
484
columns = [sql.literal_column("'col1'").label('label1')]
485
x = select(columns, from_obj=[table]).alias('alias1')
487
self.assert_compile(x,
492
'\'col1\' AS label1 '
497
# Not lower case names
498
metadata = MetaData()
499
table = Table('T1', metadata,
500
Column('Col1', Integer))
502
# Note that 'Col1' is already quoted (literal_column)
503
columns = [sql.literal_column("'Col1'").label('Label1')]
504
x = select(columns, from_obj=[table]).alias('Alias1')
506
self.assert_compile(x,
511
'\'Col1\' AS "Label1" '
516
def test_apply_labels(self):
517
# Not lower case names, should quote
518
metadata = MetaData()
519
t1 = Table('T1', metadata,
520
Column('Col1', Integer),
523
self.assert_compile(t1.select().apply_labels(),
525
'"Foo"."T1"."Col1" AS "Foo_T1_Col1" '
530
# Not lower case names, quotes off
531
metadata = MetaData()
532
t1 = Table('T1', metadata,
533
Column('Col1', Integer, quote=False),
534
schema='Foo', quote=False, quote_schema=False)
536
# TODO: is this what we really want here ?
537
# what if table/schema *are* quoted?
538
self.assert_compile(t1.select().apply_labels(),
540
'Foo.T1.Col1 AS Foo_T1_Col1 '
186
546
class PreparerTest(fixtures.TestBase):