Agent/knowhub/knowhub_db/cascade.py

"""
级联删除：Greenplum 不支持 FK ON DELETE CASCADE，由应用层保证。

用法：
    from knowhub.knowhub_db.cascade import cascade_delete
    cascade_delete(cursor, 'knowledge', knowledge_id)
"""

# 每个实体表涉及的关联表及其外键列名
_JUNCTIONS = {
    'knowledge': [
        ('requirement_knowledge', 'knowledge_id'),
        ('capability_knowledge', 'knowledge_id'),
        ('tool_knowledge', 'knowledge_id'),
        ('knowledge_resource', 'knowledge_id'),
        ('knowledge_relation', 'source_id'),
        ('knowledge_relation', 'target_id'),
        ('strategy_knowledge', 'knowledge_id'),
    ],
    'tool': [
        ('capability_tool', 'tool_id'),
        ('tool_knowledge', 'tool_id'),
        ('tool_provider', 'tool_id'),
    ],
    'capability': [
        ('requirement_capability', 'capability_id'),
        ('capability_tool', 'capability_id'),
        ('capability_knowledge', 'capability_id'),
        ('capability_resource', 'capability_id'),
        ('strategy_capability', 'capability_id'),
    ],
    'requirement': [
        ('requirement_capability', 'requirement_id'),
        ('requirement_knowledge', 'requirement_id'),
        ('requirement_resource', 'requirement_id'),
        ('requirement_strategy', 'requirement_id'),
    ],
    'resource': [
        ('knowledge_resource', 'resource_id'),
        ('capability_resource', 'resource_id'),
        ('requirement_resource', 'resource_id'),
        ('strategy_resource', 'resource_id'),
    ],
    'strategy': [
        ('strategy_capability', 'strategy_id'),
        ('strategy_knowledge', 'strategy_id'),
        ('strategy_resource', 'strategy_id'),
        ('requirement_strategy', 'strategy_id'),
    ],
}


def cascade_delete(cursor, entity_table: str, entity_id: str):
    """先删除关联表中的引用行，再删除实体本身"""
    for junction_table, fk_column in _JUNCTIONS.get(entity_table, []):
        cursor.execute(
            f"DELETE FROM {junction_table} WHERE {fk_column} = %s",
            (entity_id,))
    cursor.execute(
        f"DELETE FROM {entity_table} WHERE id = %s",
        (entity_id,))