dewey-oracle/migrate_database.py

#!/usr/bin/env python3
"""
Database migration script to add admin features.

Adds:
- is_admin column to users table
- app_settings table
"""

import sqlite3
import sys

def migrate_database():
    """Perform database migration."""
    db_path = "./absrecommend.db"

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        print("Starting database migration...")

        # Check if is_admin column exists
        cursor.execute("PRAGMA table_info(users)")
        columns = [col[1] for col in cursor.fetchall()]

        if 'is_admin' not in columns:
            print("Adding is_admin column to users table...")
            cursor.execute("ALTER TABLE users ADD COLUMN is_admin BOOLEAN DEFAULT 0")

            # Make the first user an admin
            cursor.execute("SELECT COUNT(*) FROM users")
            user_count = cursor.fetchone()[0]

            if user_count > 0:
                print("Making the first user an admin...")
                cursor.execute("UPDATE users SET is_admin = 1 ORDER BY id LIMIT 1")
        else:
            print("is_admin column already exists")

        # Check if app_settings table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='app_settings'")
        if not cursor.fetchone():
            print("Creating app_settings table...")
            cursor.execute("""
                CREATE TABLE app_settings (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    key VARCHAR NOT NULL UNIQUE,
                    value VARCHAR NOT NULL,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            """)

            # Add default settings
            cursor.execute("INSERT INTO app_settings (key, value) VALUES ('allow_registration', 'true')")
        else:
            print("app_settings table already exists")

        conn.commit()
        print("\n✓ Migration completed successfully!")
        print("\nYou can now restart the service:")
        print("  ./restart-service.sh")

    except sqlite3.Error as e:
        print(f"\n✗ Migration failed: {e}", file=sys.stderr)
        sys.exit(1)
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    migrate_database()