Welcome back, everyone.
At the moment, the app doesn't save timings with a duration
shorter than the value specified in settings.
That's a useful feature.
But if the user reduces the value to ignore,
those timings won't appear in the report.
They weren't saved, and they're lost for good.
An improvement could be to save all timings
but not include the short ones in the report.
That's not as easy as it sounds
mainly because SQLite doesn't support parameterized views.
You can't pass a value to the view to filter out the durations
that we don't want to include in the totals.
It's possible to work around that
by reading the parameter value from a table.
We'll work on changing our app to do that in the next video.
In this video, we'll have a look at how this is going to work.
I've created a simple version of our database,
and you can download it from the resources in this video.
Download the database to a suitable directory,
and we'll use it to experiment with the queries that we'll need.
The SQLite3 command line tool
is perfectly adequate for doing this,
but typing into a terminal doesn't show up very well in videos.
I'm going to use a program called DB Browser for SQLite.
It's free, but there are other GUI front ends for SQLite
that students have found useful.
Google for SQLite GUI
and you'll find the DB Browser
and another program called SQLite Studio.
Use whichever best suits the way you work or stick with the command line.
I've got DB Browser running full screen,
and I've opened the param query database.
I'll drag the right-hand pane as small as it'll go
to let us see the database structure better.
You'll use the .schema command
if you're using the sqlite3 command line tool.
The timings table is a simplified version of our apps timings table,
containing just the columns we need to demonstrate what we're going to do.
You can expand it to see the columns that it contains.
There's also a parameters table
that will contain a single row
with the value that we want to filter on.
This is the table that we're going to add to our database
to allow the view to be filtered.
Let's have a look at the data in parameters.
I'll click the Execute SQL tab and enter a query.
Then click the run button.
At the moment, we've got the value 0 in the table.
The query we'll be using to retrieve that value
will be select parameters.value from parameters
where parameters._ID=1.
Run the query,
and again, we get a single value returned.
That's the value that we're going to use to filter the durations.
The timings table contains four rows.
We can get the total duration by summing the durations
grouping by the task ID.
Run the query, and you'll see the answer.
That's pretty much what our app's doing at the moment.
The SQL in the view is slightly more complicated
because it joins the tasks table
but this simplified SQL will let us see how we can filter the durations.
If we want to ignore short durations,
we can add a where clause.
Notice that the total duration changed to 45
because we've filtered out the 5 value.
That's what we're trying to achieve,
but we can't pass that filter value 5 in this example
into our view.
What we can do is use a subquery instead of a fixed value.
We can use the value column from the parameters
table in a where clause.
That gives the same total that we had before, 50 for task 1.
If we change the value of the parameter we get a different result.
Let's ignore timings less than 5 seconds again.
Cut the query because we are going to need it again soon.
Run the query but we don't see any output in the middle pane,
that only shows the result of select queries
but the bottom pane confirms the code did execute.
Now we can run the previous query again.
This time we get the total 45.
The duration of 5 hasn't been included.
Change the parameter to 12 and
that should ignore the first two timings.
This time, we get 35 which is correct.
The first two durations aren't included in the total.
Okay. We're using a sub query in the parameters table
to find the value to use in the where clause.
The app will update the parameters table
when the user changes the number of seconds to ignore.
When the parameter value changes,
the sum query will produce different results.
We're using a database view in our app,
and we'll be putting that where clause with the subquery
into the view.
All of the work of calculating the correct totals
will be performed by the SQLite database.
All our app has to do
is make sure the value in the parameters table is updated
to contain the value that the user sets in the app settings.
We do need to provide support for the extra table
in our app database and at provider classes.
And we'll do that, in the next video. See you over there.