def test_create_pk_with_using(self):
m = MetaData()
tbl = Table(
"testtbl",
m,
Column("data", String(255)),
PrimaryKeyConstraint("data", mysql_using="btree"),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE testtbl (data VARCHAR(255) NOT NULL, "
"PRIMARY KEY (data) USING btree)",
)
def test_column_computed_raises(self, persisted):
m = MetaData()
t = Table(
"t",
m,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", persisted=persisted)),
)
assert_raises_message(
exc.CompileError,
"Firebird computed columns do not support a persistence method",
schema.CreateTable(t).compile,
dialect=firebird.dialect(),
)
def test_column_computed(self, text, persisted):
m = MetaData()
kwargs = {"persisted": persisted} if persisted != "ignore" else {}
t = Table(
"t",
m,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", **kwargs)),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE t (x INTEGER, y INTEGER GENERATED "
"ALWAYS AS (x + 2)%s)" % text,
)
def test_identity_separate_from_primary_key(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, autoincrement=False, primary_key=True),
Column("x", Integer, autoincrement=True),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (id INTEGER NOT NULL, "
"x INTEGER NOT NULL IDENTITY(1,1), "
"PRIMARY KEY (id))",
)
def test_table_uc_explicit_nonclustered(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
UniqueConstraint("x", "y", mssql_clustered=False),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (x INTEGER NULL, y INTEGER NULL, "
"UNIQUE NONCLUSTERED (x, y))",
)
def test_table_pkc_clustering(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
PrimaryKeyConstraint("x", "y", mssql_clustered=True),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NOT NULL, "
"PRIMARY KEY CLUSTERED (x, y))",
)
def test_sequence_start_0(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, Sequence("", 0), primary_key=True),
)
with testing.expect_deprecated(
"Use of Sequence with SQL Server in order to affect "):
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (id INTEGER NOT NULL IDENTITY(0,1), "
"PRIMARY KEY (id))",
)
def test_identity_illegal_two_autoincrements(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, autoincrement=True),
Column("id2", Integer, autoincrement=True),
)
# this will be rejected by the database, just asserting this is what
# the two autoincrements will do right now
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,1), "
"id2 INTEGER NOT NULL IDENTITY(1,1))",
)
def test_column_level_ck_name(self):
t = Table(
"tbl",
MetaData(),
Column(
"a",
Integer,
CheckConstraint("a > 5", name="ck_a_greater_five"),
),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER CONSTRAINT "
"ck_a_greater_five CHECK (a > 5))",
)
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_table_options(self):
m = MetaData()
t = Table(
"foo",
m,
Column("x", Integer),
prefixes=["GLOBAL TEMPORARY"],
oracle_on_commit="PRESERVE ROWS",
)
self.assert_compile(
schema.CreateTable(t),
"CREATE GLOBAL TEMPORARY TABLE "
"foo (x INTEGER) ON COMMIT PRESERVE ROWS",
)
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_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_deferrable_column_fk(self):
t = Table(
"tbl",
MetaData(),
Column("a", Integer),
Column(
"b",
Integer,
ForeignKey("tbl.a", deferrable=True, initially="DEFERRED"),
),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER, b INTEGER, "
"FOREIGN KEY(b) REFERENCES tbl "
"(a) DEFERRABLE INITIALLY DEFERRED)",
)
def test_deferrable_column_check(self):
t = Table(
"tbl",
MetaData(),
Column("a", Integer),
Column(
"b",
Integer,
CheckConstraint("a < b", deferrable=True,
initially="DEFERRED"),
),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER, b INTEGER CHECK (a < b) "
"DEFERRABLE INITIALLY DEFERRED)",
)
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_deferrable_initially_kw_not_ignored(self):
m = MetaData()
Table("t1", m, Column("id", Integer, primary_key=True))
t2 = Table(
"t2",
m,
Column(
"id",
Integer,
ForeignKey("t1.id", deferrable=True, initially="DEFERRED"),
primary_key=True,
),
)
self.assert_compile(
schema.CreateTable(t2),
"CREATE TABLE t2 (id INTEGER NOT NULL, "
"PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES t1 (id) "
"DEFERRABLE INITIALLY DEFERRED)",
)
def test_fk_match_clause(self):
t = Table(
"tbl",
MetaData(),
Column("a", Integer),
Column("b", Integer, ForeignKey("tbl.a", match="SIMPLE")),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER, b INTEGER, "
"FOREIGN KEY(b) REFERENCES tbl "
"(a) MATCH SIMPLE)",
)
self.assert_compile(
schema.AddConstraint(list(t.foreign_keys)[0].constraint),
"ALTER TABLE tbl ADD FOREIGN KEY(b) "
"REFERENCES tbl (a) MATCH SIMPLE",
)
def test_match_kw_raises(self):
m = MetaData()
Table("t1", m, Column("id", Integer, primary_key=True))
t2 = Table(
"t2",
m,
Column(
"id",
Integer,
ForeignKey("t1.id", match="XYZ"),
primary_key=True,
),
)
assert_raises_message(
exc.CompileError,
"MySQL ignores the 'MATCH' keyword while at the same time causes "
"ON UPDATE/ON DELETE clauses to be ignored.",
schema.CreateTable(t2).compile,
dialect=mysql.dialect(),
)
def test_render_ck_constraint_inline(self):
t, t2 = self._constraint_create_fixture()
CheckConstraint(
"a < b",
name="my_test_constraint",
deferrable=True,
initially="DEFERRED",
table=t,
)
# before we create an AddConstraint,
# the CONSTRAINT comes out inline
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl ("
"a INTEGER, "
"b INTEGER, "
"CONSTRAINT my_test_constraint CHECK (a < b) "
"DEFERRABLE INITIALLY DEFERRED"
")",
)
def test_money(self):
"""Exercise type specification for money types."""
columns = [
(mssql.MSMoney, [], {}, "MONEY"),
(mssql.MSSmallMoney, [], {}, "SMALLMONEY"),
]
metadata = MetaData()
table_args = ["test_mssql_money", metadata]
for index, spec in enumerate(columns):
type_, args, kw, res = spec
table_args.append(
Column("c%s" % index, type_(*args, **kw), nullable=None))
money_table = Table(*table_args)
dialect = mssql.dialect()
gen = dialect.ddl_compiler(dialect, schema.CreateTable(money_table))
for col in money_table.c:
index = int(col.name[1:])
testing.eq_(
gen.get_column_specification(col),
"%s %s" % (col.name, columns[index][3]),
)
self.assert_(repr(col))
def test_external_ck_constraint_cancels_internal(self):
t, t2 = self._constraint_create_fixture()
constraint = CheckConstraint(
"a < b",
name="my_test_constraint",
deferrable=True,
initially="DEFERRED",
table=t,
)
schema.AddConstraint(constraint)
# once we make an AddConstraint,
# inline compilation of the CONSTRAINT
# is disabled
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl ("
"a INTEGER, "
"b INTEGER"
")",
)
def test_numeric(self):
"Exercise type specification and options for numeric types."
columns = [
# column type, args, kwargs, expected ddl
(types.NUMERIC, [], {}, "NUMERIC"),
(types.NUMERIC, [None], {}, "NUMERIC"),
(types.NUMERIC, [12, 4], {}, "NUMERIC(12, 4)"),
(types.Float, [], {}, "FLOAT"),
(types.Float, [None], {}, "FLOAT"),
(types.Float, [12], {}, "FLOAT(12)"),
(mssql.MSReal, [], {}, "REAL"),
(types.Integer, [], {}, "INTEGER"),
(types.BigInteger, [], {}, "BIGINT"),
(mssql.MSTinyInteger, [], {}, "TINYINT"),
(types.SmallInteger, [], {}, "SMALLINT"),
]
metadata = MetaData()
table_args = ["test_mssql_numeric", metadata]
for index, spec in enumerate(columns):
type_, args, kw, res = spec
table_args.append(
Column("c%s" % index, type_(*args, **kw), nullable=None))
numeric_table = Table(*table_args)
dialect = mssql.dialect()
gen = dialect.ddl_compiler(dialect, schema.CreateTable(numeric_table))
for col in numeric_table.c:
index = int(col.name[1:])
testing.eq_(
gen.get_column_specification(col),
"%s %s" % (col.name, columns[index][3]),
)
self.assert_(repr(col))
def test_char(self):
"""Exercise COLLATE-ish options on string types."""
columns = [
(mssql.MSChar, [], {}, "CHAR"),
(mssql.MSChar, [1], {}, "CHAR(1)"),
(
mssql.MSChar,
[1],
{
"collation": "Latin1_General_CI_AS"
},
"CHAR(1) COLLATE Latin1_General_CI_AS",
),
(mssql.MSNChar, [], {}, "NCHAR"),
(mssql.MSNChar, [1], {}, "NCHAR(1)"),
(
mssql.MSNChar,
[1],
{
"collation": "Latin1_General_CI_AS"
},
"NCHAR(1) COLLATE Latin1_General_CI_AS",
),
(mssql.MSString, [], {}, "VARCHAR(max)"),
(mssql.MSString, [1], {}, "VARCHAR(1)"),
(
mssql.MSString,
[1],
{
"collation": "Latin1_General_CI_AS"
},
"VARCHAR(1) COLLATE Latin1_General_CI_AS",
),
(mssql.MSNVarchar, [], {}, "NVARCHAR(max)"),
(mssql.MSNVarchar, [1], {}, "NVARCHAR(1)"),
(
mssql.MSNVarchar,
[1],
{
"collation": "Latin1_General_CI_AS"
},
"NVARCHAR(1) COLLATE Latin1_General_CI_AS",
),
(mssql.MSText, [], {}, "TEXT"),
(
mssql.MSText,
[],
{
"collation": "Latin1_General_CI_AS"
},
"TEXT COLLATE Latin1_General_CI_AS",
),
(mssql.MSNText, [], {}, "NTEXT"),
(
mssql.MSNText,
[],
{
"collation": "Latin1_General_CI_AS"
},
"NTEXT COLLATE Latin1_General_CI_AS",
),
]
metadata = MetaData()
table_args = ["test_mssql_charset", metadata]
for index, spec in enumerate(columns):
type_, args, kw, res = spec
table_args.append(
Column("c%s" % index, type_(*args, **kw), nullable=None))
charset_table = Table(*table_args)
dialect = mssql.dialect()
gen = dialect.ddl_compiler(dialect, schema.CreateTable(charset_table))
for col in charset_table.c:
index = int(col.name[1:])
testing.eq_(
gen.get_column_specification(col),
"%s %s" % (col.name, columns[index][3]),
)
self.assert_(repr(col))
