Schema Management

/**
 * API for creating and migrating a SQL database. The implementation of this interface is generated by SQLDelight.
 * @param T Says whether the generated code for this database is asynchronous or synchronous. 
 *          Most implementations of SqlDriver will require one or the other.
 */
interface SqlSchema<T : QueryResult<Unit>> {
    /**
     * The version of this schema
     */
    val version: Long
    
    /**
     * Use driver to create the schema from scratch. Assumes no existing database state.
     * @param driver The database driver to use for schema creation
     * @returns QueryResult indicating completion
     */
    fun create(driver: SqlDriver): T
    
    /**
     * Use driver to migrate from schema oldVersion to newVersion.
     * Each of the callbacks are executed during the migration whenever the upgrade 
     * to the version specified by AfterVersion.afterVersion has been completed.
     * @param driver The database driver to use for migration
     * @param oldVersion The current schema version in the database
     * @param newVersion The target schema version to migrate to
     * @param callbacks Code blocks to execute after specific version upgrades
     * @returns QueryResult indicating completion
     */
    fun migrate(
        driver: SqlDriver,
        oldVersion: Long,
        newVersion: Long,
        vararg callbacks: AfterVersion
    ): T
}

import app.cash.sqldelight.db.SqlSchema
import app.cash.sqldelight.db.SqlDriver

// Generated schema implementation (typically auto-generated by SQLDelight)
class DatabaseSchema : SqlSchema<QueryResult.Value<Unit>> {
    override val version: Long = 3L
    
    override fun create(driver: SqlDriver): QueryResult.Value<Unit> {
        // Execute all CREATE TABLE statements
        driver.execute(null, """
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL,
                created_at INTEGER NOT NULL
            )
        """.trimIndent(), 0)
        
        driver.execute(null, """
            CREATE TABLE profiles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                bio TEXT,
                avatar_url TEXT
            )
        """.trimIndent(), 0)
        
        return QueryResult.Unit
    }
    
    override fun migrate(
        driver: SqlDriver,
        oldVersion: Long,
        newVersion: Long,
        vararg callbacks: AfterVersion
    ): QueryResult.Value<Unit> {
        // Handle migrations between versions
        for (version in (oldVersion + 1)..newVersion) {
            when (version) {
                2L -> migrateToVersion2(driver)
                3L -> migrateToVersion3(driver)
            }
            
            // Execute callbacks for this version
            callbacks.filter { it.afterVersion == version }
                .forEach { it.block(driver) }
        }
        
        return QueryResult.Unit
    }
}

// Initialize new database
val schema = DatabaseSchema()
val driver = createSqlDriver()

// Create fresh database
schema.create(driver)

// Or migrate existing database
val currentVersion = getCurrentDatabaseVersion(driver)
if (currentVersion < schema.version) {
    schema.migrate(driver, currentVersion, schema.version)
}

/**
 * Represents a block of code that should be executed during a migration after 
 * the migration has finished migrating to afterVersion.
 * @param afterVersion Version after which to execute the block
 * @param block Code block to execute, receives the SqlDriver for database operations
 */
class AfterVersion(
    val afterVersion: Long,
    val block: (SqlDriver) -> Unit
)

import app.cash.sqldelight.db.AfterVersion

// Data migration after schema upgrade
val migrateUserData = AfterVersion(2L) { driver ->
    // After migrating to version 2, populate new columns with default values
    driver.execute(
        identifier = null,
        sql = "UPDATE users SET created_at = ? WHERE created_at IS NULL",
        parameters = 1
    ) {
        bindLong(1, System.currentTimeMillis())
    }
    
    println("Populated created_at column for existing users")
}

// Index creation after table modifications
val createIndices = AfterVersion(3L) { driver ->
    driver.execute(
        identifier = null,
        sql = "CREATE INDEX idx_users_email ON users(email)",
        parameters = 0
    )
    
    driver.execute(
        identifier = null,
        sql = "CREATE INDEX idx_profiles_user_id ON profiles(user_id)",
        parameters = 0
    )
    
    println("Created performance indices")
}

// Data cleanup after structural changes
val cleanupOldData = AfterVersion(4L) { driver ->
    // Remove orphaned records after foreign key constraints were added
    driver.execute(
        identifier = null,
        sql = "DELETE FROM profiles WHERE user_id NOT IN (SELECT id FROM users)",
        parameters = 0
    )
    
    println("Cleaned up orphaned profile records")
}

// Apply migration with callbacks  
schema.migrate(
    driver = driver,
    oldVersion = 1L,
    newVersion = 4L,
    migrateUserData,
    createIndices,
    cleanupOldData
)

import app.cash.sqldelight.db.SqlSchema
import app.cash.sqldelight.db.SqlDriver

// Complete database initialization
fun initializeDatabase(driver: SqlDriver): Database {
    val schema = Database.Schema
    
    // Check if database exists and get version
    val currentVersion = try {
        getCurrentSchemaVersion(driver)
    } catch (e: SQLException) {
        // Database doesn't exist yet
        0L
    }
    
    when {
        currentVersion == 0L -> {
            // Fresh installation
            println("Creating new database...")
            schema.create(driver)
            setSchemaVersion(driver, schema.version)
            println("Database created with version ${schema.version}")
        }
        
        currentVersion < schema.version -> {
            // Migration needed
            println("Migrating database from version $currentVersion to ${schema.version}")
            schema.migrate(driver, currentVersion, schema.version)
            setSchemaVersion(driver, schema.version)
            println("Migration completed")
        }
        
        currentVersion > schema.version -> {
            // Downgrade scenario (usually not supported)
            throw IllegalStateException(
                "Database version $currentVersion is newer than app version ${schema.version}"
            )
        }
        
        else -> {
            println("Database is up to date (version ${schema.version})")
        }
    }
    
    return Database(driver)
}

// Helper functions for version management
fun getCurrentSchemaVersion(driver: SqlDriver): Long {
    return try {
        driver.executeQuery(
            identifier = null,
            sql = "PRAGMA user_version",
            mapper = { cursor ->
                if (cursor.next().value) {
                    QueryResult.Value(cursor.getLong(0) ?: 0L)
                } else {
                    QueryResult.Value(0L)
                }
            },
            parameters = 0
        ).value
    } catch (e: SQLException) {
        0L
    }
}

fun setSchemaVersion(driver: SqlDriver, version: Long) {
    driver.execute(
        identifier = null,
        sql = "PRAGMA user_version = $version",
        parameters = 0
    )
}

import app.cash.sqldelight.db.SqlSchema
import app.cash.sqldelight.db.AfterVersion

// Migration with table restructuring
class ComplexMigrationSchema : SqlSchema<QueryResult.Value<Unit>> {
    override val version: Long = 5L
    
    override fun migrate(
        driver: SqlDriver,
        oldVersion: Long,
        newVersion: Long,
        vararg callbacks: AfterVersion
    ): QueryResult.Value<Unit> {
        
        for (version in (oldVersion + 1)..newVersion) {
            when (version) {
                2L -> {
                    // Add new column
                    driver.execute(null, "ALTER TABLE users ADD COLUMN phone TEXT", 0)
                }
                
                3L -> {
                    // Restructure table by creating new table and copying data
                    driver.execute(null, """
                        CREATE TABLE users_new (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            name TEXT NOT NULL,
                            email TEXT UNIQUE NOT NULL,
                            phone TEXT,
                            created_at INTEGER NOT NULL,
                            updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
                        )
                    """.trimIndent(), 0)
                    
                    // Copy data from old table
                    driver.execute(null, """
                        INSERT INTO users_new (id, name, email, phone, created_at, updated_at)
                        SELECT id, name, email, phone, created_at, created_at FROM users
                    """.trimIndent(), 0)
                    
                    // Drop old table and rename new one
                    driver.execute(null, "DROP TABLE users", 0)
                    driver.execute(null, "ALTER TABLE users_new RENAME TO users", 0)
                }
                
                4L -> {
                    // Split table into multiple tables
                    driver.execute(null, """
                        CREATE TABLE user_profiles (
                            user_id INTEGER PRIMARY KEY REFERENCES users(id),
                            bio TEXT,
                            avatar_url TEXT,
                            created_at INTEGER NOT NULL
                        )
                    """.trimIndent(), 0)
                    
                    // Create user_settings table
                    driver.execute(null, """
                        CREATE TABLE user_settings (
                            user_id INTEGER PRIMARY KEY REFERENCES users(id),
                            theme TEXT NOT NULL DEFAULT 'light',
                            notifications BOOLEAN NOT NULL DEFAULT 1,
                            language TEXT NOT NULL DEFAULT 'en'
                        )
                    """.trimIndent(), 0)
                }
                
                5L -> {
                    // Add foreign key constraints (recreate table on SQLite)
                    driver.execute(null, "PRAGMA foreign_keys = OFF", 0)
                    
                    // Create new table with constraints
                    driver.execute(null, """
                        CREATE TABLE profiles_new (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            user_id INTEGER NOT NULL,
                            bio TEXT,
                            avatar_url TEXT,
                            created_at INTEGER NOT NULL,
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                        )
                    """.trimIndent(), 0)
                    
                    // Copy data
                    driver.execute(null, """
                        INSERT INTO profiles_new SELECT * FROM profiles
                    """.trimIndent(), 0)
                    
                    // Replace old table
                    driver.execute(null, "DROP TABLE profiles", 0)
                    driver.execute(null, "ALTER TABLE profiles_new RENAME TO profiles", 0)
                    driver.execute(null, "PRAGMA foreign_keys = ON", 0)
                }
            }
            
            // Execute version-specific callbacks
            callbacks.filter { it.afterVersion == version }
                .forEach { it.block(driver) }
        }
        
        return QueryResult.Unit
    }
}

// Data transformation callbacks
val transformUserData = AfterVersion(3L) { driver ->
    // Normalize phone numbers after adding phone column
    val users = driver.executeQuery(
        null,
        "SELECT id, phone FROM users WHERE phone IS NOT NULL",
        { cursor ->
            val users = mutableListOf<Pair<Long, String>>()
            while (cursor.next().value) {
                val id = cursor.getLong(0)!!
                val phone = cursor.getString(1)
                if (phone != null) {
                    users.add(id to normalizePhoneNumber(phone))
                }
            }
            QueryResult.Value(users)
        },
        0
    ).value
    
    // Update with normalized numbers
    users.forEach { (id, normalizedPhone) ->
        driver.execute(
            null,
            "UPDATE users SET phone = ? WHERE id = ?",
            2
        ) {
            bindString(1, normalizedPhone)
            bindLong(2, id)
        }
    }
}

val populateDefaultSettings = AfterVersion(4L) { driver ->
    // Create default settings for all existing users
    driver.execute(null, """
        INSERT INTO user_settings (user_id, theme, notifications, language)
        SELECT id, 'light', 1, 'en' FROM users
        WHERE id NOT IN (SELECT user_id FROM user_settings)
    """.trimIndent(), 0)
}

// Apply complex migration
schema.migrate(
    driver = driver,
    oldVersion = 1L,
    newVersion = 5L,
    transformUserData,
    populateDefaultSettings
)

import app.cash.sqldelight.db.SqlSchema
import app.cash.sqldelight.Transacter

// Safe migration with transaction rollback
fun safeMigrate(
    schema: SqlSchema<QueryResult.Value<Unit>>,
    driver: SqlDriver,
    oldVersion: Long
): Boolean {
    return try {
        // Wrap migration in transaction for automatic rollback
        (driver as? Transacter)?.transactionWithResult { 
            // Backup current version
            val currentVersion = getCurrentSchemaVersion(driver)
            
            try {
                schema.migrate(driver, oldVersion, schema.version)
                setSchemaVersion(driver, schema.version)
                true
            } catch (e: Exception) {
                println("Migration failed: ${e.message}")
                // Transaction will automatically rollback
                throw e
            }
        } ?: run {
            // Fallback for drivers that don't support transactions
            schema.migrate(driver, oldVersion, schema.version)
            setSchemaVersion(driver, schema.version)
            true
        }
    } catch (e: Exception) {
        println("Migration failed and was rolled back: ${e.message}")
        false
    }
}

// Migration with backup and restore capability
fun migrateWithBackup(
    schema: SqlSchema<QueryResult.Value<Unit>>,
    driver: SqlDriver,
    oldVersion: Long
): MigrationResult {
    return try {
        // Create backup before migration
        val backupPath = createDatabaseBackup(driver)
        
        try {
            schema.migrate(driver, oldVersion, schema.version)
            setSchemaVersion(driver, schema.version)
            
            MigrationResult.Success(schema.version)
        } catch (e: Exception) {
            // Restore from backup on failure
            restoreDatabaseBackup(driver, backupPath)
            MigrationResult.Failed(e.message ?: "Unknown error", oldVersion)
        }
    } catch (e: Exception) {
        MigrationResult.Failed("Backup creation failed: ${e.message}", oldVersion)
    }
}

sealed class MigrationResult {
    data class Success(val newVersion: Long) : MigrationResult()
    data class Failed(val error: String, val currentVersion: Long) : MigrationResult()
}

// Validation after migration
fun validateMigration(driver: SqlDriver, expectedVersion: Long): Boolean {
    return try {
        val actualVersion = getCurrentSchemaVersion(driver)
        if (actualVersion != expectedVersion) {
            println("Version mismatch: expected $expectedVersion, got $actualVersion")
            return false
        }
        
        // Validate table structure
        val tables = getTableNames(driver)
        val requiredTables = setOf("users", "profiles", "user_settings")
        
        if (!tables.containsAll(requiredTables)) {
            println("Missing required tables. Found: $tables, Required: $requiredTables")
            return false
        }
        
        // Validate data integrity
        val userCount = driver.executeQuery(
            null,
            "SELECT COUNT(*) FROM users",
            { cursor ->
                cursor.next()
                QueryResult.Value(cursor.getLong(0) ?: 0L)
            },
            0
        ).value
        
        val profileCount = driver.executeQuery(
            null,
            "SELECT COUNT(*) FROM profiles",
            { cursor ->
                cursor.next()
                QueryResult.Value(cursor.getLong(0) ?: 0L)
            },
            0
        ).value
        
        println("Migration validation passed: $userCount users, $profileCount profiles")
        true
    } catch (e: Exception) {
        println("Migration validation failed: ${e.message}")
        false
    }
}

// Usage with validation
val migrationResult = migrateWithBackup(schema, driver, currentVersion)
when (migrationResult) {
    is MigrationResult.Success -> {
        if (validateMigration(driver, migrationResult.newVersion)) {
            println("Migration completed and validated successfully")
        } else {
            println("Migration completed but validation failed")
        }
    }
    is MigrationResult.Failed -> {
        println("Migration failed: ${migrationResult.error}")
        println("Database remains at version ${migrationResult.currentVersion}")
    }
}