Android SqliteAssetHelper

(You can suggest changes to this post.)

前面有一篇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