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_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_table_uc_clustering(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("x", Integer, autoincrement=False),
Column("y", Integer, autoincrement=False),
PrimaryKeyConstraint("x"),
UniqueConstraint("y", mssql_clustered=True),
)
self.assert_compile(
schema.CreateTable(tbl),
"CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NULL, "
"PRIMARY KEY (x), UNIQUE CLUSTERED (y))",
)
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_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_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_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_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_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_create_index_with_parser(self):
m = MetaData()
tbl = Table("testtbl", m, Column("data", String(255)))
idx = Index(
"test_idx1",
tbl.c.data,
mysql_length=10,
mysql_prefix="FULLTEXT",
mysql_with_parser="ngram",
)
self.assert_compile(
schema.CreateIndex(idx),
"CREATE FULLTEXT INDEX test_idx1 "
"ON testtbl (data(10)) WITH PARSER ngram",
)
def setup(self):
metadata = MetaData()
self.t1 = table(
"t1",
column("a", Integer),
column("b", String),
column("c", String),
)
self.t2 = Table(
"t2",
metadata,
Column("a", Integer),
Column("b", Integer),
Column("c", Integer),
schema="schema",
)
def test_indexes_cols_with_spaces(self):
metadata = self.metadata
t1 = Table(
"t",
metadata,
Column("x col", Integer, key="x"),
Column("y", Integer),
)
Index("foo", t1.c.x, t1.c.y)
metadata.create_all()
m2 = MetaData()
t2 = Table("t", m2, autoload=True, autoload_with=testing.db)
eq_(set(list(t2.indexes)[0].columns), set([t2.c["x col"], t2.c.y]))
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_all_orm_descriptors_pep520_classical(self):
class MyClass(object):
pass
from sqlalchemy_1_3.orm import mapper
from sqlalchemy_1_3 import Table, MetaData, Column, Integer
names = self._random_names()
m = MetaData()
t = Table("t", m, Column("id", Integer, primary_key=True),
*[Column(name, Integer) for name in names])
m = mapper(MyClass, t)
eq_(m.all_orm_descriptors.keys(), ["id"] + names)
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_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_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",
)
def test_create_drop_explicit(self):
metadata = MetaData()
table = Table("test_table", metadata, Column("foo", Integer))
for bind in (testing.db, testing.db.connect()):
for args in [([], {"bind": bind}), ([bind], {})]:
metadata.create_all(*args[0], **args[1])
assert table.exists(*args[0], **args[1])
metadata.drop_all(*args[0], **args[1])
table.create(*args[0], **args[1])
table.drop(*args[0], **args[1])
assert not table.exists(*args[0], **args[1])
def test_subquery_one(self):
# Lower case names, should not quote
metadata = MetaData()
t1 = Table("t1", metadata, Column("col1", Integer), schema="foo")
a = t1.select().alias("anon")
b = select([1], a.c.col1 == 2, from_obj=a)
self.assert_compile(
b,
"SELECT 1 "
"FROM ("
"SELECT "
"foo.t1.col1 AS col1 "
"FROM "
"foo.t1"
") AS anon "
"WHERE anon.col1 = :col1_1",
)
def setup(self):
meta = MetaData(testing.db)
global table
table = Table(
"tables",
meta,
Column(
"id",
Integer,
primary_key=True,
key="foo_id",
test_needs_autoincrement=True,
),
Column("data", String(20)),
)
table.create(checkfirst=True)
def test_reflect_alt_synonym_owner_local_table(self):
meta = MetaData(testing.db)
parent = Table(
"local_table",
meta,
autoload=True,
oracle_resolve_synonyms=True,
schema=testing.config.test_schema,
)
self.assert_compile(
parent.select(),
"SELECT %(test_schema)s.local_table.id, "
"%(test_schema)s.local_table.data "
"FROM %(test_schema)s.local_table" %
{"test_schema": testing.config.test_schema},
)
select([parent]).execute().fetchall()
def test_longstring(self):
metadata = MetaData(testing.db)
testing.db.execute(
"""
CREATE TABLE Z_TEST
(
ID NUMERIC(22) PRIMARY KEY,
ADD_USER VARCHAR2(20) NOT NULL
)
"""
)
try:
t = Table("z_test", metadata, autoload=True)
t.insert().execute(id=1.0, add_user="******")
assert t.select().execute().fetchall() == [(1, "foobar")]
finally:
testing.db.execute("DROP TABLE Z_TEST")
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_anticipate_no_pk_non_composite_pk_implicit_returning(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True, autoincrement=False),
Column("q", Integer),
)
d = postgresql.dialect()
d.implicit_returning = True
with expect_warnings("Column 't.x' is marked as a member.*"
"may not store NULL.$"):
self.assert_compile(
t.insert(),
"INSERT INTO t (q) VALUES (%(q)s)",
params={"q": 5},
dialect=d,
)
def test_anticipate_no_pk_composite_pk_prefetch(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True),
Column("y", Integer, primary_key=True),
)
d = postgresql.dialect()
d.implicit_returning = False
with expect_warnings("Column 't.y' is marked as a member.*"
"Note that as of SQLAlchemy 1.1,"):
self.assert_compile(
t.insert(),
"INSERT INTO t (x) VALUES (%(x)s)",
params={"x": 5},
dialect=d,
)
def test_insert_from_select_no_defaults(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer, default=func.foobar()),
)
table1 = self.tables.mytable
sel = select([table1.c.myid]).where(table1.c.name == "foo")
ins = table.insert().from_select(["id"], sel, include_defaults=False)
self.assert_compile(
ins,
"INSERT INTO sometable (id) SELECT mytable.myid "
"FROM mytable WHERE mytable.name = :name_1",
checkparams={"name_1": "foo"},
)
def test_lastrow_accessor_six(self):
metadata = MetaData()
self._test_lastrow_accessor(
Table(
"t6",
metadata,
Column(
"id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("bar", Integer, primary_key=True),
),
{"bar": 0},
{"id": 1, "bar": 0},
)
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(),
)
