Database Laravel Package

## Getting Started
### First Steps
1. **Installation**: Run `composer require bosnadev/database` in your project root. This adds PostgreSQL-specific Eloquent extensions.
2. **Service Provider**: Register `Bosnadev\Database\DatabaseServiceProvider` in `config/app.php` under `providers`.
3. **Database Configuration**: Ensure your `.env` has PostgreSQL configured (e.g., `DB_CONNECTION=pgsql`).
4. **First Use Case**: Test with a model using extended types:
   ```php
   use Bosnadev\Database\Eloquent\Model;
   use Bosnadev\Database\Eloquent\Relations\HasHStore;
   use Bosnadev\Database\Eloquent\Relations\HasUUID;

   class Product extends Model
   {
       use HasHStore, HasUUID;
       protected $hstoreKey = 'metadata';
       protected $keyType = 'uuid';
   }

• Verify: Run php artisan migrate to confirm migrations work with new types.

Key Files to Explore

• src/Eloquent/Model.php: Base model traits for extended types.
• src/Eloquent/Relations/: Custom relation classes (e.g., HasHStore, HasUUID).
• src/PostgresManager.php: Core PostgreSQL-specific logic (e.g., type casting).

Implementation Patterns

Core Workflows

1. HStore Integration

• Usage: Store semi-structured data (key-value pairs) in a single column.

  $product = new Product();
  $product->metadata = ['price' => '19.99', 'tags' => 'premium,limited'];
  $product->save();
  

• Querying:

  // Get products with 'premium' tag
  $products = Product::whereHStoreContains('metadata', 'tags', 'premium')->get();
  

• Migration:

  Schema::create('products', function (Blueprint $table) {
      $table->hstore('metadata'); // Custom column type
  });
  

2. UUID Support

• Usage: Auto-generate UUIDs or use them as primary keys.

  class Product extends Model
  {
      protected $keyType = 'string';
      protected $primaryKey = 'uuid';
  }
  

• Migration:

  Schema::create('products', function (Blueprint $table) {
      $table->uuid('uuid')->primary();
  });
  

• Querying:

  $product = Product::where('uuid', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')->first();
  

3. Geometric Types (PostGIS)

• Usage: Work with spatial data (points, polygons, etc.).

  $location = new Location();
  $location->point = 'POINT(10 20)';
  $location->polygon = 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))';
  $location->save();
  

• Querying:

  // Find locations within a bounding box
  $locations = Location::where('point', '&&', 'BOX(5 5, 15 15)')->get();
  

• Migration:

  Schema::create('locations', function (Blueprint $table) {
      $table->point('point');
      $table->polygon('polygon');
  });
  

4. Custom Casts

• Extend the package’s casting logic for custom PostgreSQL types:

  use Bosnadev\Database\Eloquent\Casts\PostgresCast;
  
  class CustomCast extends PostgresCast
  {
      public function get()
      {
          return json_decode($this->model->{$this->attribute}, true);
      }
  
      public function set($value)
      {
          return json_encode($value);
      }
  }
  

• Apply to a model:

  class Product extends Model
  {
      protected $casts = [
          'custom_data' => CustomCast::class,
      ];
  }
  

Integration Tips

1. Seeding: Use DB::statement() for complex PostgreSQL operations in seeds:

   DB::statement("INSERT INTO locations (point) VALUES ('POINT(1 1)')");
   

2. Raw Queries: Leverage PostgreSQL-specific functions:

   $results = DB::select("SELECT ST_Distance(point, 'POINT(2 2)') FROM locations");
   

3. Testing: Use DatabaseMigrations and RefreshDatabase traits with PostgreSQL:

   use Bosnadev\Database\DatabaseServiceProvider;
   use Illuminate\Foundation\Testing\DatabaseMigrations;
   
   class ProductTest extends TestCase
   {
       use DatabaseMigrations;
   
       public function testHStore()
       {
           // Test logic here
       }
   }
   

4. Performance:

   • For large hstore columns, consider indexing:

     Schema::table('products', function (Blueprint $table) {
         $table->hstore('metadata')->index();
     });
     

   • Use ->select() to limit columns for geometric queries to reduce overhead.

Gotchas and Tips

Pitfalls

1. Type Mismatches:

   • Ensure migrations match the model’s $casts or traits. For example, if using HasUUID, the column must be defined as uuid() in migrations, not string().
   • Fix: Run php artisan migrate:fresh if types are misconfigured.

2. HStore Serialization:

   • hstore data is stored as text. Unexpected serialization (e.g., json_encode on arrays) can corrupt data.
   • Fix: Use the package’s built-in methods (e.g., $model->metadata['key'] = 'value') instead of raw JSON.

3. PostGIS Dependencies:

   • Geometric types require PostGIS to be enabled in PostgreSQL. If queries fail with "function doesn’t exist," verify PostGIS is installed:

     CREATE EXTENSION postgis;
     

   • Fix: Add this to your database setup script or use a tool like pgAdmin to enable PostGIS.

4. UUID Conflicts:

   • If using UUIDs as primary keys, ensure your application doesn’t generate duplicates. Laravel’s HasUUID trait handles generation, but custom logic may bypass this.
   • Fix: Use Str::uuid()->toString() for manual generation or validate uniqueness in migrations.

5. Case Sensitivity:

   • PostgreSQL is case-sensitive for identifiers. If queries fail, check for typos in column/table names (e.g., point vs Point).

Debugging

1. Query Logs: Enable query logging to debug complex PostgreSQL queries:

   DB::enableQueryLog();
   $results = Product::whereHStoreContains('metadata', 'tags', 'premium')->get();
   dd(DB::getQueryLog());
   

2. Raw SQL: For geometric queries, test raw SQL in psql or a PostgreSQL client first:

   SELECT ST_Distance(point, 'POINT(2 2)') FROM locations;
   

3. Schema Dumps: Use php artisan schema:dump to inspect generated migrations and identify mismatches.

Configuration Quirks

1. Connection Overrides: The package assumes the default pgsql connection. Override in .env if needed:

   DB_CONNECTION=pgsql_custom
   

   Then configure config/database.php accordingly.

2. Timezone Handling: PostgreSQL timestamps respect the database’s timezone. Set this in .env:

   DB_TIMEZONE=UTC
   

Extension Points

1. Custom Traits: Extend existing traits (e.g., HasHStore) to add domain-specific logic:

   trait HasCustomHStore extends HasHStore
   {
       public function scopeWithNestedHStore($query, $key, $nestedKey, $value)
       {
           return $query->whereRaw("metadata->>'$key'->>'$nestedKey' = ?", [$value]);
       }
   }
   

2. Query Builder Extensions: Add custom methods to the query builder by publishing the package’s config:

   php artisan vendor:publish --provider="Bosnadev\Database\DatabaseServiceProvider"
   

   Then extend app/Extensions/QueryBuilder.php.

3. Event Listeners: Listen for model events to transform data before/after saving:

   Product::saved(function ($product) {
       if ($product->wasChanged('metadata')) {
           // Log or process hstore changes
       }
   });
   

4. Testing Helpers: Create custom test helpers for PostgreSQL-specific assertions:

   function assertHStoreContains($model, $key, $value)
   {
       $this->assertTrue($model