def test_column_computed(self, 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))",
)
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_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_schema_many_tokens_two(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, primary_key=True),
schema="[abc].[def].[efg].[hij]",
)
self.assert_compile(
select([tbl]),
"SELECT [abc].[def].[efg].hij.test.id "
"FROM [abc].[def].[efg].hij.test",
)
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_table_idx_explicit_nonclustered(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
)
idx = Index("myidx", tbl.c.x, tbl.c.y, mssql_clustered=False)
self.assert_compile(
schema.CreateIndex(idx),
"CREATE NONCLUSTERED INDEX myidx ON test (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_literal_binds_pgarray(self):
from sqlalchemy_1_3.dialects.postgresql import ARRAY, array
m = MetaData()
t = Table(
"t",
m,
Column("x", ARRAY(Integer), server_default=array([1, 2, 3])),
)
self.assert_compile(
CreateTable(t),
"CREATE TABLE t (x INTEGER[] DEFAULT ARRAY[1, 2, 3])",
dialect="postgresql",
)
def test_index_declaration_inline(self):
metadata = MetaData()
t1 = Table(
"t1",
metadata,
Column("x", Integer),
Column("y", Integer),
Index("foo", "x", "y"),
)
self.assert_compile(
schema.CreateIndex(list(t1.indexes)[0]),
"CREATE INDEX foo ON t1 (x, y)",
)
def test_reflection_with_table_options(self):
comment = r"""Comment types type speedily ' " \ '' Fun!"""
def_table = Table(
"mysql_def",
MetaData(testing.db),
Column("c1", Integer()),
mysql_engine="MEMORY",
comment=comment,
mysql_default_charset="utf8",
mysql_auto_increment="5",
mysql_avg_row_length="3",
mysql_password="******",
mysql_connection="fish",
)
def_table.create()
try:
reflected = Table("mysql_def", MetaData(testing.db), autoload=True)
finally:
def_table.drop()
assert def_table.kwargs["mysql_engine"] == "MEMORY"
assert def_table.comment == comment
assert def_table.kwargs["mysql_default_charset"] == "utf8"
assert def_table.kwargs["mysql_auto_increment"] == "5"
assert def_table.kwargs["mysql_avg_row_length"] == "3"
assert def_table.kwargs["mysql_password"] == "secret"
assert def_table.kwargs["mysql_connection"] == "fish"
assert reflected.kwargs["mysql_engine"] == "MEMORY"
assert reflected.comment == comment
assert reflected.kwargs["mysql_comment"] == comment
assert reflected.kwargs["mysql_default charset"] == "utf8"
assert reflected.kwargs["mysql_avg_row_length"] == "3"
assert reflected.kwargs["mysql_connection"] == "fish"
def test_misc_one(self):
metadata = MetaData(testing.db)
node_table = Table(
"node",
metadata,
Column("node_id", Integer, primary_key=True),
Column("name_index", Integer, nullable=True),
)
node_name_table = Table(
"node_name",
metadata,
Column("node_name_id", Integer, primary_key=True),
Column("node_id", Integer, ForeignKey("node.node_id")),
Column("host_id", Integer, ForeignKey("host.host_id")),
Column("name", String(64), nullable=False),
)
host_table = Table(
"host",
metadata,
Column("host_id", Integer, primary_key=True),
Column("hostname", String(64), nullable=False, unique=True),
)
metadata.create_all()
try:
node_table.insert().execute(node_id=1, node_index=5)
class Node(object):
pass
class NodeName(object):
pass
class Host(object):
pass
mapper(Node, node_table)
mapper(Host, host_table)
mapper(
NodeName,
node_name_table,
properties={
"node": relationship(Node, backref=backref("names")),
"host": relationship(Host),
},
)
sess = create_session()
assert sess.query(Node).get(1).names == []
finally:
metadata.drop_all()
def test_autoincrement_ignore_fk(self):
m = MetaData()
Table("y", m, Column("id", Integer(), primary_key=True))
x = Table(
"x",
m,
Column(
"id",
Integer(),
ForeignKey("y.id"),
autoincrement="ignore_fk",
primary_key=True,
),
)
assert x._autoincrement_column is x.c.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_autoincrement_reflected_from_server_default(self, connection):
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", Integer, server_default="5", primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
assert t._autoincrement_column is None
metadata.create_all(connection)
m2 = MetaData()
t2 = Table("x", m2, autoload_with=connection, implicit_returning=False)
assert t2._autoincrement_column is None
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_reflect_oltp_compression(self):
metadata = self.metadata
tbl = Table(
"test_compress",
metadata,
Column("data", Integer, primary_key=True),
oracle_compress="OLTP",
)
metadata.create_all()
m2 = MetaData(testing.db)
tbl = Table("test_compress", m2, autoload=True)
assert tbl.dialect_options["oracle"]["compress"] == "OLTP"
def setup_class(cls):
global metadata, foo
metadata = MetaData(testing.db)
foo = Table(
"foo",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String(100)),
)
metadata.create_all()
testing.db.execute(
"create function insert_foo(varchar) "
"returns integer as 'insert into foo(data) "
"values ($1);select 1;' language sql"
)
def test_table_name_that_is_greater_than_16_chars(self):
metadata = self.metadata
Table(
"ABCDEFGHIJKLMNOPQRSTUVWXYZ",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Index("foo_idx", "foo"),
)
metadata.create_all()
t = Table("ABCDEFGHIJKLMNOPQRSTUVWXYZ",
MetaData(),
autoload_with=testing.db)
eq_(t.name, "ABCDEFGHIJKLMNOPQRSTUVWXYZ")
def test_reflect_alt_table_owner_local_synonym(self):
meta = MetaData(testing.db)
parent = Table(
"%s_pt" % testing.config.test_schema,
meta,
autoload=True,
oracle_resolve_synonyms=True,
)
self.assert_compile(
parent.select(),
"SELECT %(test_schema)s_pt.id, "
"%(test_schema)s_pt.data FROM %(test_schema)s_pt" %
{"test_schema": testing.config.test_schema},
)
select([parent]).execute().fetchall()
def test_returning_insert_computed(self):
m = MetaData()
t1 = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
self.assert_compile(
t1.insert().values(id=1, foo=5).returning(t1.c.bar),
"INSERT INTO t1 (id, foo) VALUES (:id, :foo) "
"RETURNING t1.bar INTO :ret_0",
)
def test_bindparam_quote(self):
"""test that bound parameters take on quoting for reserved words,
column names quote flag enabled."""
# note: this is only in cx_oracle at the moment. not sure
# what other hypothetical oracle dialects might need
self.assert_compile(bindparam("option"), ':"option"')
self.assert_compile(bindparam("plain"), ":plain")
t = Table("s", MetaData(), Column("plain", Integer, quote=True))
self.assert_compile(
t.insert().values(plain=5),
'INSERT INTO s ("plain") VALUES (:"plain")',
)
self.assert_compile(t.update().values(plain=5),
'UPDATE s SET "plain"=:"plain"')
def test_join_condition_ignore_nonexistent_tables(self):
m = MetaData()
t1 = Table("t1", m, Column("id", Integer))
t2 = Table("t2", m, Column("id", Integer),
Column("t1id", ForeignKey("t1.id")))
with testing.expect_deprecated(
"The join_condition.ignore_nonexistent_tables "
"parameter is deprecated"):
join_cond = sql_util.join_condition(t1,
t2,
ignore_nonexistent_tables=True)
t1t2 = t1.join(t2)
assert t1t2.onclause.compare(join_cond)
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 setup_class(cls):
global metadata, cattable, matchtable
metadata = MetaData(testing.db)
cattable = Table(
"cattable",
metadata,
Column("id", Integer, primary_key=True),
Column("description", String(50)),
mysql_engine="MyISAM",
)
matchtable = Table(
"matchtable",
metadata,
Column("id", Integer, primary_key=True),
Column("title", String(200)),
Column("category_id", Integer, ForeignKey("cattable.id")),
mysql_engine="MyISAM",
)
metadata.create_all()
cattable.insert().execute(
[
{"id": 1, "description": "Python"},
{"id": 2, "description": "Ruby"},
]
)
matchtable.insert().execute(
[
{
"id": 1,
"title": "Agile Web Development with Ruby On Rails",
"category_id": 2,
},
{"id": 2, "title": "Dive Into Python", "category_id": 1},
{
"id": 3,
"title": "Programming Matz's Ruby",
"category_id": 2,
},
{
"id": 4,
"title": "The Definitive Guide to Django",
"category_id": 1,
},
{"id": 5, "title": "Python in a Nutshell", "category_id": 1},
]
)
def test_table_is_reflected(self):
from sqlalchemy_1_3.types import (
Integer,
Text,
BLOB,
String,
Date,
Time,
DateTime,
)
metadata = MetaData(testing.db)
table = Table("testtable", metadata, autoload=True)
eq_(
set(table.columns.keys()),
set([
"question",
"answer",
"remark",
"photo",
"d",
"t",
"dt",
"redundant",
]),
"Columns of reflected table didn't equal expected "
"columns",
)
eq_(table.c.question.primary_key, True)
# disabled per http://www.sqlalchemy.org/trac/ticket/1660
# eq_(table.c.question.sequence.name, 'gen_testtable_id')
assert isinstance(table.c.question.type, Integer)
eq_(table.c.question.server_default.arg.text, "42")
assert isinstance(table.c.answer.type, String)
assert table.c.answer.type.length == 255
eq_(table.c.answer.server_default.arg.text, "'no answer'")
assert isinstance(table.c.remark.type, Text)
eq_(table.c.remark.server_default.arg.text, "''")
assert isinstance(table.c.photo.type, BLOB)
assert table.c.redundant.server_default is None
# The following assume a Dialect 3 database
assert isinstance(table.c.d.type, Date)
assert isinstance(table.c.t.type, Time)
assert isinstance(table.c.dt.type, DateTime)
def test_reflect_basic_compression(self):
metadata = self.metadata
tbl = Table(
"test_compress",
metadata,
Column("data", Integer, primary_key=True),
oracle_compress=True,
)
metadata.create_all()
m2 = MetaData(testing.db)
tbl = Table("test_compress", m2, autoload=True)
# Don't hardcode the exact value, but it must be non-empty
assert tbl.dialect_options["oracle"]["compress"]
def _use_alter_fixture_one(self):
m = MetaData()
t1 = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("t2id", Integer, ForeignKey("t2.id")),
)
t2 = Table(
"t2",
m,
Column("id", Integer, primary_key=True),
Column("t1id", Integer, ForeignKey("t1.id")),
)
return m, t1, t2
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_returning_update_computed_warning(self):
m = MetaData()
t1 = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
with testing.expect_warnings(
"Computed columns don't work with Oracle UPDATE"):
self.assert_compile(
t1.update().values(id=1, foo=5).returning(t1.c.bar),
"UPDATE t1 SET id=:id, foo=:foo RETURNING t1.bar INTO :ret_0",
)