def sync(DB_URL: str = "postgresql://orflaedi:@localhost/orflaedi"): from sqlbag import S, temporary_database as temporary_db with temporary_db() as TEMP_DB_URL: os.environ["DATABASE_URL"] = TEMP_DB_URL from orflaedi.database import engine, Base from orflaedi.models import Model, Retailer, VehicleClassEnum, TagEnum Base.metadata.create_all(engine) with S(DB_URL) as s_current, S(TEMP_DB_URL) as s_target: m = Migration(s_current, s_target) m.set_safety(False) m.add_all_changes() if m.statements: print("THE FOLLOWING CHANGES ARE PENDING:", end="\n\n") print(m.sql) print() if input("Apply these changes?") == "yes": print("Applying...") m.apply() else: print("Not applying.") else: print("Already synced.")
def sync(database): if os.getenv('HOSTINGENV') == 'DEV': db_url_format = 'postgresql://*****:*****@postgres:5432/%s' else: db_url_format = 'postgresql://*****:*****@/%s?host=/var/run/postgresql' temp_db_url = db_url_format % f'{database}temp' db_url = db_url_format % database with temp_db(temp_db_url) as s_target_temp: create_database(db_url) with S(db_url) as s_current, S(s_target_temp) as s_target: run_all(f'{database}/migrations', s_current) run_all(f'{database}/schemas', s_target) run_all(f'{database}/tables', s_target) m = Migration(s_current, s_target) m.set_safety(False) m.add_all_changes() if m.statements: print('THE FOLLOWING CHANGES ARE PENDING:', end='\n\n') print(m.sql) print() if (len(sys.argv) > 1 and sys.argv[1] == 'noconfirm') or input('Apply these changes? (y/n) ') == 'y': print('Applying...') m.apply() else: print('Not applying.') else: print('Already synced.')
def sync(args: List[str]) -> None: """ Compare live database to application schema & apply changes to database. Uses running database specified for application via `DB_[USER|PASS|HOST|NAME]` environment variables & compares to application schema defined at `./src/models/**/*.sql`. """ # define if prompts are needed or not no_prompt = False if 'noprompt' in args: no_prompt = True # create temp database for app schema with _temp_db(host=DB_HOST, user=DB_USER, password=DB_PASS) as temp_db_url: print(f'db url: {DB_URL}') print(f'temp url: {temp_db_url}') # create sessions for current db state & target schema with S(DB_URL) as from_schema_session, \ S(temp_db_url) as target_schema_session: # load target schema to temp db _load_from_app(target_schema_session) # diff target db & current db migration = Migration(from_schema_session, target_schema_session) migration.set_safety(False) migration.add_all_changes() # handle changes if migration.statements: print('\nTHE FOLLOWING CHANGES ARE PENDING:', end='\n\n') print(migration.sql) if no_prompt: print('Applying...') migration.apply() print('Changes applied.') else: if _prompt('Apply these changes?'): print('Applying...') migration.apply() print('Changes applied.') else: print('Not applying.') else: print('Already synced.')
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 sync(): DB_URL = get_current_app_db_url() with temporary_db() as TEMP_DB_URL: load_from_app_model(TEMP_DB_URL) with S(DB_URL) as s_current, S(TEMP_DB_URL) as s_target: m = Migration(s_current, s_target) m.set_safety(False) m.add_all_changes() if m.statements: print('THE FOLLOWING CHANGES ARE PENDING:', end='\n\n') print(m.sql) print('Applying...') m.apply() else: print('Already synced.')
def sync(): DB_URL = get_current_app_db_url() with temporary_db() as TEMP_DB_URL: load_from_app_model(TEMP_DB_URL) with S(DB_URL) as s_current, S(TEMP_DB_URL) as s_target: m = Migration(s_current, s_target) m.set_safety(False) m.add_all_changes() if m.statements: print('THE FOLLOWING CHANGES ARE PENDING:', end='\n\n') print(m.sql) print('Applying...') m.apply() else: print('Already synced.')
def sync(database): DB_URL = f'postgresql://*****:*****@postgres:5432/{database}' with temp_db(f'postgresql://*****:*****@postgres:5432/{database}temp') as TEMP_DB_URL: create_database(TEMP_DB_URL) create_database(DB_URL) with S(DB_URL) as s_current, S(TEMP_DB_URL) as s_target: run_all(f'{database}/schemas', s_target) run_all(f'{database}/tables', s_target) m = Migration(s_current, s_target) m.set_safety(False) m.add_all_changes() if m.statements: print('THE FOLLOWING CHANGES ARE PENDING:', end='\n\n') print(m.sql) print() if (len(sys.argv) > 1 and sys.argv[1] == 'noconfirm') or input('Apply these changes? (y/n) ') == 'y': print('Applying...') m.apply() else: print('Not applying.') else: print('Already synced.')
def sync_db_structure_to_target_db(db_url, target_db_url, confirm=True, create_extensions_only=False, **kwargs): log.info(f"syncing: {db_url} to {target_db_url}") with S(db_url, poolclass=NullPool) as s_current, S(target_db_url, poolclass=NullPool) as s_target: m = Migration(s_current, s_target, **kwargs) m.set_safety(False) if create_extensions_only: log.info("Syncing extension creation only...") m.add_extension_changes(creates=True, drops=False) else: m.add_all_changes() if m.statements: if confirm: # pragma: no cover print("THE FOLLOWING CHANGES ARE PENDING:", end="\n\n") print(m.sql) print() if not confirm or prompt("Apply these changes?"): log.info("Applying...") m.apply() log.info("Applied.") else: if confirm: # pragma: no cover print("Not applying.") else: if confirm: # pragma: no cover print("Already synced.") current_schema_hash = schema_hash(db_url) if confirm: # pragma: no cover print(f"Schema hash: {current_schema_hash}")
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_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 diff( sql_statements: str, db: str, unsafe: bool = False, apply: bool = False, chatty: bool = False, ): base_uri = copy_url(db) patch = create_admin_patch(base_uri) patch.start() roles, statements = extract_roles(sql_statements) with temporary_database(base_uri) as sTemp: roles_m = Migration(sTemp, NullInspector()) from_roles = roles_m.changes.i_from.roles # Exclude all unspecified roles for k in set(from_roles.keys()) - set(roles.keys()): del from_roles[k] # Compare roles roles_m.add(statements_for_changes(from_roles, roles)) if roles_m.statements: roles_m.set_safety(True) roles_sql = roles_m.sql if chatty: print(pg_format(roles_sql.encode(), unquote=False).decode()) print(cf.bold("Applying roles...")) roles_m.apply() sTemp.commit() if chatty: print(cf.bold("Done.")) target_exists = database_exists(base_uri, test_can_select=True) with S(db) if target_exists else temporary_database(base_uri) as sFrom: # Run schema in temporary database try: raw_execute(sTemp, statements) except Exception as e: raise SQLSyntaxError(e, statements) # Compare m = Migration(sFrom, sTemp) m.set_safety(not unsafe) m.add_all_changes(privileges=True) if not m.statements: if chatty: print(cf.bold("All done! ✨")) print(f'Database "{base_uri.database}" is up to date.') return None, False sql = "" # Get SQL try: sql = m.sql except UnsafeMigrationException: m.set_safety(False) sql = m.sql formatted = pg_format(sql.encode(), unquote=False).decode() return formatted, True formatted = pg_format(sql.encode(), unquote=False).decode() if chatty: print(formatted) if apply: if chatty: print(cf.bold("Applying...")) m.apply() if chatty: print(cf.bold("All done! ✨")) print(f'Database "{base_uri.database}" has been updated.') return formatted, False
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