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 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 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 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 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,
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')
