Alright, so moving on now.
We're
going to go ahead and create, or add the
necessary SQL code, to create our
database.
So we're going to build up the
necessary string, but what we're going to
do is use the constants from our Tasks
Contract class, rather than hard coding
the table and column names, as you can
see in the comment.
So I'm going to come
down here and we'll start typing.
I'll do a
log entry first, a Log.d parentheses
TAG comma onCreate colon starts.
Then
on the next line we're going to type val
sSQL in upper case, equals three double
quotes CREATE TABLE dollar sign left and
right curly braces.
Then we're going to type
TasksContract.TABLE_NAME, then have
the closing right curly brace, then a left
parentheses.
Then on the next line we're
going to put the dollar sign, left and
right curly braces TasksContract dot
Columns.ID right curly brace, and it's
going to be INTEGER space PRIMARY KEY,
with a space between them, then NOT
NULL with a space comma.
On the next line,
dollar sign left and right curly braces.
We're doing the same but for the TASK
underscore NAME right curly brace, and
it's going to be TEXT NOT NULL.
Alright, and
the next two lines what I'm going to do,
is just copy that twice.
It's going to
be easier to change it rather than typing
the whole thing in again.
So TASKS_NAME, or TASK_NAME,
we're going to make dot TASK_
DESCRIPTION and that's also a text but
it's optional, so we're going to remove
the NOT NULL.
And on the last line,
instead of TASK_NAME we're
going to go with TASK_
SORT_ORDER.
That's not a text - that's going to be an
INTEGER - and we've already established
in the previous video that that's
optional, so I'm going to remove the
NOT NULL.
Instead what I'm going to do is
then delete the
comma.
We're going to add a parentheses
there, semicolon then I'm going to add
the three double quotes to close off the
string, and on the end of the string I'm
going to do a .replaceIndent.
Then in
parentheses double quotes with a space
in between.
Now we can confirm we've got
it right by logging the string and
checking the logcat.
So let's add a logging entry on the next
line, so Log.d parentheses TAG
comma sSQL.
And obviously, logging will
help if we've made a mistake so that we
can check the SQL in our logcat with
our comment.
And you might be wondering
why I've used replaceIndent on line
35.
Well that's to format the spaces at
the start of each line, and while it's
not strictly necessary for the SQL to
work, it does make checking a lot easier.
So that looks pretty complicated
compared to the comment line above it.
What I've done is replace all the hard
coded references to the column names in
the original string, with the constants
that we've defined in our TasksContract
class.
Now the SQL statement will be
exactly the same as it was, and we'll see
that when we run the app and check the
logcat.
So you might be wondering why
I've gone to all the trouble of using
this complicated code, when the simpler
string with hard-coded values would have
worked.
Well there are a few reasons for
this.
We've created a contract that
defines the column names, so that any
external programs that use our Content
Provider know which columns are
available in the database.
If we make a
mistake in typing one of the column
names, either here or in the Contract,
then those external programs won't work.
The names we've used in the Contract
have to exactly match the column names
we use when we create the table.
Building
up the SQL using the constants, rather
than hard coding the names, make sure
that they do match.
And it's a good rule
when programming that things should only
be defined once.
So having created those
constants for our column names, we should
use them wherever we need to refer to
those columns.
So that's one reason - but
another reason is that things have a way
of changing.
As an example, let's say that
we're creating this app as part
of a team, and another programmer is
working on a different set of database
tables.
One of her tables also has a Sort
Order column, so that when that table's
joined to ours the column names are
changed, and we saw that when we joined
the artists and album tables in the
SQL videos.
Both tables had a name
column, so the album's table name column
became name column 1.
Now it is easy to
work around that by aliasing their
column names using the AS keyword, but
that means remembering to do so in every
query.
And there's no guarantee that
every programmer will use the same
aliases or apply them the same way
around.
So as a result the team decides
to change the name of your SortOrder
column, so that there's no longer any
confusion.
Now because you've done things
properly you can change it in the Tasks
Contract Columns class.
Five minutes
later you've released a new version to
testing, and you're lazing in the Sun
outside Google's headquarters.
Now if
you'd hard-coded the column name in all
your SQL statements, you'd then have
to search every file in the project to
find all occurrences of SortOrder in
SQL statements, and then change them.
And Android Studio will let you do a
search and replace across files, but you
do need to be very careful that you
haven't created a method or variable
that's also called SortOrder.
That's
going to take a lot longer, and you may
introduce bugs by missing one of the
hard-coded column names or changing
another object by mistake.
And to provide
some perspective here, your programs that
you develop may be used by thousands of
people for a decade or more, and when you
view it like that, a bit of extra work at
this stage is nothing compared to the
amount of times your code will be
executed.
Of course I can't guarantee
that doing things properly will hand you
a job at Google.
What I can guarantee, is
if you don't do things properly, you
won't get that job at Google.
Okay, so
those are a couple of reasons for this
extra work, and if you still not
convinced at this point in time, please
just take my word for it for now and
let's move on.
Alright, so we know now
how to execute the SQL statement.
We
just pass it to the database's exec
SQL method so let's go ahead and do
that, but we are going to get an error
here.
I'm going to type db.execSQL.
I'm going to pass the sSQL string that
we created.
So hover over this now and
see what the error is, and we've got an error,
basically, because the code generator has
created the db parameter as a nullable
type.
In Google they're adding the non null
annotations to the Android framework
code, but they haven't got around to the
SQLiteOpenHelper class as of the
time I'm recording this.
Now if you're
not confident changing the function
signatures when you work on your own
apps, then you can use the Kotlin safe
call operator here.
So all you need to do
is change the statement by adding a
question mark after the db, and that
would certainly work.
But the source code
doesn't give any real indication that db
can't be null, but it just doesn't make any
sense for this function to be called
with an old database.
If it is, our
program's going to crash anyway.
Now
I'll just do it later when we come to
access a database that hasn't been
created.
So what I'm saying here is I'm
confident that db won't be null.
If it
isn't though, I prefer the app to crash
here rather than after the user's
entered data and tried to save it.
So what I'm
going to do is undo that change and
modify the function signature instead - so
removing your question mark.
And then to
change the function signature, we just
need to come up here to the start of the
function and remove the question mark on
the end, and that fixes the problem.
And
I'm also going to take this opportunity
to make db a non nullable type in the on
Upgrade function as well for the same
reason.
And we'll come back to that on
Upgrade function later.
We're nearly finished
with the AppDatabase class.
We're going
to make it a singleton to prevent
multiple instances being created.
I'm
going to talk more about that, why we're
doing that and how to do it, in the next
video.