def asserts_pg(i):
# schemas
assert list(i.schemas.keys()) == ["otherschema", "public"]
otherschema = i.schemas["otherschema"]
assert i.schemas["public"] != i.schemas["otherschema"]
assert otherschema.create_statement == 'create schema if not exists "otherschema";'
assert otherschema.drop_statement == 'drop schema if exists "otherschema";'
# to_pytype
assert to_pytype(i.dialect, "integer") == int
assert to_pytype(i.dialect, "nonexistent") == type(None) # noqa
# dialect
assert i.dialect.name == "postgresql"
# tables and views
films = n("films")
v_films = n("v_films")
v_films2 = n("v_films2")
v = i.views[v_films]
public_views = od(
(k, v) for k, v in i.views.items() if v.schema == "public")
assert list(public_views.keys()) == [v_films, v_films2]
assert v.columns == FILMS_COLUMNS
assert v.create_statement == VDEF
assert v == v
assert v == deepcopy(v)
assert v.drop_statement == "drop view if exists {};".format(v_films)
v = i.views[v_films]
# dependencies
assert v.dependent_on == [films]
v = i.views[v_films2]
assert v.dependent_on == [v_films]
for k, r in i.relations.items():
for dependent in r.dependents:
assert k in i.relations[dependent].dependent_on
for dependency in r.dependent_on:
assert k in i.relations[dependency].dependents
# materialized views
mv_films = n("mv_films")
mv = i.materialized_views[mv_films]
assert list(i.materialized_views.keys()) == [mv_films]
assert mv.columns == FILMS_COLUMNS
assert mv.create_statement == MVDEF
assert mv.drop_statement == "drop materialized view if exists {};".format(
mv_films)
# materialized view indexes
assert n("mv_films_title_idx") in mv.indexes
# functions
films_f = n("films_f") + "(d date, def_t text, def_d date)"
inc_f = n("inc_f") + "(integer)"
inc_f_noargs = n("inc_f_noargs") + "()"
inc_f_out = n("inc_f_out") + "(integer, OUT outparam integer)"
public_funcs = [k for k, v in i.functions.items() if v.schema == "public"]
assert public_funcs == [films_f, inc_f, inc_f_noargs, inc_f_out]
f = i.functions[films_f]
f2 = i.functions[inc_f]
f3 = i.functions[inc_f_noargs]
f4 = i.functions[inc_f_out]
assert f == f
assert f != f2
assert f.columns == FILMSF_COLUMNS
assert f.inputs == FILMSF_INPUTS
assert f3.inputs == []
assert list(f2.columns.values())[0].name == "inc_f"
assert list(f3.columns.values())[0].name == "inc_f_noargs"
assert list(f4.columns.values())[0].name == "outparam"
fdef = i.functions[films_f].definition
assert fdef == "select 'a'::varchar, '2014-01-01'::date"
assert f.create_statement == FDEF
assert (
f.drop_statement ==
'drop function if exists "public"."films_f"(d date, def_t text, def_d date);'
)
assert f.comment == "films_f comment"
assert f2.comment is None
# extensions
assert [e.quoted_full_name for e in i.extensions.values()] == [
n("plpgsql", schema="pg_catalog"),
n("pg_trgm"),
]
# constraints
cons = i.constraints['"public"."films"."firstkey"']
assert (
cons.create_statement ==
'alter table "public"."films" add constraint "firstkey" PRIMARY KEY using index "firstkey";'
)
# tables
t_films = n("films")
t = i.tables[t_films]
empty = i.tables[n("emptytable")]
assert empty.comment == "emptytable comment"
# empty tables
assert empty.columns == od()
assert (empty.create_statement == """create table "public"."emptytable" (
);
""")
# create and drop tables
assert t.create_statement == T_CREATE
assert t.drop_statement == "drop table {};".format(t_films)
assert t.alter_table_statement("x") == "alter table {} x;".format(t_films)
# table indexes
assert n("films_title_idx") in t.indexes
# privileges
g = InspectedPrivilege("table", "public", "films", "select", "postgres")
g = i.privileges[g.key]
assert g.create_statement == "grant select on table {} to postgres;".format(
t_films)
assert g.drop_statement == "revoke select on table {} from postgres;".format(
t_films)
# composite types
ct = i.composite_types[n("ttt")]
assert [(x.name, x.dbtype) for x in ct.columns.values()] == [
("a", "integer"),
("b", "text"),
]
assert (ct.create_statement ==
'create type "public"."ttt" as ("a" integer, "b" text);')
assert ct.drop_statement == 'drop type "public"."ttt";'
# enums
assert i.enums[n("abc")].elements == ["a", "b", "c"]
x = i.tables[n("t_abc")].columns["x"]
assert x.is_enum
assert (
x.change_enum_to_string_statement("t") ==
'alter table t alter column "x" set data type varchar using "x"::varchar;'
)
assert (x.change_string_to_enum_statement("t") ==
'alter table t alter column "x" set data type abc using "x"::abc;')
tid = i.tables[n("t_abc")].columns["id"]
with raises(ValueError):
tid.change_enum_to_string_statement("t")
with raises(ValueError):
tid.change_string_to_enum_statement("t")
def asserts_pg(i):
assert list(i.schemas.keys()) == ['otherschema', 'public']
otherschema = i.schemas['otherschema']
assert i.schemas['public'] != i.schemas['otherschema']
assert otherschema.create_statement == 'create schema if not exists "otherschema";'
assert otherschema.drop_statement == 'drop schema if exists "otherschema";'
assert to_pytype(i.dialect, 'integer') == int
assert to_pytype(i.dialect, 'nonexistent') == type(None) # noqa
def n(name, schema='public'):
return quoted_identifier(name, schema=schema)
assert i.dialect.name == 'postgresql'
films = n('films')
v_films = n('v_films')
v_films2 = n('v_films2')
v = i.views[v_films]
public_views = od(
(k, v) for k, v in i.views.items() if v.schema == 'public')
assert list(public_views.keys()) == [v_films, v_films2]
assert v.columns == FILMS_COLUMNS
assert v.create_statement == VDEF
assert v == v
assert v == deepcopy(v)
assert v.drop_statement == 'drop view if exists {} cascade;'.format(
v_films)
v = i.views[v_films]
assert v.dependent_on == [films]
v = i.views[v_films2]
assert v.dependent_on == [v_films]
for k, r in i.relations.items():
for dependent in r.dependents:
assert k in i.relations[dependent].dependent_on
for dependency in r.dependent_on:
assert k in i.relations[dependency].dependents
mv_films = n('mv_films')
mv = i.materialized_views[mv_films]
assert list(i.materialized_views.keys()) == [mv_films]
assert mv.columns == FILMS_COLUMNS
assert mv.create_statement == MVDEF
assert mv.drop_statement == 'drop materialized view if exists {} cascade;'.format(
mv_films)
assert n('mv_films_title_idx') in mv.indexes
films_f = n('films_f') + '(d date, def_t text, def_d date)'
inc_f = n('inc_f') + '(integer)'
inc_f_noargs = n('inc_f_noargs') + '()'
inc_f_out = n('inc_f_out') + '(integer, OUT outparam integer)'
public_funcs = [k for k, v in i.functions.items() if v.schema == 'public']
assert public_funcs == [films_f, inc_f, inc_f_noargs, inc_f_out]
f = i.functions[films_f]
f2 = i.functions[inc_f]
f3 = i.functions[inc_f_noargs]
f4 = i.functions[inc_f_out]
assert f == f
assert f != f2
assert f.columns == FILMSF_COLUMNS
assert f.inputs == FILMSF_INPUTS
assert f3.inputs == []
assert list(f2.columns.values())[0].name == 'inc_f'
assert list(f3.columns.values())[0].name == 'inc_f_noargs'
assert list(f4.columns.values())[0].name == 'outparam'
fdef = i.functions[films_f].definition
assert fdef == "select 'a'::varchar, '2014-01-01'::date"
assert f.create_statement == FDEF
assert f.drop_statement == 'drop function if exists "public"."films_f"(d date, def_t text, def_d date) cascade;'
assert [e.quoted_full_name for e in i.extensions.values()
] == [n('plpgsql', schema='pg_catalog'),
n('pg_trgm')]
cons = i.constraints['"public"."films"."firstkey"']
assert cons.create_statement == 'alter table "public"."films" add constraint "firstkey" PRIMARY KEY using index "firstkey";'
t_films = n('films')
t = i.tables[t_films]
assert t.create_statement == T_CREATE
assert t.drop_statement == 'drop table {};'.format(t_films)
assert t.alter_table_statement('x') == 'alter table {} x;'.format(t_films)
assert n('films_title_idx') in t.indexes
ct = i.composite_types[n('ttt')]
assert [(x.name, x.dbtype)
for x in ct.columns.values()] == [('a', 'integer'), ('b', 'text')]
assert ct.create_statement == 'create type "public"."ttt" as ("a" integer, "b" text);'
assert ct.drop_statement == 'drop type "public"."ttt";'
assert i.enums[n('abc')].elements == ['a', 'b', 'c']
x = i.tables[n('t_abc')].columns['x']
assert x.is_enum
assert x.change_enum_to_string_statement(
't') == 'alter table t alter column "x" set data type varchar;'
assert x.change_string_to_enum_statement(
't'
) == 'alter table t alter column "x" set data type abc using "x"::abc;'
tid = i.tables[n('t_abc')].columns['id']
with raises(ValueError):
tid.change_enum_to_string_statement('t')
with raises(ValueError):
tid.change_string_to_enum_statement('t')
def asserts_pg(i):
# schemas
assert list(i.schemas.keys()) == ["otherschema", "public"]
otherschema = i.schemas["otherschema"]
assert i.schemas["public"] != i.schemas["otherschema"]
assert otherschema.create_statement == 'create schema if not exists "otherschema";'
assert otherschema.drop_statement == 'drop schema if exists "otherschema";'
# to_pytype
assert to_pytype(i.dialect, "integer") == int
assert to_pytype(i.dialect, "nonexistent") == type(None) # noqa
# dialect
assert i.dialect.name == "postgresql"
# tables and views
films = n("films")
v_films = n("v_films")
v_films2 = n("v_films2")
v = i.views[v_films]
public_views = od((k, v) for k, v in i.views.items() if v.schema == "public")
assert list(public_views.keys()) == [v_films, v_films2]
assert v.columns == FILMS_COLUMNS
assert v.create_statement == VDEF
assert v == v
assert v == deepcopy(v)
assert v.drop_statement == "drop view if exists {};".format(v_films)
v = i.views[v_films]
# dependencies
assert v.dependent_on == [films]
v = i.views[v_films2]
assert v.dependent_on == [v_films]
for k, r in i.relations.items():
for dependent in r.dependents:
assert k in i.relations[dependent].dependent_on
for dependency in r.dependent_on:
assert k in i.relations[dependency].dependents
# materialized views
mv_films = n("mv_films")
mv = i.materialized_views[mv_films]
assert list(i.materialized_views.keys()) == [mv_films]
assert mv.columns == FILMS_COLUMNS
assert mv.create_statement == MVDEF
assert mv.drop_statement == "drop materialized view if exists {};".format(mv_films)
# materialized view indexes
assert n("mv_films_title_idx") in mv.indexes
# functions
films_f = n("films_f") + "(d date, def_t text, def_d date)"
inc_f = n("inc_f") + "(integer)"
inc_f_noargs = n("inc_f_noargs") + "()"
inc_f_out = n("inc_f_out") + "(integer, OUT outparam integer)"
public_funcs = [k for k, v in i.functions.items() if v.schema == "public"]
assert public_funcs == [films_f, inc_f, inc_f_noargs, inc_f_out]
f = i.functions[films_f]
f2 = i.functions[inc_f]
f3 = i.functions[inc_f_noargs]
f4 = i.functions[inc_f_out]
assert f == f
assert f != f2
assert f.columns == FILMSF_COLUMNS
assert f.inputs == FILMSF_INPUTS
assert f3.inputs == []
assert list(f2.columns.values())[0].name == "inc_f"
assert list(f3.columns.values())[0].name == "inc_f_noargs"
assert list(f4.columns.values())[0].name == "outparam"
fdef = i.functions[films_f].definition
assert fdef == "select 'a'::varchar, '2014-01-01'::date"
assert f.create_statement == FDEF
assert (
f.drop_statement
== 'drop function if exists "public"."films_f"(d date, def_t text, def_d date);'
)
assert f.comment == "films_f comment"
assert f2.comment is None
# extensions
assert [e.quoted_full_name for e in i.extensions.values()] == [
n("plpgsql", schema="pg_catalog"),
n("pg_trgm"),
]
# constraints
cons = i.constraints['"public"."films"."firstkey"']
assert (
cons.create_statement
== 'alter table "public"."films" add constraint "firstkey" PRIMARY KEY using index "firstkey";'
)
# tables
t_films = n("films")
t = i.tables[t_films]
empty = i.tables[n("emptytable")]
assert empty.comment == "emptytable comment"
# empty tables
assert empty.columns == od()
assert (
empty.create_statement
== """create table "public"."emptytable" (
);
"""
)
# create and drop tables
assert t.create_statement == T_CREATE
assert t.drop_statement == "drop table {};".format(t_films)
assert t.alter_table_statement("x") == "alter table {} x;".format(t_films)
# table indexes
assert n("films_title_idx") in t.indexes
# privileges
g = InspectedPrivilege("table", "public", "films", "select", "postgres")
g = i.privileges[g.key]
assert g.create_statement == "grant select on table {} to postgres;".format(t_films)
assert g.drop_statement == "revoke select on table {} from postgres;".format(
t_films
)
# composite types
ct = i.composite_types[n("ttt")]
assert [(x.name, x.dbtype) for x in ct.columns.values()] == [
("a", "integer"),
("b", "text"),
]
assert (
ct.create_statement == 'create type "public"."ttt" as ("a" integer, "b" text);'
)
assert ct.drop_statement == 'drop type "public"."ttt";'
# enums
assert i.enums[n("abc")].elements == ["a", "b", "c"]
x = i.tables[n("t_abc")].columns["x"]
assert x.is_enum
assert (
x.change_enum_to_string_statement("t")
== 'alter table t alter column "x" set data type varchar using "x"::varchar;'
)
assert (
x.change_string_to_enum_statement("t")
== 'alter table t alter column "x" set data type abc using "x"::abc;'
)
tid = i.tables[n("t_abc")].columns["id"]
with raises(ValueError):
tid.change_enum_to_string_statement("t")
with raises(ValueError):
tid.change_string_to_enum_statement("t")