v2.7.1

🚀 Release v2.7.1 - Major Query Builder Enhancements

Overview

v2.7.1 introduces five major query builder features with full cross-database support, comprehensive testing, and extensive documentation. This release significantly expands the library's capabilities for complex SQL queries and advanced analytics.

✨ New Features

📊 Window Functions Support

Advanced analytics with SQL window functions - perfect for rankings, leaderboards, and time-series analysis.

Ranking Functions:

• Db::rowNumber() - Sequential row numbering
• Db::rank() - Rank with gaps for ties
• Db::denseRank() - Rank without gaps
• Db::ntile() - Divide rows into buckets

Value Access Functions:

• Db::lag() - Access previous row values
• Db::lead() - Access next row values
• Db::firstValue() - First value in window
• Db::lastValue() - Last value in window
• Db::nthValue() - Nth value in window

Window Aggregates:

• Db::windowAggregate() - Running totals, moving averages

Features:

• Support for PARTITION BY, ORDER BY, and frame clauses (ROWS BETWEEN)
• Cross-database support (MySQL 8.0+, PostgreSQL 9.4+, SQLite 3.25+)
• 11 helper methods in WindowHelpersTrait
• Complete documentation (900+ lines)
• 10 comprehensive use cases in examples

Documentation: documentation/03-query-builder/window-functions.md
Examples: examples/16-window-functions/

---

🔗 Common Table Expressions (CTEs)

WITH clauses for complex queries and recursive data processing.

Features:

• QueryBuilder::with() - Basic CTEs
• QueryBuilder::withRecursive() - Recursive CTEs for hierarchical data
• Support for Closure, QueryBuilder, or raw SQL
• Multiple CTEs with unique parameter scoping
• Explicit column lists for recursive CTEs
• Cross-database support (MySQL 8.0+, PostgreSQL 8.4+, SQLite 3.8.3+)

Use Cases:

• Tree structures (categories, comments, file systems)
• Organizational charts
• Recursive calculations
• Query organization and readability

Documentation: documentation/03-query-builder/cte.md
Examples: examples/17-cte/

---

🔀 Set Operations

UNION, INTERSECT, and EXCEPT operations for combining query results.

Features:

• QueryBuilder::union() - Combine results (removes duplicates)
• QueryBuilder::unionAll() - Combine results (keeps duplicates)
• QueryBuilder::intersect() - Common rows in both queries
• QueryBuilder::except() - Rows in first but not in second
• Support for Closure and QueryBuilder instances
• Proper ORDER BY/LIMIT/OFFSET placement (SQL standard compliance)
• Cross-database: MySQL 8.0+, PostgreSQL, SQLite 3.8.3+

Documentation: documentation/03-query-builder/set-operations.md
Examples: examples/18-set-operations/

---

🔍 DISTINCT and DISTINCT ON

Remove duplicates from result sets with advanced control.

Features:

• QueryBuilder::distinct() - Remove duplicates (all databases)
• QueryBuilder::distinctOn() - PostgreSQL-specific distinct on columns
• Runtime dialect validation with clear exceptions
• DialectInterface::supportsDistinctOn() for feature detection

Documentation: documentation/03-query-builder/distinct.md
Examples: Extended examples/01-basic/05-ordering.php

---

🎯 FILTER Clause for Conditional Aggregates

SQL:2003 standard compliance with automatic dialect translation.

Features:

• filter() method chainable after all aggregate functions
• Db::count()->filter(...), Db::sum()->filter(...), etc.
• Native FILTER (WHERE ...) clause for PostgreSQL and SQLite 3.30+
• Automatic CASE WHEN fallback for MySQL
• DialectInterface::supportsFilterClause() for feature detection

Example:

$db->find()
    ->from('orders')
    ->select([
        'total_orders' => Db::count('*'),
        'paid_orders' => Db::count('*')->filter('status', 'paid'),
        'pending_amount' => Db::sum('amount')->filter('status', 'pending'),
    ])
    ->groupBy('user_id')
    ->get();

Documentation: documentation/03-query-builder/filter-clause.md
Examples: Extended examples/02-intermediate/02-aggregations.php

---

🧪 Testing

20 new edge-case tests covering critical scenarios:

• Empty result sets
• NULL value handling
• Boundary conditions
• Unsupported feature detection

Total Coverage:

• ✅ 574 tests with 2526 assertions
• ✅ All tests passing on MySQL, PostgreSQL, SQLite
• ✅ 108 examples (36 files × 3 dialects)

---

📚 Documentation

Added 6 new comprehensive documentation files (2,305+ lines):

• Window Functions (923 lines)
• CTEs (409 lines)
• Set Operations (204 lines)
• DISTINCT (320 lines)
• FILTER Clause (349 lines)
• Window Helpers Reference (550 lines)

---

🔧 Changes & Fixes

Changed

• Aggregate helpers now return FilterValue instead of RawValue to support filter() chaining
• SelectQueryBuilderInterface extended with setUnions(), setDistinct(), setDistinctOn()
• Enhanced parameter management for UNION subqueries

Fixed

• MySQL recursive CTE string concatenation: Fixed test failure using CONCAT() instead of || operator
• All examples now work correctly on all three database dialects

---

📊 Statistics

• 39 files changed
• 6,507 insertions, 80 deletions
• PHPStan Level 9 (upgraded from Level 8)
• 100% backward compatible - no breaking changes

---

🔗 Links

• Full Changelog: CHANGELOG.md
• Documentation: documentation/
• Examples: examples/
• Comparison: v2.7.0...v2.7.1

---

📦 Installation

composer require tommyknocker/pdo-database-class:^2.7.1

---

Enjoy building powerful database queries! 🎉

v2.7.0

🎉 Release v2.7.0 - Major Feature Update

We're excited to announce version 2.7.0 - our biggest release yet! This version adds 8 major new features, 57 comprehensive documentation files, and significant performance improvements.

🚀 What's New

🔍 Full-Text Search

Cross-database full-text search with unified API across MySQL, PostgreSQL, and SQLite:

$results = $db->find()
    ->from('articles')
    ->where(Db::fulltextMatch(['title', 'content'], 'search term'))
    ->get();

⚡ Query Result Caching (PSR-16)

10-1000x faster repeated queries with built-in caching support:

$db->enableCache($cache);

// First call hits database, subsequent calls use cache
$users = $db->find()->from('users')->cache(300)->get();

📄 Advanced Pagination

Three pagination types for any use case:

• Full pagination - Complete with page numbers and totals
• Simple pagination - Fast, no COUNT overhead
• Cursor-based - Perfect for real-time feeds and infinite scroll

$result = $db->find()->from('posts')->paginate(1, 20);
// Returns: items, total, currentPage, lastPage, perPage

🔄 Read/Write Connection Splitting

Master-replica architecture with automatic query routing and load balancing:

$db->enableReadWriteSplitting(new RoundRobinLoadBalancer());
$db->addConnection('write', ['host' => 'master.db']);
$db->addConnection('read-1', ['host' => 'replica1.db', 'type' => 'read']);
$db->addConnection('read-2', ['host' => 'replica2.db', 'type' => 'read']);

// Automatically routes to replicas
$users = $db->find()->from('users')->get();

📊 Schema Introspection

Query your database structure programmatically:

$indexes = $db->indexes('users');
$foreignKeys = $db->keys('orders');
$constraints = $db->constraints('products');

📤 Export Helpers

Export results to JSON, CSV, or XML with ease:

$users = $db->find()->from('users')->get();
$json = Db::toJson($users, JSON_PRETTY_PRINT);
$csv = Db::toCsv($users);
$xml = Db::toXml($users, 'users', 'user');

📥 JSON File Loading

Bulk load JSON data directly into tables:

$db->find()->table('products')
    ->loadJson('products.json', update: true);

🔀 Enhanced orderBy()

Multiple new syntax options for flexible sorting:

// Array with directions
->orderBy(['name' => 'ASC', 'created_at' => 'DESC'])

// Comma-separated string
->orderBy('name ASC, email DESC, id')

// Array with default direction
->orderBy(['name', 'email'], 'DESC')

📚 Documentation

57 comprehensive documentation files (~12,600 lines) covering:

• Getting Started & Core Concepts
• Query Builder & JSON Operations
• Advanced Features (Caching, Pagination, Read/Write Splitting, Batch Processing)
• Error Handling & Helper Functions
• Best Practices & Security
• Complete API Reference
• Real-world Examples & Troubleshooting

📖 Browse Documentation

🎯 Examples

93 working examples (31 files × 3 dialects):

• All examples tested and passing on MySQL, PostgreSQL, and SQLite
• New examples for all features added in this release
• README files in all example directories

🔗 View Examples

🔧 Technical Details

• ✅ 533 tests passing (+55 new), 2,397 assertions
• ✅ 93/93 examples passing on all dialects
• ✅ PHPStan Level 8 - Zero errors
• ✅ PSR-12 compliant (php-cs-fixer)
• ✅ 100% backward compatible - No breaking changes
• ✅ SOLID principles - KISS, DRY, YAGNI

📈 Performance Improvements

• Memory-efficient generators for CSV/XML loading (handles files larger than RAM)
• Query caching support for 10-1000x speedup on repeated queries
• Optimized connection handling for read/write splitting

🔄 Changed

• Refactored file loading to use PHP generators (dramatically reduced memory usage)
• Improved code architecture (better extensibility with protected visibility)
• Enhanced test suite (best practices, no direct connection access)
• Restructured README with detailed feature descriptions

🐛 Fixed

• Cross-dialect compatibility in exception handling examples
• Invalid release date in CHANGELOG for v2.6.1

📦 Installation

composer require tommyknocker/pdo-database-class:^2.7

🔗 Links

• 📖 Full Changelog
• 📚 Documentation
• 🎯 Examples
• 🐛 Report Issues

🙏 Thank You

Thank you to everyone using this library! Your feedback and support drive continuous improvement.

---

Full Changelog: v2.6.2...v2.7.0

v2.6.2

Release v2.6.2 - Major Architectural Refactoring

Release Date: October 25, 2025
Version: 2.6.2
Type: Minor Release (Backward Compatible)

🎉 What's New

Comprehensive Examples Expansion

• 5 new comprehensive example files demonstrating all library functionality:
  • 05-comparison-helpers.php - LIKE, BETWEEN, IN, NOT operations with practical scenarios
  • 06-conditional-helpers.php - CASE statements with complex conditional logic
  • 07-boolean-helpers.php - TRUE/FALSE/DEFAULT values and boolean operations
  • 08-type-helpers.php - CAST, GREATEST, LEAST type conversion and comparison functions
  • 04-subqueries.php - Complete subquery examples (EXISTS, NOT EXISTS, scalar subqueries)
• Enhanced existing examples with real-world scenarios and better educational value
• Complete CONTRIBUTING.md (413 lines) with comprehensive development guidelines

Major Architectural Improvements

This release represents a significant internal refactoring following SOLID principles:

🏗️ Connection Architecture

• Extracted ConnectionLogger, ConnectionState, DialectRegistry, RetryConfigValidator
• Improved separation of concerns and testability
• Better error handling with consistent exception processing

🎯 Strategy Pattern for Exception Handling

• Replaced monolithic ExceptionFactory with 7 specialized strategies:
  • ConstraintViolationStrategy (priority 10)
  • AuthenticationStrategy (priority 20)
  • TimeoutStrategy (priority 30)
  • TransactionStrategy (priority 50)
  • ConnectionStrategy (priority 60)
  • ResourceStrategy (priority 70)
  • QueryStrategy (priority 1000 - fallback)

🔧 Dialect Refactoring

• Extracted common functionality into reusable traits:
  • JsonPathBuilderTrait - eliminates JSON path building duplication
  • UpsertBuilderTrait - common upsert operations with dialect-specific implementations
  • FileLoader - CSV/XML loading logic extraction
• Created utility classes: ParameterManager, ConstraintParser, ErrorCodeRegistry

🧩 Helper System Refactoring

• Converted all helper classes to trait-based organization
• Moved helpers to src/helpers/traits/ directory by functionality
• Split DbError into dialect-specific traits (MysqlErrorTrait, PostgresqlErrorTrait, SqliteErrorTrait)

🔨 QueryBuilder Refactoring

• Split monolithic QueryBuilder into focused components with proper interfaces
• Moved query interfaces to src/query/interfaces/ directory
• Extracted common functionality into reusable traits:
  • CommonDependenciesTrait
  • ExternalReferenceProcessingTrait
  • IdentifierQuotingTrait
  • RawValueResolutionTrait
  • TableManagementTrait

🚀 Technical Improvements

Code Quality

• Eliminated ~1000+ lines of duplicate code through trait extraction
• PHPStan Level 8 compliance - zero errors across entire codebase
• Enhanced type safety with proper PHPDoc annotations and null safety checks
• Improved maintainability through SOLID principles and better organization

Cross-Database Compatibility

• Fixed all examples to work consistently on SQLite, MySQL, and PostgreSQL
• Enhanced examples replaced Db::raw() calls with helper functions where possible
• Better educational value with practical, real-world scenarios

📊 Quality Metrics

• ✅ 478 tests - all passing (2198 assertions)
• ✅ 90 examples - all working across all dialects (30 files × 3 dialects each)
• ✅ PHPStan Level 8 - zero errors
• ✅ 100% backward compatibility - no breaking changes to public API
• ✅ Performance improved through reduced code duplication and better architecture

🔄 Migration Guide

No migration required! This release maintains 100% backward compatibility. All existing code will continue to work unchanged.

🎯 What This Means for You

For Users

• Better performance through optimized architecture
• More comprehensive examples for learning and reference
• Improved reliability through better error handling
• Enhanced documentation with complete development guidelines

For Contributors

• Cleaner codebase following SOLID principles
• Better testability with smaller, focused components
• Easier maintenance through trait-based organization
• Comprehensive guidelines in CONTRIBUTING.md

🏷️ Full Changelog

See CHANGELOG.md for complete details of all changes since v2.6.1.

🔗 Links

• GitHub Release: v2.6.2
• Documentation: README.md
• Examples: examples/
• Contributing: CONTRIBUTING.md

---

Thank you for using PdoDb! This release represents a significant step forward in code quality and maintainability while preserving the simplicity and power that makes PdoDb great.

Happy coding! 🚀

v2.6.1

Release v2.6.1: Automatic External Reference Detection 🎯

🚀 What's New

This release introduces automatic external reference detection in subqueries, making complex queries much more intuitive to write. No more manual Db::raw() wrapping for external table references!

✨ Key Features

Automatic External Reference Detection

QueryBuilder now automatically detects and converts external table references (table.column) to RawValue objects:

• Works in: where(), select(), orderBy(), groupBy(), having() methods
• Smart detection: Only converts references to tables not in current query's FROM clause
• Alias support: Works with table aliases (e.g., u.id where u is an alias)
• Pattern matching: Detects table.column or alias.column patterns
• Safe processing: Invalid patterns (like 123.invalid) are not converted

New Helper: Db::ref()

Added Db::ref() helper for manual external references (though now mostly unnecessary):

Db::ref('users.id')  // Equivalent to Db::raw('users.id')

📝 Examples

Before (v2.6.0)

$users = $db->find()
    ->from('users')
    ->whereExists(function($query) {
        $query->from('orders')
            ->where('user_id', Db::raw('users.id'))  // Manual wrapping required
            ->where('status', 'completed');
    })
    ->get();

After (v2.6.1)

$users = $db->find()
    ->from('users')
    ->whereExists(function($query) {
        $query->from('orders')
            ->where('user_id', 'users.id')  // ✨ Automatic detection!
            ->where('status', 'completed');
    })
    ->get();

More Examples

SELECT with external references:

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'total_orders' => 'COUNT(orders.id)',     // Auto-detected
        'last_order' => 'MAX(orders.created_at)' // Auto-detected
    ])
    ->leftJoin('orders', 'orders.user_id = users.id')
    ->groupBy('users.id', 'users.name')
    ->get();

ORDER BY with external references:

$users = $db->find()
    ->from('users')
    ->select(['users.id', 'users.name', 'total' => 'SUM(orders.amount)'])
    ->leftJoin('orders', 'orders.user_id = users.id')
    ->groupBy('users.id', 'users.name')
    ->orderBy('total', 'DESC')  // Auto-detected
    ->get();

With table aliases:

$users = $db->find()
    ->from('users AS u')
    ->whereExists(function($query) {
        $query->from('orders AS o')
            ->where('o.user_id', 'u.id')  // Auto-detected with aliases
            ->where('o.status', 'completed');
    })
    ->get();

🧪 Test Coverage

• 39 new tests across all dialects (MySQL, PostgreSQL, SQLite)
• 13 tests per dialect covering all scenarios:
  • whereExists and whereNotExists with external references
  • select expressions with external references
  • orderBy and groupBy with external references
  • having clauses with external references
  • Internal references (not converted)
  • Aliased table references
  • Complex external references
  • Edge cases and invalid patterns

🔧 Technical Details

• All tests passing: 429+ tests, 2044+ assertions
• PHPStan Level 8: Zero errors across entire codebase
• All examples passing: 24/24 examples on all database dialects
• Backward compatibility: Fully maintained - existing code continues to work unchanged
• Performance: Minimal overhead - only processes string values matching table.column pattern

🎯 Detection Rules

• Pattern: table.column or alias.column
• Scope: Only converts if the table/alias is not in the current query's FROM clause
• Methods: Works in where(), select(), orderBy(), groupBy(), having()
• Safety: Internal references (tables in current query) are not converted
• Validation: Invalid patterns (like 123.invalid) are not converted

📚 Documentation Updates

• Updated README.md with comprehensive examples
• Added new "Automatic External Reference Detection" section
• Updated subquery examples to demonstrate automatic detection
• Added Db::ref() to helper functions reference

🔄 Migration Guide

No migration required! This is a fully backward-compatible release. Existing code continues to work unchanged.

If you want to take advantage of the new automatic detection, simply remove Db::raw() wrappers around external table references:

// Old way (still works)
->where('user_id', Db::raw('users.id'))

// New way (automatic detection)
->where('user_id', 'users.id')

🎉 Benefits

1. Cleaner code: No more Db::raw() wrappers for simple external references
2. Better readability: Natural SQL syntax in subqueries
3. Reduced errors: Automatic detection prevents common mistakes
4. Consistent behavior: Works the same across all database dialects
5. Zero breaking changes: Existing code continues to work

---

Full Changelog: v2.6.0...v2.6.1

Installation: composer require tommyknocker/pdo-database-class:^2.6.1

v2.6.0

🚀 Release v2.6.0: Comprehensive Exception Handling System

Release Date: October 23, 2025
Type: Major Release (Breaking Changes)

🎯 Overview

This major release introduces a comprehensive exception handling system that replaces generic PDOException with specialized exception types, providing better error handling capabilities and improved debugging experience.

⚠️ Breaking Changes

Exception Handling System

• loadCsv() and loadXml() now throw specialized exceptions instead of returning false
• All database operations now throw custom DatabaseException hierarchy instead of PDOException

Migration Guide

// Before (v2.5.x)
$result = $db->find()->table('users')->loadCsv('/path/file.csv');
if (!$result) {
    // Handle error
}

// After (v2.6.0)
try {
    $db->find()->table('users')->loadCsv('/path/file.csv');
} catch (DatabaseException $e) {
    // Handle specific exception type
}

✨ New Features

🎯 Comprehensive Exception Hierarchy

• DatabaseException - Base exception class extending PDOException
• ConnectionException - Connection-related errors (retryable)
• QueryException - Query execution errors
• ConstraintViolationException - Constraint violations with detailed context
• TransactionException - Transaction errors (deadlocks, etc.)
• AuthenticationException - Authentication failures
• TimeoutException - Query/connection timeouts
• ResourceException - Resource exhaustion errors

🏭 ExceptionFactory

• Automatic exception type detection based on error codes and messages
• Cross-dialect support for MySQL, PostgreSQL, and SQLite
• Enhanced error context with driver, query, and retryable status

📊 Enhanced Error Context

catch (ConstraintViolationException $e) {
    echo "Constraint: " . $e->getConstraintName();
    echo "Table: " . $e->getTableName();
    echo "Column: " . $e->getColumnName();
    echo "Retryable: " . ($e->isRetryable() ? 'Yes' : 'No');
}

🔧 Improved Error Codes

• DbError class with standardized error codes for all dialects
• Helper methods for retryable error detection
• Human-readable descriptions for error codes

🛠️ Technical Improvements

📝 Documentation & Examples

• Comprehensive error handling examples in examples/09-exception-handling/
• Enhanced README examples with error handling demonstrations
• Migration guide for breaking changes
• Exception handling best practices

🧪 Testing & Quality

• 429 tests passing across all dialects
• 24 examples working on MySQL, PostgreSQL, and SQLite
• PHPStan level 8 compliance with PHPDoc annotations
• test-examples script integrated into composer check

🔄 Connection Retry System

• Retry mechanism with exponential backoff
• Comprehensive logging with Monolog integration
• Configurable retry policies for different error types

📈 Statistics

• 18 commits in this release
• 429 tests passing
• 24 examples working across all dialects
• PHPStan level 8 compliance
• Zero external dependencies maintained

🔗 Links

• Full Changelog: v2.5.1...v2.6.0
• Documentation: README.md
• Examples: examples/

---

Installation:

composer require tommyknocker/pdo-database-class:^2.6.0

Upgrade from v2.5.x:
Please review the breaking changes section and update your error handling code accordingly.

v2.5.1

🚀 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

v2.5.0

✨ Major Feature Release

New Features

Connection Pooling

• Support for initialization without default connection via new PdoDb()
• Better error messages for uninitialized connections
• Perfect for microservices and multi-tenant applications

17 New SQL Helper Functions

• NULL handling: Db::ifNull(), Db::coalesce(), Db::nullIf()
• Math operations: Db::abs(), Db::round(), Db::mod(), Db::greatest(), Db::least()
• String operations: Db::upper(), Db::lower(), Db::trim(), Db::length(), Db::substring(), Db::replace()
• Date/Time extraction: Db::curDate(), Db::curTime(), Db::date(), Db::time(), Db::year(), Db::month(), Db::day(), Db::hour(), Db::minute(), Db::second()

Complete JSON Operations API

• Db::jsonGet(), Db::jsonLength(), Db::jsonKeys(), Db::jsonType()
• Unified API across MySQL, PostgreSQL, and SQLite
• Comprehensive edge-case testing

📚 21 Runnable Examples (NEW!)
Complete, tested examples organized by complexity:

• Basic: Connection, CRUD, WHERE conditions, INSERT/UPDATE
• Intermediate: JOINs, aggregations, pagination, transactions
• Advanced: Connection pooling, bulk operations, UPSERT
• JSON: Complete JSON operations guide
• Helpers: String, math, date/time, NULL handling
• Real-World:
  • Blog system with posts, comments, tags, and analytics
  • User authentication with sessions, RBAC, password hashing
  • Advanced search & filters with facets and pagination
  • Multi-tenant SaaS with resource tracking and quotas

Comprehensive Test Coverage

• 334 tests, 1499 assertions - ALL PASSING ✅
• 90%+ code coverage with dialect-specific testing
• 68 tests in SharedCoverageTest for dialect-independent code
• 8 new edge-case tests for Db::concat() bug fixes
• Improved test organization with automatic setUp() cleanup

Utility Scripts

• scripts/release.sh - Release automation with validation
• scripts/test-examples.sh - Example verification

Documentation

• Professional README (1400+ lines)
• Table of contents, error handling, performance tips
• Debugging guide and troubleshooting section
• Complete CHANGELOG from v1.0.3

🐛 Critical Bug Fixes

• CRITICAL: insertMulti() bulk UPSERT bug: Fixed automatic conflict target determination for PostgreSQL/SQLite ON CONFLICT

  • buildInsertMultiSql() now correctly uses first column when id not present (matches insert() behavior)
  • Enables proper bulk UPSERT operations across all dialects
  • Without this fix, bulk inserts with onDuplicate parameter would fail on PostgreSQL/SQLite

• CRITICAL: Db::concat() helper bugs (2 major issues fixed):

  • Bug #1: ConcatValue not initializing parent class → "Typed property not initialized" error
    • Fixed with parent::__construct('') and protective getValue() override
  • Bug #2: String literals (spaces, special chars) treated as column names
    • Enhanced auto-detection and quoting for spaces, :, |, -, emoji, unicode
    • Now works: Db::concat('first_name', ' ', 'last_name') ✅
  • Added 8 comprehensive edge-case tests covering all scenarios

• Restored RawValue support in rawQuery methods

• Fixed method calls in lock/unlock/loadData/loadXml

• MySQL EXPLAIN compatibility (table format preservation)

• PostgreSQL formatSelectOptions (FOR UPDATE/FOR SHARE)

• SQLite JSON edge cases

🔧 Improvements

• Enhanced examples: Replaced 30+ raw SQL expressions with Db:: helpers for better readability
  • Db::inc()/Db::dec() for balance transfers and counters
  • Db::count(), Db::sum(), Db::case() for aggregations
  • Db::concat() with automatic string literal handling
• Better test isolation: Added setUp() method for automatic cleanup
  • Removed 26+ redundant cleanup statements
  • Improved reliability and maintainability
• Optimized QueryBuilder: Refactored with helper methods
• Clearer error messages: Better guidance for common issues

🗑️ Removed (Non-Breaking)

Deprecated helper methods from PdoDb (~130 lines):

• inc(), dec(), not() → Use Db:: equivalents
• escape() → Use prepared statements
• tableExists() → Use QueryBuilder::tableExists()
• now() → Use Db::now()
• loadData(), loadXml() → Use QueryBuilder::loadCsv(), QueryBuilder::loadXml()

📝 Technical Details

• Backward compatible: Zero breaking changes
• PHP 8.4+ required (property hooks, union types, match expressions)
• 90%+ test coverage with comprehensive edge-case testing
• All 21 examples tested and verified on PHP 8.4.13
• Full CHANGELOG: See CHANGELOG.md

---

🚀 Quick Start

composer require tommyknocker/pdo-database-class

# Try examples
cd vendor/tommyknocker/pdo-database-class/examples
cp config.example.php config.php
php 01-basic/01-connection.php
php 06-real-world/02-user-auth.php
php 06-real-world/03-search-filters.php

📖 Resources

• Documentation
• Examples
• CHANGELOG
• Issues