def test_table_overrides_metadata_create(self, connection):
metadata = self.metadata
Sequence("s1", metadata=metadata)
s2 = Sequence("s2", metadata=metadata)
s3 = Sequence("s3")
t = Table("t", metadata, Column("c", Integer, s3, primary_key=True))
assert s3.metadata is metadata
t.create(connection, checkfirst=True)
s3.drop(connection)
# 't' is created, and 's3' won't be
# re-created since it's linked to 't'.
# 's1' and 's2' are, however.
metadata.create_all(connection)
assert self._has_sequence(connection, "s1")
assert self._has_sequence(connection, "s2")
assert not self._has_sequence(connection, "s3")
s2.drop(connection)
assert self._has_sequence(connection, "s1")
assert not self._has_sequence(connection, "s2")
metadata.drop_all(connection)
assert not self._has_sequence(connection, "s1")
assert not self._has_sequence(connection, "s2")
def _baseline_1_create_tables(self):
Table(
"Zoo",
self.metadata,
Column(
"ID",
Integer,
Sequence("zoo_id_seq"),
primary_key=True,
index=True,
),
Column("Name", Unicode(255)),
Column("Founded", Date),
Column("Opens", Time),
Column("LastEscape", DateTime),
Column("Admission", Float),
)
Table(
"Animal",
self.metadata,
Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True),
Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True),
Column("Name", Unicode(100)),
Column("Species", Unicode(100)),
Column("Legs", Integer, default=4),
Column("LastEscape", DateTime),
Column("Lifespan", Float(4)),
Column("MotherID", Integer, ForeignKey("Animal.ID")),
Column("PreferredFoodID", Integer),
Column("AlternateFoodID", Integer),
)
self.metadata.create_all()
def _table_seq_fixture(self):
m = MetaData()
s1 = Sequence("s1")
s2 = Sequence("s2")
t1 = Table("t1", m, Column("x", Integer, s1, primary_key=True))
t2 = Table("t2", m, Column("x", Integer, s2, primary_key=True))
return m, t1, t2, s1, s2
def test_basic(self):
seq = Sequence("my_seq_no_schema")
dialect = oracle.OracleDialect()
assert (dialect.identifier_preparer.format_sequence(seq) ==
"my_seq_no_schema")
seq = Sequence("my_seq", schema="some_schema")
assert (dialect.identifier_preparer.format_sequence(seq) ==
"some_schema.my_seq")
seq = Sequence("My_Seq", schema="Some_Schema")
assert (dialect.identifier_preparer.format_sequence(seq) ==
'"Some_Schema"."My_Seq"')
def test_checksfor_sequence(self):
meta1 = self.metadata
seq = Sequence("fooseq")
t = Table("mytable", meta1, Column("col1", Integer, seq))
seq.drop()
testing.db.execute("CREATE SEQUENCE fooseq")
t.create(checkfirst=True)
def define_tables(cls, metadata):
global foo, bar, foo_bar
foo = Table(
"foo",
metadata,
Column(
"id",
Integer,
Sequence("foo_id_seq", optional=True),
primary_key=True,
),
Column("data", String(20)),
)
bar = Table(
"bar",
metadata,
Column("bid", Integer, ForeignKey("foo.id"), primary_key=True),
)
foo_bar = Table(
"foo_bar",
metadata,
Column("foo_id", Integer, ForeignKey("foo.id")),
Column("bar_id", Integer, ForeignKey("bar.bid")),
)
def define_tables(cls, metadata):
global principals
global users
global groups
global user_group_map
principals = Table(
"principals",
metadata,
Column(
"principal_id",
Integer,
Sequence("principal_id_seq", optional=False),
primary_key=True,
),
Column("name", String(50), nullable=False),
)
users = Table(
"prin_users",
metadata,
Column(
"principal_id",
Integer,
ForeignKey("principals.principal_id"),
primary_key=True,
),
Column("password", String(50), nullable=False),
Column("email", String(50), nullable=False),
Column("login_id", String(50), nullable=False),
)
groups = Table(
"prin_groups",
metadata,
Column(
"principal_id",
Integer,
ForeignKey("principals.principal_id"),
primary_key=True,
),
)
user_group_map = Table(
"prin_user_group_map",
metadata,
Column(
"user_id",
Integer,
ForeignKey("prin_users.principal_id"),
primary_key=True,
),
Column(
"group_id",
Integer,
ForeignKey("prin_groups.principal_id"),
primary_key=True,
),
)
def define_tables(cls, metadata):
Table(
"foo",
metadata,
Column("id", Integer, Sequence("t_id_seq"), primary_key=True),
Column("data", String(50)),
Column("x", Integer),
)
def test_checkfirst_sequence(self, connection):
s = Sequence("my_sequence")
s.create(connection, checkfirst=False)
assert self._has_sequence(connection, "my_sequence")
s.create(connection, checkfirst=True)
s.drop(connection, checkfirst=False)
assert not self._has_sequence(connection, "my_sequence")
s.drop(connection, checkfirst=True)
def _baseline_1_create_tables(self):
zoo = Table(
"Zoo",
self.metadata,
Column(
"ID",
Integer,
Sequence("zoo_id_seq"),
primary_key=True,
index=True,
),
Column("Name", Unicode(255)),
Column("Founded", Date),
Column("Opens", Time),
Column("LastEscape", DateTime),
Column("Admission", Float),
)
animal = Table(
"Animal",
self.metadata,
Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True),
Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True),
Column("Name", Unicode(100)),
Column("Species", Unicode(100)),
Column("Legs", Integer, default=4),
Column("LastEscape", DateTime),
Column("Lifespan", Float(4)),
Column("MotherID", Integer, ForeignKey("Animal.ID")),
Column("PreferredFoodID", Integer),
Column("AlternateFoodID", Integer),
)
self.metadata.create_all()
global Zoo, Animal
class Zoo(object):
def __init__(self, **kwargs):
for k, v in kwargs.items():
setattr(self, k, v)
class Animal(object):
def __init__(self, **kwargs):
for k, v in kwargs.items():
setattr(self, k, v)
mapper(Zoo, zoo)
mapper(Animal, animal)
def test_explicit_sequence(self):
t = self._fixture()
self._test(
t.insert().values(
id=func.next_value(Sequence("t_id_seq")), data="data", x=5
),
(1, "data", 5),
)
def define_tables(cls, metadata):
m = metadata
s = Sequence("t_seq", metadata=m)
Table(
"t_seq_test",
m,
Column("id", Integer, s, server_default=s.next_value()),
Column("data", String(50)),
)
s2 = Sequence("t_seq_2", metadata=m)
Table(
"t_seq_test_2",
m,
Column("id", Integer, server_default=s2.next_value()),
Column("data", String(50)),
)
def test_checkfirst_metadata(self, connection):
m = MetaData()
Sequence("my_sequence", metadata=m)
m.create_all(connection, checkfirst=False)
assert self._has_sequence(connection, "my_sequence")
m.create_all(connection, checkfirst=True)
m.drop_all(connection, checkfirst=False)
assert not self._has_sequence(connection, "my_sequence")
m.drop_all(connection, checkfirst=True)
def test_sequence_insert(self):
table = Table(
"testtable",
self.metadata,
Column("id", Integer, Sequence("my_seq"), primary_key=True),
Column("data", String(30)),
)
self.metadata.create_all()
self._assert_data_with_sequence(table, "my_seq")
def test_sequences_are_integers(self):
seq = Sequence("foo_seq")
seq.create(testing.db)
try:
val = testing.db.execute(seq)
eq_(val, 1)
assert type(val) is int
finally:
seq.drop(testing.db)
def test_func_embedded_valuesbase(self, connection):
"""test can use next_value() in values() of _ValuesBase"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
s = Sequence("my_sequence")
connection.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(connection.scalar(t1.select()))
def test_checkfirst_table(self, connection):
m = MetaData()
s = Sequence("my_sequence")
t = Table("t", m, Column("c", Integer, s, primary_key=True))
t.create(connection, checkfirst=False)
assert self._has_sequence(connection, "my_sequence")
t.create(connection, checkfirst=True)
t.drop(connection, checkfirst=False)
assert not self._has_sequence(connection, "my_sequence")
t.drop(connection, checkfirst=True)
def setup(self):
meta = MetaData(testing.db)
global table, seq
seq = Sequence("tid_seq")
table = Table(
"tables",
meta,
Column("id", Integer, seq, primary_key=True),
Column("data", String(50)),
)
table.create(checkfirst=True)
def test_nextval_unsupported(self):
"""test next_value() used on non-sequence platform
raises NotImplementedError."""
s = Sequence("my_seq")
d = sqlite.dialect()
assert_raises_message(
NotImplementedError,
"Dialect 'sqlite' does not support sequence increments.",
s.next_value().compile,
dialect=d,
)
def test_inserted_pk_no_returning_no_lastrowid(self):
"""test inserted_primary_key contains [None] when
pk_col=next_value(), implicit returning is not used."""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer, primary_key=True))
t1.create(testing.db)
e = engines.testing_engine(options={"implicit_returning": False})
s = Sequence("my_sequence")
with e.connect() as conn:
r = conn.execute(t1.insert().values(x=s.next_value()))
eq_(r.inserted_primary_key, [None])
def test_inserted_pk_implicit_returning(self):
"""test inserted_primary_key contains the result when
pk_col=next_value(), when implicit returning is used."""
metadata = self.metadata
s = Sequence("my_sequence")
t1 = Table("t", metadata, Column("x", Integer, primary_key=True))
t1.create(testing.db)
e = engines.testing_engine(options={"implicit_returning": True})
with e.connect() as conn:
r = conn.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(r.inserted_primary_key[0])
def test_sequence_ignore_nullability(self):
metadata = MetaData()
tbl = Table(
"test",
metadata,
Column("id", Integer, Sequence("", start=5), nullable=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(5,1))",
)
def define_tables(cls, metadata):
Table(
"aitable",
metadata,
Column(
"id",
Integer,
Sequence("ai_id_seq", optional=True),
primary_key=True,
),
Column("int1", Integer),
Column("str1", String(20)),
)
def test_insert_literal_binds_sequence_notimplemented(self):
table = Table("x", MetaData(), Column("y", Integer, Sequence("y_seq")))
dialect = default.DefaultDialect()
dialect.supports_sequences = True
stmt = table.insert().values(myid=3, name="jack")
assert_raises(
NotImplementedError,
stmt.compile,
compile_kwargs=dict(literal_binds=True),
dialect=dialect,
)
def define_tables(cls, metadata):
global foo, bar, blub, bar_foo, blub_bar, blub_foo
# the 'data' columns are to appease SQLite which cant handle a blank
# INSERT
foo = Table(
"foo",
metadata,
Column(
"id",
Integer,
Sequence("foo_seq", optional=True),
primary_key=True,
),
Column("data", String(20)),
)
bar = Table(
"bar",
metadata,
Column("id", Integer, ForeignKey("foo.id"), primary_key=True),
Column("bar_data", String(20)),
)
blub = Table(
"blub",
metadata,
Column("id", Integer, ForeignKey("bar.id"), primary_key=True),
Column("blub_data", String(20)),
)
bar_foo = Table(
"bar_foo",
metadata,
Column("bar_id", Integer, ForeignKey("bar.id")),
Column("foo_id", Integer, ForeignKey("foo.id")),
)
blub_bar = Table(
"bar_blub",
metadata,
Column("blub_id", Integer, ForeignKey("blub.id")),
Column("bar_id", Integer, ForeignKey("bar.id")),
)
blub_foo = Table(
"blub_foo",
metadata,
Column("blub_id", Integer, ForeignKey("blub.id")),
Column("foo_id", Integer, ForeignKey("foo.id")),
)
def test_func_embedded_whereclause(self, connection):
"""test can use next_value() in whereclause"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
connection.execute(t1.insert(), [{"x": 1}, {"x": 300}, {"x": 301}])
s = Sequence("my_sequence")
eq_(
list(
connection.execute(t1.select().where(t1.c.x > s.next_value()))
),
[(300,), (301,)],
)
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_opt_sequence_returning_insert(self):
table = Table(
"testtable",
self.metadata,
Column(
"id",
Integer,
Sequence("my_seq", optional=True),
primary_key=True,
),
Column("data", String(30)),
)
self.metadata.create_all()
self._assert_data_autoincrement_returning(table)
def define_tables(cls, metadata):
Table(
"cartitems",
metadata,
Column(
"cart_id", Integer, Sequence("cart_id_seq"), primary_key=True
),
Column("description", String(40)),
Column("createdate", sa.DateTime()),
)
# a little bit of implicit case sensitive naming test going on here
Table(
"Manager",
metadata,
Column("obj_id", Integer, Sequence("obj_id_seq")),
Column("name", String(128)),
Column(
"id",
Integer,
Sequence("Manager_id_seq", optional=True),
primary_key=True,
),
)
def test_float(self):
float_table = Table(
"float_table",
metadata,
Column(
"id",
Integer,
Sequence("numeric_id_seq", optional=True),
primary_key=True,
),
Column("floatcol", Float()),
)
metadata.create_all()
try:
test_items = [
float(d) for d in (
"1500000.00000000000000000000",
"-1500000.00000000000000000000",
"1500000",
"0.0000000000000000002",
"0.2",
"-0.0000000000000000002",
"156666.458923543",
"-156666.458923543",
"1",
"-1",
"1234",
"2E-12",
"4E8",
"3E-6",
"3E-7",
"4.1",
"1E-1",
"1E-2",
"1E-3",
"1E-4",
"1E-5",
"1E-6",
"1E-7",
"1E-8",
)
]
for value in test_items:
float_table.insert().execute(floatcol=value)
except Exception as e:
raise e