android - 如何仅在插入一些新记录后删除 Android SQLite 数据库上的先前记录
问题描述
我正在构建一个 Android 应用程序，它通过 HTML 页面的内容进行抓取（学校的网站没有给我们官方应用程序，也没有公共 API）。
我无法确定数据是否绝对是新的，所以目前所有获取数据并将其插入数据库中都是在 AsyncTask 中完成的，该任务基本上是这样的：
- recreateDatabase() // 删除所有表并重新创建它们。
- insertSubjectList() //遍历列表中的所有项目并将它们添加到数据库中
然后在 AsyncTask 结果中：
- 刷新当前片段（）；
问题是：如果用户进入应用程序中的某个区域，该区域在 recreateDatabase() 和 insertSubjectList() 之间从数据库中读取数据，则数据库中可能只有很少或根本没有数据，因为它当前正在重新填充。
它不会使应用程序崩溃左右，但这是一种不良行为。我的问题是：是否可以告诉 SQLite 仅在新数据可用于查询后清理旧数据？旧的和新的不能同时读取，因为它们的信息可能会崩溃。
希望有人能帮助我度过这个难关！
解决方案
听起来不是：-
- recreateDatabase() // 删除所有表并重新创建它们。
- insertSubjectList() //遍历列表中的所有项目并将它们添加到数据库中
你要
- insertSubjectList() 到过渡表中（与对应的名称不同的表，例如新创建的表）
- 将原始表重命名为另一个表名，例如使用
ALTER TABLE original TO original_renamed - 将过渡表重命名为对应的（原始）表名。
- 删除旧表。
因此，在加载数据时，原始表仍然存在，然后仅在相对较短的时间内没有数据可用。
这也可以在事务中完成，这可能会提高性能。
因此，这将回答是
是否可以告诉 SQLite 仅在新数据可用于查询后清理旧数据？
至于
旧的和新的不能同时读取，因为它们的信息可能会崩溃。
他们不会被同时阅读。
例如考虑以下演示：-
DROP TABLE IF EXISTS master;
CREATE TABLE IF NOT EXISTS master (mydata TEXT);
-- Load the original table with some data
WITH RECURSIVE cte1(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte1 where x < 1000)
INSERT INTO master SELECT * FROM cte1;
SELECT * FROM master LIMIT 10;
BEGIN TRANSACTION;
-- Load the new data (original table still available)
CREATE TABLE IF NOT EXISTS transitional (mydata TEXT);
WITH RECURSIVE cte1(x) AS (SELECT 2000 UNION ALL SELECT x+1 FROM cte1 where x < 3000)
INSERT INTO transitional SELECT * FROM cte1;
SELECT * FROM transitional LIMIT 10;
-- Switch the tables - original not available
ALTER TABLE master RENAME TO master_old;
ALTER TABLE transitional RENAME TO master;
-- original now available
-- Clean-up old
DROP TABLE master_old;
END TRANSACTION;
SELECT * FROM master LIMIT 10;
安卓演示
以下是与上述类似的工作 Android 示例的代码。
调用活动和数据库助手是两段代码
MainActivity.java
public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// Instantiate the helper noting that data will be loaded
mDBHlpr = new DBHelper(this);
// Get the current data
Cursor csr = mDBHlpr.getFirst10Rows();
// Write the current data to the log
DatabaseUtils.dumpCursor(csr);
//<<<<<<<<<< do the switch >>>>>>>>>>
mDBHlpr.switchData(DBHelper.TABLE_MASTER);
//Again get the data and write to the log
csr = mDBHlpr.getFirst10Rows();
DatabaseUtils.dumpCursor(csr);
//Clean up
csr.close();
}
}
DBHelper.java
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
private static final int DBVERSION = 1;
public static final String TABLE_MASTER = "master";
public static final String COl_MASTER_ID = BaseColumns._ID;
public static final String COL_MASTER_MYDATA = "mydata";
SQLiteDatabase mDB;
//Construct the helper NOTE will create the db if needed
public DBHelper(Context context) {
super(context, DBNAME,null, DBVERSION);
mDB = this.getWritableDatabase(); // force db open
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(create_table_sql(TABLE_MASTER));
mDB = db;
addLotsOfData(TABLE_MASTER,1000,1); // Load some data
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
// Generate the table SQL according to the table name passed
private String create_table_sql(String table) {
return "CREATE TABLE IF NOT EXISTS " + table + "(" +
COl_MASTER_ID + " INTEGER PRIMARY KEY," +
COL_MASTER_MYDATA + " TEXT" +
")";
}
// Switch the original table for a newly created version
public void switchData(String table) {
String transitional_table = "transitional";
String renamed_master = table + "_old";
boolean already_in_transaction = mDB.inTransaction();
if (!already_in_transaction) {
mDB.beginTransaction();
}
//<<<<<<<<<< create and load of new data could be done elsewhere
mDB.execSQL(create_table_sql(transitional_table));
addLotsOfData(transitional_table,1000,3001); //<<<<<<<< load new data here
//>>>>>>>>>>
mDB.execSQL("ALTER TABLE " + TABLE_MASTER + " RENAME TO " + renamed_master);
mDB.execSQL("ALTER TABLE " + transitional_table + " RENAME TO " + TABLE_MASTER);
mDB.execSQL("DROP TABLE IF EXISTS " + renamed_master);
if (!already_in_transaction) {
mDB.setTransactionSuccessful();
mDB.endTransaction();
}
}
// Add some data
private void addLotsOfData(String table, int rows, int value_offset) {
boolean already_in_transaction = mDB.inTransaction();
if (!already_in_transaction) {
mDB.beginTransaction();
}
for (int i = 0; i < rows; i++) {
addRow(table,String.valueOf(value_offset + i));
}
if (!already_in_transaction) {
mDB.setTransactionSuccessful();
mDB.endTransaction();
}
}
// Insert a single row
public long addRow(String table, String value) {
ContentValues cv = new ContentValues();
cv.put(COL_MASTER_MYDATA,value);
return mDB.insert(table,null,cv);
}
public Cursor getFirst10Rows() {
return mDB.query(TABLE_MASTER,null,null,null,null,null,null,"10");
}
}
- 请注意，上面的设计只运行一次，对于后续运行，之前和之后的数据是相同的。
结果
日志显示（如预期）： -
04-26 08:42:43.556I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@11697ce
04-26 08:42:43.557I/System.out: 0 {
04-26 08:42:43.557I/System.out: _id=1
04-26 08:42:43.557I/System.out: mydata=1
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 1 {
04-26 08:42:43.557I/System.out: _id=2
04-26 08:42:43.557I/System.out: mydata=2
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 2 {
04-26 08:42:43.557I/System.out: _id=3
04-26 08:42:43.557I/System.out: mydata=3
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 3 {
04-26 08:42:43.557I/System.out: _id=4
04-26 08:42:43.557I/System.out: mydata=4
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 4 {
04-26 08:42:43.557I/System.out: _id=5
04-26 08:42:43.557I/System.out: mydata=5
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 5 {
04-26 08:42:43.557I/System.out: _id=6
04-26 08:42:43.557I/System.out: mydata=6
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 6 {
04-26 08:42:43.557I/System.out: _id=7
04-26 08:42:43.557I/System.out: mydata=7
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 7 {
04-26 08:42:43.557I/System.out: _id=8
04-26 08:42:43.557I/System.out: mydata=8
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 8 {
04-26 08:42:43.557I/System.out: _id=9
04-26 08:42:43.557I/System.out: mydata=9
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: 9 {
04-26 08:42:43.557I/System.out: _id=10
04-26 08:42:43.557I/System.out: mydata=10
04-26 08:42:43.557I/System.out: }
04-26 08:42:43.557I/System.out: <<<<<
04-26 08:42:43.652I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@3396ef
04-26 08:42:43.652I/System.out: 0 {
04-26 08:42:43.652I/System.out: _id=1
04-26 08:42:43.652I/System.out: mydata=3001
04-26 08:42:43.652I/System.out: }
04-26 08:42:43.652I/System.out: 1 {
04-26 08:42:43.652I/System.out: _id=2
04-26 08:42:43.652I/System.out: mydata=3002
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 2 {
04-26 08:42:43.653I/System.out: _id=3
04-26 08:42:43.653I/System.out: mydata=3003
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 3 {
04-26 08:42:43.653I/System.out: _id=4
04-26 08:42:43.653I/System.out: mydata=3004
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 4 {
04-26 08:42:43.653I/System.out: _id=5
04-26 08:42:43.653I/System.out: mydata=3005
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 5 {
04-26 08:42:43.653I/System.out: _id=6
04-26 08:42:43.653I/System.out: mydata=3006
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 6 {
04-26 08:42:43.653I/System.out: _id=7
04-26 08:42:43.653I/System.out: mydata=3007
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 7 {
04-26 08:42:43.653I/System.out: _id=8
04-26 08:42:43.653I/System.out: mydata=3008
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.653I/System.out: 8 {
04-26 08:42:43.653I/System.out: _id=9
04-26 08:42:43.653I/System.out: mydata=3009
04-26 08:42:43.653I/System.out: }
04-26 08:42:43.654I/System.out: 9 {
04-26 08:42:43.654I/System.out: _id=10
04-26 08:42:43.654I/System.out: mydata=3010
04-26 08:42:43.654I/System.out: }
04-26 08:42:43.654I/System.out: <<<<<
额外的
这是一种更通用的方法，可以处理多个表，禁止将新数据明显加载到转换表中的应用程序/表特定的加载。
添加了日志记录，这可以实现计时。
例如运行这个并加载 10,000 行（使用
mDBHlpr.addLotsOfData(DBHelper.TABLE_MASTER + DBHelper.TRANSITION_SUFFIX,100000,10000);）。可以看出：-尽管表需要 0.659 秒来加载数据（10,000 行），但表实际上仅在 0.007 秒内不可用（如果 DROP 在切换后完成，则为 0.001 秒（删除表在时间方面可能很昂贵，同时重命名一张桌子需要很少的时间））。
修改后的活动是：-
public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// Instantiate the helper noting that data will be loaded
mDBHlpr = new DBHelper(this);
// Get the current data
Cursor csr = mDBHlpr.getFirst10Rows();
// Write the current data to the log
DatabaseUtils.dumpCursor(csr);
//<<<<<<<<<< do the switch >>>>>>>>>>
mDBHlpr.switchData(DBHelper.TABLE_MASTER);
//Again get the data and write to the log
csr = mDBHlpr.getFirst10Rows();
DatabaseUtils.dumpCursor(csr);
//<<<<<<<<<<<<<<< AutoSwitch example >>>>>>>>>>
//Create the transition tables
mDBHlpr.prepareSwitchAllAppTables();
Log.d("NEWDATALOADSTART","Loading of new data has started");
// Prepare the new data by loading the data into the transition table
// (only the 1 table for demo)
// but perhaps 1 load per table according to requirements
mDBHlpr.addLotsOfData(DBHelper.TABLE_MASTER + DBHelper.TRANSITION_SUFFIX,100000,10000);
Log.d("TABLESNOTAVAILABLE","Tables will now be unavailable whil switching");
// Switch all of the tables
mDBHlpr.doSwitchAllAppTables();
Log.d("TABLESAVAILABLE","Switch completed, Tables are now available with new data");
//Again get the data and write to the log
csr = mDBHlpr.getFirst10Rows();
DatabaseUtils.dumpCursor(csr);
//Clean up
csr.close();
}
}
- 即 from line
//<<<<<<<<<<<<<<< AutoSwitch example >>>>>>>>>>已添加，但csr.close()仍是最后一行。 - 看评论
- 切换分为两个阶段准备（创建转换表）和实际切换（重命名原始表，然后将转换表重命名为相应的原始表）。尽管该切换包括删除重命名的表，但这可以移至第三阶段，进一步减少表不可用的时间。
和修改后的DBHelpr.java
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
private static final int DBVERSION = 1;
public static final String TABLE_MASTER = "master";
public static final String COl_MASTER_ID = BaseColumns._ID;
public static final String COL_MASTER_MYDATA = "mydata";
public static final String TRANSITION_SUFFIX = "_trn";
public static final String RENAME_SUFFIX = "rename";
private static final String SQLITE_MASTER = "sqlite_master";
private static final String SQLITE_MASTER_TYPECOLUMN = "type";
private static final String SQLITE_MASTER_NAMECOLUMN = "name";
private static final String SQLITE_MASTER_SQLCOLUMN = "sql";
private static final String[] SQLITE_MATSER_COLUMNS = new String[]{SQLITE_MASTER_NAMECOLUMN,SQLITE_MASTER_SQLCOLUMN};
private static final String APPTABLES_WHERECLAUSE =
"(" +
SQLITE_MASTER_NAMECOLUMN + " NOT LIKE 'sqlite%' " +
" AND " + SQLITE_MASTER_NAMECOLUMN + " NOT LIKE 'android%' " +
" AND " + SQLITE_MASTER_NAMECOLUMN + " NOT LIKE '%" + TRANSITION_SUFFIX + "'" +
")" +
" AND " + SQLITE_MASTER_TYPECOLUMN + "= '" + "table" + "'";
SQLiteDatabase mDB;
//Construct the helper NOTE will create the db if needed
public DBHelper(Context context) {
super(context, DBNAME,null, DBVERSION);
mDB = this.getWritableDatabase(); // force db open
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(create_table_sql(TABLE_MASTER));
mDB = db;
addLotsOfData(TABLE_MASTER,1000,1); // Load some data
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
// Generate the table SQL according to the table name passed
private String create_table_sql(String table) {
return "CREATE TABLE IF NOT EXISTS " + table + "(" +
COl_MASTER_ID + " INTEGER PRIMARY KEY," +
COL_MASTER_MYDATA + " TEXT" +
")";
}
// Switch the original table for a newly created version
public void switchData(String table) {
String transitional_table = "transitional";
String renamed_master = table + "_old";
boolean already_in_transaction = mDB.inTransaction();
if (!already_in_transaction) {
mDB.beginTransaction();
}
//<<<<<<<<<< create and load of new data could be done elsewhere
mDB.execSQL(create_table_sql(transitional_table));
addLotsOfData(transitional_table,1000,3001); //<<<<<<<< load new data here
//>>>>>>>>>>
mDB.execSQL("ALTER TABLE " + TABLE_MASTER + " RENAME TO " + renamed_master);
mDB.execSQL("ALTER TABLE " + transitional_table + " RENAME TO " + TABLE_MASTER);
mDB.execSQL("DROP TABLE IF EXISTS " + renamed_master);
if (!already_in_transaction) {
mDB.setTransactionSuccessful();
mDB.endTransaction();
}
}
// Add some data
public void addLotsOfData(String table, int rows, int value_offset) {
boolean already_in_transaction = mDB.inTransaction();
if (!already_in_transaction) {
mDB.beginTransaction();
}
for (int i = 0; i < rows; i++) {
addRow(table,String.valueOf(value_offset + i));
}
if (!already_in_transaction) {
mDB.setTransactionSuccessful();
mDB.endTransaction();
}
}
// Insert a single row
public long addRow(String table, String value) {
ContentValues cv = new ContentValues();
cv.put(COL_MASTER_MYDATA,value);
return mDB.insert(table,null,cv);
}
public Cursor getFirst10Rows() {
return mDB.query(TABLE_MASTER,null,null,null,null,null,null,"10");
}
/**
* Create transition copy of all App tables (not sqlite..... tables or android.... tables)
*/
public void prepareSwitchAllAppTables() {
boolean already_in_transaction = mDB.inTransaction();
if (!already_in_transaction) {
mDB.beginTransaction();
}
Cursor csr = mDB.query(SQLITE_MASTER,SQLITE_MATSER_COLUMNS,APPTABLES_WHERECLAUSE,null,null, null,null);
while (csr.moveToNext()) {
String original_tablename = csr.getString(csr.getColumnIndex(SQLITE_MASTER_NAMECOLUMN));
String original_sql = csr.getString(csr.getColumnIndex(SQLITE_MASTER_SQLCOLUMN));
String transition_tablename = original_tablename + TRANSITION_SUFFIX;
String transition_sql = original_sql.replace(original_tablename,transition_tablename).replace("CREATE TABLE","CREATE TABLE IF NOT EXISTS");
Log.d("PREAPRE4SWITCH","Executing the SQL (create transition table for table " + original_sql +
") \n\t" + transition_sql);
mDB.execSQL(transition_sql);
mDB.delete(transition_tablename,null,null); // just to make sure that all transition tables are empty
}
if (!already_in_transaction) {
mDB.setTransactionSuccessful();
mDB.endTransaction();
}
}
public void doSwitchAllAppTables() {
boolean already_in_transaction = mDB.inTransaction();
if (!already_in_transaction) {
mDB.beginTransaction();
}
Cursor csr = mDB.query(SQLITE_MASTER,SQLITE_MATSER_COLUMNS,APPTABLES_WHERECLAUSE,null,null, null,null);
ArrayList<String> tables_to_delete = new ArrayList<>();
while (csr.moveToNext()) {
String original_name = csr.getString(csr.getColumnIndex(SQLITE_MASTER_NAMECOLUMN));
String transition_name = original_name + TRANSITION_SUFFIX;
String rename_name = RENAME_SUFFIX;
tables_to_delete.add(rename_name);
Log.d("SWITCHRENAMEORIG","Executing the SQL to rename(original) " + original_name + " table to " + rename_name);
mDB.execSQL("ALTER TABLE " + original_name + " RENAME TO " + rename_name);
Log.d("SWITCHRENAMETRNS","Executing the SQL to rename(transition) from " + transition_name +
" to (original)" + original_name);
mDB.execSQL("ALTER TABLE " + transition_name + " RENAME TO " + original_name);
}
csr.close();
for (String table_to_delete: tables_to_delete) {
Log.d("SWITCHDROPRENAMED","Dropping renamed original table " + table_to_delete);
mDB.execSQL("DROP TABLE If EXISTS " + table_to_delete);
}
if (!already_in_transaction) {
mDB.setTransactionSuccessful();
mDB.endTransaction();
}
}
}
这假设您从不创建以android或sqlite开头的 App 表
（sqlite_ 受保护
以“sqlite_”开头的表名保留供内部使用。尝试创建名称以“sqlite_”开头的表是错误的。）。
所以真的
SQLITE_MASTER_NAMECOLUMN + " NOT LIKE 'sqlite%' "应该如此SQLITE_MASTER_NAMECOLUMN + " NOT LIKE 'sqlite_%' "。
sqlite_master 用于驱动流程，它是模式并包含表列表（以及其他实体，如索引、视图和触发器）。
请注意，如果使用 FOREIGN KEYS 并且未使用 ON CASCADE DELETE，则不能依赖上述内容。因为子表必须在父母之前被删除。
- 触发器见ALTER TABLE - ALTER TABLE RENAME
结果
在日志中运行上述结果，包括：-
...........
04-26 13:41:36.000 I/System.out: mydata=3010
04-26 13:41:36.000 I/System.out: }
04-26 13:41:36.000 I/System.out: <<<<<
04-26 13:41:36.000 D/PREAPRE4SWITCH: Executing the SQL (create transition table for table CREATE TABLE "master"(_id INTEGER PRIMARY KEY,mydata TEXT))
CREATE TABLE IF NOT EXISTS "master_trn"(_id INTEGER PRIMARY KEY,mydata TEXT)
04-26 13:41:36.007 D/NEWDATALOADSTART: Loading of new data has started
04-26 13:41:43.666 D/TABLESNOTAVAILABLE: Tables will now be unavailable whil switching
04-26 13:41:43.666 D/SWITCHRENAMEORIG: Executing the SQL to rename(original) master table to rename
04-26 13:41:43.667 D/SWITCHRENAMETRNS: Executing the SQL to rename(transition) from master_trn to (original)master
04-26 13:41:43.667 D/SWITCHDROPRENAMED: Dropping renamed original table rename
04-26 13:41:43.673 D/TABLESAVAILABLE: Switch completed, Tables are now available with new data
04-26 13:41:43.673 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@9ce45fc
04-26 13:41:43.673 I/System.out: 0 {
04-26 13:41:43.673 I/System.out: _id=1
04-26 13:41:43.673 I/System.out: mydata=10000
04-26 13:41:43.673 I/System.out: }
04-26 13:41:43.673 I/System.out: 1 {
04-26 13:41:43.673 I/System.out: _id=2
04-26 13:41:43.673 I/System.out: mydata=10001
04-26 13:41:43.673 I/System.out:
..........
最后
您可能对以下内容感兴趣：-
在这种情况下，您可以使用enableWriteAheadLogging（覆盖onConfigure()方法以使用它）打开 WAL（Android 9+ 默认情况下打开）。
推荐阅读
- c - 将 INFIX 表达式转换为等效的 POSTFIX 表达式
- python - 即使没有事件，我也希望我的 for 循环更新
- ruby-on-rails - 将排行榜报告下载到 csv rails
- c# - NPOI如何获取单元格中文本的大小
- asp.net-core - EF Core - 在没有外键约束的两个模型之间创建关系
- angular - 检测在订阅块中更新的 BehaviorSubject 组件成员变量的变化？
- javascript - '<' 字符在我输入时显示
- flutter - 如何有效地保存和检索后端数据库中元素的位置？
- python - python vtk 多个渲染器和方向小部件
- amazon-web-services - 作为 ROOT SSH 到 Elastic Beanstalk 实例