diff options
author | Vasu Nori <vnori@google.com> | 2010-04-26 23:33:39 -0700 |
---|---|---|
committer | Vasu Nori <vnori@google.com> | 2010-06-14 14:50:03 -0700 |
commit | 6c354da9436e946708fc3f3a1c0d18b18bbfdf43 (patch) | |
tree | 290fc21e11d859eea4d93517f97e314f5f81e9b6 | |
parent | c465f9c4003c5d1806954f81c50e6f9d56adecc4 (diff) | |
download | frameworks_base-6c354da9436e946708fc3f3a1c0d18b18bbfdf43.zip frameworks_base-6c354da9436e946708fc3f3a1c0d18b18bbfdf43.tar.gz frameworks_base-6c354da9436e946708fc3f3a1c0d18b18bbfdf43.tar.bz2 |
read old version of data and use multiple connections to db
cts tests are in Change-Id: Ifcc89b4ff484c7c810fd2d450ded212a43360dda
dependency on: Change-Id: I938c42afc3fb50f5296d01c55ffcf4a102d8b0cb
1. Use sqlite's work-in-progress writeahead logging feature to read old
versions of data and thus increase concurrency of readers
even when there is a writer on the database
2. New API executeQueriesInParallel() sets up a database connecion pool
automatically created and managed by sqlite java layer
3. To increase reader concurrency, add an option to do BEGIN IMMEDIATE xaction
instead of BEGIN EXCLUSIVE
Change-Id: I3ce55a8a7cba538f01f731736e7de8ae1e2a8a1f
-rw-r--r-- | api/current.xml | 48 | ||||
-rw-r--r-- | core/java/android/database/sqlite/DatabaseConnectionPool.java | 309 | ||||
-rw-r--r-- | core/java/android/database/sqlite/SQLiteDatabase.java | 326 | ||||
-rw-r--r-- | core/jni/android_database_SQLiteDatabase.cpp | 43 | ||||
-rw-r--r-- | tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java | 274 |
5 files changed, 947 insertions, 53 deletions
diff --git a/api/current.xml b/api/current.xml index 0457940..3931e9e 100644 --- a/api/current.xml +++ b/api/current.xml @@ -60294,6 +60294,17 @@ visibility="public" > </method> +<method name="beginTransactionNonExclusive" + return="void" + abstract="false" + native="false" + synchronized="false" + static="false" + final="false" + deprecated="not deprecated" + visibility="public" +> +</method> <method name="beginTransactionWithListener" return="void" abstract="false" @@ -60307,6 +60318,19 @@ <parameter name="transactionListener" type="android.database.sqlite.SQLiteTransactionListener"> </parameter> </method> +<method name="beginTransactionWithListenerNonExclusive" + return="void" + abstract="false" + native="false" + synchronized="false" + static="false" + final="false" + deprecated="not deprecated" + visibility="public" +> +<parameter name="transactionListener" type="android.database.sqlite.SQLiteTransactionListener"> +</parameter> +</method> <method name="close" return="void" abstract="false" @@ -60363,6 +60387,17 @@ <parameter name="whereArgs" type="java.lang.String[]"> </parameter> </method> +<method name="enableWriteAheadLogging" + return="void" + abstract="false" + native="false" + synchronized="true" + static="false" + final="false" + deprecated="not deprecated" + visibility="public" +> +</method> <method name="endTransaction" return="void" abstract="false" @@ -60938,6 +60973,19 @@ <exception name="SQLException" type="android.database.SQLException"> </exception> </method> +<method name="setConnectionPoolSize" + return="void" + abstract="false" + native="false" + synchronized="true" + static="false" + final="false" + deprecated="not deprecated" + visibility="public" +> +<parameter name="size" type="int"> +</parameter> +</method> <method name="setLocale" return="void" abstract="false" diff --git a/core/java/android/database/sqlite/DatabaseConnectionPool.java b/core/java/android/database/sqlite/DatabaseConnectionPool.java new file mode 100644 index 0000000..3f7018f --- /dev/null +++ b/core/java/android/database/sqlite/DatabaseConnectionPool.java @@ -0,0 +1,309 @@ +/* + * Copyright (C) 20010 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package android.database.sqlite; + +import android.os.SystemClock; +import android.util.Log; + +import java.util.ArrayList; +import java.util.HashSet; +import java.util.Random; + +/** + * A connection pool to be used by readers. + * Note that each connection can be used by only one reader at a time. + */ +/* package */ class DatabaseConnectionPool { + + private static final String TAG = "DatabaseConnectionPool"; + + /** The default connection pool size. It is set based on the amount of memory the device has. + * TODO: set this with 'a system call' which returns the amount of memory the device has + */ + private static final int DEFAULT_CONNECTION_POOL_SIZE = 1; + + /** the pool size set for this {@link SQLiteDatabase} */ + private volatile int mMaxPoolSize = DEFAULT_CONNECTION_POOL_SIZE; + + /** The connection pool objects are stored in this member. + * TODO: revisit this data struct as the number of pooled connections increase beyond + * single-digit values. + */ + private final ArrayList<PoolObj> mPool = new ArrayList<PoolObj>(mMaxPoolSize); + + /** the main database connection to which this connection pool is attached */ + private final SQLiteDatabase mParentDbObj; + + /* package */ DatabaseConnectionPool(SQLiteDatabase db) { + this.mParentDbObj = db; + if (Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, "Max Pool Size: " + mMaxPoolSize); + } + } + + /** + * close all database connections in the pool - even if they are in use! + */ + /* package */ void close() { + synchronized(mParentDbObj) { + for (int i = mPool.size() - 1; i >= 0; i--) { + mPool.get(i).mDb.close(); + } + mPool.clear(); + } + } + + /** + * get a free connection from the pool + * + * @param sql if not null, try to find a connection inthe pool which already has cached + * the compiled statement for this sql. + * @return the Database connection that the caller can use + */ + /* package */ SQLiteDatabase get(String sql) { + if (Log.isLoggable(TAG, Log.DEBUG)) { + doAsserts(); + } + + SQLiteDatabase db = null; + PoolObj poolObj = null; + synchronized(mParentDbObj) { + if (getFreePoolSize() == 0) { + if (mMaxPoolSize == mPool.size()) { + // maxed out. can't open any more connections. + // let the caller wait on one of the pooled connections + if (mMaxPoolSize == 1) { + poolObj = mPool.get(0); + } else { + // get a random number between 0 and (mMaxPoolSize-1) + poolObj = mPool.get( + new Random(SystemClock.elapsedRealtime()).nextInt(mMaxPoolSize-1)); + } + db = poolObj.mDb; + } else { + // create a new connection and add it to the pool, since we haven't reached + // max pool size allowed + int poolSize = getPoolSize(); + db = mParentDbObj.createPoolConnection((short)(poolSize + 1)); + poolObj = new PoolObj(db); + mPool.add(poolSize, poolObj); + } + } else { + // there are free connections available. pick one + for (int i = mPool.size() - 1; i >= 0; i--) { + poolObj = mPool.get(i); + if (!poolObj.isFree()) { + continue; + } + // it is free - but does its database object already have the given sql in its + // statement-cache? + db = poolObj.mDb; + if (sql == null || db.isSqlInStatementCache(sql)) { + // found a free connection we can use + break; + } + // haven't found a database object which has the given sql in its + // statement-cache + } + } + + assert poolObj != null; + assert poolObj.mDb == db; + + poolObj.acquire(); + } + + if (Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, "END get-connection: " + toString() + poolObj.toString()); + } + return db; + // TODO if a thread acquires a connection and dies without releasing the connection, then + // there could be a connection leak. + } + + /** + * release the given database connection back to the pool. + * @param db the connection to be released + */ + /* package */ void release(SQLiteDatabase db) { + PoolObj poolObj; + synchronized(mParentDbObj) { + if (Log.isLoggable(TAG, Log.DEBUG)) { + assert db.mConnectionNum > 0; + doAsserts(); + assert mPool.get(db.mConnectionNum - 1).mDb == db; + } + + poolObj = mPool.get(db.mConnectionNum - 1); + + if (Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, "BEGIN release-conn: " + toString() + poolObj.toString()); + } + + if (poolObj.isFree()) { + throw new IllegalStateException("Releasing object already freed: " + + db.mConnectionNum); + } + + poolObj.release(); + } + + if (Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, "END release-conn: " + toString() + poolObj.toString()); + } + } + + /** + * Returns a list of all database connections in the pool (both free and busy connections). + * This method is used when "adb bugreport" is done. + */ + /* package */ ArrayList<SQLiteDatabase> getConnectionList() { + ArrayList<SQLiteDatabase> list = new ArrayList<SQLiteDatabase>(); + synchronized(mParentDbObj) { + for (int i = mPool.size() - 1; i >= 0; i--) { + list.add(mPool.get(i).mDb); + } + } + return list; + } + + /* package */ int getPoolSize() { + synchronized(mParentDbObj) { + return mPool.size(); + } + } + + private int getFreePoolSize() { + int count = 0; + for (int i = mPool.size() - 1; i >= 0; i--) { + if (mPool.get(i).isFree()) { + count++; + } + } + return count++; + } + + @Override + public String toString() { + return "db: " + mParentDbObj.getPath() + + ", threadid = " + Thread.currentThread().getId() + + ", totalsize = " + mPool.size() + ", #free = " + getFreePoolSize() + + ", maxpoolsize = " + mMaxPoolSize; + } + + private void doAsserts() { + for (int i = 0; i < mPool.size(); i++) { + mPool.get(i).verify(); + assert mPool.get(i).mDb.mConnectionNum == (i + 1); + } + } + + /* package */ void setMaxPoolSize(int size) { + synchronized(mParentDbObj) { + mMaxPoolSize = size; + } + } + + /* package */ int getMaxPoolSize() { + synchronized(mParentDbObj) { + return mMaxPoolSize; + } + } + + /** + * represents objects in the connection pool. + */ + private static class PoolObj { + + private final SQLiteDatabase mDb; + private boolean mFreeBusyFlag = FREE; + private static final boolean FREE = true; + private static final boolean BUSY = false; + + /** the number of threads holding this connection */ + // @GuardedBy("this") + private int mNumHolders = 0; + + /** contains the threadIds of the threads holding this connection. + * used for debugging purposes only. + */ + // @GuardedBy("this") + private HashSet<Long> mHolderIds = new HashSet<Long>(); + + public PoolObj(SQLiteDatabase db) { + mDb = db; + } + + private synchronized void acquire() { + if (Log.isLoggable(TAG, Log.DEBUG)) { + assert isFree(); + long id = Thread.currentThread().getId(); + assert !mHolderIds.contains(id); + mHolderIds.add(id); + } + + mNumHolders++; + mFreeBusyFlag = BUSY; + } + + private synchronized void release() { + if (Log.isLoggable(TAG, Log.DEBUG)) { + long id = Thread.currentThread().getId(); + assert mHolderIds.size() == mNumHolders; + assert mHolderIds.contains(id); + mHolderIds.remove(id); + } + + mNumHolders--; + if (mNumHolders == 0) { + mFreeBusyFlag = FREE; + } + } + + private synchronized boolean isFree() { + if (Log.isLoggable(TAG, Log.DEBUG)) { + verify(); + } + return (mFreeBusyFlag == FREE); + } + + private synchronized void verify() { + if (mFreeBusyFlag == FREE) { + assert mNumHolders == 0; + } else { + assert mNumHolders > 0; + } + } + + @Override + public String toString() { + StringBuilder buff = new StringBuilder(); + buff.append(", conn # "); + buff.append(mDb.mConnectionNum); + buff.append(", mCountHolders = "); + synchronized(this) { + buff.append(mNumHolders); + buff.append(", freeBusyFlag = "); + buff.append(mFreeBusyFlag); + for (Long l : mHolderIds) { + buff.append(", id = " + l); + } + } + return buff.toString(); + } + } +} diff --git a/core/java/android/database/sqlite/SQLiteDatabase.java b/core/java/android/database/sqlite/SQLiteDatabase.java index 4fdc46d..2fa2e99 100644 --- a/core/java/android/database/sqlite/SQLiteDatabase.java +++ b/core/java/android/database/sqlite/SQLiteDatabase.java @@ -330,6 +330,17 @@ public class SQLiteDatabase extends SQLiteClosable { * */ private final DatabaseErrorHandler mErrorHandler; + /** The Database connection pool {@link DatabaseConnectionPool}. + * Visibility is package-private for testing purposes. otherwise, private visibility is enough. + */ + /* package */ volatile DatabaseConnectionPool mConnectionPool = null; + + /** Each database connection handle in the pool is assigned a number 1..N, where N is the + * size of the connection pool. + * The main connection handle to which the pool is attached is assigned a value of 0. + */ + /* package */ final short mConnectionNum; + /** * @param closable */ @@ -504,7 +515,31 @@ public class SQLiteDatabase extends SQLiteClosable { * </pre> */ public void beginTransaction() { - beginTransactionWithListener(null /* transactionStatusCallback */); + beginTransaction(null /* transactionStatusCallback */, true); + } + + /** + * Begins a transaction in IMMEDIATE mode. Transactions can be nested. When + * the outer transaction is ended all of the work done in that transaction + * and all of the nested transactions will be committed or rolled back. The + * changes will be rolled back if any transaction is ended without being + * marked as clean (by calling setTransactionSuccessful). Otherwise they + * will be committed. + * <p> + * Here is the standard idiom for transactions: + * + * <pre> + * db.beginTransactionNonExclusive(); + * try { + * ... + * db.setTransactionSuccessful(); + * } finally { + * db.endTransaction(); + * } + * </pre> + */ + public void beginTransactionNonExclusive() { + beginTransaction(null /* transactionStatusCallback */, false); } /** @@ -533,6 +568,40 @@ public class SQLiteDatabase extends SQLiteClosable { * {@link #yieldIfContendedSafely}. */ public void beginTransactionWithListener(SQLiteTransactionListener transactionListener) { + beginTransaction(transactionListener, true); + } + + /** + * Begins a transaction in IMMEDIATE mode. Transactions can be nested. When + * the outer transaction is ended all of the work done in that transaction + * and all of the nested transactions will be committed or rolled back. The + * changes will be rolled back if any transaction is ended without being + * marked as clean (by calling setTransactionSuccessful). Otherwise they + * will be committed. + * <p> + * Here is the standard idiom for transactions: + * + * <pre> + * db.beginTransactionWithListenerNonExclusive(listener); + * try { + * ... + * db.setTransactionSuccessful(); + * } finally { + * db.endTransaction(); + * } + * </pre> + * + * @param transactionListener listener that should be notified when the + * transaction begins, commits, or is rolled back, either + * explicitly or by a call to {@link #yieldIfContendedSafely}. + */ + public void beginTransactionWithListenerNonExclusive( + SQLiteTransactionListener transactionListener) { + beginTransaction(transactionListener, false); + } + + private void beginTransaction(SQLiteTransactionListener transactionListener, + boolean exclusive) { verifyDbIsOpen(); lockForced(); boolean ok = false; @@ -552,7 +621,11 @@ public class SQLiteDatabase extends SQLiteClosable { // This thread didn't already have the lock, so begin a database // transaction now. - execSQL("BEGIN EXCLUSIVE;"); + if (exclusive) { + execSQL("BEGIN EXCLUSIVE;"); + } else { + execSQL("BEGIN IMMEDIATE;"); + } mTransactionListener = transactionListener; mTransactionIsSuccessful = true; mInnerTransactionIsSuccessful = false; @@ -604,6 +677,18 @@ public class SQLiteDatabase extends SQLiteClosable { } if (mTransactionIsSuccessful) { execSQL(COMMIT_SQL); + // if write-ahead logging is used, we have to take care of checkpoint. + // TODO: should applications be given the flexibility of choosing when to + // trigger checkpoint? + // for now, do checkpoint after every COMMIT because that is the fastest + // way to guarantee that readers will see latest data. + // but this is the slowest way to run sqlite with in write-ahead logging mode. + if (this.mConnectionPool != null) { + execSQL("PRAGMA wal_checkpoint;"); + if (SQLiteDebug.DEBUG_SQL_STATEMENTS) { + Log.i(TAG, "PRAGMA wal_Checkpoint done"); + } + } } else { try { execSQL("ROLLBACK;"); @@ -859,22 +944,8 @@ public class SQLiteDatabase extends SQLiteClosable { */ public static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags, DatabaseErrorHandler errorHandler) { - SQLiteDatabase sqliteDatabase = new SQLiteDatabase(path, factory, flags, errorHandler); - - try { - // Open the database. - sqliteDatabase.openDatabase(path, flags); - if (SQLiteDebug.DEBUG_SQL_STATEMENTS) { - sqliteDatabase.enableSqlTracing(path); - } - if (SQLiteDebug.DEBUG_SQL_TIME) { - sqliteDatabase.enableSqlProfiling(path); - } - } catch (SQLiteDatabaseCorruptException e) { - // Database is not even openable. - errorHandler.onCorruption(sqliteDatabase); - sqliteDatabase = new SQLiteDatabase(path, factory, flags, errorHandler); - } + SQLiteDatabase sqliteDatabase = openDatabase(path, factory, flags, errorHandler, + (short) 0 /* the main connection handle */); // set sqlite pagesize to mBlockSize if (sBlockSize == 0) { @@ -896,14 +967,26 @@ public class SQLiteDatabase extends SQLiteClosable { return sqliteDatabase; } - private void openDatabase(String path, int flags) { - // Open the database. - dbopen(path, flags); + private static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags, + DatabaseErrorHandler errorHandler, short connectionNum) { + SQLiteDatabase db = new SQLiteDatabase(path, factory, flags, errorHandler, connectionNum); try { - setLocale(Locale.getDefault()); - } catch (RuntimeException e) { - Log.e(TAG, "Failed to setLocale(). closing the database", e); - dbclose(); + // Open the database. + db.dbopen(path, flags); + db.setLocale(Locale.getDefault()); + if (SQLiteDebug.DEBUG_SQL_STATEMENTS) { + db.enableSqlTracing(path, connectionNum); + } + if (SQLiteDebug.DEBUG_SQL_TIME) { + db.enableSqlProfiling(path, connectionNum); + } + return db; + } catch (SQLiteDatabaseCorruptException e) { + db.mErrorHandler.onCorruption(db); + return SQLiteDatabase.openDatabase(path, factory, flags, errorHandler); + } catch (SQLiteException e) { + Log.e(TAG, "Failed to open the database. closing it.", e); + db.close(); throw e; } } @@ -923,10 +1006,7 @@ public class SQLiteDatabase extends SQLiteClosable { } /** - * same as {@link #openOrCreateDatabase(String, CursorFactory)} except for an additional param - * errorHandler. - * @param errorHandler the {@link DatabaseErrorHandler} obj to be used when database - * corruption is detected on the database. + * Equivalent to openDatabase(path, factory, CREATE_IF_NECESSARY, errorHandler). */ public static SQLiteDatabase openOrCreateDatabase(String path, CursorFactory factory, DatabaseErrorHandler errorHandler) { @@ -963,6 +1043,9 @@ public class SQLiteDatabase extends SQLiteClosable { closePendingStatements(); // close this database instance - regardless of its reference count value onAllReferencesReleased(); + if (mConnectionPool != null) { + mConnectionPool.close(); + } } finally { unlock(); } @@ -1175,11 +1258,18 @@ public class SQLiteDatabase extends SQLiteClosable { */ public SQLiteStatement compileStatement(String sql) throws SQLException { verifyDbIsOpen(); - lock(); + String prefixSql = sql.trim().substring(0, 6); + SQLiteDatabase db = this; + // get a pooled database connection handle to use, if this is a query + if (prefixSql.equalsIgnoreCase("SELECT")) { + db = getDbConnection(sql); + } + db.lock(); try { - return new SQLiteStatement(this, sql); + return new SQLiteStatement(db, sql); } finally { - unlock(); + releaseDbConnection(db); + db.unlock(); } } @@ -1376,7 +1466,8 @@ public class SQLiteDatabase extends SQLiteClosable { timeStart = System.currentTimeMillis(); } - SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable); + SQLiteDatabase db = getDbConnection(sql); + SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(db, sql, editTable); Cursor cursor = null; try { @@ -1402,6 +1493,7 @@ public class SQLiteDatabase extends SQLiteClosable { : "<null>") + ", count is " + count); } } + releaseDbConnection(db); } return cursor; } @@ -1872,9 +1964,11 @@ public class SQLiteDatabase extends SQLiteClosable { * exists, mFlags will be updated appropriately. * @param errorHandler The {@link DatabaseErrorHandler} to be used when sqlite reports database * corruption. may be NULL. + * @param connectionNum 0 for main database connection handle. 1..N for pooled database + * connection handles. */ private SQLiteDatabase(String path, CursorFactory factory, int flags, - DatabaseErrorHandler errorHandler) { + DatabaseErrorHandler errorHandler, short connectionNum) { if (path == null) { throw new IllegalArgumentException("path should not be null"); } @@ -1887,6 +1981,7 @@ public class SQLiteDatabase extends SQLiteClosable { // Set the DatabaseErrorHandler to be used when SQLite reports corruption. // If the caller sets errorHandler = null, then use default errorhandler. mErrorHandler = (errorHandler == null) ? new DefaultDatabaseErrorHandler() : errorHandler; + mConnectionNum = connectionNum; } /** @@ -2129,6 +2224,12 @@ public class SQLiteDatabase extends SQLiteClosable { mMaxSqlCacheSize = cacheSize; } + /* package */ boolean isSqlInStatementCache(String sql) { + synchronized (mCompiledQueries) { + return mCompiledQueries.containsKey(sql); + } + } + /* package */ void finalizeStatementLater(int id) { if (!isOpen()) { // database already closed. this statement will already have been finalized. @@ -2175,6 +2276,145 @@ public class SQLiteDatabase extends SQLiteClosable { return mClosedStatementIds; } + /** + * This method enables parallel execution of queries from multiple threads on the same database. + * It does this by opening multiple handles to the database and using a different + * database handle for each query. + * <p> + * If a transaction is in progress on one connection handle and say, a table is updated in the + * transaction, then query on the same table on another connection handle will block for the + * transaction to complete. But this method enables such queries to execute by having them + * return old version of the data from the table. Most often it is the data that existed in the + * table prior to the above transaction updates on that table. + * <p> + * Maximum number of simultaneous handles used to execute queries in parallel is + * dependent upon the device memory and possibly other properties. + * <p> + * After calling this method, execution of queries in parallel is enabled as long as this + * database handle is open. To disable execution of queries in parallel, database should + * be closed and reopened. + * <p> + * If a query is part of a transaction, then it is executed on the same database handle the + * transaction was begun. + * + * <p> + * If the database has any attached databases, then execution of queries in paralel is NOT + * possible. In such cases, {@link IllegalStateException} is thrown. + * <p> + * A typical way to use this method is the following: + * <pre> + * SQLiteDatabase db = SQLiteDatabase.openDatabase("db_filename", cursorFactory, + * CREATE_IF_NECESSARY, myDatabaseErrorHandler); + * db.enableWriteAheadLogging(); + * </pre> + * <p> + * Writers should use {@link #beginTransactionNonExclusive()} or + * {@link #beginTransactionWithListenerNonExclusive(SQLiteTransactionListener)} + * to start a trsnsaction. + * Non-exclusive mode allows database file to be in readable by threads executing queries. + * </p> + * + * @throws IllegalStateException thrown if the database has any attached databases. + */ + public synchronized void enableWriteAheadLogging() { + if (mConnectionPool != null) { + // connection pool already setup. + return; + } + + // make sure this database has NO attached databases because sqlite's write-ahead-logging + // doesn't work for databases with attached databases + if (getAttachedDbs().size() > 1) { + throw new IllegalStateException("this database: " + mPath + + " has attached databases. can't do execution of of queries in parallel."); + } + mConnectionPool = new DatabaseConnectionPool(this); + + // set journal_mode to WAL + String s = DatabaseUtils.stringForQuery(this, "PRAGMA journal_mode=WAL", null); + if (!s.equalsIgnoreCase("WAL")) { + Log.e(TAG, "setting journal_mode to WAL failed"); + } + } + + /** + * Sets the database connection handle pool size to the given value. + * Database connection handle pool is enabled when the app calls + * {@link #enableWriteAheadLogging()}. + * <p> + * The default connection handle pool is set by the system by taking into account various + * aspects of the device, such as memory, number of cores etc. It is recommended that + * applications use the default pool size set by the system. + * + * @param size the value the connection handle pool size should be set to. + */ + public synchronized void setConnectionPoolSize(int size) { + if (mConnectionPool == null) { + throw new IllegalStateException("connection pool not enabled"); + } + int i = mConnectionPool.getMaxPoolSize(); + if (size < i) { + throw new IllegalStateException( + "cannot set max pool size to a value less than the current max value(=" + + i + ")"); + } + mConnectionPool.setMaxPoolSize(size); + } + + /* package */ SQLiteDatabase createPoolConnection(short connectionNum) { + return openDatabase(mPath, mFactory, mFlags, mErrorHandler, connectionNum); + } + + private boolean isPooledConnection() { + return this.mConnectionNum > 0; + } + + private SQLiteDatabase getDbConnection(String sql) { + verifyDbIsOpen(); + + // use the current connection handle if + // 1. this is a pooled connection handle + // 2. OR, if this thread is in a transaction + // 3. OR, if there is NO connection handle pool setup + SQLiteDatabase db = null; + if (isPooledConnection() || + (inTransaction() && mLock.isHeldByCurrentThread()) || + (this.mConnectionPool == null)) { + db = this; + } else { + // get a connection handle from the pool + if (Log.isLoggable(TAG, Log.DEBUG)) { + assert mConnectionPool != null; + } + db = mConnectionPool.get(sql); + } + if (Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, "getDbConnection threadid = " + Thread.currentThread().getId() + + ", request on # " + mConnectionNum + + ", assigned # " + db.mConnectionNum + ", " + getPath()); + } + return db; + } + + private void releaseDbConnection(SQLiteDatabase db) { + // ignore this release call if + // 1. the database is closed + // 2. OR, if db is NOT a pooled connection handle + // 3. OR, if the database being released is same as 'this' (this condition means + // that we should always be releasing a pooled connection handle by calling this method + // from the 'main' connection handle + if (!isOpen() || !db.isPooledConnection() || (db == this)) { + return; + } + if (Log.isLoggable(TAG, Log.DEBUG)) { + assert isPooledConnection(); + assert mConnectionPool != null; + Log.d(TAG, "releaseDbConnection threadid = " + Thread.currentThread().getId() + + ", releasing # " + db.mConnectionNum + ", " + getPath()); + } + mConnectionPool.release(db); + } + static class ActiveDatabases { private static final ActiveDatabases activeDatabases = new ActiveDatabases(); private HashSet<WeakReference<SQLiteDatabase>> mActiveDatabases = @@ -2240,6 +2480,14 @@ public class SQLiteDatabase extends SQLiteClosable { db.mCompiledQueries.size())); } } + // if there are pooled connections, return the cache stats for them also. + if (db.mConnectionPool != null) { + for (SQLiteDatabase pDb : db.mConnectionPool.getConnectionList()) { + dbStatsList.add(new DbStats("(pooled # " + pDb.mConnectionNum + ") " + + lastnode, 0, 0, 0, pDb.mNumCacheHits, pDb.mNumCacheMisses, + pDb.mCompiledQueries.size())); + } + } } catch (SQLiteException e) { // ignore. we don't care about exceptions when we are taking adb // bugreport! @@ -2329,8 +2577,11 @@ public class SQLiteDatabase extends SQLiteClosable { * Native call to setup tracing of all SQL statements * * @param path the full path to the database + * @param connectionNum connection number: 0 - N, where the main database + * connection handle is numbered 0 and the connection handles in the connection + * pool are numbered 1..N. */ - private native void enableSqlTracing(String path); + private native void enableSqlTracing(String path, short connectionNum); /** * Native call to setup profiling of all SQL statements. @@ -2339,8 +2590,11 @@ public class SQLiteDatabase extends SQLiteClosable { * are executed. * * @param path the full path to the database + * @param connectionNum connection number: 0 - N, where the main database + * connection handle is numbered 0 and the connection handles in the connection + * pool are numbered 1..N. */ - private native void enableSqlProfiling(String path); + private native void enableSqlProfiling(String path, short connectionNum); /** * Native call to execute a raw SQL statement. {@link #lock} must be held diff --git a/core/jni/android_database_SQLiteDatabase.cpp b/core/jni/android_database_SQLiteDatabase.cpp index e4a050d..5a92193 100644 --- a/core/jni/android_database_SQLiteDatabase.cpp +++ b/core/jni/android_database_SQLiteDatabase.cpp @@ -63,8 +63,8 @@ enum { static jfieldID offset_db_handle; -static char *createStr(const char *path) { - int len = strlen(path); +static char *createStr(const char *path, short extra) { + int len = strlen(path) + extra; char *str = (char *)malloc(len + 1); strncpy(str, path, len); str[len] = NULL; @@ -85,7 +85,7 @@ static void registerLoggingFunc(const char *path) { } LOGV("Registering sqlite logging func \n"); - int err = sqlite3_config(SQLITE_CONFIG_LOG, &sqlLogger, (void *)createStr(path)); + int err = sqlite3_config(SQLITE_CONFIG_LOG, &sqlLogger, (void *)createStr(path, 0)); if (err != SQLITE_OK) { LOGE("sqlite_config failed error_code = %d. THIS SHOULD NEVER occur.\n", err); return; @@ -176,13 +176,17 @@ done: if (handle != NULL) sqlite3_close(handle); } -static char *getDatabaseName(JNIEnv* env, sqlite3 * handle, jstring databaseName) { +static char *getDatabaseName(JNIEnv* env, sqlite3 * handle, jstring databaseName, short connNum) { char const *path = env->GetStringUTFChars(databaseName, NULL); if (path == NULL) { LOGE("Failure in getDatabaseName(). VM ran out of memory?\n"); return NULL; // VM would have thrown OutOfMemoryError } - char *dbNameStr = createStr(path); + char *dbNameStr = createStr(path, 4); + if (connNum > 999) { // TODO: if number of pooled connections > 999, fix this line. + connNum = -1; + } + sprintf(dbNameStr + strlen(path), "|%03d", connNum); env->ReleaseStringUTFChars(databaseName, path); return dbNameStr; } @@ -192,10 +196,10 @@ static void sqlTrace(void *databaseName, const char *sql) { } /* public native void enableSqlTracing(); */ -static void enableSqlTracing(JNIEnv* env, jobject object, jstring databaseName) +static void enableSqlTracing(JNIEnv* env, jobject object, jstring databaseName, jshort connType) { sqlite3 * handle = (sqlite3 *)env->GetIntField(object, offset_db_handle); - sqlite3_trace(handle, &sqlTrace, (void *)getDatabaseName(env, handle, databaseName)); + sqlite3_trace(handle, &sqlTrace, (void *)getDatabaseName(env, handle, databaseName, connType)); } static void sqlProfile(void *databaseName, const char *sql, sqlite3_uint64 tm) { @@ -204,13 +208,13 @@ static void sqlProfile(void *databaseName, const char *sql, sqlite3_uint64 tm) { } /* public native void enableSqlProfiling(); */ -static void enableSqlProfiling(JNIEnv* env, jobject object, jstring databaseName) +static void enableSqlProfiling(JNIEnv* env, jobject object, jstring databaseName, jshort connType) { sqlite3 * handle = (sqlite3 *)env->GetIntField(object, offset_db_handle); - sqlite3_profile(handle, &sqlProfile, (void *)getDatabaseName(env, handle, databaseName)); + sqlite3_profile(handle, &sqlProfile, (void *)getDatabaseName(env, handle, databaseName, + connType)); } - /* public native void close(); */ static void dbclose(JNIEnv* env, jobject object) { @@ -251,7 +255,8 @@ static void native_execSQL(JNIEnv* env, jobject object, jstring sqlString) jsize sqlLen = env->GetStringLength(sqlString); if (sql == NULL || sqlLen == 0) { - jniThrowException(env, "java/lang/IllegalArgumentException", "You must supply an SQL string"); + jniThrowException(env, "java/lang/IllegalArgumentException", + "You must supply an SQL string"); return; } @@ -261,7 +266,8 @@ static void native_execSQL(JNIEnv* env, jobject object, jstring sqlString) if (err != SQLITE_OK) { char const * sql8 = env->GetStringUTFChars(sqlString, NULL); - LOGE("Failure %d (%s) on %p when preparing '%s'.\n", err, sqlite3_errmsg(handle), handle, sql8); + LOGE("Failure %d (%s) on %p when preparing '%s'.\n", err, sqlite3_errmsg(handle), + handle, sql8); throw_sqlite3_exception(env, handle, sql8); env->ReleaseStringUTFChars(sqlString, sql8); return; @@ -272,10 +278,12 @@ static void native_execSQL(JNIEnv* env, jobject object, jstring sqlString) if (stepErr != SQLITE_DONE) { if (stepErr == SQLITE_ROW) { - throw_sqlite3_exception(env, "Queries cannot be performed using execSQL(), use query() instead."); + throw_sqlite3_exception(env, + "Queries cannot be performed using execSQL(), use query() instead."); } else { char const * sql8 = env->GetStringUTFChars(sqlString, NULL); - LOGE("Failure %d (%s) on %p when executing '%s'\n", err, sqlite3_errmsg(handle), handle, sql8); + LOGE("Failure %d (%s) on %p when executing '%s'\n", err, sqlite3_errmsg(handle), + handle, sql8); throw_sqlite3_exception(env, handle, sql8); env->ReleaseStringUTFChars(sqlString, sql8); @@ -455,8 +463,8 @@ static JNINativeMethod sMethods[] = /* name, signature, funcPtr */ {"dbopen", "(Ljava/lang/String;I)V", (void *)dbopen}, {"dbclose", "()V", (void *)dbclose}, - {"enableSqlTracing", "(Ljava/lang/String;)V", (void *)enableSqlTracing}, - {"enableSqlProfiling", "(Ljava/lang/String;)V", (void *)enableSqlProfiling}, + {"enableSqlTracing", "(Ljava/lang/String;S)V", (void *)enableSqlTracing}, + {"enableSqlProfiling", "(Ljava/lang/String;S)V", (void *)enableSqlProfiling}, {"native_execSQL", "(Ljava/lang/String;)V", (void *)native_execSQL}, {"lastInsertRow", "()J", (void *)lastInsertRow}, {"lastChangeCount", "()I", (void *)lastChangeCount}, @@ -482,7 +490,8 @@ int register_android_database_SQLiteDatabase(JNIEnv *env) return -1; } - return AndroidRuntime::registerNativeMethods(env, "android/database/sqlite/SQLiteDatabase", sMethods, NELEM(sMethods)); + return AndroidRuntime::registerNativeMethods(env, "android/database/sqlite/SQLiteDatabase", + sMethods, NELEM(sMethods)); } /* throw a SQLiteException with a message appropriate for the error in handle */ diff --git a/tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java b/tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java new file mode 100644 index 0000000..4d228c4 --- /dev/null +++ b/tests/framework-tests/src/android/database/sqlite/SQLiteDatabaseTest.java @@ -0,0 +1,274 @@ +/* + * Copyright (C) 2006 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package android.database.sqlite; + +import android.content.Context; +import android.database.DatabaseUtils; +import android.test.AndroidTestCase; +import android.test.suitebuilder.annotation.LargeTest; +import android.test.suitebuilder.annotation.SmallTest; +import android.util.Log; + +import java.io.File; + +public class SQLiteDatabaseTest extends AndroidTestCase { + private static final String TAG = "DatabaseGeneralTest"; + + private static final int CURRENT_DATABASE_VERSION = 42; + private SQLiteDatabase mDatabase; + private File mDatabaseFile; + + @Override + protected void setUp() throws Exception { + super.setUp(); + dbSetUp(); + } + + @Override + protected void tearDown() throws Exception { + dbTeardown(); + super.tearDown(); + } + + private void dbTeardown() throws Exception { + mDatabase.close(); + mDatabaseFile.delete(); + } + + private void dbSetUp() throws Exception { + File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); + mDatabaseFile = new File(dbDir, "database_test.db"); + if (mDatabaseFile.exists()) { + mDatabaseFile.delete(); + } + mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null); + assertNotNull(mDatabase); + mDatabase.setVersion(CURRENT_DATABASE_VERSION); + } + + @SmallTest + public void testEnableWriteAheadLogging() { + assertNull(mDatabase.mConnectionPool); + mDatabase.enableWriteAheadLogging(); + DatabaseConnectionPool pool = mDatabase.mConnectionPool; + assertNotNull(pool); + // make the same call again and make sure the pool already setup is not re-created + mDatabase.enableWriteAheadLogging(); + assertEquals(pool, mDatabase.mConnectionPool); + } + + @SmallTest + public void testSetConnectionPoolSize() { + mDatabase.enableWriteAheadLogging(); + // can't set pool size to zero + try { + mDatabase.setConnectionPoolSize(0); + fail("IllegalStateException expected"); + } catch (IllegalStateException e) { + assertTrue(e.getMessage().contains("less than the current max value")); + } + // set pool size to a valid value + mDatabase.setConnectionPoolSize(10); + assertEquals(10, mDatabase.mConnectionPool.getMaxPoolSize()); + // can't set pool size to < the value above + try { + mDatabase.setConnectionPoolSize(1); + fail("IllegalStateException expected"); + } catch (IllegalStateException e) { + assertTrue(e.getMessage().contains("less than the current max value")); + } + } + + /** + * Test to ensure that readers are able to read the database data (old versions) + * EVEN WHEN the writer is in a transaction on the same database. + *<p> + * This test starts 1 Writer and 2 Readers and sets up connection pool for readers + * by calling the method {@link SQLiteDatabase#enableWriteAheadLogging()}. + * <p> + * Writer does the following in a tight loop + * <pre> + * begin transaction + * insert into table_1 + * insert into table_2 + * commit + * </pre> + * <p> + * As long a the writer is alive, Readers do the following in a tight loop at the same time + * <pre> + * Reader_K does "select count(*) from table_K" where K = 1 or 2 + * </pre> + * <p> + * The test is run for TIME_TO_RUN_WAL_TEST_FOR sec. + * <p> + * The test is repeated for different connection-pool-sizes (1..3) + * <p> + * And at the end of of each test, the following statistics are printed + * <ul> + * <li>connection-pool-size</li> + * <li>number-of-transactions by writer</li> + * <li>number of reads by reader_K while the writer is IN or NOT-IN xaction</li> + * </ul> + */ + @LargeTest + public void testConcurrencyEffectsOfConnPool() throws Exception { + // run the test with sqlite WAL enable + runConnectionPoolTest(true); + + // run the same test WITHOUT sqlite WAL enabled + runConnectionPoolTest(false); + } + + private void runConnectionPoolTest(boolean useWal) throws Exception { + int M = 3; + StringBuilder[] buff = new StringBuilder[M]; + for (int i = 0; i < M; i++) { + if (useWal) { + // set up connection pool + mDatabase.enableWriteAheadLogging(); + mDatabase.setConnectionPoolSize(i + 1); + } + mDatabase.execSQL("CREATE TABLE t1 (i int, j int);"); + mDatabase.execSQL("CREATE TABLE t2 (i int, j int);"); + mDatabase.beginTransaction(); + for (int k = 0; k < 5; k++) { + mDatabase.execSQL("insert into t1 values(?,?);", new String[] {k+"", k+""}); + mDatabase.execSQL("insert into t2 values(?,?);", new String[] {k+"", k+""}); + } + mDatabase.setTransactionSuccessful(); + mDatabase.endTransaction(); + + // start a writer + Writer w = new Writer(mDatabase); + + // initialize an array of counters to be passed to the readers + Reader r1 = new Reader(mDatabase, "t1", w, 0); + Reader r2 = new Reader(mDatabase, "t2", w, 1); + w.start(); + r1.start(); + r2.start(); + + // wait for all threads to die + w.join(); + r1.join(); + r2.join(); + + // print the stats + int[][] counts = getCounts(); + buff[i] = new StringBuilder(); + buff[i].append("connpool-size = "); + buff[i].append(i + 1); + buff[i].append(", num xacts by writer = "); + buff[i].append(getNumXacts()); + buff[i].append(", num-reads-in-xact/NOT-in-xact by reader1 = "); + buff[i].append(counts[0][1] + "/" + counts[0][0]); + buff[i].append(", by reader2 = "); + buff[i].append(counts[1][1] + "/" + counts[1][0]); + + Log.i(TAG, "done testing for conn-pool-size of " + (i+1)); + + dbTeardown(); + dbSetUp(); + } + Log.i(TAG, "duration of test " + TIME_TO_RUN_WAL_TEST_FOR + " sec"); + for (int i = 0; i < M; i++) { + Log.i(TAG, buff[i].toString()); + } + } + + private boolean inXact = false; + private int numXacts; + private static final int TIME_TO_RUN_WAL_TEST_FOR = 15; // num sec this test shoudl run + private int[][] counts = new int[2][2]; + + private synchronized boolean inXact() { + return inXact; + } + + private synchronized void setInXactFlag(boolean flag) { + inXact = flag; + } + + private synchronized void setCounts(int readerNum, int[] numReads) { + counts[readerNum][0] = numReads[0]; + counts[readerNum][1] = numReads[1]; + } + + private synchronized int[][] getCounts() { + return counts; + } + + private synchronized void setNumXacts(int num) { + numXacts = num; + } + + private synchronized int getNumXacts() { + return numXacts; + } + + private class Writer extends Thread { + private SQLiteDatabase db = null; + public Writer(SQLiteDatabase db) { + this.db = db; + } + @Override public void run() { + // in a loop, for N sec, do the following + // BEGIN transaction + // insert into table t1, t2 + // Commit + long now = System.currentTimeMillis(); + int k; + for (k = 0;(System.currentTimeMillis() - now) / 1000 < TIME_TO_RUN_WAL_TEST_FOR; k++) { + db.beginTransactionNonExclusive(); + setInXactFlag(true); + for (int i = 0; i < 10; i++) { + db.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""}); + db.execSQL("insert into t2 values(?,?);", new String[] {i+"", i+""}); + } + db.setTransactionSuccessful(); + setInXactFlag(false); + db.endTransaction(); + } + setNumXacts(k); + } + } + + private class Reader extends Thread { + private SQLiteDatabase db = null; + private String table = null; + private Writer w = null; + private int readerNum; + private int[] numReads = new int[2]; + public Reader(SQLiteDatabase db, String table, Writer w, int readerNum) { + this.db = db; + this.table = table; + this.w = w; + this.readerNum = readerNum; + } + @Override public void run() { + // while the write is alive, in a loop do the query on a table + while (w.isAlive()) { + for (int i = 0; i < 10; i++) { + DatabaseUtils.longForQuery(db, "select count(*) from " + this.table, null); + // update count of reads + numReads[inXact() ? 1 : 0] += 1; + } + } + setCounts(readerNum, numReads); + } + } +} |