Skip to content

Latest commit

 

History

History
119 lines (85 loc) · 5.99 KB

WHY.md

File metadata and controls

119 lines (85 loc) · 5.99 KB
Raw

Why did I write this database access layer ?

I had a dream

Wouldn’t it be nice to ask something like:

  • Give me all the SQL requests or procedures that select this field.
  • Give me all the SQL requests or procedures that select these fields.
  • Give me all the SQL requests or procedures that update these fields.
  • Give me all the SQL requests or procedures that return this quantity (for example: the number of users).
  • Give me all the SQL requests or procedures that are associated with this tag, and that select these fields.
  • ...

Wouldn’t it be nice if your IDE could immediately locate SQL requests or procedures within the entire application's code with 100% accuracy ?

Sure, that would be nice !

You could instantly find where, in your code, a specific action on some data is done.

If you modify your database's schema, you could evaluate very quickly, and very accurately, the impacts on the code.

If you are new to the application, having a very flexible documentary referential should be a great help.

How to make the dream come true?

In order to make your first wish come true, you need to build a relational database that organises all the information about SQL requests and procedures. Once you've injected all the information about SQL requests and procedures into a relational database, then you can use SQL to express your requests for information.

In order to make your second wish come true, you need to identify SQL requests and procedures using PHP constructs that can be identified by your IDE. Constants or classes’ constants are perfectly suited for this use.

In practice:

  • Database entry points (SQL requests and procedures) are (PHP) classes which fully qualified names and paths follow the PSR4 specification.
  • Classes that implement database entry points implement an interface that is used to extract information about the database entry points. This information will be injected into a documentary relational database.
  • The fully qualified name (FQN) of the class that implements a database entry points is a URI.
  • All fully qualified names of the classes that implement database entry points associated to a given database must share a common namespace. Given the previous constraints, this implies that all the file that implements the classes share a common (prefix) path.
  • Rather than using the fully qualified names as URI, we use only the last part of them. Because all database entry points fully qualified names share the same prefix, we use the "variable part" (of the FQN) to identify a database entry point. We call this "variable part" the (unique) "name" of the database entry point.

For example:

We assume that your file composer.json contains the following specification:

  "autoload": {
    "psr-4": {
      "dbeurive\\Application\\": "src"
    }
  },

And that we have the following file tree:

src
├── Procedures
│   └── User
│       ├── Authenticate.php
│       └── Delete.php
└── Sqls
    ├── Profile
    │   └── Get.php
    └── User
        ├── Authenticate.php
        └── Delete.php

Please note that all procedures are stored under the directory src/Procedures. And all SQL request are stored under the directory src/Sqls.

Then, the names for the SQL requests and the procedures are:

File FQN of the class Unique name
src/Procedures/User/Authenticate.php \dbeurive\Application\Procedures\User\Authenticate User\Authenticate
src/Procedures/User/Delete.php \dbeurive\Application\Procedures\User\Delete User\Delete
src/Sqls/Profile/Get.php \dbeurive\Application\Sqls\Profile\Get Profile\Get
src/Sqls/User/Authenticate.php \dbeurive\Application\Sqls\User\Authenticate User\Authenticate
src/Sqls/User/Delete.php \dbeurive\Application\Sqls\User\Delete User\Delete

Please note that, although an SQL request with the same name exists, "User\Authenticate" is a unique name for a procedure.

Then, a script scans all the classes for SQL requests and procedures. As a result, this script produces an SQLite database that organises all information extracted from the scanned classes.

The generated SQLite database is a structured documentary database. And we can use simple SQL to query it.

Your first wish is fulfilled.

And if you define databases entry points' names as constants (or classes’ constants), then you can use your IDE to find any use of an entry point within your entire application, with 100% accuracy.

Using the previous example, we should define the following identifiers:

define('SQL_USER_AUTHENTICATION', 'User\\Authenticate');
define('SQL_USER_DELETE', 'User\\Delete');
define('SQL_PROFILE_GET', 'Profile\\Get');

define('PROCEDURE_USER_AUTHENTICATION', 'User\\Authenticate');
define('PROCEDURE_USER_DELETE', 'User\\Delete');

Or something like:

Class SqlId {
    const SQL_USER_AUTHENTICATION = 'User\\Authenticate';
    const SQL_USER_DELETE = 'User\\Delete';
    const SQL_PROFILE_GET = 'Profile\\Get';
};

Class ProcedureId {
    const PROCEDURE_USER_AUTHENTICATION = 'User\\Authenticate';
    const PROCEDURE_USER_DELETE = 'User\\Delete';
};

Your second wish is fulfilled.