def __init__(self,
x_from,
x_target,
schema=None,
tables=None,
tables_only=False,
no_drops=False):
self.statements = Statements(no_drops=no_drops)
self.tables = tables
self.tables_only = tables_only
self.no_drops = no_drops
self.changes = Changes(None, None, tables, tables_only)
self.schema = schema.split(",") if schema is not None else None
if isinstance(x_from, DBInspector):
self.changes.i_from = x_from
else:
self.changes.i_from = get_inspector(
x_from,
schema=self._determine_schema(),
tables=tables,
tables_only=tables_only)
if x_from:
self.s_from = x_from
if isinstance(x_target, DBInspector):
self.changes.i_target = x_target
else:
self.changes.i_target = get_inspector(
x_target,
schema=self._determine_schema(False),
tables=tables,
tables_only=tables_only)
if x_target:
self.s_target = x_target
def test_generated_columns(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version < 12:
pytest.skip("generated columns not supported in < 12")
s.execute("""create table t(
c int generated always as (1) stored
) """)
i = get_inspector(s)
t_key = '"public"."t"'
assert list(i.tables.keys())[0] == t_key
t = i.tables[t_key]
EXPECTED = ("1", False, False, True)
c = t.columns["c"]
tup = (c.default, c.is_identity, c.is_identity_always, c.is_generated)
assert tup == EXPECTED
EXPECTED = '"c" integer generated always as (1) stored'
assert c.creation_clause == EXPECTED
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 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_sequences(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version < 10:
pytest.skip("identity columns not supported in 9")
s.execute("""
create table t(id serial);
""")
s.execute("""
CREATE SEQUENCE serial START 101;
""")
s.execute("""
create table t2(id integer generated always as identity);
""")
i = get_inspector(s)
seqs = list(i.sequences)
assert seqs == ['"public"."serial"', '"public"."t_id_seq"']
unowned = i.sequences['"public"."serial"']
assert unowned.table_name is None
owned = i.sequences['"public"."t_id_seq"']
assert owned.table_name == "t"
assert owned.quoted_full_table_name == '"public"."t"'
assert owned.quoted_table_and_column_name == '"public"."t"."id"'
def test_postgres_inspect_singleschema(db):
with S(db) as s:
setup_pg_schema(s)
i = get_inspector(s, schema="otherschema")
asserts_pg_singleschema(i, "otherschema")
i = get_inspector(s, schema="public")
asserts_pg_singleschema(i, "public")
def test_postgres_inspect_singleschema(db):
with S(db) as s:
setup_pg_schema(s)
i = get_inspector(s, schema='otherschema')
asserts_pg_singleschema(i, 'otherschema')
i = get_inspector(s, schema='public')
asserts_pg_singleschema(i, 'public')
def test_index_defs(db):
with S(db) as s:
ii = get_inspector(s)
if ii.pg_version <= 10:
return
s.execute(INDEX_DEFS)
ii = get_inspector(s)
indexes_keys = list(ii.indexes.keys())
EXPECTED = ['"s"."i"', '"s"."iii"', '"s"."iii_exp"', '"s"."pk"']
assert indexes_keys == EXPECTED
i = ii.indexes['"s"."i"']
assert i.index_columns == ["a"]
assert i.key_columns == ["a"]
assert i.included_columns == []
assert i.key_expressions is None
i = ii.indexes['"s"."iii"']
assert i.index_columns == ["b", "a", "id"]
assert i.key_columns == ["b", "a"]
assert i.included_columns == ["id"]
assert i.key_expressions is None
i = ii.indexes['"s"."iii_exp"']
assert i.index_columns is None
assert i.key_columns is None
assert i.included_columns is None
assert i.key_expressions == "lower((id)::text)"
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_postgres_inspect_singleschema(db):
with S(db) as s:
setup_pg_schema(s)
i = get_inspector(s, schema="otherschema")
asserts_pg_singleschema(i, "otherschema")
i = get_inspector(s, schema="public")
asserts_pg_singleschema(i, "public")
def test_dep_order(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version <= 10:
return
# s.execute(CREATES)
s.execute(CREATES_FK)
i = get_inspector(s)
# dependency_order doesn't work in py2
if sys.version_info < (3, 0):
return
create_order = i.dependency_order(include_fk_deps=True, )
drop_order = i.dependency_order(
drop_order=True,
include_fk_deps=True,
)
for x in drop_order:
thing = i.get_dependency_by_signature(x)
drop = thing.drop_statement
s.execute(drop)
for x in create_order:
thing = i.get_dependency_by_signature(x)
create = thing.create_statement
s.execute(create)
def assert_postgres_inspect(db, has_timescale=False):
with S(db) as s:
if has_timescale:
s.execute("create extension if not exists timescaledb;")
setup_pg_schema(s)
i = get_inspector(s)
asserts_pg(i, has_timescale)
assert i == i == get_inspector(s)
def test_collations(db):
with S(db) as s:
s.execute("""
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
create schema x;
CREATE COLLATION x.german (provider = icu, locale = 'de-DE-x-icu');
CREATE COLLATION naturalsort (provider = icu, locale = 'en-u-kn-true');
""")
i = get_inspector(s)
assert list(i.collations) == ['"public"."naturalsort"', '"x"."german"']
gc = i.collations['"x"."german"']
assert (
gc.create_statement ==
"""create collation if not exists "x"."german" (provider = 'icu', locale = 'de-DE-x-icu');"""
)
nc = i.collations['"public"."naturalsort"']
assert (
nc.create_statement ==
"""create collation if not exists "public"."naturalsort" (provider = 'icu', locale = 'en-u-kn-true');"""
)
assert gc == gc
assert gc != nc
with S(db) as s:
s.execute("""
CREATE TABLE tt (
id int,
t text,
tde text collate "POSIX"
);
""")
i = get_inspector(s)
tt = i.tables['"public"."tt"']
t = tt.columns["t"]
tde = tt.columns["tde"]
assert t.collation is None
assert tde.collation == "POSIX"
assert (t.alter_data_type_clause ==
'alter column "t" set data type text using "t"::text')
assert (
tde.alter_data_type_clause ==
'alter column "tde" set data type text collate "POSIX" using "tde"::text'
)
def test_indexes(db):
with S(db) as s:
s.execute(CREATE)
i1 = get_inspector(s, schema="it")
# Recreate schema.
# Functions oids will be changed
s.execute(CREATE)
i2 = get_inspector(s, schema="it")
assert i1.indexes == i2.indexes
def test_postgres_inspect_excludeschema(db):
with S(db) as s:
setup_pg_schema(s)
s.execute("create schema thirdschema;")
s.execute("create schema forthschema;")
i = get_inspector(s, exclude_schema="otherschema")
asserts_pg_excludedschema(i, ["public", "forthschema", "thirdschema"],
"otherschema")
i = get_inspector(s, exclude_schema="forthschema")
asserts_pg_excludedschema(i, ["public", "otherschema", "thirdschema"],
"forthschema")
def test_types_and_domains(db):
with S(db) as s:
s.execute(CREATE)
i = get_inspector(s)
compfoo = i.types['"public"."compfoo"']
assert len(compfoo.columns) == 2
assert (
compfoo.create_statement
== """\
create type "public"."compfoo" as (
"f1" int4,
"f2" text
);"""
)
compfoo.name = "compfoo2"
s.execute(compfoo.create_statement)
i = get_inspector(s)
c1 = i.types['"public"."compfoo"']
c2 = i.types['"public"."compfoo2"']
c2.name = "compfoo"
assert c1 == c2
postal = i.domains['"public"."us_postal_code"']
assert postal.data_type == "text"
assert postal.not_null is False
assert postal.constraint_name == "us_postal_code_check"
assert postal.check == CHECK
assert (
postal.create_statement
== """\
create domain "public"."us_postal_code"
as text
null
{}
""".format(
CHECK
)
)
assert postal.drop_statement == """drop domain "public"."us_postal_code";"""
postal.name = "postal2"
s.execute(postal.create_statement)
def test_identity_columns(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version < 10:
pytest.skip("identity columns not supported in 9")
s.execute("""create table t(
a int,
b int default 1,
--c int generated always as (1) stored,
d int generated always as identity,
e int generated by default as identity
) """)
i = get_inspector(s)
t_key = '"public"."t"'
assert list(i.tables.keys())[0] == t_key
t = i.tables[t_key]
assert list(t.columns) == "a b d e".split()
EXPECTED = [
(None, False, False, False),
("1", False, False, False),
# ("1", False, False, True),
(None, True, True, False),
(None, True, False, False),
]
cols = list(t.columns.values())
for c, expected in zip(cols, EXPECTED):
tup = (c.default, c.is_identity, c.is_identity_always,
c.is_generated)
assert tup == expected
EXPECTED = [
'"a" integer',
'"b" integer default 1',
# "c" integer generated always as (1) stored',
'"d" integer generated always as identity not null',
'"e" integer generated by default as identity not null',
]
for c, expected in zip(cols, EXPECTED):
assert c.creation_clause == expected
def test_fk_info(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version <= 10:
return
s.execute(CREATES_FK)
i = get_inspector(s)
fk = i.constraints['"public"."salary"."salary_emp_id_fkey"']
assert fk.is_fk is True
assert fk.quoted_full_foreign_table_name == '"other"."emp"'
def test_inheritance(db):
with S(db) as s:
s.execute(INHERITANCE)
ii = get_inspector(s)
normal = ii.tables['"public"."normal"']
parent = ii.tables['"public"."parent"']
child = ii.tables['"public"."child"']
assert list(normal.columns) == ["id"]
assert list(parent.columns) == ["t"]
assert list(child.columns) == "t a b".split()
assert normal.columns["id"].is_inherited is False
assert parent.columns["t"].is_inherited is False
if ii.pg_version <= 9:
return
# uncertain why this fails on <=pg9 only
assert child.columns["t"].is_inherited is True
for c in "a b".split():
child.columns[c].is_inherited is False
assert parent.dependents == ['"public"."child"']
assert child.dependent_on == ['"public"."parent"']
def __init__(self, x_from, x_target, schema=None):
self.statements = Statements()
self.changes = Changes(None, None)
self.schema = schema
if isinstance(x_from, DBInspector):
self.changes.i_from = x_from
else:
self.changes.i_from = get_inspector(x_from, schema=schema)
if x_from:
self.s_from = x_from
if isinstance(x_target, DBInspector):
self.changes.i_target = x_target
else:
self.changes.i_target = get_inspector(x_target, schema=schema)
if x_target:
self.s_target = x_target
def test_relationships(db):
# commented-out dependencies are the dependencies that aren't tracked directly by postgres
with S(db) as s:
s.execute(CREATES)
with S(db) as s:
i = get_inspector(s)
dependencies_by_name = {
k: v.dependent_on
for k, v in i.selectables.items() if v.dependent_on
}
assert dependencies_by_name == {
# '"public"."depends_on_vvv"(t text)': [
# '"public"."vvv"'
# ],
'"public"."depends_on_fff"': ['"public"."fff"(t text)'],
'"public"."doubledep"': [
'"public"."depends_on_fff"',
'"public"."depends_on_vvv"(t text)',
],
}
dependents_by_name = {
k: v.dependents
for k, v in i.selectables.items() if v.dependents
}
assert dependents_by_name == {
# '"public"."vvv"': ['"public"."depends_on_vvv"(t text)'],
'"public"."fff"(t text)': ['"public"."depends_on_fff"'],
'"public"."depends_on_fff"': ['"public"."doubledep"'],
'"public"."depends_on_vvv"(t text)': ['"public"."doubledep"'],
}
# testing recursive deps
dependencies_by_name = {
k: v.dependent_on_all
for k, v in i.selectables.items() if v.dependent_on_all
}
assert dependencies_by_name == {
# '"public"."depends_on_vvv"(t text)': [
# '"public"."vvv"'
# ],
'"public"."depends_on_fff"': ['"public"."fff"(t text)'],
'"public"."doubledep"': [
'"public"."depends_on_fff"',
'"public"."depends_on_vvv"(t text)',
'"public"."fff"(t text)',
],
# '"public"."vvv"'
}
dependents_by_name = {
k: v.dependents_all
for k, v in i.selectables.items() if v.dependents_all
}
assert dependents_by_name == {
# '"public"."vvv"': ['"public"."depends_on_vvv"(t text)', '"public"."doubledep"'],
'"public"."fff"(t text)': [
'"public"."depends_on_fff"',
'"public"."doubledep"',
],
'"public"."depends_on_fff"': ['"public"."doubledep"'],
'"public"."depends_on_vvv"(t text)': ['"public"."doubledep"'],
}
def test_lineendings(db):
with S(db) as s:
s.execute(FUNC_N)
i = get_inspector(s)
f = i.functions['"public"."depends_on_vvv"(t text)']
assert f.definition == "select\r\n1"
def apply(self):
for stmt in self.statements:
raw_execute(self.s_from, stmt)
self.changes.i_from = get_inspector(self.s_from, schema=self.schema)
safety_on = self.statements.safe
self.clear()
self.set_safety(safety_on)
def __init__(self, x_from, x_target, schema=None):
self.statements = Statements()
self.changes = Changes(None, None)
self.schema = schema
if isinstance(x_from, DBInspector):
self.changes.i_from = x_from
else:
self.changes.i_from = get_inspector(x_from, schema=schema)
if x_from:
self.s_from = x_from
if isinstance(x_target, DBInspector):
self.changes.i_target = x_target
else:
self.changes.i_target = get_inspector(x_target, schema=schema)
if x_target:
self.s_target = x_target
def apply(self):
for stmt in self.statements:
raw_execute(self.s_from, stmt)
self.changes.i_from = get_inspector(self.s_from, schema=self.schema)
safety_on = self.statements.safe
self.clear()
self.set_safety(safety_on)
def test_relationships(db):
# commented-out dependencies are the dependencies that aren't tracked directly by postgres
with S(db) as s:
s.execute(CREATES)
with S(db) as s:
i = get_inspector(s)
dependencies_by_name = {
k: v.dependent_on for k, v in i.selectables.items() if v.dependent_on
}
assert dependencies_by_name == {
# '"public"."depends_on_vvv"(t text)': [
# '"public"."vvv"'
# ],
'"public"."depends_on_fff"': ['"public"."fff"(t text)'],
'"public"."doubledep"': [
'"public"."depends_on_fff"',
'"public"."depends_on_vvv"(t text)',
],
}
dependents_by_name = {
k: v.dependents for k, v in i.selectables.items() if v.dependents
}
assert dependents_by_name == {
# '"public"."vvv"': ['"public"."depends_on_vvv"(t text)'],
'"public"."fff"(t text)': ['"public"."depends_on_fff"'],
'"public"."depends_on_fff"': ['"public"."doubledep"'],
'"public"."depends_on_vvv"(t text)': ['"public"."doubledep"'],
}
# testing recursive deps
dependencies_by_name = {
k: v.dependent_on_all
for k, v in i.selectables.items()
if v.dependent_on_all
}
assert dependencies_by_name == {
# '"public"."depends_on_vvv"(t text)': [
# '"public"."vvv"'
# ],
'"public"."depends_on_fff"': ['"public"."fff"(t text)'],
'"public"."doubledep"': [
'"public"."depends_on_fff"',
'"public"."depends_on_vvv"(t text)',
'"public"."fff"(t text)',
],
# '"public"."vvv"'
}
dependents_by_name = {
k: v.dependents_all for k, v in i.selectables.items() if v.dependents_all
}
assert dependents_by_name == {
# '"public"."vvv"': ['"public"."depends_on_vvv"(t text)', '"public"."doubledep"'],
'"public"."fff"(t text)': [
'"public"."depends_on_fff"',
'"public"."doubledep"',
],
'"public"."depends_on_fff"': ['"public"."doubledep"'],
'"public"."depends_on_vvv"(t text)': ['"public"."doubledep"'],
}
def has_column(engine, table, column):
insp = get_inspector(engine)
if table not in insp.tables:
return False
tbl = insp.tables[table]
for col in tbl.columns:
if col == column:
return True
return False
def test_can_replace(db):
with S(db) as s:
s.execute(CREATES)
with S(db) as s:
s.execute("""create table t(a int, b varchar);""")
i = get_inspector(s)
s.execute(
"""
create or replace view vvv as select similarity('aaa', 'aaabc')::decimal as x, 1 as y;
"""
)
i2 = get_inspector(s)
v1 = i.views['"public"."vvv"']
v2 = i2.views['"public"."vvv"']
assert v1 != v2
assert v2.can_replace(v1)
s.execute(
"""
drop function "public"."fff"(t text) cascade;
"""
)
s.execute(
"""
create or replace function "public"."fff"(t text)
returns TABLE(score decimal, x integer) as
$$ select similarity('aaa', 'aaabc')::decimal, 1 as x $$
language SQL VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
drop table t;
create table t(a int, b varchar primary key not null, c int);
"""
)
i2 = get_inspector(s)
f1 = i.selectables['"public"."fff"(t text)']
f2 = i2.selectables['"public"."fff"(t text)']
assert f1 != f2
assert f2.can_replace(f1) is False
t1 = i.selectables['"public"."t"']
t2 = i2.selectables['"public"."t"']
assert t2.can_replace(t1) is True
assert t1.can_replace(t2) is False
def test_constraints(db):
with S(db) as s:
s.execute(CREATE_CONST)
i = get_inspector(s)
constraints_keys = list(i.constraints.keys())
assert constraints_keys == ['"public"."t"."t_pkey"']
indexes_keys = list(i.indexes.keys())
assert indexes_keys == ['"public"."t_pkey"']
def apply(self, quiet=False):
n = len(self.statements)
log.debug(f"Applying migration with {n} operations")
for stmt in self.statements:
self._exec(stmt, quiet=quiet)
self.changes.i_from = get_inspector(self.s_from,
schema=self.schema,
exclude_schema=self.exclude_schema)
safety_on = self.statements.safe
self.clear()
self.set_safety(safety_on)
def test_inherit(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version <= 9:
return
s.execute("""
CREATE TABLE entity_bindings (
id BIGSERIAL,
entity_type TEXT NOT NULL,
entity_id BIGINT NOT NULL
);
CREATE TABLE entity_bindings_A (
CONSTRAINT "entity_type must be A" CHECK("entity_type" = 'A'),
UNIQUE("entity_id", "entity_type")
) INHERITS (entity_bindings)
;
CREATE TABLE entity_bindings_B (
CONSTRAINT "entity_type must be B" CHECK("entity_type" = 'B'),
UNIQUE("entity_id", "entity_type")
) INHERITS (entity_bindings)
;
CREATE TABLE entity_bindings_C (
CONSTRAINT "entity_type must be C" CHECK("entity_type" = 'C'),
UNIQUE("entity_id", "entity_type")
) INHERITS (entity_bindings)
;
""")
i = get_inspector(s)
t = i.tables['"public"."entity_bindings"']
tb = i.tables['"public"."entity_bindings_b"']
assert tb.parent_table == '"public"."entity_bindings"'
assert t.parent_table is None
assert tb.is_inheritance_child_table is True
assert tb.uses_partitioning is False
def test_kinds(db):
with S(db) as s:
s.execute(FUNC)
i = get_inspector(s)
f = i.functions['"public"."ordinary_f"(t text)']
assert f.definition == "select\r\n1"
assert f.kind == "f"
if i.pg_version < 11:
return
s.execute(PROC)
i = get_inspector(s)
p = i.functions['"public"."proc"(a integer, b integer)']
assert p.definition == "\nselect a, b;\n"
assert p.kind == "p"
assert (
p.drop_statement ==
'drop procedure if exists "public"."proc"(a integer, b integer);')
def test_can_replace(db):
with S(db) as s:
s.execute(CREATES)
with S(db) as s:
s.execute("""create table t(a int, b varchar);""")
i = get_inspector(s)
s.execute("""
create or replace view vvv as select similarity('aaa', 'aaabc')::decimal as x, 1 as y;
""")
i2 = get_inspector(s)
v1 = i.views['"public"."vvv"']
v2 = i2.views['"public"."vvv"']
assert v1 != v2
assert v2.can_replace(v1)
s.execute("""
drop function "public"."fff"(t text) cascade;
""")
s.execute("""
create or replace function "public"."fff"(t text)
returns TABLE(score decimal, x integer) as
$$ select similarity('aaa', 'aaabc')::decimal, 1 as x $$
language SQL VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
drop table t;
create table t(a int, b varchar primary key not null, c int);
""")
i2 = get_inspector(s)
f1 = i.selectables['"public"."fff"(t text)']
f2 = i2.selectables['"public"."fff"(t text)']
assert f1 != f2
assert f2.can_replace(f1) is False
t1 = i.selectables['"public"."t"']
t2 = i2.selectables['"public"."t"']
assert t2.can_replace(t1) is True
assert t1.can_replace(t2) is False
def __init__(self, x_from, x_target, schema=None, exclude_schema=None):
self.statements = Statements()
self.changes = Changes(None, None)
if schema and exclude_schema:
raise ValueError(
"You cannot have both a schema and excluded schema")
self.schema = schema
self.exclude_schema = exclude_schema
if isinstance(x_from, DBInspector):
self.changes.i_from = x_from
else:
self.changes.i_from = get_inspector(x_from,
schema=schema,
exclude_schema=exclude_schema)
if x_from:
self.s_from = x_from
if isinstance(x_target, DBInspector):
self.changes.i_target = x_target
else:
self.changes.i_target = get_inspector(
x_target, schema=schema, exclude_schema=exclude_schema)
if x_target:
self.s_target = x_target
def test_table_dependency_order(db):
with S(db) as s:
i = get_inspector(s)
if i.pg_version <= 9:
return
s.execute(INHERITANCE)
ii = get_inspector(s)
dep_order = ii.dependency_order()
assert list(ii.tables.keys()) == [
'"public"."child"',
'"public"."normal"',
'"public"."parent"',
]
assert dep_order == [
'"public"."parent"',
'"public"."normal"',
'"public"."child"',
]
def test_long_identifiers(db):
with S(db) as s:
for i in range(50, 70):
ident = "x" * i
truncated = "x" * min(i, 63)
func = FUNC.replace("ordinary_f", ident)
s.execute(func)
i = get_inspector(s)
expected_sig = '"public"."{}"(t text)'.format(truncated)
f = i.functions[expected_sig]
assert f.full_definition is not None
def test_postgres_inspect(db):
with S(db) as s:
setup_pg_schema(s)
i = get_inspector(s)
asserts_pg(i)
assert i == i == get_inspector(s)
def test_weird_names(db):
with S(db) as s:
s.execute("""create table "a(abc=3)"(id text) """)
i = get_inspector(s)
assert list(i.tables.keys())[0] == '"public"."a(abc=3)"'
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 test_rls(db):
with S(db) as s:
s.execute(
"""
CREATE TABLE t(id uuid, a text, b decimal);
"""
)
i = get_inspector(s)
t = i.tables['"public"."t"']
assert t.rowsecurity is False
assert (
t.alter_rls_statement
== 'alter table "public"."t" disable row level security;'
)
t.rowsecurity = True
s.execute(t.alter_rls_statement)
i = get_inspector(s)
t = i.tables['"public"."t"']
assert t.rowsecurity is True
assert (
t.alter_rls_statement
== 'alter table "public"."t" enable row level security;'
)
create_role(s, schemainspect_test_role)
s.execute(
f"""
CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO {schemainspect_test_role}
USING (manager = current_user);
create policy "insert_gamer"
on accounts
as permissive
for insert
to {schemainspect_test_role}
with check (manager = current_user);
"""
)
i = get_inspector(s)
pname = '"public"."accounts"."account_managers"'
t = i.rlspolicies[pname]
assert t.name == "account_managers"
assert t.schema == "public"
assert t.table_name == "accounts"
assert t.commandtype == "*"
assert t.permissive is True
assert t.roles == ["schemainspect_test_role"]
assert t.qual == "(manager = (CURRENT_USER)::text)"
assert t.withcheck is None
assert (
t.create_statement
== """create policy "account_managers"
on "public"."accounts"
as permissive
for all
to schemainspect_test_role
using (manager = (CURRENT_USER)::text);
"""
)
assert (
t.drop_statement == 'drop policy "account_managers" on "public"."accounts";'
)
s.execute(t.drop_statement)
s.execute(t.create_statement)
i = get_inspector(s)
t = i.rlspolicies[pname]
assert t.name == "account_managers"
assert t.schema == "public"
assert t.table_name == "accounts"
assert t.commandtype == "*"
assert t.permissive is True
assert t.roles == ["schemainspect_test_role"]
assert t.qual == "(manager = (CURRENT_USER)::text)"
assert t.withcheck is None
pname = '"public"."accounts"."insert_gamer"'
t = i.rlspolicies[pname]
assert t.name == "insert_gamer"
assert t.schema == "public"
assert t.table_name == "accounts"
assert t.commandtype == "a"
assert t.permissive is True
assert t.roles == ["schemainspect_test_role"]
assert t.withcheck == "(manager = (CURRENT_USER)::text)"
assert t.qual is None
assert (
t.create_statement
== """create policy "insert_gamer"
on "public"."accounts"
as permissive
for insert
to schemainspect_test_role
with check (manager = (CURRENT_USER)::text);
"""
)
assert t.drop_statement == 'drop policy "insert_gamer" on "public"."accounts";'
def inspect_from(self):
self.changes.i_from = get_inspector(self.s_from, schema=self.schema)
def inspect_target(self):
self.changes.i_target = get_inspector(self.s_target, schema=self.schema)
def test_empty():
x = NullInspector()
assert x.tables == od()
assert x.relations == od()
assert type(schemainspect.get_inspector(None)) == NullInspector
def test_partitions(db):
with S(db) as s:
s.execute(
"""
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
)
PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006 PARTITION OF measurement
FOR VALUES FROM ('2006-01-01') TO ('2007-01-01');
"""
)
i = get_inspector(s)
assert list(i.tables) == ['"public"."measurement"', '"public"."measurement_y2006"']
m2006 = i.tables['"public"."measurement_y2006"']
m = i.tables['"public"."measurement"']
assert m.parent_table is None
assert m2006.parent_table == '"public"."measurement"'
assert m2006.partition_def == "FOR VALUES FROM ('2006-01-01') TO ('2007-01-01')"
assert m.partition_def == "RANGE (logdate)"
assert (
m.create_statement
== """create table "public"."measurement" (
"city_id" integer not null,
"logdate" date not null,
"peaktemp" integer,
"unitsales" integer
) partition by RANGE (logdate);
"""
)
assert (
m2006.create_statement
== """create table partition of "public"."measurement" FOR VALUES FROM ('2006-01-01') TO ('2007-01-01');
"""
)
assert m.is_child_table is False
assert m.contains_data is False
assert m.is_alterable is True
assert m.is_partitioned is True
assert m.uses_partitioning is True
assert m2006.is_child_table is True
assert m2006.contains_data is True
assert m2006.is_alterable is False
assert m2006.is_partitioned is False
assert m2006.uses_partitioning is True
with S(db) as s:
s.execute(
"""
CREATE TABLE plain (id int);
"""
)
i = get_inspector(s)
p = i.tables['"public"."plain"']
assert p.is_child_table is False
assert p.contains_data is True
assert p.is_alterable is True
assert p.is_partitioned is False
assert p.uses_partitioning is False
