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.

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)

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.

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();
                    }

Thanks all.

回答1:

Method:1:

PRAGMA writable_schema = 1;
delete from sqlite_master where type = 'table';
PRAGMA writable_schema = 0;

Method: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.

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_").

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:

Drop all tables command

回答2:

Execute raw SQL request on DB to delete table:

DROP TABLE table_name;

for each table in your database.

DROP TABLE table_name CASCADE;

To drop a table, and all the other objects that depend on it, use one of these.

Cursor cursor = getReadableDatabase().rawQuery("DROP TABLE ?", new String[] { "MY_TABLE" });