sqlalchemy-pgview/docs/guide/dependencies.md

# Dependency Tracking

SQLAlchemy-PGView can query PostgreSQL system catalogs to discover view dependencies, helping you manage complex view hierarchies.

## Why Track Dependencies?

- **Safe migrations**: Know which views to drop/recreate when modifying a base view
- **Impact analysis**: Understand what breaks if you change a table or view
- **Correct ordering**: Create views in the right order during deployment

## Getting All Views

```python
from sqlalchemy_pgview import get_all_views

with engine.connect() as conn:
    views = get_all_views(conn)

    for view in views:
        print(f"{view.schema}.{view.name}")
        print(f"  Materialized: {view.is_materialized}")
        print(f"  Definition: {view.definition[:50]}...")

    # Only views in 'analytics' schema
    views = get_all_views(conn, schema="analytics")

    # Exclude materialized views
    views = get_all_views(conn, include_materialized=False)
```

## Getting View Definition

```python
from sqlalchemy_pgview import get_view_definition

with engine.connect() as conn:
    definition = get_view_definition(conn, "user_stats")
    print(definition)
    # SELECT u.id, u.name, count(o.id) AS order_count
    # FROM users u LEFT JOIN orders o ON u.id = o.user_id
    # GROUP BY u.id, u.name
```

## Direct Dependencies

Find what a view depends on:

```python
from sqlalchemy_pgview import get_view_dependencies

with engine.connect() as conn:
    deps = get_view_dependencies(conn, "user_order_summary")

    for dep in deps:
        print(f"{dep.dependent_fullname} depends on {dep.referenced_fullname}")
```

## Reverse Dependencies

Find what depends on a view (impact analysis):

```python
from sqlalchemy_pgview import get_reverse_dependencies

with engine.connect() as conn:
    # What views depend on 'users' table/view?
    dependents = get_reverse_dependencies(conn, "users")

    print("Views that depend on 'users':")
    for view in dependents:
        print(f"  - {view.fullname}")
```

## Dependency Order

Get views sorted by dependencies (dependencies first):

```python
from sqlalchemy_pgview import get_dependency_order

with engine.connect() as conn:
    ordered_views = get_dependency_order(conn)

    print("Views in creation order:")
    for i, view in enumerate(ordered_views, 1):
        print(f"{i}. {view.fullname}")
```

This is useful for:

- **Creating views**: Process in order to avoid "relation does not exist" errors
- **Dropping views**: Process in reverse order to avoid dependency errors

## Safe View Modification

```python
from sqlalchemy_pgview import (
    get_reverse_dependencies,
    get_view_definition,
    DropView,
    CreateView,
)

def modify_view_safely(conn, view_name, new_definition):
    """Modify a view by dropping/recreating it and all dependents."""

    # Get all views that depend on this one
    dependents = get_reverse_dependencies(conn, view_name)

    # Save their definitions
    saved_definitions = {}
    for dep in dependents:
        saved_definitions[dep.fullname] = get_view_definition(
            conn, dep.name, dep.schema
        )

    # Drop dependents in reverse order
    for dep in reversed(dependents):
        conn.execute(DropView(dep.name, schema=dep.schema, if_exists=True))

    # Drop and recreate the target view
    conn.execute(DropView(view_name, if_exists=True))
    conn.execute(text(f"CREATE VIEW {view_name} AS {new_definition}"))

    # Recreate dependents in order
    for dep in dependents:
        definition = saved_definitions[dep.fullname]
        conn.execute(text(
            f"CREATE VIEW {dep.fullname} AS {definition}"
        ))
```

## Migration Helper

```python
def generate_view_migration(conn, schema=None):
    """Generate migration code for all views in dependency order."""

    views = get_dependency_order(conn, schema=schema)

    print("def upgrade():")
    for view in views:
        view_type = "materialized_view" if view.is_materialized else "view"
        definition = view.definition.replace("'", "\\'")
        print(f"    op.create_{view_type}(")
        print(f"        '{view.name}',")
        print(f"        '''{definition}''',")
        if view.schema != "public":
            print(f"        schema='{view.schema}',")
        print(f"    )")
        print()

    print("def downgrade():")
    for view in reversed(views):
        view_type = "materialized_view" if view.is_materialized else "view"
        print(f"    op.drop_{view_type}('{view.name}'", end="")
        if view.schema != "public":
            print(f", schema='{view.schema}'", end="")
        print(")")
```

## Dependency Visualization

```python
def print_dependency_tree(conn, view_name, indent=0):
    """Print a tree of view dependencies."""
    prefix = "  " * indent
    print(f"{prefix}- {view_name}")

    deps = get_view_dependencies(conn, view_name)
    for dep in deps:
        print_dependency_tree(conn, dep.referenced_fullname, indent + 1)
```

Output:
```
- sales_summary
  - monthly_sales
    - orders
    - products
  - customer_stats
    - customers
    - orders
```

## Data Classes

### ViewInfo

| Attribute | Type | Description |
|-----------|------|-------------|
| `name` | `str` | View name |
| `schema` | `str` | Schema name |
| `definition` | `str` | SQL definition |
| `is_materialized` | `bool` | Whether the view is materialized |
| `fullname` | `str` | `schema.name` (property) |

### ViewDependency

| Attribute | Type | Description |
|-----------|------|-------------|
| `dependent_view` | `str` | Name of the dependent view |
| `dependent_schema` | `str` | Schema of the dependent view |
| `referenced_view` | `str` | Name of the referenced object |
| `referenced_schema` | `str` | Schema of the referenced object |
| `dependent_fullname` | `str` | `schema.view` (property) |
| `referenced_fullname` | `str` | `schema.view` (property) |