1
00:00:04,250 --> 00:00:05,050
Welcome back.

2
00:00:05,850 --> 00:00:09,150
We've now got our app creating version 3 of the database,

3
00:00:09,450 --> 00:00:12,810
and we've added some test timing data in the last video.

4
00:00:13,920 --> 00:00:17,720
For our reports, we're going to use a SQLite view,

5
00:00:18,320 --> 00:00:21,620
which means the database will need upgrading again.

6
00:00:22,620 --> 00:00:26,320
Testing the upgrade, from version 2 of the database to version 3,

7
00:00:26,520 --> 00:00:27,520
will be easy.

8
00:00:28,320 --> 00:00:31,920
It's always easy to test the upgrade from the previous version.

9
00:00:33,320 --> 00:00:37,420
Where things get a bit trickier is when users don't update the app

10
00:00:37,420 --> 00:00:38,420
straight away.

11
00:00:38,970 --> 00:00:41,270
They may run with version one of the database

12
00:00:41,270 --> 00:00:43,870
and not update until we've released a version

13
00:00:44,230 --> 00:00:46,830
that uses the version 3 database.

14
00:00:47,380 --> 00:00:50,480
That means we always have to test our upgrade process

15
00:00:50,480 --> 00:00:53,680
from every previous version to make sure it works

16
00:00:53,680 --> 00:00:55,680
whichever database is on the device.

17
00:00:58,080 --> 00:01:01,380
I know we haven't released previous versions of the app

18
00:01:01,680 --> 00:01:03,280
so this is a bit artificial.

19
00:01:03,530 --> 00:01:07,530
But we're going to go through the process to understand what we need to do

20
00:01:07,530 --> 00:01:09,130
when our database does change.

21
00:01:10,430 --> 00:01:13,630
You can't be sure that your database will be the final version.

22
00:01:14,180 --> 00:01:16,680
We could make a mistake with our view, for example,

23
00:01:16,980 --> 00:01:19,680
and have to release a new version to fix a bug.

24
00:01:20,580 --> 00:01:24,980
For that reason, it's a good idea to keep a copy of each database version.

25
00:01:25,380 --> 00:01:26,380
So let's do that.

26
00:01:28,180 --> 00:01:30,880
I've already got a copy of version 1 and version 2

27
00:01:31,240 --> 00:01:32,540
from a few videos ago.

28
00:01:33,240 --> 00:01:35,240
I've copied them to different directories,

29
00:01:35,600 --> 00:01:37,900
TaskTimerdb-version1,

30
00:01:38,200 --> 00:01:40,600
and TaskTimerdb-version2.

31
00:01:41,600 --> 00:01:45,500
My emulator is now populated with the version 3 database,

32
00:01:45,500 --> 00:01:49,600
and I'll copy that onto my mac using the device file explorer.

33
00:02:01,960 --> 00:02:04,460
Now the database has been copied back,

34
00:02:04,860 --> 00:02:07,860
I'll use the max finder to make another copy of it.

35
00:02:08,520 --> 00:02:12,020
On windows, you'd use the file explorer or nautilus

36
00:02:12,020 --> 00:02:13,520
or whatever on Linux.

37
00:02:14,420 --> 00:02:18,520
You can also copy the directory from the terminal if you're comfortable doing that.

38
00:02:19,320 --> 00:02:23,680
The database that I've copied to tasktimerdb-version3

39
00:02:24,080 --> 00:02:27,280
is to be used when testing upgrades to different versions.

40
00:02:28,380 --> 00:02:30,580
If I go using it to experiment on,

41
00:02:30,580 --> 00:02:35,180
I might change the structure and that would invalidate any testing that I do.

42
00:02:35,940 --> 00:02:39,240
That's why it's important to only work with a copy of the database.

43
00:02:39,740 --> 00:02:43,240
Don't modify the backup copies that you're going to use for testing.

44
00:02:44,490 --> 00:02:47,850
All right. I'll copy tasktimerdb-version3

45
00:02:48,250 --> 00:02:51,550
and call the new directory tasktimerdb-

46
00:02:51,550 --> 00:02:53,050
-experimenting.

47
00:03:12,550 --> 00:03:16,210
Back in android studio, I'll open the terminal pane

48
00:03:16,210 --> 00:03:18,010
and change into that directory.

49
00:03:24,260 --> 00:03:27,460
I'll be using this terminal pane a lot the next few minutes.

50
00:03:27,960 --> 00:03:31,560
So I'm going to put into floating mode and drag it up near the top of the screen,

51
00:03:31,560 --> 00:03:33,760
away from the subtitles if you're using them.

52
00:03:34,660 --> 00:03:38,060
I don't need the device explorer anymore, so I'll close it.

53
00:03:57,160 --> 00:04:00,820
Open the database in the SQLite3 command line utility.

54
00:04:08,070 --> 00:04:10,670
And while we're here, we can check to make sure

55
00:04:10,670 --> 00:04:13,330
that there is some test data in the timings table.

56
00:04:22,480 --> 00:04:26,730
That looks good. Now we can create a view to join the tasks

57
00:04:26,730 --> 00:04:30,230
and timings tables to create the data for our reports.

58
00:04:30,630 --> 00:04:34,880
Looking at the timings table, the first column is the ID from the timings

59
00:04:34,880 --> 00:04:37,240
table that will be unique.

60
00:04:37,240 --> 00:04:39,840
Each timing row will have a unique ID.

61
00:04:40,390 --> 00:04:42,390
Next, we have the task ID.

62
00:04:43,090 --> 00:04:45,890
That can be used to join this timings table

63
00:04:45,890 --> 00:04:47,290
to the tasks table

64
00:04:48,080 --> 00:04:50,680
to let us get the task name and the description.

65
00:04:51,380 --> 00:04:53,380
The third column is the start time.

66
00:04:53,880 --> 00:04:57,780
Remember, we're storing the number of seconds since the start of the epic

67
00:04:58,180 --> 00:05:00,780
which was the 1st of January 1970.

68
00:05:01,580 --> 00:05:04,180
Numbers like that aren't very useful to our users

69
00:05:04,680 --> 00:05:06,980
but SQLite has a date function

70
00:05:06,980 --> 00:05:10,480
that can convert the number into a more user-friendly format.

71
00:05:10,980 --> 00:05:11,980
We'll see that in a minute.

72
00:05:12,780 --> 00:05:16,580
Finally, there's a duration stored as a number of seconds.

73
00:05:18,080 --> 00:05:20,680
If we stop and think about how the app will be used,

74
00:05:21,180 --> 00:05:25,330
the users will probably be starting and stopping their tasks several times a day.

75
00:05:25,930 --> 00:05:29,130
After all, if they spent all day working on the same task,

76
00:05:29,490 --> 00:05:31,290
they wouldn't need the app in the first place.

77
00:05:32,170 --> 00:05:35,870
So our view will need to add up all the timings for a task

78
00:05:36,230 --> 00:05:40,030
for each day. Once again, SQLite can help with this.

79
00:05:40,830 --> 00:05:44,030
It's got a sum function that will do just that.

80
00:05:44,530 --> 00:05:48,330
All we have to do is tell it to group the rows by the date

81
00:05:48,330 --> 00:05:49,330
and task columns.

82
00:05:50,320 --> 00:05:53,620
You don't want to watch me typing SQL, so I'll paste it in

83
00:05:53,620 --> 00:05:55,920
and leave it on the screen while we talk about it.

84
00:06:00,910 --> 00:06:04,900
We're creating a new view that joins the tasks table

85
00:06:04,900 --> 00:06:09,100
to the timings table. The columns we want to include are

86
00:06:09,100 --> 00:06:12,100
the task.Name and the task.Description.

87
00:06:13,400 --> 00:06:16,200
We need the start time column in the original form

88
00:06:16,200 --> 00:06:19,800
to display the date in a format that our user recognizes.

89
00:06:21,000 --> 00:06:23,200
You'll see that being used in our code

90
00:06:23,200 --> 00:06:25,560
when we use the date format class.

91
00:06:26,160 --> 00:06:28,860
We need a date in milliseconds since the epic

92
00:06:29,660 --> 00:06:32,660
and this column holds the date in seconds since the epic,

93
00:06:33,160 --> 00:06:36,160
multiplying it by a thousand will give us the value we need.

94
00:06:37,660 --> 00:06:39,880
The start date is included again,

95
00:06:40,180 --> 00:06:42,480
this time converted to a date format.

96
00:06:43,380 --> 00:06:45,380
This column uses an alias,

97
00:06:45,580 --> 00:06:49,880
so the column gets renamed to StartDate in the view.

98
00:06:50,480 --> 00:06:54,680
This might seem a bit strange. Why include the same column twice?

99
00:06:55,980 --> 00:06:59,280
It'll make more sense when you've seen the result of running this query.

100
00:07:00,080 --> 00:07:03,380
We want to group all the timings from the same date together,

101
00:07:03,980 --> 00:07:07,540
to do things like calculating the total for each day

102
00:07:07,540 --> 00:07:09,840
and selecting rows for a certain date.

103
00:07:10,940 --> 00:07:13,240
Using the start time column won't work

104
00:07:13,540 --> 00:07:16,040
because that's in seconds, not days.

105
00:07:17,290 --> 00:07:20,590
The SQL date function converts our epic date

106
00:07:20,590 --> 00:07:22,290
into a more readable format,

107
00:07:22,890 --> 00:07:24,590
and we'll see what that looks like shortly.

108
00:07:25,890 --> 00:07:29,690
I'm not going to include a link for the documentation about the SQLite

109
00:07:29,690 --> 00:07:30,690
date function.

110
00:07:31,490 --> 00:07:34,990
You're way past the point of having to be told to Google for the term

111
00:07:34,990 --> 00:07:37,990
SQLite date function to find out more about it.

112
00:07:39,240 --> 00:07:43,130
Note that we want to display the unix timestamp in local time.

113
00:07:43,930 --> 00:07:46,730
If we don't include the local time modifier,

114
00:07:46,730 --> 00:07:50,630
a timing that was performed before about 9:00 a.m. in Australia

115
00:07:50,630 --> 00:07:52,230
will appear to be the day before.

116
00:07:52,890 --> 00:07:56,790
Australia is between 8 and 11 hours ahead of UTC,

117
00:07:57,120 --> 00:08:00,420
depending on which state you're in and what part of the year it is.

118
00:08:01,120 --> 00:08:03,720
Similarly, someone working late in America

119
00:08:03,720 --> 00:08:06,220
would find their timings appearing for the following day

120
00:08:06,720 --> 00:08:10,320
because the United States is between 5 and 8 hours

121
00:08:10,320 --> 00:08:13,420
behind UTC. For example,

122
00:08:13,420 --> 00:08:18,020
10 o'clock at night in New York is 2 or 3 o'clock the following morning

123
00:08:18,220 --> 00:08:19,210
in UTC.

124
00:08:20,210 --> 00:08:22,570
We also want to sum the durations.

125
00:08:23,230 --> 00:08:26,730
The SQLite sum function totals the values of a column.

126
00:08:27,630 --> 00:08:30,230
It works with the group by clause

127
00:08:30,230 --> 00:08:34,669
to produce the total duration for all the rows with the same task ID

128
00:08:34,870 --> 00:08:35,860
and start date.

129
00:08:37,059 --> 00:08:39,419
The group by clause at the end

130
00:08:39,419 --> 00:08:43,120
make sure that only rows for the same task and date are totaled.

131
00:08:43,720 --> 00:08:47,320
This will give the total duration for each task each day.

132
00:08:47,920 --> 00:08:49,920
The group by clause is interesting

133
00:08:49,920 --> 00:08:52,920
because it uses a column that's not included in the view.

134
00:08:53,620 --> 00:08:55,420
It's perfectly valid to do that

135
00:08:55,420 --> 00:08:59,080
as long as the column does exist in one of the tables that we're joining of course.

136
00:08:59,980 --> 00:09:02,680
We could have grouped by the task name instead

137
00:09:03,280 --> 00:09:07,270
but it's more efficient to use a primary key column, whenever possible.

138
00:09:08,670 --> 00:09:12,030
Three of the column names are coming from the underlying tables,

139
00:09:12,030 --> 00:09:14,020
tasks and timings.

140
00:09:15,020 --> 00:09:17,620
Any columns that don't have an "AS" clause

141
00:09:17,620 --> 00:09:20,620
will keep the same name as they had in those original tables.

142
00:09:21,820 --> 00:09:24,020
Okay, does it work?

143
00:09:33,820 --> 00:09:37,620
That's a good start. We've got some data, and it looks like what we need.

144
00:09:38,280 --> 00:09:42,080
The data is ordered by the columns that we used in the group by clause.

145
00:09:42,480 --> 00:09:46,080
And scrolling up, we can see the data for the earlier tasks.

146
00:09:49,780 --> 00:09:51,780
Now we can see the data

147
00:09:52,280 --> 00:09:55,180
that start date column makes more sense.

148
00:09:56,170 --> 00:09:58,370
It's taken a time in seconds

149
00:09:58,730 --> 00:10:02,730
and converted it to a date in the form year, month, day.

150
00:10:03,730 --> 00:10:07,930
The values for each row will include durations from several rows of the tables,

151
00:10:08,290 --> 00:10:11,190
grouped together by that start date value.

152
00:10:12,390 --> 00:10:16,390
All we've really tested though is that the view is valid SQL.

153
00:10:17,160 --> 00:10:19,360
We don't know that the values are correct.

154
00:10:20,660 --> 00:10:22,660
When you're doing this in your own apps,

155
00:10:22,860 --> 00:10:25,760
you need to check the results that the view produces,

156
00:10:26,060 --> 00:10:28,960
that's tedious but it's necessary.

157
00:10:29,950 --> 00:10:33,950
I suggest generating a much smaller amount of test data to start with.

158
00:10:34,750 --> 00:10:37,110
You can then add the values up manually

159
00:10:37,110 --> 00:10:40,710
to make sure the totals in this view agree with what you'd expect.

160
00:10:42,210 --> 00:10:45,410
You can also export the data from the SQLite database

161
00:10:45,710 --> 00:10:47,210
as a CSV file,

162
00:10:47,810 --> 00:10:51,610
and we saw how to do that in the SQL videos in an earlier section.

163
00:10:53,170 --> 00:10:57,160
The CSV file can be imported into a spreadsheet program,

164
00:10:57,520 --> 00:11:00,180
which makes validating the values a lot easier.

165
00:11:00,830 --> 00:11:03,730
As I said, that bit's quite tedious,

166
00:11:04,330 --> 00:11:07,530
but if your app doesn't produce accurate reports,

167
00:11:08,030 --> 00:11:09,730
then it's really of no use to anyone.

168
00:11:10,390 --> 00:11:13,690
Remember, that you can drop views and recreate them

169
00:11:13,690 --> 00:11:15,990
without affecting the underlying data.

170
00:11:17,190 --> 00:11:21,290
That's why I prefixed the view name with VW by the way.

171
00:11:21,690 --> 00:11:25,050
It's useful to have an indication that something's a view

172
00:11:25,410 --> 00:11:26,610
rather than a table.

173
00:11:27,410 --> 00:11:29,910
It stops you from attempting to add records to the view,

174
00:11:30,270 --> 00:11:31,870
which won't work in SQLite

175
00:11:32,370 --> 00:11:36,070
and it also lets you see quickly which objects you can delete

176
00:11:36,070 --> 00:11:37,370
without losing data.

177
00:11:38,370 --> 00:11:41,970
Okay. We've got the SQL to create our views,

178
00:11:41,970 --> 00:11:43,570
so I'll stop this video here.

179
00:11:44,270 --> 00:11:48,270
In the next video, we'll create the contract for this new view

180
00:11:48,630 --> 00:11:51,730
and add the code to create it in our database class.

181
00:11:52,530 --> 00:11:53,530
I'll see you then.