🔍 Code Extractor

Flask route handler that adds a manually specified database relationship to the schema and clears the schema cache to force reload with the new relationship.

Purpose

This endpoint allows users to manually define relationships between database tables when automatic schema discovery doesn't detect them or when domain knowledge indicates a relationship exists. It validates the input, persists the relationship using a manual relationship manager, clears the schema cache to ensure the new relationship is included in future queries, and returns a success response with the relationship details.

Source Code

def add_manual_relationship():
    """Add a manual relationship to the schema"""
    try:
        data = request.get_json()
        
        # Validate required fields
        required_fields = ['from_table', 'from_column', 'to_table', 'to_column']
        for field in required_fields:
            if not data.get(field):
                return jsonify({
                    'success': False,
                    'error': f'Missing required field: {field}'
                }), 400
        
        # Add to persistent manual relationship storage
        from manual_relationships import get_manual_relationship_manager
        manual_manager = get_manual_relationship_manager()
        
        success = manual_manager.add_relationship(
            from_table=data['from_table'],
            from_column=data['from_column'],
            to_table=data['to_table'],
            to_column=data['to_column'],
            confidence=data.get('confidence', 1.0),
            description=data.get('description', 'Manually specified relationship'),
            created_by='user'
        )
        
        if not success:
            return jsonify({
                'success': False,
                'error': 'Relationship already exists'
            }), 400
        
        # Clear schema cache to force reload with manual relationships on next request
        try:
            discovery = DynamicSchemaDiscovery(data_processor, statistical_agent)
            discovery._clear_cache()
            logger.info("Schema cache cleared to include new manual relationship")
        except Exception as e:
            logger.warning(f"Could not clear schema cache: {e}")
        
        logger.info(f"Added manual relationship: {data['from_table']}.{data['from_column']} → {data['to_table']}.{data['to_column']}")
        
        return jsonify({
            'success': True,
            'message': 'Manual relationship added successfully and will be included in future schema discoveries',
            'relationship': {
                'from_table': data['from_table'],
                'from_column': data['from_column'],
                'to_table': data['to_table'],
                'to_column': data['to_column'],
                'confidence': data.get('confidence', 1.0),
                'detection_method': 'manual'
            }
        })
            
    except Exception as e:
        logger.error(f"Error adding manual relationship: {str(e)}")
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

Return Value

Returns a Flask JSON response tuple. On success (200): {'success': True, 'message': str, 'relationship': dict with from_table, from_column, to_table, to_column, confidence, detection_method}. On validation error (400): {'success': False, 'error': str}. On server error (500): {'success': False, 'error': str}. The tuple includes the response object and HTTP status code.

Dependencies

• flask
• logging
• manual_relationships
• dynamic_schema_discovery
• data_processor
• statistical_agent

Required Imports

from flask import request, jsonify
import logging

Conditional/Optional Imports

These imports are only needed under specific conditions:

from manual_relationships import get_manual_relationship_manager

from dynamic_schema_discovery import DynamicSchemaDiscovery

Usage Example

# Example client-side request to this endpoint
import requests

# Define the manual relationship
relationship_data = {
    'from_table': 'orders',
    'from_column': 'customer_id',
    'to_table': 'customers',
    'to_column': 'id',
    'confidence': 0.95,
    'description': 'Foreign key relationship between orders and customers'
}

# Send POST request
response = requests.post(
    'http://localhost:5000/add_manual_relationship',
    json=relationship_data,
    headers={'Content-Type': 'application/json'}
)

# Check response
if response.status_code == 200:
    result = response.json()
    print(f"Success: {result['message']}")
    print(f"Relationship: {result['relationship']}")
else:
    error = response.json()
    print(f"Error: {error['error']}")

Best Practices

• Always validate that all required fields (from_table, from_column, to_table, to_column) are present in the request body
• The function automatically sets confidence to 1.0 for manual relationships if not specified, indicating high trust in user-defined relationships
• Schema cache is cleared after adding the relationship to ensure it's included in future queries, but cache clearing failures are logged as warnings rather than errors
• The function prevents duplicate relationships by checking if the relationship already exists before adding
• All operations are logged for audit trail and debugging purposes
• Error handling is comprehensive with appropriate HTTP status codes (400 for validation errors, 500 for server errors)
• The 'created_by' field is hardcoded to 'user' - consider making this dynamic based on authenticated user in production
• Ensure the manual_relationships module and its manager are properly initialized before this endpoint is called
• Consider adding authentication/authorization checks before allowing manual relationship modifications

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• class ManualRelationshipManager 63.4% similar

  A class that manages manually defined database relationships with persistent JSON storage, allowing users to add, retrieve, update, and remove relationship definitions between database tables.

  From: /tf/active/vicechatdev/full_smartstat/manual_relationships.py

• function refresh_database_schema 61.0% similar

  Flask route endpoint that forces a refresh of the database schema by invoking the schema discovery service with LLM-generated descriptions.

  From: /tf/active/vicechatdev/full_smartstat/app.py

• function get_database_schema 58.1% similar

  Flask route handler that retrieves and returns comprehensive database schema information, including tables, columns, relationships, and statistics.

  From: /tf/active/vicechatdev/full_smartstat/app.py

• function get_database_schema_viewer 53.7% similar

  Flask route handler that retrieves and formats detailed database schema information from a discovered schema stored in the Flask app object, returning it as JSON for visualization purposes.

  From: /tf/active/vicechatdev/full_smartstat/app.py

• function database_schema 53.6% similar

  Flask route handler that renders the database schema viewer page template.

  From: /tf/active/vicechatdev/full_smartstat/app.py