Access to byte stream of sqlite byte array

There are two ways to store sqlite byte arrays:

  1. It's easy to understand how to store byte array in string type

Base64.encodeToString(rawData, Base64.DEFAULT) byte[]Convert to string
Base64.decode(printData.getBytes(), Base64.DEFAULT) string Convert to byte[]

  1. The data type of the specified field is blob

BLOB - The value is a blob of data, stored exactly as it was input

It should be noted that the better way to design mobile database is to give priority to readability

In actual projects, java objects are serialized and saved. The first reaction is to serialize them into a byte stream, and then save the byte stream. During debugging, it was found that the database was completely unreadable, which was a great delay. Finally, I decided to convert the object into a json string and save the string much better.

Design and implementation of an android sequential installation APK Manager


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, "Failed to add an installation task, installed version $installedVid;pdk$pkg")
            return
        }
        LogUtil.d(TAG, "Add an installation task $pkg")
        var alreadyInTask = false
        // Modify the record if it already exists, otherwise add a record
        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()

        // Insert into task queue
        if (alreadyInTask) {
            LogUtil.e(TAG, "Installation task already exists, the same version should only be installed once!!! Adjust the installation status and 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, "Never installed, $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, "Failed to detect an installation task installed version $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, "Detect an installation task $b,$pkg")
        return b
    }


    fun getInstalledTime(pkgName: String?): String? {
        if (TextUtils.isEmpty(pkgName)) {
            LogUtil.d(TAG, "Failed to get installation time $pkgName")
            return null
        }

        var installTime: String? = null
        // Query the version number of the installed 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, "Get installation time" + 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) {
        // Migrate data from old database to new database
        // It's the first version in itself. There's no old version
    }

    companion object {
        // installResult 0 is not installed, 1 installation succeeded, 2 installation failed
        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)"
    }
}

Tags: SQL Database SQLite Mobile

Posted on Sun, 31 May 2020 01:45:55 -0400 by lutzlutz896