def test_model_and_migration_schemas_are_the_same(self):
"""Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models. It produces a text file with the
results to help debug differences.
"""
from sqlalchemy.engine import create_engine
from sqlalchemydiff import compare
with create_engine(self.db_url_left).begin() as connection:
self.alembic_cfg_left.attributes['connection'] = connection
command.upgrade(self.alembic_cfg_left, "head")
engine_right = create_engine(self.db_url_right)
Base.metadata.create_all(engine_right)
engine_right.dispose()
result = compare(
self.db_url_left, self.db_url_right, set(['alembic_version']))
self.assertTrue(result.is_match,
"The migration database doesn't match to the one "
"created by the models.\nDifferences: " +
result._dump_data(result.errors))
def test_model_and_migration_schemas_are_the_same(uri_left, uri_right,
alembic_config_left):
"""Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models.
"""
prepare_schema_from_migrations(uri_left, alembic_config_left)
prepare_schema_from_models(uri_right, Base)
result = compare(uri_left, uri_right, set(['alembic_version']))
assert result.is_match
def test_model_and_migration_schemas_are_the_same(self, uri_left,
uri_right,
alembic_config_left):
"""Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models.
"""
setup_extensions(uri_left)
prepare_schema_from_migrations(uri_left, alembic_config_left)
from ingredients_db.models.images import Image, ImageMembers
from ingredients_db.models.instance import Instance, InstanceKeypair
from ingredients_db.models.network import Network
from ingredients_db.models.network_port import NetworkPort
from ingredients_db.models.project import Project, ProjectMembers
from ingredients_db.models.keypair import Keypair
from ingredients_db.models.task import Task
from ingredients_db.models.authn import AuthNUser, AuthNServiceAccount
from ingredients_db.models.authz import AuthZPolicy, AuthZRole, AuthZRolePolicy
from ingredients_db.models.region import Region
from ingredients_db.models.zones import Zone
from ingredients_db.models.builtin import BuiltInUser
# Make sure the imports don't go away
Image.mro()
ImageMembers.mro()
Instance.mro()
InstanceKeypair.mro()
Network.mro()
NetworkPort.mro()
Project.mro()
ProjectMembers.mro()
Keypair.mro()
Task.mro()
AuthNUser.mro()
AuthNServiceAccount.mro()
AuthZPolicy.mro()
AuthZRole.mro()
AuthZRolePolicy.mro()
Region.mro()
Zone.mro()
BuiltInUser.mro()
setup_extensions(uri_right)
prepare_schema_from_models(uri_right, Base)
result = compare(uri_left, uri_right, {'alembic_version'})
print(json.dumps(result.errors, indent=4))
assert result.is_match
def test_model_and_migration_schemas_are_the_same(self):
"""Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models. It produces a text file with the
results to help debug differences.
"""
prepare_schema_from_migrations(self.uri_left, self.alembic_config_left)
prepare_schema_from_models(self.uri_right, Base)
result = compare(self.uri_left, self.uri_right,
set(['alembic_version']))
assert result.is_match
def execute(baseline, destination, schemas):
missing = pd.DataFrame(columns=[
'table', 'left_only_cols', 'right_only_cols', 'left_only_indices',
'right_only_indices'
])
for schema_name in schemas:
left = '{}/{}'.format(baseline, schema_name)
right = '{}/{}'.format(destination, schema_name)
tool = SchemaSyncHelper(left, right, schema_name)
alter_table_stmts = tool.generate_alter_table_stmts(
schema_name, left, right)
result = compare(left, right)
alter_column_stmts = generate_alter_column_stmts(schema_name, result)
add_col_stmts, drop_col_stmts = tool.geneate_add_drop_columns(
schema_name)
if alter_table_stmts or alter_column_stmts or add_col_stmts or drop_col_stmts:
file_name = 'output/{}-ddl.sql'.format(schema_name)
print("Generating ddl: {}".format(file_name))
with open(file_name, 'w') as f:
if alter_table_stmts:
f.write('\n\n'.join(alter_table_stmts))
f.write('\n\n')
if alter_column_stmts:
f.write('\n\n'.join(alter_column_stmts))
f.write('\n\n')
if add_col_stmts:
f.write('\n\n'.join(add_col_stmts))
f.write('\n\n')
if drop_col_stmts:
f.write('\n\n'.join(drop_col_stmts))
f.write('\n\n')
diff = tool.generate_column_index_diff_report(schema_name)
if diff:
df = pd.DataFrame.from_dict(diff)
missing = missing.append(df, ignore_index=True)
report_file_name = 'output/missing-cols-indices.csv'
print("Generating report: {}".format(report_file_name))
missing.to_csv(report_file_name)
def test_same_schema_is_the_same(uri_left, uri_right, alembic_config_left,
alembic_config_right):
"""Compare two databases both from migrations.
Makes sure the schema comparer validates a database to an exact
replica of itself.
"""
prepare_schema_from_migrations(uri_left, alembic_config_left)
prepare_schema_from_migrations(uri_right, alembic_config_right)
result = compare(uri_left, uri_right, set(['alembic_version']))
# uncomment to see the dump of info dict
# result.dump_info()
assert result.is_match
def test_model_and_migration_schemas_are_the_same(uri_left, uri_right,
alembic_config_left):
"""Compares the database schema obtained with all migrations against the
one we get out of the models.
"""
ah.load_premigration_sql(uri_left)
prepare_schema_from_migrations(uri_left, alembic_config_left)
prepare_schema_from_models(uri_right, Base)
result = compare(uri_left, uri_right, ignores=['alembic_version'])
assert result.is_match is True, \
'Differences (left is migrations, right is models):\n' \
'%s' % json.dumps(
result.errors, sort_keys=True, indent=4, separators=(',', ': ')
)
def get(self, request):
connString1QuerySet = DatabseDetails.objects.filter(
id=request.GET['db1id'])
connString2QuerySet = DatabseDetails.objects.filter(
id=request.GET['db2id'])
connString1 = connString1QuerySet[0].database_config
connString2 = connString2QuerySet[0].database_config
result = sqlalchemydiff.compare(connString1, connString2)
difference_unfiltered = result.errors
difference_filtered = get_filtered_schema_compare(
difference_unfiltered)
lst_response = []
for i in difference_filtered:
lst_response.append({i: difference_filtered[i]})
return Response((lst_response))
def test_model_and_migration_schemas_are_the_same(self):
"""Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models. It produces a text file with the
results to help debug differences.
"""
prepare_schema_from_migrations(self.uri_left, self.alembic_config_left)
prepare_schema_from_models(self.uri_right, Base)
result = compare(self.uri_left,
self.uri_right,
ignores=set([
'alembic_version',
'menstrual_cup_fill.col.removal_time',
'menstrual_cup_fill.col.time'
]))
import json
assert result.is_match, json.dumps(result.errors, indent=True)
def test_model_and_migration_schemas_are_the_same(
uri_left, uri_right, alembic_config_left):
"""Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models.
"""
prepare_schema_from_migrations(uri_left, alembic_config_left)
engine = create_engine(uri_right)
engine.execute('create extension postgis')
prepare_schema_from_models(uri_right, Base)
result = compare(
uri_left, uri_right,
# Ignore grids.srid fkey because of the flaky way it has to be set up;
# for details see comments in definiton of `Grid` in `v2.py`.
ignores={'alembic_version', 'grids.fk.grids_srid_fkey'}
)
assert result.is_match
def test_model_and_migration_schemas_are_the_same(self):
"""
Compare two databases.
Compares the database obtained with all migrations against the
one we get out of the models. It produces a text file with the
results to help debug differences.
"""
prepare_schema_from_migrations(self.uri_left, self.alembic_config_left)
engine = create_engine(self.uri_right)
engine.execute("CREATE EXTENSION pgcrypto;")
prepare_schema_from_models(self.uri_right, db)
result = compare(self.uri_left, self.uri_right,
set(['alembic_version']))
if not result.is_match:
print("###### DB MISMATCH:")
pprint.PrettyPrinter(indent=1).pprint(result.errors)
assert result.is_match
def test_model_and_migration_schemas_are_not_the_same(uri_left, uri_right,
alembic_config_left):
"""Compares the database obtained with the first migration against
the one we get out of the models.
"""
prepare_schema_from_migrations(uri_left,
alembic_config_left,
revision="+1")
prepare_schema_from_models(uri_right, Base)
result = compare(uri_left, uri_right, set(['alembic_version']))
# uncomment to see the dump of errors dict
# result.dump_errors()
errors = {
'tables': {
'left_only': ['addresses'],
'right_only': ['roles']
},
'tables_data': {
'employees': {
'columns': {
'left_only': [{
'default': None,
'name': 'favourite_meal',
'nullable': False,
'type': "ENUM('meat','vegan','vegetarian')"
}],
'right_only': [
{
'autoincrement': False,
'default': None,
'name': 'role_id',
'nullable': False,
'type': 'INTEGER(11)'
},
{
'autoincrement': False,
'default': None,
'name': 'number_of_pets',
'nullable': False,
'type': 'INTEGER(11)'
},
]
},
'foreign_keys': {
'right_only': [{
'constrained_columns': ['role_id'],
'name': 'fk_employees_roles',
'options': {},
'referred_columns': ['id'],
'referred_schema': None,
'referred_table': 'roles'
}]
},
'indexes': {
'left_only': [{
'column_names': ['name'],
'name': 'name',
'type': 'UNIQUE',
'unique': True
}],
'right_only': [{
'column_names': ['role_id'],
'name': 'fk_employees_roles',
'unique': False
}, {
'column_names': ['name'],
'name': 'ix_employees_name',
'type': 'UNIQUE',
'unique': True
}]
}
},
'phone_numbers': {
'columns': {
'diff': [{
'key': 'number',
'left': {
'default': None,
'name': 'number',
'nullable': True,
'type': 'VARCHAR(40)'
},
'right': {
'default': None,
'name': 'number',
'nullable': False,
'type': 'VARCHAR(40)'
}
}]
}
}
},
'uris': {
'left': uri_left,
'right': uri_right,
}
}
compare_error_dicts(errors, result.errors)
