WEBVTT 00:00.200 --> 00:06.080 Okay, guys, let's create our script that will load the database schema. 00:07.130 --> 00:12.890 So first let me jump to the bootstrap file because this one is incomplete. 00:12.920 --> 00:15.650 We are having an auto loader here. 00:15.680 --> 00:19.850 We are declaring the strict types and loading the config. 00:19.850 --> 00:24.800 But we haven't really used our dependency injection container yet. 00:25.910 --> 00:29.420 So I'd like to use the core app. 00:29.780 --> 00:33.590 Let me add this use statement somewhere over here. 00:35.810 --> 00:40.850 And now I'd like to bind to this dependency injection container. 00:41.090 --> 00:44.570 First I'd like to get the configuration. 00:45.110 --> 00:47.960 That's something we are loading right up here. 00:47.990 --> 00:53.690 And another thing that I'd like to bind is the database. 00:53.690 --> 01:01.820 So essentially those resources whether that's config or our created object will be available under those 01:01.820 --> 01:02.930 names. 01:03.080 --> 01:10.430 Now here we are going to create a new new instance of this database class. 01:10.550 --> 01:13.460 Let's make sure we have a use statement for it. 01:13.460 --> 01:17.690 So this is core database. 01:18.410 --> 01:22.490 And the database requires a config. 01:22.520 --> 01:27.410 That's why we're going to get the config database. 01:27.410 --> 01:29.540 So we've got some type issues here. 01:29.540 --> 01:31.100 Let's check this out. 01:31.130 --> 01:34.700 Okay so apparently I forgot to add a namespace. 01:36.020 --> 01:36.530 Okay. 01:36.530 --> 01:38.420 So let's add the core namespace. 01:38.420 --> 01:39.350 There we have it. 01:39.380 --> 01:40.700 Everything is fine. 01:40.730 --> 01:43.670 Now our bootstrap file is complete. 01:43.670 --> 01:46.910 We can use the database in both the HTTP server. 01:46.910 --> 01:51.080 So our application and inside the command line scripts. 01:51.110 --> 01:53.540 Now let's go ahead and create one. 01:54.710 --> 01:59.380 So I can close this bootstrap file and all the scripts. 01:59.380 --> 02:05.170 We're going to put them inside the bin folder as some binary scripts. 02:05.410 --> 02:07.660 Let me create the first one. 02:08.830 --> 02:12.130 I'm going to call this schema underscore load. 02:12.160 --> 02:15.190 PHP add a PHP tag. 02:15.190 --> 02:20.320 And the first thing we're going to do is we require once the bootstrap file. 02:21.310 --> 02:23.710 So we're getting the current directory. 02:25.780 --> 02:31.000 And I think we need to go one level up and get the bootstrap file. 02:31.090 --> 02:31.600 Okay. 02:31.630 --> 02:35.200 So we should have a dependency injection container here. 02:35.200 --> 02:46.960 And since the container uses static methods I can grab the database by just using the app get method. 02:46.960 --> 02:50.260 And the name is database. 02:50.500 --> 02:53.440 So at this point I'm just connected to database. 02:53.440 --> 02:57.250 I don't need to do anything extra. 02:58.120 --> 02:59.740 This gives me a database. 02:59.740 --> 03:05.800 The one slight problem that we have is that this returns a mixed type. 03:05.890 --> 03:14.470 So later on, when we're gonna use the methods of the database object of the database class, we won't 03:14.470 --> 03:18.700 get any suggestions inside the editor or the IDE. 03:19.060 --> 03:22.930 And we are going to solve this problem a little later. 03:22.930 --> 03:25.900 For now we need to manage somehow. 03:26.320 --> 03:31.780 Now let me move this require line at the top of the script. 03:32.530 --> 03:33.010 All right. 03:33.010 --> 03:35.170 So the logic here is pretty simple. 03:35.170 --> 03:38.290 We need to figure out the schema file. 03:38.290 --> 03:41.290 So this is also the current directory. 03:41.590 --> 03:53.200 And I'm going to go up and grab from the database folder I think this is called schema SQL. 03:53.460 --> 03:58.320 We might make this configurable, but I think it's just good enough for now. 03:58.350 --> 04:00.840 Okay, so this is database schema SQL. 04:01.140 --> 04:03.930 That should be fine. 04:05.250 --> 04:10.560 And the actual SQL to run we're going to use file getcontents. 04:10.560 --> 04:14.670 This will just load the contents of the schema file. 04:15.180 --> 04:16.860 So the SQL to run. 04:16.860 --> 04:19.920 And at this point we're going to run it. 04:19.950 --> 04:23.730 Let's wrap everything with a try catch block. 04:23.760 --> 04:29.250 And here we're just going to be catching a generic exception. 04:29.670 --> 04:38.280 So if anything goes wrong we're gonna say error loading schema maybe. 04:38.310 --> 04:40.920 We're gonna also output the message. 04:40.920 --> 04:43.170 This is not for our users. 04:43.170 --> 04:44.760 That's always for us. 04:44.760 --> 04:47.880 So I think it's safe to just output that. 04:49.800 --> 04:50.130 Okay. 04:50.130 --> 04:53.400 I think this error handling is enough. 04:53.940 --> 04:59.670 Let's add the concatenation character and the logic goes inside this try block. 04:59.670 --> 05:02.220 So let's take a look at the schema again. 05:02.610 --> 05:11.580 This is just a series of create table statements that as you can see are separated by semicolon. 05:12.480 --> 05:14.220 Okay let's use that. 05:15.270 --> 05:19.080 Maybe we can use explode. 05:19.080 --> 05:20.160 Let's see. 05:20.460 --> 05:22.080 Let me call that parts. 05:22.080 --> 05:36.150 And let's use PHP explode function where the separator is semicolon and the string is SQL. 05:36.240 --> 05:40.590 Notice I'm using named parameters for a built in PHP function. 05:41.040 --> 05:50.510 And at this point let's just see what those parts are and stop okay, now I can open the command line 05:50.510 --> 05:55.310 and I'm going to run PHP bin schema load PHP. 05:55.580 --> 05:56.000 Okay. 05:56.030 --> 05:59.060 So we've got some issues inside the database. 05:59.300 --> 06:00.500 PHP file. 06:01.730 --> 06:02.090 Okay. 06:02.090 --> 06:05.990 So we are missing the PDO class. 06:06.500 --> 06:09.290 Let's make sure it's imported here. 06:09.290 --> 06:12.110 So we need to use the PDO class. 06:12.560 --> 06:21.680 When I'm just typing the PDO exception I just got a suggestion to import those classes. 06:22.610 --> 06:25.040 I think yes almost everything. 06:25.040 --> 06:27.560 We also need the PDO statement. 06:27.560 --> 06:31.910 So I did that to have this list of imports. 06:31.940 --> 06:40.370 Exception pdo pdo exception pdo statement I think that should be everything, at least from that file. 06:40.430 --> 06:43.910 Okay, let's try running this again. 06:44.990 --> 06:52.760 So this is our SQL but we've got four items and we are creating three tables, not four. 06:53.480 --> 06:54.440 Let's take a look. 06:54.440 --> 06:57.200 So that's an SQL. 06:57.230 --> 06:58.850 This is an SQL. 06:59.150 --> 07:00.740 This looks like an SQL. 07:00.740 --> 07:07.700 And then the fourth element with index three is an empty string. 07:08.270 --> 07:12.320 Well we don't want that that's for sure. 07:12.320 --> 07:19.370 So let's wrap that with an array filter without any additional parameters. 07:19.370 --> 07:24.320 It will just check if every single item is not empty. 07:24.350 --> 07:26.720 If you skip this callback. 07:26.720 --> 07:28.670 So we are skipping the callback. 07:28.670 --> 07:33.140 And I expect to get three items right now. 07:33.500 --> 07:36.020 And that's the case. 07:36.020 --> 07:40.910 We've got an array with three SQL statements creating the tables. 07:41.810 --> 07:46.570 Okay, why don't we run it one by One. 07:47.230 --> 07:59.140 Let's do the for each loop and go with parts as part or call it SQL part. 08:01.930 --> 08:03.250 And now the db. 08:03.520 --> 08:12.820 The database object has a query method to which we pass the SQL part. 08:13.630 --> 08:15.880 So we run it one by one. 08:15.910 --> 08:24.340 Obviously we need to get rid of this line and if everything goes fine we're just going to say schema 08:24.370 --> 08:28.090 loaded successfully. 08:28.960 --> 08:30.190 And a new line. 08:33.460 --> 08:34.810 Let's run it again. 08:34.840 --> 08:35.200 Mm. 08:35.290 --> 08:37.420 Apparently everything was loaded. 08:37.810 --> 08:44.290 So now I can open it using this browser inside Visual Studio Code. 08:44.710 --> 08:47.770 Well, we didn't have to create this file. 08:47.770 --> 08:56.530 I can see we've got those tables with all the columns that we have defined, which means that this script 08:56.530 --> 08:57.820 has worked fine. 08:59.230 --> 09:04.480 So even though the script is fine, let's make a further improvement. 09:06.010 --> 09:16.420 And for now, let me delete this database, the blog SQLite file like that and let me open the composer.json. 09:16.480 --> 09:23.830 So using composer, you can also define some custom scripts that will make it a little bit easier to 09:23.830 --> 09:28.000 run your own command line interface commands. 09:28.000 --> 09:32.170 And also it will help everyone using your commands. 09:32.170 --> 09:34.240 Guess the proper name. 09:34.720 --> 09:36.610 You're gonna see that in action in a second. 09:36.610 --> 09:43.650 So we can add this scripts section where you're gonna give your command names like schema load. 09:43.680 --> 09:49.050 That's the command name and it will run PHP bin forward slash. 09:49.620 --> 09:51.810 This is load schema. 09:51.840 --> 09:52.740 Schema load. 09:52.770 --> 09:56.010 That's schema underscore load PHP. 09:57.570 --> 10:03.930 Right now I can just write composer schema. 10:05.550 --> 10:08.970 And as you see this command is not found. 10:08.970 --> 10:14.910 But it is suggesting that I run schema load okay. 10:14.910 --> 10:18.990 So let me run composer schema load. 10:19.440 --> 10:27.120 There it is schema loaded successfully and obviously all the tables are there. 10:28.020 --> 10:29.010 So we're going to use this. 10:29.040 --> 10:37.440 We're going to use this composer scripts section to make it much easier to run all of the command line 10:37.440 --> 10:41.370 scripts that we're going to create for our PHP apps.