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