#!/usr/bin/env python """ Fix RolloverRequest schema by directly adding missing columns to the database. This script is designed to run in production without requiring Django migrations. """ import os import sys import django import logging from decimal import Decimal # Setup logging logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', handlers=[ logging.FileHandler("rollover_schema_fix.log"), logging.StreamHandler() ] ) logger = logging.getLogger(__name__) # Set up Django environment os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') django.setup() from django.db import connection, transaction from django.conf import settings def get_table_name(model_name): """Get the actual table name for a model by checking what exists in the database""" possible_names = [ f'loans_{model_name.lower()}', f'loan_{model_name.lower()}', f'{model_name.lower()}', f'xygbfpsg_graz.loans_{model_name.lower()}', f'xygbfpsg_graz.loan_{model_name.lower()}' ] with connection.cursor() as cursor: for name in possible_names: try: if 'mysql' in connection.vendor: cursor.execute(f"SHOW TABLES LIKE '{name}'") elif 'sqlite' in connection.vendor: cursor.execute(f"SELECT name FROM sqlite_master WHERE type='table' AND name='{name}'") else: # PostgreSQL cursor.execute(f"SELECT to_regclass('{name}')") result = cursor.fetchone() if result: logger.info(f"Found table: {name}") return name except Exception as e: logger.warning(f"Error checking table {name}: {str(e)}") # If we get here, try to get the table name from Django's model try: from loans.models import RolloverRequest table_name = RolloverRequest._meta.db_table logger.info(f"Using Django model table name: {table_name}") return table_name except: logger.warning("Could not determine table name from Django model") # Default fallback return 'loans_rolloverrequest' def check_column_exists(table_name, column_name): """Check if a column exists in a table""" try: with connection.cursor() as cursor: if 'mysql' in connection.vendor: cursor.execute(""" SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s """, [table_name.split('.')[-1], column_name]) elif 'sqlite' in connection.vendor: cursor.execute(f"PRAGMA table_info({table_name})") columns = [col[1] for col in cursor.fetchall()] return column_name in columns else: cursor.execute(""" SELECT COUNT(*) FROM information_schema.columns WHERE table_name = %s AND column_name = %s """, [table_name.split('.')[-1], column_name]) if 'sqlite' not in connection.vendor: result = cursor.fetchone()[0] return result > 0 return False except Exception as e: logger.error(f"Error checking if column exists: {str(e)}") return False def add_column_if_not_exists(table_name, column_name, column_definition): """Add a column to a table if it doesn't exist""" try: if not check_column_exists(table_name, column_name): logger.info(f"Adding column {column_name} to {table_name}") with connection.cursor() as cursor: if 'mysql' in connection.vendor: cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}") elif 'sqlite' in connection.vendor: cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}") else: # PostgreSQL cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN IF NOT EXISTS {column_name} {column_definition}") return True else: logger.info(f"Column {column_name} already exists in {table_name}") return False except Exception as e: logger.error(f"Error adding column: {str(e)}") return False def fix_rollover_schema(): """Fix the RolloverRequest schema by adding missing columns""" # Get the actual table name table_name = get_table_name('rolloverrequest') if not table_name: logger.error("Could not find RolloverRequest table") return False try: with transaction.atomic(): # Add missing columns with appropriate definitions columns_to_add = { 'requested_amount': 'DECIMAL(12,2) NOT NULL DEFAULT 0', 'requested_duration': 'INTEGER NOT NULL DEFAULT 30', 'requested_interest_rate': 'DECIMAL(5,2) NULL', 'requested_processing_fee': 'DECIMAL(12,2) NULL', } for column_name, definition in columns_to_add.items(): added = add_column_if_not_exists(table_name, column_name, definition) if added: logger.info(f"Successfully added column {column_name} to {table_name}") # Try to update the requested_amount to match the loan amount for existing records try: loan_table = get_table_name('loan') if loan_table: with connection.cursor() as cursor: try: cursor.execute(f""" UPDATE {table_name} rr JOIN {loan_table} l ON rr.loan_id = l.id SET rr.requested_amount = l.amount WHERE rr.requested_amount = 0 """) rows_updated = cursor.rowcount logger.info(f"Updated requested_amount for {rows_updated} existing rollover requests") cursor.execute(f""" UPDATE {table_name} rr JOIN {loan_table} l ON rr.loan_id = l.id SET rr.requested_duration = l.duration_days WHERE rr.requested_duration = 30 """) rows_updated = cursor.rowcount logger.info(f"Updated requested_duration for {rows_updated} existing rollover requests") except Exception as e: logger.warning(f"Could not update existing records: {str(e)}") except Exception as e: logger.warning(f"Error updating existing records: {str(e)}") logger.info("Schema fix completed successfully") return True except Exception as e: logger.error(f"Error fixing schema: {str(e)}") return False def fix_migration_history(): """Fix inconsistent migration history by updating the django_migrations table""" try: with transaction.atomic(): with connection.cursor() as cursor: # Check if the problematic migration exists cursor.execute(""" SELECT id FROM django_migrations WHERE app = 'users' AND name = '0012_branch' """) migration_exists = cursor.fetchone() if migration_exists: # Check if the dependency exists cursor.execute(""" SELECT id FROM django_migrations WHERE app = 'users' AND name = '0011_manual_add_is_default' """) dependency_exists = cursor.fetchone() if not dependency_exists: # Add the missing dependency migration with an earlier applied date try: cursor.execute(""" INSERT INTO django_migrations (app, name, applied) SELECT 'users', '0011_manual_add_is_default', (SELECT applied FROM django_migrations WHERE app = 'users' AND name = '0012_branch') - INTERVAL 1 SECOND """) logger.info("Added missing migration users.0011_manual_add_is_default") except Exception as e: # Try alternative syntax for SQLite try: cursor.execute(""" INSERT INTO django_migrations (app, name, applied) SELECT 'users', '0011_manual_add_is_default', datetime((SELECT applied FROM django_migrations WHERE app = 'users' AND name = '0012_branch'), '-1 second') """) logger.info("Added missing migration users.0011_manual_add_is_default (SQLite syntax)") except Exception as e2: logger.error(f"Could not add migration: {str(e2)}") else: logger.info("Dependency migration already exists") else: logger.info("Problematic migration does not exist") logger.info("Migration history fix completed successfully") return True except Exception as e: logger.error(f"Error fixing migration history: {str(e)}") return False def modify_loan_detail_view(): """Modify the loan_detail view to work without the missing columns""" try: # Try to find the views.py file import os from django.conf import settings base_dir = settings.BASE_DIR views_file = os.path.join(base_dir, 'loans', 'views.py') if not os.path.exists(views_file): logger.warning(f"Could not find views file at {views_file}") return False # Create a backup backup_file = views_file + '.bak' try: import shutil shutil.copy2(views_file, backup_file) logger.info(f"Created backup of views.py at {backup_file}") except Exception as e: logger.warning(f"Could not create backup: {str(e)}") # Read the file with open(views_file, 'r') as f: content = f.read() # Check if we need to modify the file if "RolloverRequest.objects.filter(loan=loan)" in content: # Replace the problematic line with raw SQL modified_content = content.replace( "rollovers = RolloverRequest.objects.filter(loan=loan).order_by('-requested_at')", """# Modified by fix_rollover_schema.py try: rollovers = RolloverRequest.objects.filter(loan=loan).order_by('-requested_at') except Exception as e: # Fallback to raw SQL if there's an error from django.db import connection with connection.cursor() as cursor: cursor.execute(\"\"\" SELECT id, loan_id, borrower_id, reason, rollover_fee, status, reviewed_by_id, reviewed_at, review_notes, requested_at, created_at, updated_at FROM loans_rolloverrequest WHERE loan_id = %s ORDER BY requested_at DESC \"\"\", [str(loan.id)]) columns = [col[0] for col in cursor.description] rollovers = [dict(zip(columns, row)) for row in cursor.fetchall()]""" ) # Write the modified content with open(views_file, 'w') as f: f.write(modified_content) logger.info("Modified loan_detail view to work without the missing columns") return True else: logger.info("No need to modify loan_detail view") return True except Exception as e: logger.error(f"Error modifying loan_detail view: {str(e)}") return False if __name__ == "__main__": logger.info("Starting database schema fix script") # Fix the schema issues schema_fixed = fix_rollover_schema() # Fix migration history issues migration_fixed = fix_migration_history() # Modify the loan_detail view as a fallback view_modified = modify_loan_detail_view() if schema_fixed and migration_fixed: logger.info("All fixes applied successfully") sys.exit(0) else: logger.error("Some fixes failed to apply") sys.exit(1)