3
from sqlalchemy.testing import eq_, assert_raises_message
4
from sqlalchemy import *
5
from sqlalchemy import sql, exc, schema, types as sqltypes
6
from sqlalchemy.dialects.mysql import base as mysql
7
from sqlalchemy.testing import fixtures, AssertsCompiledSQL
8
from sqlalchemy import testing
10
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
12
__dialect__ = mysql.dialect()
14
def test_reserved_words(self):
15
table = Table("mysql_table", MetaData(),
16
Column("col1", Integer),
17
Column("master_ssl_verify_server_cert", Integer))
18
x = select([table.c.col1, table.c.master_ssl_verify_server_cert])
20
self.assert_compile(x,
21
"SELECT mysql_table.col1, "
22
"mysql_table.`master_ssl_verify_server_cert` FROM mysql_table")
24
def test_create_index_simple(self):
26
tbl = Table('testtbl', m, Column('data', String(255)))
27
idx = Index('test_idx1', tbl.c.data)
29
self.assert_compile(schema.CreateIndex(idx),
30
'CREATE INDEX test_idx1 ON testtbl (data)')
32
def test_create_index_with_length(self):
34
tbl = Table('testtbl', m, Column('data', String(255)))
35
idx1 = Index('test_idx1', tbl.c.data, mysql_length=10)
36
idx2 = Index('test_idx2', tbl.c.data, mysql_length=5)
38
self.assert_compile(schema.CreateIndex(idx1),
39
'CREATE INDEX test_idx1 ON testtbl (data(10))')
40
self.assert_compile(schema.CreateIndex(idx2),
41
'CREATE INDEX test_idx2 ON testtbl (data(5))')
43
def test_create_composite_index_with_length(self):
45
tbl = Table('testtbl', m,
46
Column('a', String(255)),
47
Column('b', String(255)))
49
idx1 = Index('test_idx1', tbl.c.a, tbl.c.b,
50
mysql_length={'a': 10, 'b': 20})
51
idx2 = Index('test_idx2', tbl.c.a, tbl.c.b,
52
mysql_length={'a': 15})
53
idx3 = Index('test_idx3', tbl.c.a, tbl.c.b,
57
schema.CreateIndex(idx1),
58
'CREATE INDEX test_idx1 ON testtbl (a(10), b(20))'
61
schema.CreateIndex(idx2),
62
'CREATE INDEX test_idx2 ON testtbl (a(15), b)'
65
schema.CreateIndex(idx3),
66
'CREATE INDEX test_idx3 ON testtbl (a(30), b(30))'
69
def test_create_index_with_using(self):
71
tbl = Table('testtbl', m, Column('data', String(255)))
72
idx1 = Index('test_idx1', tbl.c.data, mysql_using='btree')
73
idx2 = Index('test_idx2', tbl.c.data, mysql_using='hash')
75
self.assert_compile(schema.CreateIndex(idx1),
76
'CREATE INDEX test_idx1 ON testtbl (data) USING btree')
77
self.assert_compile(schema.CreateIndex(idx2),
78
'CREATE INDEX test_idx2 ON testtbl (data) USING hash')
80
def test_create_pk_plain(self):
82
tbl = Table('testtbl', m, Column('data', String(255)),
83
PrimaryKeyConstraint('data'))
85
self.assert_compile(schema.CreateTable(tbl),
86
"CREATE TABLE testtbl (data VARCHAR(255), PRIMARY KEY (data))")
88
def test_create_pk_with_using(self):
90
tbl = Table('testtbl', m, Column('data', String(255)),
91
PrimaryKeyConstraint('data', mysql_using='btree'))
93
self.assert_compile(schema.CreateTable(tbl),
94
"CREATE TABLE testtbl (data VARCHAR(255), "
95
"PRIMARY KEY (data) USING btree)")
97
def test_skip_deferrable_kw(self):
99
t1 = Table('t1', m, Column('id', Integer, primary_key=True))
100
t2 = Table('t2', m, Column('id', Integer,
101
ForeignKey('t1.id', deferrable=True),
105
schema.CreateTable(t2),
106
"CREATE TABLE t2 (id INTEGER NOT NULL, "
107
"PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES t1 (id))"
110
class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
111
"""Tests MySQL-dialect specific compilation."""
113
__dialect__ = mysql.dialect()
115
def test_precolumns(self):
116
dialect = self.__dialect__
118
def gen(distinct=None, prefixes=None):
120
if distinct is not None:
121
kw['distinct'] = distinct
122
if prefixes is not None:
123
kw['prefixes'] = prefixes
124
return str(select(['q'], **kw).compile(dialect=dialect))
126
eq_(gen(None), 'SELECT q')
127
eq_(gen(True), 'SELECT DISTINCT q')
129
eq_(gen(prefixes=['ALL']), 'SELECT ALL q')
130
eq_(gen(prefixes=['DISTINCTROW']),
131
'SELECT DISTINCTROW q')
133
# Interaction with MySQL prefix extensions
135
gen(None, ['straight_join']),
136
'SELECT straight_join q')
138
gen(False, ['HIGH_PRIORITY', 'SQL_SMALL_RESULT', 'ALL']),
139
'SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL q')
141
gen(True, ['high_priority', sql.text('sql_cache')]),
142
'SELECT high_priority sql_cache DISTINCT q')
144
@testing.uses_deprecated
145
def test_deprecated_distinct(self):
146
dialect = self.__dialect__
149
select(['q'], distinct='ALL'),
154
select(['q'], distinct='distinctROW'),
155
'SELECT DISTINCTROW q',
159
select(['q'], distinct='ALL',
160
prefixes=['HIGH_PRIORITY', 'SQL_SMALL_RESULT']),
161
'SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL q'
164
def test_backslash_escaping(self):
166
sql.column('foo').like('bar', escape='\\'),
167
"foo LIKE %s ESCAPE '\\\\'"
170
dialect = mysql.dialect()
171
dialect._backslash_escapes=False
173
sql.column('foo').like('bar', escape='\\'),
174
"foo LIKE %s ESCAPE '\\'",
178
def test_limit(self):
179
t = sql.table('t', sql.column('col1'), sql.column('col2'))
182
select([t]).limit(10).offset(20),
183
"SELECT t.col1, t.col2 FROM t LIMIT %s, %s",
184
{'param_1':20, 'param_2':10}
187
select([t]).limit(10),
188
"SELECT t.col1, t.col2 FROM t LIMIT %s",
192
select([t]).offset(10),
193
"SELECT t.col1, t.col2 FROM t LIMIT %s, 18446744073709551615",
197
def test_varchar_raise(self):
207
type_ = sqltypes.to_instance(type_)
208
assert_raises_message(
210
"VARCHAR requires a length on dialect mysql",
212
dialect=mysql.dialect()
215
t1 = Table('sometable', MetaData(),
216
Column('somecolumn', type_)
218
assert_raises_message(
220
r"\(in table 'sometable', column 'somecolumn'\)\: "
221
r"(?:N)?VARCHAR requires a length on dialect mysql",
222
schema.CreateTable(t1).compile,
223
dialect=mysql.dialect()
226
def test_update_limit(self):
227
t = sql.table('t', sql.column('col1'), sql.column('col2'))
230
t.update(values={'col1':123}),
231
"UPDATE t SET col1=%s"
234
t.update(values={'col1':123}, mysql_limit=5),
235
"UPDATE t SET col1=%s LIMIT 5"
238
t.update(values={'col1':123}, mysql_limit=None),
239
"UPDATE t SET col1=%s"
242
t.update(t.c.col2==456, values={'col1':123}, mysql_limit=1),
243
"UPDATE t SET col1=%s WHERE t.col2 = %s LIMIT 1"
246
def test_utc_timestamp(self):
247
self.assert_compile(func.utc_timestamp(), "UTC_TIMESTAMP")
249
def test_sysdate(self):
250
self.assert_compile(func.sysdate(), "SYSDATE()")
253
t = sql.table('t', sql.column('col'))
257
(Integer, "CAST(t.col AS SIGNED INTEGER)"),
258
(INT, "CAST(t.col AS SIGNED INTEGER)"),
259
(m.MSInteger, "CAST(t.col AS SIGNED INTEGER)"),
260
(m.MSInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),
261
(SmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
262
(m.MSSmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
263
(m.MSTinyInteger, "CAST(t.col AS SIGNED INTEGER)"),
264
# 'SIGNED INTEGER' is a bigint, so this is ok.
265
(m.MSBigInteger, "CAST(t.col AS SIGNED INTEGER)"),
266
(m.MSBigInteger(unsigned=False), "CAST(t.col AS SIGNED INTEGER)"),
267
(m.MSBigInteger(unsigned=True),
268
"CAST(t.col AS UNSIGNED INTEGER)"),
271
# this is kind of sucky. thank you default arguments!
272
(NUMERIC, "CAST(t.col AS DECIMAL)"),
273
(DECIMAL, "CAST(t.col AS DECIMAL)"),
274
(Numeric, "CAST(t.col AS DECIMAL)"),
275
(m.MSNumeric, "CAST(t.col AS DECIMAL)"),
276
(m.MSDecimal, "CAST(t.col AS DECIMAL)"),
280
(m.MSFloat, "t.col"),
281
(m.MSDouble, "t.col"),
284
(TIMESTAMP, "CAST(t.col AS DATETIME)"),
285
(DATETIME, "CAST(t.col AS DATETIME)"),
286
(DATE, "CAST(t.col AS DATE)"),
287
(TIME, "CAST(t.col AS TIME)"),
288
(DateTime, "CAST(t.col AS DATETIME)"),
289
(Date, "CAST(t.col AS DATE)"),
290
(Time, "CAST(t.col AS TIME)"),
291
(DateTime, "CAST(t.col AS DATETIME)"),
292
(Date, "CAST(t.col AS DATE)"),
293
(m.MSTime, "CAST(t.col AS TIME)"),
294
(m.MSTimeStamp, "CAST(t.col AS DATETIME)"),
296
(m.MSYear(2), "t.col"),
299
(String, "CAST(t.col AS CHAR)"),
300
(Unicode, "CAST(t.col AS CHAR)"),
301
(UnicodeText, "CAST(t.col AS CHAR)"),
302
(VARCHAR, "CAST(t.col AS CHAR)"),
303
(NCHAR, "CAST(t.col AS CHAR)"),
304
(CHAR, "CAST(t.col AS CHAR)"),
305
(CLOB, "CAST(t.col AS CHAR)"),
306
(TEXT, "CAST(t.col AS CHAR)"),
307
(String(32), "CAST(t.col AS CHAR(32))"),
308
(Unicode(32), "CAST(t.col AS CHAR(32))"),
309
(CHAR(32), "CAST(t.col AS CHAR(32))"),
310
(m.MSString, "CAST(t.col AS CHAR)"),
311
(m.MSText, "CAST(t.col AS CHAR)"),
312
(m.MSTinyText, "CAST(t.col AS CHAR)"),
313
(m.MSMediumText, "CAST(t.col AS CHAR)"),
314
(m.MSLongText, "CAST(t.col AS CHAR)"),
315
(m.MSNChar, "CAST(t.col AS CHAR)"),
316
(m.MSNVarChar, "CAST(t.col AS CHAR)"),
318
(LargeBinary, "CAST(t.col AS BINARY)"),
319
(BLOB, "CAST(t.col AS BINARY)"),
320
(m.MSBlob, "CAST(t.col AS BINARY)"),
321
(m.MSBlob(32), "CAST(t.col AS BINARY)"),
322
(m.MSTinyBlob, "CAST(t.col AS BINARY)"),
323
(m.MSMediumBlob, "CAST(t.col AS BINARY)"),
324
(m.MSLongBlob, "CAST(t.col AS BINARY)"),
325
(m.MSBinary, "CAST(t.col AS BINARY)"),
326
(m.MSBinary(32), "CAST(t.col AS BINARY)"),
327
(m.MSVarBinary, "CAST(t.col AS BINARY)"),
328
(m.MSVarBinary(32), "CAST(t.col AS BINARY)"),
330
# maybe this could be changed to something more DWIM, needs
336
(m.MSEnum("1", "2"), "t.col"),
338
(m.MSSet("1", "2"), "t.col"),
341
for type_, expected in specs:
342
self.assert_compile(cast(t.c.col, type_), expected)
344
def test_no_cast_pre_4(self):
346
cast(Column('foo', Integer), String),
349
dialect = mysql.dialect()
350
dialect.server_version_info = (3, 2, 3)
352
cast(Column('foo', Integer), String),
357
def test_cast_grouped_expression_non_castable(self):
359
cast(sql.column('x') + sql.column('y'), Float),
363
def test_cast_grouped_expression_pre_4(self):
364
dialect = mysql.dialect()
365
dialect.server_version_info = (3, 2, 3)
367
cast(sql.column('x') + sql.column('y'), Integer),
372
def test_extract(self):
373
t = sql.table('t', sql.column('col1'))
375
for field in 'year', 'month', 'day':
377
select([extract(field, t.c.col1)]),
378
"SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field)
380
# millsecondS to millisecond
382
select([extract('milliseconds', t.c.col1)]),
383
"SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t")
385
def test_too_long_index(self):
386
exp = 'ix_zyrenian_zyme_zyzzogeton_zyzzogeton_zyrenian_zyme_zyz_5cd2'
387
tname = 'zyrenian_zyme_zyzzogeton_zyzzogeton'
388
cname = 'zyrenian_zyme_zyzzogeton_zo'
390
t1 = Table(tname, MetaData(),
391
Column(cname, Integer, index=True),
393
ix1 = list(t1.indexes)[0]
396
schema.CreateIndex(ix1),
398
"ON %s (%s)" % (exp, tname, cname)
401
def test_innodb_autoincrement(self):
402
t1 = Table('sometable', MetaData(), Column('assigned_id',
403
Integer(), primary_key=True, autoincrement=False),
404
Column('id', Integer(), primary_key=True,
405
autoincrement=True), mysql_engine='InnoDB')
406
self.assert_compile(schema.CreateTable(t1),
407
'CREATE TABLE sometable (assigned_id '
408
'INTEGER NOT NULL, id INTEGER NOT NULL '
409
'AUTO_INCREMENT, PRIMARY KEY (assigned_id, '
410
'id), KEY idx_autoinc_id (id))ENGINE=Inn'
413
t1 = Table('sometable', MetaData(), Column('assigned_id',
414
Integer(), primary_key=True, autoincrement=True),
415
Column('id', Integer(), primary_key=True,
416
autoincrement=False), mysql_engine='InnoDB')
417
self.assert_compile(schema.CreateTable(t1),
418
'CREATE TABLE sometable (assigned_id '
419
'INTEGER NOT NULL AUTO_INCREMENT, id '
420
'INTEGER NOT NULL, PRIMARY KEY '
421
'(assigned_id, id))ENGINE=InnoDB')
423
def test_innodb_autoincrement_reserved_word_column_name(self):
425
'sometable', MetaData(),
426
Column('id', Integer(), primary_key=True, autoincrement=False),
427
Column('order', Integer(), primary_key=True, autoincrement=True),
428
mysql_engine='InnoDB')
430
schema.CreateTable(t1),
431
'CREATE TABLE sometable ('
432
'id INTEGER NOT NULL, '
433
'`order` INTEGER NOT NULL AUTO_INCREMENT, '
434
'PRIMARY KEY (id, `order`), '
435
'KEY idx_autoinc_order (`order`)'