Large Sqlite Statements for Android

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
Large Sqlite Statements for Android

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;";

-------------Problems Reply------------

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:

Moreover, here is a great article about loaders:

Hope my advice helps you out somehow.

Category:android Views:6 Time:2018-11-06

Related post

  • How to use sqlite database for android application 2011-01-18

    I am Newbee to Database concepts specially i need relative database concepts..I want an example working that uses sqlite database for android..i walked through android developer site but found very little stuff regarding database examples.. As i am n

  • Unable to delete or update a row from sqlite table for android 2011-04-19

    I would like to delete a row from sqlite table for android. I am using following code but its giving unexpected stopping your program: SQLiteDatabase db; db = openOrCreateDatabase("user_info.db",SQLiteDatabase.CREATE_IF_NECESSARY,null); db.delete("ch

  • is there an api to allow retrieval of app download stats for android market? 2011-06-23

    Is there an api or programmatic method to allow automatic retrieval of app download stats for android market? --------------Solutions------------- There is an app in the market called Andlytics that displays download stats, the app also explains that

  • cmd run automatically -> shell commands to view a sqlite database for android 2011-09-29

    I want to run a 'adb shell' -> to view a sqlite database for android. I need to run commands automatically in a CMD file. But I only can get access to the shell #,not to sqlite3 sqlite.cmd (file) cd "C:\android-sdk-windows\platform-tools" adb shel

  • SQLite Database for Android Application for local highscores. How to open ALL data and then OVERWRITE all data? 2012-01-08

    I found a lot of examples about SQlite. I have no experience with this language, but Android recommends using this database to save things locally. I just can't solve this problem. I have the following (adapted from an example): import android.conten

  • Problem with SQLite DB for Android 2011-07-14

    I am new to Android and have been following tutorials to create an app. I am having much difficulty with inserting data into a database. I have tried everything I can think of with no avail. The database class is: package com.mckallip.BeerOnTheWall;

  • Is there a way to include a library based on API Level or an if statement for Android 2010-08-17

    So I have a API issue. So i am creating a class that utilizes AndroidHttpClient to download Bitmap images from my server. Problem is, its API Level is 8 and I want this to be used from API 1 and above. Now I know I can use DefaultHttpClient (the API

  • SQLite database for Android app with potential multiple users 2011-08-11

    I have an app with an SQLite database for storing information for a user's account. I designed the database on the assumption that the app would only have one user. As such, the user's ID isn't stored in the database (but it is stored in a central da

  • How can I make the text larger in OneNote for Android? 2014-06-06

    Original Title: zoom onenote android I see the letters so small in the android app onenote. I cannot apply zoom. how can i see the things bigger? --------------Solutions------------- Currently onenote for android does not support any zoom functionali

  • Optimize SQL statement for android sqlite 2010-07-23

    I'm developing an application that tracks the user's current position and stores it into a SQLite database. Everything works fine, but now I have the problem when querying the database for a track with more than 1000 records it takes nearly 1.5 minut

  • How to view generated SQL Statements for Android/SQLite 2011-01-09

    I wonder if there is an easy way to see the SQL generated by the SQLiteQueryBuilder class in Android? I want to use this for debugging purposes. I looked around and searched but couldn't find anything. Either it's not easy or it's super obvious. ----

  • Uses of Aspects in SQLite Databases for Android Programming in Eclipse 2012-04-06

    I've written an Application that makes use of the SQLite Databases that come as part of the Android SDK, and they are all fully functioning and working correctly. However, I'm not overly sure of what each aspect of the database code is used for, and

  • SQLite Optimization for Android application 2010-10-25

    We have about 7-8 tables in our Android application each having about 8 columns on an average. Both read and write operations are performed on the database and I am experimenting and trying to find ways to enhance the performance of the DataAccess la

  • Sqlite encryption for Android 2010-11-12

    I have an Android app that has been already developed using SQLite. The DB is quite large (over 100 Megs) so it can be deployed only on the SD card. The data inside the DB is sensitive so for this reason we need to encrypt the DB. The default SQLite

  • How to get debugging statements for Android in Eclipse 2010-04-20

    I've read the lame documentation, and checked other answers. I'd like my Android app to print some debug statements in the logcat window of Eclispe. If I use the isLoggable method on the various types of debug levels on the Log class, I find that WAR

  • SQLite library for Android 2012-02-08

    I was wondering if there exists a library for easy configurations of SQLite. The way you can create databases and tables etc. in Android has a lot of overhead. Does it exist a library or plugin of some sort that automatically generates tables based o

  • Need help reading from SQLite database for Android 2012-03-13

    I have read through many sites and tutorials and have been unable to solve my problem. I have an application that works until I try to read from my database (it opens it just fine). I know its not the right way to do things but I have two global stri

  • Use custom SQLite database for Android 2010-07-02

    Say I want to create a database using the Android SDK's sqlite tool. What would the file extension have to be? I would think .db or .sqlite3 or .sqlite? How would I import it in my source code (Is there a method I should call?) and where in my source

  • Sqlite schema for android application 2010-11-18

    I am developing an Android application that queries various statistics from a static on-phone database about cars, such as acceleration, engine displacement, doors, etc. I need to specify the format of the database from the client. The UI needs to be

  • Set state for Android button 2011-03-05

    I declare a Android button like this: <Button android:layout_width="fill_parent" android:layout_height="wrap_content" android:background="@drawable/festival_button_selector" android:textColor="@color/white" android:text="@string/STR_FEST_SELOFF" a

  • Sqlite statements for iOS 2011-04-25

    I am trying to create a table with a number of columns. I am trying to bind the value (which is a variable) to create the table name. I keep getting an error: ('Error while creating update statement. 'near "?": syntax error'') So obviously I am doing

  • Localising SQLite databases for Android 2012-02-09

    I've had a lot of people ask for my app to be translated into German. The data for my app (items and their descriptions for example) are all held in a database, and I don't know how to localise this database (which is held in the assets folder, then

  • SQLite delete for Android 2012-03-20

    Can someone explain how the delete actually works, as the documentation isn't providing me with enough help. What i'm trying to do is delete all rows from my table where the date matches the one i provide in the delete method. This is what i've tried

  • Problem summing floats from SQLite database for android app 2011-06-24

    I'm having a problem with summing floats properly from my SQLite database. I have created my table as follows where I have set the column 'amount' as FLOAT through the String sql4. I have an EditText 'value' where the user enters a value, then it is

  • unable to insert data into sqlite database for android device 2011-12-19

    I've met an unknown error while inserting data into the database. The LogCat had been display "unable to insert data", however, the code that I've used is copied from another activity (which works). weird enough, when I placed the code in the onStart

Copyright (C), All Rights Reserved.

processed in 0.112 (s). 11 q(s)