Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Hi. Welcome back to Module five. This is less than 5.4 database Cedar Update and this lesson we're gonna implement the seating process in a way that supports composite keys.
00:11
So Larible does not natively support composite keys. This could be fixed with some additional PHP programming, But this is a sequel course. So we're gonna try to keep the PHP programming components pretty light locally. The way that we handle this isn't too bad.
00:27
Within the factory fouls the I d field must be a model count plus one function call. So we're gonna count the number of records in the model database and we're gonna add one to that number, and we're going to use that as the i d.
00:41
The I d field will no longer auto increments. So this is why we must use this method.
00:46
The school I d must be added is a field and we're going to select a random school much in the same way that we selected random models from other database tables
00:55
within the migration files. We must remove the big increments I D column on. We're going to replace this with another I d column. That is a big in urge er with no auto increment ing.
01:07
We're also going to add a school I d reference
01:10
within the cedar fall. We're going to use concurrency calls on all except the school creation.
01:15
And if you as you move through this, you may want to move one file at a time, being sure that test that one file as you move through all of the files. This will help you avoid being overwhelmed by a possible heirs in all the files if you try to edit them all at the same time and then test them all at the end.
01:33
With that said, let's move on to the programming environment
01:37
and see what this looks like.
01:40
All right, let's get those composite keys implemented. As you can see from the diagram, I've already implemented the composite key on many of the tables. I've done this to shorten the length of this video.
01:49
If we were going to walk through the implementation on all the tables, it would be repetitive, and it would also increase the length of lesson from 20 to 25 minutes, or maybe two or three videos.
02:01
Now, with that said, we're still going to walk through the implementation of how it was done, and I'm going to also go through them implementation of the last table. Now let's go ahead and get started. If we go to our shared folder and go to the database cedar file,
02:17
we'll see that I've switched all of the factory calls except the very first school call to concurrency function calls. And that is because when we switched to using a composite primary key lay arable doesn't actually support that.
02:32
Or at least it doesn't support it very well. We have to take the automatic increment ing away from the tables and the room, and then we got to do a count of the class and see how many are out there and then add one to that number, and that becomes the I D that we generate ourselves.
02:51
So let's take a look at what that looks like.
02:54
It sounds worse than what it actually looks like. So if you go to a factories and let's just go the 1st 1 which would be building,
03:02
go ahead and open this one,
03:05
you can see that I'm rolling an I D from building all count plus one
03:10
So this goes and accounts all the buildings, adds one. To that count, we assign that to a variable called I D. We then use that I d. As the idea on the table when we inserted. And that's also why we got to do the concurrency call. Because if we don't and we try to use a regular factory, call
03:30
some of these air going to generate the same count because they're going to be running on multiple threads or processes and they'll come back with same number and cause a failure. And that will be random, which means you could actually get it to work a couple times before it actually fails.
03:46
And, of course, we're doing the school and random order to get the I. D.
03:50
So from the building you can see how this has been implemented.
03:53
We have the school
03:55
from a random school,
03:58
and then we have the I D being generated from a building all arrow count, plus one. Then we use that as the I D.
04:06
Now let's go look at the table structure because the table structure has changed, too.
04:12
But,
04:13
you know, before we do that, let's just see how different. One of these other factories is from the building factory. They're all pretty much the same implementation. We're getting a school
04:24
and we're getting an I. D. This room is from before
04:28
and we ah, that's for a foreign key constraint. And again, we use the idea in the same way
04:33
and the school in the same way.
04:36
Something to be careful off when implementing these is to make sure that if you copy and paste that you change the calling class
04:46
to what you're using it for. For example, if I copied this from building and then forgot to change the
04:54
the building keyword to course keyword, then I would be actually counting buildings and making my i d based on that.
05:01
And then also, we have to make sure that we include
05:04
the resource is that we are using. So school, for example, has to be included in the files.
05:12
And most of these factories
05:14
or all the factories, are the same thing. It's the school I d and the I d being generated in the same way.
05:21
So
05:24
let's go ahead and take a look at a table because we had to change the table migrations as well.
05:30
So if we look at the my gracious folder and let's go back to the buildings concept and I'll open up the table for that,
05:36
we can see that I've switched the big increments to a big manager
05:43
with an i d. For the column name and false. True.
05:46
Now this is a way to control the way that the I. D column is handled so false means it will not automatically increment
05:55
and true means it's unsigned again,
05:58
so that's very similar to just putting this on the end.
06:03
So on all the tables, we switch out the big increments with the big image er
06:10
with I d false. True, in the exact same way.
06:14
And then at the bottom,
06:15
we add this call, which is Table
06:18
Aero primary
06:20
array because of the square brackets and then i D
06:25
and school I D. And again, this is same for all the tables as well.
06:29
And the big thing when you're implementing these is don't forget your semicolons.
06:33
Make sure you get the names correct
06:36
and on the factory's you'll want to make sure that you include the models that you reference,
06:43
and you definitely want toe make sure you get the verb ege right here. If your copy and pasting from other files these files of will be available for download as well.
06:51
So let's go ahead and go through this process for the last file that I have
06:56
to ah, make reflect the composite key.
07:00
So in my backup folder, I still have the users courses table.
07:04
So I copy or I cut all of my files into a backup
07:09
folder. And then I moved them out one by one, fixing them one by one and running the migration and the sea process one by one. That way, if I had a problem, I knew which one file it. Waas
07:23
and I wasn't over burning by numerous airs. If I tried to change all the files at the same time,
07:30
we'll go ahead and
07:31
control axe.
07:32
Go back one directory and paste that out
07:36
and I'm gonna go ahead and open this.
07:40
Okay, so this is the table creation. So I need to switch this big increments I d. With what I was using on the other ones,
07:47
which is this table big imager.
07:49
And again, that true, false, true
07:54
or the This is the column name.
07:57
This means that it will not auto increment.
08:00
And this means that it is unsigned.
08:03
And then I need to
08:05
copy this table primary call out.
08:09
And I just need to pace that up
08:11
the bottom of the columns
08:15
and saved that.
08:18
Now let's go and take care of the factory.
08:22
We're going to my factories
08:24
and user Course factory
08:26
is right here
08:28
when open this one
08:31
and in here, I need to
08:33
return my own I d and return a school I d.
08:37
So I'm going to need these two lines here,
08:48
and I need to change this to
08:52
user course. That's the model we are dealing with.
08:58
And I also need to make sure that I include the using statement
09:03
for school
09:05
like so
09:09
Okay, And then, of course, I need to
09:13
put those as the columns that will be inserted. So I d
09:18
We didn't have to do I d before because auto incriminating. But now it is not. So. We have to tell it what I d to use, and we need to not forget the common I did that a couple times
09:30
and then school
09:33
underscore i d
09:35
big arrow
09:37
school,
09:39
and I think we will be ready to go ahead and run that
09:43
migration and hopefully we don't get any errors. But if we do, we'll fix it.
09:48
You can see that I got an air appear above and the problem was that I forgot the column or the comma right there on the I D.
09:56
That's common. That's why it's a good idea to move one file at a time or two files at most. And then you can fix the errors incrementally instead of being overwhelmed. If all your files have errors.
10:07
So
10:09
beach be partition,
10:13
my great fresh. So we got a really and Stan, she ate all the tables.
10:18
I'll just sit up,
10:20
Are you?
10:28
And we didn't get any heirs?
10:31
And now we're gonna run the seeding process. Well, actually, let's check the database. Cedar. Make sure that set up.
10:37
I need a uncommon the user course truncate,
10:41
and I need to Owen comment the use, of course. Concurrency creation.
10:46
Now, when you go, if you go through and you implement these one by one, make sure you comment out the ones that you're not using when you run the migration.
10:54
So
10:56
when I started this at the very beginning.
10:58
I was just working on the factory of the school.
11:01
I had all of these count. All of these commented out except for this school
11:07
like like this
11:09
because if I try to use that resource when I'm not working on it,
11:13
I could generate airs. So it's
11:16
and then I would uncommon one
11:20
as I went along.
11:22
But we're on the last one, so I can uncommon all of those.
11:28
It's faster. If I hold Ault and I slide my mouse down, I could edit multiple lines at the same time. I'm going to save that now. I'm gonna run my DB Cedar class
11:39
on. Hopefully we do not get an air
11:41
and it looks like it completed successfully. Let's go in and take a look in the database.
11:46
So I'm gonna refresh this
11:52
on the diagram. Changed user courses, air there.
11:56
I d school idea
11:58
So we can see that this user
12:01
belongs to this school
12:03
and this user belongs to this school
12:07
and some to keep in mind when we have this composite key is that within the user, I d. We could have to
12:13
number 46 is at different schools, but we couldn't have two of the number 46 at the same school.
12:20
All right, now that that's done, let's go ahead and head over to the summary, and that brings us to the 5.4 database. Cedar updates summary. So what did we do in this lesson? What we reviewed the factory file changes, and those changes include a concurrency call in the Cedar File. We added a school I d reference,
12:39
and we added an i D field as the
12:41
I. D Field no longer auto increments. We also reviewed the migration file changes. We removed the big increments. We replaced it. We replaced it with an I D. Feel that does not auto increment. And we also added the composite primary key,
12:56
and that completes this lesson, and I hope to see you in the next.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor