def test_stamp_api_creates_table(self):
context = self.make_one(connection=self.connection)
assert ('alembic_version'
not in Inspector(self.connection).get_table_names())
script = mock.Mock(
_stamp_revs=lambda revision, heads:
[_up(None, 'a', True), _up(None, 'b', True)])
context.stamp(script, 'b')
eq_(context.get_current_heads(), ('a', 'b'))
assert ('alembic_version'
in Inspector(self.connection).get_table_names())
def find_pending(dburl):
db = sa.create_engine(dburl)
inspector = Inspector(db)
# Newer buildbot has a "buildrequest_claims" table
if "buildrequest_claims" in inspector.get_table_names():
query = sa.text("""
SELECT buildername, id FROM
buildrequests WHERE
complete=0 AND
submitted_at > :yesterday AND
submitted_at < :toonew AND
(select count(brid) from buildrequest_claims
where brid=id) = 0""")
# Older buildbot doesn't
else:
query = sa.text("""
SELECT buildername, id FROM
buildrequests WHERE
complete=0 AND
claimed_at=0 AND
submitted_at > :yesterday AND
submitted_at < :toonew""")
result = db.execute(query,
yesterday=time.time() - 86400,
toonew=time.time() - 10)
retval = result.fetchall()
return retval
def test_compare_get_schema_names_for_sql_and_odbc(self, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
schema_names_fallback = dialect.get_schema_names(
connection=c, use_sql_fallback=True)
schema_names_odbc = dialect.get_schema_names(connection=c)
assert sorted(schema_names_fallback) == sorted(schema_names_odbc)
def __init__(self, sqla_conn, args, schema=None):
self.args = args
self.sqla_conn = sqla_conn
self.schema = schema
self.engine = sa.create_engine(sqla_conn)
self.meta = sa.MetaData(
bind=self.engine) # excised schema=schema to prevent errors
self.meta.reflect(schema=self.schema)
self.inspector = Inspector(bind=self.engine)
self.conn = self.engine.connect()
self.tables = OrderedDict()
for tbl in self.meta.sorted_tables:
tbl.db = self
# TODO: Replace all these monkeypatches with an instance assigment
tbl.find_n_rows = types.MethodType(_find_n_rows, tbl)
tbl.random_row_func = types.MethodType(_random_row_func, tbl)
tbl.fks = self.inspector.get_foreign_keys(tbl.name,
schema=tbl.schema)
tbl.pk = self.inspector.get_primary_keys(tbl.name,
schema=tbl.schema)
tbl.filtered_by = types.MethodType(_filtered_by, tbl)
tbl.by_pk = types.MethodType(_by_pk, tbl)
tbl.pk_val = types.MethodType(_pk_val, tbl)
tbl.exists = types.MethodType(_exists, tbl)
tbl.child_fks = []
tbl.find_n_rows(estimate=True)
self.tables[(tbl.schema, tbl.name)] = tbl
for ((tbl_schema, tbl_name), tbl) in self.tables.items():
for fk in tbl.fks:
fk['constrained_schema'] = tbl_schema
fk['constrained_table'] = tbl_name # TODO: check against constrained_table
self.tables[(fk['referred_schema'],
fk['referred_table'])].child_fks.append(fk)
def test_get_unique_constraints(self):
meta = MetaData(testing.db)
t1 = Table('foo', meta, Column('f', Integer),
UniqueConstraint('f', name='foo_f'))
t2 = Table('bar',
meta,
Column('b', Integer),
UniqueConstraint('b', name='bar_b'),
prefixes=['TEMPORARY'])
meta.create_all()
from sqlalchemy.engine.reflection import Inspector
try:
inspector = Inspector(testing.db)
eq_(inspector.get_unique_constraints('foo'),
[{
'column_names': [u'f'],
'name': u'foo_f'
}])
eq_(inspector.get_unique_constraints('bar'),
[{
'column_names': [u'b'],
'name': u'bar_b'
}])
finally:
meta.drop_all()
def __init__(self, sqla_conn, args, schemas=[None]):
self.args = args
self.sqla_conn = sqla_conn
self.schemas = schemas
self.engine = sa.create_engine(sqla_conn)
self.inspector = Inspector(bind=self.engine)
self.conn = self.engine.connect()
self.tables = OrderedDict()
for schema in self.schemas:
meta = sa.MetaData(
bind=self.engine) # excised schema=schema to prevent errors
meta.reflect(schema=schema)
for tbl in meta.sorted_tables:
if args.tables and not _table_matches_any_pattern(
tbl.schema, tbl.name, self.args.tables):
continue
if _table_matches_any_pattern(tbl.schema, tbl.name,
self.args.exclude_tables):
continue
tbl.db = self
if self.engine.name == 'postgresql':
fix_postgres_array_of_enum(self.conn, tbl)
# TODO: Replace all these monkeypatches with an instance assigment
tbl.find_n_rows = types.MethodType(_find_n_rows, tbl)
tbl.random_row_func = types.MethodType(_random_row_func, tbl)
tbl.fks = self.inspector.get_foreign_keys(tbl.name,
schema=tbl.schema)
tbl.pk = self.inspector.get_primary_keys(tbl.name,
schema=tbl.schema)
if not tbl.pk:
tbl.pk = [
d['name']
for d in self.inspector.get_columns(tbl.name,
schema=tbl.schema)
]
tbl.filtered_by = types.MethodType(_filtered_by, tbl)
tbl.by_pk = types.MethodType(_by_pk, tbl)
tbl.pk_val = types.MethodType(_pk_val, tbl)
tbl.child_fks = []
estimate_rows = not _table_matches_any_pattern(
tbl.schema, tbl.name, self.args.full_tables)
tbl.find_n_rows(estimate=estimate_rows)
self.tables[(tbl.schema, tbl.name)] = tbl
all_constraints = args.config.get('constraints', {})
for ((tbl_schema, tbl_name), tbl) in self.tables.items():
qualified = "{}.{}".format(tbl_schema, tbl_name)
if qualified in all_constraints:
constraints = all_constraints[qualified]
else:
constraints = all_constraints.get(tbl_name, [])
tbl.constraints = constraints
for fk in (tbl.fks + constraints):
fk['constrained_schema'] = tbl_schema
fk['constrained_table'] = tbl_name # TODO: check against constrained_table
self.tables[(fk['referred_schema'],
fk['referred_table'])].child_fks.append(fk)
def test_has_table_table_exists_not(self, use_sql_fallback, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
has_table = dialect.has_table(connection=c,
schema=self.schema,
table_name="not_exist",
use_sql_fallback=use_sql_fallback)
assert not has_table, "Table %s.not_exist was found, but should not exist" % self.schema
def test_get_view_names(self, use_sql_fallback, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
view_names = dialect.get_view_names(
connection=c,
schema=self.schema,
use_sql_fallback=use_sql_fallback)
assert "v" in view_names
def test_get_columns_table_name_none(self, use_sql_fallback, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
columns = dialect.get_columns(connection=c,
schema=self.schema,
table_name=None,
use_sql_fallback=use_sql_fallback)
assert columns == []
def detect_changed(self):
""" Detect the difference between the metadata and the database
:rtype: MigrationReport instance
"""
diff = compare_metadata(self.context, self.metadata)
inspector = Inspector(self.conn)
diff.extend(self.detect_undetected_constraint_from_alembic(inspector))
return MigrationReport(self, diff)
def test_deprecated_get_primary_keys(self):
meta = self.metadata
users = self.tables.users
insp = Inspector(meta.bind)
assert_raises_message(
sa_exc.SADeprecationWarning,
"Call to deprecated method get_primary_keys."
" Use get_pk_constraint instead.", insp.get_primary_keys,
users.name)
def test_get_view_definition(self, use_sql_fallback, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
view_definition = dialect.get_view_definition(
connection=c,
schema=self.schema,
view_name="v",
use_sql_fallback=use_sql_fallback)
assert self.view_defintion == view_definition
def setUp(self):
self.connection = create_engine('sqlite:///:memory:').connect()
self.metadata = MetaData(self.connection)
self.table = Table('thingy', self.metadata,
Column('thingy_id', Integer, primary_key=True))
self.metadata.create_all()
self.migration_context = MigrationContext.configure(self.connection)
self.op = IdempotentOperations(self.migration_context, self)
self.inspector = Inspector(self.connection)
def test_get_pk_constraint(self, use_sql_fallback, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
pk_constraint = dialect.get_pk_constraint(
connection=c,
schema=self.schema,
table_name="t",
use_sql_fallback=use_sql_fallback)
assert pk_constraint["constrained_columns"] == ['pid1', 'pid2'] and \
pk_constraint["name"].startswith("sys_")
def test_get_pk_constraint_table_name_none(self, use_sql_fallback,
engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
pk_constraint = dialect.get_pk_constraint(
connection=c,
schema=self.schema,
table_name=None,
use_sql_fallback=use_sql_fallback)
assert pk_constraint is None
def test_compare_has_table_for_sql_and_odbc(self, schema, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
has_table_fallback = dialect.has_table(connection=c,
schema=schema,
use_sql_fallback=True,
table_name="t")
has_table_odbc = dialect.has_table(connection=c,
schema=schema,
table_name="t")
assert has_table_fallback == has_table_odbc, "Expected table %s.t with odbc and fallback" % schema
def _check_schema_consistency(config, db_name, schema_name, parsed_schema,
schema_version, schema_description,
target_engine):
# Connect to the server that has database that is being added
from sqlalchemy.engine.reflection import Inspector
inspector = Inspector(target_engine)
if schema_name not in inspector.get_schema_names():
config.log.error("Schema '%s' not found.", db_name)
raise MetaBException(MetaBException.DB_DOES_NOT_EXIST, db_name)
db_tables = inspector.get_table_names(schema=schema_name)
for table_name, parsed_table in parsed_schema.items():
# Check parsed tables - we allow other tables in schema
if table_name not in db_tables:
config.log.error(
"Table '%s' not found in db, present in ascii file.",
table_name)
raise MetaBException(MetaBException.TB_NOT_IN_DB, table_name)
db_columns = inspector.get_columns(table_name=table_name,
schema=schema_name)
parsed_columns = parsed_table["columns"]
if len(parsed_columns) != len(db_columns):
config.log.error(
"Number of columns in db for table %s (%d) "
"differs from number columns in schema (%d)", table_name,
len(db_columns), len(parsed_columns))
raise MetaBException(MetaBException.NOT_MATCHING)
for column in parsed_columns:
column_name = column["name"]
if column_name not in db_columns:
config.log.error(
"Column '%s.%s' not found in db, "
"but exists in schema DDL", table_name, column_name)
raise MetaBException(MetaBException.COL_NOT_IN_TB, column_name,
table_name)
# Get schema description and version, it is ok if it is missing
ret = target_engine.execute(
"SELECT version, descr FROM %s.ZZZ_Schema_Description" % db_name)
if ret.rowcount != 1:
config.log.error("Db '%s' does not contain schema version/description",
db_name)
else:
(found_schema_version, found_schema_description) = ret.first()
if found_schema_version != schema_version or \
found_schema_description != schema_description:
raise MetaBException(
MetaBException.NOT_MATCHING,
"Schema name or description does not match defined values.")
def test_compare_get_view_names_for_sql_and_odbc(self, schema,
engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
c.execute("OPEN SCHEMA %s" % self.schema)
view_names_fallback = dialect.get_view_names(connection=c,
schema=schema,
use_sql_fallback=True)
view_names_odbc = dialect.get_view_names(connection=c,
schema=schema)
assert view_names_fallback == view_names_odbc
def test_dont_reflect_autoindex(self):
meta = self.metadata
Table('foo', meta, Column('bar', String, primary_key=True))
meta.create_all()
inspector = Inspector(testing.db)
eq_(inspector.get_indexes('foo'), [])
eq_(inspector.get_indexes('foo', include_auto_indexes=True),
[{
'unique': 1,
'name': 'sqlite_autoindex_foo_1',
'column_names': ['bar']
}])
def upgrade(migrate_engine):
meta = MetaData(bind=migrate_engine)
for prefix in ('', 'shadow_'):
services = Table(prefix + 'services', meta, autoload=True)
if not hasattr(services.c, 'uuid'):
services.create_column(Column('uuid', String(36), nullable=True))
uuid_index_name = 'services_uuid_idx'
indexes = Inspector(migrate_engine).get_indexes('services')
if uuid_index_name not in (i['name'] for i in indexes):
services = Table('services', meta, autoload=True)
Index(uuid_index_name, services.c.uuid, unique=True).create()
def test_dont_reflect_autoindex(self):
meta = MetaData(testing.db)
t = Table('foo', meta, Column('bar', String, primary_key=True))
meta.create_all()
from sqlalchemy.engine.reflection import Inspector
try:
inspector = Inspector(testing.db)
eq_(inspector.get_indexes('foo'), [])
eq_(inspector.get_indexes('foo',
include_auto_indexes=True), [{'unique': 1, 'name'
: u'sqlite_autoindex_foo_1', 'column_names': [u'bar']}])
finally:
meta.drop_all()
def test_create_index_with_schema(self):
"""Test creation of index with explicit schema"""
meta = self.metadata
Table('foo', meta, Column('bar', String, index=True), schema='main')
meta.create_all()
inspector = Inspector(testing.db)
eq_(inspector.get_indexes('foo', schema='main'),
[{
'unique': 0,
'name': u'ix_main_foo_bar',
'column_names': [u'bar']
}])
def detect_changed(self, schema_only=False):
""" Detect the difference between the metadata and the database
:rtype: MigrationReport instance
"""
inspector = Inspector(self.conn)
if schema_only:
diff = self.detect_added_new_schema(inspector)
else:
diff = compare_metadata(self.context, self.metadata)
diff.extend(
self.detect_undetected_constraint_from_alembic(inspector))
return MigrationReport(self, diff)
def test_compare_get_foreign_keys_for_sql_and_odbc(self, schema, table,
engine_name):
with self.engine_map[engine_name].begin() as c:
if schema is None:
c.execute("OPEN SCHEMA %s" % self.schema_2)
dialect = Inspector(c).dialect
foreign_keys_fallback = dialect.get_foreign_keys(
connection=c,
table_name=table,
schema=schema,
use_sql_fallback=True)
foreign_keys_odbc = dialect.get_foreign_keys(connection=c,
table_name=table,
schema=schema)
assert str(foreign_keys_fallback) == str(foreign_keys_odbc)
def test_compare_get_columns_for_sql_and_odbc(self, schema, table,
engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
if schema is None:
c.execute("OPEN SCHEMA %s" % self.schema)
columns_fallback = dialect.get_columns(connection=c,
table_name=table,
schema=schema,
use_sql_fallback=True)
columns_odbc = dialect.get_columns(connection=c,
table_name=table,
schema=schema)
assert str(columns_fallback) == str(
columns_odbc) # object equality doesn't work for sqltypes
def test_add_index_constraint(self):
def add_in_registry():
@register(Model)
class Address:
id = Integer(primary_key=True)
@register(Model)
class Person:
name = String(primary_key=True)
address = Many2One(model=Model.Address, index=True)
registry = self.init_registry(add_in_registry)
inspector = Inspector(registry.session.connection())
indexes = inspector.get_indexes(registry.Person.__tablename__)
self.assertEqual(len(indexes), 1)
def test_get_foreign_keys(self, use_sql_fallback, engine_name):
with self.engine_map[engine_name].begin() as c:
dialect = Inspector(c).dialect
foreign_keys = dialect.get_foreign_keys(
connection=c,
schema=self.schema,
table_name="s",
use_sql_fallback=use_sql_fallback)
expected = [{
'name': 'fk_test',
'constrained_columns': ['fid1', 'fid2'],
'referred_schema': 'test_get_metadata_functions_schema',
'referred_table': 't',
'referred_columns': ['pid1', 'pid2']
}]
assert foreign_keys == expected
def test_add_primary_keys_constraint(self):
def add_in_registry():
@register(Model)
class Address:
id = Integer(primary_key=True)
@register(Model)
class Person:
name = String(primary_key=True)
address = Many2One(model=Model.Address, primary_key=True)
registry = self.init_registry(add_in_registry)
inspector = Inspector(registry.session.connection())
pks = inspector.get_primary_keys(registry.Person.__tablename__)
assert 'address_id' in pks
def upgrade(migrate_engine):
"""Add service_uuid column to volumes."""
meta = MetaData(bind=migrate_engine)
Table('services', meta, autoload=True)
volumes = Table('volumes', meta, autoload=True)
if not hasattr(volumes.c, 'service_uuid'):
volumes.create_column(
Column('service_uuid',
String(36),
ForeignKey('services.uuid'),
nullable=True))
index_name = 'volumes_service_uuid_idx'
indexes = Inspector(migrate_engine).get_indexes('volumes')
if index_name not in (i['name'] for i in indexes):
volumes = Table('volumes', meta, autoload=True)
Index(index_name, volumes.c.service_uuid, volumes.c.deleted).create()
def upgrade(migrate_engine):
"""Add uuid column to services."""
meta = MetaData(bind=migrate_engine)
services = Table('services', meta, autoload=True)
if not hasattr(services.c, 'uuid'):
services.create_column(Column('uuid', String(36), nullable=True))
uuid_index_name = 'services_uuid_idx'
indexes = Inspector(migrate_engine).get_indexes('services')
if uuid_index_name not in (i['name'] for i in indexes):
services = Table('services', meta, autoload=True)
Index(uuid_index_name, services.c.uuid, unique=True).create()
service_list = list(services.select().execute())
for s in service_list:
if not s.uuid:
services.update().where(services.c.id == s.id).\
values(uuid=six.text_type(uuid.uuid4())).execute()
