def testcast(self):
tbl = table('casttest',
column('id', Integer),
column('v1', Float),
column('v2', Float),
column('ts', TIMESTAMP),
)
def check_results(dialect, expected_results, literal):
self.assertEqual(len(expected_results), 5, 'Incorrect number of expected results')
self.assertEqual(str(cast(tbl.c.v1, Numeric).compile(dialect=dialect)), 'CAST(casttest.v1 AS %s)' %expected_results[0])
self.assertEqual(str(cast(tbl.c.v1, Numeric(12, 9)).compile(dialect=dialect)), 'CAST(casttest.v1 AS %s)' %expected_results[1])
self.assertEqual(str(cast(tbl.c.ts, Date).compile(dialect=dialect)), 'CAST(casttest.ts AS %s)' %expected_results[2])
self.assertEqual(str(cast(1234, TEXT).compile(dialect=dialect)), 'CAST(%s AS %s)' %(literal, expected_results[3]))
self.assertEqual(str(cast('test', String(20)).compile(dialect=dialect)), 'CAST(%s AS %s)' %(literal, expected_results[4]))
sel = select([tbl, cast(tbl.c.v1, Numeric)]).compile(dialect=dialect)
self.assertEqual(str(sel), "SELECT casttest.id, casttest.v1, casttest.v2, casttest.ts, CAST(casttest.v1 AS NUMERIC(10, 2)) \nFROM casttest")
# first test with Postgres engine
check_results(postgres.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%(literal)s')
# then the Oracle engine
check_results(oracle.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20)'], ':literal')
# then the sqlite engine
check_results(sqlite.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '?')
# MySQL seems to only support DATE types for cast
self.assertEqual(str(cast(tbl.c.ts, Date).compile(dialect=mysql.dialect())), 'CAST(casttest.ts AS DATE)')
self.assertEqual(str(cast(tbl.c.ts, Numeric).compile(dialect=mysql.dialect())), 'casttest.ts')
def compileQuery(self,dbsInst,sel):
if type(sel) is types.StringType:
return sel
if self.dbType[dbsInst]=='oracle':
return sel.compile(dialect=oracle.dialect())
if self.dbType[dbsInst]=='mysql':
return sel.compile(dialect=mysql.dialect())
if self.dbType[dbsInst]=='sqlite':
return sel.compile(dialect=sqlite.dialect())
class SQLTest(TestBase, AssertsCompiledSQL):
"""Tests MySQL-dialect specific compilation."""
__dialect__ = mysql.dialect()
def test_precolumns(self):
dialect = self.__dialect__
def gen(distinct=None, prefixes=None):
kw = {}
if distinct is not None:
kw['distinct'] = distinct
if prefixes is not None:
kw['prefixes'] = prefixes
return str(select(['q'], **kw).compile(dialect=dialect))
self.assertEqual(gen(None), 'SELECT q')
self.assertEqual(gen(True), 'SELECT DISTINCT q')
self.assertEqual(gen(1), 'SELECT DISTINCT q')
self.assertEqual(gen('diSTInct'), 'SELECT DISTINCT q')
self.assertEqual(gen('DISTINCT'), 'SELECT DISTINCT q')
# Standard SQL
self.assertEqual(gen('all'), 'SELECT ALL q')
self.assertEqual(gen('distinctrow'), 'SELECT DISTINCTROW q')
# Interaction with MySQL prefix extensions
self.assertEqual(gen(None, ['straight_join']),
'SELECT straight_join q')
self.assertEqual(gen('all', ['HIGH_PRIORITY SQL_SMALL_RESULT']),
'SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL q')
self.assertEqual(gen(
True, ['high_priority', sql.text('sql_cache')]),
'SELECT high_priority sql_cache DISTINCT q')
def test_limit(self):
t = sql.table('t', sql.column('col1'), sql.column('col2'))
self.assert_compile(
select([t]).limit(10).offset(20),
"SELECT t.col1, t.col2 FROM t LIMIT 20, 10")
self.assert_compile(
select([t]).limit(10), "SELECT t.col1, t.col2 FROM t LIMIT 10")
self.assert_compile(
select([t]).offset(10),
"SELECT t.col1, t.col2 FROM t LIMIT 10, 18446744073709551615")
def test_update_limit(self):
t = sql.table('t', sql.column('col1'), sql.column('col2'))
self.assert_compile(t.update(values={'col1': 123}),
"UPDATE t SET col1=%s")
self.assert_compile(t.update(values={'col1': 123}, mysql_limit=5),
"UPDATE t SET col1=%s LIMIT 5")
self.assert_compile(t.update(values={'col1': 123}, mysql_limit=None),
"UPDATE t SET col1=%s")
self.assert_compile(
t.update(t.c.col2 == 456, values={'col1': 123}, mysql_limit=1),
"UPDATE t SET col1=%s WHERE t.col2 = %s LIMIT 1")
def test_utc_timestamp(self):
self.assert_compile(func.utc_timestamp(), "UTC_TIMESTAMP")
def test_cast(self):
t = sql.table('t', sql.column('col'))
m = mysql
specs = [
(Integer, "CAST(t.col AS SIGNED INTEGER)"),
(INT, "CAST(t.col AS SIGNED INTEGER)"),
(m.MSInteger, "CAST(t.col AS SIGNED INTEGER)"),
(m.MSInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),
(SmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
(m.MSSmallInteger, "CAST(t.col AS SIGNED INTEGER)"),
(m.MSTinyInteger, "CAST(t.col AS SIGNED INTEGER)"),
# 'SIGNED INTEGER' is a bigint, so this is ok.
(m.MSBigInteger, "CAST(t.col AS SIGNED INTEGER)"),
(m.MSBigInteger(unsigned=False), "CAST(t.col AS SIGNED INTEGER)"),
(m.MSBigInteger(unsigned=True), "CAST(t.col AS UNSIGNED INTEGER)"),
(m.MSBit, "t.col"),
# this is kind of sucky. thank you default arguments!
(NUMERIC, "CAST(t.col AS DECIMAL(10, 2))"),
(DECIMAL, "CAST(t.col AS DECIMAL(10, 2))"),
(Numeric, "CAST(t.col AS DECIMAL(10, 2))"),
(m.MSNumeric, "CAST(t.col AS DECIMAL(10, 2))"),
(m.MSDecimal, "CAST(t.col AS DECIMAL(10, 2))"),
(FLOAT, "t.col"),
(Float, "t.col"),
(m.MSFloat, "t.col"),
(m.MSDouble, "t.col"),
(m.MSReal, "t.col"),
(TIMESTAMP, "CAST(t.col AS DATETIME)"),
(DATETIME, "CAST(t.col AS DATETIME)"),
(DATE, "CAST(t.col AS DATE)"),
(TIME, "CAST(t.col AS TIME)"),
(DateTime, "CAST(t.col AS DATETIME)"),
(Date, "CAST(t.col AS DATE)"),
(Time, "CAST(t.col AS TIME)"),
(m.MSDateTime, "CAST(t.col AS DATETIME)"),
(m.MSDate, "CAST(t.col AS DATE)"),
(m.MSTime, "CAST(t.col AS TIME)"),
(m.MSTimeStamp, "CAST(t.col AS DATETIME)"),
(m.MSYear, "t.col"),
(m.MSYear(2), "t.col"),
(Interval, "t.col"),
(String, "CAST(t.col AS CHAR)"),
(Unicode, "CAST(t.col AS CHAR)"),
(UnicodeText, "CAST(t.col AS CHAR)"),
(VARCHAR, "CAST(t.col AS CHAR)"),
(NCHAR, "CAST(t.col AS CHAR)"),
(CHAR, "CAST(t.col AS CHAR)"),
(CLOB, "CAST(t.col AS CHAR)"),
(TEXT, "CAST(t.col AS CHAR)"),
(String(32), "CAST(t.col AS CHAR(32))"),
(Unicode(32), "CAST(t.col AS CHAR(32))"),
(CHAR(32), "CAST(t.col AS CHAR(32))"),
(m.MSString, "CAST(t.col AS CHAR)"),
(m.MSText, "CAST(t.col AS CHAR)"),
(m.MSTinyText, "CAST(t.col AS CHAR)"),
(m.MSMediumText, "CAST(t.col AS CHAR)"),
(m.MSLongText, "CAST(t.col AS CHAR)"),
(m.MSNChar, "CAST(t.col AS CHAR)"),
(m.MSNVarChar, "CAST(t.col AS CHAR)"),
(Binary, "CAST(t.col AS BINARY)"),
(BLOB, "CAST(t.col AS BINARY)"),
(m.MSBlob, "CAST(t.col AS BINARY)"),
(m.MSBlob(32), "CAST(t.col AS BINARY)"),
(m.MSTinyBlob, "CAST(t.col AS BINARY)"),
(m.MSMediumBlob, "CAST(t.col AS BINARY)"),
(m.MSLongBlob, "CAST(t.col AS BINARY)"),
(m.MSBinary, "CAST(t.col AS BINARY)"),
(m.MSBinary(32), "CAST(t.col AS BINARY)"),
(m.MSVarBinary, "CAST(t.col AS BINARY)"),
(m.MSVarBinary(32), "CAST(t.col AS BINARY)"),
# maybe this could be changed to something more DWIM, needs
# testing
(Boolean, "t.col"),
(BOOLEAN, "t.col"),
(m.MSBoolean, "t.col"),
(m.MSEnum, "t.col"),
(m.MSEnum("'1'", "'2'"), "t.col"),
(m.MSSet, "t.col"),
(m.MSSet("'1'", "'2'"), "t.col"),
]
for type_, expected in specs:
self.assert_compile(cast(t.c.col, type_), expected)
def setUp(self):
self.dialect = mysql.dialect()
self.reflector = mysql.MySQLSchemaReflector(
self.dialect.identifier_preparer)
def testforupdate(self):
self.runtest(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE")
self.runtest(table1.select(table1.c.myid==7, for_update="nowait"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE")
self.runtest(table1.select(table1.c.myid==7, for_update="nowait"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE NOWAIT", dialect=oracle.dialect())
self.runtest(table1.select(table1.c.myid==7, for_update="read"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE", dialect=mysql.dialect())
self.runtest(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = %s FOR UPDATE", dialect=mysql.dialect())
self.runtest(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE", dialect=oracle.dialect())
def setUp(self):
self.dialect = mysql.dialect()
self.reflector = mysql.MySQLSchemaReflector(
self.dialect.identifier_preparer)
