title: How to delete all tables from Sqlite in android
body: <p>I search a lot, lots of about this.  But little helps.<br>
  The matter is the <strong>upgrade and downgrade of database</strong>. <em>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).</em><br>
  So <strong>What matters is that the user may downgrade then upgrade.</strong></p>
  
  <p>Consider this situation:<br>
  Get 4 version,there are a1(low version), a2, a3, a4(high version).<br>
  a1  table1<br>
  a2  table1                  table2(add table,add filed t2_2)<br>
  a3  table1                  table2(add field t2_3)<br>
  a4  table1(add field t1_4)  table2(add field t2_4)        </p>
  
  <p>if <strong>install a total</strong> new(not updrade or downgrade),all versions will create its own tables in <em>onCreate</em>.<br>
  if <strong>upgrade</strong>,we will upgrade table or add table in <em>onUpgrade</em> like this:<a href="http://blog.adamsbros.org/2012/02/28/upgrade-android-sqlite-database/" rel="nofollow">http://blog.adamsbros.org/2012/02/28/upgrade-android-sqlite-database/</a><br>
  if <strong>downgrade</strong>,we delete and recreate the table we known in the corresponding version in <em>onDowngrade</em>.<br>
  for example when a4->a1, delete table1 and recreate it; a4->a2 delete table1 and table2,then recreate them.  </p>
  
  <p><strong>But this will cause a error when user do this a4->a1->a3.</strong><br>
  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).<br>
  *I get a idea to solve this,is deleting all tables when downgrade. But do not have a way to delete all tables.<br>
  Delete databases,I think this may works, but  reality is cruel.<br>
  When downgrading I invoked <em>context.deleteDatabase(DB_NAME);</em> in onDowngrade. Then invoked the table creating function. The it crashed. First I think the system will recreate the database file,actually not.*<br>
    <strong>MY QUESSTION is:<br>
     1.how to delete all tables from sqlite.<br>
     2.or how to recreate the database when I delete it.</strong><br>
   Any one solved is ok.  </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>later I get this way to get all the tables of the database and then drop the tables and recreate them.</p>
  
  <pre><code>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&amp;&amp;!cursor.isClosed()){
                          cursor.close();
                      } 
  </code></pre>
  
  <p>Thanks all.</p>