Migrations, FTS & Database Views

Copy code// Adding a column (addColumn is non-destructive)
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE articles ADD COLUMN view_count INTEGER NOT NULL DEFAULT 0")
    }
}

// Renaming a column (SQLite doesn't support RENAME COLUMN before API 29)
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Old SQLite approach: create new table, copy data, drop old
        database.execSQL("""
            CREATE TABLE articles_new (
                id TEXT PRIMARY KEY NOT NULL,
                title TEXT NOT NULL,
                body TEXT NOT NULL,
                author_id TEXT NOT NULL,
                published_at INTEGER NOT NULL,
                view_count INTEGER NOT NULL DEFAULT 0
            )
        """)
        database.execSQL("""
            INSERT INTO articles_new SELECT id, title, body, authorId, published_at, view_count FROM articles
        """)
        database.execSQL("DROP TABLE articles")
        database.execSQL("ALTER TABLE articles_new RENAME TO articles")
    }
}

// Register migrations in the Database builder
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

Copy code// build.gradle.kts
ksp {
    arg("room.schemaLocation", "$projectDir/schemas")
    arg("room.generateKotlin", "true")
}

Copy code@RunWith(AndroidJUnit4::class)
class MigrationTest {
    @get:Rule val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test fun migrate1To2() {
        // Create v1 DB and insert data
        val db = helper.createDatabase(TEST_DB, 1).apply {
            execSQL("INSERT INTO articles VALUES ('a1', 'Title', 'Body', 'author1', 123456)")
            close()
        }
        // Run migration and verify schema
        val migratedDb = helper.runMigrationsAndValidate(TEST_DB, 2, true, MIGRATION_1_2)
        val cursor = migratedDb.query("SELECT view_count FROM articles WHERE id='a1'")
        assertTrue(cursor.moveToFirst())
        assertEquals(0, cursor.getInt(0))  // new column has default value
    }
}

Copy code@Fts4(contentEntity = ArticleEntity::class)
@Entity(tableName = "articles_fts")
data class ArticleFtsEntity(
    val title: String,
    val body: String
)

Copy code// In ArticleDao
@Query("SELECT * FROM articles WHERE id IN (SELECT rowid FROM articles_fts WHERE articles_fts MATCH :query)")
suspend fun searchArticles(query: String): List<ArticleEntity>

// Usage — FTS supports boolean operators and prefix search
dao.searchArticles("android AND performance")
dao.searchArticles("coroutin*")  // prefix: finds "coroutines", "coroutine"

Copy code@DatabaseView("""
    SELECT a.id, a.title, a.published_at,
           u.name as author_name,
           COUNT(c.id) as comment_count
    FROM articles a
    LEFT JOIN authors u ON a.author_id = u.id
    LEFT JOIN comments c ON c.article_id = a.id
    GROUP BY a.id
""")
data class ArticleSummaryView(
    val id: String,
    val title: String,
    val publishedAt: Long,
    val authorName: String,
    val commentCount: Int
)

// Register in @Database
@Database(
    entities = [ArticleEntity::class, AuthorEntity::class, CommentEntity::class],
    views = [ArticleSummaryView::class],
    version = 1
)
abstract class AppDatabase : RoomDatabase()

// Query the view like a table
@Dao
interface ArticleSummaryDao {
    @Query("SELECT * FROM ArticleSummaryView ORDER BY published_at DESC")
    fun getAll(): Flow<List<ArticleSummaryView>>
}