Welcome back.
We've now got our app creating version 3 of the database,
and we've added some test timing data in the last video.
For our reports, we're going to use a SQLite view,
which means the database will need upgrading again.
Testing the upgrade, from version 2 of the database to version 3,
will be easy.
It's always easy to test the upgrade from the previous version.
Where things get a bit trickier is when users don't update the app
straight away.
They may run with version one of the database
and not update until we've released a version
that uses the version 3 database.
That means we always have to test our upgrade process
from every previous version to make sure it works
whichever database is on the device.
I know we haven't released previous versions of the app
so this is a bit artificial.
But we're going to go through the process to understand what we need to do
when our database does change.
You can't be sure that your database will be the final version.
We could make a mistake with our view, for example,
and have to release a new version to fix a bug.
For that reason, it's a good idea to keep a copy of each database version.
So let's do that.
I've already got a copy of version 1 and version 2
from a few videos ago.
I've copied them to different directories,
TaskTimerdb-version1,
and TaskTimerdb-version2.
My emulator is now populated with the version 3 database,
and I'll copy that onto my mac using the device file explorer.
Now the database has been copied back,
I'll use the max finder to make another copy of it.
On windows, you'd use the file explorer or nautilus
or whatever on Linux.
You can also copy the directory from the terminal if you're comfortable doing that.
The database that I've copied to tasktimerdb-version3
is to be used when testing upgrades to different versions.
If I go using it to experiment on,
I might change the structure and that would invalidate any testing that I do.
That's why it's important to only work with a copy of the database.
Don't modify the backup copies that you're going to use for testing.
All right. I'll copy tasktimerdb-version3
and call the new directory tasktimerdb-
-experimenting.
Back in android studio, I'll open the terminal pane
and change into that directory.
I'll be using this terminal pane a lot the next few minutes.
So I'm going to put into floating mode and drag it up near the top of the screen,
away from the subtitles if you're using them.
I don't need the device explorer anymore, so I'll close it.
Open the database in the SQLite3 command line utility.
And while we're here, we can check to make sure
that there is some test data in the timings table.
That looks good. Now we can create a view to join the tasks
and timings tables to create the data for our reports.
Looking at the timings table, the first column is the ID from the timings
table that will be unique.
Each timing row will have a unique ID.
Next, we have the task ID.
That can be used to join this timings table
to the tasks table
to let us get the task name and the description.
The third column is the start time.
Remember, we're storing the number of seconds since the start of the epic
which was the 1st of January 1970.
Numbers like that aren't very useful to our users
but SQLite has a date function
that can convert the number into a more user-friendly format.
We'll see that in a minute.
Finally, there's a duration stored as a number of seconds.
If we stop and think about how the app will be used,
the users will probably be starting and stopping their tasks several times a day.
After all, if they spent all day working on the same task,
they wouldn't need the app in the first place.
So our view will need to add up all the timings for a task
for each day. Once again, SQLite can help with this.
It's got a sum function that will do just that.
All we have to do is tell it to group the rows by the date
and task columns.
You don't want to watch me typing SQL, so I'll paste it in
and leave it on the screen while we talk about it.
We're creating a new view that joins the tasks table
to the timings table. The columns we want to include are
the task.Name and the task.Description.
We need the start time column in the original form
to display the date in a format that our user recognizes.
You'll see that being used in our code
when we use the date format class.
We need a date in milliseconds since the epic
and this column holds the date in seconds since the epic,
multiplying it by a thousand will give us the value we need.
The start date is included again,
this time converted to a date format.
This column uses an alias,
so the column gets renamed to StartDate in the view.
This might seem a bit strange. Why include the same column twice?
It'll make more sense when you've seen the result of running this query.
We want to group all the timings from the same date together,
to do things like calculating the total for each day
and selecting rows for a certain date.
Using the start time column won't work
because that's in seconds, not days.
The SQL date function converts our epic date
into a more readable format,
and we'll see what that looks like shortly.
I'm not going to include a link for the documentation about the SQLite
date function.
You're way past the point of having to be told to Google for the term
SQLite date function to find out more about it.
Note that we want to display the unix timestamp in local time.
If we don't include the local time modifier,
a timing that was performed before about 9:00 a.m. in Australia
will appear to be the day before.
Australia is between 8 and 11 hours ahead of UTC,
depending on which state you're in and what part of the year it is.
Similarly, someone working late in America
would find their timings appearing for the following day
because the United States is between 5 and 8 hours
behind UTC. For example,
10 o'clock at night in New York is 2 or 3 o'clock the following morning
in UTC.
We also want to sum the durations.
The SQLite sum function totals the values of a column.
It works with the group by clause
to produce the total duration for all the rows with the same task ID
and start date.
The group by clause at the end
make sure that only rows for the same task and date are totaled.
This will give the total duration for each task each day.
The group by clause is interesting
because it uses a column that's not included in the view.
It's perfectly valid to do that
as long as the column does exist in one of the tables that we're joining of course.
We could have grouped by the task name instead
but it's more efficient to use a primary key column, whenever possible.
Three of the column names are coming from the underlying tables,
tasks and timings.
Any columns that don't have an "AS" clause
will keep the same name as they had in those original tables.
Okay, does it work?
That's a good start. We've got some data, and it looks like what we need.
The data is ordered by the columns that we used in the group by clause.
And scrolling up, we can see the data for the earlier tasks.
Now we can see the data
that start date column makes more sense.
It's taken a time in seconds
and converted it to a date in the form year, month, day.
The values for each row will include durations from several rows of the tables,
grouped together by that start date value.
All we've really tested though is that the view is valid SQL.
We don't know that the values are correct.
When you're doing this in your own apps,
you need to check the results that the view produces,
that's tedious but it's necessary.
I suggest generating a much smaller amount of test data to start with.
You can then add the values up manually
to make sure the totals in this view agree with what you'd expect.
You can also export the data from the SQLite database
as a CSV file,
and we saw how to do that in the SQL videos in an earlier section.
The CSV file can be imported into a spreadsheet program,
which makes validating the values a lot easier.
As I said, that bit's quite tedious,
but if your app doesn't produce accurate reports,
then it's really of no use to anyone.
Remember, that you can drop views and recreate them
without affecting the underlying data.
That's why I prefixed the view name with VW by the way.
It's useful to have an indication that something's a view
rather than a table.
It stops you from attempting to add records to the view,
which won't work in SQLite
and it also lets you see quickly which objects you can delete
without losing data.
Okay. We've got the SQL to create our views,
so I'll stop this video here.
In the next video, we'll create the contract for this new view
and add the code to create it in our database class.
I'll see you then.