Android SqliteAssetHelper

Question

前面有一篇 blog 提到在 Android 开发中我们一般有两种方式使用 sqlite，第一种是在 application 中手动创建，然后程序中管理数据库的升级；第二种是预先放置一份 sqlite 数据库，程序中使用的时候仅是查询功能，并不会涉及到更改、删除操作。这种情况下多是起到提供一个基础资源库的作用，如预先放置的一些提醒励志语句、以及预先放置的一些食物数据等。今天就来总结下如何管理 assets 文件夹下的 sqlite 数据库。

使用场景与策略

数据库管理一般都会伴随着升级，试想放在 assets 文件夹下的数据库升级是该怎么处理呢？

首先放在 assets 文件夹里的 sqlite 文件一定是我们事先经过处理好的数据库，包括里面的数据也是我们人为的生成的，如我们的 app 其实就是根据后端 mysql 转换成的 sqlite，但是后端的数据是会不断完善以及不断变化的，所以伴随着我们的 app 端的 sqlite 也会是不断完善的，我想这种情况下大多数的策略是后端重新生成一份最新的 sqlite 文件，然后等到 app 发布的时候直接拷贝并覆盖原来旧的数据库。基于这种场景参考了 github 上一些资料，定义了一个 SqliteAssetHelper 来管理数据库的升级。下面看代码：

public class SQLiteAssetHelper extends SQLiteOpenHelper {
	static final String TAG = SQLiteAssetHelper.class.getSimpleName();
	private static final String ASSET_DB_PATH = "databases";
	
	private final Context mContext;
	private final String mName;
	private final CursorFactory mFactory;
	private final int mNewVersion;

	private SQLiteDatabase mDatabase = null;
	private boolean mIsInitializing = false;

	private String mDatabasePath;
	private String mArchivePath;

	public SQLiteAssetHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
		
		if (version < 1) throw new IllegalArgumentException("Version must be >= 1, was " + version);
		if (name == null) throw new IllegalArgumentException("Databse name cannot be null");
		
		mContext = context;
		mName = name;
		mFactory = factory;
		mNewVersion = version;
		
		mArchivePath = ASSET_DB_PATH + "/" + name + ".zip";
		mDatabasePath = context.getApplicationInfo().dataDir + "/databases";
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// do nothing - createOrOpenDatabase() is called in 
		// getWritableDatabase() to handle database creation.
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	
	@Override
	public synchronized SQLiteDatabase getWritableDatabase() {
		if (mDatabase != null && mDatabase.isOpen() && !mDatabase.isReadOnly()) {
			return mDatabase;  // The database is already open for business
		}

		if (mIsInitializing) {
			throw new IllegalStateException("getWritableDatabase called recursively");
		}

		// If we have a read-only database open, someone could be using it
		// (though they shouldn't), which would cause a lock to be held on
		// the file, and our attempts to open the database read-write would
		// fail waiting for the file lock.  To prevent that, we acquire the
		// lock on the read-only database, which shuts out other users.

		boolean success = false;
		SQLiteDatabase db = null;
		//if (mDatabase != null) mDatabase.lock();
		try {
			mIsInitializing = true;
			db = createOrOpenDatabase(false);

			int version = db.getVersion();
			Log.e(TAG, "old version:" + version);
			Log.e(TAG, "new version:" + mNewVersion);
			
			// do force upgrade
			if (version != 0 && version < mNewVersion) {
				db = createOrOpenDatabase(true);
				version = db.getVersion();
			}
			
			onOpen(db);
			success = true;
			return db;
		} finally {
			mIsInitializing = false;
			if (success) {
				if (mDatabase != null) {
					try { mDatabase.close(); } catch (Exception e) { }
					//mDatabase.unlock();
				}
				mDatabase = db;
			} else {
				//if (mDatabase != null) mDatabase.unlock();
				if (db != null) db.close();
			}
		}

	}
	
	@Override
	public synchronized SQLiteDatabase getReadableDatabase() {
		if (mDatabase != null && mDatabase.isOpen()) {
			return mDatabase;  // The database is already open for business
		}

		if (mIsInitializing) {
			throw new IllegalStateException("getReadableDatabase called recursively");
		}

		try {
			return getWritableDatabase();
		} catch (SQLiteException e) {
			if (mName == null) throw e;  // Can't open a temp database read-only!
			Log.e(TAG, "Couldn't open " + mName + " for writing (will try read-only):", e);
		}

		SQLiteDatabase db = null;
		try {
			mIsInitializing = true;
			String path = mContext.getDatabasePath(mName).getPath();
			db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READONLY);
			if (db.getVersion() != mNewVersion) {
				throw new SQLiteException("Can't upgrade read-only database from version " +
						db.getVersion() + " to " + mNewVersion + ": " + path);
			}

			onOpen(db);
			Log.w(TAG, "Opened " + mName + " in read-only mode");
			mDatabase = db;
			return mDatabase;
		} finally {
			mIsInitializing = false;
			if (db != null && db != mDatabase) db.close();
		}
	}
	
	private SQLiteDatabase createOrOpenDatabase(boolean force) throws SQLiteAssetException {		
		SQLiteDatabase db = returnDatabase();
		if (db != null) {
			// database already exists
			if (force) {
				Log.w(TAG, "forcing database upgrade!");
				copyDatabaseFromAssets();
				db = returnDatabase();
				db.setVersion(mNewVersion);
			}
			return db;
		} else {
			// database does not exist, copy it from assets and return it
			copyDatabaseFromAssets();
			db = returnDatabase();
			db.setVersion(mNewVersion);
			return db;
		}
	}
	
	private SQLiteDatabase returnDatabase(){
		try {
			SQLiteDatabase db = SQLiteDatabase.openDatabase(mDatabasePath + "/" + mName, mFactory, SQLiteDatabase.OPEN_READWRITE);
			Log.i(TAG, "successfully opened database " + mName);
			return db;
		} catch (SQLiteException e) {
			Log.w(TAG, "could not open database " + mName + " - " + e.getMessage());
			return null;
		}
	}

	private void copyDatabaseFromAssets() throws SQLiteAssetException {
		Log.e(TAG, "copying database from assets...");

		try {
			InputStream zipFileStream = mContext.getAssets().open(mArchivePath);
			File f = new File(mDatabasePath + "/");
			if (!f.exists()) { f.mkdir(); }

			ZipInputStream zis = getFileFromZip(zipFileStream);
			if (zis == null) {
				throw new SQLiteAssetException("Archive is missing a SQLite database file"); 
			}
			writeExtractedFileToDisk(zis, new FileOutputStream(mDatabasePath + "/" + mName));

			Log.e(TAG, "database copy complete");

		} catch (FileNotFoundException fe) {
			SQLiteAssetException se = new SQLiteAssetException("Missing " + mArchivePath + " file in assets or target folder not writable");
			se.setStackTrace(fe.getStackTrace());
			throw se;
		} catch (IOException e) {
			SQLiteAssetException se = new SQLiteAssetException("Unable to extract " + mArchivePath + " to data directory");
			se.setStackTrace(e.getStackTrace());
			throw se;
		}
	}
	
	private void writeExtractedFileToDisk(ZipInputStream zin, OutputStream outs) throws IOException {
		byte[] buffer = new byte[1024];
		int length;
		while ((length = zin.read(buffer))>0){
			outs.write(buffer, 0, length);
		}
		outs.flush();
		outs.close();
		zin.close();
	}

	private ZipInputStream getFileFromZip(InputStream zipFileStream) throws FileNotFoundException, IOException {
		ZipInputStream zis = new ZipInputStream(zipFileStream);
		ZipEntry ze = null;
		while ((ze = zis.getNextEntry()) != null) {
			Log.e(TAG, "extracting file: '" + ze.getName() + "'...");
			return zis;
		}
		return null;
	}
}

使用方法

使用时只需把实现准备好的 sqlite 文件压缩成 zip 包放在 assets 文件夹下的 databases 目录，然后定义一个 Helper 继承自 SqliteAssetHelper，如下代码：

public class DBHelper extends SQLiteAssetHelper {
	
	private static final String DATABASE_NAME = "bhdb.sqlite";
	private static final int DATABASE_VERSION = 1;

	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
}

升级的时候只需要改 DATABASE_VERSION 的值就好了。

优点与缺点

• 优点：管理 assets 文件夹下的数据库简单方便，把 sqlite 文件以 zip 包的形式放在程序中，减少包大小。
• 缺点：每次更新只能覆盖原来的数据，及时是少量数据更新也是这种方式。如果少量数据更新的话打算以执行 sql 的方式来更新数据那可以参考下面这个项目：android-sqlite-asset-helper