def test_migration(shelf_filename, cld_host, dbexport_host, username, password, service_binding): PENDING = sql_from_folder(PENDING_FOLDER) with tempfolder() as tempf: outfile = os.path.join(tempf, 'schemadump.sql') do_schema_dump(outfile, cld_host, dbexport_host, username, password, service_binding) for i in range(len(PENDING) + 1): ATTEMPTING = list(reversed(PENDING))[:i] ATTEMPTING.reverse() print("TESTING MIGRATION USING LAST {} MIGRATION FILES".format(i)) with temporary_db() as dummy_db_url, temporary_db() as target_db_url: with S(dummy_db_url) as s_dummy: load_sql_from_file(s_dummy, outfile) try: for migration_sql in ATTEMPTING: raw_execute(s_dummy, migration_sql) except DB_ERROR_TUPLE: print('TRIED USING LAST {} PENDING FILES TO MIGRATE BUT THIS FAILED, MOVING TO NEXT'.format(i)) continue load_from_app_model(target_db_url) if databases_are_equal(dummy_db_url, target_db_url): print('SUCCESS WITH LAST {} PENDING FILES'.format(i)) shelve_result(shelf_filename, ATTEMPTING) return 0 else: print('TRIED USING LAST {} PENDING FILES TO MIGRATE BUT THIS DOES NOT GIVE A CORRECT OUTCOME, MOVING TO NEXT'.format(i)) print('COULD NOT FIND A CORRECT MIGRATION PATH :(') return 1
def dump(): from sqlbag import S, temporary_database as temporary_db, load_sql_from_file from migra import Migration DB_URL_FUTURE = 'postgresql:///cuedev' with temporary_db() as DB_URL_CURRENT: with S(DB_URL_FUTURE) as s_current, S(DB_URL_CURRENT) as s_target: load_sql_from_file(s_target, './host.sql') m = Migration(s_target, s_current) m.set_safety(False) m.add_all_changes() if m.statements: print('THE FOLLOWING CHANGES ARE NOT IMMORTALISED:', end='\n\n') print(m.sql) print() os.system( 'pg_dump -s --no-comments --no-owner --no-acl --no-tablespaces postgresql:///cuedev | sed -e "/^--/d" > host.sql' ) if input('Dump Dev to host.sql?') == 'yes': print('Dumping...') else: print('Not applying.') else: print('Already synced.')
def apply_statements(statements: str, db: str): """ Apply a file of SQL statements to a database. :param statements: An SQL file of statements to apply to the database. :param db: A database to target. """ if not os.path.exists(statements): print( cf.bold_red("Error:"), f'Could not find file "{statements}"', file=sys.stderr, ) sys.exit(os.EX_OSFILE) base_uri = copy_url(db) target_exists = database_exists(base_uri, test_can_select=True) if not target_exists: print( cf.bold_red("Error:"), f'Database "{base_uri.database}" does not exist.', ) sys.exit(os.EX_NOHOST) with S(db) as s: try: load_sql_from_file(s, statements) except Exception as e: print(cf.bold_red("Error:"), e, file=sys.stderr) sys.exit(os.EX_DATAERR) print(cf.bold("All done! ✨"))
def load_from_app_model(dburl): def create_tables(include=None, exclude=None): tables = set(t for t in db.metadata.tables.values() if ((include is None) or t.name in include) and ( (exclude is None) or t.name not in exclude)) db.metadata.create_all(s.bind.engine, tables=tables) with S(dburl) as s: load_sql_from_file(s, 'DB/migration/setup-pre.sql') create_tables() with S(dburl) as s: load_sql_from_file(s, 'DB/migration/setup-post.sql')
def do_migration(REAL_DB_URL): PENDING = sql_from_folder(PENDING_FOLDER) with tempfolder() as tempf: outfile = os.path.join(tempf, 'schemadump.sql') do_schema_dump(REAL_DB_URL, outfile) for i in range(len(PENDING) + 1): ATTEMPTING = list(reversed(PENDING))[:i] ATTEMPTING.reverse() print("TESTING MIGRATION USING LAST {} MIGRATION FILES".format(i)) with temporary_db() as dummy_db_url, temporary_db( ) as target_db_url: with S(dummy_db_url) as s_dummy: load_sql_from_file(s_dummy, outfile) try: for migration_sql in ATTEMPTING: raw_execute(s_dummy, migration_sql) except DB_ERROR_TUPLE as e: print( 'TRIED USING LAST {} PENDING FILES TO MIGRATE BUT THIS FAILED, MOVING TO NEXT' .format(i)) continue load_from_app_model(target_db_url) if databases_are_equal(dummy_db_url, target_db_url): print('APPLYING LAST {} PENDING FILES'.format(i)) with S(REAL_DB_URL) as s_real: for migration_sql in ATTEMPTING: if not DRY_RUN: print("EXECUTING:") print(migration_sql) raw_execute(s_real, migration_sql) else: print('DRY RUN, would apply:') print(migration_sql) print('SUCCESS: DATABASE UP TO DATE.') return 0 else: print( 'TRIED USING LAST {} PENDING FILES TO MIGRATE BUT THIS DOES NOT GIVE A CORRECT OUTCOME, MOVING TO NEXT' .format(i)) print('COULD NOT FIND A CORRECT MIGRATION PATH :(') return 1
def load_from_app_model(dburl): def create_tables(include=None, exclude=None): tables = set( t for t in db.metadata.tables.values() if ((include is None) or t.name in include) and ((exclude is None) or t.name not in exclude) ) db.metadata.create_all(s.bind.engine, tables=tables) with S(dburl) as s: load_sql_from_file(s, 'DB/migration/setup-pre.sql') create_tables() with S(dburl) as s: load_sql_from_file(s, 'DB/migration/setup-post.sql')
def do_migration(REAL_DB_URL): PENDING = sql_from_folder(PENDING_FOLDER) with tempfolder() as tempf: outfile = os.path.join(tempf, 'schemadump.sql') do_schema_dump(REAL_DB_URL, outfile) for i in range(len(PENDING) + 1): ATTEMPTING = list(reversed(PENDING))[:i] ATTEMPTING.reverse() print("TESTING MIGRATION USING LAST {} MIGRATION FILES".format(i)) with temporary_db() as dummy_db_url, temporary_db() as target_db_url: with S(dummy_db_url) as s_dummy: load_sql_from_file(s_dummy, outfile) try: for migration_sql in ATTEMPTING: raw_execute(s_dummy, migration_sql) except DB_ERROR_TUPLE as e: print('TRIED USING LAST {} PENDING FILES TO MIGRATE BUT THIS FAILED, MOVING TO NEXT'.format(i)) continue load_from_app_model(target_db_url) if databases_are_equal(dummy_db_url, target_db_url): print('APPLYING LAST {} PENDING FILES'.format(i)) with S(REAL_DB_URL) as s_real: for migration_sql in ATTEMPTING: if not DRY_RUN: print("EXECUTING:") print(migration_sql) raw_execute(s_real, migration_sql) else: print('DRY RUN, would apply:') print(migration_sql) print('SUCCESS: DATABASE UP TO DATE.') return 0 else: print('TRIED USING LAST {} PENDING FILES TO MIGRATE BUT THIS DOES NOT GIVE A CORRECT OUTCOME, MOVING TO NEXT'.format(i)) print('COULD NOT FIND A CORRECT MIGRATION PATH :(') return 1
def test_all(): with temporary_database() as d0, temporary_database() as d1: with S(d0) as s0, S(d1) as s1: load_sql_from_file(s0, 'tests/FIXTURES/a.sql') load_sql_from_file(s1, 'tests/FIXTURES/b.sql') args = parse_args([d0, d1]) assert not args.unsafe out, err = outs() assert run(args, out=out, err=err) == 3 assert out.getvalue() == '' assert err.getvalue() == '-- ERROR: destructive statements generated. Use the --unsafe flag to suppress this error.\n' args = parse_args(['--unsafe', d0, d1]) assert args.unsafe out, err = outs() assert run(args, out=out, err=err) == 2 assert err.getvalue() == '' assert out.getvalue().strip() == EXPECTED with S(d0) as s0, S(d1) as s1: m = Migration(s0, s1) with raises(AttributeError): m.changes.nonexist m.set_safety(False) m.add_sql('alter table products rename column oldcolumn to newcolumn;') m.apply() m.add_all_changes() assert m.sql.strip() == EXPECTED2 # sql generated OK m.apply() # check for changes again and make sure none are pending m.add_all_changes() assert m.changes.i_from == m.changes.i_target assert not m.statements # no further statements to apply out, err = outs() assert run(args, out=out, err=err) == 0
def do_startup(): with S(dburl) as s: load_sql_from_file(s, 'DB/data/on_startup.sql')
def load_test_fixtures(dburl): with S(dburl) as s: load_sql_from_file(s, 'DB/data/test_fixtures.sql')
def do_fixture_test( fixture_name, schema=None, create_extensions_only=False, with_privileges=False ): flags = ["--unsafe"] if schema: flags += ["--schema", schema] if create_extensions_only: flags += ["--create-extensions-only"] if with_privileges: flags += ["--with-privileges"] fixture_path = "tests/FIXTURES/{}/".format(fixture_name) EXPECTED = io.open(fixture_path + "expected.sql").read().strip() with temporary_database(host="localhost") as d0, temporary_database( host="localhost" ) as d1: with S(d0) as s0: create_role(s0, schemainspect_test_role) with S(d0) as s0, S(d1) as s1: load_sql_from_file(s0, fixture_path + "a.sql") load_sql_from_file(s1, fixture_path + "b.sql") args = parse_args([d0, d1]) assert not args.unsafe assert args.schema is None out, err = outs() assert run(args, out=out, err=err) == 3 assert out.getvalue() == "" DESTRUCTIVE = "-- ERROR: destructive statements generated. Use the --unsafe flag to suppress this error.\n" assert err.getvalue() == DESTRUCTIVE args = parse_args(flags + [d0, d1]) assert args.unsafe assert args.schema == schema out, err = outs() assert run(args, out=out, err=err) == 2 assert err.getvalue() == "" assert out.getvalue().strip() == EXPECTED ADDITIONS = io.open(fixture_path + "additions.sql").read().strip() EXPECTED2 = io.open(fixture_path + "expected2.sql").read().strip() with S(d0) as s0, S(d1) as s1: m = Migration(s0, s1, schema=schema) m.inspect_from() m.inspect_target() with raises(AttributeError): m.changes.nonexist m.set_safety(False) if ADDITIONS: m.add_sql(ADDITIONS) m.apply() if create_extensions_only: m.add_extension_changes(drops=False) else: m.add_all_changes(privileges=with_privileges) expected = EXPECTED2 if ADDITIONS else EXPECTED assert m.sql.strip() == expected # sql generated OK m.apply() # check for changes again and make sure none are pending if create_extensions_only: m.add_extension_changes(drops=False) assert ( m.changes.i_from.extensions.items() >= m.changes.i_target.extensions.items() ) else: m.add_all_changes(privileges=with_privileges) assert m.changes.i_from == m.changes.i_target assert not m.statements # no further statements to apply assert m.sql == "" out, err = outs() assert run(args, out=out, err=err) == 0 # test alternative parameters with S(d0) as s0, S(d1) as s1: m = Migration(get_inspector(s0), get_inspector(s1)) # test empty m = Migration(None, None) m.add_all_changes(privileges=with_privileges) with raises(AttributeError): m.s_from with raises(AttributeError): m.s_target args = parse_args(flags + ["EMPTY", "EMPTY"]) out, err = outs() assert run(args, out=out, err=err) == 0
def load_current_staging_state(dburl): with S(dburl) as s: load_sql_from_file(s, 'DB/dumps/staging.schema.dump.sql')
def load_current_production_state(dburl): with S(dburl) as s: load_sql_from_file(s, 'DB/dumps/prod.schema.dump.sql')
def do_fixture_test(fixture_name): fixture_path = 'tests/FIXTURES/{}/'.format(fixture_name) EXPECTED = io.open(fixture_path + 'expected.sql').read().strip() with temporary_database() as d0, temporary_database() as d1: with S(d0) as s0, S(d1) as s1: load_sql_from_file(s0, fixture_path + 'a.sql') load_sql_from_file(s1, fixture_path + 'b.sql') args = parse_args([d0, d1]) assert not args.unsafe out, err = outs() assert run(args, out=out, err=err) == 3 assert out.getvalue() == '' assert err.getvalue() == '-- ERROR: destructive statements generated. Use the --unsafe flag to suppress this error.\n' args = parse_args(['--unsafe', d0, d1]) assert args.unsafe out, err = outs() assert run(args, out=out, err=err) == 2 assert err.getvalue() == '' assert out.getvalue().strip() == EXPECTED ADDITIONS = io.open(fixture_path + 'additions.sql').read().strip() EXPECTED2 = io.open(fixture_path + 'expected2.sql').read().strip() if ADDITIONS: with S(d0) as s0, S(d1) as s1: m = Migration(s0, s1) m.inspect_from() m.inspect_target() with raises(AttributeError): m.changes.nonexist m.set_safety(False) m.add_sql(ADDITIONS) m.apply() m.add_all_changes() assert m.sql.strip() == EXPECTED2 # sql generated OK m.apply() # check for changes again and make sure none are pending m.add_all_changes() assert m.changes.i_from == m.changes.i_target assert not m.statements # no further statements to apply assert m.sql == '' out, err = outs() assert run(args, out=out, err=err) == 0 # test alternative parameters with S(d0) as s0, S(d1) as s1: m = Migration( get_inspector(s0), get_inspector(s1) ) # test empty m = Migration(None, None) m.add_all_changes() with raises(AttributeError): m.s_from with raises(AttributeError): m.s_target args = parse_args(['--unsafe', 'EMPTY', 'EMPTY']) out, err = outs() assert run(args, out=out, err=err) == 0
def do_fixture_test(fixture_name, schema=None, create_extensions_only=False, with_privileges=False): flags = ["--unsafe"] if schema: flags += ["--schema", schema] if create_extensions_only: flags += ["--create-extensions-only"] if with_privileges: flags += ["--with-privileges"] fixture_path = "tests/FIXTURES/{}/".format(fixture_name) EXPECTED = io.open(fixture_path + "expected.sql").read().strip() with temporary_database(host="localhost") as d0, temporary_database( host="localhost") as d1: with S(d0) as s0: create_role(s0, schemainspect_test_role) with S(d0) as s0, S(d1) as s1: load_sql_from_file(s0, fixture_path + "a.sql") load_sql_from_file(s1, fixture_path + "b.sql") args = parse_args([d0, d1]) assert not args.unsafe assert args.schema is None out, err = outs() assert run(args, out=out, err=err) == 3 assert out.getvalue() == "" DESTRUCTIVE = "-- ERROR: destructive statements generated. Use the --unsafe flag to suppress this error.\n" assert err.getvalue() == DESTRUCTIVE args = parse_args(flags + [d0, d1]) assert args.unsafe assert args.schema == schema out, err = outs() assert run(args, out=out, err=err) == 2 assert err.getvalue() == "" assert out.getvalue().strip() == EXPECTED ADDITIONS = io.open(fixture_path + "additions.sql").read().strip() EXPECTED2 = io.open(fixture_path + "expected2.sql").read().strip() with S(d0) as s0, S(d1) as s1: m = Migration(s0, s1, schema=schema) m.inspect_from() m.inspect_target() with raises(AttributeError): m.changes.nonexist m.set_safety(False) if ADDITIONS: m.add_sql(ADDITIONS) m.apply() if create_extensions_only: m.add_extension_changes(drops=False) else: m.add_all_changes(privileges=with_privileges) expected = EXPECTED2 if ADDITIONS else EXPECTED assert m.sql.strip() == expected # sql generated OK m.apply() # check for changes again and make sure none are pending if create_extensions_only: m.add_extension_changes(drops=False) assert (m.changes.i_from.extensions.items() >= m.changes.i_target.extensions.items()) else: m.add_all_changes(privileges=with_privileges) assert m.changes.i_from == m.changes.i_target assert not m.statements # no further statements to apply assert m.sql == "" out, err = outs() assert run(args, out=out, err=err) == 0 # test alternative parameters with S(d0) as s0, S(d1) as s1: m = Migration(get_inspector(s0), get_inspector(s1)) # test empty m = Migration(None, None) m.add_all_changes(privileges=with_privileges) with raises(AttributeError): m.s_from with raises(AttributeError): m.s_target args = parse_args(flags + ["EMPTY", "EMPTY"]) out, err = outs() assert run(args, out=out, err=err) == 0