WEBVTT 00:00.290 --> 00:08.990 So as I have mentioned previously, it would be best if we have a kind of an organized way to load data 00:08.990 --> 00:11.690 into the database in the future. 00:11.690 --> 00:15.200 It might even be autogenerated data. 00:15.350 --> 00:25.280 For now, I think we can do with some predefined data that's easily loadable into the database. 00:25.310 --> 00:29.780 Let's add a new command because this will be a command. 00:31.910 --> 00:36.560 Maybe I can call that schema fixtures. 00:36.560 --> 00:43.100 So fixtures is how you typically call this data that's predefined. 00:45.230 --> 00:52.130 So what you can put here is either the data that your system, your application or website always needs. 00:52.130 --> 00:56.570 Like a predefined users like super admins. 00:56.600 --> 01:02.760 Or maybe your application is some kind of a, let's say, warehouse management. 01:02.760 --> 01:09.720 And you just know that you're gonna have some specific data inside the database at all times. 01:09.750 --> 01:12.030 It can be used for those cases. 01:12.150 --> 01:20.280 Another good use case for fixtures is to load your database during automatic testing, and then you 01:20.280 --> 01:24.090 can easily try the features of your app. 01:24.120 --> 01:33.120 Now, our need here is to be able to click through the application to sign in as specific users, and 01:33.120 --> 01:40.170 to have some posts so that we can see manually if the app works or not. 01:40.440 --> 01:47.070 Now there will be quite a lot of typing here, or would be a lot of typing. 01:47.130 --> 01:50.670 That's why I'm just gonna copy paste the data. 01:51.210 --> 01:54.690 This data will be available to you under this video. 01:54.720 --> 01:56.220 There is always the source code. 01:56.220 --> 01:58.320 You can go ahead and grab it. 01:58.920 --> 02:02.970 There is absolutely no point in typing this all. 02:03.120 --> 02:05.070 So we start with users. 02:05.070 --> 02:09.000 We're going to have three all with hashed passwords. 02:09.030 --> 02:18.390 Then there will be three posts and then there will be around four different comments. 02:19.470 --> 02:23.970 What we are going to create is just a logic for loading those. 02:23.970 --> 02:27.870 And we will configure everything and run it. 02:28.410 --> 02:31.440 Okay guys, now let me scroll to the top of the file. 02:31.470 --> 02:37.920 There is a thing missing here which is the bootstrap include or require statement. 02:39.300 --> 02:42.870 Let's grab it from this script. 02:43.440 --> 02:45.870 So it should go at the top. 02:45.930 --> 02:52.590 Then we can have the data defined and all the way here. 02:53.670 --> 02:57.750 Let's grab the reference to the database from the app. 03:01.990 --> 03:07.810 And also, let's make sure that we got a use statement for the app. 03:08.890 --> 03:10.510 Now it's up to you. 03:10.570 --> 03:17.560 Now it is up to us if we want to clear existing data first. 03:18.580 --> 03:24.760 If we do want that, then we can simply run a delete query. 03:24.790 --> 03:32.440 Now just watch out with deleting data using commands, because this can really be unsafe. 03:33.310 --> 03:38.050 If you run this script on the wrong database, you're going to wipe out your database. 03:38.080 --> 03:45.010 But generally, to delete data you will write delete from table name. 03:45.520 --> 03:47.380 So we'll start with comments. 03:47.380 --> 03:49.720 Then we'll go with the posts. 03:49.720 --> 03:53.530 And finally we can delete the users. 03:53.710 --> 03:58.180 If you don't want that you can always comment this out or just remove it. 03:58.210 --> 03:59.410 It's up to you. 03:59.440 --> 04:06.670 Just be careful with running this and just make sure you are pointing to the right database when you 04:06.670 --> 04:08.020 run this commands. 04:09.610 --> 04:18.550 Another thing we might want to do if we are deleting data is to reset the auto increments. 04:18.910 --> 04:26.590 So what this means is we would like all the new records that would be inserted to start from index one. 04:27.370 --> 04:38.110 And in SQLite you need to remove something from a special SQLite table called SQLite sequence. 04:38.140 --> 04:40.810 Let me quickly jump to the database. 04:41.860 --> 04:43.120 That's this table. 04:43.120 --> 04:47.410 That's special table that is internal for SQLite. 04:47.410 --> 04:53.410 And inside this table it refers to all the tables in the database. 04:53.470 --> 05:02.210 And it is just saying what's the next number for autoincrement integers that are used for primary keys 05:02.210 --> 05:03.140 typically. 05:03.710 --> 05:05.510 So that's optional. 05:05.510 --> 05:07.610 That's actually completely optional. 05:07.610 --> 05:15.230 But if you would like to do it then we would go with delete from SQLite sequence. 05:17.900 --> 05:21.770 We only want to delete some specific sequences. 05:21.770 --> 05:32.720 So the word query and we check if the name that's the column is in the specific set of values, which 05:32.720 --> 05:35.030 is users posts. 05:35.030 --> 05:37.070 And can you guess that. 05:37.100 --> 05:39.110 Yes that's comments. 05:40.280 --> 05:40.850 Okay. 05:40.850 --> 05:44.990 So we are deleting the data from all three tables. 05:45.320 --> 05:49.790 Then we are resetting the autoincrement sequence numbers. 05:49.790 --> 05:59.930 And then next up we should just go over every user and insert it into the database. 06:01.400 --> 06:03.230 For that we can use models. 06:03.410 --> 06:05.540 So we can go with user. 06:05.540 --> 06:08.330 Create user. 06:09.770 --> 06:18.980 And as you guessed that we are going to do it for every single database that we have. 06:21.020 --> 06:25.370 So we are going to use the post model to create posts. 06:25.400 --> 06:29.840 Just make sure you don't mix up those variable names. 06:30.740 --> 06:39.110 Finally we've got this comments array and we are going to insert some comments. 06:40.970 --> 06:48.080 Now it's up to you if you'd like to report the success after every single database table that's populated 06:48.080 --> 06:57.300 or not, we can just finish off with saying that fixtures Ships loaded successfully. 06:58.110 --> 06:58.680 Dot. 06:58.710 --> 06:59.700 Newline. 06:59.730 --> 07:00.840 Semicolon. 07:01.320 --> 07:03.000 That's everything. 07:03.420 --> 07:13.380 If anything would go wrong, then we are just getting an exception which is already handled in the command 07:13.380 --> 07:13.710 line. 07:13.710 --> 07:17.190 So we don't have to worry about this at all. 07:18.600 --> 07:23.640 The last thing that we should do is oh, one more thing. 07:24.030 --> 07:31.050 Let's also add this to Composer.json scripts so we can run that easier. 07:31.080 --> 07:35.580 This can be maybe schema fixtures. 07:36.060 --> 07:41.730 And the script name is Schema fixtures. 07:43.830 --> 07:44.970 All right. 07:45.000 --> 07:50.610 Now we should be able to run composer schema fixtures. 07:51.600 --> 07:53.370 Everything was loaded. 07:53.400 --> 07:59.070 Let's close all of those windows and jump to the database. 08:00.510 --> 08:09.210 You might have to refresh if nothing's showing up, but I can already see that there are some comments. 08:09.420 --> 08:20.340 I've got some blog posts with automatically populated dates, and we've got three users with different 08:20.340 --> 08:23.160 roles, so we'll be able to use that. 08:23.160 --> 08:24.300 Definitely. 08:24.420 --> 08:27.390 Now let's run it again to see what happens. 08:27.390 --> 08:30.810 So everything should be removed and just recreate it. 08:30.810 --> 08:35.070 And also everything should start from ID one. 08:35.070 --> 08:36.780 And this is what we wanted. 08:36.810 --> 08:37.290 Okay. 08:37.320 --> 08:44.580 So now that we have some data we can go ahead and really implement some logic displaying blog posts. 08:44.610 --> 08:52.560 The admin dashboard and all the UI parts of our blog application.