CODEWITHSUNDEEP
androiddatabasekotlinexportandroid-room
Faris Rizvanović
Faris Rizvanović

Reputation: 150

How to export Room database as a .db to Download file so I can use It later?

How can I export a Room database to a .db file so I can use it later? I would like to export it to Download folder in Android device storage. I already have setup export to .CSV but now i need to .db.

This is so I can reimport it if the user is switching devices and needs his data on the other device.

Is there a way just to use .CSV data or do i have to use .db?

Upvotes: 6

Views: 8428

Answers (4)

pyloolex
pyloolex

Reputation: 302

When you work in Android Studio, the database is stored in the device's memory, namely in /data/data/<name_of_the_app>/databases/<name_of_the_db>,

where <name_of_the_app> will look like com.android.thebestappever (you choose this name when creating an app);

<name_of_the_db> is also chosen by you when you instantiate the DB in your application.

You can access this DB file through the Device Explorer. You can open it by navigating to View -> Tool Windows -> Device Explorer (for example, in Android Studio Giraffe).

When you find your database file, you will also notice two more additional files next to it. If your DB is called thebestdb, there will be three files there: thebestdb, thebestdb-shm and thebestdb-wal.

shm and wal are auxiliary files for the write-ahead logging (you can read more about it at https://www.sqlite.org/wal.html).

The thing is, the main DB file might not contain all the latest data because it might be stored only in shm and wal and not yet synchronised with the main file.

In order to "commit" these changes and get the main DB file up to date, you need to execute PRAGMA wal_checkpoint. You can do it anywhere inside the code of your app.

If you instantiated your database with the databaseBuilder like so:

db = Room.databaseBuilder(
    applicationContext,
    AppDatabase::class.java, "thebestdb"
).allowMainThreadQueries().build()

then you have a RoomDatabase instance which you could use to do that synchronisation like this:

val cursor = db.query("PRAGMA wal_checkpoint", arrayOf())
cursor.moveToFirst()

Now your database file is up to date and you can save it by clicking on it in the Device Explorer. Do the right click -> Save As...

If you get an error, try to double click this file first and then try "Save As..." again.

Now, when you have this file saved, you can even put it into /app/src/main/assets/database/thebestdb.db of your codebase and use it in createFromAsset() if you want:

db = Room.databaseBuilder(
    applicationContext,
    AppDatabase::class.java, "thebestdb"
).createFromAsset("database/thebestdb.db")
    .allowMainThreadQueries().build()

Upvotes: 1

Eric
Eric

Reputation: 17536

  1. run the app

  2. perform actions in app to get the DB created, and populated with the data you want

  3. Android Studio > Device File Explorer

    Android Studio > Device File Explorer

  4. in the drop-down menu, select the device that has the DB that we want to export. for me, it is this emulator

    enter image description here

  5. navigate to data/data/your.app.package.name/databases

  6. right click on your DB file > save as

    enter image description here

Upvotes: 2

MikeT
MikeT

Reputation: 56958

Here's a CSV solution that will backup and restore ALL non-system/room/android tables BUT, it does not handle BLOBS (that is more complex) it may have space issues, it may have issues if Foreign Keys constraints are included (backing up the file is the suggested way as it is far less likely to encounter issues, other than the suggested restart of the App which is just an inconvenience).

Furthermore, for brevity and convenience, it

  • a) runs on the main thread
  • b) places the backups in the databases folder rather than elsewhere

It revolves around 4 functions, in this example within the @Database annotated class (TheDatabase). The functions are :-

getTableColumnnames (see example/demo)

  • this returns the list of columns in the provided table name using the provided SupportSQliteDatabase (both passed from the createAutoCSV function)
    • the list is return to the calling function.

createAutoCSV (see example/demo)

  • This function generates and returns the CSV file as a String from a StringBuffer that is progressively built.
  • The CSV, in addition to the actual data, has a start and end indicator (not really required as they are just skipped) and an indicator for each table that precedes the data for the table (see the restoreFromCSV function).
  • If the data includes a comma, the comma is replaced with {COMMA} (value can easily be changed) as it, like many values are constants.

An example of the CSV that String this returns is :-

{START}
{TABLE},theTable
1,DATA1{COMMA}_1659427794369
....
{TABLE},theOtherTable
1,OTHERDATA1_1659427794{COMMA},{COMMA}1659427794398{COMMA},     
....
{END}
  • note for testing a BLOB was used for the 3rd column in the theOtherTable (however BLOBS are not correctly handled)
  • as can be seen {START} and {END} indicate the start and end of the data (not really needed)
  • also, as can be seen, a table is indicated by {TABLE} followed by a comma and the tablename itself.

backupAsCSV() (see example demo)

  • This is just writes the data, deleting any previous version (the example uses just the single backup file, so a restore is just to the latest backup).

restoreFromCSV (see example/demo )

  • this, in a single transaction (so there should only be one write to the database file itself, albeit it perhaps numerous pages) for each table encountered (should be ALL non SQLite/Room/Android tables):-

    • deletes all existing rows, then
    • inserts each row in turn

  • Note there is no protection built in against a restore from a previous backup file to a changed schema (which may well result in failures when inserting (e.g. NOT NULL conflict), or subsequent failures)

  • Note neither is there is any order logic that considers relationships (ensuring that Foreign Key parents are populated before the children)

  • In short, as suggested, backing up the database file(s) is the way to go as all of the pitfalls of using a CSV file are irrelevant. The only drawback is that it is suggested that the App is restarted after a restore via the file(s).

Working Example/Demo

Here's the full code that:-

  • displays data from 2 tables (as per the example CSV above) in two ListViews (one for each table).
  • Allows the data to be backed up, via a button click (2 buttons) as:-
    • a copy of the database file(s), and or
    • a CSV file
  • Allows the data to be restored, via a button click (2 buttons) from:-
    • the file copy of the database
      • NOTE with a restart of the App to ensure the integrity of the database
    • and or, the CSV file
      • note that BLOBS are not correctly backed up/restored ( more complex handling would be required to handle BLOBS)

The files/classes:-

RoomEntities.kt (all the database code in one file for convenience/brevity)

/* Database level */
const val THEDATABASE_DATABASE_NAME = "thedatabase.whatever"
const val THEDATABASE_DATABASE_BACKUP_SUFFIX = "-bkp"
const val THEDATBASE_DATABASE_BACKUP_CSV_SUFFIX = ".csv"
const val SQLITE_WALFILE_SUFFIX = "-wal"
const val SQLITE_SHMFILE_SUFFIX = "-shm"
/* Tables */
const val THETABLE_TABLENAME = "theTable"
const val THEOTHERTABLE_TABLENAME = "theOtherTable"
/* Columns  TheTable */
const val THETABLE_ID_COLUMN = THETABLE_TABLENAME + "_id"
const val THETABLE_OTHER_COLUMN = THEOTHERTABLE_TABLENAME + "_other"
/* Columns TheOtherTable */
const val THEOTHERTABLE_ID_COLUMN = THEOTHERTABLE_TABLENAME + "_id"
const val THEOTHERTABLE_OTHER_COLUMN = THEOTHERTABLE_TABLENAME + "_other"
const val THEOTHERTABLE_ANOTHER_COLUMN = THEOTHERTABLE_TABLENAME + "_another"
const val THEOTHERTABLE_YETANOTHER_COLUMN = THEOTHERTABLE_TABLENAME + "_yetanother"
/*CSV level */
const val CSV_NEWLINE = "\n"
const val CSV_INDICATOR_START = "{START}"
const val CSV_INDICATOR_END = "{END}"
const val CSV_INDICATOR_TABLE = "{TABLE}"
const val CSV_COMMA_REPLACE = "{COMMA}"

@Entity(tableName = THETABLE_TABLENAME)
data class TheTable(
    @PrimaryKey @ColumnInfo(name = THETABLE_ID_COLUMN) var id: Long?=null,
    @ColumnInfo(name = THETABLE_OTHER_COLUMN) var other: String
)
@Entity(tableName = THEOTHERTABLE_TABLENAME)
data class TheOtherTable(
    @PrimaryKey @ColumnInfo(name = THEOTHERTABLE_ID_COLUMN) var id: Long? = null,
    @ColumnInfo(name = THEOTHERTABLE_OTHER_COLUMN) var other: String,
    @ColumnInfo(name = THEOTHERTABLE_ANOTHER_COLUMN) var another: String,
    @ColumnInfo(name = THEOTHERTABLE_YETANOTHER_COLUMN ) var yetanother: ByteArray = byteArrayOf(1,2,3,4,5,127,-127)
)
@Dao
interface AllDAO {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(theTable: TheTable): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(theOtherTable: TheOtherTable): Long
    @Query("SELECT * FROM $THETABLE_TABLENAME")
    fun getAllFromTheTable(): List<TheTable>
    @Query("SELECT * FROM $THEOTHERTABLE_TABLENAME")
    fun getAllFromTheOtherTable(): List<TheOtherTable>
}
@Database(entities = [TheTable::class,TheOtherTable::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDAO

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,
                    THEDATABASE_DATABASE_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
    /**
     * Backup the database
     */
    fun backupDatabase(context: Context): Int {
        var result = -99
        if (instance==null) return result
        val dbFile = context.getDatabasePath(THEDATABASE_DATABASE_NAME)
        val dbWalFile = File(dbFile.path + SQLITE_WALFILE_SUFFIX)
        val dbShmFile = File(dbFile.path + SQLITE_SHMFILE_SUFFIX)
        val bkpFile = File(dbFile.path + THEDATABASE_DATABASE_BACKUP_SUFFIX)
        val bkpWalFile = File(bkpFile.path + SQLITE_WALFILE_SUFFIX)
        val bkpShmFile = File(bkpFile.path + SQLITE_SHMFILE_SUFFIX)
        if (bkpFile.exists()) bkpFile.delete()
        if (bkpWalFile.exists()) bkpWalFile.delete()
        if (bkpShmFile.exists()) bkpShmFile.delete()
        try {
            dbFile.copyTo(bkpFile,true)
            if (dbWalFile.exists()) dbWalFile.copyTo(bkpWalFile,true)
            if (dbShmFile.exists()) dbShmFile.copyTo(bkpShmFile, true)
            result = 0
        } catch (e: IOException) {
            e.printStackTrace()
        }
        return result
    }
    /**
     *  Restore the database and then restart the App
     */
    fun restoreDatabase(context: Context,restart: Boolean = true) {
        if(!File(context.getDatabasePath(THEDATABASE_DATABASE_NAME).path + THEDATABASE_DATABASE_BACKUP_SUFFIX).exists()) {
            return
        }
        if (instance == null) return
        val dbpath = instance!!.getOpenHelper().readableDatabase.path
        val dbFile = File(dbpath)
        val dbWalFile = File(dbFile.path + SQLITE_WALFILE_SUFFIX)
        val dbShmFile = File(dbFile.path + SQLITE_SHMFILE_SUFFIX)
        val bkpFile = File(dbFile.path + THEDATABASE_DATABASE_BACKUP_SUFFIX)
        val bkpWalFile = File(bkpFile.path + SQLITE_WALFILE_SUFFIX)
        val bkpShmFile = File(bkpFile.path + SQLITE_SHMFILE_SUFFIX)
        dbShmFile.delete() /* ADDED for VACUUM BACKUP */
        dbWalFile.delete() /* ADDED for VACUUM BACKUP */
        try {
            bkpFile.copyTo(dbFile, true)
            if (bkpWalFile.exists()) bkpWalFile.copyTo(dbWalFile, true)
            if (bkpShmFile.exists()) bkpShmFile.copyTo(dbShmFile,true)
        } catch (e: IOException) {
            e.printStackTrace()
        }
        if (restart) {
            val i = context.packageManager.getLaunchIntentForPackage(context.packageName)
            i!!.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP)
            context.startActivity(i)
            System.exit(0)
        }
    }
    fun backupAsCSV() {
        val csvFile = File(instance!!.getOpenHelper().writableDatabase.path + THEDATBASE_DATABASE_BACKUP_CSV_SUFFIX)
        csvFile.delete()
        csvFile.writeText(createAutoCSV())
    }
    fun restoreFromCSV() {
        var db = instance!!.getOpenHelper().writableDatabase
        db.beginTransaction() /* want to run in a single transaction */
        var currentTableName = ""
        val TAG = "CSVRESTORE"
        val csvFile = File(instance!!.getOpenHelper().writableDatabase.path + THEDATBASE_DATABASE_BACKUP_CSV_SUFFIX)
        csvFile.forEachLine {
            /* if Start header then skip */
            if (it.equals(CSV_INDICATOR_START)) {
                Log.d(TAG,"START INDICATED")
            } else {
                /* if  end header then skip */
                if (it.equals(CSV_INDICATOR_END)) {
                    Log.d(TAG, "END INDICATED")
                } else {
                    /* If table header then delete all rows for the table */
                    if (it.substring(0, CSV_INDICATOR_TABLE.length).equals(CSV_INDICATOR_TABLE)) {
                        currentTableName = it.substring(CSV_INDICATOR_TABLE.length + 1)
                        Log.d("CSVRESTORE","NEW TABLE INDICATED. TableName is $currentTableName")
                        db.execSQL("DELETE FROM $currentTableName")
                        Log.d(TAG,"$currentTableName cleared of data")
                    } else {
                        /* Otherwise it is actual data for a row so insert the data */
                        /* Note replacing the comma replacement string with a comma */
                        Log.d(TAG,"Actual Data is $it")
                        /* split the CSV into individual elements (column data) */
                        val splitdata = it.split(",")
                        var resolvedData: ArrayList<String> = arrayListOf()
                        val sql = StringBuilder().append("INSERT INTO $currentTableName VALUES(")
                        var afterFirstData = false
                        /* For each split element (column) */
                        /* 1. add a ? for parameter binding */
                        /* 2. add the resolved (commas reinstated) values for the parameters */
                        for (s in splitdata) {
                            if (afterFirstData) sql.append(",")
                            afterFirstData = true
                            sql.append("?")
                            resolvedData.add(s.replace(CSV_COMMA_REPLACE,","))
                        }
                        sql.append(");")
                        db.execSQL(sql.toString(),resolvedData.toArray())
                    }
                }
            }
        }
        db.setTransactionSuccessful()
        db.endTransaction()
    }

    private fun createAutoCSV(): String {
        val replaceCommaInData = CSV_COMMA_REPLACE /* commas in the data will be replaced by this */
        val rv = StringBuilder().append(CSV_INDICATOR_START)
        val sql = StringBuilder()
        var afterFirstTable = false
        var afterFirstColumn = false
        var afterFirstRow = false
        val suppDb = instance!!.getOpenHelper().writableDatabase
        var currentTableName: String = ""
        val csr = instance!!.query(
            "SELECT name FROM sqlite_master " +
                    "WHERE type='table' " +
                    "AND name NOT LIKE('sqlite_%') " +
                    "AND name NOT LIKE('room_%') " +
                    "AND name NOT LIKE('android_%')",
            null
        )
        while (csr.moveToNext()) {
            sql.clear()
            sql.append("SELECT ")
            currentTableName = csr.getString(0)
            //if (afterFirstTable) rv.append("$CSV_NEWLINE")
            afterFirstTable = true
            afterFirstColumn = false
            rv.append(CSV_NEWLINE).append("$CSV_INDICATOR_TABLE,$currentTableName")
            for (columnName in getTableColumnNames(currentTableName,suppDb)) {
                if (afterFirstColumn) sql.append("||','||")
                afterFirstColumn = true
                sql.append("replace(`$columnName`,',','$replaceCommaInData')")
            }
            sql.append(" FROM `${currentTableName}`")
            val csr2 = instance!!.query(sql.toString(),null)
            afterFirstRow = false
            while (csr2.moveToNext()) {
                //if (!afterFirstRow) rv.append("$CSV_NEWLINE")
                afterFirstRow = true
                rv.append(CSV_NEWLINE).append(csr2.getString(0))
            }
        }
        rv.append(CSV_NEWLINE).append("$CSV_INDICATOR_END")
        return rv.toString()
    }
    private fun getTableColumnNames(tableName: String, suppDB: SupportSQLiteDatabase): List<String> {
        val rv = arrayListOf<String>()
        val csr = suppDB.query("SELECT name FROM pragma_table_info('${tableName}')",null)
        while (csr.moveToNext()) {
            rv.add(csr.getString(0))
        }
        csr.close()
        return rv.toList()
    }
}

MainActivity (layout should be easy enough to build- see the lateinit's)

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    lateinit var backupButton: Button
    lateinit var csvBackupButton: Button
    lateinit var restoreButton: Button
    lateinit var restoreFromCSVButton: Button
    lateinit var addDataButton: Button
    lateinit var listView: ListView
    lateinit var listViewOther: ListView
    var listAdapter: SimpleCursorAdapter? = null
    var listAdapterOther: SimpleCursorAdapter? = null

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        backupButton = this.findViewById(R.id.backup)
        csvBackupButton = this.findViewById(R.id.csvBackup)
        restoreButton = this.findViewById(R.id.restore)
        restoreFromCSVButton = this.findViewById(R.id.restoreFromCSV)
        addDataButton = this.findViewById(R.id.addData)
        listView = this.findViewById(R.id.datalist)
        listViewOther = this.findViewById(R.id.datalistOther)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()
        setButtonListeners()
        setOrRefreshListView()
    }

    fun setButtonListeners() {
        addDataButton.setOnClickListener {
            dao.insert(TheTable(other = "DATA1,_${System.currentTimeMillis()}"))
            dao.insert(TheTable(other = "DATA2_,${System.currentTimeMillis()}"))
            dao.insert(TheTable(other = ",DATA3_${System.currentTimeMillis()}"))
            /*******************************************************************************************************/
            /* Note for CSV backup/restore BLOBS are NOT handled properly (that would entail even more complexity) */
            /*******************************************************************************************************/
            dao.insert(
                TheOtherTable(
                    other = "OTHERDATA1_${System.currentTimeMillis() / 1000},",
                    another = ",${System.currentTimeMillis()},",
                    yetanother = byteArrayOf(0,0,0,0,0)
                )
            )
            dao.insert(
                TheOtherTable(
                    other = "OTHERDATA2_${System.currentTimeMillis() / 1000},",
                    another = "${System.currentTimeMillis()}")
            )
            dao.insert(
                TheOtherTable(
                    other = "OTHER,DATA3_${System.currentTimeMillis() / 1000}",
                    another = "${System.currentTimeMillis()}",
                    yetanother = byteArrayOf(0x00,0x10,0x20,0x30,0x40,0x50,0x60,0x70))
            )
            dao.insert(
                TheOtherTable(other = "OTHERDATA,4_${System.currentTimeMillis() / 1000}",
                    another = "${System.currentTimeMillis()}")
            )
            setOrRefreshListView()
        }
        backupButton.setOnClickListener {
            db.backupDatabase(this)
        }
        csvBackupButton.setOnClickListener {
            db.backupAsCSV()
        }
        restoreButton.setOnClickListener {
            db.restoreDatabase(this)
            setOrRefreshListView()
        }
        restoreFromCSVButton.setOnClickListener {
            db.restoreFromCSV()
            setOrRefreshListView()
        }
    }

    /* initialise or refresh the 2 ListViews */
    private fun setOrRefreshListView() {
        var csr = db.getOpenHelper().writableDatabase.query("SELECT $THETABLE_ID_COLUMN AS ${BaseColumns._ID}, $THETABLE_OTHER_COLUMN FROM $THETABLE_TABLENAME")
        if (listAdapter==null) {
            listAdapter = SimpleCursorAdapter(
                this,
                android.R.layout.simple_list_item_2,
                csr,
                arrayOf("_id", THETABLE_OTHER_COLUMN),
                intArrayOf(android.R.id.text1,android.R.id.text2), 0
            )
            listView.adapter = listAdapter
        } else {
            listAdapter!!.swapCursor(csr)
        }
        var csr2 = db.getOpenHelper().writableDatabase.query("SELECT $THEOTHERTABLE_ID_COLUMN AS ${BaseColumns._ID}, $THEOTHERTABLE_OTHER_COLUMN, $THEOTHERTABLE_ANOTHER_COLUMN FROM $THEOTHERTABLE_TABLENAME;")
        if (listAdapterOther==null) {
            listAdapterOther = SimpleCursorAdapter(
                this,
                android.R.layout.simple_list_item_2,
                csr2,
                arrayOf(THEOTHERTABLE_OTHER_COLUMN, THEOTHERTABLE_ANOTHER_COLUMN),
                intArrayOf(android.R.id.text1,android.R.id.text2),
                0
            )
            listViewOther.adapter = listAdapterOther
        } else {
            listAdapterOther!!.swapCursor(csr2)
        }
    }
}

When run after clicking ADD DATA once (3 rows in 1st table and 4 in 2nd) and then clicking both the BACKUP and CSV BACKUP buttons.

enter image description here

Then after clicking ADD DATA button again to add some data after the backups.

enter image description here

Then clicking the CSV RESTORE button

enter image description here

Again ADD DATA is clicked one or more times and then RESTORE button is clicked (restore from the File backup)

enter image description here

  • so back to the original data, noting that the App is restarted.

Upvotes: 1

MikeT
MikeT

Reputation: 56958

It would be far simpler to backup the database file(s) and probably also faster than trying to manipulate CSV data.

There is also the advantage that the database can then be used in SQLite Tools simply by copying the files.

P.S. The extension ( e.g. .csv .db) is only an indication of the file's use. In the example .whatever has been used.

  • An issue is that after restoring the database the can be problems with accessing the restored data.

    • A way to circumvent such issue would be to include restarting the App automatically after the restore.

  • Another issue is that Room uses Write-Ahead Logging (WAL) by default and it doesn't appear to be that easy to checkpoint the database.

    • as a get-around instead of backing up a single file, 3 files can be backed up and restored (the wal file and the shm file) see https://www.sqlite.org/wal.html

Here's an example/demo App that uses a Room database with a single table with just 2 columns. When the App is run there is a single activity that has 4 UI components:-

  • A button to allow the database to be backed up.

  • A button to allow the database to be restored.

  • A button that allows some data to be added (3 rows).

  • A List of the current data.

  • Note for simplicity and brevity the all work is undertaken on the main thread

  • Also for simplicity and brevity, all the database code is in a single file RoomEntities

  • Also for simplicity the backup is made to the same directory data/data/the_package_name/databases

RoomEntities (all the database code, including the backup and restore code) :-

const val THETABLE_TABLENAME = "theTable"
const val THETABLE_ID_COLUMN = THETABLE_TABLENAME + "_id"
const val TheTABLE_OTHER_COLUMN = THETABLE_TABLENAME + "_other"
const val THEDATABASE_DATABASE_NAME = "thedatabase.whatever"
const val THEDATABASE_DATABASE_BACKUP_SUFFIX = "-bkp"
const val SQLITE_WALFILE_SUFFIX = "-wal"
const val SQLITE_SHMFILE_SUFFIX = "-shm"
@Entity(tableName = THETABLE_TABLENAME)
data class TheTable(
    @PrimaryKey @ColumnInfo(name = THETABLE_ID_COLUMN) var id: Long?=null,
    @ColumnInfo(name = TheTABLE_OTHER_COLUMN) var other: String
)
@Dao
interface AllDAO {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(theTable: TheTable): Long
    @Query("SELECT * FROM $THETABLE_TABLENAME")
    fun getAllFromTheTable(): List<TheTable>
}
@Database(entities = [TheTable::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDAO

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,
                    THEDATABASE_DATABASE_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }

    /**
     * Backup the database
     */
    fun backupDatabase(context: Context): Int {
        var result = -99
        if (instance==null) return result

        val dbFile = context.getDatabasePath(THEDATABASE_DATABASE_NAME)
        val dbWalFile = File(dbFile.path + SQLITE_WALFILE_SUFFIX)
        val dbShmFile = File(dbFile.path + SQLITE_SHMFILE_SUFFIX)
        val bkpFile = File(dbFile.path + THEDATABASE_DATABASE_BACKUP_SUFFIX)
        val bkpWalFile = File(bkpFile.path + SQLITE_WALFILE_SUFFIX)
        val bkpShmFile = File(bkpFile.path + SQLITE_SHMFILE_SUFFIX)
        if (bkpFile.exists()) bkpFile.delete()
        if (bkpWalFile.exists()) bkpWalFile.delete()
        if (bkpShmFile.exists()) bkpShmFile.delete()
        checkpoint()
        try {
            dbFile.copyTo(bkpFile,true)
            if (dbWalFile.exists()) dbWalFile.copyTo(bkpWalFile,true)
            if (dbShmFile.exists()) dbShmFile.copyTo(bkpShmFile, true)
            result = 0
        } catch (e: IOException) {
            e.printStackTrace()
        }
        return result
    }

    /**
     *  Restore the database and then restart the App
     */
    fun restoreDatabase(context: Context,restart: Boolean = true) {
        if(!File(context.getDatabasePath(THEDATABASE_DATABASE_NAME).path + THEDATABASE_DATABASE_BACKUP_SUFFIX).exists()) {
            return
        }
        if (instance == null) return
        val dbpath = instance!!.getOpenHelper().readableDatabase.path
        val dbFile = File(dbpath)
        val dbWalFile = File(dbFile.path + SQLITE_WALFILE_SUFFIX)
        val dbShmFile = File(dbFile.path + SQLITE_SHMFILE_SUFFIX)
        val bkpFile = File(dbFile.path + THEDATABASE_DATABASE_BACKUP_SUFFIX)
        val bkpWalFile = File(bkpFile.path + SQLITE_WALFILE_SUFFIX)
        val bkpShmFile = File(bkpFile.path + SQLITE_SHMFILE_SUFFIX)
        try {
            bkpFile.copyTo(dbFile, true)
            if (bkpWalFile.exists()) bkpWalFile.copyTo(dbWalFile, true)
            if (bkpShmFile.exists()) bkpShmFile.copyTo(dbShmFile,true)
            checkpoint()
        } catch (e: IOException) {
            e.printStackTrace()
        }
        if (restart) {
            val i = context.packageManager.getLaunchIntentForPackage(context.packageName)
            i!!.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP)
            context.startActivity(i)
            System.exit(0)
        }
    }
    private fun checkpoint() {
        var db = this.getOpenHelper().writableDatabase
        db.query("PRAGMA wal_checkpoint(FULL);",null)
        db.query("PRAGMA wal_checkpoint(TRUNCATE);",null)
    }
}
  • Note the checkpoint function doesn't appear to actually checkpoint (closing the Database, which would checkpoint the database, results in issues around the Room framework/wrapper)

As can be seen from the code, the backup and restore is basically just copying files.

The Activity's layout is:-

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Hello World!"
        />
    <Button
        android:id="@+id/backup"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="BACKUP"
        >
    </Button>
    <Button
        android:id="@+id/restore"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="RESTORE"
        >
    </Button>
    <Button
        android:id="@+id/addData"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="ADD DATA"
        >
    </Button>
    <ListView
        android:id="@+id/datalist"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        >
    </ListView>
</LinearLayout>

The Activity (MainActivity) is:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    lateinit var backupButton: Button
    lateinit var restoreButton: Button
    lateinit var addDataButton: Button
    lateinit var listView: ListView
    var listAdapter: SimpleCursorAdapter? = null

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        backupButton = this.findViewById(R.id.backup)
        restoreButton = this.findViewById(R.id.restore)
        addDataButton = this.findViewById(R.id.addData)
        listView = this.findViewById(R.id.datalist)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()
        setButtonListeners()
        setOrRefreshListView()
    }

    fun setButtonListeners() {
        addDataButton.setOnClickListener {
            dao.insert(TheTable(other = "DATA_${System.currentTimeMillis()}"))
            dao.insert(TheTable(other = "DATA_${System.currentTimeMillis()}"))
            dao.insert(TheTable(other = "DATA_${System.currentTimeMillis()}"))
            setOrRefreshListView()
        }
        backupButton.setOnClickListener {
            db.backupDatabase(this)
        }
        restoreButton.setOnClickListener {
            db.restoreDatabase(this)
        }

    }

    fun setOrRefreshListView() {
        var csr = db.getOpenHelper().writableDatabase.query("SELECT $THETABLE_ID_COLUMN AS _id, $TheTABLE_OTHER_COLUMN FROM $THETABLE_TABLENAME")
        if (listAdapter==null) {
            listAdapter = SimpleCursorAdapter(
                this,
                android.R.layout.simple_list_item_2,
                csr,
                arrayOf("_id", TheTABLE_OTHER_COLUMN),
                intArrayOf(android.R.id.text1,android.R.id.text2), 0
            )
            listView.adapter = listAdapter
        } else {
            listAdapter!!.swapCursor(csr)
        }
    }
}

Results

When first Run then the Appears as:-

enter image description here

  • Ideally the Restore button should not appear as there is, at this stage nothing to restore from
    • clicking the Restore button is fine as the restore function checks to see if there are any restore files and if not returns immediately

If the backup button is clicked then the files will be backed up (even if there is no data (there will actually be some data in the file though, such as the file header and the system tables)).

If after clicking the Backup button, the restore button is clicked (even if no data has been added) then the database is actually restored and the App is restarted.

If the AddData button is clicked the 3 rows of data are added and displayed (another 3 added if clicked again and so on).

enter image description here

As the Backup buttons was previously clicked. Clicking the restore button will restore to the backup that has no data and restart the App displaying no data.

enter image description here

Adding 2 sets of data and also clicking the Backup button

enter image description here

Adding another 2 sets of data

enter image description here

Clicking restore then the data is restored to just the 6 rows of data

enter image description here

Upvotes: 7

Related Questions