1 00:00:05,400 --> 00:00:06,500 Welcome back, everyone. 2 00:00:06,800 --> 00:00:10,800 At the moment, the app doesn't save timings with a duration 3 00:00:10,800 --> 00:00:13,400 shorter than the value specified in settings. 4 00:00:14,200 --> 00:00:15,560 That's a useful feature. 5 00:00:16,059 --> 00:00:18,760 But if the user reduces the value to ignore, 6 00:00:19,060 --> 00:00:21,260 those timings won't appear in the report. 7 00:00:21,660 --> 00:00:23,660 They weren't saved, and they're lost for good. 8 00:00:24,660 --> 00:00:27,430 An improvement could be to save all timings 9 00:00:27,790 --> 00:00:29,990 but not include the short ones in the report. 10 00:00:31,190 --> 00:00:33,180 That's not as easy as it sounds 11 00:00:33,180 --> 00:00:37,480 mainly because SQLite doesn't support parameterized views. 12 00:00:37,980 --> 00:00:41,980 You can't pass a value to the view to filter out the durations 13 00:00:41,980 --> 00:00:44,080 that we don't want to include in the totals. 14 00:00:45,740 --> 00:00:47,840 It's possible to work around that 15 00:00:47,840 --> 00:00:50,340 by reading the parameter value from a table. 16 00:00:51,240 --> 00:00:54,540 We'll work on changing our app to do that in the next video. 17 00:00:55,040 --> 00:00:58,140 In this video, we'll have a look at how this is going to work. 18 00:00:59,130 --> 00:01:01,730 I've created a simple version of our database, 19 00:01:02,030 --> 00:01:04,830 and you can download it from the resources in this video. 20 00:01:06,130 --> 00:01:08,430 Download the database to a suitable directory, 21 00:01:08,730 --> 00:01:11,730 and we'll use it to experiment with the queries that we'll need. 22 00:01:13,530 --> 00:01:15,830 The SQLite3 command line tool 23 00:01:16,130 --> 00:01:18,330 is perfectly adequate for doing this, 24 00:01:18,730 --> 00:01:22,090 but typing into a terminal doesn't show up very well in videos. 25 00:01:23,090 --> 00:01:27,080 I'm going to use a program called DB Browser for SQLite. 26 00:01:27,630 --> 00:01:31,290 It's free, but there are other GUI front ends for SQLite 27 00:01:31,290 --> 00:01:33,190 that students have found useful. 28 00:01:34,520 --> 00:01:37,020 Google for SQLite GUI 29 00:01:37,020 --> 00:01:39,020 and you'll find the DB Browser 30 00:01:39,220 --> 00:01:42,220 and another program called SQLite Studio. 31 00:01:43,120 --> 00:01:46,620 Use whichever best suits the way you work or stick with the command line. 32 00:01:47,620 --> 00:01:50,220 I've got DB Browser running full screen, 33 00:01:50,470 --> 00:01:53,270 and I've opened the param query database. 34 00:01:54,070 --> 00:01:57,430 I'll drag the right-hand pane as small as it'll go 35 00:01:57,430 --> 00:01:59,630 to let us see the database structure better. 36 00:02:00,830 --> 00:02:03,430 You'll use the .schema command 37 00:02:03,430 --> 00:02:06,430 if you're using the sqlite3 command line tool. 38 00:02:06,930 --> 00:02:11,130 The timings table is a simplified version of our apps timings table, 39 00:02:11,430 --> 00:02:15,630 containing just the columns we need to demonstrate what we're going to do. 40 00:02:16,430 --> 00:02:19,730 You can expand it to see the columns that it contains. 41 00:02:25,030 --> 00:02:27,230 There's also a parameters table 42 00:02:27,230 --> 00:02:29,230 that will contain a single row 43 00:02:29,480 --> 00:02:31,280 with the value that we want to filter on. 44 00:02:35,280 --> 00:02:38,380 This is the table that we're going to add to our database 45 00:02:38,380 --> 00:02:40,180 to allow the view to be filtered. 46 00:02:41,480 --> 00:02:43,780 Let's have a look at the data in parameters. 47 00:02:44,140 --> 00:02:48,040 I'll click the Execute SQL tab and enter a query. 48 00:03:00,240 --> 00:03:02,040 Then click the run button. 49 00:03:04,840 --> 00:03:08,440 At the moment, we've got the value 0 in the table. 50 00:03:11,040 --> 00:03:14,140 The query we'll be using to retrieve that value 51 00:03:14,140 --> 00:03:18,600 will be select parameters.value from parameters 52 00:03:18,600 --> 00:03:21,700 where parameters._ID=1. 53 00:03:53,000 --> 00:03:53,990 Run the query, 54 00:03:56,350 --> 00:03:58,710 and again, we get a single value returned. 55 00:03:59,710 --> 00:04:03,010 That's the value that we're going to use to filter the durations. 56 00:04:03,810 --> 00:04:06,910 The timings table contains four rows. 57 00:04:23,910 --> 00:04:27,210 We can get the total duration by summing the durations 58 00:04:27,510 --> 00:04:29,710 grouping by the task ID. 59 00:04:48,070 --> 00:04:50,170 Run the query, and you'll see the answer. 60 00:04:51,050 --> 00:04:53,650 That's pretty much what our app's doing at the moment. 61 00:04:53,850 --> 00:04:56,750 The SQL in the view is slightly more complicated 62 00:04:56,750 --> 00:04:59,250 because it joins the tasks table 63 00:04:59,250 --> 00:05:04,150 but this simplified SQL will let us see how we can filter the durations. 64 00:05:04,550 --> 00:05:06,950 If we want to ignore short durations, 65 00:05:06,950 --> 00:05:08,450 we can add a where clause. 66 00:05:25,750 --> 00:05:29,110 Notice that the total duration changed to 45 67 00:05:29,510 --> 00:05:31,510 because we've filtered out the 5 value. 68 00:05:32,010 --> 00:05:33,510 That's what we're trying to achieve, 69 00:05:34,010 --> 00:05:37,910 but we can't pass that filter value 5 in this example 70 00:05:37,910 --> 00:05:38,900 into our view. 71 00:05:39,700 --> 00:05:44,280 What we can do is use a subquery instead of a fixed value. 72 00:05:44,880 --> 00:05:48,080 We can use the value column from the parameters 73 00:05:48,080 --> 00:05:49,480 table in a where clause. 74 00:06:37,480 --> 00:06:41,630 That gives the same total that we had before, 50 for task 1. 75 00:06:42,510 --> 00:06:46,610 If we change the value of the parameter we get a different result. 76 00:06:46,910 --> 00:06:50,460 Let's ignore timings less than 5 seconds again. 77 00:06:54,660 --> 00:06:57,660 Cut the query because we are going to need it again soon. 78 00:07:10,460 --> 00:07:13,760 Run the query but we don't see any output in the middle pane, 79 00:07:14,160 --> 00:07:16,860 that only shows the result of select queries 80 00:07:17,260 --> 00:07:20,160 but the bottom pane confirms the code did execute. 81 00:07:20,760 --> 00:07:23,260 Now we can run the previous query again. 82 00:07:30,260 --> 00:07:33,260 This time we get the total 45. 83 00:07:33,660 --> 00:07:36,060 The duration of 5 hasn't been included. 84 00:07:36,610 --> 00:07:38,710 Change the parameter to 12 and 85 00:07:38,710 --> 00:07:41,710 that should ignore the first two timings. 86 00:08:03,070 --> 00:08:06,430 This time, we get 35 which is correct. 87 00:08:06,430 --> 00:08:09,330 The first two durations aren't included in the total. 88 00:08:10,230 --> 00:08:13,730 Okay. We're using a sub query in the parameters table 89 00:08:13,730 --> 00:08:16,330 to find the value to use in the where clause. 90 00:08:16,830 --> 00:08:19,430 The app will update the parameters table 91 00:08:19,430 --> 00:08:22,430 when the user changes the number of seconds to ignore. 92 00:08:22,790 --> 00:08:24,790 When the parameter value changes, 93 00:08:24,790 --> 00:08:27,390 the sum query will produce different results. 94 00:08:28,290 --> 00:08:30,290 We're using a database view in our app, 95 00:08:30,650 --> 00:08:33,950 and we'll be putting that where clause with the subquery 96 00:08:33,950 --> 00:08:35,049 into the view. 97 00:08:35,710 --> 00:08:38,309 All of the work of calculating the correct totals 98 00:08:38,309 --> 00:08:40,909 will be performed by the SQLite database. 99 00:08:41,409 --> 00:08:42,909 All our app has to do 100 00:08:42,909 --> 00:08:46,270 is make sure the value in the parameters table is updated 101 00:08:46,570 --> 00:08:49,930 to contain the value that the user sets in the app settings. 102 00:08:50,590 --> 00:08:53,580 We do need to provide support for the extra table 103 00:08:53,580 --> 00:08:56,580 in our app database and at provider classes. 104 00:08:56,880 --> 00:09:00,480 And we'll do that, in the next video. See you over there.