MCP Server with LLM Integration

""" PostgreSQL Configuration Examples Examples of how to configure the generic PostgreSQL integration for different domain-specific use cases without hardcoded table references. """ from postgres_integration import PostgreSQLIntegration from sqlalchemy import create_engine def create_e_commerce_config(): """Configuration for e-commerce databases""" return { "semantic_mappings": { "users": { "type": "entity", "common_names": ["user", "users", "customer", "customers", "account", "accounts"], "primary_display_columns": ["name", "email", "username"], "date_columns": ["created_at", "updated_at", "last_login"], "searchable_columns": ["name", "email", "username"] }, "products": { "type": "entity", "common_names": ["product", "products", "item", "items", "merchandise"], "primary_display_columns": ["name", "title", "description"], "date_columns": ["created_at", "updated_at"], "searchable_columns": ["name", "title", "description", "sku"] }, "orders": { "type": "temporal", "common_names": ["order", "orders", "purchase", "purchases", "transaction"], "primary_display_columns": ["order_number", "total", "status"], "date_columns": ["created_at", "shipped_at", "delivered_at"], "searchable_columns": ["order_number", "status"] } }, "query_patterns": { "count_keywords": ["count", "how many", "number of", "total", "quantity"], "recent_keywords": ["recent", "latest", "last", "newest", "current", "today"], "list_keywords": ["show", "list", "display", "get", "find", "all"], "search_keywords": ["search", "find", "where", "with", "containing", "matching"] }, "priority_tables": ["users", "products", "orders", "categories"], "relationship_hints": { "users": [{"via_table": "orders", "column": "user_id", "references": "id"}], "products": [{"via_table": "order_items", "column": "product_id", "references": "id"}] } } def create_hr_management_config(): """Configuration for HR/employee management databases""" return { "semantic_mappings": { "employees": { "type": "entity", "common_names": ["employee", "employees", "staff", "personnel", "worker", "workers"], "primary_display_columns": ["name", "email", "employee_id"], "date_columns": ["hire_date", "start_date", "created_at"], "searchable_columns": ["name", "email", "department", "position"] }, "departments": { "type": "entity", "common_names": ["department", "departments", "division", "divisions", "team"], "primary_display_columns": ["name", "code", "description"], "date_columns": ["created_at"], "searchable_columns": ["name", "code", "description"] }, "projects": { "type": "temporal", "common_names": ["project", "projects", "assignment", "assignments"], "primary_display_columns": ["name", "status", "progress"], "date_columns": ["start_date", "end_date", "created_at"], "searchable_columns": ["name", "description", "status"] } }, "query_patterns": { "count_keywords": ["count", "how many", "number of", "total"], "recent_keywords": ["recent", "latest", "current", "active", "ongoing"], "list_keywords": ["show", "list", "display", "get", "find", "all"], "search_keywords": ["search", "find", "where", "with", "in department", "working on"] }, "priority_tables": ["employees", "departments", "projects"], "relationship_hints": { "employees": [{"via_table": "employee_projects", "column": "employee_id", "references": "id"}], "departments": [{"via_table": "employees", "column": "department_id", "references": "id"}] } } def create_blog_cms_config(): """Configuration for blog/CMS databases""" return { "semantic_mappings": { "posts": { "type": "temporal", "common_names": ["post", "posts", "article", "articles", "blog", "content"], "primary_display_columns": ["title", "slug", "status"], "date_columns": ["published_at", "created_at", "updated_at"], "searchable_columns": ["title", "content", "excerpt", "slug"] }, "authors": { "type": "entity", "common_names": ["author", "authors", "writer", "writers", "user", "users"], "primary_display_columns": ["name", "email", "username"], "date_columns": ["created_at", "last_login"], "searchable_columns": ["name", "email", "username", "bio"] }, "categories": { "type": "entity", "common_names": ["category", "categories", "topic", "topics", "tag"], "primary_display_columns": ["name", "slug", "description"], "date_columns": ["created_at"], "searchable_columns": ["name", "slug", "description"] } }, "query_patterns": { "count_keywords": ["count", "how many", "number of", "total"], "recent_keywords": ["recent", "latest", "newest", "published", "current"], "list_keywords": ["show", "list", "display", "get", "find", "all"], "search_keywords": ["search", "find", "about", "tagged", "in category", "written by"] }, "priority_tables": ["posts", "authors", "categories"], "relationship_hints": { "posts": [ {"via_table": "post_categories", "column": "post_id", "references": "id"}, {"via_table": "authors", "column": "author_id", "references": "id"} ] } } def create_inventory_config(): """Configuration for inventory management databases""" return { "semantic_mappings": { "items": { "type": "entity", "common_names": ["item", "items", "product", "products", "inventory", "stock"], "primary_display_columns": ["name", "sku", "quantity"], "date_columns": ["created_at", "updated_at", "last_counted"], "searchable_columns": ["name", "sku", "description", "barcode"] }, "warehouses": { "type": "entity", "common_names": ["warehouse", "warehouses", "location", "locations", "facility"], "primary_display_columns": ["name", "code", "address"], "date_columns": ["created_at"], "searchable_columns": ["name", "code", "address", "city"] }, "movements": { "type": "temporal", "common_names": ["movement", "movements", "transaction", "transactions", "transfer"], "primary_display_columns": ["type", "quantity", "reference"], "date_columns": ["created_at", "processed_at"], "searchable_columns": ["type", "reference", "notes"] } }, "priority_tables": ["items", "warehouses", "movements"] } def example_usage(): """Example of how to use custom configurations""" # Create database connection engine = create_engine("postgresql://user:password@localhost/ecommerce_db") # Initialize with e-commerce configuration postgres = PostgreSQLIntegration(engine, "ecommerce", create_e_commerce_config()) # Now the system understands e-commerce domain language sql1 = postgres.generate_sql("How many customers do we have?") # Will generate: SELECT COUNT(*) as count FROM users sql2 = postgres.generate_sql("Show me recent orders") # Will generate: SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 sql3 = postgres.generate_sql("Find products containing 'laptop'") # Will generate: SELECT * FROM products WHERE name ILIKE '%laptop%' OR description ILIKE '%laptop%' LIMIT 20 return sql1, sql2, sql3 def dynamic_configuration_example(): """Example of using automatic table discovery without predefined config""" engine = create_engine("postgresql://user:password@localhost/unknown_db") # Initialize without configuration - will auto-discover postgres = PostgreSQLIntegration(engine, "unknown") # The system will automatically: # 1. Discover all tables # 2. Analyze column names and types # 3. Detect relationships via foreign keys # 4. Create semantic mappings based on naming patterns # 5. Prioritize tables with ID + name columns # Generate SQL using discovered semantics sql = postgres.generate_sql("Show me recent entries") # Will automatically find the most appropriate table with date columns return sql def custom_patterns_example(): """Example of customizing query patterns for specific domains""" custom_config = { "query_patterns": { "count_keywords": ["count", "how many", "number of", "total", "census"], "recent_keywords": ["recent", "latest", "new", "fresh", "current", "today", "this week"], "list_keywords": ["show", "list", "display", "enumerate", "catalog"], "search_keywords": ["search", "find", "locate", "identify", "match"] }, "date_column_patterns": ["date", "time", "timestamp", "when", "at", "on"], "name_column_patterns": ["name", "title", "label", "caption", "description", "summary"], "id_column_patterns": ["id", "key", "pk", "identifier", "code"] } engine = create_engine("postgresql://user:password@localhost/custom_db") postgres = PostgreSQLIntegration(engine, "custom", custom_config) return postgres if __name__ == "__main__": print("PostgreSQL Configuration Examples") print("=" * 40) print() print("Available Configurations:") print("1. E-commerce: create_e_commerce_config()") print("2. HR Management: create_hr_management_config()") print("3. Blog/CMS: create_blog_cms_config()") print("4. Inventory: create_inventory_config()") print() print("Usage Patterns:") print("• Domain-specific configurations") print("• Automatic table discovery") print("• Custom query patterns") print("• Semantic table mappings") print("• Relationship discovery") print() print("Benefits:") print("✓ No hardcoded table names") print("✓ Configurable for any domain") print("✓ Automatic schema discovery") print("✓ Intelligent query generation") print("✓ Extensible pattern matching")