Events

00:00

I welcome back to module five. This is less than 5.8. Event events are commonly used for maintenance tasks such as cleaning logs, which would include controlling the log file size, preparing report tables or cleaning data tables.

00:15

Now there are some special notes to consider when dealing with the event. Scheduler In my sequel,

00:20

My Sequel Events, Cultural Scheduler is off by default, so we must run the command set Global event Scheduler equal on to enable it.

00:31

This is reset if my sequel or the server is restarted or reset. So if the computer is turned off and on the event schedule, er will be off. If the my Civil Services restarted, the event sculpture Scheduler will again be off to enable by to fall. You must edit the my sequel dot

00:50

CNF file and add the line event Scheduler Equal on. And this listen we're going to implement in the event that calls a procedure that accepts the parameter for the size of logs that we want to maintain. So with that said, Let's get started.

01:04

I welcome back to the virtual machine Now there is before we dive into this, there is an air that exists in our previous files for that trigger that we created. It's very subtle, but I was I did notice it and let's go ahead and fix it, and I will point out what the air is.

01:23

So I'm gonna open up the files, the migration files, and it's in the triggers user after the league gonna edit that

01:30

and you can see that we declare a variable called I D.

01:36

But we cannot do that because my sequel is mixing up our variable i d. With the I. D. That exists on the table. Now this is causing it to try and insert a zero every time it runs this method. So we're gonna change the name of this variable. And I just picked the word target,

01:56

so that's something to be aware of.

01:57

My circle can have a name collision that could go undetected if you accidentally select a variable name that shares the same name with a column on a table that you happen to be working with. The easiest way to avoid this is just to make sure that you're naming is distinct. Your variables are not the same as any of the columns on the table.

02:19

So let me go ahead and finish implementing this target target. So I d. This idea references the actual i d on the table so we can't change that.

02:29

And then right here, where we insert that value of I d. That's a reference to our variable. So we need to go ahead and change that to target

02:38

and save that. And let's go ahead and run the migration with a refresh and make sure that works and that will empty out all our data as well. So we'll have to see it again if we want to test our data.

02:53

And there we go, so that should fix that problem.

02:58

Let me go ahead and see the database again.

03:01

And let's move on to talking about our procedures. Will that runs.

03:06

So the way I wanted to implement this, I almost all put it all in just the event. But then I decided to go ahead and write a procedure for it, and then use that the event to call the procedure.

03:16

So the procedure is called control Underscore logs, and it accepts an imager that is a count of how many laws you want to keep

03:27

so and the procedure.

03:29

Okay, so this is just the syntax, you know, Drop procedure. If exists, create procedure. Declare the parameter that's coming in big. Unsigned.

03:38

Begin declare our variables, which is CNT begin assigned and declare target, which is a date time. So I set CNT equal to a select of count from blog's.

03:52

If count is greater than allowed, which is the parameter that comes in. Then I run the code in this if statement. Otherwise, we won't run this code in the f statement, and the procedure would just exit without doing anything.

04:04

So in the event that count is greater than allowed, we set allowed equal to allow minus one.

04:11

And then we set the target equal to select, created at from logs ordered by school. I d i d

04:17

limit allowed

04:19

select one.

04:21

So what we're trying to do is select the record from a table and the way we do that in my sequel as we select from Table

04:29

we ordered by the I D, which is the school I d. Comma I d. In this case. And then we limit the allowed comma one, and that's saying, select this number, but limit one record from that selection, and we set the variable target to the created at date of that record.

04:48

And then we delete from users

04:50

where created at is less than that date, so that effectively deletes the older logs and keeps the newer lochs in the table

05:00

based on the record we bring back, which is controlled by the number that we pass in,

05:08

which has a variable name of allowed.

05:11

So let's go ahead and create the migration files that will instance this into our database.

05:17

So

05:19

PHP artisan

05:23

make migration. You need to make the procedure first because we can't have the event called the procedure

05:30

if the procedure doesn't exist yet. So

05:32

control

05:34

glove

05:35

procedure

05:42

that would go into my are folder, our migration folder.

05:46

Go ahead and open up that new file.

05:48

Take a peek back in the old files. I want to pull out the DB unprepared again,

05:55

paste it into the new file,

05:57

delete what we don't need,

06:00

and I'm gonna go ahead and copy it while I have this here, paste it into the function down

06:04

and let's get the procedure

06:06

so we don't need the delimit er swapping.

06:12

Go back into our

06:15

file.

06:17

Paste it in there,

06:19

gonna save it,

06:21

and then we just need the drop procedure. If exists, Command,

06:29

bring it here

06:31

on Save that and let's run a migration refresh. So I probably shouldn't have ceded the database. We were gonna lose it right here.

06:41

Make sure that procedure gets in there

06:46

When we have an air. Let's see what happened.

06:51

Okay, this might have happened because I had the procedure in the database before I ran this. So let's go ahead and delete it.

07:00

Uh, not that the leads triggers procedures were fresh,

07:05

Then someone to delete the event

07:11

on. I'm going to delete the procedure.

07:14

You may not need to do this if you're following along, because you won't have those in your database.

07:18

So let's go ahead and run it.

07:28

OK, It worked, But let's make sure the down part of that is running,

07:31

so we'll run it again,

07:38

and it's working.

07:40

So, two instance, that procedure it would have had to drop it again first. So if you go ahead and refresh this

07:46

there, it iss Okay, Next, let's make the migration for our our event

07:53

make migration control log

07:58

sighs event.

08:01

Okay,

08:05

go ahead and open that file up.

08:13

See the way. And I won't have that in there.

08:16

Go ahead and copy this one.

08:18

I'm just gonna pace it in both areas.

08:22

The leading what we don't need.

08:28

Let's go get that event code.

08:31

I don't need the limiters switching.

08:37

So of course, we have to make the event after the procedure because we're calling that procedure from the event

08:43

I have a current size of three, which is fine,

08:48

because we have do some additional testing to test the rial size, which might be something as high as a 1,000,000 records.

08:54

All right,

08:56

and then we need the drop event. It exists.

09:01

Okay,

09:03

Save that.

09:05

That's going to run our migration again. And make sure this is working

09:09

hopes.

09:11

I expect that air because it does exist.

09:16

All right, refresh.

09:20

I like to run the refresh twice just to make sure that it's dropping. And it's rien stance eating all my objects correctly because if you for example, if you forgot Teoh put the drops statement in there, it would work one time. It would just feel the next time.

09:35

Okay, it looks like it's working. So if we go and look at our events

09:39

should be there

09:43

that completes this part of this lesson. Let's go ahead and head over to the summary.

09:48

So that brings us to the 58 events summary. And this lesson we implemented an event and a procedure in our migration fouls. So we use those two objects to add a log rotation event so that we could control the size of our log table.

10:03

We implemented a procedure the event calls the procedure with a size parameter.