pimbay / sequence-random-sql
Canonical SQL schema and INSERT IGNORE statements for random sequence operations — MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, Oracle, DB2.
Maintainers
README
Canonical SQL schema and INSERT IGNORE statements for random sequence operations across MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, Oracle and IBM DB2. Part of the PimBay Sequence Stack.
Purpose
Decouples SQL from adapter code. Adapters load snippets via file_get_contents(), replace the {table} placeholder, bind ? positional parameters and execute. No PHP code — SQL and documentation only.
Installation
composer require pimbay/sequence-random-sql
SQL Snippets
INSERT IGNORE (sql/code/)
Collision-safe code insertion. The UNIQUE constraint (group_id, code) guarantees uniqueness at group level — no application-level locking required.
| File | Database | Strategy | Params |
|---|---|---|---|
insert_ignore_mysql.sql | MySQL | INSERT IGNORE | 4 |
insert_ignore_mariadb.sql | MariaDB | INSERT IGNORE | 4 |
insert_ignore_postgres.sql | PostgreSQL | ON CONFLICT (group_id, code) DO NOTHING | 4 |
insert_ignore_sqlite.sql | SQLite | INSERT OR IGNORE | 4 |
insert_ignore_mssql.sql | MSSQL | INSERT ... SELECT WHERE NOT EXISTS | 6 |
insert_ignore_oracle.sql | Oracle | INSERT ... SELECT FROM dual WHERE NOT EXISTS | 6 |
insert_ignore_db2.sql | IBM DB2 | INSERT ... SELECT FROM sysibm.sysdummy1 WHERE NOT EXISTS | 6 |
Parameters for MySQL, MariaDB, PostgreSQL, SQLite: [group_id, sequence_id, code, metadata]
Parameters for MSSQL, Oracle, DB2: [group_id, sequence_id, code, metadata, group_id, code] — the last two repeat for the WHERE NOT EXISTS subquery.
Schema (schema/)
CREATE TABLE definitions for all three tables ({table}_group, {table}, {table}_code) per database. Foreign key constraint names include the {table} placeholder — e.g. fk_{table}_group, fk_{table}_code_group, fk_{table}_code_sequence — so multiple table sets with different names can coexist in the same database. UNIQUE constraints are named the same way — uq_{table}_group_name, uq_{table}_name, uq_{table}_code. SQLite uses anonymous inline FK constraints and is unaffected.
Collision detection
rowCount() after INSERT signals the outcome — identically across all databases:
rowCount | Meaning |
|---|---|
1 | Code inserted — unique within the group |
0 | Collision — code already exists in this group |
No lock, no transaction. Two concurrent processes may generate the same candidate code simultaneously — only one INSERT will succeed. The other detects the collision via rowCount() === 0 and retries.
Code format and storage
Codes are stored exactly as generated — including any separators, prefixes, or suffixes produced by the pattern. A code generated from pattern {R3}-{R3} is stored as ABC-123, not ABC123. The separator is part of the code, not a display formatting layer.
The UNIQUE(group_id, code) constraint operates on the stored value. Two codes that differ only in separators — ABC123 and ABC-123 — are treated as distinct codes.
When accepting codes from user input, normalize before lookup: strip or preserve separators consistently on both the generation and validation side.
Usage
$sql = file_get_contents(__DIR__ . '/vendor/pimbay/sequence-random-sql/sql/code/insert_ignore_mysql.sql');
$sql = str_replace('{table}', 'pimbay_sequence_random', $sql);
$stmt = $pdo->prepare($sql);
$stmt->execute([$groupId, $sequenceId, $code, $metadata]);
$inserted = $stmt->rowCount() === 1;
The {table} placeholder must always be replaced before prepare(). Never pass table names from user input.
Real-world example — e-commerce cart discount codes
Consider an e-commerce system with three types of discount codes sharing the same cart validation:
Group: ecommerce_cart
ecommerce_cart (group)
├── coupon — manually created fixed codes, e.g. VACATION25, SUMMER26
├── coupon_competitions — generated codes for competitions, limited to one use each
└── wallet_voucher — auto-generated codes from user wallet conversions
Each sequence has its own generator configuration — coupon stores no generator (codes are inserted manually via insertCode), coupon_competitions uses a 7-character alphanumeric generator, wallet_voucher uses a fixed-value voucher generator.
Discount rules are applied at group level, not per code — a rule references ecommerce_cart and applies to any valid code within the group, regardless of which sequence created it. This avoids maintaining 1000 individual code rules when a batch of competition codes is generated.
Why group-level uniqueness matters: a code like SUMMER26 must not appear in both coupon and wallet_voucher. The UNIQUE(group_id, code) constraint in {table}_code guarantees this automatically — an attempt to insert a duplicate code in any sequence within the group will return rowCount() === 0.
Why sequence_id in {table}_code: knowing that code ABC123 was created by wallet_voucher (not coupon_competitions) is essential for audit, analytics and support. Without sequence_id, you only know that a code exists in the group — not how it was created or which generator produced it.
Documentation
- Schema — table structure, relationships, column types
- Flow — internal implementation flow for all
RandomSequenceInterfacemethods - INSERT — MySQL
- INSERT — MariaDB
- INSERT — PostgreSQL
- INSERT — SQLite
- INSERT — MSSQL
- INSERT — Oracle
- INSERT — IBM DB2
License
Public domain — Unlicense
Created by Jan Sarmir · No conditions · No copyright