1

00:00:00,800 --> 00:00:06,200

So how did you make out with the exercise and did you notice that we didn't add any indices to the reservations



2

00:00:06,200 --> 00:00:06,560

table?



3

00:00:06,890 --> 00:00:07,650

Hopefully you did.



4

00:00:07,670 --> 00:00:08,870

If not, don't worry.



5

00:00:08,870 --> 00:00:13,820

I have the solution to the exercise and we'll add a couple of indices to the reservations table as well.



6

00:00:14,810 --> 00:00:21,410

So let's go back to our idea and open a terminal window and clear the screen and create a migration.



7

00:00:21,410 --> 00:00:30,410

So to generate fees, add K and all the indices at the same time indices.



8

00:00:32,530 --> 00:00:40,390

To reservation table that creates the migration's let's open them up, so under Migration's it will



9

00:00:40,390 --> 00:00:41,400

be the bottom two.



10

00:00:41,560 --> 00:00:46,000

And I want to first of all, add a foreign key and I want to add indices.



11

00:00:46,000 --> 00:00:53,500

So let's copy one of our foreign key creations here just to save some typing and paste it in here and



12

00:00:53,500 --> 00:00:59,530

let's copy and indices up so here and paste it in here.



13

00:00:59,800 --> 00:01:02,790

And we'll just put one for now until we decide how many we want to add.



14

00:01:03,160 --> 00:01:09,940

First of all, we're working on the reservations table and we want to add a foreign key from if I go



15

00:01:09,940 --> 00:01:16,390

back to my diagram from reservation ID to reservations.



16

00:01:16,660 --> 00:01:18,290

So it's actually in the room restrictions table.



17

00:01:18,430 --> 00:01:19,630

Let's go back there and fix that.



18

00:01:21,100 --> 00:01:31,690

Room restrictions and its reservation is on the reservations table and its ID, so that will be that.



19

00:01:32,140 --> 00:01:35,050

Now, what indices do we want on the reservations table?



20

00:01:35,080 --> 00:01:36,370

Because we have them here already.



21

00:01:36,400 --> 00:01:37,700

We don't have any on this one.



22

00:01:38,200 --> 00:01:40,480

Well, think about how you might be looking things up.



23

00:01:40,480 --> 00:01:45,150

You're probably going to look for a reservation by last name or email.



24

00:01:45,160 --> 00:01:50,020

So let's go with that on reservations.



25

00:01:51,130 --> 00:01:56,150

We want to add an index to email and we want to add an index on last name.



26

00:01:57,070 --> 00:02:03,730

So what we're doing in one migration is adding a foreign key to the room restrictions table from the



27

00:02:03,730 --> 00:02:08,200

column reservation ID to the reservations table column ID.



28

00:02:08,560 --> 00:02:15,580

And then on the reservations table, we're adding a basic default index, a very fast lookup on email



29

00:02:15,700 --> 00:02:16,690

and on the last name.



30

00:02:16,810 --> 00:02:18,320

So let's run that and see if we got it right.



31

00:02:19,240 --> 00:02:21,660

So to migrate and we did.



32

00:02:22,270 --> 00:02:24,580

But of course we also need the down.



33

00:02:25,480 --> 00:02:30,800

So let's open our down one and let's see how we have a down for foreign keys.



34

00:02:30,850 --> 00:02:37,450

We'll just copy one, we'll copy this one and open up our down again and paste it in.



35

00:02:38,080 --> 00:02:46,180

And we're adding two indices both on the reservations table and it will be for reservations.



36

00:02:46,180 --> 00:02:48,880

We can look the name up, but we figured out the syntax by now.



37

00:02:48,880 --> 00:02:56,920

I'm sure reservations and the link is the foreign key now, the foreign keys on the room restriction



38

00:02:56,920 --> 00:02:57,250

table.



39

00:02:59,080 --> 00:03:04,690

Room restrictions and we're going from room restrictions.



40

00:03:10,000 --> 00:03:18,640

And the column is reservation it, so it'll be reservation idy, and that will drop that and we only



41

00:03:18,640 --> 00:03:19,580

have one for Inchy.



42

00:03:20,260 --> 00:03:25,740

So now let's go to the down for creating indices and there's one we can copy.



43

00:03:25,990 --> 00:03:27,100

So let's copy this one.



44

00:03:29,320 --> 00:03:33,010

And the indices, there are two of them, both on the reservations table.



45

00:03:36,980 --> 00:03:39,200

And one was for reservations.



46

00:03:42,510 --> 00:03:43,630

And it's not remedy.



47

00:03:43,890 --> 00:03:50,820

It was email and we'll just duplicate that and get rid of this one and the other one was for last name



48

00:03:52,560 --> 00:03:53,530

and that's all there is to it.



49

00:03:54,030 --> 00:03:59,100

So now we have our up and down migration's and we've added a couple of indices.



50

00:03:59,490 --> 00:04:01,480

So we're pretty much good to go at this point.



51

00:04:01,500 --> 00:04:07,270

We've actually created our database structure and everything seems to work as expected.



52

00:04:07,290 --> 00:04:08,780

So here's a cool thing.



53

00:04:08,940 --> 00:04:09,650

Watch this.



54

00:04:10,260 --> 00:04:13,740

I'm going to open my terminal window and say SOTA reset.



55

00:04:13,820 --> 00:04:17,790

Now, it's not going to work the first try and I'm going to make it not work on purpose just because



56

00:04:17,790 --> 00:04:19,200

you will do this at some point.



57

00:04:19,560 --> 00:04:26,130

If I try to reset what this is going to do is run all of the down migration's and then run all of the



58

00:04:26,130 --> 00:04:28,370

up migration's, but I can't do it right now.



59

00:04:28,410 --> 00:04:31,440

Here's why it tries.



60

00:04:31,440 --> 00:04:32,430

It's thinking about it.



61

00:04:32,880 --> 00:04:38,040

But I happen to have d'hiver running and it's connected to the database.



62

00:04:38,040 --> 00:04:40,320

And since the database is connected, we get an error.



63

00:04:40,800 --> 00:04:43,680

The database bookings is being accessed by other users.



64

00:04:43,680 --> 00:04:48,690

But if I go over here now and quit out of Dvor, as soon as that little dot disappears that I know it's



65

00:04:48,690 --> 00:04:49,020

quit.



66

00:04:50,070 --> 00:04:51,660

It's giving me the warning message.



67

00:04:51,900 --> 00:04:52,560

Yes, quit.



68

00:04:54,890 --> 00:05:01,660

It's gone now if I clear the screen and run so to reset again, it will drop everything and create everything.



69

00:05:01,670 --> 00:05:10,820

So when I go back here and look at my DB ever again, what it's done is completely recreate our database.



70

00:05:10,820 --> 00:05:13,430

And this will be very useful for us as we're going through.



71

00:05:13,460 --> 00:05:19,100

So we'll actually have in our bookings we have all of our tables.



72

00:05:20,000 --> 00:05:23,300

So under schemas, under public, there's all of our tables.



73

00:05:24,620 --> 00:05:29,780

And if I go and insert a row into one of these tables of diver, go back and run.



74

00:05:29,780 --> 00:05:35,600

So to reset, start up diver and look at it again, you'll see that that row I entered is gone and it's



75

00:05:35,600 --> 00:05:37,820

recreated our database from the ground up.



76

00:05:38,540 --> 00:05:45,020

So what we'll be able to do in the not too distant future is to actually add some migration's that populate



77

00:05:45,020 --> 00:05:51,410

our database with default data so we can be working along and populating our database, adding records,



78

00:05:51,410 --> 00:05:52,160

deleting records.



79

00:05:52,160 --> 00:05:57,140

And at some point you'll say to yourself, wow, it would be great if I could start with a clean copy



80

00:05:57,140 --> 00:05:59,690

of the database with known default data.



81

00:05:59,780 --> 00:06:02,930

And we can do that using SOTA with only one limitation.



82

00:06:02,930 --> 00:06:03,800

And it's a minor one.



83

00:06:03,800 --> 00:06:06,260

And I'll draw to your attention when we get to that point.



84

00:06:07,010 --> 00:06:11,540

But right now, we've created all of our migration's I've added all of mine to get except for the last



85

00:06:11,540 --> 00:06:13,580

two we just created, which I'm going to do right now.



86

00:06:14,330 --> 00:06:15,290

I'll add those together.



87

00:06:16,100 --> 00:06:23,060

And we have a means of maintaining our database structure and our database integrity committed right



88

00:06:23,060 --> 00:06:24,520

to our git repository.



89

00:06:24,530 --> 00:06:28,190

And this will be ever so helpful as we continue through this course.



90

00:06:29,000 --> 00:06:29,330

All right.



91

00:06:29,330 --> 00:06:30,080

Time to move on.



92

00:06:30,080 --> 00:06:35,060

And it's actually time to start working on connecting our code to a database.



93

00:06:35,210 --> 00:06:36,290

Right, Engo.