Large Sqlite Statements for Android

Question

I am filling a CursorLoader for an app that has been in the play store for over a year now. There is no trouble with it crashing or any complaints about it. The trouble is, without the use of stored procedures, it has a massive SQL statement to fill the loader. To make this a little more manageable, I used string variables to build it up. Without the string variables, it would unfold to several pages of unmanageable SQL statement. The app is CardioLogger in the play store. All this Sql does is format 4 columns of data on the log page. I can't help feeling like I am missing some important concept or API. What is the best practice for composing sqlite statements that are in dire need of stored procedures? What are veteran developers using to develop clean code?
Here is the 4 column SQL result

SQL statement to give above result.

/*******************************************************************************
 *  
 *        SQL string for workoutLogColumns
 * 
 * *****************************************************************************/   
Calendar c = Calendar.getInstance();
int utcOffset = c.get(Calendar.ZONE_OFFSET) + c.get(Calendar.DST_OFFSET);  
Long utcMilliseconds = c.getTimeInMillis() + utcOffset;
//since1970 variable used 1 time.
private final int since1970 = (int) (utcMilliseconds/86400000.0);

//numberOfDaysAgo variable used 1 time.
private final String numberOfDaysAgo = "(" + since1970 + " - " + WorkoutLog.WO_DATE + ")";
//convertDate variable used 4 times.
private final String convertDate = "(" + WorkoutLog.WO_DATE + "*86400), 'unixepoch'";
// convertTime variable used 5 times.
private final String convertTime = WorkoutLog.TOTAL_TIME + ", 'unixepoch'";
// dayOfWeek variable used 5 times.
private final String dayOfWeek = "CASE (CAST (strftime('%w'," + convertDate+") AS INTEGER)) " +
        "WHEN 0 THEN 'sun'"+ 
        "WHEN 1 THEN 'mon'"+ 
        "WHEN 2 THEN 'tues'"+ 
        "WHEN 3 THEN 'wed'"+ 
        "WHEN 4 THEN 'thurs'"+ 
        "WHEN 5 THEN 'fri'"+
        "ELSE 'sat'"+
        "END ";
//shortenDate variable used 1 time.
private final String shortendDate = "CASE WHEN strftime('%Y',date('now')) == strftime('%Y', "+ convertDate+")"+
        "THEN strftime('%m/%d', "+ convertDate+")"+
        "ELSE strftime('%m/%d/%Y', "+ convertDate+")"+
        "END ";
//eventDate variable used 1 time.
private final String eventDate = "CASE "+ numberOfDaysAgo +
        "WHEN 0 THEN 'today' "+
        "WHEN 1 THEN 'yesterday' "+
        "WHEN 2 THEN " +dayOfWeek +
        "WHEN 3 THEN " +dayOfWeek +
        "WHEN 4 THEN " +dayOfWeek +
        "WHEN 5 THEN " +dayOfWeek +
        "WHEN 6 THEN " +dayOfWeek +
        "ELSE "+shortendDate+
        "END AS "+ WorkoutLog.WO_DATE;
//lap variable used 6 times.
private final String lap = Routine.ROUTINE_DISTANCE + " * " + WorkoutLog.DISTANCE;
//lapMeter variable used 1 time.
private final String lapMeter = " CASE WHEN ("+lap+") >= 1000 "+ 
        "THEN REPLACE(ROUND("+lap+" / 1000, 2),'.0','')||'km' "+
        "ELSE REPLACE(ROUND("+lap+", 2),'.0','')||'m' "+
        "END ";
//distMeter variable used 1 time.
private final String distMeter = " CASE WHEN ("+WorkoutLog.DISTANCE+") >= 1000 "+ 
        "THEN REPLACE(ROUND("+WorkoutLog.DISTANCE+" / 1000, 2),'.0','')||'km' "+
        "ELSE REPLACE(ROUND("+WorkoutLog.DISTANCE+", 2),'.0','')||'m' "+
        "END ";
//eventDist variable used 1 time.
private final String eventDist = "CASE(" + Routine.SETTINGS + " & 97) "+
        "WHEN 0 THEN REPLACE(ROUND("+lap+",2),'.0','')||'mi'"+  //Miles
        "WHEN 1 THEN REPLACE(ROUND("+WorkoutLog.DISTANCE+",2),'.0','')||'mi'"+
        "WHEN 32 THEN REPLACE(ROUND("+lap+",2),'.0','')||'yd'"+ //Yards
        "WHEN 33 THEN REPLACE(ROUND("+WorkoutLog.DISTANCE+",2),'.0','')||'yd'"+
        "WHEN 64 THEN" + lapMeter +                             //Meters
        "WHEN 65 THEN" + distMeter + 
        "WHEN 96 THEN REPLACE(ROUND("+lap+",2),'.0','')||'km'"+ //Km
        "WHEN 97 THEN REPLACE(ROUND("+WorkoutLog.DISTANCE+",2),'.0','')||'km'"+
        "END AS " + WorkoutLog.DISTANCE;
//places variable used 1 time.
private final String places = "((" + Routine.SETTINGS +" & 384)>>7)";
//timeFrac variable used 2 times.
private final String timeFrac = "||'.'||SUBSTR('00'||" + WorkoutLog.TOTAL_FRACTIONS + ", LENGTH('00'||" + WorkoutLog.TOTAL_FRACTIONS +")+1-"+places+","+places+")";
//eventTime variable used 1 time.
private final String eventTime = "CASE CAST (((strftime('%H'," + convertTime+")=='00')||(IFNULL("+ WorkoutLog.TOTAL_FRACTIONS + ",0)==0)) AS INTEGER)"+
        "WHEN 0 THEN strftime('%H:%M:%S'," + convertTime + ")" + timeFrac +
        "WHEN 1 THEN strftime('%H:%M:%S'," + convertTime + ") " +
        "WHEN 10 THEN strftime('%M:%S'," + convertTime + ")" + timeFrac +
        "WHEN 11 THEN strftime('%M:%S'," + convertTime + ") " +
        "END AS " + WorkoutLog.TOTAL_FRACTIONS;

//workoutLogColumns variable is used for the sqlite statement to fill a CursorLoader.
private final String  workoutLogColumns = "SELECT " +
        eventDate + ", "+
        Routine.ROUTINE_TABLE_NAME + "." +Routine.R_NAME + ", "  +
        eventDist + ", "+
        eventTime + ", "+
        WorkoutLog.WO_DATE + " AS workout_date, "+
        WorkoutLog.WORKOUT_LOG_TABLE_NAME + "." + WorkoutLog._ID +
        " FROM " + WorkoutLog.WORKOUT_LOG_TABLE_NAME + " JOIN " + Routine.ROUTINE_TABLE_NAME +
        " ON (" + WorkoutLog.WORKOUT_LOG_TABLE_NAME + "." + WorkoutLog.ROUTINE_ID + "=" + Routine.ROUTINE_TABLE_NAME + "." + Routine._ID + ")" +
        " ORDER BY workout_date DESC;";

Answer 1

It is quite difficult to refactor database to loader-based approach from customized one. Nevertheless, it is possible. Basically, what should be done primarily is to create SQL table per every model. Once you have separate table for the model you can make SQL views or simply your tables providing data to the UI. The only thing that you have to remember about is to register content observer for every table Uri that your sqlite view consists of (once you decide to create SQL view ofc).

If you suffer from poor performance, you can move all the loading-related logic to Loader's thread and display data as a cursor or parsed directly to the models.

I don't know the extent of your database but in your case I would choose custom loaders.

Commonsware created the loader transforming SQL String to the cursor: https://github.com/commonsguy/cwac-loaderex/blob/master/src/com/commonsware/cwac/loaderex/SQLiteCursorLoader.java

Moreover, here is a great article about loaders: http://chalup.github.io/blog/2014/06/12/android-loaders/

Hope my advice helps you out somehow.