Doctrine Dbal Cariboo Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package via Composer:

   composer require cariboo/doctrine-dbal-cariboo
   

   Ensure doctrine/dbal is installed (this package extends it).

2. Basic Usage Register the service provider in config/app.php:

   'providers' => [
       // ...
       Cariboo\DoctrineDbalCariboo\DoctrineDbalCaribooServiceProvider::class,
   ],
   

   Publish the config (if needed):

   php artisan vendor:publish --provider="Cariboo\DoctrineDbalCariboo\DoctrineDbalCaribooServiceProvider"
   

3. First Use Case Use the extended DBAL connection in a Laravel service:

   use Illuminate\Support\Facades\DB;
   use Cariboo\DoctrineDbalCariboo\Extensions\QueryBuilder;
   
   $query = DB::connection()->createQueryBuilder();
   $query->select('*')->from('users')->where('active = :active')->setParameter('active', 1);
   $result = $query->execute()->fetchAll();
   

Implementation Patterns

Workflows

1. Query Builder Extensions Leverage extended methods (e.g., groupByWithRollup, havingWithRollup) for advanced SQL operations:

   $query = DB::connection()->createQueryBuilder();
   $query->select('department, COUNT(*) as count')
         ->from('employees')
         ->groupByWithRollup('department');
   

2. Transaction Management Use the extended transaction handler for nested transactions:

   DB::connection()->beginTransaction();
   try {
       DB::connection()->executeStatement('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
       DB::connection()->executeStatement('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
       DB::connection()->commit();
   } catch (\Exception $e) {
       DB::connection()->rollBack();
       throw $e;
   }
   

3. Schema Introspection Fetch database metadata with extended methods:

   $tables = DB::connection()->getSchemaManager()->listTables();
   $columns = DB::connection()->getSchemaManager()->listTableColumns('users');
   

Integration Tips

• Laravel Eloquent: Use raw queries via DB::select() or DB::statement() for operations not covered by Eloquent.
• Migrations: Extend Schema builder for custom table/column operations:

  Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('name');
      $table->timestamps();
      // Use extended methods if available
      $table->indexWithLength('name', 50);
  });
  

• Query Caching: Cache complex queries using Laravel’s cache system:

  $cacheKey = 'complex_query_' . md5($queryString);
  $result = Cache::remember($cacheKey, now()->addHours(1), function () use ($query) {
      return $query->execute()->fetchAll();
  });
  

Gotchas and Tips

Pitfalls

1. Method Availability Not all DBAL methods are extended. Verify the package’s Extensions namespace for available methods (e.g., QueryBuilder, SchemaManager).

   • Fix: Check the source code for supported extensions.

2. Configuration Conflicts Override default DBAL settings in config/database.php may break extended functionality.

   • Fix: Test configurations incrementally and refer to the package’s config/doctrine-dbal-cariboo.php.

3. Transaction Isolation Extended transactions may not support all database engines equally (e.g., MySQL vs. PostgreSQL).

   • Fix: Test transactions in your target environment.

Debugging

• Query Logging Enable DBAL logging in config/database.php:

  'logging' => true,
  'logging_query' => true,
  'logging_time' => true,
  

  Logs appear in storage/logs/laravel.log.

• Parameter Binding Debug parameterized queries with:

  $query->getParameters(); // Inspect bound parameters
  

Extension Points

1. Custom Extensions Extend the package by creating a new trait or class:

   namespace App\Extensions;
   
   use Doctrine\DBAL\Query\QueryBuilder;
   
   trait CustomQueryBuilder {
       public function customMethod() {
           return $this->andWhere('created_at > NOW() - INTERVAL 1 DAY');
       }
   }
   

   Register it in the service provider.

2. Schema Events Listen for schema events (e.g., postCreateTable) via Laravel’s event system:

   Schema::after(function (Blueprint $table) {
       if ($table->getTableName() === 'users') {
           $table->index('email');
       }
   });
   

3. Database-Specific Quirks Handle engine-specific SQL (e.g., LIMIT vs. FETCH FIRST):

   if (DB::connection()->getDatabasePlatform() === 'mysql') {
       $query->setMaxResults(100);
   } else {
       $query->setFirstResult(0)->setMaxResults(100);
   }