在Android中使用SQLite的附加数据库（Attach DB）

上一篇文章 SQLite实用武器库（4）附加数据库（Attach DB）介绍了SQLite层面如何使用附加数据库。本文简单介绍在Android APP开发中如何使用。本文Android源代码基于Android6.0。
Attach/Detach DB操作是通过SQL语句实现，所以还是通过SQLiteDatabase.execSQL()实现。

    /**     * Execute a single SQL statement that is NOT a SELECT     * or any other SQL statement that returns data.     * <p>     * It has no means to return any data (such as the number of affected rows).     * Instead, you're encouraged to use {@link #insert(String, String, ContentValues)},     * {@link #update(String, ContentValues, String, String[])}, et al, when possible.     * </p>     * <p>     * When using {@link #enableWriteAheadLogging()}, journal_mode is     * automatically managed by this class. So, do not set journal_mode     * using "PRAGMA journal_mode'<value>" statement if your app is using     * {@link #enableWriteAheadLogging()}     * </p>     *     * @param sql the SQL statement to be executed. Multiple statements separated by semicolons are     * not supported.     * @throws SQLException if the SQL string is invalid     */    public void execSQL(String sql) throws SQLException

具体使用例子：

        db.execSQL(String.format("ATTACH DATABASE \'%s\' AS %s", path, name));        db.execSQL(String.format("DETACH DATABASE %s", name));

这里面有几个点需要注意：

1. 数据库路径问题

数据库文件路径（path）需要一个系统运行时SQLite能够识别的路径，而不能简单地传入db文件名（譬如 “mydatas.db”）。实现方法类似Android的SQLite数据库工具类SQLiteOpenHelper。我们看一下SQLiteOpenHelper的典型用法，构造方法中传入一个db文件名

    /**     * Create a helper object to create, open, and/or manage a database.     * This method always returns very quickly.  The database is not actually     * created or opened until one of {@link #getWritableDatabase} or     * {@link #getReadableDatabase} is called.     *     * @param context to use to open or create the database     * @param name of the database file, or null for an in-memory database     * @param factory to use for creating cursor objects, or null for the default     * @param version number of the database (starting at 1); if the database is older,     *     {@link #onUpgrade} will be used to upgrade the database; if the database is     *     newer, {@link #onDowngrade} will be used to downgrade the database     */    public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {        this(context, name, factory, version, null);    }    /**     * Create a helper object to create, open, and/or manage a database.     * The database is not actually created or opened until one of     * {@link #getWritableDatabase} or {@link #getReadableDatabase} is called.     *     * <p>Accepts input param: a concrete instance of {@link DatabaseErrorHandler} to be     * used to handle corruption when sqlite reports database corruption.</p>     *     * @param context to use to open or create the database     * @param name of the database file, or null for an in-memory database     * @param factory to use for creating cursor objects, or null for the default     * @param version number of the database (starting at 1); if the database is older,     *     {@link #onUpgrade} will be used to upgrade the database; if the database is     *     newer, {@link #onDowngrade} will be used to downgrade the database     * @param errorHandler the {@link DatabaseErrorHandler} to be used when sqlite reports database     * corruption, or null to use the default error handler.     */    public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version,            DatabaseErrorHandler errorHandler) {        if (version < 1) throw new IllegalArgumentException("Version must be >= 1, was " + version);        mContext = context;        mName = name;        mFactory = factory;        mNewVersion = version;        mErrorHandler = errorHandler;    }

我们知道，其中的参数name传入的是一个简单的文件名，类似”mydatas.db”，是不需要APP开发者考虑文件路径问题的。那么这个路径是如何转化为系统运行时SQLite可以识别的完整路径呢？在SQLiteOpenHelper的关键代码——打开数据库的方法里面

    private SQLiteDatabase getDatabaseLocked(boolean writable) {        if (mDatabase != null) {            if (!mDatabase.isOpen()) {                // Darn!  The user closed the database by calling mDatabase.close().                mDatabase = null;            } else if (!writable || !mDatabase.isReadOnly()) {                // The database is already open for business.                return mDatabase;            }        }        if (mIsInitializing) {            throw new IllegalStateException("getDatabase called recursively");        }        SQLiteDatabase db = mDatabase;        try {            mIsInitializing = true;            if (db != null) {                if (writable && db.isReadOnly()) {                    db.reopenReadWrite();                }            } else if (mName == null) {                db = SQLiteDatabase.create(null);            } else {                try {                    if (DEBUG_STRICT_READONLY && !writable) {                        final String path = mContext.getDatabasePath(mName).getPath();                        db = SQLiteDatabase.openDatabase(path, mFactory,                                SQLiteDatabase.OPEN_READONLY, mErrorHandler);                    } else {                        db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?                                Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,                                mFactory, mErrorHandler);                    }                } catch (SQLiteException ex) {                    if (writable) {                        throw ex;                    }                    Log.e(TAG, "Couldn't open " + mName                            + " for writing (will try read-only):", ex);                    final String path = mContext.getDatabasePath(mName).getPath();                    db = SQLiteDatabase.openDatabase(path, mFactory,                            SQLiteDatabase.OPEN_READONLY, mErrorHandler);                }            }            onConfigure(db);            final int version = db.getVersion();            if (version != mNewVersion) {                if (db.isReadOnly()) {                    throw new SQLiteException("Can't upgrade read-only database from version " +                            db.getVersion() + " to " + mNewVersion + ": " + mName);                }                db.beginTransaction();                try {                    if (version == 0) {                        onCreate(db);                    } else {                        if (version > mNewVersion) {                            onDowngrade(db, version, mNewVersion);                        } else {                            onUpgrade(db, version, mNewVersion);                        }                    }                    db.setVersion(mNewVersion);                    db.setTransactionSuccessful();                } finally {                    db.endTransaction();                }            }            onOpen(db);            if (db.isReadOnly()) {                Log.w(TAG, "Opened " + mName + " in read-only mode");            }            mDatabase = db;            return db;        } finally {            mIsInitializing = false;            if (db != null && db != mDatabase) {                db.close();            }        }    }

我们看到一段关键的代码：

                try {                    if (DEBUG_STRICT_READONLY && !writable) {                        final String path = mContext.getDatabasePath(mName).getPath();                        db = SQLiteDatabase.openDatabase(path, mFactory,                                SQLiteDatabase.OPEN_READONLY, mErrorHandler);                    } else {                        db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?                                Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,                                mFactory, mErrorHandler);                    }                } catch (SQLiteException ex) {                    if (writable) {                        throw ex;                    }                    Log.e(TAG, "Couldn't open " + mName                            + " for writing (will try read-only):", ex);                    final String path = mContext.getDatabasePath(mName).getPath();                    db = SQLiteDatabase.openDatabase(path, mFactory,                            SQLiteDatabase.OPEN_READONLY, mErrorHandler);                }

OK，看到系统提供的获取系统运行时SQLite可以识别的完整数据库文件路径的方法：

Context.getDatabasePath(mName).getPath()

通过这个方法得到路径，作为path参数放入Attach DB的SQL语句中，可以成功Attach，实测可用。

2.别名唯一

Attach进来的数据库，是以as后面的参数（Attach数据库别名）作为ID，不能重复使用。如果Attach某个数据库，但别名在当前已经被其他Attach数据库所占，那么是无法Attach成功的。这就需要事先有一个检查。
SQLiteDatabase提供了API查看当前所有的Attach数据库信息：

    /**     * Returns list of full pathnames of all attached databases including the main database     * by executing 'pragma database_list' on the database.     *     * @return ArrayList of pairs of (database name, database file path) or null if the database     * is not open.     */    public List<Pair<String, String>> getAttachedDbs()

可以使用这个API做检查，如果发现别名已经占用，可以先Detach，再Attach。