Mysword Sqlite Forensics

MySword SQLite Forensics — Engenharia Reversa de Arquivos

Use quando: O schema do arquivo é desconhecido, incompleto, ou diferente do esperado.
Princípio: Nunca assumir. Sempre inspecionar sqlite_master primeiro.

🔬 Passo 1 — Inspeção Completa de Qualquer Arquivo SQLite

/**
 * Ponto de entrada para qualquer arquivo .mybible ou .SQLite3 desconhecido.
 * Executa ANTES de qualquer query de dados.
 */
fun inspectSQLiteFile(filePath: String): SQLiteInspectionResult {
    return SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READONLY).use { db ->

        // 1. Listar TODAS as tabelas
        val tables = db.rawQuery(
            "SELECT name, type FROM sqlite_master WHERE type IN ('table','view') ORDER BY name", null
        ).use { c -> buildList { while (c.moveToNext()) add(c.getString(0) to c.getString(1)) } }

        // 2. Listar TODOS os índices
        val indexes = db.rawQuery(
            "SELECT name, tbl_name, sql FROM sqlite_master WHERE type='index' ORDER BY tbl_name", null
        ).use { c -> buildList { while (c.moveToNext()) add(Triple(c.getString(0), c.getString(1), c.getString(2))) } }

        // 3. Para cada tabela, listar colunas
        val schemas = tables.associate { (tableName, _) ->
            tableName to db.rawQuery("PRAGMA table_info('$tableName')", null).use { c ->
                buildList {
                    while (c.moveToNext()) {
                        add(ColumnInfo(
                            cid = c.getInt(0),
                            name = c.getString(1),
                            type = c.getString(2),
                            notNull = c.getInt(3) == 1,
                            defaultValue = c.getStringOrNull(4),
                            isPrimaryKey = c.getInt(5) == 1
                        ))
                    }
                }
            }
        }

        // 4. Contar registros por tabela
        val rowCounts = tables.associate { (tableName, _) ->
            tableName to db.rawQuery("SELECT COUNT(*) FROM '$tableName'", null)
                .use { c -> if (c.moveToFirst()) c.getLong(0) else 0L }
        }

        // 5. Verificar modo de journal
        val journalMode = db.rawQuery("PRAGMA journal_mode", null)
            .use { c -> if (c.moveToFirst()) c.getString(0) else "unknown" }

        // 6. Verificar encoding
        val encoding = db.rawQuery("PRAGMA encoding", null)
            .use { c -> if (c.moveToFirst()) c.getString(0) else "unknown" }

        SQLiteInspectionResult(tables, indexes, schemas, rowCounts, journalMode, encoding)
    }
}

data class ColumnInfo(
    val cid: Int, val name: String, val type: String,
    val notNull: Boolean, val defaultValue: String?, val isPrimaryKey: Boolean
)

data class SQLiteInspectionResult(
    val tables: List<Pair<String, String>>,    // (name, type)
    val indexes: List<Triple<String, String, String?>>,
    val schemas: Map<String, List<ColumnInfo>>,
    val rowCounts: Map<String, Long>,
    val journalMode: String,
    val encoding: String
) {
    fun summary(): String = buildString {
        appendLine("=== SQLite Inspection ===")
        appendLine("Journal mode: $journalMode  |  Encoding: $encoding")
        appendLine("Tables (${tables.size}):")
        tables.forEach { (name, type) ->
            val cols = schemas[name]?.joinToString(", ") { it.name } ?: "?"
            val rows = rowCounts[name] ?: 0
            appendLine("  $name [$type] ($rows rows) — columns: $cols")
        }
    }
}

🔍 Passo 2 — Identificação de Tipo por Assinatura de Tabelas

object SQLiteModuleIdentifier {

    data class ModuleSignature(
        val requiredTables: Set<String>,
        val optionalTables: Set<String> = emptySet(),
        val identifyingColumns: Map<String, Set<String>> = emptyMap()
    )

    // Assinaturas conhecidas
    val SIGNATURES = mapOf(
        "MySword Bible" to ModuleSignature(
            requiredTables = setOf("Bible", "Details"),
            identifyingColumns = mapOf("Bible" to setOf("Book", "Chapter", "Verse", "Scripture"))
        ),
        "MySword Commentary" to ModuleSignature(
            requiredTables = setOf("Commentary", "Details"),
            identifyingColumns = mapOf("Commentary" to setOf("Book", "Chapter", "FromVerse", "ToVerse", "Data"))
        ),
        "MySword Dictionary" to ModuleSignature(
            requiredTables = setOf("Dictionary", "Details"),
            identifyingColumns = mapOf("Dictionary" to setOf("Word", "Data"))
        ),
        "MySword Journal/Notes" to ModuleSignature(
            requiredTables = setOf("Commentary"),
            optionalTables = setOf("Details")
            // Commentary sem Details = notes pessoais
        ),
        "MyBible Bible" to ModuleSignature(
            requiredTables = setOf("verses", "info"),  // minúsculas!
            identifyingColumns = mapOf("verses" to setOf("book_number", "chapter", "verse", "text"))
        ),
        "MyBible Commentary" to ModuleSignature(
            requiredTables = setOf("commentaries", "info"),
            identifyingColumns = mapOf("commentaries" to setOf("book_number", "chapter_number_from", "verse_number_from", "text"))
        ),
        "MyBible Dictionary" to ModuleSignature(
            requiredTables = setOf("dictionary", "info"),
            identifyingColumns = mapOf("dictionary" to setOf("topic", "definition"))
        ),
        "MySword Bookmarks" to ModuleSignature(
            requiredTables = setOf("Bookmarks")
        ),
        "MySword Highlights" to ModuleSignature(
            requiredTables = setOf("Highlights")
        ),
        "MySword Settings" to ModuleSignature(
            requiredTables = setOf("settings"),
            identifyingColumns = mapOf("settings" to setOf("key", "value"))
        ),
        "MySword Verselist" to ModuleSignature(
            requiredTables = setOf("Verselist")
        ),
        "MySword Tags" to ModuleSignature(
            requiredTables = setOf("Tags")
        )
    )

    fun identify(result: SQLiteInspectionResult): String {
        val tableNames = result.tables.map { it.first }.toSet()
        val tableNamesLower = tableNames.map { it.lowercase() }.toSet()

        return SIGNATURES.entries.firstOrNull { (_, sig) ->
            val required = sig.requiredTables
            val matchedTables = required.filter { req ->
                req in tableNames || req.lowercase() in tableNamesLower
            }
            if (matchedTables.size != required.size) return@firstOrNull false

            // Verificar colunas identificadoras
            sig.identifyingColumns.all { (tableName, expectedCols) ->
                val actualTable = tableNames.firstOrNull { it.equals(tableName, ignoreCase = true) }
                    ?: return@all false
                val actualCols = result.schemas[actualTable]?.map { it.name.lowercase() }?.toSet() ?: return@all false
                expectedCols.all { col -> col.lowercase() in actualCols }
            }
        }?.key ?: "Tipo desconhecido — tabelas: ${tableNames.joinToString()}"
    }
}

🛡️ Passo 3 — Leitura Defensiva (Schema Parcialmente Conhecido)

/**
 * Leitura defensiva: funciona mesmo que colunas opcionais estejam ausentes.
 * Sempre verificar PRAGMA table_info antes de SELECT de colunas desconhecidas.
 */
fun readTableDefensively(db: SQLiteDatabase, tableName: String): List<Map<String, Any?>> {
    // Verificar quais colunas realmente existem
    val existingColumns = db.rawQuery("PRAGMA table_info('$tableName')", null).use { c ->
        buildSet { while (c.moveToNext()) add(c.getString(1)) }  // coluna 'name'
    }

    if (existingColumns.isEmpty()) return emptyList()

    // Construir SELECT apenas com colunas existentes
    val selectCols = existingColumns.joinToString(", ") { "\"$it\"" }
    return db.rawQuery("SELECT $selectCols FROM \"$tableName\"", null).use { cursor ->
        buildList {
            while (cursor.moveToNext()) {
                val row = mutableMapOf<String, Any?>()
                existingColumns.forEachIndexed { idx, colName ->
                    row[colName] = when (cursor.getType(idx)) {
                        Cursor.FIELD_TYPE_INTEGER -> cursor.getLong(idx)
                        Cursor.FIELD_TYPE_FLOAT   -> cursor.getDouble(idx)
                        Cursor.FIELD_TYPE_STRING  -> cursor.getString(idx)
                        Cursor.FIELD_TYPE_BLOB    -> cursor.getBlob(idx)
                        else -> null  // FIELD_TYPE_NULL
                    }
                }
                add(row)
            }
        }
    }
}

// Extensão para leitura segura de coluna opcional
fun Cursor.getStringOrNull(columnName: String): String? {
    val idx = getColumnIndex(columnName)
    return if (idx >= 0 && !isNull(idx)) getString(idx) else null
}

fun Cursor.getIntOrDefault(columnName: String, default: Int = 0): Int {
    val idx = getColumnIndex(columnName)
    return if (idx >= 0 && !isNull(idx)) getInt(idx) else default
}

💊 Passo 4 — Diagnóstico e Recovery de WAL Mode

/**
 * O MySword usa WAL (Write-Ahead Log) mode. Arquivos com .mybible-wal e .mybible-shm
 * podem estar em estado inconsistente se o app estava aberto quando o arquivo foi copiado.
 */
object WALDiagnostics {

    fun checkWALState(filePath: String): WALState {
        val walFile = File("$filePath-wal")
        val shmFile = File("$filePath-shm")
        return when {
            walFile.exists() && walFile.length() > 0 -> WALState.WAL_ACTIVE
            walFile.exists() && walFile.length() == 0L -> WALState.WAL_EMPTY
            shmFile.exists() -> WALState.SHM_ONLY
            else -> WALState.CLEAN
        }
    }

    /**
     * Forçar checkpoint do WAL (compactar WAL de volta para o arquivo principal).
     * Chamar SOMENTE se o arquivo não estiver sendo usado por outro processo.
     */
    fun forceWALCheckpoint(filePath: String): Boolean = runCatching {
        SQLiteDatabase.openDatabase(
            filePath, null,
            SQLiteDatabase.OPEN_READWRITE  // precisa de escrita para checkpoint
        ).use { db ->
            db.rawQuery("PRAGMA wal_checkpoint(FULL)", null).use { c ->
                c.moveToFirst()
                val busy = c.getInt(0)     // 0 = não busy
                val checkpointed = c.getInt(2)
                busy == 0 && checkpointed >= 0
            }
        }
    }.getOrDefault(false)

    /**
     * Converter de WAL para DELETE journal (mais seguro para arquivos read-only).
     * Use SOMENTE se você tem permissão de escrita e o arquivo não está em uso.
     */
    fun convertToDeleteMode(filePath: String): Boolean = runCatching {
        SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READWRITE).use { db ->
            db.execSQL("PRAGMA journal_mode=DELETE")
            true
        }
    }.getOrDefault(false)

    enum class WALState { CLEAN, WAL_ACTIVE, WAL_EMPTY, SHM_ONLY }
}

🗃️ Guia dos Arquivos mydata/ com Schemas Inferidos

bookmarks.mybible

-- Assinatura para identificação
-- Tabela obrigatória: Bookmarks
-- Colunas esperadas: Book, Chapter, Verse
-- Colunas opcionais: BibleMod, GroupName, Note, CreatedAt
SELECT name FROM sqlite_master WHERE name='Bookmarks'

highlight.mybible

-- Tabela obrigatória: Highlights
-- Colunas esperadas: Book, Chapter, Verse, Color
-- Colunas opcionais: ToVerse, CreatedAt
-- Valores de Color: red, orange, brown, yellowgreen, green, bluegreen,
--                  blue, violet, purple, pink, gray

settings.mybible

-- Estrutura key-value genérica
-- Tabela: settings
-- Colunas: key (TEXT PK), value (TEXT)
-- Atualizado frequentemente — não confiável para leitura durante uso do MySword
SELECT key, value FROM settings WHERE key LIKE '%current%' OR key LIKE '%bible%'

format.mybible

-- Estrutura desconhecida — preservar opaco
-- NÃO tentar parsear FormatData — formato proprietário não documentado
-- Estratégia: copiar bytes sem interpretação ao fazer backup/restore
SELECT COUNT(*) FROM Format  -- apenas para verificar se tem dados

default.xrefs.twm

-- Cross-references customizadas do usuário
-- Extensão .twm incomum mas internamente é SQLite
-- Assinatura provável: tabela de cross-references com book/chapter/verse
SELECT name FROM sqlite_master WHERE type='table'

🚨 Erros Comuns e Diagnóstico

🧪 Toolkit de Diagnóstico Rápido

// Uso: DumpSQLiteFile.dump(context, filePath) para diagnóstico completo em log
object DumpSQLiteFile {
    fun dump(filePath: String) {
        val walState = WALDiagnostics.checkWALState(filePath)
        android.util.Log.d("SQLiteForensics", "WAL state: $walState")

        val result = inspectSQLiteFile(filePath)
        android.util.Log.d("SQLiteForensics", result.summary())

        val moduleType = SQLiteModuleIdentifier.identify(result)
        android.util.Log.d("SQLiteForensics", "Identified as: $moduleType")

        // Amostra das primeiras 3 linhas de cada tabela
        SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READONLY).use { db ->
            result.tables.forEach { (tableName, _) ->
                val sample = db.rawQuery("SELECT * FROM '$tableName' LIMIT 3", null)
                android.util.Log.d("SQLiteForensics", "  $tableName sample: ${sample.count} cols, first row: ${if (sample.moveToFirst()) (0 until sample.columnCount).map { sample.getString(it) } else "empty"}")
                sample.close()
            }
        }
    }
}