Skip to content

v2.5.1

Choose a tag to compare

View all tags
@tommyknocker tommyknocker released this 22 Oct 02:27
· 455 commits to master since this release
v2.5.1
1aef66f

🚀 Release v2.5.1 - UPSERT Improvements & QueryBuilder API Migration

This release brings critical bug fixes for groupBy()/orderBy() with qualified column names, adds Db::inc()/Db::dec() support to UPSERT operations, and migrates all examples to QueryBuilder API for consistency and best practices demonstration.

🎯 Highlights

  • ✅ 343 tests, 1544 assertions - all passing
  • ✅ 21 examples working on all 3 database dialects (SQLite, MySQL, PostgreSQL)
  • ✅ PHPStan Level 8 - zero errors
  • ✅ CI testing for all examples added to GitHub Actions

🆕 New Features

Db::inc() / Db::dec() in onDuplicate()

Now you can use convenient helpers for UPSERT increments:

$db->find()->table('user_stats')
    ->onDuplicate([
        'login_count' => Db::inc(1),      // Increment old value
        'points' => Db::dec(10)            // Decrement old value
    ])
    ->insert(['user_id' => 1, 'login_count' => 1, 'points' => 100]);

Works seamlessly across all dialects:

  • MySQL: counter = counter + 5
  • PostgreSQL: "counter" = "user_stats"."counter" + 5
  • SQLite: "counter" = "counter" + 5

CI Testing for Examples

All 21 examples now automatically tested in GitHub Actions on available databases.

🐛 Critical Bug Fixes

1. groupBy() / orderBy() with Qualified Column Names

Bug: Qualified names like u.id were quoted as `u.id` (single identifier) instead of `u`.`id` (table.column).

Impact: Broke on MySQL/PostgreSQL with error: Unknown column 'u.id' in 'group statement'

Example that was broken:

$db->find()
    ->from('users AS u')
    ->leftJoin('orders AS o', 'o.user_id = u.id')
    ->select(['u.name', 'total' => Db::sum('o.amount')])
    ->groupBy(['u.id', 'u.name'])  // ❌ Was broken
    ->get();

Fix: Changed groupBy() and orderBy() to use quoteQualifiedIdentifier().

Test: Added testGroupByWithQualifiedNames() to verify fix across all 3 dialects.

2. Db::inc() / Db::dec() Ignored in onDuplicate()

Bug: Db::inc(5) was ignored, value replaced instead of incremented.

Impact: UPSERT didn't work as expected - counters were reset instead of incremented.

Fix: Added ['__op' => 'inc'] array handling in all dialect's buildUpsertClause().

Test: Added testUpsertWithIncHelper() for all 3 dialects.

3. PostgreSQL UPSERT "Ambiguous Column" Errors

Bug: Db::raw('age + 5') in onDuplicate() caused PostgreSQL error: "column reference 'age' is ambiguous".

Impact: PostgreSQL couldn't distinguish between old table value vs new excluded value.

Fix: Auto-qualify column references with table name.

Test: testUpsertWithRawIncrement() verifies both Db::inc() and Db::raw() work correctly.

4. PostgreSQL lastInsertId() Exception

Bug: Crash when inserting into tables without auto-increment (SERIAL) columns.

Fix: Added try-catch in executeInsert() to gracefully handle missing sequence.

📝 Examples Migration to QueryBuilder API

All 21 examples refactored to use QueryBuilder fluent API instead of raw SQL:

Before (v2.5.0):

$count = $db->rawQueryValue('SELECT COUNT(*) FROM users WHERE is_active = 1');
$db->rawQuery("UPDATE users SET age = age + 1 WHERE id = 5");

After (v2.5.1):

$count = $db->find()->from('users')->select([Db::count()])->where('is_active', 1)->getValue();
$db->find()->table('users')->where('id', 5)->update(['age' => Db::inc(1)]);

rawQuery() now used ONLY for:

  • ✅ CREATE TABLE
  • ✅ ALTER TABLE
  • ✅ DROP TABLE
  • ✅ SET statements (e.g., FOREIGN_KEY_CHECKS)

🔧 API Changes (Backwards Compatible)

Enhanced buildUpsertClause() signature across all dialects:

public function buildUpsertClause(
    array $updateColumns,
    string $defaultConflictTarget = 'id',
    string $tableName = ''  // NEW: optional, for PostgreSQL
): string;

Breaking changes: NONE - optional parameter with default value maintains full backwards compatibility.

📊 Testing Results

✅ PHPUnit:  343 tests, 1544 assertions (0 failures, 0 errors)
✅ Examples: 61/61 runs passed
   - SQLite:     21/21
   - MySQL:      20/20
   - PostgreSQL: 20/20
✅ PHPStan:  Level 8, 0 errors

📦 Files Changed

Source (5 files):

  • src/query/QueryBuilder.php - qualified identifier handling, executeInsert() fix
  • src/dialects/DialectInterface.php - updated interface signature
  • src/dialects/MySQLDialect.php - Db::inc()/Db::dec() support
  • src/dialects/PostgreSQLDialect.php - Db::inc()/Db::dec() + table qualification
  • src/dialects/SqliteDialect.php - Db::inc()/Db::dec() support

Examples (13 files): All migrated to QueryBuilder API

Tests (3 files): Added 9 new tests total

CI (1 file): .github/workflows/tests.yml - added example testing

🔄 Upgrade Path

This release is fully backwards compatible. Simply update:

composer update tommyknocker/pdo-database-class

No code changes required! But you can now:

  • Use Db::inc()/Db::dec() in onDuplicate()
  • Use qualified column names in groupBy()/orderBy() without Db::raw()

🙏 Contributors

Special thanks to the community for reporting issues and testing across different database dialects!

Full Changelog: v2.5.0...v2.5.1

Assets 2
Loading