PHP PDO

PDO is an extension of PHP that defines an interface for accessing databases through PHP. It allows to switch the underlying database without having to modify the code itself.

For a school project, me and three other colleagues had to create a website from scratch using PHP. To enhance our developer experience, I created a system that would implement PDO while reflecting the database structure.

PDO Manager

The first step was to be able to communicate with the database via PHP. To achieve this, I had to create a class that would handle basic tasks like connecting to the database and call functions.

The external code would simply need to run the following code in order to directly call procedures and functions:

$a = 10;
$b = "0x0";
PDO_Manager::call_procedure("test", $a, $b, null);

Automatically, the manager would create the query needed, and execute it. This approach allows the callers to ignore the type conversions. The class would automatically convert PHP values into SQL values. For example, it would convert null into NULL, or booleans into 0 or 1.

PDO Object

When we started the project, the main issue was the tight coupling between PHP and SQL. When using PDO, the properties' name would be used as the SQL columns.

class User
{
    public string $idUser = "";
    public string $alias = "";
    public string $name = "";
}

The coupling prevents both sides from applying their naming preferences. PHP would prefer to use $Id, but SQL would prefer to use idUser.

This becomes inviable, specially two different teams are working on either side. As soon as you want to apply a new naming preference, it requires changes across the entire codebase.

To solve this issue, we had to add metadata to the properties to bridge the gap:

class User extends PDO_Object
{
    #[PDO_Object_Id("idUser")]
    public string $Id = "";

    #[PDO_Object_Id("alias")]
    public string $Alias = "";

    #[PDO_Object_Id("name")]
    public string $Name = "";
}

The attribute PDO_Object_id allows to assign the column's name to the property, without worrying about it's name.

It also allows to have type safety that are more understandable than before. Instead of taking a map, functions can require a certain object type:

// Assumes the array has the correct values
function updateUser(array $user): void { /* ... */ }

// Requires the correct argument type
function updateUser(User $user): void { /* ... */ }

Utilities

Whilst adding support for custom queries, I also added helper functions to create them:

// Instead of this:
User::select(
    [User::NAME],
    "ORDER BY " + User::NAME + ", " + User::ALIAS + " LIMIT " + 5 + " OFFSET " + 10
);

// You could do this:
User::select(
    [User::NAME],
    orderByAll(
        [User::NAME],
        [User::ALIAS]
    ),
    limit(5, 10)
);

The methods allow to hide the exact implementation of the query. If the database had to change, these methods could change their inner workings to suit the new database.

Conclusion

This project has taught me how to create an Object-relational mapping from scratch, before even knowing what they were!

This outlines how useful oriented-object can be at handling object related problems.