WEBVTT 00:01.400 --> 00:02.000 Okay, guys. 00:02.000 --> 00:08.990 So last time we've created this dependency injection container, we've also moved some startup code 00:08.990 --> 00:14.360 to the bootstrap file and the config of the app into the config file. 00:14.390 --> 00:21.440 Our next step is to create a database abstraction so that we can first initialize the database using 00:21.440 --> 00:26.900 the command line script, and then just work with the database inside the app. 00:26.900 --> 00:31.130 So that's another thing we are adding to our core namespace. 00:31.130 --> 00:34.130 Let me call this database. 00:34.970 --> 00:39.740 And that would be a class that we're going to use to work with the database. 00:39.740 --> 00:42.890 So that's our database class. 00:43.010 --> 00:47.690 And inside it we'd like to have the PDO instance. 00:47.690 --> 00:51.680 This is what we create when this class is created. 00:51.950 --> 00:55.490 That means we next up need to add a constructor. 00:56.000 --> 01:01.440 And this will accept the config by the way the config is an array. 01:01.470 --> 01:04.200 It's exactly what we have under the database. 01:04.230 --> 01:06.990 Key inside our config.php file. 01:07.020 --> 01:12.450 Now our goal is to make this config as universal as possible. 01:12.720 --> 01:14.700 And we are using PDO here. 01:14.730 --> 01:21.840 PDO is a database abstraction in PHP that can work with a lot of different databases. 01:21.840 --> 01:28.680 So this constructor job is to connect to the database and just throw an exception if it fails. 01:29.160 --> 01:33.330 So we should be adding a try catch block inside. 01:33.570 --> 01:39.210 And the exception kind I'd like to catch is this PDO exception. 01:40.050 --> 01:45.330 So when it happens I'd like to throw our own custom exception. 01:47.400 --> 01:52.410 That would just say could not connect to the database. 01:53.610 --> 01:56.190 Maybe without any more details. 01:56.220 --> 01:58.530 Okay, now the connection itself. 01:58.530 --> 02:04.720 So we've got this PDF field we need to initialize it by creating a new PDL object. 02:04.720 --> 02:10.030 As you see, it accepts different parameters DSN, which stands for Data Source name. 02:10.030 --> 02:17.560 I'm going to go back to it in a second and then optionally the database user username, then the password, 02:17.560 --> 02:21.610 and then some additional options if you have them okay. 02:21.640 --> 02:24.070 So first the DSN. 02:24.070 --> 02:31.330 Well that's just a string that you construct which can contain info like the port, the database name, 02:31.360 --> 02:32.200 etc.. 02:32.230 --> 02:36.220 This can look a little bit different for different databases. 02:36.220 --> 02:43.870 That's why we should add a method that will generate a proper DSN depending on the driver. 02:43.870 --> 02:48.880 So I can call that create DSN. 02:48.880 --> 02:53.980 And it's going to accept our config array and return a string. 02:55.240 --> 03:02.510 So if we take a look at the config again we've got this driver setting and this database entry inside 03:02.510 --> 03:08.300 the config should accept the properties that are specific to our database. 03:08.300 --> 03:15.920 So in case of SQLite, we only want the path if we would be using MySQL or Postgres, this would also 03:15.920 --> 03:20.330 be username, password and the database name etc.. 03:22.190 --> 03:30.080 So here let's get the driver from the config driver. 03:30.260 --> 03:34.850 So the config that we are receiving here is this database key. 03:34.850 --> 03:37.340 So we are not getting the whole config. 03:37.370 --> 03:40.610 Next up let's get the database name. 03:42.740 --> 03:47.480 So we've got this configured differently in the config dot path. 03:47.480 --> 03:53.750 So it's just because in SQLite the database name is actually the path to the file. 03:53.780 --> 03:57.650 But to make it more universal let's call it database name. 03:57.650 --> 03:58.640 So we would. 03:58.670 --> 04:04.320 So then it will also make more sense with other database systems. 04:05.220 --> 04:13.920 And now we should return the value from matching the driver, as there will be different DSN depending 04:13.920 --> 04:15.420 on the driver. 04:15.990 --> 04:18.690 So we only support SQLite. 04:18.720 --> 04:21.510 You can add more later on if you prefer. 04:21.540 --> 04:29.220 And in SQLite it looks like this starts with SQLite colon followed by the file name which is just database 04:29.250 --> 04:30.870 name in our case. 04:31.170 --> 04:39.060 Let's also add the default case, which would just throw a new exception because we've got an unsupported 04:39.060 --> 04:39.600 driver. 04:39.600 --> 04:48.900 So let's say unsupported driver or maybe be specific database driver. 04:50.040 --> 04:50.760 Driver. 04:50.760 --> 04:57.540 In case someone misspells the database driver it will also catch that and throw an exception. 04:57.870 --> 05:00.630 So this is our DSN function. 05:00.900 --> 05:07.700 So going back to the constructor, which will construct the database, we need to grab the DSN. 05:08.270 --> 05:15.080 So we call this create DSN passing the config. 05:15.080 --> 05:16.610 So we've got this. 05:16.910 --> 05:18.620 We can pass that to PDL. 05:18.650 --> 05:20.930 That's the only required argument. 05:21.350 --> 05:25.580 But then we also should support the username. 05:25.580 --> 05:27.920 So we get that from the config. 05:27.950 --> 05:29.750 Only if it's present. 05:30.140 --> 05:32.480 So we do config username. 05:32.480 --> 05:35.480 But if it's not set we default to null. 05:35.960 --> 05:41.270 Now let's do the same with the password. 05:43.070 --> 05:46.970 So only if the password is present in the options we use it. 05:46.970 --> 05:51.860 And finally the third thing is options. 05:52.340 --> 05:56.510 We use the options from the config if they are set. 05:56.540 --> 05:58.130 If not we ignore them. 05:58.130 --> 06:01.850 And now let's pass that username password. 06:01.850 --> 06:04.340 And the fourth argument is options. 06:04.370 --> 06:12.470 Now we have a pretty universal config that will connect to different kinds of databases, but obviously 06:12.470 --> 06:16.670 we need to add those dsns for different databases. 06:16.670 --> 06:19.460 I might add that at the end of this video. 06:19.490 --> 06:21.980 So you can see how this would look like. 06:21.980 --> 06:28.880 For now, we need to add some useful methods that will make using the database easier. 06:28.910 --> 06:31.940 Let's start with a query method. 06:33.740 --> 06:35.150 Now let me explain. 06:35.150 --> 06:37.610 Why do we even create this abstraction? 06:37.610 --> 06:43.160 Because that's not obvious and you are probably guessing why. 06:43.160 --> 06:46.340 But let me be clear about that. 06:46.340 --> 06:54.680 So we just want one common way of connecting to database and interacting with the database throughout 06:54.680 --> 06:58.250 the whole application and in the future, the next projects. 06:58.250 --> 07:03.170 That's why we are building this small framework, let's call it this way. 07:03.320 --> 07:08.390 And we also want to be really strong on security. 07:08.390 --> 07:14.600 So what I mean by that is there are some best practices when it comes to working with databases, like 07:14.630 --> 07:22.670 using prepared statements and using PDO, not some other functions that are also available in PHP. 07:22.700 --> 07:28.790 That's why we create this database class so we have best practices in it. 07:28.820 --> 07:29.960 It's universal. 07:29.960 --> 07:37.280 It can work with different database systems and also it will just save us some work. 07:37.520 --> 07:47.120 Its job is to always connect the same way, use some sensible default config and avoid any security 07:47.150 --> 07:48.650 vulnerabilities. 07:49.640 --> 07:55.700 So speaking about the vulnerabilities, let's add the first public function called query. 07:56.960 --> 08:06.780 So we need to accept the SQL and the optional params of type array that's initialized with an empty 08:06.780 --> 08:07.200 array. 08:07.230 --> 08:11.010 This will just return the PDO statement. 08:12.810 --> 08:16.350 So we will use prepared statements. 08:17.070 --> 08:26.220 And this will protect us from vulnerability called SQL injection so that we make sure no one will pass 08:26.220 --> 08:34.290 any additional SQL through parameters that can be dangerous to our database and our users. 08:34.290 --> 08:40.680 That's why we call PDO prepare passing the SQL given. 08:42.000 --> 08:48.390 Then on the statement object that we've created, we execute it. 08:48.900 --> 08:52.200 So this PDO statement has the execute methods only. 08:52.200 --> 09:01.740 Then we pass the parameters and then we return the statement so something else can be done with it. 09:03.210 --> 09:03.450 Okay. 09:03.490 --> 09:06.760 next up we are going to use it twice ourselves. 09:07.360 --> 09:11.080 So let's add another useful method to this class. 09:11.110 --> 09:13.480 This will be called Fetch all. 09:14.590 --> 09:20.170 That's something that we're gonna be using probably quite often. 09:20.260 --> 09:27.460 So we are passing the SQL and then params which we initialize with an empty array. 09:27.460 --> 09:31.360 And this should return an array of things. 09:31.990 --> 09:35.860 And we're going to use this query. 09:37.090 --> 09:40.600 And there we pass our SQL with params. 09:40.600 --> 09:46.480 But then it returns this PDF statement that we can only then do something with. 09:46.480 --> 09:47.680 And we do fetch. 09:47.710 --> 09:55.900 All this would just fetch all the results and we want to use fetch um object. 09:57.580 --> 10:03.790 Let me find this constant so that every record would be fetched as an object. 10:03.790 --> 10:09.890 We're going to have some model classes later on, and every model will just represent specific table 10:09.890 --> 10:11.810 and the results from that table. 10:11.840 --> 10:14.270 That's why I want to use objects. 10:14.330 --> 10:14.660 Okay. 10:14.690 --> 10:17.510 So this would be pretty useful. 10:17.510 --> 10:22.850 And another pretty useful thing is just the fetch method. 10:23.450 --> 10:26.660 This will be used to fetch one record at a time. 10:26.660 --> 10:34.580 And it has the same signature as fetch all except it doesn't return an array. 10:35.330 --> 10:37.520 It returns an object. 10:39.050 --> 10:44.720 So let's use this type and object are false because we've got two possible types here. 10:44.720 --> 10:47.780 That's why I'm using the so-called union type. 10:47.810 --> 10:49.670 You need to go back to PHP basics. 10:49.670 --> 10:51.470 If you don't know what that is. 10:51.500 --> 10:54.680 And then I'm calling query. 10:54.680 --> 10:58.550 Same way with the SQL and the params. 10:59.090 --> 11:02.000 And then I follow with fetch. 11:02.210 --> 11:06.660 So I'd like to use fetch object again. 11:07.800 --> 11:09.390 Okay, we're almost done here. 11:09.390 --> 11:14.580 So the last method is last insert id. 11:15.090 --> 11:18.540 So obviously we're not going to be only fetching the data. 11:18.540 --> 11:21.120 We're also going to be inserting it. 11:22.260 --> 11:24.870 And this will return a string or false. 11:24.870 --> 11:27.150 So again union type. 11:27.150 --> 11:30.810 And this is just on PDF. 11:30.840 --> 11:33.000 We're going to call last insert ID. 11:33.150 --> 11:39.810 Now as you see last insert ID will return either string or false itself. 11:40.950 --> 11:46.320 So this would be useful to grab the ID of just inserted record. 11:46.320 --> 11:50.070 And this class is now complete. 11:50.910 --> 11:52.980 Well it is almost complete. 11:52.980 --> 12:00.240 I forgot about one pretty important thing which is to set some attributes of the PDL. 12:00.240 --> 12:04.170 And this is the PDL error mode. 12:04.170 --> 12:07.570 So this is attribute error mode. 12:07.570 --> 12:17.770 And I'd like all the errors that happen during whatever we do with databases to be the. 12:18.880 --> 12:21.460 Can I find this on the list error mode exception? 12:21.460 --> 12:23.020 Yes, this is what I meant. 12:23.170 --> 12:30.850 I'd like the exception to be thrown every single time there is any kind of issue with the databases, 12:30.850 --> 12:34.000 so that we can catch those in the first place. 12:34.300 --> 12:36.040 Okay, now we are done. 12:36.040 --> 12:42.550 At least we are done with implementing our tiny database abstraction, which would save us a lot of 12:42.550 --> 12:43.090 time. 12:43.090 --> 12:50.440 I think, again, we should take a break right now and only then next up we're gonna wire it together 12:50.440 --> 12:55.630 with the config, create a database object that we can actually use. 12:55.630 --> 13:03.700 And the first use case would be to create a command line interface command that would create our SQLite 13:03.700 --> 13:05.380 database tables.