WEBVTT 00:01.700 --> 00:07.160 So this diagram got modified a little because we've got new requirements. 00:07.370 --> 00:15.350 So so far we've been only worrying about our application and the graphical user interface to it. 00:15.410 --> 00:23.210 So essentially everything that users can see now we need to go back to the command line interface. 00:23.300 --> 00:30.230 So you should be familiar with it because we learned the PHP language basics using a CLI. 00:30.260 --> 00:36.680 So running the PHP code through a terminal not through a graphical user interface like a browser. 00:36.920 --> 00:39.710 So what's this all about? 00:40.130 --> 00:44.900 Now let's first remind ourselves what we need to do. 00:44.930 --> 00:49.160 We would have to create the database schema. 00:49.190 --> 00:54.440 This means we need to make sure that this file schema SQL is run. 00:55.280 --> 01:03.660 So this file contains the definition of the database tables or just table because we have just one. 01:03.960 --> 01:10.920 Anyway, we need to make sure that the database table is created. 01:10.950 --> 01:14.550 Doing it manually is not a good idea. 01:14.580 --> 01:21.660 Now don't get me wrong, this is still possible and if you are working on your app alone, then maybe 01:21.660 --> 01:22.350 it's fine. 01:22.350 --> 01:27.360 But I'm trying to teach you the best practices, not just any practices. 01:27.390 --> 01:36.870 That's why we should have at least one file that would be responsible for creating the database schema. 01:36.870 --> 01:46.740 And this can be a set of command line tools that we can have for our app, because it doesn't have to 01:46.740 --> 01:48.780 be limited to just one command. 01:48.810 --> 01:58.050 We have a requirement for one command right now for this schema PHP command that would load the schema 01:58.080 --> 02:00.750 into a SQLite database. 02:00.780 --> 02:04.470 but the requirements in the future might be different. 02:04.470 --> 02:07.080 We might need more such commands. 02:07.110 --> 02:11.700 Okay, so without further ado, let's get to the point. 02:11.730 --> 02:16.800 The point is that currently we've got this index.php file. 02:16.800 --> 02:19.470 Let me open it for a quick reminder. 02:21.270 --> 02:26.130 So here we define some constants with the paths of different directories. 02:26.130 --> 02:32.010 We also load some files so they are available in every single file of the app. 02:32.130 --> 02:34.320 And we handle the request. 02:34.320 --> 02:43.620 And actually what was defined as this file responsibilities where it is just an entry point a front 02:43.620 --> 02:44.190 controller. 02:44.190 --> 02:46.530 So it should only do this part. 02:46.560 --> 02:51.630 It should dispatch the request to a specific route handler. 02:51.660 --> 02:56.760 That's why we should create another file called bootstrap. 02:56.790 --> 03:06.010 This is a file that job is to initialize everything our application needs, and this bootstrap file 03:06.010 --> 03:10.960 can then be reused by different parts of our application. 03:11.530 --> 03:22.510 So for our user facing app that is our website we're going to use bootstrap inside the front controller. 03:22.510 --> 03:25.000 So public index.php file. 03:25.030 --> 03:36.940 But also to not copy paste the same code inside the schema and possibly other command line utilities 03:36.940 --> 03:38.440 command line scripts. 03:38.650 --> 03:41.980 We will we will also reuse this bootstrap file. 03:41.980 --> 03:43.660 So we have all those goodies. 03:43.660 --> 03:48.460 We import all the files and we get access to all the functions. 03:48.460 --> 03:50.920 Just the logic there would be different. 03:50.920 --> 03:54.970 It would load the data into the database. 03:57.250 --> 03:57.850 Okay. 03:57.850 --> 04:03.110 So after this Pretty long introduction. 04:03.140 --> 04:10.730 Now let's focus on creating the schema file, adding the missing functionality. 04:10.730 --> 04:14.960 Because we don't have a function that will load the data into the database. 04:14.960 --> 04:19.700 And extracting this code here into a bootstrap. 04:19.700 --> 04:28.340 By the way, that's a common technique that you would see in many different web frameworks like Laravel, 04:28.370 --> 04:31.280 Symfony, and also frameworks from other languages. 04:31.280 --> 04:33.650 So that's not something I've made up. 04:33.920 --> 04:42.290 So we are just learning things that will future proof you as not only a PHP developer, but also a framework 04:42.290 --> 04:49.010 developer and a developer that can use any language best practices. 04:49.040 --> 04:51.650 Okay, let's get to some coding. 04:52.130 --> 05:01.560 So now let me expand the coding space, but still keeping an eye on the diagram so we know what needs 05:01.560 --> 05:02.520 to be done. 05:03.420 --> 05:07.080 So I think the first step should be to create this bootstrap file. 05:07.080 --> 05:09.840 And I think it can be at the root directory. 05:11.190 --> 05:13.350 So that's the bootstrap PHP. 05:15.330 --> 05:21.120 So now we should just be moving some of the parts from the index.php file. 05:22.230 --> 05:27.390 So those constants they need to be moved to bootstrap. 05:28.500 --> 05:32.130 Also it might be useful to have this declaration. 05:33.120 --> 05:34.680 Let's have it here. 05:34.770 --> 05:37.320 Then well the session start. 05:37.320 --> 05:43.530 It's not really required because the idea is that all those include files all those included files. 05:43.560 --> 05:46.230 They don't run any code automatically. 05:46.230 --> 05:53.970 This would be a bad practice I think, because this would mean that by including some file you basically 05:53.970 --> 05:54.930 have a side effect. 05:54.930 --> 05:57.210 It's running some unexpected code. 05:57.420 --> 06:01.020 So this is called includes for a reason. 06:01.020 --> 06:07.440 You should be able to safely include those files without worrying what would happen. 06:08.040 --> 06:14.760 Now we've got these paths here updated because they originally were looking like this. 06:14.760 --> 06:21.630 So just make sure that you have those paths correctly relative to bootstrap PHP file. 06:21.660 --> 06:25.200 So bootstrap PHP is at the root directory. 06:25.560 --> 06:33.000 That means you need to get rid of this double dots, because those folders are just at the same level 06:33.000 --> 06:33.960 as bootstrap. 06:33.960 --> 06:40.230 And those directories need to be relative to this new bootstrap PHP file. 06:41.040 --> 06:41.430 Okay. 06:41.460 --> 06:43.740 So this file is complete. 06:43.740 --> 06:52.710 We can save it now and don't refresh the page because it will be broken for a slight second before we 06:52.710 --> 06:56.490 just require once and we are in the index. 06:56.490 --> 07:04.030 So I use the current directory dot forward slash two dots to go one directory up and bootstrap. 07:04.330 --> 07:04.720 PHP. 07:06.940 --> 07:11.470 Now this should keep our page working. 07:11.470 --> 07:12.610 Now I'm refreshing it. 07:12.610 --> 07:13.570 Navigating. 07:13.600 --> 07:17.110 Everything is fine, so it worked perfectly. 07:17.140 --> 07:23.620 Now our schema updating script CLI. 07:23.650 --> 07:26.050 So we should create a new directory. 07:26.050 --> 07:34.600 We can either call it CLI or often this directories with scripts are called bin as binary. 07:34.960 --> 07:36.640 So let's go with binary. 07:37.000 --> 07:39.310 Now let's create the schema php. 07:40.360 --> 07:53.410 Add the strict types declaration then require once the bootstrap php file. 07:53.890 --> 07:55.540 And there we have it. 07:55.570 --> 08:00.100 Next up we should just add some function call that will do something. 08:00.140 --> 08:01.370 And what do you want to do? 08:01.400 --> 08:08.480 Well, we would like to use this schema SQL file to load the database schema. 08:09.560 --> 08:12.770 So let's jump to db php file. 08:13.460 --> 08:16.490 And here let's add a function that will do that. 08:16.490 --> 08:23.840 That will load the schema from schema SQL file into the SQLite database file. 08:25.040 --> 08:33.500 Now let's add a function that would load the contents of this schema SQL file that we have right here 08:33.500 --> 08:36.830 into an SQLite database. 08:38.240 --> 08:40.760 So this doesn't return anything. 08:41.780 --> 08:43.490 And the arguments. 08:43.490 --> 08:45.620 So the first is PDO. 08:45.620 --> 08:47.750 So the connection to the database. 08:47.900 --> 08:51.110 And the second would be the schema file. 08:51.110 --> 08:57.440 So we want this to be flexible so that you can load multiple schema files. 08:57.440 --> 08:59.690 If you need to. 08:59.690 --> 09:03.240 And here we also don't have any error handling. 09:03.630 --> 09:06.450 As I've said previously, I'm going to talk about that later. 09:06.450 --> 09:11.970 So I just don't want to confuse you with some additional concepts. 09:12.990 --> 09:17.550 So the first thing that we need to do is we need to load the schema file. 09:17.970 --> 09:20.820 So we do file get contents. 09:21.240 --> 09:28.500 That's a nice little handy PHP function for loading the complete contents of a file. 09:29.280 --> 09:34.050 So it's in the file name is the is in the schema file variable. 09:34.080 --> 09:35.670 So this is pretty easy. 09:35.700 --> 09:41.460 Now this function will return false if the file could not be loaded. 09:41.460 --> 09:45.090 So we check if SQL is exactly false. 09:45.750 --> 09:55.140 So I'm adding false on the left side so that I won't accidentally associate false to SQL because that's 09:55.140 --> 09:55.800 comparison. 09:55.800 --> 09:59.160 But one equal sign would be association. 09:59.160 --> 10:04.890 And I'll be honest with you, I made this mistake hundreds of times in my life. 10:04.890 --> 10:06.780 So I've learned the hard way. 10:06.780 --> 10:15.030 And when you add the constant on the left, there is no way your editor won't highlight this as an error. 10:15.030 --> 10:20.070 And it wouldn't do it if SQL variable would be on the left side. 10:20.880 --> 10:30.780 So that's a good practice you can learn, and if so, if this fails, we would normally do something 10:30.780 --> 10:32.760 that is related to error handling. 10:32.760 --> 10:40.740 But since I have said I'm not going to talk about this just yet, let's just die saying um. 10:43.230 --> 10:52.950 Failed to open or failed to load schema because this error is actually not due to failing to load the 10:52.950 --> 10:56.610 schema into the database, it's because we couldn't open the file. 10:56.610 --> 11:00.730 So the file was incorrect. 11:00.760 --> 11:01.720 Who knows. 11:01.720 --> 11:07.390 So maybe I can also say that the file is at fault. 11:09.220 --> 11:15.550 So it would be obvious that maybe something is wrong with the file with the schema file for the people. 11:15.580 --> 11:18.520 I mean the SQL schema file. 11:19.210 --> 11:24.700 And if this went fine, we get access to PDO. 11:24.730 --> 11:27.970 This is the connection returned from connect DB. 11:28.090 --> 11:31.420 So it's also the responsibility of this schema. 11:31.900 --> 11:34.570 PHP script to connect to database. 11:34.600 --> 11:37.570 And the easiest function is exec. 11:37.600 --> 11:42.820 It just runs some SQL code without any additional thought. 11:42.850 --> 11:47.530 So we've loaded the SQL from schema SQL file. 11:47.800 --> 11:51.310 If we couldn't load we just crash. 11:51.730 --> 11:54.010 If we did load, we run it. 11:54.010 --> 12:01.130 And if nothing goes wrong and it would go wrong if there would be any issues. 12:01.190 --> 12:03.590 Then we can safely say at this point. 12:03.620 --> 12:04.520 Schema. 12:04.550 --> 12:08.450 Load it successfully. 12:08.570 --> 12:10.250 I hope I spelled that right. 12:10.280 --> 12:12.320 It's a tough word. 12:12.410 --> 12:13.910 And add a new line. 12:13.910 --> 12:19.040 So it's clear in the terminal that it was a success. 12:19.220 --> 12:22.100 Okay, so I think this function is ready. 12:22.130 --> 12:29.270 Now let me go back to schema PHP where I can run load schema. 12:29.690 --> 12:31.820 I need to pass the PDO. 12:32.210 --> 12:39.680 So I just do connect db and I point to the schema file. 12:39.680 --> 12:43.700 So a quick reminder with this in the db folder. 12:43.760 --> 12:48.860 So we've got this db directory I can use because I'm importing a bootstrap. 12:49.730 --> 12:53.750 This needs a slash and schema SQL. 12:54.140 --> 12:55.970 I think that's the file name. 12:55.970 --> 12:57.560 It creates just a single table. 12:57.560 --> 13:01.170 So we should see this table after we run that script. 13:02.190 --> 13:02.820 Okay. 13:02.850 --> 13:05.760 I think that should be everything. 13:05.760 --> 13:07.260 Now let's run it. 13:07.500 --> 13:10.530 So stop the server if you have it running. 13:10.530 --> 13:19.770 I just opened another tab in the terminal and I run PHP bin schema. 13:20.640 --> 13:23.100 And there is an issue. 13:23.130 --> 13:27.270 We can't open the bootstrap file. 13:27.300 --> 13:27.840 Okay. 13:27.840 --> 13:29.190 So I know what's the problem. 13:29.220 --> 13:32.730 Um, we need to give it an absolute path. 13:32.760 --> 13:36.690 That's why I need to start with this dear constant. 13:36.690 --> 13:44.250 So as a quick reminder in PHP, this would always point to the full path to the current file, this 13:44.250 --> 13:45.690 file schema PHP. 13:46.140 --> 13:52.950 So now I point to the actual bootstrap php file and let me clear the terminal. 13:52.950 --> 13:54.000 Run it again. 13:54.120 --> 13:56.370 Schema loaded successfully. 13:56.370 --> 13:57.600 Well congrats! 13:57.630 --> 14:01.230 Now let me open this file db SQLite. 14:01.230 --> 14:05.340 It's already warning me that this is a binary file. 14:05.460 --> 14:13.590 I'm going to open it anyway, and this looks like a proper SQLite file with some database tables. 14:13.620 --> 14:18.150 Now let's use a database client to see if that's the case. 14:19.560 --> 14:23.910 So as the last step, let's verify if everything went fine. 14:23.910 --> 14:29.190 So let's jump to VSCode extensions and look for SQLite. 14:29.220 --> 14:32.430 By the way you can just select this view from the menu. 14:32.460 --> 14:40.290 The view extensions works the same on windows and I would pick up the second one. 14:40.350 --> 14:46.890 SQLite viewer seems to be simpler and we can have a visual preview. 14:46.920 --> 14:50.280 This one is more for geeks I guess. 14:50.970 --> 14:54.300 So let me install this one SQLite viewer. 14:54.330 --> 14:57.630 Okay, I think we have it now. 14:57.630 --> 15:02.770 Let's just jump back to the directory view and we can just double click this DB. 15:02.800 --> 15:03.910 SQLite. 15:04.570 --> 15:05.560 There it is. 15:05.590 --> 15:07.810 We've got the messages table. 15:07.810 --> 15:08.890 It's empty. 15:08.890 --> 15:11.800 But here you can see the structure. 15:11.800 --> 15:14.950 Everything is according to our schema. 15:15.100 --> 15:19.030 So ID name email message Createdat columns. 15:19.450 --> 15:24.670 So we can confirm that everything went perfectly fine. 15:24.700 --> 15:33.910 Now again if you don't like to use this specific extension, which I can't recommend because I haven't 15:33.910 --> 15:40.060 used it a lot, you can try other database clients like Table Plus is a popular one. 15:40.090 --> 15:48.370 Now, there are probably also a lot of free database clients specific to SQLite or more generic ones. 15:48.370 --> 15:49.000 Anyway. 15:49.030 --> 15:55.300 We can confirm at this point that everything went fine and our database table is created. 15:55.300 --> 16:00.190 So next up we can load some records into this database table.