Intro to SQLlite in Android

It has been a while since I've dealt with SQL. Recently we wanted to see if having an SQLlite database in Android would provide a performance boost. The idea is to prefetch the day's TV guide and load it into an SQL database during the night. And then when the user starts the app, we would first look at the database to see if we already have the data. If so we would load that instead of going through a network request. There may also be a performance boost if the query was written properly. Another benefit over network request is that the REST resource is in JSON format and needs to be parsed, whereas translating from a database row into a POJO potentially is faster. It also takes us later into using CusorAdapter and load data on demand. For those reason it's worth trying it out.

By the way, great site to practice your SQL is http://www.w3schools.com/sql/trysql.asp

The first thing I need to do is store all my TV Program POJO into the database. And the first step is to create a table. To create a table, I execute an SQL statement such as this:
CREATE TABLE program (id INTEGER PRIMARY KEY, name TEXT NOT NULL, start_time NUMERIC, end_time NUMERIC)

It reads that we're going to be creating a new table with 4 fields: an ID, name, start and stop time. This table will have the name program. Each row in this table must have at least an ID and a display name.

In Android, we do this by creating a class that extends SQLiteOpenHelper and overriding the method
public void onCreate(SQLiteDatabase db);

In that function we can execute the query
db.execSQL(createTableQueryStr);

The SQLiteOpenHelper is a class that manage database creation and help with version control.

When you extends SQLiteOpenHelper, you are also required to implement onUpgrade method. Here, we'll simply delete the table and erase all previous data. The query is:
DROP TABLE IF EXISTS program

	public class ProgramsDbHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "programs.db";
	private static final int DATABASE_VERSION = 1;
	public static final String TABLE_PROGRAM = "program";
	public static final String COLUMN_ID = "id";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_START_TIME = "start_time";
	public static final String COLUMN_END_TIME = "end_time";
	public ProgramsDbHelper(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
	StringBuilder createTableQuery = new StringBuilder();
	createTableQuery.append("CREATE TABLE ").append(TABLE_PROGRAM).append(" (");
	createTableQuery.append(COLUMN_ID).append(" INTEGER PRIMARY KEY, ");
	createTableQuery.append(COLUMN_NAME).append(" TEXT NOT NULL, ");
	createTableQuery.append(COLUMN_START_TIME).append(" NUMERIC, ");
	createTableQuery.append(COLUMN_END_TIME).append(" NUMERIC");
	createTableQuery.append(")");
	db.execSQL(createTableQuery.toString());
	}
	/** Uncomment this if we use foreign key constraint in any of our table
	@Override
	public void onConfigure(SQLiteDatabase db) {
	super.onConfigure(db);
	db.setForeignKeyConstraintsEnabled(true);
	}
	*/
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROGRAM);
	onCreate(db);
	}
	}

view raw ProgramsDbHelper.java hosted with ❤ by GitHub

The Program POJO actually has more data than the 4 things, but for simplicity of this blog, we'll pretend there's only 4 fields. But I want to mention that in reality, the POJO also includes a List of Strings that denotes which genre(s) the program belongs to. In order to include this information, I needed to create a 2nd table of genres. I decided to use Foreign key contraint to better insure data integrity. This way I don't accidentally add a Genre that doesn't belong to any program. The query for that table looks like this:
CREATE TABLE genre (program_id INTEGER, genre_name TEXT, FOREIGN KEY (program_id) REFERENCES program(id))

A database query always return a Cursor. It's the mechanism for which you retrieve the result. It doesn't load all the results in memory, only what the cursor is pointing at. This is good if you pass this cursor to your view so that you only need to get the data that is displayed in the UI, but it's not always possible depending on how the app is structured. So for convinence, I've created some method to translate between table row and POJO and vise versa.

	public class Program {
	public String id;
	public String name;
	public long start_time;
	public long end_time;
	public static Program toProgram(Cursor cursor) {
	Program p = new Program();
	p.id = cursor.getString(cursor.getColumnIndex(ProgramsDbHelper.COLUMN_ID));
	p.name = cursor.getString(cursor.getColumnIndex(ProgramsDbHelper.COLUMN_NAME));
	p.start_time = cursor.getLong(cursor.getColumnIndex(ProgramsDbHelper.COLUMN_START_TIME));
	p.end_time = cursor.getLong(cursor.getColumnIndex(ProgramsDbHelper.COLUMN_END_TIME));
	return p;
	}
	public static ContentValues toDbValues(Program program) {
	ContentValues values = new ContentValues();
	values.put(ProgramsDbHelper.COLUMN_ID, program.id);
	values.put(ProgramsDbHelper.COLUMN_NAME, program.name);
	values.put(ProgramsDbHelper.COLUMN_START_TIME, program.start_time);
	values.put(ProgramsDbHelper.COLUMN_END_TIME, program.end_time);
	return values;
	}
	}

view raw Program.java hosted with ❤ by GitHub

Before you can insert, query, or delete anything in the database, you need to open it. This is usually done either than your app is created or when your Service is connected. Here I've created a ProgramsDataSource class that'll help me insert query and remove programs based on the start_time of the TV program.
Note the use of the insertWithOnConflict method where if a row already exist, I'll replace the values instead of throwing an exception.
The other thing to note is that if you need to use the DISTINCT function of SQL to query all unique names for example. Let's say I want to know programs of my entire TV Guide during prime time during the week, but I don't want to see the evening news show up 5 times, I would do a query with DISTINCT. To do that in SQLiteDatabase in Android, you use the overloaded method for query with the boolean as first argument.

	public class ProgramsDataSource {
	private ProgramsDbHelper dbHelper;
	private SQLiteDatabase database;
	private String[] allColumns = {
	ProgramsDbHelper.COLUMN_ID,
	ProgramsDbHelper.COLUMN_NAME,
	ProgramsDbHelper.COLUMN_START_TIME,
	ProgramsDbHelper.COLUMN_END_TIME,
	};
	public ProgramsDataSource(Context context) {
	dbHelper = new ProgramsDbHelper(context);
	}
	public void open() throws SQLException {
	database = dbHelper.getWritableDatabase();
	}
	public void close() {
	dbHelper.close();
	}
	public synchronized void insertPrograms(List<Program> programs) {
	try {
	database.beginTransaction();
	for (Program program : programs) {
	ContentValues values = ProgramsMapper.toDbValues(program);
	database.insertWithOnConflict(ProgramsDbHelper.TABLE_PROGRAM, null, values, SQLiteDatabase.CONFLICT_REPLACE);
	}
	database.setTransactionSuccessful();
	} catch (Exception e) {
	e.printStackTrace(System.err);
	} finally {
	database.endTransaction();
	}
	}
	public synchronized void removePrograms(long olderThan) {
	try {
	database.beginTransaction();
	//TODO: remove entry in genre table
	database.delete(ProgramsDbHelper.TABLE_PROGRAM, ProgramsDbHelper.COLUMN_START_TIME + " < " + olderThan, null);
	database.setTransactionSuccessful();
	} catch (Exception e) {
	e.printStackTrace(System.err);
	} finally {
	database.endTransaction();
	}
	}
	public synchronized List<Program> getPrograms(long start_time, long end_time) {
	assert(end_time > start_time);
	StringBuilder selection = new StringBuilder();
	selection.append(ProgramsDbHelper.COLUMN_START_TIME).append(" < ").append(end_time);
	selection.append(" AND ");
	selection.append(ProgramsDbHelper.COLUMN_END_TIME).append(" > ").append(start_time);
	Cursor cursor = database.query(ProgramsDbHelper.TABLE_PROGRAM, allColumns,
	selection.toString(), null, null, null, ProgramsDbHelper.COLUMN_START_TIME );
	cursor.moveToFirst();
	List<Program> programs = new ArrayList<Program>();
	while (!cursor.isAfterLast()) {
	Program program = ProgramsMapper.toProgram(cursor);
	programs.add(program);
	cursor.moveToNext();
	}
	return programs;
	}
	}

view raw ProgramsDataSource.java hosted with ❤ by GitHub

WebRep

currentVote

noRating

noWeight

Quick & Dirty Tips: Splitting words

First thing to note is a StringTokenzier is depreciated.

StringTokenizer is a legacy class that is retained for compatibility reasons although its use is discouraged in new code. It is recommended that anyone seeking this functionality use the split method of String or the java.util.regex package instead.

Instead use String.split(String regex), which takes regular expression. So to use the method we have to look at how regular express can split words.

[^abc] Any character except a, b, or c (negation)
\s A whitespace character: [ \t\n\x0B\f\r]
\S A non-whitespace character: [^\s]

So to split words in a String, I use the following:

String line = "Lorem ipsum dolor sit amet, consectetur adipisicing elit...";
String[] words = line.split("\\s*[^a-zA-Z]+\\s*");


Links:

• Sting.split(regex)
• Regular Expression

Creating a REST library using Retrofit & OkHttp

I've been tasked to modernize our application's networking layer. We have been writing our own network manager to deal with http 302 redirect and caching responses, but there are many opensource project that already does these things very well. I've looked into RoboSpice and Volley as the more popular choices for doing networking in Android. I found that they're a good candidate as an improved AsyncTask as well as providing caching. However both framework are closely related to the Activity context and doesn't work as well when put into a Service such as ones that I intent to make with a SyncAdapter. We needed a lower level framework. To figure out what fits our bill, we wrote down some of our requirements:

1. It needs to support Https
2. We want to translate server JSON into client POJO
3. The framework needs to allow us to edit the header and post body for authentication
4. We want the ability to pass arguments to methods and insert them as part of the URL or the query
5. We want to save bandwidth with Gzip
6. We want efficient communication with parallel requests
7. we want Http caching that follows the server's http cache directive
8. It needs to follow http 302 redirect
9. It needs to work well with Android

I stumbled across Square's Retrofit and OkHttp. Here's how it fits.

Retrofit

Supports Https
In Retrofit's home page, their example uses https, so clearly it supports it.
RestAdapter restAdapter = new RestAdapter.Builder() .setServer("https://api.github.com") .build();

Converts JSON to POJO
Retrofit use GSON by default to convert Http bodies to and from JSON.

Edit request header and body
Retrofit allow header information to be set statically using annotation or at runtime using a RequestIntercepter.

Edit URL and query
Retrofit uses annotated method arguments to replace PATH or query placeholders. You can also change it at runtime using RequestIntercepter.

Working with Android
The generated implementation GET and POST can be made synchronously outside of UI thread, or asynchronously callback on the UI thread, or in background thread via Netflix's RxJava rx.Observable. So it's very flexible in what threading model we need. We can also put RoboSpice on top of Retrofit later if we want.

OkHttp

Supports GZIP
"Transparent GZIP shrinks download sizes."

Efficient Networking

• "SPDY support allows all requests to the same host to share a socket."
• "Connection pooling reduces request latency (if SPDY isn’t available)."
• "it will silently recover from common connection problems"

Local Caching
OkHttp's HttpResponseCache allows you to set aside a specific amount of space in cache directory, very similar to Google's HttpResponseCache.


First create an OkHttpClient and set 10MB of space in the cache director for caching

	OkHttpClient okHttpClient = new OkHttpClient();
	File cacheDir = context.getCacheDir();
	HttpResponseCache cache = new HttpResponseCache(cacheDir, 10 * 1024 * 1024);
	okHttpClient.setResponseCache(cache);

view raw gistfile1.java hosted with ❤ by GitHub

Then in order to add some standard PATH and query parameters I create a RequestInterceptor

	private class BasicRequestInterceptor implements RequestInterceptor {
	@Override
	public void intercept(RequestFacade requestFacade) {
	requestFacade.addEncodedPathParam("product_name", mProductName);
	requestFacade.addQueryParam("device", android.os.Build.MODEL);
	requestFacade.addQueryParam("firmware", android.os.Build.VERSION.RELEASE);
	}
	}

view raw gistfile1.java hosted with ❤ by GitHub

Then we can build the RestAdapter and set the OkHttpClient as the networking component and set the request interceptor so that every request will be modified to include those Path and query.
Note that for debugging purpose I've turned on debugging so I can see the actual request and response in the log.
Also note that the http scheme is set as part of the most.

	new RestAdapter.Builder()
	.setServer("http://" + this.mHost)
	.setRequestInterceptor(requestInterceptor)
	.setClient(httpClient)
	.setLogLevel(RestAdapter.LogLevel.FULL)
	.build();

view raw gistfile1.java hosted with ❤ by GitHub

Then with the RestAdapter, you call create with the Interface that you want to create. But before we do that, we need to define the Interface.

GET
Notice that synchronous call throws RetrofitError at runtime, so I've declared it in the interface explicitly here. The caller should try/catch the request in case an error occurred.

	public class Program {
	public String id;
	public String name;
	public String description;
	public long start_time;
	public long duration;
	public String channel_id;
	public List<String> genre_list;
	public long original_air_date;
	}
	@GET("/{product_name}/guide/v3/programs")
	List<Program> getPrograms(
	@Path("product_name") String productName
	@Query("channel_id") String channelId,
	@Query("start-time") long startTime,
	@Query("timeslice") long timeSlice) throws RetrofitError;

view raw gistfile1.java hosted with ❤ by GitHub

POST

	public class AuthTokenPostData {
	public String grant_type = "password";
	public String client_id;
	public String username;
	public String password;
	}
	public class AuthTokenResponse {
	public String token_type;
	public String access_token;
	public String scope;
	public Long expires_in;
	public String refresh_token;
	}
	@POST("/{product_name}/oauth2/tokens.json")
	@Headers({
	HTTP.CONN_DIRECTIVE + ":" + HTTP.CONN_CLOSE,
	})
	AuthTokenResponse getAuthtoken(@Body AuthTokenPostData userNamePassword) throws RetrofitError;

view raw gistfile1.java hosted with ❤ by GitHub

So now, we can create POJO data for all the data that our server returns, create Interface files for all the APIs that the server has available, annotate the URL, the header, the post body, and the return type. And then I should have a brand new and robust REST library for my application!
For more information, visit the

• Retrofit project page
• OkHttp project page