Extract superclass for HostDatabase into RobustSQLiteOpenHelper

git-svn-id: https://connectbot.googlecode.com/svn/trunk/connectbot@333 df292f66-193f-0410-a5fc-6d59da041ff2

3 files changed, 195 insertions, 128 deletions

diff --git a/src/org/connectbot/util/HostDatabase.java b/src/org/connectbot/util/HostDatabase.java
index f81d2c7..b1b628d 100644
--- a/src/org/connectbot/util/HostDatabase.java
+++ b/src/org/connectbot/util/HostDatabase.java
@@ -33,7 +33,6 @@ import android.content.Context;
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteException;
-import android.database.sqlite.SQLiteOpenHelper;
 import android.util.Log;
 
 import com.trilead.ssh2.KnownHosts;
@@ -44,7 +43,7 @@ import com.trilead.ssh2.KnownHosts;
  *
  * @author jsharkey
  */
-public class HostDatabase extends SQLiteOpenHelper {
+public class HostDatabase extends RobustSQLiteOpenHelper {
 
 	public final static String TAG = "ConnectBot.HostDatabase";
 
@@ -112,6 +111,11 @@ public class HostDatabase extends SQLiteOpenHelper {
 	public final static long PUBKEYID_NEVER = -2;
 	public final static long PUBKEYID_ANY = -1;
 
+	static {
+		addTableName(TABLE_HOSTS);
+		addTableName(TABLE_PORTFORWARDS);
+	}
+
 	public HostDatabase(Context context) {
 		super(context, DB_NAME, null, DB_VERSION);
 
@@ -120,7 +124,7 @@ public class HostDatabase extends SQLiteOpenHelper {
 
 	@Override
 	public void onCreate(SQLiteDatabase db) {
-		dropAllTables(db);
+		super.onCreate(db);
 
 		db.execSQL("CREATE TABLE " + TABLE_HOSTS
 				+ " (_id INTEGER PRIMARY KEY, "
@@ -174,7 +178,7 @@ public class HostDatabase extends SQLiteOpenHelper {
 	}
 
 	@Override
-	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
+	public void onRobustUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) throws SQLiteException {
 		// Versions of the database before the Android Market release will be
 		// shot without warning.
 		if (oldVersion <= 9) {
@@ -183,128 +187,58 @@ public class HostDatabase extends SQLiteOpenHelper {
 			return;
 		}
 
-		try {
-			switch (oldVersion) {
-			case 10:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_PUBKEYID + " INTEGER DEFAULT " + PUBKEYID_ANY);
-			case 11:
-				db.execSQL("CREATE TABLE " + TABLE_PORTFORWARDS
-						+ " (_id INTEGER PRIMARY KEY, "
-						+ FIELD_PORTFORWARD_HOSTID + " INTEGER, "
-						+ FIELD_PORTFORWARD_NICKNAME + " TEXT, "
-						+ FIELD_PORTFORWARD_TYPE + " TEXT NOT NULL DEFAULT " + PORTFORWARD_LOCAL + ", "
-						+ FIELD_PORTFORWARD_SOURCEPORT + " INTEGER NOT NULL DEFAULT 8080, "
-						+ FIELD_PORTFORWARD_DESTADDR + " TEXT, "
-						+ FIELD_PORTFORWARD_DESTPORT + " INTEGER)");
-			case 12:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_WANTSESSION + " TEXT DEFAULT '" + Boolean.toString(true) + "'");
-			case 13:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_COMPRESSION + " TEXT DEFAULT '" + Boolean.toString(false) + "'");
-			case 14:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_ENCODING + " TEXT DEFAULT '" + ENCODING_DEFAULT + "'");
-			case 15:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_PROTOCOL + " TEXT DEFAULT 'ssh'");
-			case 16:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_DELKEY + " TEXT DEFAULT '" + DELKEY_DEL + "'");
-			case 17:
-				db.execSQL("CREATE INDEX " + TABLE_PORTFORWARDS + FIELD_PORTFORWARD_HOSTID + "index ON "
-						+ TABLE_PORTFORWARDS + " (" + FIELD_PORTFORWARD_HOSTID + ");");
-
-				// Add colors
-				db.execSQL("CREATE TABLE " + TABLE_COLORS
-						+ " (_id INTEGER PRIMARY KEY, "
-						+ FIELD_COLOR_NUMBER + " INTEGER, "
-						+ FIELD_COLOR_VALUE + " INTEGER, "
-						+ FIELD_COLOR_SCHEME + " INTEGER)");
-				db.execSQL("CREATE INDEX " + TABLE_COLORS + FIELD_COLOR_SCHEME + "index ON "
-						+ TABLE_COLORS + " (" + FIELD_COLOR_SCHEME + ");");
-
-				db.execSQL("CREATE TABLE " + TABLE_COLOR_DEFAULTS
-						+ " (_id INTEGER PRIMARY KEY, "
-						+ FIELD_COLOR_SCHEME + " INTEGER, "
-						+ FIELD_COLOR_FG + " INTEGER, "
-						+ FIELD_COLOR_BG + " INTEGER)");
-				db.execSQL("CREATE INDEX " + TABLE_COLOR_DEFAULTS + FIELD_COLOR_SCHEME + "index ON "
-						+ TABLE_COLOR_DEFAULTS + " (" + FIELD_COLOR_SCHEME + ");");
-			case 18:
-				db.execSQL("ALTER TABLE " + TABLE_HOSTS
-						+ " ADD COLUMN " + FIELD_HOST_USEAUTHAGENT + " TEXT DEFAULT '" + AUTHAGENT_NO + "'");
-			}
-		} catch (SQLiteException e) {
-			// The database has entered an unknown state. Try to recover.
-			try {
-				regenerateTables(db);
-			} catch (SQLiteException e2) {
-				dropAndCreateTables(db);
-			}
-		}
-	}
-
-	private void regenerateTables(SQLiteDatabase db) {
-		dropAllTablesWithPrefix(db, "OLD_");
-		db.execSQL("ALTER TABLE " + TABLE_HOSTS + " RENAME TO OLD_"
-				+ TABLE_HOSTS);
-		db.execSQL("ALTER TABLE " + TABLE_PORTFORWARDS + " RENAME TO OLD_"
-				+ TABLE_PORTFORWARDS);
-
-		onCreate(db);
-
-		repopulateTable(db, TABLE_HOSTS);
-		repopulateTable(db, TABLE_PORTFORWARDS);
-
-		dropAllTablesWithPrefix(db, "OLD_");
-	}
-
-	private void repopulateTable(SQLiteDatabase db, String tableName) {
-		String columns = getTableColumnNames(db, tableName);
-
-		StringBuilder sb = new StringBuilder();
-		sb.append("INSERT INTO ")
-				.append(tableName)
-				.append(" (")
-				.append(columns)
-				.append(") SELECT ")
-				.append(columns)
-				.append(" FROM OLD_")
-				.append(tableName);
-
-		String sql = sb.toString();
-		Log.d(TAG, "Attempting to execute repopulation command: " + sql);
-		db.execSQL(sql);
-	}
-
-	private String getTableColumnNames(SQLiteDatabase db, String tableName) {
-		StringBuilder sb = new StringBuilder();
-
-		Cursor fields = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
-		while (fields.moveToNext()) {
-			if (!fields.isFirst())
-				sb.append(", ");
-			sb.append(fields.getString(1));
+		switch (oldVersion) {
+		case 10:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_PUBKEYID + " INTEGER DEFAULT " + PUBKEYID_ANY);
+		case 11:
+			db.execSQL("CREATE TABLE " + TABLE_PORTFORWARDS
+					+ " (_id INTEGER PRIMARY KEY, "
+					+ FIELD_PORTFORWARD_HOSTID + " INTEGER, "
+					+ FIELD_PORTFORWARD_NICKNAME + " TEXT, "
+					+ FIELD_PORTFORWARD_TYPE + " TEXT NOT NULL DEFAULT " + PORTFORWARD_LOCAL + ", "
+					+ FIELD_PORTFORWARD_SOURCEPORT + " INTEGER NOT NULL DEFAULT 8080, "
+					+ FIELD_PORTFORWARD_DESTADDR + " TEXT, "
+					+ FIELD_PORTFORWARD_DESTPORT + " INTEGER)");
+		case 12:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_WANTSESSION + " TEXT DEFAULT '" + Boolean.toString(true) + "'");
+		case 13:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_COMPRESSION + " TEXT DEFAULT '" + Boolean.toString(false) + "'");
+		case 14:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_ENCODING + " TEXT DEFAULT '" + ENCODING_DEFAULT + "'");
+		case 15:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_PROTOCOL + " TEXT DEFAULT 'ssh'");
+		case 16:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_DELKEY + " TEXT DEFAULT '" + DELKEY_DEL + "'");
+		case 17:
+			db.execSQL("CREATE INDEX " + TABLE_PORTFORWARDS + FIELD_PORTFORWARD_HOSTID + "index ON "
+					+ TABLE_PORTFORWARDS + " (" + FIELD_PORTFORWARD_HOSTID + ");");
+
+			// Add colors
+			db.execSQL("CREATE TABLE " + TABLE_COLORS
+					+ " (_id INTEGER PRIMARY KEY, "
+					+ FIELD_COLOR_NUMBER + " INTEGER, "
+					+ FIELD_COLOR_VALUE + " INTEGER, "
+					+ FIELD_COLOR_SCHEME + " INTEGER)");
+			db.execSQL("CREATE INDEX " + TABLE_COLORS + FIELD_COLOR_SCHEME + "index ON "
+					+ TABLE_COLORS + " (" + FIELD_COLOR_SCHEME + ");");
+
+			db.execSQL("CREATE TABLE " + TABLE_COLOR_DEFAULTS
+					+ " (_id INTEGER PRIMARY KEY, "
+					+ FIELD_COLOR_SCHEME + " INTEGER, "
+					+ FIELD_COLOR_FG + " INTEGER, "
+					+ FIELD_COLOR_BG + " INTEGER)");
+			db.execSQL("CREATE INDEX " + TABLE_COLOR_DEFAULTS + FIELD_COLOR_SCHEME + "index ON "
+					+ TABLE_COLOR_DEFAULTS + " (" + FIELD_COLOR_SCHEME + ");");
+		case 18:
+			db.execSQL("ALTER TABLE " + TABLE_HOSTS
+					+ " ADD COLUMN " + FIELD_HOST_USEAUTHAGENT + " TEXT DEFAULT '" + AUTHAGENT_NO + "'");
 		}
-		fields.close();
-
-		return sb.toString();
-	}
-
-	private void dropAndCreateTables(SQLiteDatabase db) {
-		dropAllTables(db);
-		onCreate(db);
-	}
-
-	private void dropAllTablesWithPrefix(SQLiteDatabase db, String prefix) {
-		db.execSQL("DROP TABLE IF EXISTS " + prefix + TABLE_HOSTS);
-		db.execSQL("DROP TABLE IF EXISTS " + prefix + TABLE_PORTFORWARDS);
-	}
-
-	private void dropAllTables(SQLiteDatabase db) {
-		dropAllTablesWithPrefix(db, "");
 	}
 
 	/**
diff --git a/src/org/connectbot/util/PubkeyDatabase.java b/src/org/connectbot/util/PubkeyDatabase.java
index fdf1c53..b7481b0 100644
--- a/src/org/connectbot/util/PubkeyDatabase.java
+++ b/src/org/connectbot/util/PubkeyDatabase.java
@@ -27,7 +27,7 @@ import android.content.ContentValues;
 import android.content.Context;
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
-import android.database.sqlite.SQLiteOpenHelper;
+import android.database.sqlite.SQLiteException;
 
 /**
  * Public Key Encryption database. Contains private and public key pairs
@@ -35,7 +35,7 @@ import android.database.sqlite.SQLiteOpenHelper;
  *
  * @author Kenny Root
  */
-public class PubkeyDatabase extends SQLiteOpenHelper {
+public class PubkeyDatabase extends RobustSQLiteOpenHelper {
 	public final static String TAG = "ConnectBot.PubkeyDatabase";
 
 	public final static String DB_NAME = "pubkeys";
@@ -55,6 +55,10 @@ public class PubkeyDatabase extends SQLiteOpenHelper {
 
 	private Context context;
 
+	static {
+		addTableName(TABLE_PUBKEYS);
+	}
+
 	public PubkeyDatabase(Context context) {
 		super(context, DB_NAME, null, DB_VERSION);
 
@@ -63,6 +67,8 @@ public class PubkeyDatabase extends SQLiteOpenHelper {
 
 	@Override
 	public void onCreate(SQLiteDatabase db) {
+		super.onCreate(db);
+
 		db.execSQL("CREATE TABLE " + TABLE_PUBKEYS
 				+ " (_id INTEGER PRIMARY KEY, "
 				+ FIELD_PUBKEY_NICKNAME + " TEXT, "
@@ -74,7 +80,7 @@ public class PubkeyDatabase extends SQLiteOpenHelper {
 	}
 
 	@Override
-	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
+	public void onRobustUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) throws SQLiteException {
 
 	}
 
diff --git a/src/org/connectbot/util/RobustSQLiteOpenHelper.java b/src/org/connectbot/util/RobustSQLiteOpenHelper.java
new file mode 100644
index 0000000..00d37ef
--- /dev/null
+++ b/src/org/connectbot/util/RobustSQLiteOpenHelper.java
@@ -0,0 +1,127 @@
+/*
+	ConnectBot: simple, powerful, open-source SSH client for Android
+	Copyright (C) 2007-2008 Kenny Root, Jeffrey Sharkey
+
+	This program is free software: you can redistribute it and/or modify
+	it under the terms of the GNU General Public License as published by
+	the Free Software Foundation, either version 3 of the License, or
+	(at your option) any later version.
+
+	This program is distributed in the hope that it will be useful,
+	but WITHOUT ANY WARRANTY; without even the implied warranty of
+	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+	GNU General Public License for more details.
+
+	You should have received a copy of the GNU General Public License
+	along with this program.  If not, see <http://www.gnu.org/licenses/>.
+*/
+
+package org.connectbot.util;
+
+import java.util.LinkedList;
+import java.util.List;
+
+import android.content.Context;
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.database.sqlite.SQLiteException;
+import android.database.sqlite.SQLiteOpenHelper;
+import android.database.sqlite.SQLiteDatabase.CursorFactory;
+
+/**
+ * @author Kenny Root
+ *
+ */
+public abstract class RobustSQLiteOpenHelper extends SQLiteOpenHelper {
+	private static List<String> mTableNames = new LinkedList<String>();
+
+	public RobustSQLiteOpenHelper(Context context, String name,
+			CursorFactory factory, int version) {
+		super(context, name, factory, version);
+	}
+
+	protected static void addTableName(String tableName) {
+		mTableNames.add(tableName);
+	}
+
+	@Override
+	public void onCreate(SQLiteDatabase db) {
+		dropAllTables(db);
+	}
+
+	@Override
+	public final void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
+		try {
+			onRobustUpgrade(db, oldVersion, newVersion);
+		} catch (SQLiteException e) {
+			// The database has entered an unknown state. Try to recover.
+			try {
+				regenerateTables(db);
+			} catch (SQLiteException e2) {
+				dropAndCreateTables(db);
+			}
+		}
+	}
+
+	public abstract void onRobustUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) throws SQLiteException;
+
+	private void regenerateTables(SQLiteDatabase db) {
+		dropAllTablesWithPrefix(db, "OLD_");
+
+		for (String tableName : mTableNames)
+			db.execSQL("ALTER TABLE " + tableName + " RENAME TO OLD_"
+					+ tableName);
+
+		onCreate(db);
+
+		for (String tableName : mTableNames)
+			repopulateTable(db, tableName);
+
+		dropAllTablesWithPrefix(db, "OLD_");
+	}
+
+	private void repopulateTable(SQLiteDatabase db, String tableName) {
+		String columns = getTableColumnNames(db, tableName);
+
+		StringBuilder sb = new StringBuilder();
+		sb.append("INSERT INTO ")
+				.append(tableName)
+				.append(" (")
+				.append(columns)
+				.append(") SELECT ")
+				.append(columns)
+				.append(" FROM OLD_")
+				.append(tableName);
+
+		String sql = sb.toString();
+		db.execSQL(sql);
+	}
+
+	private String getTableColumnNames(SQLiteDatabase db, String tableName) {
+		StringBuilder sb = new StringBuilder();
+
+		Cursor fields = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
+		while (fields.moveToNext()) {
+			if (!fields.isFirst())
+				sb.append(", ");
+			sb.append(fields.getString(1));
+		}
+		fields.close();
+
+		return sb.toString();
+	}
+
+	private void dropAndCreateTables(SQLiteDatabase db) {
+		dropAllTables(db);
+		onCreate(db);
+	}
+
+	private void dropAllTablesWithPrefix(SQLiteDatabase db, String prefix) {
+		for (String tableName : mTableNames)
+			db.execSQL("DROP TABLE IF EXISTS " + prefix + tableName);
+	}
+
+	private void dropAllTables(SQLiteDatabase db) {
+		dropAllTablesWithPrefix(db, "");
+	}
+}