Sunday 23 November 2014

Android, SQLite: EXPLAIN QUERY PLAN method for UPDATE queries

If you're using the SQLiteDatabase.update(String table, ContentValues values, String whereClause, String[] whereArgs) method for updating records in your local database, here are a couple of helper methods to compose and execute EXPLAIN QUERY PLAN commands for analysing your UPDATE queries:

/**
 * Composes and executes an EXECUTE QUERY PLAN command
 * for the UPDATE query that would be composed from the parameters provided.
 * 
 * @see {@link SQLiteDatabase#update(String, ContentValues, String, String[])} for a description of this method's parameters.
 */
private static void explainQueryPlanForUpdateStatement(SQLiteDatabase database, String table, ContentValues contentValues, String selection, String[] selectionArgs) {

  final StringBuilder sb = new StringBuilder();
  sb.append("EXPLAIN QUERY PLAN UPDATE ");

  sb.append(table);

  sb.append(" SET ");

  final Set keys = contentValues.keySet();

  boolean firstKey = true;

  for (String key : keys) {
    if (!firstKey) {
      sb.append(", ");
    }

    sb.append(key);
    sb.append(" = ");

    if (contentValues.get(key) == null) {
      sb.append("NULL");
    } else if (contentValues.get(key) instanceof Boolean) {
      Boolean value = (Boolean) contentValues.get(key);

      if (value.booleanValue()) {
        sb.append("1");
      } else {
        sb.append("0");
      }
    } else if (contentValues.get(key) instanceof Number) {
      sb.append(contentValues.get(key).toString());
    } else {
      sb.append("'");
      sb.append(contentValues.get(key).toString());
      sb.append("' ");
    }

    firstKey = false;
  }

  if (!TextUtils.isEmpty(selection)) {
    sb.append(" WHERE ");
    sb.append(selection);
  }

  executeExplainQueryPlanStatement(database, sb.toString(), selectionArgs);

}

/**
 * Executes sql using database
 * and prints the result to logs.
 * 
 * @param database the {@link SQLiteDatabase} instance to use to execute the query.
 * @param sql is an EXPLAIN QUERY PLAN command which must not be ; terminated.
 * @param selectionArgs the values to replace the ?s in the where clause of sql.
 */
private static void executeExplainQueryPlanStatement(SQLiteDatabase database, String sql, String[] selectionArgs) {

  final Cursor cursor = database.rawQuery(sql, selectionArgs);

  if (cursor.moveToFirst()) {
    final int colIndexSelectId = cursor.getColumnIndex("selectid");
    final int colIndexOrder = cursor.getColumnIndex("order");
    final int colIndexFrom = cursor.getColumnIndex("from");
    final int colIndexDetail = cursor.getColumnIndex("detail");

    final int selectId = cursor.getInt(colIndexSelectId);
    final int order = cursor.getInt(colIndexOrder);
    final int from = cursor.getInt(colIndexFrom);
    final String detail = cursor.getString(colIndexDetail);

    Log.d(TAG, sql);
    Log.d(TAG, String.format("%d | %d | %d | %s", selectId, order, from, detail));
  }

  cursor.close();

}
UPDATE: These methods are now contained in the QueryPlanExplainer class in this repository.

Saturday 22 November 2014

Android, SQLite: EXPLAIN QUERY PLAN method for SELECT queries

If you're using the SQLiteDatabase.query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) method for performing your local database queries (or one of the similar SQLiteDatabase query methods), here are a couple of helper methods to compose and execute EXPLAIN QUERY PLAN commands for analysing your SELECT queries:

/**
 * Composes and executes an EXECUTE QUERY PLAN command
 * for the SELECT query that would be composed from the parameters provided.
 * 
 * @see {@link SQLiteDatabase#query(String, String[], String, String[], String, String, String, String)} for a description of this method's parameters.
 */
private static void explainQueryPlanForSelectStatement(SQLiteDatabase database, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {

  final StringBuilder sb = new StringBuilder();
  sb.append("EXPLAIN QUERY PLAN SELECT ");

  if (columns == null || columns.length == 0) {
    sb.append(" * ");
  } else {
    boolean firstColumn = true;

    for (String column : columns) {
      if (!firstColumn) {
        sb.append(", ");
      }

      sb.append(column);

      firstColumn = false;
    }
  }

  sb.append(" FROM ");
  sb.append(table);

  if (!TextUtils.isEmpty(selection)) {
    sb.append(" WHERE ");
    sb.append(selection);
  }

  if (!TextUtils.isEmpty(groupBy)) {
    sb.append(" GROUP BY ");
    sb.append(groupBy);
  }

  if (!TextUtils.isEmpty(having)) {
    sb.append(" HAVING ");
    sb.append(having);
  }

  if (!TextUtils.isEmpty(orderBy)) {
    sb.append(" ORDER BY ");
    sb.append(orderBy);
  }

  if (!TextUtils.isEmpty(limit)) {
    sb.append(" LIMIT ");
    sb.append(limit);
  }

  executeExplainQueryPlanStatement(database, sb.toString(), selectionArgs);

}

/**
 * Executes sql using database
 * and prints the result to logs.
 * 
 * @param database the {@link SQLiteDatabase} instance to use to execute the query.
 * @param sql is an EXPLAIN QUERY PLAN command which must not be ; terminated.
 * @param selectionArgs the values to replace the ?s in the where clause of sql.
 */
private static void executeExplainQueryPlanStatement(SQLiteDatabase database, String sql, String[] selectionArgs) {

  final Cursor cursor = database.rawQuery(sql, selectionArgs);

  if (cursor.moveToFirst()) {
    final int colIndexSelectId = cursor.getColumnIndex("selectid");
    final int colIndexOrder = cursor.getColumnIndex("order");
    final int colIndexFrom = cursor.getColumnIndex("from");
    final int colIndexDetail = cursor.getColumnIndex("detail");

    final int selectId = cursor.getInt(colIndexSelectId);
    final int order = cursor.getInt(colIndexOrder);
    final int from = cursor.getInt(colIndexFrom);
    final String detail = cursor.getString(colIndexDetail);

    Log.d(TAG, sql);
    Log.d(TAG, String.format("%d | %d | %d | %s", selectId, order, from, detail));
  }

  cursor.close();

}

UPDATE: These methods are now contained in the QueryPlanExplainer class in this repository.