So this diagram got modified a little because we've got new requirements.
So so far we've been only worrying about our application and the graphical user interface to it.
So essentially everything that users can see now we need to go back to the command line interface.
So you should be familiar with it because we learned the PHP language basics using a CLI.
So running the PHP code through a terminal not through a graphical user interface like a browser.
So what's this all about?
Now let's first remind ourselves what we need to do.
We would have to create the database schema.
This means we need to make sure that this file schema SQL is run.
So this file contains the definition of the database tables or just table because we have just one.
Anyway, we need to make sure that the database table is created.
Doing it manually is not a good idea.
Now don't get me wrong, this is still possible and if you are working on your app alone, then maybe
it's fine.
But I'm trying to teach you the best practices, not just any practices.
That's why we should have at least one file that would be responsible for creating the database schema.
And this can be a set of command line tools that we can have for our app, because it doesn't have to
be limited to just one command.
We have a requirement for one command right now for this schema PHP command that would load the schema
into a SQLite database.
but the requirements in the future might be different.
We might need more such commands.
Okay, so without further ado, let's get to the point.
The point is that currently we've got this index.php file.
Let me open it for a quick reminder.
So here we define some constants with the paths of different directories.
We also load some files so they are available in every single file of the app.
And we handle the request.
And actually what was defined as this file responsibilities where it is just an entry point a front
controller.
So it should only do this part.
It should dispatch the request to a specific route handler.
That's why we should create another file called bootstrap.
This is a file that job is to initialize everything our application needs, and this bootstrap file
can then be reused by different parts of our application.
So for our user facing app that is our website we're going to use bootstrap inside the front controller.
So public index.php file.
But also to not copy paste the same code inside the schema and possibly other command line utilities
command line scripts.
We will we will also reuse this bootstrap file.
So we have all those goodies.
We import all the files and we get access to all the functions.
Just the logic there would be different.
It would load the data into the database.
Okay.
So after this Pretty long introduction.
Now let's focus on creating the schema file, adding the missing functionality.
Because we don't have a function that will load the data into the database.
And extracting this code here into a bootstrap.
By the way, that's a common technique that you would see in many different web frameworks like Laravel,
Symfony, and also frameworks from other languages.
So that's not something I've made up.
So we are just learning things that will future proof you as not only a PHP developer, but also a framework
developer and a developer that can use any language best practices.
Okay, let's get to some coding.
So now let me expand the coding space, but still keeping an eye on the diagram so we know what needs
to be done.
So I think the first step should be to create this bootstrap file.
And I think it can be at the root directory.
So that's the bootstrap PHP.
So now we should just be moving some of the parts from the index.php file.
So those constants they need to be moved to bootstrap.
Also it might be useful to have this declaration.
Let's have it here.
Then well the session start.
It's not really required because the idea is that all those include files all those included files.
They don't run any code automatically.
This would be a bad practice I think, because this would mean that by including some file you basically
have a side effect.
It's running some unexpected code.
So this is called includes for a reason.
You should be able to safely include those files without worrying what would happen.
Now we've got these paths here updated because they originally were looking like this.
So just make sure that you have those paths correctly relative to bootstrap PHP file.
So bootstrap PHP is at the root directory.
That means you need to get rid of this double dots, because those folders are just at the same level
as bootstrap.
And those directories need to be relative to this new bootstrap PHP file.
Okay.
So this file is complete.
We can save it now and don't refresh the page because it will be broken for a slight second before we
just require once and we are in the index.
So I use the current directory dot forward slash two dots to go one directory up and bootstrap.
PHP.
Now this should keep our page working.
Now I'm refreshing it.
Navigating.
Everything is fine, so it worked perfectly.
Now our schema updating script CLI.
So we should create a new directory.
We can either call it CLI or often this directories with scripts are called bin as binary.
So let's go with binary.
Now let's create the schema php.
Add the strict types declaration then require once the bootstrap php file.
And there we have it.
Next up we should just add some function call that will do something.
And what do you want to do?
Well, we would like to use this schema SQL file to load the database schema.
So let's jump to db php file.
And here let's add a function that will do that.
That will load the schema from schema SQL file into the SQLite database file.
Now let's add a function that would load the contents of this schema SQL file that we have right here
into an SQLite database.
So this doesn't return anything.
And the arguments.
So the first is PDO.
So the connection to the database.
And the second would be the schema file.
So we want this to be flexible so that you can load multiple schema files.
If you need to.
And here we also don't have any error handling.
As I've said previously, I'm going to talk about that later.
So I just don't want to confuse you with some additional concepts.
So the first thing that we need to do is we need to load the schema file.
So we do file get contents.
That's a nice little handy PHP function for loading the complete contents of a file.
So it's in the file name is the is in the schema file variable.
So this is pretty easy.
Now this function will return false if the file could not be loaded.
So we check if SQL is exactly false.
So I'm adding false on the left side so that I won't accidentally associate false to SQL because that's
comparison.
But one equal sign would be association.
And I'll be honest with you, I made this mistake hundreds of times in my life.
So I've learned the hard way.
And when you add the constant on the left, there is no way your editor won't highlight this as an error.
And it wouldn't do it if SQL variable would be on the left side.
So that's a good practice you can learn, and if so, if this fails, we would normally do something
that is related to error handling.
But since I have said I'm not going to talk about this just yet, let's just die saying um.
Failed to open or failed to load schema because this error is actually not due to failing to load the
schema into the database, it's because we couldn't open the file.
So the file was incorrect.
Who knows.
So maybe I can also say that the file is at fault.
So it would be obvious that maybe something is wrong with the file with the schema file for the people.
I mean the SQL schema file.
And if this went fine, we get access to PDO.
This is the connection returned from connect DB.
So it's also the responsibility of this schema.
PHP script to connect to database.
And the easiest function is exec.
It just runs some SQL code without any additional thought.
So we've loaded the SQL from schema SQL file.
If we couldn't load we just crash.
If we did load, we run it.
And if nothing goes wrong and it would go wrong if there would be any issues.
Then we can safely say at this point.
Schema.
Load it successfully.
I hope I spelled that right.
It's a tough word.
And add a new line.
So it's clear in the terminal that it was a success.
Okay, so I think this function is ready.
Now let me go back to schema PHP where I can run load schema.
I need to pass the PDO.
So I just do connect db and I point to the schema file.
So a quick reminder with this in the db folder.
So we've got this db directory I can use because I'm importing a bootstrap.
This needs a slash and schema SQL.
I think that's the file name.
It creates just a single table.
So we should see this table after we run that script.
Okay.
I think that should be everything.
Now let's run it.
So stop the server if you have it running.
I just opened another tab in the terminal and I run PHP bin schema.
And there is an issue.
We can't open the bootstrap file.
Okay.
So I know what's the problem.
Um, we need to give it an absolute path.
That's why I need to start with this dear constant.
So as a quick reminder in PHP, this would always point to the full path to the current file, this
file schema PHP.
So now I point to the actual bootstrap php file and let me clear the terminal.
Run it again.
Schema loaded successfully.
Well congrats!
Now let me open this file db SQLite.
It's already warning me that this is a binary file.
I'm going to open it anyway, and this looks like a proper SQLite file with some database tables.
Now let's use a database client to see if that's the case.
So as the last step, let's verify if everything went fine.
So let's jump to VSCode extensions and look for SQLite.
By the way you can just select this view from the menu.
The view extensions works the same on windows and I would pick up the second one.
SQLite viewer seems to be simpler and we can have a visual preview.
This one is more for geeks I guess.
So let me install this one SQLite viewer.
Okay, I think we have it now.
Let's just jump back to the directory view and we can just double click this DB.
SQLite.
There it is.
We've got the messages table.
It's empty.
But here you can see the structure.
Everything is according to our schema.
So ID name email message Createdat columns.
So we can confirm that everything went perfectly fine.
Now again if you don't like to use this specific extension, which I can't recommend because I haven't
used it a lot, you can try other database clients like Table Plus is a popular one.
Now, there are probably also a lot of free database clients specific to SQLite or more generic ones.
Anyway.
We can confirm at this point that everything went fine and our database table is created.
So next up we can load some records into this database table.