README

The pimbay/number-sequence-sql package serves as a centralized repository for database-specific SQL snippets utilized throughout the PimBay Number Sequence Stack. Its primary purpose is to decouple SQL logic from PHP application code in PDO adapters, enhancing maintainability and clarity.

This package provides tested and optimized SQL fragments for performing atomic UPSERT operations, implementing pessimistic locking strategies, and creating the required table schema across various supported database systems, including MySQL, MariaDB, PostgreSQL, MSSQL, and SQLite.

Key Features

Centralized SQL Management: All core SQL logic is managed in one place, making it easier to review, update, and maintain database interactions.
Atomic UPSERT Snippets: Provides INSERT ... ON DUPLICATE KEY UPDATE (MySQL/MariaDB), INSERT ... ON CONFLICT ... (PostgreSQL), and MERGE (MSSQL) SQL for atomic sequence generation.
Pessimistic Locking Snippets: Offers SELECT ... FOR UPDATE (MySQL/MariaDB/PostgreSQL) and SELECT ... WITH (UPDLOCK, ROWLOCK) (MSSQL) SQL fragments for transaction-based, high-concurrency sequence updates.
Table Schema Definitions: Provides CREATE TABLE SQL for all supported databases, ensuring a consistent table structure across environments.
Database Agnostic PHP: Allows PDO adapters (e.g., pimbay-php/number-sequence-pdo) to load and execute these SQL snippets, making the PHP code cleaner and more portable.
Well-Documented: Each SQL snippet category is explained with parameters, behavior notes, and multi-language implementation examples.

Installation

This package is typically a dependency of other PimBay packages (like pimbay-php/number-sequence-pdo) and contains no executable PHP code itself. You can install it via Composer if you need direct access to the SQL files:

composer require pimbay/number-sequence-sql

Usage

The SQL snippets provided by this package are designed to be loaded and used by PDO adapters. The {table} placeholder in every SQL file must be replaced with the actual table name before preparing the statement.

SQL Snippet Categories

sql/upsert/: Contains SQL for creating or updating a sequence value atomically.
- mariadb.sql: MariaDB specific ON DUPLICATE KEY UPDATE (10.3+).
- mariadb-returning.sql: MariaDB optimized UPSERT with RETURNING clause (10.5+).
- mssql.sql: MSSQL specific MERGE statement.
- mysql.sql: MySQL specific ON DUPLICATE KEY UPDATE.
- postgres.sql: PostgreSQL specific ON CONFLICT DO UPDATE.
sql/pessimistic-locking/: Contains SQL for implementing pessimistic locking.
- insert.sql: Generic INSERT for when a sequence does not exist.
- select.sql: Generic SELECT to check for a sequence (SQLite).
- select_for_update.sql: SELECT ... FOR UPDATE for MySQL, MariaDB, PostgreSQL.
- select_with_lock.sql: SELECT ... WITH (UPDLOCK, ROWLOCK) for MSSQL.
- update.sql: Generic UPDATE for an existing sequence — sets updated_at = CURRENT_TIMESTAMP explicitly for compatibility across all databases.
schema/: Contains CREATE TABLE SQL for each supported database.
- mariadb.sql: MariaDB table definition.
- mssql.sql: MSSQL table definition.
- mysql.sql: MySQL table definition.
- postgres.sql: PostgreSQL table definition.
- sqlite.sql: SQLite table definition.

Example (Conceptual PHP usage by an adapter):

<?php

declare(strict_types=1);

class MysqlSequenceAdapter implements SequenceAdapterInterface
{
    private string $upsertSql;

    public function __construct(private \PDO $pdo, private string $tableName)
    {
        $vendorDir = dirname(__DIR__, 3);
        $sqlFile = $vendorDir . '/pimbay/number-sequence-sql/sql/upsert/mysql.sql';
        $this->upsertSql = str_replace('{table}', $this->tableName, file_get_contents($sqlFile));
    }

    public function nextNumber(string $group, string $name, int $initialValue, ?string $metadata): int
    {
        $stmt = $this->pdo->prepare($this->upsertSql);
        $stmt->execute([$group, $name, $initialValue, $metadata]);

        return $stmt->rowCount() === 1
            ? $initialValue
            : (int) $this->pdo->lastInsertId();
    }
}

Contribution

Contributions are welcome. If you have optimized SQL snippets or support for new databases, please open an issue or submit a pull request. Ensure new SQL adheres to best practices and is properly parameterized using ? positional parameters.

License

Public domain — Unlicense

pimbay / number-sequence-sql

Maintainers

Package info

Statistics

Security