README

The pimbay-php/number-sequence-pdo package provides the core PDO-based engine for atomic, persistent number sequence generation and schema management. It supports MySQL, MariaDB, PostgreSQL, and SQLite, ensuring reliable and collision-free sequence operations in high-concurrency environments.

This package serves as the backbone for database-driven number sequencing within the PimBay Sequence Stack, offering a flexible and extensible architecture through its PdoAdapterInterface.

Why not AUTO_INCREMENT?

AUTO_INCREMENT and PostgreSQL SEQUENCE give you a unique number — but they don't give you control over grouping, naming, or initial values.

number-sequence-pdo lets you maintain multiple independent sequences in a single table:

$sequence->nextNumber('invoice', '2026');     // → 1, 2, 3 ...
$sequence->nextNumber('invoice', '2025');     // → independent sequence
$sequence->nextNumber('order', 'proforma');   // → another independent sequence

Each sequence is identified by a (group, name) pair and increments atomically — safe for concurrent requests without application-level locking.

Key Features

• Atomic UPSERT Engine: Implements native SQL UPSERT logic (ON DUPLICATE KEY UPDATE for MySQL/MariaDB, ON CONFLICT for PostgreSQL) for robust, collision-free sequence increments.
• MariaDB RETURNING Adapter: Dedicated MariadbPdoAdapter using native RETURNING clause (MariaDB 10.5+) — cleaner and faster than the MySQL approach.
• Pessimistic Locking: SelectForUpdatePdoAdapter and SqlitePdoAdapter for scenarios requiring explicit transaction-based locking with retry logic.
• Extensible Adapter Architecture: PdoAdapterInterface allows easy integration of custom or third-party database adapters (e.g. MSSQL via pimbay-php/number-sequence-pdo-mssql).
• DriverDetectingPdoAdapter: Automatically selects the appropriate adapter at runtime based on the PDO driver name. Useful for libraries that don't know the database upfront. Throws PdoSequenceException for unsupported drivers — no silent fallback.
• Schema Management: PdoSchemaManager provides database-specific CREATE TABLE SQL for MySQL, MariaDB, PostgreSQL and SQLite, verified 1:1 against pimbay/number-sequence-sql.
• SQL Constants: Every adapter exposes its SQL as a public const — verifiable against the canonical pimbay/number-sequence-sql repository.
• Consistent Error Handling: Wraps underlying PDO exceptions into PdoSequenceException for unified error management.
• Reference Benchmarks: Includes comprehensive benchmarks across MySQL, MariaDB, PostgreSQL, and SQLite — serves as the performance baseline for the entire PimBay Sequence Stack.

Installation

composer require pimbay-php/number-sequence-pdo

Usage

PdoNumberSequence

The PdoNumberSequence is the main entry point for interacting with sequences. You inject a PdoAdapterInterface implementation into its constructor.

<?php

declare(strict_types=1);

use PimBay\NumberSequence\Pdo\PdoNumberSequence;
use PimBay\NumberSequence\Pdo\Adapter\MysqlPdoAdapter;
use PimBay\NumberSequence\Pdo\PdoSchemaManager;

// Assume $pdo is an initialized PDO connection (e.g., MySQL)
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$tableName = 'my_sequences';

// Ensure the sequence table exists
$schemaManager = new PdoSchemaManager($pdo);
$schemaManager->createTable($tableName);

// Create an adapter for MySQL
$mysqlAdapter = new MysqlPdoAdapter($pdo, $tableName);

// Initialize the number sequence service
$numberSequence = new PdoNumberSequence($mysqlAdapter);

// Get the next number for a group and name
$nextInvoiceNumber = $numberSequence->nextNumber('invoice', '2024', 1000, ['department' => 'sales']);
echo "Next Invoice Number: " . $nextInvoiceNumber . "\n"; // → 1000

$nextInvoiceNumber = $numberSequence->nextNumber('invoice', '2024');
echo "Next Invoice Number: " . $nextInvoiceNumber . "\n"; // → 1001

// Get the current value without incrementing
$currentOrder = $numberSequence->getCurrent('invoice', '2024');
echo "Current Invoice Value: " . $currentOrder->currentValue . "\n"; // → 1001

DriverDetectingPdoAdapter (Automatic Adapter Selection)

For libraries that don't know the database upfront, DriverDetectingPdoAdapter automatically selects the correct adapter based on the PDO driver name.

<?php

declare(strict_types=1);

use PimBay\NumberSequence\Pdo\PdoNumberSequence;
use PimBay\NumberSequence\Pdo\Adapter\DriverDetectingPdoAdapter;
use PimBay\NumberSequence\Pdo\PdoSchemaManager;

$pdo = new PDO('pgsql:host=localhost;dbname=test', 'user', 'password');
$tableName = 'my_sequences';

$schemaManager = new PdoSchemaManager($pdo);
$schemaManager->createTable($tableName);

// Automatically picks PostgresPdoAdapter for pgsql driver
$adapter = new DriverDetectingPdoAdapter($pdo, $tableName);
$numberSequence = new PdoNumberSequence($adapter);

$nextNumber = $numberSequence->nextNumber('report', 'daily', 1);
echo "Next Report Number: " . $nextNumber . "\n";

MariaDB and DriverDetectingPdoAdapter

PDO reports mysql as the driver name for both MySQL and MariaDB connections — there is no way to distinguish them without an extra query. For this reason, DriverDetectingPdoAdapter always resolves to MysqlPdoAdapter on MariaDB.

To use the optimized MariadbPdoAdapter (which uses the native RETURNING clause available since MariaDB 10.5+), inject it explicitly:

use PimBay\NumberSequence\Pdo\Adapter\MariadbPdoAdapter;

$adapter = new MariadbPdoAdapter($pdo, $tableName);
$sequence = new PdoNumberSequence($adapter);

PdoSchemaManager

The PdoSchemaManager class helps in setting up the necessary database tables.

<?php

declare(strict_types=1);

use PimBay\NumberSequence\Pdo\PdoSchemaManager;

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$schemaManager = new PdoSchemaManager($pdo);

// Create the default sequence table
$schemaManager->createTable();

// Create a custom-named sequence table
$schemaManager->createTable('my_custom_sequences');

Performance Benchmarks

Benchmarks were run across PHP 8.3, 8.4, and 8.5 with MySQL 5.7, MySQL 8.0, MariaDB 10, MariaDB 11, PostgreSQL 15, and PostgreSQL 16. Each DB + adapter combination runs in an isolated container with a restart between runs to eliminate cache effects.

• MariaDB and PostgreSQL deliver ~2ms Native adapter median at all load levels — fastest and most consistent choices.
• Native adapter outperforms SelectForUpdate by 15–20% across MariaDB and PostgreSQL — single atomic SQL statement vs. full transaction with multiple round-trips.
• MySQL 8.0 is significantly slower — ~8ms vs ~2ms for MariaDB/PostgreSQL, caused by stricter default InnoDB durability settings.
• MySQL 5.7 — ~3.8ms, faster than MySQL 8.0 but slower than MariaDB/PostgreSQL.
• PHP version has no measurable impact — PHP 8.3, 8.4, and 8.5 produce identical results within statistical error margin.
• MariaDB Native (RETURNING) vs MySQL Native (LAST_INSERT_ID) — no measurable difference in Docker environment; TCP overhead dominates.

For production use, MariaDB or PostgreSQL with the Native adapter is the recommended combination.

Full benchmark results and interactive charts

SQL Reference

All SQL used by this library is documented and versioned in the canonical SQL repository:

pimbay/number-sequence-sql

Each adapter exposes its SQL as a public constant for testing and verification:

MysqlPdoAdapter::UPSERT_SQL
PostgresPdoAdapter::UPSERT_SQL
SelectForUpdatePdoAdapter::SELECT_SQL
SelectForUpdatePdoAdapter::INSERT_SQL
SelectForUpdatePdoAdapter::UPDATE_SQL

Test Matrix

Contribution

Contributions are welcome! Please ensure that your code adheres to the existing coding standards and all tests pass. Open an issue or submit a pull request for any new features or bug fixes.

License

Public domain — Unlicense

Created by Jan Sarmir · No conditions · No copyright