如何从android中的Sqlite删除所有表 [英] How to delete all tables from Sqlite in android
问题描述
我搜索了很多有关此的内容.但没有什么帮助.
问题是数据库的升级和降级. 如果只是升级或降级，这很简单.
升级，修改或添加表时(保留数据，这一点很重要)，降级时，删除已知表，然后重新创建(数据丢失，这没有关系，因为降级不是用户的推荐操作，但是当用户降级完成后，应用程序将无法崩溃.)
因此，重要的是，用户可以先降级然后升级.
I search a lot, lots of about this. But little helps.
The matter is the upgrade and downgrade of database. If just upgrade or downgrade, it's simple.
when upgrade,modify or add tables(keep data,it is important), when downgrade, delete the known table, and recreate it again(data lose,its does not matter,as downgrade is not a recommend action for user,but when user downgrade finished, the application can not crash).
So What matters is that the user may downgrade then upgrade.
考虑这种情况:
获得4个版本，有a1(低版本)，a2，a3，a4(高版本).
a1 table1
a2 table1 table2(添加表，添加字段t2_2)
a3 table1 table2(添加字段t2_3)
a4 table1(添加字段t1_4)table2(添加字段t2_4)
Consider this situation:
Get 4 version,there are a1(low version), a2, a3, a4(high version).
a1 table1
a2 table1 table2(add table,add filed t2_2)
a3 table1 table2(add field t2_3)
a4 table1(add field t1_4) table2(add field t2_4)
如果安装总计新版本(不是升级或降级)，则所有版本都会在 onCreate 中创建自己的表.
如果升级，我们将升级表或在 onUpgrade 中添加表，如下所示:
if install a total new(not updrade or downgrade),all versions will create its own tables in onCreate.
if upgrade,we will upgrade table or add table in onUpgrade like this:http://blog.adamsbros.org/2012/02/28/upgrade-android-sqlite-database/
if downgrade,we delete and recreate the table we known in the corresponding version in onDowngrade.
for example when a4->a1, delete table1 and recreate it; a4->a2 delete table1 and table2,then recreate them.
但这将在用户执行a4-> a1-> a3时导致错误.
当a1升级到a3时，表2实际上存在，因为当a4降级到a1时，它不会被删除(在a1中，它不会在以后的版本中添加新表).我们无法丢失数据，因此在升级时，我们无法删除该表以重新创建该表.因此，假设我们知道表的结构，那么唯一的方法就是修改表.问题来了，在a4-> a1-> a3中，a3不知道表的前一个结构是什么.它简单地创建了table2(可能会出错，实际上是在实际使用中，可能会更复杂).
*我有一个解决方案，降级时删除所有表.但是没有删除所有表的方法.
删除数据库，我认为这可能有效，但是现实很残酷.
降级时，我在onDowngrade中调用了 context.deleteDatabase(DB_NAME); .然后调用表创建功能.它崩溃了.首先，我认为系统会重新创建数据库文件，实际上不是.*
我的问题是:
1.如何从sqlite中删除所有表.
2.或删除时如何重新创建数据库.
任何一个解决都可以.
But this will cause a error when user do this a4->a1->a3.
when a1 upgrade to a3,the table2 actually exists ,because when a4 downgrade to a1,it is not deleted(in a1,it do not in later version will add a new table).We canot lose the data,so when upgrading ,we can not delete the table to recreate the table. So,the only way is to modify the table,supposing we know the table structure. The problem comes here, in a4->a1->a3, a3 do not know what is the former structure of tables. It simplily create table2 (may get errors,actually in pratical use,may be more complex).
*I get a idea to solve this,is deleting all tables when downgrade. But do not have a way to delete all tables.
Delete databases,I think this may works, but reality is cruel.
When downgrading I invoked context.deleteDatabase(DB_NAME); in onDowngrade. Then invoked the table creating function. The it crashed. First I think the system will recreate the database file,actually not.*
MY QUESSTION is:
1.how to delete all tables from sqlite.
2.or how to recreate the database when I delete it.
Any one solved is ok.
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.app.ActivityThread.installContentProviders(ActivityThread.java:4263)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.app.ActivityThread.handleBindApplication(ActivityThread.java:4201)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.app.ActivityThread.access$1300(ActivityThread.java:137)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1262)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.os.Handler.dispatchMessage(Handler.java:99)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.os.Looper.loop(Looper.java:137)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.app.ActivityThread.main(ActivityThread.java:4819)
09-03 17:42:47.800: E/AndroidRuntime(19267): at java.lang.reflect.Method.invokeNative(Native Method)
09-03 17:42:47.800: E/AndroidRuntime(19267): at java.lang.reflect.Method.invoke(Method.java:511)
09-03 17:42:47.800: E/AndroidRuntime(19267): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
09-03 17:42:47.800: E/AndroidRuntime(19267): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
09-03 17:42:47.800: E/AndroidRuntime(19267): at dalvik.system.NativeStart.main(Native Method)
09-03 17:42:47.800: E/AndroidRuntime(19267): Caused by: android.database.sqlite.SQLiteException: cannot rollback - no transaction is active (code 1)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteConnection.nativeExecute(Native Method)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteConnection.execute(SQLiteConnection.java:548)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteSession.endTransactionUnchecked(SQLiteSession.java:439)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteSession.endTransaction(SQLiteSession.java:401)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteDatabase.endTransaction(SQLiteDatabase.java:522)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:263)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
09-03 17:42:47.800: E/AndroidRuntime(19267): at com.sogou.appmall.db.MarketContentProvider.onCreate(SourceFile:36)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.content.ContentProvider.attachInfo(ContentProvider.java:1058)
09-03 17:42:47.800: E/AndroidRuntime(19267): at android.app.ActivityThread.installProvider(ActivityThread.java:4634)
09-03 17:42:47.800: E/AndroidRuntime(19267): ... 12 more
09-03 17:42:49.020: E/Trace(19302): error opening trace file: No such file or directory (2)
09-03 17:42:49.960: E/Trace(19318): error opening trace file: No such file or directory (2)
后来我用这种方法来获取数据库的所有表，然后删除表并重新创建它们.
later I get this way to get all the tables of the database and then drop the tables and recreate them.
SQLiteDatabase db = openOrCreateDatabase(MarketDBHelper.DB_NAME,
Context.MODE_PRIVATE, null);
Cursor cursor = db
.rawQuery(
"select name from sqlite_master where type='table' order by name",
null);
while (cursor.moveToNext()) {
// 遍历出表名
String name = cursor.getString(0);
Log.i("MarketDBHelper", name);
}
if(cursor!=null&&!cursor.isClosed()){
cursor.close();
}
谢谢.
推荐答案
方法:1:
PRAGMA writable_schema = 1;
delete from sqlite_master where type = 'table';
PRAGMA writable_schema = 0;
方法:2:
select 'drop table ' || name || ';' from sqlite_master where type = 'table';
此输出是一个脚本，它将为您删除表.对于索引，只需将表替换为索引即可.
The output of this is a script that will drop the tables for you. For indexes, just replace table with index.
您可以在where部分中使用其他子句来限制选择哪些表或索引(例如，对于以"pax_"开头的表或索引，请使用和名称glob'pax _ *'").
You can use other clauses in the where section to limit which tables or indexes are selected (such as "and name glob 'pax_*'" for those starting with "pax_").
您可以在一个简单的bash(或cmd.exe)脚本中将此脚本的创建与运行结合起来，这样就只需要运行一个命令即可.
You could combine the creation of this script with the running of it in a simple bash (or cmd.exe) script so there's only one command to run.
如果您不关心数据库中的任何信息，我认为您可以删除存储在硬盘上的文件，这可能会更快.我从来没有测试过，但是我看不出为什么它不起作用.
If you don't care about any of the information in the DB, I think you can just delete the file it's stored in off the hard disk - that's probably faster. I've never tested this but I can't see why it wouldn't work.
请参见下面的删除所有表"命令链接:
refer the bellow drop all table command link:
这篇关于如何从android中的Sqlite删除所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
