def test_parsable_body() -> None:
SQL = "create or replace view public.some_view as select 1 one;"
try:
view = PGView.from_sql(SQL)
except SQLParseFailure:
pytest.fail(f"Unexpected SQLParseFailure for view {SQL}")
SQL = "create view public.some_view(one) as select 1 one;"
try:
view = PGView.from_sql(SQL)
assert view.signature == "some_view"
except SQLParseFailure:
pytest.fail(f"Unexpected SQLParseFailure for view {SQL}")
def test_update_revision(engine) -> None:
# Create the view outside of a revision
engine.execute(TEST_VIEW.to_sql_statement_create())
# Update definition of TO_UPPER
UPDATED_TEST_VIEW = PGView(
TEST_VIEW.schema, TEST_VIEW.signature, """select *, TRUE as is_updated from pg_views"""
)
register_entities([UPDATED_TEST_VIEW])
# Autogenerate a new migration
# It should detect the change we made and produce a "replace_function" statement
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={"autogenerate": True, "rev_id": "2", "message": "replace"},
)
migration_replace_path = TEST_VERSIONS_ROOT / "2_replace.py"
with migration_replace_path.open() as migration_file:
migration_contents = migration_file.read()
assert "op.replace_entity" in migration_contents
assert "op.create_entity" not in migration_contents
assert "op.drop_entity" not in migration_contents
assert "from alembic_utils.pg_view import PGView" in migration_contents
# Execute upgrade
run_alembic_command(engine=engine, command="upgrade", command_kwargs={"revision": "head"})
# Execute Downgrade
run_alembic_command(engine=engine, command="downgrade", command_kwargs={"revision": "base"})
def collect_all_db_entities(sess: Session) -> List[ReplaceableEntity]:
"""Collect all entities from the database"""
return [
*PGFunction.from_database(sess, "%"),
*PGTrigger.from_database(sess, "%"),
*PGView.from_database(sess, "%"),
*PGMaterializedView.from_database(sess, "%"),
]
def test_teardown_temp_schema_on_error(engine) -> None:
"""Make sure the temporary schema gets town down when the simulated entity fails"""
SQL = "create or replace view public.some_view as INVALID SQL!;"
view = PGView.from_sql(SQL)
with engine.connect() as connection:
with pytest.raises(Exception):
with simulate_entity(connection, view):
pass
maybe_schema = connection.execute(
"select * from pg_namespace where nspname = 'alembic_utils';"
).fetchone()
assert maybe_schema is None
def test_attempt_revision_on_unparsable(engine) -> None:
BROKEN_VIEW = PGView(schema="public",
signature="broken_view",
definition="NOPE;")
register_entities([BROKEN_VIEW], entity_types=[PGView])
# Reraise of psycopg2.errors.SyntaxError
with pytest.raises(ProgrammingError):
run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
"autogenerate": True,
"rev_id": "1",
"message": "create"
},
)
def test_update_create_or_replace_failover_to_drop_add(engine) -> None:
# Create the view outside of a revision
engine.execute(TEST_VIEW.to_sql_statement_create())
# Update definition of TO_UPPER
# deleted columns from the beginning of the view.
# this will fail a create or replace statemnt
# psycopg2.errors.InvalidTableDefinition) cannot drop columns from view
# and should fail over to drop and then replace (in plpgsql of `create_or_replace_entity` method
# on pgview
UPDATED_TEST_VIEW = PGView(TEST_VIEW.schema, TEST_VIEW.signature,
"""select TRUE as is_updated from pg_views""")
register_entities([UPDATED_TEST_VIEW], entity_types=[PGView])
# Autogenerate a new migration
# It should detect the change we made and produce a "replace_function" statement
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
"autogenerate": True,
"rev_id": "2",
"message": "replace"
},
)
migration_replace_path = TEST_VERSIONS_ROOT / "2_replace.py"
with migration_replace_path.open() as migration_file:
migration_contents = migration_file.read()
assert "op.replace_entity" in migration_contents
assert "op.create_entity" not in migration_contents
assert "op.drop_entity" not in migration_contents
assert "from alembic_utils.pg_view import PGView" in migration_contents
# Execute upgrade
run_alembic_command(engine=engine,
command="upgrade",
command_kwargs={"revision": "head"})
# Execute Downgrade
run_alembic_command(engine=engine,
command="downgrade",
command_kwargs={"revision": "base"})
def test_drop_fails_with_cascade(engine, sess) -> None:
engine.execute(A.to_sql_statement_create())
engine.execute(B_A.to_sql_statement_create())
register_entities([B_A], schemas=["DEV"], entity_types=[PGView])
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
"autogenerate": True,
"rev_id": "1",
"message": "drop"
},
)
migration_create_path = TEST_VERSIONS_ROOT / "1_drop.py"
with migration_create_path.open() as migration_file:
migration_contents = migration_file.read()
assert "op.drop_entity" in migration_contents
assert "op.drop_entity(public_a_view)" in migration_contents
migration_contents = migration_contents.replace(
"op.drop_entity(public_a_view)",
"op.drop_entity(public_a_view, cascade=True)")
with migration_create_path.open("w") as migration_file:
migration_file.write(migration_contents)
assert "op.create_entity" in migration_contents
assert "from alembic_utils" in migration_contents
assert migration_contents.index(
"op.drop_entity") < migration_contents.index("op.create_entity")
# Cascade drops *B_A* and succeeds
run_alembic_command(engine=engine,
command="upgrade",
command_kwargs={"revision": "head"})
# Make sure the drop ocurred
all_views = PGView.from_database(sess, "public")
assert len(all_views) == 0
def test_view_contains_colon(engine) -> None:
TEST_SEMI_VIEW = PGView(
schema="public",
signature="sample",
definition="select ':' as myfield, '1'::int as othi",
)
# NOTE: if a definition contains something that looks like a bind parameter e.g. :a
# an exception is raised. This test confirms that non-bind-parameter usage of colon
# is a non-issue
register_entities([TEST_SEMI_VIEW], entity_types=[PGView])
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
"autogenerate": True,
"rev_id": "1",
"message": "create"
},
)
migration_create_path = TEST_VERSIONS_ROOT / "1_create.py"
with migration_create_path.open() as migration_file:
migration_contents = migration_file.read()
assert "op.create_entity" in migration_contents
assert "op.drop_entity" in migration_contents
assert "op.replace_entity" not in migration_contents
assert "from alembic_utils.pg_view import PGView" in migration_contents
# Execute upgrade
run_alembic_command(engine=engine,
command="upgrade",
command_kwargs={"revision": "head"})
# Execute Downgrade
run_alembic_command(engine=engine,
command="downgrade",
command_kwargs={"revision": "base"})
def test_create_revision_with_url_w_colon(engine) -> None:
"""Ensure no regression where views escape colons
More info at: https://github.com/olirice/alembic_utils/issues/58
"""
url = "https://something/"
query = f"SELECT concat('{url}', v::text) FROM generate_series(1,2) x(v)"
some_view = PGView(schema="public", signature="exa", definition=query)
register_entities([some_view], entity_types=[PGView])
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
"autogenerate": True,
"rev_id": "1",
"message": "create"
},
)
migration_create_path = TEST_VERSIONS_ROOT / "1_create.py"
with migration_create_path.open() as migration_file:
migration_contents = migration_file.read()
assert url in migration_contents
assert "op.create_entity" in migration_contents
assert "op.drop_entity" in migration_contents
assert "op.replace_entity" not in migration_contents
assert "from alembic_utils.pg_view import PGView" in migration_contents
# Execute upgrade
run_alembic_command(engine=engine,
command="upgrade",
command_kwargs={"revision": "head"})
# Execute Downgrade
run_alembic_command(engine=engine,
command="downgrade",
command_kwargs={"revision": "base"})
def test_view_contains_semicolon(engine) -> None:
TEST_SEMI_VIEW = PGView(
schema="public",
signature="sample",
definition="select ':a' as myfield, '1'::int as othi")
register_entities([TEST_SEMI_VIEW], entity_types=[PGView])
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
"autogenerate": True,
"rev_id": "1",
"message": "create"
},
)
migration_create_path = TEST_VERSIONS_ROOT / "1_create.py"
with migration_create_path.open() as migration_file:
migration_contents = migration_file.read()
assert "op.create_entity" in migration_contents
assert "op.drop_entity" in migration_contents
assert "op.replace_entity" not in migration_contents
assert "from alembic_utils.pg_view import PGView" in migration_contents
# Execute upgrade
run_alembic_command(engine=engine,
command="upgrade",
command_kwargs={"revision": "head"})
# Execute Downgrade
run_alembic_command(engine=engine,
command="downgrade",
command_kwargs={"revision": "base"})
from alembic_utils.pg_function import PGFunction
from alembic_utils.pg_view import PGView
from alembic_utils.replaceable_entity import register_entities
from alembic_utils.testbase import TEST_VERSIONS_ROOT, run_alembic_command
# The objects marked as "excluded" have names corresponding
# to the filters in src/test/alembic_config/env.py
IncludedView = PGView(
schema="public",
signature="A_view",
definition="select 1 as one",
)
ObjExcludedView = PGView(
schema="public",
signature="exclude_obj_view",
definition="select 1 as one",
)
ReflectedIncludedView = PGView(
schema="public",
signature="reflected_view",
definition="select 1 as one",
)
ReflectedExcludedView = PGView(
schema="public",
signature="exclude_name_reflected_view",
definition="select 1 as one",
)
def test_unparsable_view() -> None:
SQL = "create or replace vew public.some_view as select 1 one;"
with pytest.raises(SQLParseFailure):
view = PGView.from_sql(SQL)
import pytest
from alembic_utils.exceptions import SQLParseFailure
from alembic_utils.pg_view import PGView
from alembic_utils.replaceable_entity import register_entities, simulate_entity
from alembic_utils.testbase import TEST_VERSIONS_ROOT, run_alembic_command
TEST_VIEW = PGView(
schema="DEV", signature="testExample", definition="select *, FALSE as is_updated from pg_views"
)
def test_unparsable_view() -> None:
SQL = "create or replace vew public.some_view as select 1 one;"
with pytest.raises(SQLParseFailure):
view = PGView.from_sql(SQL)
def test_parsable_body() -> None:
SQL = "create or replace view public.some_view as select 1 one;"
try:
view = PGView.from_sql(SQL)
except SQLParseFailure:
pytest.fail(f"Unexpected SQLParseFailure for view {SQL}")
SQL = "create view public.some_view(one) as select 1 one;"
try:
view = PGView.from_sql(SQL)
assert view.signature == "some_view"
except SQLParseFailure:
pytest.fail(f"Unexpected SQLParseFailure for view {SQL}")
import pytest
from alembic_utils.depends import solve_resolution_order
from alembic_utils.pg_view import PGView
from alembic_utils.replaceable_entity import register_entities
from alembic_utils.testbase import TEST_VERSIONS_ROOT, run_alembic_command
# NAME_DEPENDENCIES
A = PGView(
schema="public",
signature="A_view",
definition="select 1 as one",
)
B_A = PGView(
schema="public",
signature="B_view",
definition='select * from public."A_view"',
)
C_A = PGView(
schema="public",
signature="C_view",
definition='select * from public."A_view"',
)
D_B = PGView(
schema="public",
signature="D_view",
definition='select * from public."B_view"',
from alembic_utils.pg_view import PGView
# revision identifiers, used by Alembic.
revision = "b0ecefa97eda"
down_revision = "efe0a86d3526"
branch_labels = None
depends_on = None
public_project_site = PGView(
schema="public",
signature="project_site",
definition="""
SELECT p.id project_id
, p.name project_name
, s.name site_name
, s.updated_on site_updated_on
, ST_ASTEXT(s.location) site_location
FROM project p
JOIN site s ON s.project_id = p.id
;
""",
)
def upgrade():
op.replace_entity(public_project_site)
def downgrade():
op.drop_entity(public_project_site)
import pytest
from sqlalchemy.exc import DataError
from sqlalchemy.orm import Session
from alembic_utils.pg_view import PGView
from alembic_utils.simulate import simulate_entity
TEST_VIEW = PGView(
schema="public",
signature="tview",
definition="select *, FALSE as is_updated from pg_views",
)
def test_simulate_entity_shows_user_code_error(sess: Session) -> None:
sess.execute(TEST_VIEW.to_sql_statement_create())
with pytest.raises(DataError):
with simulate_entity(sess, TEST_VIEW):
# Raises a sql error
sess.execute("select 1/0").fetchone()
# Confirm context manager exited gracefully
assert True
import pytest
from sqlalchemy.exc import InternalError
from alembic_utils.pg_view import PGView
from alembic_utils.replaceable_entity import register_entities
from alembic_utils.testbase import TEST_VERSIONS_ROOT, run_alembic_command
A = PGView(
schema="public",
signature="A_view",
definition="select 1::integer as one",
)
B_A = PGView(
schema="public",
signature="B_view",
definition='select * from public."A_view"',
)
def test_drop_fails_without_cascade(engine) -> None:
engine.execute(A.to_sql_statement_create())
engine.execute(B_A.to_sql_statement_create())
register_entities([B_A], schemas=["DEV"], entity_types=[PGView])
output = run_alembic_command(
engine=engine,
command="revision",
command_kwargs={
def sql_view_entities():
sql_view_entities = []
for p in Path("./views").glob("*.sql"):
pg_view_entity = PGView.from_sql(p.read_text())
sql_view_entities.append(pg_view_entity)
return sql_view_entities
import pytest
from sqlalchemy.orm import Session
from alembic_utils.depends import recreate_dropped
from alembic_utils.pg_view import PGView
TEST_ROOT_BIGINT = PGView(schema="public",
signature="root",
definition="select 1::bigint as some_val")
TEST_ROOT_INT = PGView(schema="public",
signature="root",
definition="select 1::int as some_val")
TEST_DEPENDENT = PGView(schema="public",
signature="branch",
definition="select * from public.root")
def test_fails_without_defering(sess: Session) -> None:
# Create the original view
sess.execute(TEST_ROOT_BIGINT.to_sql_statement_create())
# Create the view that depends on it
sess.execute(TEST_DEPENDENT.to_sql_statement_create())
# Try to update a column type of the base view from undeneath
# the dependent view
with pytest.raises(Exception):
sess.execute(TEST_ROOT_INT.to_sql_statement_create_or_replace())
