def test_char_length(self):
self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)")
self.assert_compile(CHAR(50), "CHAR(50)")
def test_varchar_raise(self):
for type_ in (
String,
VARCHAR,
String(),
VARCHAR(),
Unicode,
Unicode(),
):
type_ = sqltypes.to_instance(type_)
assert_raises_message(
exc.CompileError,
"VARCHAR requires a length on dialect firebird",
type_.compile,
dialect=firebird.dialect(),
)
t1 = Table("sometable", MetaData(), Column("somecolumn", type_))
assert_raises_message(
exc.CompileError,
r"\(in table 'sometable', column 'somecolumn'\)\: "
r"(?:N)?VARCHAR requires a length on dialect firebird",
schema.CreateTable(t1).compile,
dialect=firebird.dialect(),
)
def define_tables(cls, metadata):
Table(
"users",
metadata,
Column(
"user_id", INT, primary_key=True, test_needs_autoincrement=True
),
Column("user_name", VARCHAR(20)),
test_needs_acid=True,
)
def setup_class(cls):
global users, metadata
metadata = MetaData()
users = Table(
"query_users",
metadata,
Column("user_id", INT, primary_key=True),
Column("user_name", VARCHAR(20)),
test_needs_acid=True,
)
users.create(testing.db)
def test_char_length(self):
metadata = self.metadata
t1 = Table(
"t1",
metadata,
Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)),
Column("c3", CHAR(200)),
Column("c4", NCHAR(180)),
)
t1.create()
m2 = MetaData(testing.db)
t2 = Table("t1", m2, autoload=True)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
eq_(t2.c.c4.type.length, 180)
def test_rollback_deadlock(self):
"""test that returning connections to the pool clears any object
locks."""
conn1 = testing.db.connect()
conn2 = testing.db.connect()
users = Table(
"deadlock_users",
metadata,
Column("user_id", INT, primary_key=True),
Column("user_name", VARCHAR(20)),
test_needs_acid=True,
)
users.create(conn1)
conn1.execute("select * from deadlock_users")
conn1.close()
# without auto-rollback in the connection pool's return() logic,
# this deadlocks in PostgreSQL, because conn1 is returned to the
# pool but still has a lock on "deadlock_users". comment out the
# rollback in pool/ConnectionFairy._close() to see !
users.drop(conn2)
conn2.close()
class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = oracle.OracleDialect()
def test_no_clobs_for_string_params(self):
"""test that simple string params get a DBAPI type of
VARCHAR, not CLOB. This is to prevent setinputsizes
from setting up cx_oracle.CLOBs on
string-based bind params [ticket:793]."""
class FakeDBAPI(object):
def __getattr__(self, attr):
return attr
dialect = oracle.OracleDialect()
dbapi = FakeDBAPI()
b = bindparam("foo", "hello world!")
eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")
b = bindparam("foo", "hello world!")
eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")
def test_long(self):
self.assert_compile(oracle.LONG(), "LONG")
@testing.combinations(
(Date(), cx_oracle._OracleDate),
(oracle.OracleRaw(), cx_oracle._OracleRaw),
(String(), String),
(VARCHAR(), cx_oracle._OracleString),
(DATE(), cx_oracle._OracleDate),
(oracle.DATE(), oracle.DATE),
(String(50), cx_oracle._OracleString),
(Unicode(), cx_oracle._OracleUnicodeStringCHAR),
(Text(), cx_oracle._OracleText),
(UnicodeText(), cx_oracle._OracleUnicodeTextCLOB),
(CHAR(), cx_oracle._OracleChar),
(NCHAR(), cx_oracle._OracleNChar),
(NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR),
(oracle.RAW(50), cx_oracle._OracleRaw),
)
def test_type_adapt(self, start, test):
dialect = cx_oracle.dialect()
assert isinstance(
start.dialect_impl(dialect), test
), "wanted %r got %r" % (test, start.dialect_impl(dialect))
@testing.combinations(
(String(), String),
(VARCHAR(), cx_oracle._OracleString),
(String(50), cx_oracle._OracleString),
(Unicode(), cx_oracle._OracleUnicodeStringNCHAR),
(Text(), cx_oracle._OracleText),
(UnicodeText(), cx_oracle._OracleUnicodeTextNCLOB),
(NCHAR(), cx_oracle._OracleNChar),
(NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR),
)
def test_type_adapt_nchar(self, start, test):
dialect = cx_oracle.dialect(use_nchar_for_unicode=True)
assert isinstance(
start.dialect_impl(dialect), test
), "wanted %r got %r" % (test, start.dialect_impl(dialect))
def test_raw_compile(self):
self.assert_compile(oracle.RAW(), "RAW")
self.assert_compile(oracle.RAW(35), "RAW(35)")
def test_char_length(self):
self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)")
self.assert_compile(CHAR(50), "CHAR(50)")
@testing.combinations(
(String(50), "VARCHAR2(50 CHAR)"),
(Unicode(50), "VARCHAR2(50 CHAR)"),
(NVARCHAR(50), "NVARCHAR2(50)"),
(VARCHAR(50), "VARCHAR(50 CHAR)"),
(oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
(oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
(String(), "VARCHAR2"),
(Unicode(), "VARCHAR2"),
(NVARCHAR(), "NVARCHAR2"),
(VARCHAR(), "VARCHAR"),
(oracle.NVARCHAR2(), "NVARCHAR2"),
(oracle.VARCHAR2(), "VARCHAR2"),
)
def test_varchar_types(self, typ, exp):
dialect = oracle.dialect()
self.assert_compile(typ, exp, dialect=dialect)
@testing.combinations(
(String(50), "VARCHAR2(50 CHAR)"),
(Unicode(50), "NVARCHAR2(50)"),
(NVARCHAR(50), "NVARCHAR2(50)"),
(VARCHAR(50), "VARCHAR(50 CHAR)"),
(oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
(oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
(String(), "VARCHAR2"),
(Unicode(), "NVARCHAR2"),
(NVARCHAR(), "NVARCHAR2"),
(VARCHAR(), "VARCHAR"),
(oracle.NVARCHAR2(), "NVARCHAR2"),
(oracle.VARCHAR2(), "VARCHAR2"),
)
def test_varchar_use_nchar_types(self, typ, exp):
dialect = oracle.dialect(use_nchar_for_unicode=True)
self.assert_compile(typ, exp, dialect=dialect)
@testing.combinations(
(oracle.INTERVAL(), "INTERVAL DAY TO SECOND"),
(oracle.INTERVAL(day_precision=3), "INTERVAL DAY(3) TO SECOND"),
(oracle.INTERVAL(second_precision=5), "INTERVAL DAY TO SECOND(5)"),
(
oracle.INTERVAL(day_precision=2, second_precision=5),
"INTERVAL DAY(2) TO SECOND(5)",
),
)
def test_interval(self, type_, expected):
self.assert_compile(type_, expected)
class SQLTest(fixtures.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([column("q")], **kw).compile(dialect=dialect))
eq_(gen(None), "SELECT q")
eq_(gen(True), "SELECT DISTINCT q")
eq_(gen(prefixes=["ALL"]), "SELECT ALL q")
eq_(gen(prefixes=["DISTINCTROW"]), "SELECT DISTINCTROW q")
# Interaction with MySQL prefix extensions
eq_(gen(None, ["straight_join"]), "SELECT straight_join q")
eq_(
gen(False, ["HIGH_PRIORITY", "SQL_SMALL_RESULT", "ALL"]),
"SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL q",
)
eq_(
gen(True, ["high_priority", sql.text("sql_cache")]),
"SELECT high_priority sql_cache DISTINCT q",
)
def test_backslash_escaping(self):
self.assert_compile(
sql.column("foo").like("bar", escape="\\"),
"foo LIKE %s ESCAPE '\\\\'",
)
dialect = mysql.dialect()
dialect._backslash_escapes = False
self.assert_compile(
sql.column("foo").like("bar", escape="\\"),
"foo LIKE %s ESCAPE '\\'",
dialect=dialect,
)
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 %s, %s",
{
"param_1": 20,
"param_2": 10
},
)
self.assert_compile(
select([t]).limit(10),
"SELECT t.col1, t.col2 FROM t LIMIT %s",
{"param_1": 10},
)
self.assert_compile(
select([t]).offset(10),
"SELECT t.col1, t.col2 FROM t LIMIT %s, 18446744073709551615",
{"param_1": 10},
)
@testing.combinations(
(String, ),
(VARCHAR, ),
(String(), ),
(VARCHAR(), ),
(NVARCHAR(), ),
(Unicode, ),
(Unicode(), ),
)
def test_varchar_raise(self, type_):
type_ = sqltypes.to_instance(type_)
assert_raises_message(
exc.CompileError,
"VARCHAR requires a length on dialect mysql",
type_.compile,
dialect=mysql.dialect(),
)
t1 = Table("sometable", MetaData(), Column("somecolumn", type_))
assert_raises_message(
exc.CompileError,
r"\(in table 'sometable', column 'somecolumn'\)\: "
r"(?:N)?VARCHAR requires a length on dialect mysql",
schema.CreateTable(t1).compile,
dialect=mysql.dialect(),
)
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_utc_timestamp_fsp(self):
self.assert_compile(
func.utc_timestamp(5),
"utc_timestamp(%s)",
checkparams={"utc_timestamp_1": 5},
)
def test_sysdate(self):
self.assert_compile(func.sysdate(), "SYSDATE()")
m = mysql
@testing.combinations(
(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)"),
# this is kind of sucky. thank you default arguments!
(NUMERIC, "CAST(t.col AS DECIMAL)"),
(DECIMAL, "CAST(t.col AS DECIMAL)"),
(Numeric, "CAST(t.col AS DECIMAL)"),
(m.MSNumeric, "CAST(t.col AS DECIMAL)"),
(m.MSDecimal, "CAST(t.col AS DECIMAL)"),
(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)"),
(DateTime, "CAST(t.col AS DATETIME)"),
(Date, "CAST(t.col AS DATE)"),
(m.MSTime, "CAST(t.col AS TIME)"),
(m.MSTimeStamp, "CAST(t.col AS DATETIME)"),
(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)"),
(m.CHAR(charset="utf8"), "CAST(t.col AS CHAR CHARACTER SET utf8)"),
(CLOB, "CAST(t.col AS CHAR)"),
(TEXT, "CAST(t.col AS CHAR)"),
(m.TEXT(charset="utf8"), "CAST(t.col AS CHAR CHARACTER SET utf8)"),
(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)"),
(LargeBinary, "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)"),
(Interval, "CAST(t.col AS DATETIME)"),
)
def test_cast(self, type_, expected):
t = sql.table("t", sql.column("col"))
self.assert_compile(cast(t.c.col, type_), expected)
def test_cast_type_decorator(self):
class MyInteger(sqltypes.TypeDecorator):
impl = Integer
type_ = MyInteger()
t = sql.table("t", sql.column("col"))
self.assert_compile(cast(t.c.col, type_),
"CAST(t.col AS SIGNED INTEGER)")
def test_cast_literal_bind(self):
expr = cast(column("foo", Integer) + 5, Integer())
self.assert_compile(expr,
"CAST(foo + 5 AS SIGNED INTEGER)",
literal_binds=True)
def test_unsupported_cast_literal_bind(self):
expr = cast(column("foo", Integer) + 5, Float)
with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
self.assert_compile(expr, "(foo + 5)", literal_binds=True)
dialect = mysql.MySQLDialect()
dialect.server_version_info = (3, 9, 8)
with expect_warnings("Current MySQL version does not support CAST"):
eq_(
str(
expr.compile(dialect=dialect,
compile_kwargs={"literal_binds": True})),
"(foo + 5)",
)
m = mysql
@testing.combinations(
(m.MSBit, "t.col"),
(FLOAT, "t.col"),
(Float, "t.col"),
(m.MSFloat, "t.col"),
(m.MSDouble, "t.col"),
(m.MSReal, "t.col"),
(m.MSYear, "t.col"),
(m.MSYear(2), "t.col"),
(Boolean, "t.col"),
(BOOLEAN, "t.col"),
(m.MSEnum, "t.col"),
(m.MSEnum("1", "2"), "t.col"),
(m.MSSet, "t.col"),
(m.MSSet("1", "2"), "t.col"),
)
def test_unsupported_casts(self, type_, expected):
t = sql.table("t", sql.column("col"))
with expect_warnings("Datatype .* does not support CAST on MySQL;"):
self.assert_compile(cast(t.c.col, type_), expected)
def test_no_cast_pre_4(self):
self.assert_compile(cast(Column("foo", Integer), String),
"CAST(foo AS CHAR)")
dialect = mysql.dialect()
dialect.server_version_info = (3, 2, 3)
with expect_warnings("Current MySQL version does not support CAST;"):
self.assert_compile(cast(Column("foo", Integer), String),
"foo",
dialect=dialect)
def test_cast_grouped_expression_non_castable(self):
with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
self.assert_compile(cast(sql.column("x") + sql.column("y"), Float),
"(x + y)")
def test_cast_grouped_expression_pre_4(self):
dialect = mysql.dialect()
dialect.server_version_info = (3, 2, 3)
with expect_warnings("Current MySQL version does not support CAST;"):
self.assert_compile(
cast(sql.column("x") + sql.column("y"), Integer),
"(x + y)",
dialect=dialect,
)
def test_extract(self):
t = sql.table("t", sql.column("col1"))
for field in "year", "month", "day":
self.assert_compile(
select([extract(field, t.c.col1)]),
"SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field,
)
# millsecondS to millisecond
self.assert_compile(
select([extract("milliseconds", t.c.col1)]),
"SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t",
)
def test_too_long_index(self):
exp = "ix_zyrenian_zyme_zyzzogeton_zyzzogeton_zyrenian_zyme_zyz_5cd2"
tname = "zyrenian_zyme_zyzzogeton_zyzzogeton"
cname = "zyrenian_zyme_zyzzogeton_zo"
t1 = Table(tname, MetaData(), Column(cname, Integer, index=True))
ix1 = list(t1.indexes)[0]
self.assert_compile(
schema.CreateIndex(ix1),
"CREATE INDEX %s "
"ON %s (%s)" % (exp, tname, cname),
)
def test_innodb_autoincrement(self):
t1 = Table(
"sometable",
MetaData(),
Column("assigned_id",
Integer(),
primary_key=True,
autoincrement=False),
Column("id", Integer(), primary_key=True, autoincrement=True),
mysql_engine="InnoDB",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE sometable (assigned_id "
"INTEGER NOT NULL, id INTEGER NOT NULL "
"AUTO_INCREMENT, PRIMARY KEY (id, assigned_id)"
")ENGINE=InnoDB",
)
t1 = Table(
"sometable",
MetaData(),
Column("assigned_id",
Integer(),
primary_key=True,
autoincrement=True),
Column("id", Integer(), primary_key=True, autoincrement=False),
mysql_engine="InnoDB",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE sometable (assigned_id "
"INTEGER NOT NULL AUTO_INCREMENT, id "
"INTEGER NOT NULL, PRIMARY KEY "
"(assigned_id, id))ENGINE=InnoDB",
)
def test_innodb_autoincrement_reserved_word_column_name(self):
t1 = Table(
"sometable",
MetaData(),
Column("id", Integer(), primary_key=True, autoincrement=False),
Column("order", Integer(), primary_key=True, autoincrement=True),
mysql_engine="InnoDB",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE sometable ("
"id INTEGER NOT NULL, "
"`order` INTEGER NOT NULL AUTO_INCREMENT, "
"PRIMARY KEY (`order`, id)"
")ENGINE=InnoDB",
)
def test_create_table_with_partition(self):
t1 = Table(
"testtable",
MetaData(),
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("other_id",
Integer(),
primary_key=True,
autoincrement=False),
mysql_partitions="2",
mysql_partition_by="KEY(other_id)",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE testtable ("
"id INTEGER NOT NULL AUTO_INCREMENT, "
"other_id INTEGER NOT NULL, "
"PRIMARY KEY (id, other_id)"
")PARTITION BY KEY(other_id) PARTITIONS 2",
)
def test_create_table_with_subpartition(self):
t1 = Table(
"testtable",
MetaData(),
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("other_id",
Integer(),
primary_key=True,
autoincrement=False),
mysql_partitions="2",
mysql_partition_by="KEY(other_id)",
mysql_subpartition_by="HASH(some_expr)",
mysql_subpartitions="2",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE testtable ("
"id INTEGER NOT NULL AUTO_INCREMENT, "
"other_id INTEGER NOT NULL, "
"PRIMARY KEY (id, other_id)"
")PARTITION BY KEY(other_id) PARTITIONS 2 "
"SUBPARTITION BY HASH(some_expr) SUBPARTITIONS 2",
)
def test_create_table_with_partition_hash(self):
t1 = Table(
"testtable",
MetaData(),
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("other_id",
Integer(),
primary_key=True,
autoincrement=False),
mysql_partitions="2",
mysql_partition_by="HASH(other_id)",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE testtable ("
"id INTEGER NOT NULL AUTO_INCREMENT, "
"other_id INTEGER NOT NULL, "
"PRIMARY KEY (id, other_id)"
")PARTITION BY HASH(other_id) PARTITIONS 2",
)
def test_create_table_with_partition_and_other_opts(self):
t1 = Table(
"testtable",
MetaData(),
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("other_id",
Integer(),
primary_key=True,
autoincrement=False),
mysql_stats_sample_pages="2",
mysql_partitions="2",
mysql_partition_by="HASH(other_id)",
)
self.assert_compile(
schema.CreateTable(t1),
"CREATE TABLE testtable ("
"id INTEGER NOT NULL AUTO_INCREMENT, "
"other_id INTEGER NOT NULL, "
"PRIMARY KEY (id, other_id)"
")STATS_SAMPLE_PAGES=2 PARTITION BY HASH(other_id) PARTITIONS 2",
)
def test_create_table_with_collate(self):
# issue #5411
t1 = Table(
"testtable",
MetaData(),
Column("id", Integer(), primary_key=True, autoincrement=True),
mysql_engine="InnoDB",
mysql_collate="utf8_icelandic_ci",
mysql_charset="utf8",
)
first_part = ("CREATE TABLE testtable ("
"id INTEGER NOT NULL AUTO_INCREMENT, "
"PRIMARY KEY (id))")
try:
self.assert_compile(
schema.CreateTable(t1),
first_part +
"ENGINE=InnoDB CHARSET=utf8 COLLATE utf8_icelandic_ci",
)
except AssertionError:
self.assert_compile(
schema.CreateTable(t1),
first_part +
"CHARSET=utf8 ENGINE=InnoDB COLLATE utf8_icelandic_ci",
)
def test_inner_join(self):
t1 = table("t1", column("x"))
t2 = table("t2", column("y"))
self.assert_compile(t1.join(t2, t1.c.x == t2.c.y),
"t1 INNER JOIN t2 ON t1.x = t2.y")
def test_outer_join(self):
t1 = table("t1", column("x"))
t2 = table("t2", column("y"))
self.assert_compile(
t1.outerjoin(t2, t1.c.x == t2.c.y),
"t1 LEFT OUTER JOIN t2 ON t1.x = t2.y",
)
def test_full_outer_join(self):
t1 = table("t1", column("x"))
t2 = table("t2", column("y"))
self.assert_compile(
t1.outerjoin(t2, t1.c.x == t2.c.y, full=True),
"t1 FULL OUTER JOIN t2 ON t1.x = t2.y",
)
class BuggyDomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
"""Test Firebird domains (and some other reflection bumps),
see [ticket:1663] and http://tracker.firebirdsql.org/browse/CORE-356"""
__only_on__ = "firebird"
# NB: spacing and newlines are *significant* here!
# PS: this test is superfluous on recent FB, where the issue 356 is
# probably fixed...
AUTOINC_DM = """\
CREATE DOMAIN AUTOINC_DM
AS
NUMERIC(18,0)
"""
MONEY_DM = """\
CREATE DOMAIN MONEY_DM
AS
NUMERIC(15,2)
DEFAULT 0
CHECK (VALUE BETWEEN -
9999999999999.99 AND +9999999999999.99)
"""
NOSI_DM = """\
CREATE DOMAIN
NOSI_DM AS
CHAR(1)
DEFAULT 'N'
NOT NULL
CHECK (VALUE IN
('S', 'N'))
"""
RIT_TESORERIA_CAPITOLO_DM = """\
CREATE DOMAIN RIT_TESORERIA_CAPITOLO_DM
AS
VARCHAR(6)
CHECK ((VALUE IS NULL) OR (VALUE =
UPPER(VALUE)))
"""
DEF_ERROR_TB = """\
CREATE TABLE DEF_ERROR (
RITENUTAMOV_ID AUTOINC_DM
NOT NULL,
RITENUTA MONEY_DM,
INTERESSI MONEY_DM
DEFAULT
0,
STAMPATO_MODULO NOSI_DM DEFAULT 'S',
TESORERIA_CAPITOLO
RIT_TESORERIA_CAPITOLO_DM)
"""
DEF_ERROR_NODOM_TB = """\
CREATE TABLE
DEF_ERROR_NODOM (
RITENUTAMOV_ID INTEGER NOT NULL,
RITENUTA NUMERIC(15,2) DEFAULT 0,
INTERESSI NUMERIC(15,2)
DEFAULT
0,
STAMPATO_MODULO CHAR(1) DEFAULT 'S',
TESORERIA_CAPITOLO
CHAR(1))
"""
DOM_ID = """
CREATE DOMAIN DOM_ID INTEGER NOT NULL
"""
TABLE_A = """\
CREATE TABLE A (
ID DOM_ID /* INTEGER NOT NULL */ DEFAULT 0 )
"""
# the 'default' keyword is lower case here
TABLE_B = """\
CREATE TABLE B (
ID DOM_ID /* INTEGER NOT NULL */ default 0 )
"""
@classmethod
def setup_class(cls):
con = testing.db.connect()
con.execute(cls.AUTOINC_DM)
con.execute(cls.MONEY_DM)
con.execute(cls.NOSI_DM)
con.execute(cls.RIT_TESORERIA_CAPITOLO_DM)
con.execute(cls.DEF_ERROR_TB)
con.execute(cls.DEF_ERROR_NODOM_TB)
con.execute(cls.DOM_ID)
con.execute(cls.TABLE_A)
con.execute(cls.TABLE_B)
@classmethod
def teardown_class(cls):
con = testing.db.connect()
con.execute("DROP TABLE a")
con.execute("DROP TABLE b")
con.execute("DROP DOMAIN dom_id")
con.execute("DROP TABLE def_error_nodom")
con.execute("DROP TABLE def_error")
con.execute("DROP DOMAIN rit_tesoreria_capitolo_dm")
con.execute("DROP DOMAIN nosi_dm")
con.execute("DROP DOMAIN money_dm")
con.execute("DROP DOMAIN autoinc_dm")
def test_tables_are_reflected_same_way(self):
metadata = MetaData(testing.db)
table_dom = Table("def_error", metadata, autoload=True)
table_nodom = Table("def_error_nodom", metadata, autoload=True)
eq_(
table_dom.c.interessi.server_default.arg.text,
table_nodom.c.interessi.server_default.arg.text,
)
eq_(
table_dom.c.ritenuta.server_default.arg.text,
table_nodom.c.ritenuta.server_default.arg.text,
)
eq_(
table_dom.c.stampato_modulo.server_default.arg.text,
table_nodom.c.stampato_modulo.server_default.arg.text,
)
def test_intermixed_comment(self):
metadata = MetaData(testing.db)
table_a = Table("a", metadata, autoload=True)
eq_(table_a.c.id.server_default.arg.text, "0")
def test_lowercase_default_name(self):
metadata = MetaData(testing.db)
table_b = Table("b", metadata, autoload=True)
eq_(table_b.c.id.server_default.arg.text, "0")