1 00:00:03,740 --> 00:00:10,460 G'day everyone, welcome back. Okay, we need to modify our AppDatabase class, so that 2 00:00:10,460 --> 00:00:17,280 it creates a Timing table as well as a Task table. The code's just more of the same - 3 00:00:17,280 --> 00:00:43,400 - a sequel create table statement, in the onCreate method. 4 00:00:43,400 --> 00:00:48,380 That just uses the sequel create table statement to create the Timings table, 5 00:00:48,380 --> 00:00:53,500 using the column names that we defined in the TimingsContract class. We can do 6 00:00:53,500 --> 00:00:57,760 a bit more than that. As I've mentioned, the sequel language is extremely 7 00:00:57,760 --> 00:01:02,559 powerful. If you're going to work with databases a lot, then it's well worth 8 00:01:02,559 --> 00:01:07,600 investigating sequel further. It's often a lot easier to use sequel, rather than 9 00:01:07,600 --> 00:01:11,680 trying to do the same thing in Kotlin code, and this next step is a great 10 00:01:11,680 --> 00:01:16,740 example of that. One problem we're going to get if we leave things like this, 11 00:01:16,740 --> 00:01:22,100 is what will happen when we delete a task. If the task has any timing records, 12 00:01:22,100 --> 00:01:26,740 they'll get left in the database. That, obviously, takes up space, but more 13 00:01:26,740 --> 00:01:32,200 importantly, notice that we haven't used the auto increment keyword in our 14 00:01:32,200 --> 00:01:37,780 underscore ID primary key. I'll quickly refresh what that means. Let's have a 15 00:01:37,780 --> 00:01:46,560 look at https://sqlite.org/autoinc.html 16 00:01:46,560 --> 00:01:53,100 Paragraph 1 in the summary, is why we haven't used auto increment. It imposes extra CPU, 17 00:01:53,110 --> 00:01:59,680 memory, disk space and disk i/o overhead. As it should be avoided unless strictly 18 00:01:59,680 --> 00:02:05,470 necessary, and it isn't usually needed, we're not using it. Paragraph 4 explains 19 00:02:05,470 --> 00:02:11,200 a consequence of this, as it relates to our app. Using auto-increment prevents 20 00:02:11,200 --> 00:02:18,849 the IDs from deleted rows, being reused. We're not using auto-increment. When we 21 00:02:18,849 --> 00:02:24,400 delete a task, at some point, it's ID will be given to a new task. That means any 22 00:02:24,400 --> 00:02:29,049 old timing records will become associated with the new task, which will 23 00:02:29,049 --> 00:02:33,250 really mess things up. We could fix that by running another delete query to 24 00:02:33,250 --> 00:02:38,439 remove the old timing records, after we delete the task. That's fine - it wouldn't 25 00:02:38,439 --> 00:02:42,250 be a lot of code - but we'd have to remember to do it whenever we delete a 26 00:02:42,250 --> 00:02:47,650 task. That may not seem like a big deal, but if we allow other apps to access our 27 00:02:47,650 --> 00:02:53,049 data via the content provider, then the programmers of those apps will also have 28 00:02:53,049 --> 00:02:57,879 to remember to clear out the timings. when a task is deleted. At some point, 29 00:02:57,879 --> 00:03:03,549 it's all going to go wrong. So, the best place for all this, is inside the 30 00:03:03,549 --> 00:03:09,579 database itself. Unfortunately, sequel databases provide a way to do this, using 31 00:03:09,579 --> 00:03:14,530 something called a trigger. I'm not going to go into a lot of detail about sequel 32 00:03:14,530 --> 00:03:18,790 triggers - you can find plenty of information on the internet, if you need 33 00:03:18,790 --> 00:03:23,620 to do a lot of database work. Basically, a trigger is code that you tell the 34 00:03:23,620 --> 00:03:28,930 database to execute, whenever some event happens. In our case, that event is the 35 00:03:28,930 --> 00:03:32,459 deletion of a task record. 36 00:03:49,500 --> 00:03:55,290 With that trigger created, the database will execute the delete statement, after 37 00:03:55,290 --> 00:04:00,820 any row is deleted from the Tasks table. sqlite takes care of that for us. 38 00:04:00,820 --> 00:04:03,810 Okay, that code will create the Timings table, 39 00:04:03,810 --> 00:04:08,130 with the trigger, whenever the app runs for the first time. But what about our 40 00:04:08,130 --> 00:04:13,380 existing devices that already have the app installed? This onCreate function's 41 00:04:13,380 --> 00:04:17,160 only called if the database doesn't already exist. 42 00:04:17,160 --> 00:04:21,870 So our existing database won't get the table. That's where the onUpgrade 43 00:04:21,870 --> 00:04:27,120 function comes in. At the start of the AppDatabase class, we added a database 44 00:04:27,120 --> 00:04:31,610 version field, currently set to 1. 45 00:04:32,930 --> 00:04:37,560 Whenever we make a change to the database structure in here, we increase 46 00:04:37,560 --> 00:04:46,080 that number. I'll change it to 2. Now, in the onUpgrade function, we can write the 47 00:04:46,080 --> 00:04:51,540 code to handle the change, from version 1 of the database, to version 2. In this 48 00:04:51,540 --> 00:04:56,220 case, that's quite easy. We just execute the same code that's just been added to 49 00:04:56,220 --> 00:05:00,900 the onCreate function. It'll make sense to break that out into its own function, 50 00:05:00,900 --> 00:05:18,080 so that we can reuse the code instead of duplicating it. 51 00:05:18,080 --> 00:05:24,300 We can now paste that code into a new function. I'll call it addTimingsTable and place 52 00:05:24,300 --> 00:05:28,400 it after onUpgrade. 53 00:05:45,500 --> 00:05:50,520 Our old version was 1, so in the onUpgrade function, we add 54 00:05:50,520 --> 00:06:09,180 a call to addTimingsTable, to the case when the old version is 1, 55 00:06:09,180 --> 00:06:14,979 and that's all we have to do. When the app runs and finds version 1 of the database, this 56 00:06:14,980 --> 00:06:20,220 onUpgrade function will be called and will perform the upgrade for us. 57 00:06:20,220 --> 00:06:24,660 It's all done by magic. Well, obviously not, but I'll leave the explanation of how 58 00:06:24,669 --> 00:06:28,509 Android knows that the version needs to be updated. We'll look at that once 59 00:06:28,509 --> 00:06:36,120 we've seen it working. Run the app to upgrade the database on your device. 60 00:06:36,120 --> 00:06:41,760 If you're using a physical device, or one of the Google Play emulators, you can't use 61 00:06:41,770 --> 00:06:46,569 adb to navigate to the phone's database directory. That's not a problem - 62 00:06:46,569 --> 00:06:51,279 use the Device File Explorer that we looked at in the previous video, to copy the 63 00:06:51,279 --> 00:06:55,479 database files to your local hard disk. You can then change the appropriate 64 00:06:55,479 --> 00:07:00,819 directory, and use sqlite 3 to examine the database. It's quicker to use 65 00:07:00,819 --> 00:07:05,259 a terminal and open the database directly on the device, but that will 66 00:07:05,259 --> 00:07:10,779 only work on some of the emulators. I've done that in an earlier video, so this 67 00:07:10,780 --> 00:07:14,340 time I'll use the Device File Explorer instead. 68 00:07:14,340 --> 00:07:21,100 Browse to data /data/learnprogramming.academy.tasktimer, 69 00:07:21,100 --> 00:07:24,500 and then expand the directory. 70 00:07:30,660 --> 00:07:37,900 Next, right-click on the databases directory and choose Save As. 71 00:07:37,900 --> 00:07:44,920 I've created a TaskTimer db - version 2 directory, and I'll save the files in there. 72 00:07:51,440 --> 00:07:55,780 From the terminal, or a command prompt in Windows, change to the directory 73 00:07:55,780 --> 00:08:01,860 where you save the files, then run sqlite 3. 74 00:08:32,490 --> 00:08:37,740 When I use the dot schema command, there's our new timings table and the 75 00:08:37,740 --> 00:08:43,080 trigger that we added. So that's working fine and the database has been upgraded. 76 00:08:43,080 --> 00:08:48,120 So how did Android Studio know that it had to call the onUpgrade function? 77 00:08:48,120 --> 00:08:52,160 sqlite has a way to access information that's not stored in 78 00:08:52,160 --> 00:08:57,260 database tables, using the pragma command, 79 00:09:03,820 --> 00:09:10,080 and it returns 2. Android uses that user underscore version value to keep track of 80 00:09:10,080 --> 00:09:15,180 the current session of the database, and compares it to the version in our code. 81 00:09:15,180 --> 00:09:19,350 Pragma statements, by the way, aren't standard sequel. They're extensions 82 00:09:19,350 --> 00:09:23,700 that are specific to sqlite. I think of them as a way of storing some 83 00:09:23,700 --> 00:09:28,140 additional data about the database - a version number, in this case. 84 00:09:28,140 --> 00:09:31,960 We can set them ourselves, if we want. 85 00:09:47,960 --> 00:09:53,040 And now the version is 99. That's going to mess up any future upgrade, so we'll 86 00:09:53,040 --> 00:09:56,620 set it back to 2. 87 00:10:05,860 --> 00:10:09,600 The reason I've shown you that, is because sometimes you might 88 00:10:09,600 --> 00:10:13,959 want to ship a populated database with your app, or get the app per downloaded 89 00:10:13,959 --> 00:10:17,979 over the Internet. If you do that, it's important to make 90 00:10:17,979 --> 00:10:22,359 sure that the user underscore version pragma matches the version in your 91 00:10:22,359 --> 00:10:27,729 database class. So make sure you set the version correctly. That's important if 92 00:10:27,729 --> 00:10:32,649 you provide a complete database, rather than relying on the onCreate function to 93 00:10:32,649 --> 00:10:38,410 create it for you. So, that's the onUpgrade function. It can start to get 94 00:10:38,410 --> 00:10:43,239 quite complex, as you get more and more versions of your apps database, but it's 95 00:10:43,239 --> 00:10:47,259 really just more of the same. When you release a version of the app using a 96 00:10:47,259 --> 00:10:51,669 version 3 database, you'll need to include the code to upgrade from version 97 00:10:51,669 --> 00:10:57,399 1 to version 3, and also from version 2 version 3. Putting the upgrade code 98 00:10:57,399 --> 00:11:01,479 in the separate functions will make that a lot easier, as we did with the App 99 00:11:01,480 --> 00:11:07,680 Timings Table function, in the App Database class. While I'm in AppDatabase, 100 00:11:07,680 --> 00:11:11,979 if I hadn't spotted and fixed that incorrect version number, we'd have to 101 00:11:11,979 --> 00:11:17,769 use version 3 in our own upgrade function. That new database version would 102 00:11:17,769 --> 00:11:23,409 have to become version 4. Everything would still work, so it wasn't a serious 103 00:11:23,409 --> 00:11:29,799 problem, but it's tidier to start with version 1. I'll stop the video here, 104 00:11:29,799 --> 00:11:34,799 but first we better switch back, and quit sqlite and the shell. 105 00:11:36,529 --> 00:11:41,359 We're now ready to start saving the Timing data, and we'll do that in the 106 00:11:41,359 --> 00:11:45,339 next video. I'll see you there.