michael@0: /* This Source Code Form is subject to the terms of the Mozilla Public
michael@0:  * License, v. 2.0. If a copy of the MPL was not distributed with this file,
michael@0:  * You can obtain one at http://mozilla.org/MPL/2.0/. */
michael@0: 
michael@0: package org.mozilla.gecko.sqlite;
michael@0: 
michael@0: import android.content.ContentValues;
michael@0: import android.database.Cursor;
michael@0: import android.database.sqlite.SQLiteDatabase;
michael@0: import android.database.sqlite.SQLiteException;
michael@0: import android.text.TextUtils;
michael@0: import android.util.Log;
michael@0: 
michael@0: import org.mozilla.gecko.mozglue.RobocopTarget;
michael@0: 
michael@0: import java.util.ArrayList;
michael@0: import java.util.Arrays;
michael@0: import java.util.Map.Entry;
michael@0: 
michael@0: /*
michael@0:  * This class allows using the mozsqlite3 library included with Firefox
michael@0:  * to read SQLite databases, instead of the Android SQLiteDataBase API,
michael@0:  * which might use whatever outdated DB is present on the Android system.
michael@0:  */
michael@0: public class SQLiteBridge {
michael@0:     private static final String LOGTAG = "SQLiteBridge";
michael@0: 
michael@0:     // Path to the database. If this database was not opened with openDatabase, we reopen it every query.
michael@0:     private String mDb;
michael@0: 
michael@0:     // Pointer to the database if it was opened with openDatabase. 0 implies closed.
michael@0:     protected volatile long mDbPointer = 0L;
michael@0: 
michael@0:     // Values remembered after a query.
michael@0:     private long[] mQueryResults;
michael@0: 
michael@0:     private boolean mTransactionSuccess = false;
michael@0:     private boolean mInTransaction = false;
michael@0: 
michael@0:     private static final int RESULT_INSERT_ROW_ID = 0;
michael@0:     private static final int RESULT_ROWS_CHANGED = 1;
michael@0: 
michael@0:     // Shamelessly cribbed from db/sqlite3/src/moz.build.
michael@0:     private static final int DEFAULT_PAGE_SIZE_BYTES = 32768;
michael@0: 
michael@0:     // The same size we use elsewhere.
michael@0:     private static final int MAX_WAL_SIZE_BYTES = 524288;
michael@0: 
michael@0:     // JNI code in $(topdir)/mozglue/android/..
michael@0:     private static native MatrixBlobCursor sqliteCall(String aDb, String aQuery,
michael@0:                                                       String[] aParams,
michael@0:                                                       long[] aUpdateResult)
michael@0:         throws SQLiteBridgeException;
michael@0:     private static native MatrixBlobCursor sqliteCallWithDb(long aDb, String aQuery,
michael@0:                                                             String[] aParams,
michael@0:                                                             long[] aUpdateResult)
michael@0:         throws SQLiteBridgeException;
michael@0:     private static native long openDatabase(String aDb)
michael@0:         throws SQLiteBridgeException;
michael@0:     private static native void closeDatabase(long aDb);
michael@0: 
michael@0:     // Takes the path to the database we want to access.
michael@0:     @RobocopTarget
michael@0:     public SQLiteBridge(String aDb) throws SQLiteBridgeException {
michael@0:         mDb = aDb;
michael@0:     }
michael@0: 
michael@0:     // Executes a simple line of sql.
michael@0:     public void execSQL(String sql)
michael@0:                 throws SQLiteBridgeException {
michael@0:         internalQuery(sql, null);
michael@0:     }
michael@0: 
michael@0:     // Executes a simple line of sql. Allow you to bind arguments
michael@0:     public void execSQL(String sql, String[] bindArgs)
michael@0:                 throws SQLiteBridgeException {
michael@0:         internalQuery(sql, bindArgs);
michael@0:     }
michael@0: 
michael@0:     // Executes a DELETE statement on the database
michael@0:     public int delete(String table, String whereClause, String[] whereArgs)
michael@0:                throws SQLiteBridgeException {
michael@0:         StringBuilder sb = new StringBuilder("DELETE from ");
michael@0:         sb.append(table);
michael@0:         if (whereClause != null) {
michael@0:             sb.append(" WHERE " + whereClause);
michael@0:         }
michael@0: 
michael@0:         internalQuery(sb.toString(), whereArgs);
michael@0:         return (int)mQueryResults[RESULT_ROWS_CHANGED];
michael@0:     }
michael@0: 
michael@0:     public Cursor query(String table,
michael@0:                         String[] columns,
michael@0:                         String selection,
michael@0:                         String[] selectionArgs,
michael@0:                         String groupBy,
michael@0:                         String having,
michael@0:                         String orderBy,
michael@0:                         String limit)
michael@0:                throws SQLiteBridgeException {
michael@0:         StringBuilder sb = new StringBuilder("SELECT ");
michael@0:         if (columns != null)
michael@0:             sb.append(TextUtils.join(", ", columns));
michael@0:         else
michael@0:             sb.append(" * ");
michael@0: 
michael@0:         sb.append(" FROM ");
michael@0:         sb.append(table);
michael@0: 
michael@0:         if (selection != null) {
michael@0:             sb.append(" WHERE " + selection);
michael@0:         }
michael@0: 
michael@0:         if (groupBy != null) {
michael@0:             sb.append(" GROUP BY " + groupBy);
michael@0:         }
michael@0: 
michael@0:         if (having != null) {
michael@0:             sb.append(" HAVING " + having);
michael@0:         }
michael@0: 
michael@0:         if (orderBy != null) {
michael@0:             sb.append(" ORDER BY " + orderBy);
michael@0:         }
michael@0: 
michael@0:         if (limit != null) {
michael@0:             sb.append(" " + limit);
michael@0:         }
michael@0: 
michael@0:         return rawQuery(sb.toString(), selectionArgs);
michael@0:     }
michael@0: 
michael@0:     @RobocopTarget
michael@0:     public Cursor rawQuery(String sql, String[] selectionArgs)
michael@0:         throws SQLiteBridgeException {
michael@0:         return internalQuery(sql, selectionArgs);
michael@0:     }
michael@0: 
michael@0:     public long insert(String table, String nullColumnHack, ContentValues values)
michael@0:                throws SQLiteBridgeException {
michael@0:         if (values == null)
michael@0:             return 0;
michael@0: 
michael@0:         ArrayList<String> valueNames = new ArrayList<String>();
michael@0:         ArrayList<String> valueBinds = new ArrayList<String>();
michael@0:         ArrayList<String> keyNames = new ArrayList<String>();
michael@0: 
michael@0:         for (Entry<String, Object> value : values.valueSet()) {
michael@0:             keyNames.add(value.getKey());
michael@0: 
michael@0:             Object val = value.getValue();
michael@0:             if (val == null) {
michael@0:                 valueNames.add("NULL");
michael@0:             } else {
michael@0:                 valueNames.add("?");
michael@0:                 valueBinds.add(val.toString());
michael@0:             }
michael@0:         }
michael@0: 
michael@0:         StringBuilder sb = new StringBuilder("INSERT into ");
michael@0:         sb.append(table);
michael@0: 
michael@0:         sb.append(" (");
michael@0:         sb.append(TextUtils.join(", ", keyNames));
michael@0:         sb.append(")");
michael@0: 
michael@0:         // XXX - Do we need to bind these values?
michael@0:         sb.append(" VALUES (");
michael@0:         sb.append(TextUtils.join(", ", valueNames));
michael@0:         sb.append(") ");
michael@0: 
michael@0:         String[] binds = new String[valueBinds.size()];
michael@0:         valueBinds.toArray(binds);
michael@0:         internalQuery(sb.toString(), binds);
michael@0:         return mQueryResults[RESULT_INSERT_ROW_ID];
michael@0:     }
michael@0: 
michael@0:     public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
michael@0:                throws SQLiteBridgeException {
michael@0:         if (values == null)
michael@0:             return 0;
michael@0: 
michael@0:         ArrayList<String> valueNames = new ArrayList<String>();
michael@0: 
michael@0:         StringBuilder sb = new StringBuilder("UPDATE ");
michael@0:         sb.append(table);
michael@0:         sb.append(" SET ");
michael@0: 
michael@0:         boolean isFirst = true;
michael@0: 
michael@0:         for (Entry<String, Object> value : values.valueSet()) {
michael@0:             if (isFirst)
michael@0:                 isFirst = false;
michael@0:             else
michael@0:                 sb.append(", ");
michael@0: 
michael@0:             sb.append(value.getKey());
michael@0: 
michael@0:             Object val = value.getValue();
michael@0:             if (val == null) {
michael@0:                 sb.append(" = NULL");
michael@0:             } else {
michael@0:                 sb.append(" = ?");
michael@0:                 valueNames.add(val.toString());
michael@0:             }
michael@0:         }
michael@0: 
michael@0:         if (!TextUtils.isEmpty(whereClause)) {
michael@0:             sb.append(" WHERE ");
michael@0:             sb.append(whereClause);
michael@0:             valueNames.addAll(Arrays.asList(whereArgs));
michael@0:         }
michael@0: 
michael@0:         String[] binds = new String[valueNames.size()];
michael@0:         valueNames.toArray(binds);
michael@0: 
michael@0:         internalQuery(sb.toString(), binds);
michael@0:         return (int)mQueryResults[RESULT_ROWS_CHANGED];
michael@0:     }
michael@0: 
michael@0:     public int getVersion()
michael@0:                throws SQLiteBridgeException {
michael@0:         Cursor cursor = internalQuery("PRAGMA user_version", null);
michael@0:         int ret = -1;
michael@0:         if (cursor != null) {
michael@0:             cursor.moveToFirst();
michael@0:             String version = cursor.getString(0);
michael@0:             ret = Integer.parseInt(version);
michael@0:         }
michael@0:         return ret;
michael@0:     }
michael@0: 
michael@0:     // Do an SQL query, substituting the parameters in the query with the passed
michael@0:     // parameters. The parameters are substituted in order: named parameters
michael@0:     // are not supported.
michael@0:     private Cursor internalQuery(String aQuery, String[] aParams)
michael@0:         throws SQLiteBridgeException {
michael@0: 
michael@0:         mQueryResults = new long[2];
michael@0:         if (isOpen()) {
michael@0:             return sqliteCallWithDb(mDbPointer, aQuery, aParams, mQueryResults);
michael@0:         }
michael@0:         return sqliteCall(mDb, aQuery, aParams, mQueryResults);
michael@0:     }
michael@0: 
michael@0:     /*
michael@0:      * The second two parameters here are just provided for compatibility with SQLiteDatabase
michael@0:      * Support for them is not currently implemented.
michael@0:     */
michael@0:     public static SQLiteBridge openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags)
michael@0:         throws SQLiteException {
michael@0:         if (factory != null) {
michael@0:             throw new RuntimeException("factory not supported.");
michael@0:         }
michael@0:         if (flags != 0) {
michael@0:             throw new RuntimeException("flags not supported.");
michael@0:         }
michael@0: 
michael@0:         SQLiteBridge bridge = null;
michael@0:         try {
michael@0:             bridge = new SQLiteBridge(path);
michael@0:             bridge.mDbPointer = SQLiteBridge.openDatabase(path);
michael@0:         } catch (SQLiteBridgeException ex) {
michael@0:             // Catch and rethrow as a SQLiteException to match SQLiteDatabase.
michael@0:             throw new SQLiteException(ex.getMessage());
michael@0:         }
michael@0: 
michael@0:         prepareWAL(bridge);
michael@0: 
michael@0:         return bridge;
michael@0:     }
michael@0: 
michael@0:     public void close() {
michael@0:         if (isOpen()) {
michael@0:           closeDatabase(mDbPointer);
michael@0:         }
michael@0:         mDbPointer = 0L;
michael@0:     }
michael@0: 
michael@0:     public boolean isOpen() {
michael@0:         return mDbPointer != 0;
michael@0:     }
michael@0: 
michael@0:     public void beginTransaction() throws SQLiteBridgeException {
michael@0:         if (inTransaction()) {
michael@0:             throw new SQLiteBridgeException("Nested transactions are not supported");
michael@0:         }
michael@0:         execSQL("BEGIN EXCLUSIVE");
michael@0:         mTransactionSuccess = false;
michael@0:         mInTransaction = true;
michael@0:     }
michael@0: 
michael@0:     public void beginTransactionNonExclusive() throws SQLiteBridgeException {
michael@0:         if (inTransaction()) {
michael@0:             throw new SQLiteBridgeException("Nested transactions are not supported");
michael@0:         }
michael@0:         execSQL("BEGIN IMMEDIATE");
michael@0:         mTransactionSuccess = false;
michael@0:         mInTransaction = true;
michael@0:     }
michael@0: 
michael@0:     public void endTransaction() {
michael@0:         if (!inTransaction())
michael@0:             return;
michael@0: 
michael@0:         try {
michael@0:           if (mTransactionSuccess) {
michael@0:               execSQL("COMMIT TRANSACTION");
michael@0:           } else {
michael@0:               execSQL("ROLLBACK TRANSACTION");
michael@0:           }
michael@0:         } catch(SQLiteBridgeException ex) {
michael@0:             Log.e(LOGTAG, "Error ending transaction", ex);
michael@0:         }
michael@0:         mInTransaction = false;
michael@0:         mTransactionSuccess = false;
michael@0:     }
michael@0: 
michael@0:     public void setTransactionSuccessful() throws SQLiteBridgeException {
michael@0:         if (!inTransaction()) {
michael@0:             throw new SQLiteBridgeException("setTransactionSuccessful called outside a transaction");
michael@0:         }
michael@0:         mTransactionSuccess = true;
michael@0:     }
michael@0: 
michael@0:     public boolean inTransaction() {
michael@0:         return mInTransaction;
michael@0:     }
michael@0: 
michael@0:     @Override
michael@0:     public void finalize() {
michael@0:         if (isOpen()) {
michael@0:             Log.e(LOGTAG, "Bridge finalized without closing the database");
michael@0:             close();
michael@0:         }
michael@0:     }
michael@0: 
michael@0:     private static void prepareWAL(final SQLiteBridge bridge) {
michael@0:         // Prepare for WAL mode. If we can, we switch to journal_mode=WAL, then
michael@0:         // set the checkpoint size appropriately. If we can't, then we fall back
michael@0:         // to truncating and synchronous writes.
michael@0:         final Cursor cursor = bridge.internalQuery("PRAGMA journal_mode=WAL", null);
michael@0:         try {
michael@0:             if (cursor.moveToFirst()) {
michael@0:                 String journalMode = cursor.getString(0);
michael@0:                 Log.d(LOGTAG, "Journal mode: " + journalMode);
michael@0:                 if ("wal".equals(journalMode)) {
michael@0:                     // Success! Let's make sure we autocheckpoint at a reasonable interval.
michael@0:                     final int pageSizeBytes = bridge.getPageSizeBytes();
michael@0:                     final int checkpointPageCount = MAX_WAL_SIZE_BYTES / pageSizeBytes;
michael@0:                     bridge.internalQuery("PRAGMA wal_autocheckpoint=" + checkpointPageCount, null).close();
michael@0:                 } else {
michael@0:                     if (!"truncate".equals(journalMode)) {
michael@0:                         Log.w(LOGTAG, "Unable to activate WAL journal mode. Using truncate instead.");
michael@0:                         bridge.internalQuery("PRAGMA journal_mode=TRUNCATE", null).close();
michael@0:                     }
michael@0:                     Log.w(LOGTAG, "Not using WAL mode: using synchronous=FULL instead.");
michael@0:                     bridge.internalQuery("PRAGMA synchronous=FULL", null).close();
michael@0:                 }
michael@0:             }
michael@0:         } finally {
michael@0:             cursor.close();
michael@0:         }
michael@0:     }
michael@0: 
michael@0:     private int getPageSizeBytes() {
michael@0:         if (!isOpen()) {
michael@0:             throw new IllegalStateException("Database not open.");
michael@0:         }
michael@0: 
michael@0:         final Cursor cursor = internalQuery("PRAGMA page_size", null);
michael@0:         try {
michael@0:             if (!cursor.moveToFirst()) {
michael@0:                 Log.w(LOGTAG, "Unable to retrieve page size.");
michael@0:                 return DEFAULT_PAGE_SIZE_BYTES;
michael@0:             }
michael@0: 
michael@0:             return cursor.getInt(0);
michael@0:         } finally {
michael@0:             cursor.close();
michael@0:         }
michael@0:     }
michael@0: }