🔍 Code Extractor

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

Purpose

This endpoint serves as the primary API for fetching database schema metadata. It attempts to return a previously discovered schema stored in the Flask app context, or triggers on-demand schema discovery if none exists. The function provides detailed information about database structure including table names, column details, primary keys, row counts, and relationships. It's designed to support UI components that need to display or work with database schema information, with built-in pagination limits (20 tables, 50 relationships) to prevent overwhelming the client.

Source Code

def get_database_schema():
    """Get current database schema information"""
    try:
        # Return discovered schema if available
        if hasattr(app, 'discovered_schema') and app.discovered_schema:
            schema = app.discovered_schema
            
            # Return comprehensive schema information
            return jsonify({
                'success': True,
                'schema': {
                    'database_name': schema.database_name,
                    'server_name': schema.server_name,
                    'discovery_timestamp': schema.discovery_timestamp,
                    'total_tables': schema.total_tables,
                    'total_columns': schema.total_columns,
                    'total_rows': schema.total_rows,
                    'tables': [{
                        'name': table.name,
                        'row_count': table.row_count,
                        'column_count': len(table.columns),
                        'columns': [col['name'] for col in table.columns],
                        'primary_keys': table.primary_keys,
                        'description': table.description
                    } for table in schema.tables[:20]],  # Limit to first 20 tables for UI
                    'relationships': schema.relationships[:50],  # Limit relationships
                    'summary': schema_discovery.get_schema_summary(schema)
                }
            })
        else:
            # Fall back to static schema file information
            logger.warning("No discovered schema available, attempting to discover now...")
            
            # Try to discover schema on-demand
            try:
                discovered_schema = schema_discovery.discover_schema(force_refresh=True)
                app.discovered_schema = discovered_schema
                
                return jsonify({
                    'success': True,
                    'schema': {
                        'database_name': discovered_schema.database_name,
                        'server_name': discovered_schema.server_name,
                        'discovery_timestamp': discovered_schema.discovery_timestamp,
                        'total_tables': discovered_schema.total_tables,
                        'total_columns': discovered_schema.total_columns,
                        'total_rows': discovered_schema.total_rows,
                        'summary': schema_discovery.get_schema_summary(discovered_schema)
                    }
                })
            except Exception as discovery_error:
                logger.error(f"On-demand schema discovery failed: {str(discovery_error)}")
                return jsonify({
                    'success': False,
                    'error': f'Schema discovery failed: {str(discovery_error)}',
                    'fallback_message': 'Using static schema files as fallback'
                }), 500
        
    except Exception as e:
        logger.error(f"Error getting database schema: {str(e)}")
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

Return Value

Returns a Flask JSON response object. On success (200): {'success': True, 'schema': {...}} containing database_name, server_name, discovery_timestamp, total_tables, total_columns, total_rows, tables array (limited to 20), relationships array (limited to 50), and summary. On failure (500): {'success': False, 'error': 'error message', 'fallback_message': 'optional fallback info'}. The schema object includes detailed table information with columns, primary keys, row counts, and descriptions.

Dependencies

• flask
• logging
• pathlib

Required Imports

from flask import Flask, jsonify
import logging

Conditional/Optional Imports

These imports are only needed under specific conditions:

from dynamic_schema_discovery import DynamicSchemaDiscovery

from manual_relationships import get_manual_relationship_manager

Usage Example

# Assuming Flask app setup with required modules
from flask import Flask, jsonify
import logging

app = Flask(__name__)
logger = logging.getLogger(__name__)

# schema_discovery should be initialized elsewhere
# from dynamic_schema_discovery import DynamicSchemaDiscovery
# schema_discovery = DynamicSchemaDiscovery(config)

@app.route('/database_schema', methods=['GET'])
def get_database_schema():
    # Function implementation here
    pass

# Client usage:
# GET http://localhost:5000/database_schema
# Response: {"success": true, "schema": {"database_name": "mydb", "tables": [...], ...}}

Best Practices

• The function implements a caching strategy by checking app.discovered_schema before triggering expensive schema discovery
• Results are limited (20 tables, 50 relationships) to prevent overwhelming the client with large schemas
• Implements graceful fallback with on-demand discovery if cached schema is unavailable
• Comprehensive error handling with logging at each failure point
• Returns appropriate HTTP status codes (200 for success, 500 for errors)
• Consider implementing pagination parameters for large schemas instead of hard-coded limits
• The force_refresh=True parameter in on-demand discovery may cause performance issues; consider making this configurable
• Ensure schema_discovery module is properly initialized before the Flask app starts
• Consider adding authentication/authorization checks for production environments
• The discovered_schema should be periodically refreshed or invalidated when schema changes occur

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function get_database_schema_viewer 88.9% 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 get_database_tables_columns 80.2% similar

  Flask route handler that retrieves database schema information including tables, columns, and relationships, filtered and sorted by relevance for data analysis workflows.

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

• function database_schema 78.5% similar

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

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

• function refresh_database_schema 70.4% 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_results 58.3% similar

  Flask route handler that retrieves and returns comprehensive analysis results for a given session, including summary data, generated files, interpretations, and execution tracking information.

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