G'day everyone, welcome back. Okay, we need to modify our AppDatabase class, so that
it creates a Timing table as well as a Task table. The code's just more of the same -
- a sequel create table statement, in the onCreate method.
That just uses the sequel create table statement to create the Timings table,
using the column names that we defined in the TimingsContract class. We can do
a bit more than that. As I've mentioned, the sequel language is extremely
powerful. If you're going to work with databases a lot, then it's well worth
investigating sequel further. It's often a lot easier to use sequel, rather than
trying to do the same thing in Kotlin code, and this next step is a great
example of that. One problem we're going to get if we leave things like this,
is what will happen when we delete a task. If the task has any timing records,
they'll get left in the database. That, obviously, takes up space, but more
importantly, notice that we haven't used the auto increment keyword in our
underscore ID primary key. I'll quickly refresh what that means. Let's have a
look at https://sqlite.org/autoinc.html
Paragraph 1 in the summary, is why we haven't used auto increment. It imposes extra CPU,
memory, disk space and disk i/o overhead. As it should be avoided unless strictly
necessary, and it isn't usually needed, we're not using it. Paragraph 4 explains
a consequence of this, as it relates to our app. Using auto-increment prevents
the IDs from deleted rows, being reused. We're not using auto-increment. When we
delete a task, at some point, it's ID will be given to a new task. That means any
old timing records will become associated with the new task, which will
really mess things up. We could fix that by running another delete query to
remove the old timing records, after we delete the task. That's fine - it wouldn't
be a lot of code - but we'd have to remember to do it whenever we delete a
task. That may not seem like a big deal, but if we allow other apps to access our
data via the content provider, then the programmers of those apps will also have
to remember to clear out the timings. when a task is deleted. At some point,
it's all going to go wrong. So, the best place for all this, is inside the
database itself. Unfortunately, sequel databases provide a way to do this, using
something called a trigger. I'm not going to go into a lot of detail about sequel
triggers - you can find plenty of information on the internet, if you need
to do a lot of database work. Basically, a trigger is code that you tell the
database to execute, whenever some event happens. In our case, that event is the
deletion of a task record.
With that trigger created, the database will execute the delete statement, after
any row is deleted from the Tasks table. sqlite takes care of that for us.
Okay, that code will create the Timings table,
with the trigger, whenever the app runs for the first time. But what about our
existing devices that already have the app installed? This onCreate function's
only called if the database doesn't already exist.
So our existing database won't get the table. That's where the onUpgrade
function comes in. At the start of the AppDatabase class, we added a database
version field, currently set to 1.
Whenever we make a change to the database structure in here, we increase
that number. I'll change it to 2. Now, in the onUpgrade function, we can write the
code to handle the change, from version 1 of the database, to version 2. In this
case, that's quite easy. We just execute the same code that's just been added to
the onCreate function. It'll make sense to break that out into its own function,
so that we can reuse the code instead of duplicating it.
We can now paste that code into a new function. I'll call it addTimingsTable and place
it after onUpgrade.
Our old version was 1, so in the onUpgrade function, we add
a call to addTimingsTable, to the case when the old version is 1,
and that's all we have to do. When the app runs and finds version 1 of the database, this
onUpgrade function will be called and will perform the upgrade for us.
It's all done by magic. Well, obviously not, but I'll leave the explanation of how
Android knows that the version needs to be updated. We'll look at that once
we've seen it working. Run the app to upgrade the database on your device.
If you're using a physical device, or one of the Google Play emulators, you can't use
adb to navigate to the phone's database directory. That's not a problem -
use the Device File Explorer that we looked at in the previous video, to copy the
database files to your local hard disk. You can then change the appropriate
directory, and use sqlite 3 to examine the database. It's quicker to use
a terminal and open the database directly on the device, but that will
only work on some of the emulators. I've done that in an earlier video, so this
time I'll use the Device File Explorer instead.
Browse to data /data/learnprogramming.academy.tasktimer,
and then expand the directory.
Next, right-click on the databases directory and choose Save As.
I've created a TaskTimer db - version 2 directory, and I'll save the files in there.
From the terminal, or a command prompt in Windows, change to the directory
where you save the files, then run sqlite 3.
When I use the dot schema command, there's our new timings table and the
trigger that we added. So that's working fine and the database has been upgraded.
So how did Android Studio know that it had to call the onUpgrade function?
sqlite has a way to access information that's not stored in
database tables, using the pragma command,
and it returns 2. Android uses that user underscore version value to keep track of
the current session of the database, and compares it to the version in our code.
Pragma statements, by the way, aren't standard sequel. They're extensions
that are specific to sqlite. I think of them as a way of storing some
additional data about the database - a version number, in this case.
We can set them ourselves, if we want.
And now the version is 99. That's going to mess up any future upgrade, so we'll
set it back to 2.
The reason I've shown you that, is because sometimes you might
want to ship a populated database with your app, or get the app per downloaded
over the Internet. If you do that, it's important to make
sure that the user underscore version pragma matches the version in your
database class. So make sure you set the version correctly. That's important if
you provide a complete database, rather than relying on the onCreate function to
create it for you. So, that's the onUpgrade function. It can start to get
quite complex, as you get more and more versions of your apps database, but it's
really just more of the same. When you release a version of the app using a
version 3 database, you'll need to include the code to upgrade from version
1 to version 3, and also from version 2 version 3. Putting the upgrade code
in the separate functions will make that a lot easier, as we did with the App
Timings Table function, in the App Database class. While I'm in AppDatabase,
if I hadn't spotted and fixed that incorrect version number, we'd have to
use version 3 in our own upgrade function. That new database version would
have to become version 4. Everything would still work, so it wasn't a serious
problem, but it's tidier to start with version 1. I'll stop the video here,
but first we better switch back, and quit sqlite and the shell.
We're now ready to start saving the Timing data, and we'll do that in the
next video. I'll see you there.