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'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();

}

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'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();

}