Implement cascade delete with relations system

[genro]

Moved from: genropy/genro-mail-proxy#83
The relations system belongs in genro-proxy's SQL layer, not in genro-mail-proxy.

Problem

Currently, domain proxies (e.g., genro-mail-proxy) manually cascade deletes in their TenantsTable.remove():

async def remove(self, tenant_id: str) -> bool:
    # Delete messages for this tenant
    await self.db.adapter.execute(
        "DELETE FROM messages WHERE tenant_id = :tenant_id",
        {"tenant_id": tenant_id}
    )
    # Delete accounts for this tenant
    await self.db.adapter.execute(
        "DELETE FROM accounts WHERE tenant_id = :tenant_id",
        {"tenant_id": tenant_id}
    )
    # Delete the tenant
    rowcount = await self.delete(where={"id": tenant_id})
    return rowcount > 0

Issues:

1. Does NOT delete message_events, leaving orphaned records
2. Manual cascade logic duplicated per table
3. No validation hooks (cannot abort if condition not met)
4. SQLite FK cascade not enabled (no PRAGMA foreign_keys = ON, no ON DELETE CASCADE)

Proposed Solution

1. Create OneRelation and ManyRelation classes

# sql/relation.py

class OneRelation:
    """Relation to ONE parent (ascending)."""
    def __init__(
        self,
        table: str,
        columns: dict[str, str] | str,  # {my_col: parent_col} or "col" if same
    ):
        self.table = table
        self.columns = {columns: columns} if isinstance(columns, str) else columns

class ManyRelation:
    """Relation to MANY children (descending, auto-calculated)."""
    def __init__(
        self,
        table: str,
        columns: dict[str, str],  # {child_col: my_col}
        *,
        on_delete: str = "cascade",  # cascade | raise | set_null | ignore
        on_update: str = "cascade",
    ):
        self.table = table
        self.columns = columns
        self.on_delete = on_delete
        self.on_update = on_update

2. Declare relations in Table classes

class AccountsTable(Table):
    name = "accounts"
    relations = {
        "tenant": OneRelation("tenants", "tenant_id"),
    }

class MessagesTable(Table):
    name = "messages"
    relations = {
        "tenant": OneRelation("tenants", "tenant_id"),
        "account": OneRelation("accounts", {"tenant_id": "tenant_id", "account_id": "id"}),
    }

class MessageEventTable(Table):
    name = "message_events"
    relations = {
        "message": OneRelation("messages", {"message_pk": "pk"}),
    }

3. Auto-calculate relations_back in SqlDb

After all tables are registered, iterate and build inverse relations:

def build_back_relations(self):
    """Calculate relations_back from relations of all tables."""
    for table in self.tables.values():
        table.relations_back = {}

    for table in self.tables.values():
        for rel_name, rel in table.relations.items():
            parent = self.tables.get(rel.table)
            if parent:
                # Create ManyRelation with inverted columns
                inverted_cols = {v: k for k, v in rel.columns.items()}
                parent.relations_back[table.name] = ManyRelation(
                    table.name,
                    inverted_cols,
                    on_delete="cascade",  # default, can be overridden
                )

Result:

tenants.relations_back = {
    "accounts": ManyRelation("accounts", {"tenant_id": "id"}, on_delete="cascade"),
    "messages": ManyRelation("messages", {"tenant_id": "id"}, on_delete="cascade"),
}

messages.relations_back = {
    "message_events": ManyRelation("message_events", {"message_pk": "pk"}, on_delete="cascade"),
}

4. Generic cascade delete in Table.delete()

async def delete(self, where: dict[str, Any]) -> int:
    record = await self.select_one(where=where)
    if not record:
        return 0

    # Pre-delete hook (can raise to abort)
    await self.trigger_on_deleting(record)

    # Cascade to children based on relations_back
    for rel_name, rel in self.relations_back.items():
        child_table = self.db.table(rel.table)
        child_where = {child_col: record[my_col] 
                       for child_col, my_col in rel.columns.items()}

        match rel.on_delete:
            case "cascade":
                await child_table.delete(where=child_where)  # Recursive!
            case "raise":
                if await child_table.exists(child_where):
                    raise IntegrityError(f"Cannot delete: {rel_name} records exist")
            case "set_null":
                nulls = {col: None for col in rel.columns.keys()}
                await child_table.update(nulls, child_where)
            case "ignore":
                pass

    # Now delete self
    result = await self.db.adapter.delete(self.name, where)
    if result > 0:
        await self.trigger_on_deleted(record)
    return result

5. Simplify domain-specific remove()

async def remove(self, tenant_id: str) -> bool:
    """Delete tenant. Cascade handled automatically via relations."""
    rowcount = await self.delete(where={"id": tenant_id})
    return rowcount > 0

Benefits

1. Automatic cascade: No manual delete statements per table
2. Recursive: tenant → messages → message_events cascades automatically
3. Validation hooks: trigger_on_deleting can raise to abort entire cascade
4. Configurable: on_delete can be cascade, raise, set_null, or ignore
5. Composite keys: Support for multi-column foreign keys
6. Named relations: Semantic names like "tenant", "account", "events"
7. Database agnostic: Works for SQLite and PostgreSQL without relying on DB-level FK

Implementation Steps

1. Create sql/relation.py with OneRelation and ManyRelation classes
2. Add relations: dict[str, OneRelation] attribute to Table base class
3. Add relations_back: dict[str, ManyRelation] attribute (populated by SqlDb)
4. Implement SqlDb.build_back_relations()
5. Modify Table.delete() to cascade based on relations_back
6. Add IntegrityError exception class
7. Declare relations in all entity tables
8. Simplify domain-specific cascade delete methods
9. Remove .relation() method from Column class (optional, for cleanup)
10. Add tests for cascade delete scenarios