1 00:00:00,099 --> 00:00:06,339 Alright, so moving on now. 2 00:00:06,339 --> 00:00:07,339 We're 3 00:00:07,339 --> 00:00:09,120 going to go ahead and create, or add the 4 00:00:09,120 --> 00:00:11,520 necessary SQL code, to create our 5 00:00:11,520 --> 00:00:13,020 database. 6 00:00:13,020 --> 00:00:14,020 So we're going to build up the 7 00:00:14,020 --> 00:00:16,040 necessary string, but what we're going to 8 00:00:16,040 --> 00:00:18,400 do is use the constants from our Tasks 9 00:00:18,400 --> 00:00:21,210 Contract class, rather than hard coding 10 00:00:21,210 --> 00:00:23,360 the table and column names, as you can 11 00:00:23,360 --> 00:00:24,510 see in the comment. 12 00:00:24,510 --> 00:00:25,510 So I'm going to come 13 00:00:25,510 --> 00:00:28,340 down here and we'll start typing. 14 00:00:28,340 --> 00:00:29,890 I'll do a 15 00:00:29,890 --> 00:00:32,619 log entry first, a Log.d parentheses 16 00:00:32,619 --> 00:00:37,809 TAG comma onCreate colon starts. 17 00:00:37,809 --> 00:00:38,809 Then 18 00:00:38,809 --> 00:00:39,809 on the next line we're going to type val 19 00:00:39,809 --> 00:00:44,129 sSQL in upper case, equals three double 20 00:00:44,129 --> 00:00:50,011 quotes CREATE TABLE dollar sign left and 21 00:00:50,011 --> 00:00:51,440 right curly braces. 22 00:00:51,440 --> 00:00:55,639 Then we're going to type 23 00:00:55,639 --> 00:00:57,820 TasksContract.TABLE_NAME, then have 24 00:00:57,820 --> 00:01:01,909 the closing right curly brace, then a left 25 00:01:01,909 --> 00:01:03,530 parentheses. 26 00:01:03,530 --> 00:01:04,530 Then on the next line we're 27 00:01:04,530 --> 00:01:06,090 going to put the dollar sign, left and 28 00:01:06,090 --> 00:01:11,079 right curly braces TasksContract dot 29 00:01:11,079 --> 00:01:14,189 Columns.ID right curly brace, and it's 30 00:01:14,189 --> 00:01:18,380 going to be INTEGER space PRIMARY KEY, 31 00:01:18,380 --> 00:01:21,329 with a space between them, then NOT 32 00:01:21,329 --> 00:01:23,690 NULL with a space comma. 33 00:01:23,690 --> 00:01:26,000 On the next line, 34 00:01:26,000 --> 00:01:28,130 dollar sign left and right curly braces. 35 00:01:28,130 --> 00:01:31,820 We're doing the same but for the TASK 36 00:01:31,820 --> 00:01:35,810 underscore NAME right curly brace, and 37 00:01:35,810 --> 00:01:38,299 it's going to be TEXT NOT NULL. 38 00:01:38,299 --> 00:01:40,659 Alright, and 39 00:01:40,659 --> 00:01:41,899 the next two lines what I'm going to do, 40 00:01:41,899 --> 00:01:43,340 is just copy that twice. 41 00:01:43,340 --> 00:01:44,340 It's going to 42 00:01:44,340 --> 00:01:45,679 be easier to change it rather than typing 43 00:01:45,679 --> 00:01:46,960 the whole thing in again. 44 00:01:46,960 --> 00:01:49,119 So TASKS_NAME, or TASK_NAME, 45 00:01:49,119 --> 00:01:52,909 we're going to make dot TASK_ 46 00:01:52,909 --> 00:01:55,789 DESCRIPTION and that's also a text but 47 00:01:55,789 --> 00:01:57,409 it's optional, so we're going to remove 48 00:01:57,409 --> 00:01:58,409 the NOT NULL. 49 00:01:58,409 --> 00:02:00,119 And on the last line, 50 00:02:00,119 --> 00:02:02,270 instead of TASK_NAME we're 51 00:02:02,270 --> 00:02:04,240 going to go with TASK_ 52 00:02:04,240 --> 00:02:05,310 SORT_ORDER. 53 00:02:05,310 --> 00:02:07,149 That's not a text - that's going to be an 54 00:02:07,149 --> 00:02:10,110 INTEGER - and we've already established 55 00:02:10,110 --> 00:02:11,140 in the previous video that that's 56 00:02:11,140 --> 00:02:12,890 optional, so I'm going to remove the 57 00:02:12,890 --> 00:02:14,780 NOT NULL. 58 00:02:14,780 --> 00:02:15,950 Instead what I'm going to do is 59 00:02:15,950 --> 00:02:17,360 then delete the 60 00:02:17,360 --> 00:02:18,360 comma. 61 00:02:18,360 --> 00:02:20,940 We're going to add a parentheses 62 00:02:20,940 --> 00:02:23,250 there, semicolon then I'm going to add 63 00:02:23,250 --> 00:02:24,660 the three double quotes to close off the 64 00:02:24,660 --> 00:02:27,220 string, and on the end of the string I'm 65 00:02:27,220 --> 00:02:29,520 going to do a .replaceIndent. 66 00:02:29,520 --> 00:02:30,520 Then in 67 00:02:30,520 --> 00:02:32,890 parentheses double quotes with a space 68 00:02:32,890 --> 00:02:34,540 in between. 69 00:02:34,540 --> 00:02:35,570 Now we can confirm we've got 70 00:02:35,570 --> 00:02:37,431 it right by logging the string and 71 00:02:37,431 --> 00:02:38,431 checking the logcat. 72 00:02:38,431 --> 00:02:39,670 So let's add a logging entry on the next 73 00:02:39,670 --> 00:02:42,760 line, so Log.d parentheses TAG 74 00:02:42,760 --> 00:02:44,110 comma sSQL. 75 00:02:44,110 --> 00:02:47,030 And obviously, logging will 76 00:02:47,030 --> 00:02:48,740 help if we've made a mistake so that we 77 00:02:48,740 --> 00:02:51,740 can check the SQL in our logcat with 78 00:02:51,740 --> 00:02:52,870 our comment. 79 00:02:52,870 --> 00:02:53,870 And you might be wondering 80 00:02:53,870 --> 00:02:55,770 why I've used replaceIndent on line 81 00:02:55,770 --> 00:02:56,770 35. 82 00:02:56,770 --> 00:02:58,290 Well that's to format the spaces at 83 00:02:58,290 --> 00:03:00,800 the start of each line, and while it's 84 00:03:00,800 --> 00:03:03,490 not strictly necessary for the SQL to 85 00:03:03,490 --> 00:03:06,580 work, it does make checking a lot easier. 86 00:03:06,580 --> 00:03:08,170 So that looks pretty complicated 87 00:03:08,170 --> 00:03:10,440 compared to the comment line above it. 88 00:03:10,440 --> 00:03:11,870 What I've done is replace all the hard 89 00:03:11,870 --> 00:03:14,310 coded references to the column names in 90 00:03:14,310 --> 00:03:16,990 the original string, with the constants 91 00:03:16,990 --> 00:03:19,360 that we've defined in our TasksContract 92 00:03:19,360 --> 00:03:20,830 class. 93 00:03:20,830 --> 00:03:22,220 Now the SQL statement will be 94 00:03:22,220 --> 00:03:24,781 exactly the same as it was, and we'll see 95 00:03:24,781 --> 00:03:26,860 that when we run the app and check the 96 00:03:26,860 --> 00:03:28,050 logcat. 97 00:03:28,050 --> 00:03:29,050 So you might be wondering why 98 00:03:29,050 --> 00:03:30,790 I've gone to all the trouble of using 99 00:03:30,790 --> 00:03:33,590 this complicated code, when the simpler 100 00:03:33,590 --> 00:03:36,630 string with hard-coded values would have 101 00:03:36,630 --> 00:03:37,630 worked. 102 00:03:37,630 --> 00:03:38,630 Well there are a few reasons for 103 00:03:38,630 --> 00:03:39,630 this. 104 00:03:39,630 --> 00:03:40,920 We've created a contract that 105 00:03:40,920 --> 00:03:43,470 defines the column names, so that any 106 00:03:43,470 --> 00:03:45,560 external programs that use our Content 107 00:03:45,560 --> 00:03:47,530 Provider know which columns are 108 00:03:47,530 --> 00:03:49,930 available in the database. 109 00:03:49,930 --> 00:03:50,930 If we make a 110 00:03:50,930 --> 00:03:52,200 mistake in typing one of the column 111 00:03:52,200 --> 00:03:55,190 names, either here or in the Contract, 112 00:03:55,190 --> 00:03:58,030 then those external programs won't work. 113 00:03:58,030 --> 00:03:59,490 The names we've used in the Contract 114 00:03:59,490 --> 00:04:01,750 have to exactly match the column names 115 00:04:01,750 --> 00:04:04,300 we use when we create the table. 116 00:04:04,300 --> 00:04:05,300 Building 117 00:04:05,300 --> 00:04:06,950 up the SQL using the constants, rather 118 00:04:06,950 --> 00:04:09,380 than hard coding the names, make sure 119 00:04:09,380 --> 00:04:11,180 that they do match. 120 00:04:11,180 --> 00:04:12,180 And it's a good rule 121 00:04:12,180 --> 00:04:13,840 when programming that things should only 122 00:04:13,840 --> 00:04:15,490 be defined once. 123 00:04:15,490 --> 00:04:16,620 So having created those 124 00:04:16,620 --> 00:04:18,910 constants for our column names, we should 125 00:04:18,910 --> 00:04:20,880 use them wherever we need to refer to 126 00:04:20,880 --> 00:04:22,108 those columns. 127 00:04:22,108 --> 00:04:23,610 So that's one reason - but 128 00:04:23,610 --> 00:04:26,380 another reason is that things have a way 129 00:04:26,380 --> 00:04:27,380 of changing. 130 00:04:27,380 --> 00:04:29,400 As an example, let's say that 131 00:04:29,400 --> 00:04:31,050 we're creating this app as part 132 00:04:31,050 --> 00:04:33,860 of a team, and another programmer is 133 00:04:33,860 --> 00:04:36,170 working on a different set of database 134 00:04:36,170 --> 00:04:37,180 tables. 135 00:04:37,180 --> 00:04:39,420 One of her tables also has a Sort 136 00:04:39,420 --> 00:04:41,770 Order column, so that when that table's 137 00:04:41,770 --> 00:04:44,640 joined to ours the column names are 138 00:04:44,640 --> 00:04:46,410 changed, and we saw that when we joined 139 00:04:46,410 --> 00:04:49,150 the artists and album tables in the 140 00:04:49,150 --> 00:04:50,600 SQL videos. 141 00:04:50,600 --> 00:04:51,760 Both tables had a name 142 00:04:51,760 --> 00:04:55,010 column, so the album's table name column 143 00:04:55,010 --> 00:04:57,560 became name column 1. 144 00:04:57,560 --> 00:04:58,560 Now it is easy to 145 00:04:58,560 --> 00:04:59,910 work around that by aliasing their 146 00:04:59,910 --> 00:05:02,730 column names using the AS keyword, but 147 00:05:02,730 --> 00:05:05,030 that means remembering to do so in every 148 00:05:05,030 --> 00:05:06,220 query. 149 00:05:06,220 --> 00:05:07,320 And there's no guarantee that 150 00:05:07,320 --> 00:05:09,440 every programmer will use the same 151 00:05:09,440 --> 00:05:12,720 aliases or apply them the same way 152 00:05:12,720 --> 00:05:13,720 around. 153 00:05:13,720 --> 00:05:15,150 So as a result the team decides 154 00:05:15,150 --> 00:05:17,600 to change the name of your SortOrder 155 00:05:17,600 --> 00:05:19,520 column, so that there's no longer any 156 00:05:19,520 --> 00:05:20,710 confusion. 157 00:05:20,710 --> 00:05:21,710 Now because you've done things 158 00:05:21,710 --> 00:05:24,260 properly you can change it in the Tasks 159 00:05:24,260 --> 00:05:26,710 Contract Columns class. 160 00:05:26,710 --> 00:05:27,710 Five minutes 161 00:05:27,710 --> 00:05:28,900 later you've released a new version to 162 00:05:28,900 --> 00:05:30,510 testing, and you're lazing in the Sun 163 00:05:30,510 --> 00:05:32,810 outside Google's headquarters. 164 00:05:32,810 --> 00:05:33,810 Now if 165 00:05:33,810 --> 00:05:35,550 you'd hard-coded the column name in all 166 00:05:35,550 --> 00:05:37,050 your SQL statements, you'd then have 167 00:05:37,050 --> 00:05:39,440 to search every file in the project to 168 00:05:39,440 --> 00:05:42,370 find all occurrences of SortOrder in 169 00:05:42,370 --> 00:05:44,690 SQL statements, and then change them. 170 00:05:44,690 --> 00:05:46,290 And Android Studio will let you do a 171 00:05:46,290 --> 00:05:49,000 search and replace across files, but you 172 00:05:49,000 --> 00:05:51,180 do need to be very careful that you 173 00:05:51,180 --> 00:05:53,730 haven't created a method or variable 174 00:05:53,730 --> 00:05:55,720 that's also called SortOrder. 175 00:05:55,720 --> 00:05:56,720 That's 176 00:05:56,720 --> 00:05:57,891 going to take a lot longer, and you may 177 00:05:57,891 --> 00:05:59,710 introduce bugs by missing one of the 178 00:05:59,710 --> 00:06:01,980 hard-coded column names or changing 179 00:06:01,980 --> 00:06:04,270 another object by mistake. 180 00:06:04,270 --> 00:06:05,270 And to provide 181 00:06:05,270 --> 00:06:07,720 some perspective here, your programs that 182 00:06:07,720 --> 00:06:09,470 you develop may be used by thousands of 183 00:06:09,470 --> 00:06:12,100 people for a decade or more, and when you 184 00:06:12,100 --> 00:06:14,450 view it like that, a bit of extra work at 185 00:06:14,450 --> 00:06:16,660 this stage is nothing compared to the 186 00:06:16,660 --> 00:06:18,440 amount of times your code will be 187 00:06:18,440 --> 00:06:19,550 executed. 188 00:06:19,550 --> 00:06:20,570 Of course I can't guarantee 189 00:06:20,570 --> 00:06:22,080 that doing things properly will hand you 190 00:06:22,080 --> 00:06:23,570 a job at Google. 191 00:06:23,570 --> 00:06:25,271 What I can guarantee, is 192 00:06:25,271 --> 00:06:27,350 if you don't do things properly, you 193 00:06:27,350 --> 00:06:29,460 won't get that job at Google. 194 00:06:29,460 --> 00:06:30,460 Okay, so 195 00:06:30,460 --> 00:06:31,470 those are a couple of reasons for this 196 00:06:31,470 --> 00:06:33,310 extra work, and if you still not 197 00:06:33,310 --> 00:06:35,250 convinced at this point in time, please 198 00:06:35,250 --> 00:06:37,360 just take my word for it for now and 199 00:06:37,360 --> 00:06:38,620 let's move on. 200 00:06:38,620 --> 00:06:39,820 Alright, so we know now 201 00:06:39,820 --> 00:06:42,020 how to execute the SQL statement. 202 00:06:42,020 --> 00:06:43,020 We 203 00:06:43,020 --> 00:06:44,180 just pass it to the database's exec 204 00:06:44,180 --> 00:06:46,300 SQL method so let's go ahead and do 205 00:06:46,300 --> 00:06:48,410 that, but we are going to get an error 206 00:06:48,410 --> 00:06:49,410 here. 207 00:06:49,410 --> 00:06:51,040 I'm going to type db.execSQL. 208 00:06:51,040 --> 00:06:53,190 I'm going to pass the sSQL string that 209 00:06:53,190 --> 00:06:54,190 we created. 210 00:06:54,190 --> 00:06:55,800 So hover over this now and 211 00:06:55,800 --> 00:06:58,060 see what the error is, and we've got an error, 212 00:06:58,060 --> 00:07:00,110 basically, because the code generator has 213 00:07:00,110 --> 00:07:03,200 created the db parameter as a nullable 214 00:07:03,200 --> 00:07:04,200 type. 215 00:07:04,200 --> 00:07:05,790 In Google they're adding the non null 216 00:07:05,790 --> 00:07:07,919 annotations to the Android framework 217 00:07:07,919 --> 00:07:09,760 code, but they haven't got around to the 218 00:07:09,760 --> 00:07:12,199 SQLiteOpenHelper class as of the 219 00:07:12,199 --> 00:07:14,080 time I'm recording this. 220 00:07:14,080 --> 00:07:15,080 Now if you're 221 00:07:15,080 --> 00:07:16,360 not confident changing the function 222 00:07:16,360 --> 00:07:18,139 signatures when you work on your own 223 00:07:18,139 --> 00:07:20,300 apps, then you can use the Kotlin safe 224 00:07:20,300 --> 00:07:21,920 call operator here. 225 00:07:21,920 --> 00:07:22,920 So all you need to do 226 00:07:22,920 --> 00:07:25,210 is change the statement by adding a 227 00:07:25,210 --> 00:07:27,991 question mark after the db, and that 228 00:07:27,991 --> 00:07:29,480 would certainly work. 229 00:07:29,480 --> 00:07:30,480 But the source code 230 00:07:30,480 --> 00:07:32,900 doesn't give any real indication that db 231 00:07:32,900 --> 00:07:34,980 can't be null, but it just doesn't make any 232 00:07:34,980 --> 00:07:36,419 sense for this function to be called 233 00:07:36,419 --> 00:07:37,949 with an old database. 234 00:07:37,949 --> 00:07:39,300 If it is, our 235 00:07:39,300 --> 00:07:41,090 program's going to crash anyway. 236 00:07:41,090 --> 00:07:42,090 Now 237 00:07:42,090 --> 00:07:43,120 I'll just do it later when we come to 238 00:07:43,120 --> 00:07:45,610 access a database that hasn't been 239 00:07:45,610 --> 00:07:46,610 created. 240 00:07:46,610 --> 00:07:47,680 So what I'm saying here is I'm 241 00:07:47,680 --> 00:07:49,880 confident that db won't be null. 242 00:07:49,880 --> 00:07:50,880 If it 243 00:07:50,880 --> 00:07:52,590 isn't though, I prefer the app to crash 244 00:07:52,590 --> 00:07:54,960 here rather than after the user's 245 00:07:54,960 --> 00:07:57,360 entered data and tried to save it. 246 00:07:57,360 --> 00:07:58,360 So what I'm 247 00:07:58,360 --> 00:07:59,620 going to do is undo that change and 248 00:07:59,620 --> 00:08:02,669 modify the function signature instead - so 249 00:08:02,669 --> 00:08:04,480 removing your question mark. 250 00:08:04,480 --> 00:08:05,480 And then to 251 00:08:05,480 --> 00:08:06,729 change the function signature, we just 252 00:08:06,729 --> 00:08:09,360 need to come up here to the start of the 253 00:08:09,360 --> 00:08:12,040 function and remove the question mark on 254 00:08:12,040 --> 00:08:14,350 the end, and that fixes the problem. 255 00:08:14,350 --> 00:08:15,350 And 256 00:08:15,350 --> 00:08:16,850 I'm also going to take this opportunity 257 00:08:16,850 --> 00:08:19,780 to make db a non nullable type in the on 258 00:08:19,780 --> 00:08:22,490 Upgrade function as well for the same 259 00:08:22,490 --> 00:08:23,490 reason. 260 00:08:23,490 --> 00:08:24,490 And we'll come back to that on 261 00:08:24,490 --> 00:08:25,630 Upgrade function later. 262 00:08:25,630 --> 00:08:26,630 We're nearly finished 263 00:08:26,630 --> 00:08:27,900 with the AppDatabase class. 264 00:08:27,900 --> 00:08:28,900 We're going 265 00:08:28,900 --> 00:08:31,100 to make it a singleton to prevent 266 00:08:31,100 --> 00:08:32,990 multiple instances being created. 267 00:08:32,990 --> 00:08:33,990 I'm 268 00:08:33,990 --> 00:08:35,570 going to talk more about that, why we're 269 00:08:35,570 --> 00:08:37,869 doing that and how to do it, in the next 270 00:08:37,869 --> 00:08:38,208 video.