Alright so let's talk about the
database for this application. So the
structure of the database is fairly
simple, but it does use joined
tables and that makes it a good example
for learning how to implement a Content
Provider. Now once you've learned how to
perform the basic insert, update, delete
and query operations on our joined
tables, if you need to work with a single
table, then that should be easy. So let's
have a look at the structure of the
database that we'll be using. Now you may
want to refer back to the diagram I'm
about to show, so I'll add it to the
resources for this video. Alright, so
we've got two tables here; firstly Tasks -
that's going to hold the information on
the tasks, and then Timings which will
store the date/times that the tasks were
started, and the amount of time spent on
each task. I mentioned earlier that the
table must have an ID field called
_id if we want to use it with
the CursorAdapter class - which we'll be
doing - and it's a good idea to always
call your INTEGER PRIMARY KEY fields
_id when working with Android
databases. That way you'll always have
the option of using classes such as the
CursorAdapter, even if you didn't
originally intend to use it. To be
completely accurate, it's the underlying
Cursor that must have the _id
column. If your primary key column has a
different name then you can alias the
column name in the query or view, and we
saw how to do that using the AS keyword,
in the videos on SQL earlier in the
previous section. But why complicate
things? If you know you're developing a
database for Android, then include an
_id column as your primary key.
Alright so the Tasks table just stores
the task Name and the Description, as
well as a column called SortOrder that
can be used to list the tasks in a
different order, instead of just
alphabetically by name. Now you may want
to add extra fields once the app's
working - it may be useful to be able to
group tasks together, for example,
by adding a Category field. For this
example though, we'll stick with just
those three columns and the id.
Now the Timings table stores a number of
StartTimes and Durations for each task,
and it uses the TaskId column as a link
to the Tasks table. Now you may be
wondering why the StartTime column is
an INTEGER. Dealing with dates and times
can be very complicated when you
start to consider the various ways that
dates can be represented, and different
time zones around the world and so on. So
the easiest way to store date and time
information in a database is to store
the number of milliseconds since some
base date. Now the base date is known
as the epoch, and in the Java classes the
epoch is midnight on the 1st of January
1970. Now we're not really interested in
millisecond accuracy, so our code will
divide the millisecond values by 1000,
and we'll store seconds instead. The
duration will be worked out by
subtracting the StartTime from the time
that the task is stopped. Again, we'll
store seconds in the INTEGER Duration
column. So that's the basic tables. We'll
store the Task information in one table
and the Timings in another, and link them
together using the Task ID column. Now
I've shown a view on the slide, and we'll
be using it when we come to produce
reports, showing how long was spent on
each task. It's just used to join the two
tables together - and I've shown the JOIN
clause on the slide. Our reports
will come from the TaskDurations view,
which converts the INTEGER seconds into
a date format so that we can display it.
And it also totals up the seconds spent
on each task which is much easier than
trying to do it in our Kotlin code. But
we'll be looking at that view later in
the section. For now, we're just
interested in the two tables and we're
going to start by getting the app to add
and edit tasks, and display them in a
list. OK so let's have a look at
creating the database and the Tasks
table that we'll be starting off with.
Now we'll be getting our app to create
the database in code, but it's a lot
easier to sort out the SQL statements
using the sqlite3 command-line
interface, rather than trying to debug
both our code and the SQL statements
at the same time. So we'll run sqlite
3 and get our sequel working
from the command-line before trying to
use it in Kotlin code. But before that
though, let's start a new Android Studio
project, so I'm going to click on Start a
new Android Studio project and call
this one Task Timer. We've got Kotlin
support clicked there. We're going to leave
API 17 set up as per normal. We're going to
leave everything else unticked as we
normally do, click on Next. We're going to
choose Basic Activity here, and that's
because we don't need or won't be using
a floating action button. So choose the
Basic Activity, or whichever template
includes a menu, if Google happens to
have changed them by the time you come
to watch this video, and click on Next.
Now we can leave these names on screen
as they are, but we are going to be using
Fragments in this app, so we want to
check on, check the Use a Fragment
check-box and then click on Finish.
Now we'll be looking at what Fragments
are and why they're useful as we build
this app, so don't worry about why we
ticked that box just yet. Alright, so I'm
just going to make a bit more space here,
and now that the project's finished
building, I'm going to click over here to
the Terminal tab and we're going to
start doing some SQL. And what I'll
actually do is I'll take the opportunity
just to move this into floating mode, so
I can just drag it up the screen a
little bit and resize it so that it
doesn't affect any subtitles, if you
happen to be watching this video with
subtitles. So there's no need for you to do
that, but this can be useful by the way
if you've got two monitors, so you can
drag the floating windows into your
other display. Alright, so let's start
using sqlite3, and we're
going to create a new database - so
sqlite3, and we're going to
call the database TaskTimer.db.
Now this does rely on the path having
been set properly, so make sure you've
followed through the earlier videos in
this course for your operating system, to
set the path. Alright, so now that we're
in sqlite3 we can enter the SQL
commands to create our Tasks table. So
I'm going to type CREATE TABLE Tasks,
and parentheses _id space and it's
going to be INTEGER space PRIMARY space
KEY space NOT space NULL comma space.
And it's going to be Name TEXT space NOT NULL, NOT space
NULL comma and Description space TEXT comma
and space SortOrder space INTEGER. Then
right parentheses and semicolon. That's
our Tasks table. Now sqlite doesn't
confirm the commands have worked, so as
long as you don't get an error, then
everything's fine. And you can check that
it's worked using the .schema command.
That shows the commands used to create
any objects in the database, and you can
see our Tasks table, so that's worked
fine. Now I'm going to insert a couple of
rows, just to make sure that everything
is working as it should. I might be
interested in tracking how much time I
spent on this section, for example, as
well as my various Udemy courses, so I'm
going to create some tasks for those. So
INSERT INTO Tasks space parentheses,
and it's going to be Names comma space Description
right parentheses space VALUES
parentheses, and in single quotes - double
quotes should work okay - we're going to
type TaskTimer in single quotes comma
space. Then single quote again TaskTimer
app creation closing off single quote
right parenthesis and semicolon. Now we're
going to use a slightly different
statement for the second and third tasks, and
I'll talk about that shortly. So we'll do
INSERT INTO, for this next line, Tasks and
in space parentheses, it's going to be Name comma
Description comma and SortOrder
space VALUES parentheses. We're going to
go with Android Java comma space single
quotes Android Java course. It's a Java
version of my course. Then I'm going to put comma
2 then right parentheses and semicolon.
So for the first entry, the TaskTimer
entry, we didn't specify the field Sort
Order, so therefore we got a null Sort
Order. But in the case with the second
entry, I've specified SortOrder as the
field to insert into and a value, in this
case 2, so that we can contrast and see
the difference. Now we're not going to
insist that users enter a SortOrder for
this application, so it's important to
test that the database copes if that
value's left out, and the actual values
are arbitrary. Larger values will sort
down the list, so it's really up to the
user how they organize the tasks, but
let's do the last entry. I'm going to do
an up arrow here, and we'll get the
information back again. I can just
overwrite that. I'm going to specify
zero for the SortOrder, and for
Description I'm going to specify Android
Kotlin course, which of course is this one,
and we'll put Android Kotlin for the
first part for the name. Alright so we've
got three entries there now. Now by
default, by the way, ordering in a
database is undefined unless there's a
primary key, and because we have defined
a primary key, the rows should be
returned in the order that we've added
them. So if we just type SELECT space
* space FROM TASKS, FROM space
Tasks. You can see we've got the three
entries showing in there, in primary key
order. Now we can't use an ORDER BY
clause to sort them differently, so we
could do something like SELECT space
* space FROM space Tasks - and I'm not
going to read ahead and type space
anymore, I think you'll know when you,
where you're meant to enter a space now -
ORDER BY Name semicolon. You can see
that's now ordered by name. And the third
example, just do SELECT * from Tasks
ORDER BY SortOrder semicolon, and we can
see it ordered in
this case by SortOrder. Alright so by
including the SortOrder column, we're
providing a way for the user to show the
tasks in any order they want. The idea is
that they'll probably want the tasks
they're performing most often, to be near
the top of the list. But our database
seems to be okay, and it can cope with
null values for the SortOrder. So now we
can move on with writing the Kotlin code
to create the database in this Tasks
table. So let's quit the sqlite
command-line interface. I'm going to type dot exit,
then I'm going to type exit to close the
terminal session, and we'll finish the
video now. And in the next one we're
ready to start writing some database
code for our app, and we'll start with
the Contract class. So I'll see you in
the next video.