build_hierarchies.py

• Pfad: /var/www/scripts/pipeline/build_hierarchies.py
• Namespace: pipeline
• Zeilen: 197 | Größe: 6,418 Bytes
• Geändert: 2025-12-31 03:01:09 | Gescannt: 2025-12-31 10:22:15

Code Hygiene Score: 96

• Dependencies: 100 (25%)
• LOC: 100 (20%)
• Methods: 100 (20%)
• Secrets: 100 (15%)
• Classes: 100 (10%)
• Magic Numbers: 60 (10%)

Issues 4

Dependencies 3

• use os
• use sys
• use db.db

Funktionen 4

• build_taxonomy_hierarchy() Zeile 15
• build_ontology_hierarchy() Zeile 88
• verify_hierarchies() Zeile 134
• main() Zeile 170

Code

#!/usr/bin/env python3
"""
Build hierarchies for taxonomy_terms and ontology_classes.
Phase 2 of semantic infrastructure cleanup.
"""

import os
import sys

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from db import db


def build_taxonomy_hierarchy():
    """Build taxonomy term hierarchy for systemisches Coaching."""
    print("Building taxonomy hierarchy...")

    # Hierarchy design for systemisches Coaching:
    # Wissen (12) - Root
    #   ├── Theorie (2) - depth 1
    #   ├── Praxis (3) - depth 1
    #   │   ├── Methoden (1) - depth 2
    #   │   └── Prozess (9) - depth 2
    #   ├── Kontext (10) - depth 1
    #   │   ├── Organisation (5) - depth 2
    #   │   └── Kommunikation (4) - depth 2
    #   └── Anwendungsfelder - new, depth 1
    #       ├── Coaching (7) - depth 2
    #       ├── Therapie (8) - depth 2
    #       ├── Entwicklung (6) - depth 2
    #       └── Haltung (11) - depth 2

    ROOT_ID = 12  # Wissen

    # First, update root
    db.execute("UPDATE taxonomy_terms SET depth = 0, path = %s WHERE id = %s", ("/wissen", ROOT_ID))

    # Create Anwendungsfelder category
    db.execute(
        """INSERT INTO taxonomy_terms (name, slug, parent_id, description, depth, path)
           VALUES (%s, %s, %s, %s, %s, %s)""",
        (
            "Anwendungsfelder",
            "anwendungsfelder",
            ROOT_ID,
            "Praktische Anwendungsbereiche",
            1,
            "/wissen/anwendungsfelder",
        ),
    )
    db.commit()

    # Get Anwendungsfelder ID
    cursor = db.execute("SELECT id FROM taxonomy_terms WHERE slug = 'anwendungsfelder'")
    anwendung_id = cursor.fetchone()["id"]
    cursor.close()

    # Define hierarchy updates
    updates = [
        # Level 1 - direct children of Wissen
        (2, ROOT_ID, 1, "/wissen/theorie"),  # Theorie
        (3, ROOT_ID, 1, "/wissen/praxis"),  # Praxis
        (10, ROOT_ID, 1, "/wissen/kontext"),  # Kontext
        # Level 2 - children of Praxis
        (1, 3, 2, "/wissen/praxis/methoden"),  # Methoden -> Praxis
        (9, 3, 2, "/wissen/praxis/prozess"),  # Prozess -> Praxis
        # Level 2 - children of Kontext
        (5, 10, 2, "/wissen/kontext/organisation"),  # Organisation -> Kontext
        (4, 10, 2, "/wissen/kontext/kommunikation"),  # Kommunikation -> Kontext
        # Level 2 - children of Anwendungsfelder
        (7, anwendung_id, 2, "/wissen/anwendungsfelder/coaching"),  # Coaching
        (8, anwendung_id, 2, "/wissen/anwendungsfelder/therapie"),  # Therapie
        (6, anwendung_id, 2, "/wissen/anwendungsfelder/entwicklung"),  # Entwicklung
        (11, anwendung_id, 2, "/wissen/anwendungsfelder/haltung"),  # Haltung
    ]

    for term_id, parent_id, depth, path in updates:
        db.execute(
            "UPDATE taxonomy_terms SET parent_id = %s, depth = %s, path = %s WHERE id = %s",
            (parent_id, depth, path, term_id),
        )

    db.commit()
    print(f"  Updated {len(updates) + 1} taxonomy terms with hierarchy")


def build_ontology_hierarchy():
    """Build ontology class hierarchy."""
    print("Building ontology hierarchy...")

    # Add root class
    db.execute(
        """INSERT INTO ontology_classes (name, parent_class_id, description, properties)
           VALUES (%s, %s, %s, %s)""",
        ("Coaching-Wissen", None, "Wurzelklasse für Coaching-Domäne", '{"domain": "coaching"}'),
    )
    db.commit()

    # Get root ID
    cursor = db.execute("SELECT id FROM ontology_classes WHERE name = 'Coaching-Wissen'")
    root_id = cursor.fetchone()["id"]
    cursor.close()

    # Add intermediate classes
    db.execute(
        """INSERT INTO ontology_classes (name, parent_class_id, description)
           VALUES (%s, %s, %s)""",
        ("Coaching-Aktivität", root_id, "Handlungen und Interventionen im Coaching"),
    )
    db.commit()

    cursor = db.execute("SELECT id FROM ontology_classes WHERE name = 'Coaching-Aktivität'")
    activity_id = cursor.fetchone()["id"]
    cursor.close()

    # Update existing classes to have parents
    # Coaching-Methode, Coaching-Prozess, Team-Intervention -> Coaching-Aktivität
    # Coaching-Konzept -> Coaching-Wissen (root)
    updates = [
        (1, activity_id),  # Coaching-Methode -> Aktivität
        (3, activity_id),  # Coaching-Prozess -> Aktivität
        (4, activity_id),  # Team-Intervention -> Aktivität
        (2, root_id),  # Coaching-Konzept -> Wissen
    ]

    for class_id, parent_id in updates:
        db.execute("UPDATE ontology_classes SET parent_class_id = %s WHERE id = %s", (parent_id, class_id))

    db.commit()
    print(f"  Updated {len(updates)} ontology classes with hierarchy")


def verify_hierarchies():
    """Verify hierarchies are properly set up."""
    print("\nVerification:")

    # Check taxonomy
    cursor = db.execute("""
        SELECT t1.name, t1.depth, t1.path, t2.name as parent_name
        FROM taxonomy_terms t1
        LEFT JOIN taxonomy_terms t2 ON t1.parent_id = t2.id
        ORDER BY t1.path
    """)
    terms = cursor.fetchall()
    cursor.close()

    print("\n  Taxonomy Hierarchy:")
    for t in terms:
        indent = "    " * (t["depth"] or 0)
        parent = f" (← {t['parent_name']})" if t["parent_name"] else ""
        print(f"  {indent}• {t['name']}{parent}")

    # Check ontology
    cursor = db.execute("""
        SELECT o1.name, o2.name as parent_name
        FROM ontology_classes o1
        LEFT JOIN ontology_classes o2 ON o1.parent_class_id = o2.id
        ORDER BY o1.parent_class_id NULLS FIRST, o1.id
    """)
    classes = cursor.fetchall()
    cursor.close()

    print("\n  Ontology Hierarchy:")
    for c in classes:
        parent = f" (← {c['parent_name']})" if c["parent_name"] else " [ROOT]"
        print(f"    • {c['name']}{parent}")


def main():
    """Build hierarchies."""
    print("=" * 60)
    print("Phase 2: Building Hierarchies")
    print("=" * 60)

    if not db.connect():
        print("ERROR: Could not connect to database")
        return 1

    try:
        build_taxonomy_hierarchy()
        build_ontology_hierarchy()
        verify_hierarchies()

        print("\n" + "=" * 60)
        print("Hierarchies built successfully!")
        print("=" * 60)

    finally:
        db.disconnect()

    return 0


if __name__ == "__main__":
    sys.exit(main())