1 00:00:03,799 --> 00:00:05,850 Alright so let's talk about the 2 00:00:05,850 --> 00:00:09,059 database for this application. So the 3 00:00:09,059 --> 00:00:10,679 structure of the database is fairly 4 00:00:10,679 --> 00:00:13,139 simple, but it does use joined 5 00:00:13,139 --> 00:00:15,389 tables and that makes it a good example 6 00:00:15,389 --> 00:00:17,880 for learning how to implement a Content 7 00:00:17,880 --> 00:00:20,609 Provider. Now once you've learned how to 8 00:00:20,609 --> 00:00:23,160 perform the basic insert, update, delete 9 00:00:23,160 --> 00:00:25,529 and query operations on our joined 10 00:00:25,529 --> 00:00:28,019 tables, if you need to work with a single 11 00:00:28,019 --> 00:00:30,720 table, then that should be easy. So let's 12 00:00:30,720 --> 00:00:32,040 have a look at the structure of the 13 00:00:32,040 --> 00:00:35,190 database that we'll be using. Now you may 14 00:00:35,190 --> 00:00:37,020 want to refer back to the diagram I'm 15 00:00:37,020 --> 00:00:39,149 about to show, so I'll add it to the 16 00:00:39,149 --> 00:00:42,750 resources for this video. Alright, so 17 00:00:42,750 --> 00:00:45,570 we've got two tables here; firstly Tasks - 18 00:00:45,570 --> 00:00:47,940 that's going to hold the information on 19 00:00:47,940 --> 00:00:50,879 the tasks, and then Timings which will 20 00:00:50,879 --> 00:00:53,670 store the date/times that the tasks were 21 00:00:53,670 --> 00:00:56,160 started, and the amount of time spent on 22 00:00:56,160 --> 00:00:58,949 each task. I mentioned earlier that the 23 00:00:58,949 --> 00:01:01,440 table must have an ID field called 24 00:01:01,440 --> 00:01:04,349 _id if we want to use it with 25 00:01:04,349 --> 00:01:06,510 the CursorAdapter class - which we'll be 26 00:01:06,510 --> 00:01:08,520 doing - and it's a good idea to always 27 00:01:08,520 --> 00:01:10,799 call your INTEGER PRIMARY KEY fields 28 00:01:10,799 --> 00:01:14,040 _id when working with Android 29 00:01:14,040 --> 00:01:16,770 databases. That way you'll always have 30 00:01:16,770 --> 00:01:18,689 the option of using classes such as the 31 00:01:18,689 --> 00:01:21,240 CursorAdapter, even if you didn't 32 00:01:21,240 --> 00:01:24,509 originally intend to use it. To be 33 00:01:24,509 --> 00:01:26,640 completely accurate, it's the underlying 34 00:01:26,640 --> 00:01:29,340 Cursor that must have the _id 35 00:01:29,340 --> 00:01:31,740 column. If your primary key column has a 36 00:01:31,740 --> 00:01:34,500 different name then you can alias the 37 00:01:34,500 --> 00:01:37,560 column name in the query or view, and we 38 00:01:37,560 --> 00:01:40,140 saw how to do that using the AS keyword, 39 00:01:40,140 --> 00:01:43,020 in the videos on SQL earlier in the 40 00:01:43,020 --> 00:01:45,000 previous section. But why complicate 41 00:01:45,000 --> 00:01:47,280 things? If you know you're developing a 42 00:01:47,280 --> 00:01:49,470 database for Android, then include an 43 00:01:49,470 --> 00:01:52,009 _id column as your primary key. 44 00:01:52,009 --> 00:01:55,170 Alright so the Tasks table just stores 45 00:01:55,170 --> 00:01:57,479 the task Name and the Description, as 46 00:01:57,479 --> 00:02:00,450 well as a column called SortOrder that 47 00:02:00,450 --> 00:02:02,969 can be used to list the tasks in a 48 00:02:02,969 --> 00:02:04,920 different order, instead of just 49 00:02:04,920 --> 00:02:08,008 alphabetically by name. Now you may want 50 00:02:08,008 --> 00:02:09,660 to add extra fields once the app's 51 00:02:09,660 --> 00:02:12,180 working - it may be useful to be able to 52 00:02:12,180 --> 00:02:14,190 group tasks together, for example, 53 00:02:14,190 --> 00:02:16,350 by adding a Category field. For this 54 00:02:16,350 --> 00:02:18,180 example though, we'll stick with just 55 00:02:18,180 --> 00:02:20,640 those three columns and the id. 56 00:02:20,640 --> 00:02:23,610 Now the Timings table stores a number of 57 00:02:23,610 --> 00:02:26,550 StartTimes and Durations for each task, 58 00:02:26,550 --> 00:02:30,480 and it uses the TaskId column as a link 59 00:02:30,480 --> 00:02:33,390 to the Tasks table. Now you may be 60 00:02:33,390 --> 00:02:35,430 wondering why the StartTime column is 61 00:02:35,430 --> 00:02:37,740 an INTEGER. Dealing with dates and times 62 00:02:37,740 --> 00:02:39,930 can be very complicated when you 63 00:02:39,930 --> 00:02:42,030 start to consider the various ways that 64 00:02:42,030 --> 00:02:44,790 dates can be represented, and different 65 00:02:44,790 --> 00:02:48,000 time zones around the world and so on. So 66 00:02:48,000 --> 00:02:50,580 the easiest way to store date and time 67 00:02:50,580 --> 00:02:53,220 information in a database is to store 68 00:02:53,220 --> 00:02:55,950 the number of milliseconds since some 69 00:02:55,950 --> 00:02:58,380 base date. Now the base date is known 70 00:02:58,380 --> 00:03:01,739 as the epoch, and in the Java classes the 71 00:03:01,739 --> 00:03:04,940 epoch is midnight on the 1st of January 72 00:03:04,940 --> 00:03:07,830 1970. Now we're not really interested in 73 00:03:07,830 --> 00:03:10,470 millisecond accuracy, so our code will 74 00:03:10,470 --> 00:03:13,530 divide the millisecond values by 1000, 75 00:03:13,530 --> 00:03:16,500 and we'll store seconds instead. The 76 00:03:16,500 --> 00:03:18,900 duration will be worked out by 77 00:03:18,900 --> 00:03:21,900 subtracting the StartTime from the time 78 00:03:21,900 --> 00:03:24,810 that the task is stopped. Again, we'll 79 00:03:24,810 --> 00:03:27,480 store seconds in the INTEGER Duration 80 00:03:27,480 --> 00:03:30,510 column. So that's the basic tables. We'll 81 00:03:30,510 --> 00:03:32,730 store the Task information in one table 82 00:03:32,730 --> 00:03:35,790 and the Timings in another, and link them 83 00:03:35,790 --> 00:03:39,090 together using the Task ID column. Now 84 00:03:39,090 --> 00:03:41,130 I've shown a view on the slide, and we'll 85 00:03:41,130 --> 00:03:42,810 be using it when we come to produce 86 00:03:42,810 --> 00:03:45,450 reports, showing how long was spent on 87 00:03:45,450 --> 00:03:48,450 each task. It's just used to join the two 88 00:03:48,450 --> 00:03:50,910 tables together - and I've shown the JOIN 89 00:03:50,910 --> 00:03:54,450 clause on the slide. Our reports 90 00:03:54,450 --> 00:03:56,400 will come from the TaskDurations view, 91 00:03:56,400 --> 00:03:59,100 which converts the INTEGER seconds into 92 00:03:59,100 --> 00:04:01,500 a date format so that we can display it. 93 00:04:01,500 --> 00:04:04,260 And it also totals up the seconds spent 94 00:04:04,260 --> 00:04:06,510 on each task which is much easier than 95 00:04:06,510 --> 00:04:09,150 trying to do it in our Kotlin code. But 96 00:04:09,150 --> 00:04:10,500 we'll be looking at that view later in 97 00:04:10,500 --> 00:04:12,810 the section. For now, we're just 98 00:04:12,810 --> 00:04:15,090 interested in the two tables and we're 99 00:04:15,090 --> 00:04:17,459 going to start by getting the app to add 100 00:04:17,459 --> 00:04:20,250 and edit tasks, and display them in a 101 00:04:20,250 --> 00:04:23,220 list. OK so let's have a look at 102 00:04:23,220 --> 00:04:25,140 creating the database and the Tasks 103 00:04:25,140 --> 00:04:27,600 table that we'll be starting off with. 104 00:04:27,600 --> 00:04:29,700 Now we'll be getting our app to create 105 00:04:29,700 --> 00:04:32,010 the database in code, but it's a lot 106 00:04:32,010 --> 00:04:34,680 easier to sort out the SQL statements 107 00:04:34,680 --> 00:04:36,990 using the sqlite3 command-line 108 00:04:36,990 --> 00:04:39,360 interface, rather than trying to debug 109 00:04:39,360 --> 00:04:42,240 both our code and the SQL statements 110 00:04:42,240 --> 00:04:45,000 at the same time. So we'll run sqlite 111 00:04:45,000 --> 00:04:47,460 3 and get our sequel working 112 00:04:47,460 --> 00:04:49,590 from the command-line before trying to 113 00:04:49,590 --> 00:04:52,110 use it in Kotlin code. But before that 114 00:04:52,110 --> 00:04:53,520 though, let's start a new Android Studio 115 00:04:53,520 --> 00:04:55,860 project, so I'm going to click on Start a 116 00:04:55,860 --> 00:04:57,750 new Android Studio project and call 117 00:04:57,750 --> 00:05:01,950 this one Task Timer. We've got Kotlin 118 00:05:01,950 --> 00:05:03,630 support clicked there. We're going to leave 119 00:05:03,630 --> 00:05:07,170 API 17 set up as per normal. We're going to 120 00:05:07,170 --> 00:05:08,370 leave everything else unticked as we 121 00:05:08,370 --> 00:05:11,250 normally do, click on Next. We're going to 122 00:05:11,250 --> 00:05:14,010 choose Basic Activity here, and that's 123 00:05:14,010 --> 00:05:15,960 because we don't need or won't be using 124 00:05:15,960 --> 00:05:18,210 a floating action button. So choose the 125 00:05:18,210 --> 00:05:20,730 Basic Activity, or whichever template 126 00:05:20,730 --> 00:05:22,920 includes a menu, if Google happens to 127 00:05:22,920 --> 00:05:24,210 have changed them by the time you come 128 00:05:24,210 --> 00:05:26,720 to watch this video, and click on Next. 129 00:05:26,720 --> 00:05:29,250 Now we can leave these names on screen 130 00:05:29,250 --> 00:05:31,590 as they are, but we are going to be using 131 00:05:31,590 --> 00:05:33,540 Fragments in this app, so we want to 132 00:05:33,540 --> 00:05:35,550 check on, check the Use a Fragment 133 00:05:35,550 --> 00:05:39,870 check-box and then click on Finish. 134 00:05:39,870 --> 00:05:41,640 Now we'll be looking at what Fragments 135 00:05:41,640 --> 00:05:44,310 are and why they're useful as we build 136 00:05:44,310 --> 00:05:46,260 this app, so don't worry about why we 137 00:05:46,260 --> 00:05:49,650 ticked that box just yet. Alright, so I'm 138 00:05:49,650 --> 00:05:56,150 just going to make a bit more space here, 139 00:05:56,150 --> 00:05:58,290 and now that the project's finished 140 00:05:58,290 --> 00:06:00,330 building, I'm going to click over here to 141 00:06:00,330 --> 00:06:02,820 the Terminal tab and we're going to 142 00:06:02,820 --> 00:06:05,190 start doing some SQL. And what I'll 143 00:06:05,190 --> 00:06:06,540 actually do is I'll take the opportunity 144 00:06:06,540 --> 00:06:10,440 just to move this into floating mode, so 145 00:06:10,440 --> 00:06:11,580 I can just drag it up the screen a 146 00:06:11,580 --> 00:06:13,740 little bit and resize it so that it 147 00:06:13,740 --> 00:06:15,120 doesn't affect any subtitles, if you 148 00:06:15,120 --> 00:06:17,310 happen to be watching this video with 149 00:06:17,310 --> 00:06:19,620 subtitles. So there's no need for you to do 150 00:06:19,620 --> 00:06:21,330 that, but this can be useful by the way 151 00:06:21,330 --> 00:06:23,460 if you've got two monitors, so you can 152 00:06:23,460 --> 00:06:24,870 drag the floating windows into your 153 00:06:24,870 --> 00:06:26,640 other display. Alright, so let's start 154 00:06:26,640 --> 00:06:28,800 using sqlite3, and we're 155 00:06:28,800 --> 00:06:30,960 going to create a new database - so 156 00:06:30,960 --> 00:06:33,990 sqlite3, and we're going to 157 00:06:33,990 --> 00:06:40,520 call the database TaskTimer.db. 158 00:06:40,520 --> 00:06:43,350 Now this does rely on the path having 159 00:06:43,350 --> 00:06:45,450 been set properly, so make sure you've 160 00:06:45,450 --> 00:06:47,100 followed through the earlier videos in 161 00:06:47,100 --> 00:06:49,110 this course for your operating system, to 162 00:06:49,110 --> 00:06:51,390 set the path. Alright, so now that we're 163 00:06:51,390 --> 00:06:53,490 in sqlite3 we can enter the SQL 164 00:06:53,490 --> 00:06:57,060 commands to create our Tasks table. So 165 00:06:57,060 --> 00:07:03,530 I'm going to type CREATE TABLE Tasks, 166 00:07:03,530 --> 00:07:08,100 and parentheses _id space and it's 167 00:07:08,100 --> 00:07:10,920 going to be INTEGER space PRIMARY space 168 00:07:10,920 --> 00:07:17,040 KEY space NOT space NULL comma space. 169 00:07:17,040 --> 00:07:21,600 And it's going to be Name TEXT space NOT NULL, NOT space 170 00:07:21,600 --> 00:07:25,860 NULL comma and Description space TEXT comma 171 00:07:25,860 --> 00:07:30,600 and space SortOrder space INTEGER. Then 172 00:07:30,600 --> 00:07:33,000 right parentheses and semicolon. That's 173 00:07:33,000 --> 00:07:35,700 our Tasks table. Now sqlite doesn't 174 00:07:35,700 --> 00:07:38,610 confirm the commands have worked, so as 175 00:07:38,610 --> 00:07:40,530 long as you don't get an error, then 176 00:07:40,530 --> 00:07:42,420 everything's fine. And you can check that 177 00:07:42,420 --> 00:07:46,070 it's worked using the .schema command. 178 00:07:46,070 --> 00:07:49,020 That shows the commands used to create 179 00:07:49,020 --> 00:07:51,630 any objects in the database, and you can 180 00:07:51,630 --> 00:07:53,370 see our Tasks table, so that's worked 181 00:07:53,370 --> 00:07:55,470 fine. Now I'm going to insert a couple of 182 00:07:55,470 --> 00:07:57,600 rows, just to make sure that everything 183 00:07:57,600 --> 00:07:59,490 is working as it should. I might be 184 00:07:59,490 --> 00:08:00,870 interested in tracking how much time I 185 00:08:00,870 --> 00:08:03,060 spent on this section, for example, as 186 00:08:03,060 --> 00:08:05,220 well as my various Udemy courses, so I'm 187 00:08:05,220 --> 00:08:08,280 going to create some tasks for those. So 188 00:08:08,280 --> 00:08:13,500 INSERT INTO Tasks space parentheses, 189 00:08:13,500 --> 00:08:16,040 and it's going to be Names comma space Description 190 00:08:16,040 --> 00:08:20,060 right parentheses space VALUES 191 00:08:20,060 --> 00:08:22,740 parentheses, and in single quotes - double 192 00:08:22,740 --> 00:08:24,660 quotes should work okay - we're going to 193 00:08:24,660 --> 00:08:27,600 type TaskTimer in single quotes comma 194 00:08:27,600 --> 00:08:30,420 space. Then single quote again TaskTimer 195 00:08:30,420 --> 00:08:34,620 app creation closing off single quote 196 00:08:34,620 --> 00:08:37,409 right parenthesis and semicolon. Now we're 197 00:08:37,409 --> 00:08:38,850 going to use a slightly different 198 00:08:38,850 --> 00:08:41,610 statement for the second and third tasks, and 199 00:08:41,610 --> 00:08:43,500 I'll talk about that shortly. So we'll do 200 00:08:43,500 --> 00:08:47,460 INSERT INTO, for this next line, Tasks and 201 00:08:47,460 --> 00:08:50,240 in space parentheses, it's going to be Name comma 202 00:08:50,240 --> 00:08:54,330 Description comma and SortOrder 203 00:08:54,330 --> 00:08:57,370 space VALUES parentheses. We're going to 204 00:08:57,370 --> 00:09:01,960 go with Android Java comma space single 205 00:09:01,960 --> 00:09:05,620 quotes Android Java course. It's a Java 206 00:09:05,620 --> 00:09:07,600 version of my course. Then I'm going to put comma 207 00:09:07,600 --> 00:09:10,950 2 then right parentheses and semicolon. 208 00:09:10,950 --> 00:09:14,589 So for the first entry, the TaskTimer 209 00:09:14,589 --> 00:09:17,650 entry, we didn't specify the field Sort 210 00:09:17,650 --> 00:09:19,930 Order, so therefore we got a null Sort 211 00:09:19,930 --> 00:09:22,150 Order. But in the case with the second 212 00:09:22,150 --> 00:09:24,430 entry, I've specified SortOrder as the 213 00:09:24,430 --> 00:09:26,860 field to insert into and a value, in this 214 00:09:26,860 --> 00:09:29,080 case 2, so that we can contrast and see 215 00:09:29,080 --> 00:09:30,910 the difference. Now we're not going to 216 00:09:30,910 --> 00:09:33,010 insist that users enter a SortOrder for 217 00:09:33,010 --> 00:09:35,170 this application, so it's important to 218 00:09:35,170 --> 00:09:36,760 test that the database copes if that 219 00:09:36,760 --> 00:09:38,980 value's left out, and the actual values 220 00:09:38,980 --> 00:09:41,589 are arbitrary. Larger values will sort 221 00:09:41,589 --> 00:09:43,240 down the list, so it's really up to the 222 00:09:43,240 --> 00:09:45,700 user how they organize the tasks, but 223 00:09:45,700 --> 00:09:47,290 let's do the last entry. I'm going to do 224 00:09:47,290 --> 00:09:49,240 an up arrow here, and we'll get the 225 00:09:49,240 --> 00:09:50,710 information back again. I can just 226 00:09:50,710 --> 00:09:52,720 overwrite that. I'm going to specify 227 00:09:52,720 --> 00:09:54,100 zero for the SortOrder, and for 228 00:09:54,100 --> 00:09:58,420 Description I'm going to specify Android 229 00:09:58,420 --> 00:10:01,050 Kotlin course, which of course is this one, 230 00:10:01,050 --> 00:10:06,040 and we'll put Android Kotlin for the 231 00:10:06,040 --> 00:10:09,370 first part for the name. Alright so we've 232 00:10:09,370 --> 00:10:11,200 got three entries there now. Now by 233 00:10:11,200 --> 00:10:13,480 default, by the way, ordering in a 234 00:10:13,480 --> 00:10:15,370 database is undefined unless there's a 235 00:10:15,370 --> 00:10:17,620 primary key, and because we have defined 236 00:10:17,620 --> 00:10:19,390 a primary key, the rows should be 237 00:10:19,390 --> 00:10:20,740 returned in the order that we've added 238 00:10:20,740 --> 00:10:23,110 them. So if we just type SELECT space 239 00:10:23,110 --> 00:10:26,980 * space FROM TASKS, FROM space 240 00:10:26,980 --> 00:10:30,070 Tasks. You can see we've got the three 241 00:10:30,070 --> 00:10:32,050 entries showing in there, in primary key 242 00:10:32,050 --> 00:10:34,150 order. Now we can't use an ORDER BY 243 00:10:34,150 --> 00:10:35,770 clause to sort them differently, so we 244 00:10:35,770 --> 00:10:38,350 could do something like SELECT space 245 00:10:38,350 --> 00:10:42,310 * space FROM space Tasks - and I'm not 246 00:10:42,310 --> 00:10:43,810 going to read ahead and type space 247 00:10:43,810 --> 00:10:45,070 anymore, I think you'll know when you, 248 00:10:45,070 --> 00:10:46,620 where you're meant to enter a space now - 249 00:10:46,620 --> 00:10:52,029 ORDER BY Name semicolon. You can see 250 00:10:52,029 --> 00:10:54,430 that's now ordered by name. And the third 251 00:10:54,430 --> 00:10:59,100 example, just do SELECT * from Tasks 252 00:10:59,100 --> 00:11:05,140 ORDER BY SortOrder semicolon, and we can 253 00:11:05,140 --> 00:11:06,400 see it ordered in 254 00:11:06,400 --> 00:11:08,710 this case by SortOrder. Alright so by 255 00:11:08,710 --> 00:11:10,600 including the SortOrder column, we're 256 00:11:10,600 --> 00:11:12,430 providing a way for the user to show the 257 00:11:12,430 --> 00:11:15,310 tasks in any order they want. The idea is 258 00:11:15,310 --> 00:11:16,870 that they'll probably want the tasks 259 00:11:16,870 --> 00:11:18,880 they're performing most often, to be near 260 00:11:18,880 --> 00:11:20,680 the top of the list. But our database 261 00:11:20,680 --> 00:11:22,750 seems to be okay, and it can cope with 262 00:11:22,750 --> 00:11:25,360 null values for the SortOrder. So now we 263 00:11:25,360 --> 00:11:27,160 can move on with writing the Kotlin code 264 00:11:27,160 --> 00:11:30,190 to create the database in this Tasks 265 00:11:30,190 --> 00:11:32,140 table. So let's quit the sqlite 266 00:11:32,140 --> 00:11:34,560 command-line interface. I'm going to type dot exit, 267 00:11:34,560 --> 00:11:38,140 then I'm going to type exit to close the 268 00:11:38,140 --> 00:11:40,870 terminal session, and we'll finish the 269 00:11:40,870 --> 00:11:42,670 video now. And in the next one we're 270 00:11:42,670 --> 00:11:44,170 ready to start writing some database 271 00:11:44,170 --> 00:11:46,930 code for our app, and we'll start with 272 00:11:46,930 --> 00:11:49,240 the Contract class. So I'll see you in 273 00:11:49,240 --> 00:11:51,720 the next video.