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
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.
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.
currentVote
noRating
noWeight