sqlite 字节数组 字节流的存取

两种思路实现sqlite字节数组的存放操作：

1. 将字节数组转为string类型存放，这种方式比较容易理解

Base64.encodeToString(rawData, Base64.DEFAULT) byte[]转为string
Base64.decode(printData.getBytes(), Base64.DEFAULT) string转为byte[]

2. 指定字段的数据类型为blob

BLOB - The value is a blob of data, stored exactly as it was input.（BLOB数据不做任何转换，以输入形式存储。）

需要注意的是，移动端数据库的设计，比较好的实现方式是优先可读性

在实际的项目中遇到的是java对象的序列化和保存，第一反应就是先序列化成字节流，然后保存该字节流。调试过程中发现数据库完全不可读，很是耽误事。最后决定将对象转成json字符串，保存字符串就好多了。

一个android顺序安装APK管理器的设计与实现

class PackageInstallDatabaseHelper(context: Context?, name: String,
                                   factory: SQLiteDatabase.CursorFactory?,
                                   version: Int) :
        SQLiteOpenHelper(context, name, factory, version) {

    private lateinit var database: SQLiteDatabase

    fun initHelp() {
        database = writableDatabase
    }

    fun createOneInstallTask(pkg: PackageInformation?) {
        if (pkg == null) {
            return
        }
        val installedVid = PackageUtil.getVersion(pkg.packageName)
        if (TextUtils.isEmpty(pkg.packageName)
                || pkg.version < installedVid
                || TextUtils.isEmpty(pkg.filePath)) {
            installedListener?.installedResult(pkg, false)
            LogUtil.d(TAG, "添加一个安装任务失败，已安装版本$installedVid;pdk$pkg")
            return
        }
        LogUtil.d(TAG, "添加一个安装任务$pkg")
        var alreadyInTask = false
        // 如果已经存在记录，就修改记录，否则添加一条记录
        val sql = "select * from package_version where packageName = ?"
        val cursor = database.rawQuery(sql, arrayOf(pkg.packageName))
        while (cursor.moveToNext()) {
            val vid = cursor.getInt(cursor.getColumnIndex("vid"))
            if (vid == pkg.version) {
                alreadyInTask = true
            }
        }
        cursor?.close()

        // 插入任务队列中
        if (alreadyInTask) {
            LogUtil.e(TAG, "安装任务已存在，同一个版本应只安装一次！！！调整安装状态与path，$pkg")
            val sql = "update package_version set path = ?, installResult = 0 where packageName = ? and vid = ?"
            database.execSQL(sql, arrayOf(pkg.filePath, pkg.packageName, pkg.version.toString()))
        } else {
            LogUtil.d(TAG, "从未安装过，$pkg")
            val sql = "insert into package_version(packageName, vid, path) values(?,?,?)"
            database.execSQL(sql, arrayOf(pkg.packageName, pkg.version.toString(), pkg.filePath))
        }
    }

    fun isInstallingOrWaiting(pkg: PackageInformation?): Boolean {
        if (pkg == null)
            return false
        val installedVid = PackageUtil.getVersion(pkg.packageName)
        if (TextUtils.isEmpty(pkg.packageName) || pkg.version < installedVid) {
            LogUtil.d(TAG, "检测一个安装任务失败已安装版本$installedVid;pdk$pkg")
            return false
        }
        val sql = "select * from package_version where packageName = ? and vid = ? and installResult = 0"
        val cursor = database.rawQuery(sql, arrayOf(pkg.packageName, pkg.version.toString()))
        val b = cursor.moveToNext()
        cursor?.close()

        LogUtil.d(TAG, "检测一个安装任务$b,$pkg")
        return b
    }


    fun getInstalledTime(pkgName: String?): String? {
        if (TextUtils.isEmpty(pkgName)) {
            LogUtil.d(TAG, "获取安装时间失败$pkgName")
            return null
        }

        var installTime: String? = null
        // 查询本机已安装的apk对应的版本号
        val vid = PackageUtil.getVersion(pkgName)
        val sql = "select * from package_version where packageName = ? and vid = ? and installResult = 1 limit 1"
        val cursor = database.rawQuery(sql, arrayOf(pkgName, vid.toString()))
        while (cursor.moveToNext()) {
            installTime = cursor.getString(cursor.getColumnIndex("installTime"))
        }
        cursor?.close()

        LogUtil.d(TAG, "获取安装时间" + installTime)
        return installTime
    }

    private fun getTaskFromQueue(): PackageInformation? {
        var result: PackageInformation? = null
        val sql = "select * from package_version where installResult = 0 limit 1"
        val cursor = database.rawQuery(sql, null)
        while (cursor.moveToNext()) {
            val packageName = cursor.getString(cursor.getColumnIndex("packageName"));
            val vid = cursor.getInt(cursor.getColumnIndex("vid"));
            val path = cursor.getString(cursor.getColumnIndex("path"))
            result = PackageInformation(packageName, vid, path)
        }
        cursor?.close()
        return result
    }

    private fun saveLogTaskExecuted(pck: PackageInformation) {
        var installResult = "2"
        val vid = PackageUtil.getVersion(pck.packageName)
        if (vid >= pck.version) {
            installResult = "1"
        }

        val installTime = (System.currentTimeMillis() / 1000).toString()
        val sql = "update package_version set installResult = ?, installTime = ? where packageName = ? and vid = ?"
        database.execSQL(sql, arrayOf(installResult, installTime, pck.packageName, pck.version.toString()))
    }

    override fun onOpen(db: SQLiteDatabase?) {
        db?.execSQL(CREATE_TABLE_STR)
        super.onOpen(db)
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(CREATE_TABLE_STR)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        // 把旧数据库里的数据迁移到新数据库里
        // 本身就是第一版，不存在旧版本
    }

    companion object {
        // installResult 0未安装，1安装成功，2安装失败
        private val CREATE_TABLE_STR = // "DROP TABLE IF EXISTS package_version;" +
                "CREATE TABLE IF NOT EXISTS package_version(" +
                        "id integer primary key autoincrement, " +
                        "packageName text, " +
                        "vid integer integer default 0, " +
                        "path text," +
                        "installResult integer default 0, " +
                        "installTime text)"
    }
}