sqlbuilder.py

"""
SQL BUILDER MODULE - MySQL Schema Builder for Redis-to-SQL Migration
"""

import re
from datetime import datetime
from collections import defaultdict


class MySQLSchemaBuilder:
    """
    MySQL Schema Builder for Redis-to-SQL conversion.
    
    Handles dynamic table creation based on Redis key patterns,
    intelligent data type mapping, and relationship detection.
    """
    
    def __init__(self, cursor):
        """Initialize with MySQL cursor and tracking structures."""
        self.cursor = cursor
        self.created_tables = set()
        self.table_columns = {}  # Track columns for each table
        self.simple_keys = []    # Track simple key-value pairs for config table
        
    def create_database(self, db_name):
        """Create database and switch to it."""
        try:
            self.cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name}")
            self.cursor.execute(f"USE {db_name}")
            print(f"Database '{db_name}' created and selected")
            return True
        except Exception as e:
            print(f"Error creating database: {e}")
            return False
    
    def build_schema_from_patterns(self, key_patterns, sample_data):
        """
        Main method to build schema from Redis patterns.
        Two-pass approach: entities first, then relationships.
        """
        print("\n" + "="*60)
        print("BUILDING MySQL SCHEMA FROM REDIS PATTERNS")
        print("="*60)
        
        # Collect simple keys for config table
        self._collect_simple_keys(key_patterns)
        
        # First pass: Create entity tables
        print("\nPASS 1: Creating entity tables...")
        for pattern, keys in key_patterns.items():
            if self._is_entity_pattern(pattern):
                self._create_entity_table(pattern, keys, sample_data)
        
        # Second pass: Create relationship tables
        print("\nPASS 2: Creating relationship tables...")
        for pattern, keys in key_patterns.items():
            if self._is_relationship_pattern(pattern):
                self._create_relationship_table(pattern, keys, sample_data)
        
        # Third pass: Create config table for simple keys
        if self.simple_keys:
            print("\nPASS 3: Creating configuration table...")
            self._create_config_table()
        
        print(f"\nSchema creation complete! Created {len(self.created_tables)} tables:")
        for table in sorted(self.created_tables):
            print(f"   {table}")
    
    def _collect_simple_keys(self, key_patterns):
        """Collect simple keys that don't follow entity:id pattern"""
        for pattern, keys in key_patterns.items():
            if ':' not in pattern or pattern.count(':') == 0:
                self.simple_keys.extend(keys)
            elif not self._is_entity_pattern(pattern) and not self._is_relationship_pattern(pattern):
                self.simple_keys.extend(keys)
    
    def _is_entity_pattern(self, pattern):
        """Check if pattern is single-level entity (user:*, post:*)."""
        parts = pattern.split(':')
        # Single level with ID: entity:*
        return len(parts) == 2 and parts[1] == '*'
    
    def _is_relationship_pattern(self, pattern):
        """Check if pattern is multi-level relationship (user:*:posts)."""
        parts = pattern.split(':')
        # Multi-level: entity:*:relationship or entity:*:relationship:*
        return len(parts) >= 3 and '*' in parts
    
    def _create_entity_table(self, pattern, keys, sample_data):
        """Create table for entity pattern."""
        entity_name = pattern.split(':')[0]
        table_name = self._pluralize_table_name(entity_name)
        
        print(f"    Creating entity table: {table_name}")
        
        # Special handling for tags pattern
        if entity_name == 'tags':
            self._create_tags_table(table_name)
            return
        
        # Get sample data from first key to determine structure
        sample_key = keys[0] if keys else None
        if not sample_key or sample_key not in sample_data:
            print(f"     No sample data for {pattern}, creating basic table")
            self._create_basic_table(table_name, entity_name)
            return
        
        sample = sample_data[sample_key]
        
        # Handle different Redis data types
        if isinstance(sample, dict):  # Hash data
            self._create_table_from_hash_data(table_name, sample, entity_name)
        else:
            print(f"     Non-hash entity data for {pattern}, creating basic table")
            self._create_basic_table(table_name, entity_name)
    
    def _create_tags_table(self, table_name):
        """Create specialized tags table for post tags"""
        if table_name in self.created_tables:
            return
        
        create_sql = f"""
        CREATE TABLE {table_name} (
            id INT PRIMARY KEY AUTO_INCREMENT,
            post_id INT NOT NULL,
            tag VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_post_id (post_id),
            INDEX idx_tag (tag),
            UNIQUE KEY unique_post_tag (post_id, tag)
        )
        """
        
        try:
            self.cursor.execute(create_sql)
            self.created_tables.add(table_name)
            self.table_columns[table_name] = ['id', 'post_id', 'tag', 'created_at']
            print(f"   Created specialized tags table: {table_name}")
        except Exception as e:
            print(f"   Error creating tags table {table_name}: {e}")

    def _create_table_from_hash_data(self, table_name, hash_data, entity_name):
        """Create SQL table based on Redis hash structure."""
        if table_name in self.created_tables:
            return
        
        columns = []
        columns.append(f"{entity_name}_id INT PRIMARY KEY AUTO_INCREMENT")
        
        # Analyze each field in the hash
        for field_name, field_value in hash_data.items():
            sql_type = self._infer_sql_type(field_name, field_value)
            # Escape reserved words
            safe_field_name = self._escape_reserved_word(field_name)
            columns.append(f"{safe_field_name} {sql_type}")
        
        # Add updated timestamp if not present
        if 'updated_at' not in hash_data:
            columns.append("updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
        
        column_separator = ',\n            '
        create_sql = f"""
        CREATE TABLE {table_name} (
            {column_separator.join(columns)}
        )
        """
        
        try:
            self.cursor.execute(create_sql)
            self.created_tables.add(table_name)
            self.table_columns[table_name] = [col.split()[0] for col in columns]
            print(f"   Created table: {table_name} with {len(columns)} columns")
        except Exception as e:
            print(f"   Error creating table {table_name}: {e}")
    
    def _escape_reserved_word(self, field_name):
        """Escape MySQL reserved words"""
        reserved_words = {
            'order', 'group', 'select', 'from', 'where', 'table', 'index',
            'key', 'primary', 'foreign', 'references', 'constraint', 'check'
        }
        if field_name.lower() in reserved_words:
            return f"`{field_name}`"
        return field_name
    
    def _create_basic_table(self, table_name, entity_name):
        """Create a basic table when no hash data is available."""
        if table_name in self.created_tables:
            return
        
        create_sql = f"""
        CREATE TABLE {table_name} (
            {entity_name}_id INT PRIMARY KEY AUTO_INCREMENT,
            redis_key VARCHAR(255) UNIQUE NOT NULL,
            value TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
        
        try:
            self.cursor.execute(create_sql)
            self.created_tables.add(table_name)
            print(f"   Created basic table: {table_name}")
        except Exception as e:
            print(f"   Error creating basic table {table_name}: {e}")
    
    def _infer_sql_type(self, field_name, field_value):
        """Intelligently map Redis data to SQL types."""
        if not field_value:
            return "VARCHAR(255)"
        
        value_str = str(field_value).strip()
        
        # ID fields
        if field_name.endswith('_id') or field_name == 'id':
            if value_str.isdigit():
                return "INT"
            else:
                return "VARCHAR(50)"
        
        # Email fields
        if 'email' in field_name.lower() or self._looks_like_email(value_str):
            return "VARCHAR(255)"
        
        # Phone fields
        if 'phone' in field_name.lower():
            return "VARCHAR(20)"
        
        # Date fields
        if any(date_word in field_name.lower() for date_word in ['date', 'time', 'created', 'updated']) or self._looks_like_date(value_str):
            return "DATETIME"
        
        # Price/money fields
        if any(money_word in field_name.lower() for money_word in ['price', 'cost', 'amount', 'total']):
            try:
                float(value_str)
                return "DECIMAL(10,2)"
            except ValueError:
                pass
        
        # Numeric fields
        if value_str.isdigit():
            num = int(value_str)
            if num < 128:
                return "TINYINT"
            elif num < 32768:
                return "SMALLINT"
            elif num < 2147483648:
                return "INT"
            else:
                return "BIGINT"
        
        # Float fields
        try:
            float(value_str)
            return "FLOAT"
        except ValueError:
            pass
        
        # URL fields
        if self._looks_like_url(value_str):
            return "TEXT"
        
        # Text length-based decisions
        if len(value_str) <= 50:
            return "VARCHAR(100)"
        elif len(value_str) <= 255:
            return "VARCHAR(500)"
        else:
            return "TEXT"
    
    def _looks_like_date(self, value):
        """Helper for date detection."""
        date_patterns = [
            r'\d{4}-\d{2}-\d{2}',  # YYYY-MM-DD
            r'\d{2}/\d{2}/\d{4}',  # MM/DD/YYYY
            r'\d{4}/\d{2}/\d{2}',  # YYYY/MM/DD
            r'\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}'  # ISO 8601 format
        ]
        return any(re.match(pattern, str(value)) for pattern in date_patterns)
    
    def _looks_like_email(self, value):
        """Helper for email detection."""
        email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
        return re.match(email_pattern, str(value)) is not None
    
    def _looks_like_url(self, value):
        """Helper for URL detection."""
        url_pattern = r'^https?://'
        return re.match(url_pattern, str(value)) is not None
    
    def _create_relationship_table(self, pattern, keys, sample_data):
        """Create junction tables for relationships."""
        parts = pattern.split(':')
        
        if len(parts) == 3:  # user:*:posts format
            entity1 = parts[0]
            relationship = parts[2]
            
            # Check if this is a list or set relationship
            sample_key = keys[0] if keys else None
            if sample_key and sample_key in sample_data:
                sample = sample_data[sample_key]
                if isinstance(sample, list):
                    self._create_list_relationship_table(entity1, relationship, pattern)
                elif isinstance(sample, set):
                    self._create_set_relationship_table(entity1, relationship, pattern)
                else:
                    print(f"     Unknown relationship type for {pattern}")
    
    def _create_list_relationship_table(self, entity1, relationship, pattern):
        """Create relationship table for Redis lists (with position tracking)."""
        table_name = f"{entity1}_{relationship}"
        
        if table_name in self.created_tables:
            return
        
        # Determine if relationship values are IDs or strings
        id_column_type = "INT"  # Assume IDs for now
        
        create_sql = f"""
        CREATE TABLE {table_name} (
            id INT PRIMARY KEY AUTO_INCREMENT,
            {entity1}_id INT NOT NULL,
            {relationship.rstrip('s')}_id {id_column_type} NOT NULL,
            position_order INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_{entity1}_id ({entity1}_id),
            INDEX idx_{relationship.rstrip('s')}_id ({relationship.rstrip('s')}_id)
        )
        """
        
        try:
            self.cursor.execute(create_sql)
            self.created_tables.add(table_name)
            print(f"   Created relationship table: {table_name}")
        except Exception as e:
            print(f"   Error creating relationship table {table_name}: {e}")
    
    def _create_set_relationship_table(self, entity1, relationship, pattern):
        """Create relationship table for Redis sets"""
        table_name = f"{entity1}_{relationship}"
        
        if table_name in self.created_tables:
            return
        
        create_sql = f"""
        CREATE TABLE {table_name} (
            id INT PRIMARY KEY AUTO_INCREMENT,
            {entity1}_id INT NOT NULL,
            {relationship.rstrip('s')}_name VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_{entity1}_{relationship} ({entity1}_id, {relationship.rstrip('s')}_name),
            INDEX idx_{entity1}_id ({entity1}_id)
        )
        """
        
        try:
            self.cursor.execute(create_sql)
            self.created_tables.add(table_name)
            print(f"   Created set relationship table: {table_name}")
        except Exception as e:
            print(f"   Error creating set relationship table {table_name}: {e}")

    def _create_config_table(self):
        """Create config table for simple key-value pairs."""
        table_name = "configuration"
        
        if table_name in self.created_tables:
            return
        
        create_sql = """
        CREATE TABLE configuration (
            id INT PRIMARY KEY AUTO_INCREMENT,
            config_key VARCHAR(255) UNIQUE NOT NULL,
            config_value TEXT,
            data_type VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_config_key (config_key)
        )
        """
        
        try:
            self.cursor.execute(create_sql)
            self.created_tables.add(table_name)
            print(f"   Created configuration table")
        except Exception as e:
            print(f"   Error creating configuration table: {e}")
    
    def _pluralize_table_name(self, entity_name):
        """Convert entity name to plural table name."""
        # Special cases for better naming
        if entity_name == 'tags':  # Already plural
            return 'tags'
        elif entity_name == 'tag':
            return 'tags'
        elif entity_name.endswith('y'):
            return entity_name[:-1] + 'ies'  # category -> categories
        elif entity_name.endswith(('s', 'sh', 'ch', 'x', 'z')):
            return entity_name + 'es'  # class -> classes
        else:
            return entity_name + 's'  # user -> users
    
    def get_created_tables(self):
        """Return list of created tables."""
        return sorted(list(self.created_tables))
    
    def get_table_info(self):
        """Return detailed information about created tables."""
        return {
            'tables': list(self.created_tables),
            'table_columns': self.table_columns,
            'total_tables': len(self.created_tables)
        }


# Test section - for running the module directly
if __name__ == "__main__":
    print("MySQL Schema Builder - Test Mode")
    print("="*50)
    
    # Mock cursor for testing (without actual MySQL connection)
    class MockCursor:
        def execute(self, sql):
            print(f"SQL: {sql}")
    
    # Test the builder without database connection
    print("\nCreating MySQLSchemaBuilder instance...")
    builder = MySQLSchemaBuilder(MockCursor())
    
    # Test pattern detection
    print("\nTesting pattern detection...")
    test_patterns = {
        'user:*': ['user:1', 'user:2', 'user:3'],
        'post:*': ['post:101', 'post:102'], 
        'user:*:posts': ['user:1:posts', 'user:2:posts'],
        'tags:*': ['tags:101', 'tags:102']
    }
    
    for pattern, keys in test_patterns.items():
        is_entity = builder._is_entity_pattern(pattern)
        is_relationship = builder._is_relationship_pattern(pattern)
        print(f"   Pattern: {pattern}")
        print(f"   Entity: {is_entity}, Relationship: {is_relationship}")
    
    # Test data type inference
    print("\nTesting data type inference...")
    test_fields = {
        'user_id': '123',
        'email': 'test@example.com',
        'created_at': '2024-01-15',
        'age': '25',
        'price': '99.99',
        'phone': '+1234567890',
        'name': 'John Doe',
        'bio': 'This is a very long biography that should be stored as TEXT...'
    }
    
    for field, value in test_fields.items():
        sql_type = builder._infer_sql_type(field, value)
        print(f"   {field}: '{value}' → {sql_type}")
    
    # Test table naming
    print("\nTesting table naming...")
    test_entities = ['user', 'category', 'class', 'post']
    for entity in test_entities:
        plural = builder._pluralize_table_name(entity)
        print(f"   {entity} → {plural}")
    
    print(f"\nMySQLSchemaBuilder test completed successfully!")
    print("   This module is ready to be imported and used by redis2mysql.py")
    print("   Run redis2mysql.py to see the full conversion in action.")