备份数据库文件要简单得多,而且可能比尝试操作 CSV 数据更快。
还有一个优点是只需复制文件即可在 SQLite Tools 中使用数据库。
附:扩展名(例如 .csv .db)仅表明文件的用途。在示例中.whatever
已经用过。
这是一个示例/演示应用程序,它使用 Room 数据库和一个只有 2 列的表。当应用程序运行时,有一个具有 4 个 UI 组件的 Activity:-
房间实体(所有数据库代码,包括备份和恢复代码):-
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)
}
}
- 请注意,检查点函数似乎并未实际检查点(关闭数据库,这将对数据库进行检查点,导致 Room 框架/包装器出现问题)
从代码中可以看出,备份和恢复基本上只是复制文件。
活动的布局是:-
<?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>
活动(主要活动)是:-
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
第一次运行时,显示为:-
- Ideally the Restore button should not appear as there is, at this stage nothing to restore from
- 单击“恢复”按钮即可,因为恢复功能会检查是否有任何恢复文件,如果没有则立即返回
如果单击备份按钮,则会备份文件(即使没有数据(但文件中实际上会有一些数据,例如文件头和系统表))。
如果单击“备份”按钮后,单击“恢复”按钮(即使没有添加数据),则数据库实际上已恢复,并且应用程序将重新启动。
如果单击“添加数据”按钮,则会添加并显示 3 行数据(如果再次单击,则会添加另外 3 行数据,依此类推)。
由于先前单击了“备份”按钮。点击恢复按钮将恢复到没有数据的备份,并重新启动App,显示无数据。
添加2组数据并点击备份按钮
添加另外2组数据
点击恢复则数据恢复为仅6行数据