00:08
we already have a create users migration set up and ready to go. This came with our project because we configured authentication as we were setting it up, and we also have a user model here as well.
00:17
So we don't need to create a user migration or model as we already have one. We can just alter this to match our needs. Really, all we're going to do is switch full name from being nullable to not nullable.
00:27
We're going to require that in order for them to register. So we can give that a save, jump into our model where we can just get rid of the or null on that there as well. So that's the only change we need to make there.
00:37
Let's dive back into our terminal now. The next table that we're going to want to make is our roles table. Remember back to our Let's Learn AdonisJS 6 series, we mentioned that migrations need to run in a particular order
00:47
so that our foreign key constraints can get created successfully. So let's go ahead and do node ace make, and we'll have this make a model called role,
00:55
and we'll do hyphen m so that it also creates a migration for this model. If we run this, we should now get both a model and a migration created, and we can see that reflected here in our project structure.
01:05
Cool. So we're just going to run through that flow for all of our different tables. Where we're going to need a model, we'll use make model, and where we're going to specifically just need a migration, we'll use make migration. Next up is our organization.
01:15
So we'll do node ace make model organization hyphen m, and we'll also do c to create a controller for that as well. Run that, we should get three things created.
01:24
There we go. Now we need a migration for our organization users. So we'll do node ace make migration organization users. Run that, and there we go.
01:33
So now we have one for our create organization users table. Next, let's do our access levels, difficulties, and statuses. So node ace make, we'll want models for those.
01:42
So we'll do model access levels hyphen mc to create a migration and controller. Let's hit up one,
01:49
and then let's just change the name from access levels to difficulties. Run that, and do that one more time, switching difficulties with statuses.
01:58
Let's go ahead and clear that out now so that we have a clean slate. Hit up twice to go back to that command, and we're going to want to continue on with our courses.
02:06
Hit up again, switch courses with our modules. Up again, switching modules with lessons.
02:14
Up again, switching lessons with email histories. We don't need a controller for that one. That one's going to be bound to our settings controller.
02:22
So we'll just create a migration for that one in addition to our model. Hit up one more time, switching email histories with our organization invites. Again, we don't need a controller specific for
02:32
that one because it's going to be inside of our settings. Then we have our password reset tokens, which will bind in with our auth, so we won't need a controller for that one there either.
02:40
So password reset tokens, creating a model migration for that. Cool. So we have all of our migrations and models,
02:49
and a good majority of our controllers stubbed out. We can go ahead and clear that. Let's jump back into our text editor now, and start defining these migrations that we have. So our users migration is good to go.
02:59
Let's take care of our roles next. All that we need to do here is add in the name column of type string. So column name there, give it a length of 30, and let's make that not nullable.
03:08
Cool. So next up, our organization, that's going to be pretty much the exact same just with a different string length. So we need a table string name, and we'll give this one a length of 100.
03:18
Again, making that not nullable. Next up, we need to bind those three together via our organization users.
03:25
So let's do table integer organization underscore ID. The increments column is unsigned, meaning that it cannot be negative.
03:33
So we want to make our integer here for the relationship unsigned as well, so that we can create the foreign key successfully. And we can make that foreign key with references,
03:41
the table name of organizations dot ID. On delete, we're going to want this relationship to cascade delete. So we'll add in cascade there.
03:51
And then lastly, we want this to be not nullable because an organization user is going to require an organization ID. The reds quickly there is just because of line length. So I'm going to ignore that momentarily,
04:01
and let's add in our user ID next. So table integer user ID. Again, that's going to have a foreign key on it. So we'll want to make that unsigned,
04:09
references users dot ID. We'll want this to cascade as well. So we'll do on delete cascade, and that too is not nullable.
04:18
And then table integer, we have our role ID. This one too is going to have a foreign key. So we'll make that unsigned as well,
04:26
references our roles ID. Now we should never be deleting our roles. And if we do, we're going to want to gracefully handle that rather than deleting all of our users from all of our organizations.
04:35
So we'll leave the on delete cascade off of our role ID here so that we get errors should anybody try to delete a role ID, which should not be happening.
04:44
So that is our organization users migration. Next we have our access levels. And actually let's jump back into our organizations migration real quick. So we're going to want this particular column definition
04:54
on a number of our tables. So let's go and just give that a copy as a lot of our tables end up relating back to an organization and we're going to want it to cascade. So we'll give that a copy,
05:04
jump back into our access levels here, because this is one of those tables. Paste that in there, just like so. All right, so next we want a name for this. So table string, column name of name.
05:14
We'll give that a length of 50 and make it not nullable. Then we're going to want to be able to set a color code for our different access levels. So we'll do table string
05:22
and give this a column name of color with a length of 10. Again, that's going to be not nullable. And then our access levels, difficulties, and statuses are all going to be ordered.
05:31
So we'll want a table integer called order for those. We're going to make it unsigned just to take negative numbers out of the equation. Not nullable. And let's go ahead and default it to zero
05:41
if we never pass one in. That order is how we're going to determine our default form values for those. And that also allows the user to customize the ordering that we'll show them in
05:51
inside of the select options as well. And then we'll also have a table Boolean with an is default flag found to it and default that to false.
06:01
Now this exact structure is actually what we use for our difficulties and statuses as well. So let's just give this whole thing here a copy and a save and jump into our difficulties and paste it in.
06:10
Give that a save, jump into our statuses and paste it in. These three things share a lot of commonalities and how they behave with our organization. The tables, models and the underlying actions
06:20
that we'll have inside of our application will reflect that as well. Cool. So let's go ahead and give our organization column there another copy and let's move on to now our courses.
06:30
Give that organization column a paste and this will also relate back to our access levels, difficulties and statuses.
06:36
So we'll want a table integer for our access level ID. Make that unsigned that references our access levels table
06:48
and the ID column. And then on delete, we will cascade and we'll make that not nullable. Do that again for our difficulties.
06:56
So integer difficulty underscore ID unsigned references our difficulties.id column on delete,
07:06
cascade and make that not nullable. And then table integer status ID unsigned
07:13
references statuses.id on delete, cascade and not nullable.
07:21
Okay, relationships for our courses is done. Now we're ready to move on to the course specific columns of table string name 150 for the length, not nullable.
07:31
Table will have a text of note on it. Actually make that plural notes. I don't think we actually make use of that column at all but we'll leave it in there nonetheless.
07:40
And then table integer order, not nullable and default to zero. And there we go, let's give that a save and that's gonna format everything.
07:50
And let's give all of these relationship columns here a copy now and move on to our modules. Let's paste all of those columns and we don't need difficulty on this one.
08:00
We don't need access level either but we'll change access level. We now reference our course ID and that table reference will change to our courses table.
08:10
And for the module specific columns we'll have table string name 100 length and not nullable
08:17
table text notes and a table integer with our order not nullable and default that to zero.
08:27
Let's also make it unsigned so that we don't have to worry about negatives. And let's go back to our courses and make that one unsigned there as well. So signed, there we go. Okay, while we're in here
08:37
let's copy all of our courses relationships again and jump down into our lessons now and paste all of these relationships in. For our lessons we want it to have an organization
08:47
and access level and a status. We're not gonna have lesson specific difficulties but we can swap this to now reference our module ID and our modules table.
08:57
Then for our lesson specific columns we're gonna have a table string and a name with a length of 150 not nullable
09:05
a table text notes and a table integer order not nullable defaulted to zero.
09:14
And again, let's also make that one unsigned as well. And then lastly, let's add this into our timestamp section a table timestamp with our publish at field
09:24
so that we can specify a desired publish at date for our lessons. All right, so there's the core functionality tables out of the way. Let's move on to our add-on functionality.
09:33
So our email history is next. This relates back to our user. So we'll do table integer user ID unsigned
09:40
and that references our user's ID not nullable and on delete, we'll go ahead and cascade. Then we wanna keep track of the email change.
09:49
So we'll have a string column for our email old give this a length of 254 characters to match our email column on our users table and then not nullable.
09:59
Then we're gonna have a table string with our email new again, giving that a length of 254 and making it not nullable. So when they change their email
10:09
we're gonna put the old email that they're changing from in the email old and the new email that they're changing to in the email new, cool. Then we have our organization invites table.
10:19
We're gonna have a decent bit in this one. So we're gonna have a table integer that needs to relate back to our organizations via our organization ID, make that unsigned
10:29
references our organizations.id. On delete, we will go ahead and cascade and let's make that not nullable.
10:38
And then we'll have another table integer pointing to our users, but we'll call this column are invited by user ID.
10:47
This will be the ID of the user sending this invitation. So we'll make that unsigned and that's gonna need to reference our users ID
10:55
and then on delete, we can again cascade and make this not nullable. Should this invitation be canceled we'll also collect that information
11:03
via our integer column called canceled by user ID. And for the most part, this is gonna mimic our invited by user ID. So we'll just paste in all of those additionals.
11:13
The only change here is on delete rather than deleting this row, we wanna just set this value to null. So we'll switch that to set null and get rid of the non-nullable there.
11:22
Then we want the email of the person we are inviting. So table string email, make that 254 characters to match all of our other email fields
11:31
and then make that non-nullable as well so that it's required. And then we want their designated role. So table integer role ID, we'll make this unsigned
11:40
references our roles ID table and column. And then on delete, we'll go ahead and just cascade and set this to non-nullable.
11:49
All right, and we'll add two additional timestamps to this as well. So we'll have table timestamp accepted at
11:56
and table timestamp are canceled at. Let's give that a save. And now we're done with that one, leaving us with just our password reset tokens.
12:05
For this one, we wanna relate this back to our user. So table integer user underscore ID,
12:11
make that unsigned references our users ID. On delete, we'll go ahead and cascade and make that non-nullable.
12:20
Then we need our actual token values. So we'll have table string and we'll call this value here, make that not nullable. And let's add in an additional timestamp to this one,
12:30
table timestamp for expires at. And we'll make that non-nullable there as well. Whoops, apologies about that. I actually got a little cascade happy here.
12:39
We are not going to want our status IDs, difficulties or access levels to cascade on delete for our lessons, modules and courses. Because if we were to go through
12:48
and delete a status difficulty or access level, we don't want that lesson course or module to delete altogether. Instead, we want another valid value to go in instead.
12:57
So we wanna just remove the on deletes here. We wanna leave it for our module ID there on our lessons, but delete it from the access level there, just like so.
13:07
Go back to our modules and let's do the same thing. So delete our cascade on our status, leave it on our course so that our module deletes whenever we delete a course
13:15
and leave it on our organization there as well. Okay, and then move into our course and delete it from the access level, difficulty and status there as well.
13:25
Okay, so there's all of our migrations. Now we need to transfer those into our models so that we're ready to go. Before we do that though, let's make sure that all of our migrations run successfully
13:34
by jumping back into our terminal and let's run node ace migration run. Okay, awesome. Everything ran successfully there. So now we have all of our migrations created
13:44
inside of our actual database so that they are ready to go.
