def test_basic_schemainspect():
a = ColumnInfo("a", "text", str)
a2 = ColumnInfo("a", "text", str)
b = ColumnInfo("b", "varchar", str, dbtypestr="varchar(10)")
b2 = ColumnInfo(
"b", "text", str, dbtypestr="text", default="'d'::text", not_null=True
)
assert a == a2
assert a == a
assert a != b
assert b != b2
alter = b2.alter_table_statements(b, "t")
assert alter == [
"alter table t alter column \"b\" set default 'd'::text;",
'alter table t alter column "b" set not null;',
'alter table t alter column "b" set data type text using "b"::text;',
]
alter = b.alter_table_statements(b2, "t")
assert alter == [
'alter table t alter column "b" drop default;',
'alter table t alter column "b" drop not null;',
'alter table t alter column "b" set data type varchar(10) using "b"::varchar(10);',
]
b.add_column_clause == 'add column "b"'
b.drop_column_clause == 'drop column "b"'
with temporary_database("sqlite") as dburl:
with raises(NotImplementedError):
with S(dburl) as s:
get_inspector(s)
def test_basic_schemainspect():
a = ColumnInfo('a', 'text', str)
a2 = ColumnInfo('a', 'text', str)
b = ColumnInfo('b', 'varchar', str, dbtypestr='varchar(10)')
b2 = ColumnInfo('b',
'text',
str,
dbtypestr='text',
default="'d'::text",
not_null=True)
assert a == a2
assert a == a
assert a != b
assert b != b2
alter = b2.alter_table_statements(b, 't')
assert alter == [
'alter table t alter column "b" set default \'d\'::text;',
'alter table t alter column "b" set not null;',
'alter table t alter column "b" set data type text;',
]
alter = b.alter_table_statements(b2, 't')
assert alter == [
'alter table t alter column "b" drop default;',
'alter table t alter column "b" drop not null;',
'alter table t alter column "b" set data type varchar(10);',
]
b.add_column_clause == 'add column "b"'
b.drop_column_clause == 'drop column "b"'
with temporary_database('sqlite') as dburl:
with raises(NotImplementedError):
with S(dburl) as s:
get_inspector(s)
def test_basic_schemainspect():
a = ColumnInfo("a", "text", str)
a2 = ColumnInfo("a", "text", str)
b = ColumnInfo("b", "varchar", str, dbtypestr="varchar(10)")
b2 = ColumnInfo("b",
"text",
str,
dbtypestr="text",
default="'d'::text",
not_null=True)
assert a == a2
assert a == a
assert a != b
assert b != b2
alter = b2.alter_table_statements(b, "t")
assert alter == [
"alter table t alter column \"b\" set default 'd'::text;",
'alter table t alter column "b" set not null;',
'alter table t alter column "b" set data type text using "b"::text;',
]
alter = b.alter_table_statements(b2, "t")
assert alter == [
'alter table t alter column "b" drop default;',
'alter table t alter column "b" drop not null;',
'alter table t alter column "b" set data type varchar(10) using "b"::varchar(10);',
]
b.add_column_clause == 'add column "b"'
b.drop_column_clause == 'drop column "b"'
with temporary_database("sqlite") as dburl:
with raises(NotImplementedError):
with S(dburl) as s:
get_inspector(s)
def _dburl(request):
count = 10
data = []
for x in range(count):
b = Book(name='Book {}'.format(x), a=x, b=x % 2, c=count - x, d=99)
if x == 1:
b.a = None
b.author = Author(name='Willy Shakespeare')
data.append(b)
for x in range(count):
author = Author(name='Author {}'.format(x))
abooks = []
for y in range((2 * x) % 10):
b = Book(name='Book {}-{}'.format(x, y),
a=x + y,
b=(y * x) % 2,
c=count - x,
d=99 - y)
b.author = author
if y % 4 != 0:
b.prequel = abooks[(2 * y + 1) % len(abooks)]
abooks.append(b)
data.append(b)
with temporary_database(request.param) as dburl:
with S(dburl) as s:
Base.metadata.create_all(s.connection())
s.add_all(data)
yield dburl
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 db_initialization(request):
from config import configs
with temporary_database() as dburi:
test_config = configs['test']
test_config.SQLALCHEMY_DATABASE_URI = dburi
load_from_app_model(dburi)
load_test_fixtures(dburi)
yield
def db_initialization(request):
from config import configs
with temporary_database() as dburi:
test_config = configs['test']
test_config.SQLALCHEMY_DATABASE_URI = dburi
load_from_app_model(dburi)
load_test_fixtures(dburi)
yield
def sync_db_structure_to_setup_method(db_url,
setup_method,
confirm=True,
create_extensions_only=False,
**kwargs):
with temporary_database(host="localhost") as temp_db_url:
setup_method(temp_db_url)
sync_db_structure_to_target_db(
db_url,
temp_db_url,
confirm=confirm,
create_extensions_only=create_extensions_only,
**kwargs,
)
def test_createdrop(tmpdir):
sqlite_path = str(tmpdir / "testonly.db")
urls = [
"postgresql:///sqlbag_testonly", "mysql+pymysql:///sqlbag_testonly"
]
for db_url in urls:
drop_database(db_url)
assert not drop_database(db_url)
assert not exists(db_url)
assert create_database(db_url)
assert exists(db_url)
if db_url.startswith("postgres"):
assert create_database(db_url,
template="template1",
wipe_if_existing=True)
else:
assert create_database(db_url, wipe_if_existing=True)
assert exists(db_url)
assert drop_database(db_url)
assert not exists(db_url)
db_url = "sqlite://" # in-memory special case
assert exists(db_url)
assert not create_database(db_url)
assert exists(db_url)
assert not drop_database(db_url)
assert exists(db_url)
db_url = "sqlite:///" + sqlite_path
assert not database_exists(db_url)
# selecting works because sqlite auto-creates
assert database_exists(db_url, test_can_select=True)
drop_database(db_url)
create_database(db_url)
assert exists(db_url)
drop_database(db_url)
assert not database_exists(db_url)
assert database_exists(db_url, test_can_select=True)
with temporary_database("sqlite") as dburi:
with S(dburi) as s:
s.execute("select 1")
def dburl(request):
count = 10
data = []
for x in range(count):
b = Book(name='Book {}'.format(x), a=x, b=x % 2, c=count - x, d=99)
if x == 1:
b.a = None
b.author = Author(name='Willy Shakespeare')
data.append(b)
with temporary_database(request.param) as dburl:
with S(dburl) as s:
Base.metadata.create_all(s.connection())
s.add_all(data)
yield dburl
def test_createdrop(tmpdir):
sqlite_path = str(tmpdir / "testonly.db")
urls = ["postgresql:///sqlbag_testonly", "mysql+pymysql:///sqlbag_testonly"]
for db_url in urls:
drop_database(db_url)
assert not drop_database(db_url)
assert not exists(db_url)
assert create_database(db_url)
assert exists(db_url)
if db_url.startswith("postgres"):
assert create_database(db_url, template="template1", wipe_if_existing=True)
else:
assert create_database(db_url, wipe_if_existing=True)
assert exists(db_url)
assert drop_database(db_url)
assert not exists(db_url)
db_url = "sqlite://" # in-memory special case
assert exists(db_url)
assert not create_database(db_url)
assert exists(db_url)
assert not drop_database(db_url)
assert exists(db_url)
db_url = "sqlite:///" + sqlite_path
assert not database_exists(db_url)
# selecting works because sqlite auto-creates
assert database_exists(db_url, test_can_select=True)
drop_database(db_url)
create_database(db_url)
assert exists(db_url)
drop_database(db_url)
assert not database_exists(db_url)
assert database_exists(db_url, test_can_select=True)
with temporary_database("sqlite") as dburi:
with S(dburi) as s:
s.execute("select 1")
def joined_inheritance_dburl(request):
with temporary_database(request.param, host="localhost") as dburl:
with S(dburl) as s:
JoinedInheritanceBase.metadata.create_all(s.connection())
s.add_all(
[
Mammal(
name="Human", vertebra_count=33, leg_count=2, nipple_count=2
),
Mammal(name="Dog", vertebra_count=36, leg_count=4, nipple_count=10),
Invertebrate(name="Jellyfish"),
Invertebrate(name="Jellyfish"),
Arthropod(name="Spider", leg_count=8),
Arthropod(name="Ant", leg_count=6),
Arthropod(name="Scorpion", leg_count=8),
Arthropod(name="Beetle", leg_count=6),
Vertebrate(name="Snake", vertebra_count=300),
]
)
yield dburl
def _dburl(request):
count = 10
data = []
for x in range(count):
b = Book(name="Book {}".format(x), a=x, b=x % 2, c=count - x, d=99)
if x == 1:
b.a = None
b.author = Author(name="Willy Shakespeare", info="Old timer")
data.append(b)
for x in range(count):
author = Author(name="Author {}".format(x),
info="Rank {}".format(count + 1 - x))
abooks = []
for y in range((2 * x) % 10):
b = Book(
name="Book {}-{}".format(x, y),
a=x + y,
b=(y * x) % 2,
c=count - x,
d=99 - y,
)
b.author = author
if y % 4 != 0:
b.prequel = abooks[(2 * y + 1) % len(abooks)]
abooks.append(b)
data.append(b)
data += [
Light(colour=Colour(i % 3), intensity=(i * 13) % 53, myint=i)
for i in range(99)
]
with temporary_database(request.param, host="localhost") as dburl:
with S(dburl) as s:
Base.metadata.create_all(s.connection())
s.add_all(data)
yield dburl
def joined_inheritance_dburl(request):
with temporary_database(request.param) as dburl:
with S(dburl) as s:
JoinedInheritanceBase.metadata.create_all(s.connection())
s.add_all([
Mammal(name='Human',
vertebra_count=33,
leg_count=2,
nipple_count=2),
Mammal(name='Dog',
vertebra_count=36,
leg_count=4,
nipple_count=10),
Invertebrate(name='Jellyfish'),
Invertebrate(name='Jellyfish'),
Arthropod(name='Spider', leg_count=8),
Arthropod(name='Ant', leg_count=6),
Arthropod(name='Scorpion', leg_count=8),
Arthropod(name='Beetle', leg_count=6),
Vertebrate(name='Snake', vertebra_count=300),
])
yield dburl
def test_orm_stuff():
with temporary_database() as url:
with S(url) as s:
Base.metadata.create_all(s.bind.engine)
with S(url) as s:
x = Something(name="kanye")
s.add(x)
s.commit()
things = s.query(Something).all()
x1 = things[0]
prefix = "u" if six.PY2 else ""
repr_str = "Something(id=1, name={}'kanye')".format(prefix)
assert repr(x1) == str(x1) == repr_str
assert metadata_from_session(s).schema == Base.metadata.schema
assert x1._sqlachanges == {}
assert x1._ordereddict == OrderedDict([("id", 1), ("name", "kanye")])
x1.name = "kanye west"
assert x1._sqlachanges == {"name": ["kanye", "kanye west"]}
s.commit()
assert x1._sqlachanges == {}
def test_orm_stuff():
with temporary_database() as url:
with S(url) as s:
Base.metadata.create_all(s.bind.engine)
with S(url) as s:
x = Something(name="kanye")
s.add(x)
s.commit()
things = s.query(Something).all()
x1 = things[0]
prefix = "u" if six.PY2 else ""
repr_str = "Something(id=1, name={}'kanye')".format(prefix)
assert repr(x1) == str(x1) == repr_str
assert metadata_from_session(s).schema == Base.metadata.schema
assert x1._sqlachanges == {}
assert x1._ordereddict == OrderedDict([("id", 1),
("name", "kanye")])
x1.name = "kanye west"
assert x1._sqlachanges == {"name": ["kanye", "kanye west"]}
s.commit()
assert x1._sqlachanges == {}
def mysqldb():
with temporary_database("mysql") as dburi:
yield dburi
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 test_paging():
for db in ['postgresql', 'mysql']:
with temporary_database(db) as dburl:
fixture_setup(dburl)
do_orm_tests(dburl)
do_core_tests(dburl)
def db():
with temporary_database("postgresql") as dburi:
yield dburi
def db():
with temporary_database("postgresql") as dburi:
yield dburi
def db():
with temporary_database(host="localhost") as dburi:
yield dburi
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, 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
