README

Maatify Persistence

Package status:

Documentation:

Ecosystem and usage:

Standalone, framework-agnostic PDO utilities for Maatify projects, providing robust scoped and global ordering, composable transaction support, and pagination tools. Designed and verified for MySQL environments.

Note: PDO Pagination is available starting with v1.1.0.

🚀 Key Features

Global and Scoped Ordering: Easily manage display order across an entire table or within a specific scope.
Composable PDO Transactions: Owns a transaction when needed and participates in an existing transaction without changing its ownership.
SQL Identifier Validation: Ensures table and column configurations are safe and properly quoted.
Soft-Delete Filtering: Optional support for ignoring soft-deleted rows in ordering calculations.
Scope Isolation: Ensures only the affected range within the configured scope is updated.
PDO Pagination: Deterministic offset pagination with strict normalization, bounds checking, and safe whitelist-based sorting.

⚙️ Requirements

Runtime requirements:

PHP >= 8.2
ext-pdo
maatify/exceptions ^1.0

Database behavior:

The package behavior is designed and verified against MySQL.

📦 Installation

composer require maatify/persistence

⚡ Quick Usage

use Maatify\Persistence\Pdo\Ordering\ScopedOrderingConfig;
use Maatify\Persistence\Pdo\Ordering\ScopedOrderingManager;

// 1. Configure the ordering behavior for a table
$config = new ScopedOrderingConfig(
    table: 'maa_shipping_rates',
    scopeColumn: 'method_id', // Use null for global ordering
    idColumn: 'id',
    orderColumn: 'display_order',
    deletedAtColumn: 'deleted_at', // Use null if soft-deletes are not used
    // Set nullableScope: true when NULL is a real scope (for example, roots).
    nullableScope: false,
    // Optional: update this column atomically with a successful move.
    updatedAtColumn: null,
);

$ordering = new ScopedOrderingManager();

// 2. Get the next position for a new insert
$nextPosition = $ordering->getNextPosition(
    pdo: $pdo,
    config: $config,
    scopeValue: 2, // Use null for global ordering
);

// 3. Move an existing row within its scope
$success = $ordering->moveWithinScope(
    pdo: $pdo,
    config: $config,
    scopeValue: 2, // Use null for global ordering
    id: 15,
    newOrder: 4,
    // Required when updatedAtColumn is configured.
    updatedAtValue: null,
);

Composing PDO Mutations in One Transaction

Use the transaction runner when several mutations must commit or roll back together. Every participant must use the same PDO connection:

use Maatify\Persistence\Pdo\Transaction\PdoTransactionRunner;

$transactions = new PdoTransactionRunner($pdo);

$transactions->run(function () use ($pdo, $ordering, $config): void {
    $pdo->prepare('UPDATE `consumer_table` SET `status` = :status WHERE `id` = :id')
        ->execute(['status' => 'ready', 'id' => 10]);

    $ordering->moveWithinScope($pdo, $config, 2, 15, 4);
});

TransactionRunnerInterface exposes only run(callable $callback), so a consumer service can depend on the shared transaction abstraction without knowing about PDO. PdoTransactionRunner is the PDO implementation and receives the PDO connection through its constructor. When no transaction is active, it starts one, commits on successful callback completion, and rolls back on failure before rethrowing the original Throwable. When a transaction is already active, it participates in that transaction and does not begin, commit, or roll it back. The caller owns the outer transaction in that case.

PDO Pagination

use Maatify\Persistence\Pdo\Pagination\PaginationConfig;
use Maatify\Persistence\Pdo\Pagination\PageRequest;
use Maatify\Persistence\Pdo\Pagination\PdoPaginationQueryDescriptor;
use Maatify\Persistence\Pdo\Pagination\PdoPaginator;
use Maatify\Persistence\Pdo\Pagination\SortWhitelist;
use Maatify\Persistence\Pdo\Pagination\SortDirectionEnum;

$config = new PaginationConfig(
    defaultPerPage: 10,
    maxPerPage: 100,
    minPerPage: 1,
    sortWhitelist: new SortWhitelist([
        'id' => 'id',
        'created' => 'created_at',
        'name' => 'user_name',
    ]),
    defaultSortBy: 'created',
    defaultSortDirection: SortDirectionEnum::DESC,
    tieBreakerSortBy: 'id',
    tieBreakerDirection: SortDirectionEnum::DESC
);

$query = new PdoPaginationQueryDescriptor(
    totalSql: 'SELECT COUNT(*) FROM users',
    totalParams: [],
    filteredCountSql: 'SELECT COUNT(*) FROM users WHERE status = :status',
    filteredCountParams: ['status' => 'active'],
    dataSql: 'SELECT id, user_name, created_at FROM users WHERE status = :status',
    dataParams: ['status' => 'active']
);

$request = new PageRequest(page: 2, perPage: 15, sortBy: 'name', sortDirection: 'ASC');

$paginator = new PdoPaginator();
$result = $paginator->paginate(
    pdo: $pdo,
    query: $query,
    request: $request,
    config: $config,
    mapper: fn(array $row) => (object) $row
);

🧩 Public Runtime API

The package currently provides the following public classes for PDO ordering, transactions, and pagination:

Maatify\Persistence\Pdo\Ordering\ScopedOrderingConfig;
Maatify\Persistence\Pdo\Ordering\ScopedOrderingManager;
Maatify\Persistence\Pdo\Transaction\TransactionRunnerInterface;
Maatify\Persistence\Pdo\Transaction\PdoTransactionRunner;

Maatify\Persistence\Pdo\Pagination\PageRequest;
Maatify\Persistence\Pdo\Pagination\SortDirectionEnum;
Maatify\Persistence\Pdo\Pagination\SortWhitelist;
Maatify\Persistence\Pdo\Pagination\PaginationConfig;
Maatify\Persistence\Pdo\Pagination\PdoPaginationQueryDescriptor;
Maatify\Persistence\Pdo\Pagination\PageResult;
Maatify\Persistence\Pdo\Pagination\PdoPaginator;

// Exceptions
Maatify\Persistence\Exception\PersistenceException;
Maatify\Persistence\Exception\InvalidOrderingConfigurationException;
Maatify\Persistence\Exception\InvalidOrderingOperationException;
Maatify\Persistence\Exception\OrderingTransactionException;
Maatify\Persistence\Exception\InvalidPaginationConfigurationException;
Maatify\Persistence\Exception\InvalidPaginationQueryException;
Maatify\Persistence\Exception\PaginationExecutionException;

OrderingTransactionException remains public and autoloadable for backward compatibility with 1.x consumers, but is deprecated. moveWithinScope() now participates in an active caller-owned PDO transaction and no longer throws it for that condition.

⚠️ Critical Runtime Behavior

getNextPosition():

Does not start a transaction.
Does not lock the applicable scope.
For concurrent inserts, the host application must provide the transaction and locking mechanism required to serialize position allocation.

moveWithinScope():

Rejects inconsistent scope usage.
Rejects id <= 0.
Rejects newOrder <= 0.
Owns a transaction when called without an active PDO transaction.
Participates in an active caller-owned PDO transaction without beginning, committing, or rolling it back.
Locks the applicable active scope using SELECT ... FOR UPDATE.
Supports NULL as a scope value when nullableScope is enabled; this is distinct from global ordering, which has no scopeColumn.
Reads the current order from the database within the same transaction.
Does not trust a current order provided by the caller.
Returns false if the target row is missing.
Clamps values higher than the maximum position to the maximum available position.
Returns true if the movement is a no-op (already at the requested position).
Moves only the affected range.
When updatedAtColumn is configured, updates that column on the target row in the same SQL statement and transaction as the final order update.
Does not globally normalize pre-existing gaps.
Rolls back and returns false if the final target update fails.
Rolls back on any Throwable after starting the transaction.
Rethrows the original Throwable without arbitrary wrapping.

PdoTransactionRunner:

Requires all composed participants to use the same PDO connection.
Starts, commits, and rolls back the transaction when it owns it.
Participates in an existing transaction without changing its ownership.
Preserves the callback return value.
Rethrows the original callback Throwable after attempting to roll back an owned transaction.

rowExistsInScope():

Returns false for id <= 0.
Returns false if the row is not found within the configured scope.
Treats soft-deleted rows as non-existent if deletedAtColumn is configured.
Throws InvalidOrderingOperationException on invalid scope usage.
External PDO errors propagate unmodified.

PDO Pagination:

Normalizes page and per-page limits strictly.
Uses safe whitelist-based sorting.
Host application owns SQL, scopes, mapping, and filters.
Package handles count queries and offset calculation.
Does not alter or require active PDO transactions.

🏛️ Architecture Guarantees

Standalone Composer package.
Framework-agnostic.
Host-agnostic.
PDO-based.
No ORM.
No framework bindings.
No HTTP endpoints, UI, controllers, or routes.
No host table ownership.
No generic application repository abstraction.
The host provides the PDO connection.
The caller owns the outer transaction when composing ordinary PDO mutations with Ordering mutations.
Trusted SQL identifiers.
Runtime values use prepared statements.

🛡️ Exception and Error Propagation

All package-defined exceptions implement the marker interface Maatify\Persistence\Exception\PersistenceException. However, this interface is not a catch-all. PDOException or other external Throwables may propagate without wrapping and require a separate catch or an outer Throwable boundary if handling is needed.

🔐 Security and Trust Boundaries

The ScopedOrderingConfig validates and quotes all configured table and column identifiers. However, these identifiers must still be provided as trusted application configurations (e.g., constants), never as raw user input. All actual runtime values are safely passed using PDO prepared statements.

📚 Documentation

For a comprehensive guide, please refer to the main technical reference:

Persistence Package Reference

✅ Quality Status

PHP 8.2–8.5 verification in CI.
PHPStan Level Max.
Unit, Regression, and MySQL Integration tests.
Lowest dependencies verification.
Stable CI Gate.

Integration testing:

Real MySQL is required for Integration tests.
SQLite is not an Integration substitute.
MySQL 8.4.10 is the currently verified CI baseline.

🛠️ Development and Testing

composer validate --strict
composer analyse
composer test:unit
composer test:regression
vendor/bin/php-cs-fixer fix --dry-run --diff

composer test:integration and composer test require a real MySQL database. SQLite is explicitly not an integration substitute.

Set the following environment variables for Integration tests:

PERSISTENCE_TEST_MYSQL_DSN
PERSISTENCE_TEST_MYSQL_USER
PERSISTENCE_TEST_MYSQL_PASSWORD

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

👤 Author

Engineered by Mohamed Abdulalim (@megyptm)
Backend Lead & Technical Architect
https://www.maatify.dev

Built with ❤️ by Maatify.dev — Unified Ecosystem for Modern PHP Libraries

maatify / persistence

Package info

Security

Statistics