Maestro is a PHP library designed to facilitate the creation and management of PostgreSQL database schemas and tables. It allows developers to define schemas and tables using PHP classes, providing a clear and structured way to manage database definitions and relationships.
- Introduction
- Installation
- Schema and Table Classes
- Defining Schemas and Tables
- DatabaseManager
- Examples
To install Maestro, use Composer:
composer require ilias/maestro
Maestro uses abstract classes for schemas and tables. Developers extend these classes to define their own schemas and tables.
A schema class extends the Schema
abstract class. It contains table attributes that are typed with the table classes.
A table class extends the Table
abstract class. It can define columns as class properties, specifying their types and optional default values.
You can specify columns that should be unique by overriding the getUniqueColumns
method in your table class.
Columns can have default values. If a default value is a PostgreSQL function, it should be defined as a PostgresFunction
type to ensure it is not quoted in the final SQL query.
The DatabaseManager
class provides methods to create schemas, tables, and manage foreign key constraints.
The createSchema
and createTable
methods generate SQL queries to create schemas and tables. The createTablesForSchema
method handles the creation of all tables within a schema and their foreign key constraints.
Foreign key constraints are added using ALTER TABLE
statements after the tables are created.
The executeQuery
method executes the generated SQL queries using a PDO instance.
<?php
namespace Maestro\Example;
use Ilias\Maestro\Abstract\Schema;
use Ilias\Maestro\Abstract\Table;
use Ilias\Maestro\Interface\PostgresFunction;
use DateTime;
final class Hr extends Schema
{
public User $user;
}
final class User extends Table
{
public Hr $schema;
public string $username;
public string $email;
public string $password;
public DateTime | PostgresFunction | string $createdIn = "CURRENT_TIMESTAMP";
public function __construct(
string $username,
string $email,
string $password,
DateTime $createdIn
) {
$this->username = $username;
$this->email = $email;
$this->password = $password;
$this->createdIn = $createdIn;
}
public static function getUniqueColumns(): array
{
return ["username", "email"];
}
}
Explanations:
final
: Use the final directive declaring yourTable
,Schema
andDatabase
classes. This is the application's way of keeping track of the created entities.type
: Declare all types of class attributes so that the application can better choose the equivalent data type from the database column.__construct
: The construct method is used to define the non-nullability of a database column. Add to the constructor arguments the columns that must not be null.default
: To declare the default value, simply add an initial value to the class attribute.custom function
: To use a postgres function as the default value, follow the previous step and add the following two typings:<current type> | PostgresFunction | string
to the attribute type, then the text added as a value will be used as a function.unique
: To identify unique columns, override the static functiongetUniqueColumns
and return the names of the columns that should be unique.
<?php
require_once 'vendor/autoload.php';
use Ilias\Maestro\Database\DatabaseManager;
use Maestro\Example\Hr;
use Maestro\Example\User;
use PDO;
// Initialize PDO
$pdo = new PDO('pgsql:host=localhost;dbname=testdb', 'username', 'password');
// Initialize DatabaseManager
$dbManager = new DatabaseManager($pdo);
// Create schemas and tables based on the defined classes
$queries = $dbManager->createTablesForSchema(new Hr());
foreach ($queries as $query) {
$dbManager->executeQuery($pdo, $query);
}
Sure! Here are the explanations for the available commands that can be included in the README.md
:
Maestro provides several commands to help you manage and synchronize your database schema. Here are the available commands:
Synchronizes the schema for a specific schema class. This command ensures that the database schema for the specified class matches the schema defined in your PHP code.
./vendor/bin/maestro sync-schema <SchemaClass>
./vendor/bin/maestro sync-schema Maestro\\Example\\Hr
This command will synchronize the Hr
schema, ensuring that the database tables and columns for the Hr
schema match the definitions in your PHP code.
Synchronizes the entire database schema for a specified database class. This command iterates through all the schemas defined in the database class and ensures that each schema in the database matches the schema defined in your PHP code.
./vendor/bin/maestro sync-database <DatabaseClass>
./vendor/bin/maestro sync-database Maestro\\Example\\MaestroDb
This command will synchronize all the schemas defined in the MaestroDb
class, ensuring that the database tables and columns for each schema match the definitions in your PHP code.