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)