agpopov/laravel-pg-extensions

Extensions for Postgres Laravel

6.0.0 2022-02-28 09:19 UTC

Requires

• php: ^8.0
• doctrine/dbal: ^2.9|^3.0

Suggests

• umbrellio/laravel-common-objects: Package with helpers for common Laravel components
• umbrellio/laravel-ltree: Package for working with Postgres LTree extension

MIT

• Vitaliy Lazeev
• Korben Dallas
• Alexander Popov

• migrations
• php
• schema
• postgresql
• extension
• postgres
• builder
• laravel

README

This project extends Laravel's database layer to allow use specific Postgres features without raw queries.

Installation

Run this command to install:

composer require agpopov/laravel-pg-extensions

Features

• Extended Schema::create()
• Added Support NUMERIC Type
• Extended Schema with USING
• Extended Schema for views
• Working with UNIQUE indexes
• Working with EXCLUDE constraints
• Working with CHECK constraints
• Working with partitions
• Check existing index before manipulation

Extended table creation

Example:

Schema::create('table', function (Blueprint $table) {
    $table->like('other_table')->includingAll(); 
    $table->ifNotExists();
});

Extended Schema USING

Example:

Schema::create('table', function (Blueprint $table) {
    $table->integer('number');
});

//modifications with data...

Schema::table('table', function (Blueprint $table) {
    $table
        ->string('number')
        ->using("('[' || number || ']')::character varying")
        ->change();
});

Create views

Example:

// Facade methods:
Schema::createView('active_users', "SELECT * FROM users WHERE active = 1");
Schema::createRecursiveView('active_users', ['id', 'name'], "SELECT id, name FROM users WHERE active = 1");
Schema::dropView('active_users');

// Schema methods:
Schema::create('users', function (Blueprint $table) {
    $table
        ->createView('active_users', "SELECT * FROM users WHERE active = 1")
        ->materialize();
});

Extended indexes creation

Example:

Schema::create('table', function (Blueprint $table) {
    $table->string('code'); 
    $table->softDeletes();
    $table->indexPartial('code')->whereNull('deleted_at');
});

Extended unique indexes creation

Example:

Schema::create('table', function (Blueprint $table) {
    $table->string('code'); 
    $table->softDeletes();
    $table->uniquePartial('code')->whereNull('deleted_at');
});

If you want to delete partial unique index, use this method:

Schema::create('table', function (Blueprint $table) {
    $table->dropUniquePartial(['code']);
});

$table->dropUnique() doesn't work for Partial Unique Indexes, because PostgreSQL doesn't define a partial (ie conditional) UNIQUE constraint. If you try to delete such a Partial Unique Index you will get an error.

CREATE UNIQUE INDEX CONCURRENTLY examples_new_col_idx ON examples (new_col);
ALTER TABLE examples
    ADD CONSTRAINT examples_unique_constraint USING INDEX examples_new_col_idx;

When you create a unique index without conditions, PostgresSQL will create Unique Constraint automatically for you, and when you try to delete such an index, Constraint will be deleted first, then Unique Index.

Exclude constraints creation

Using the example below:

Schema::create('table', function (Blueprint $table) {
    $table->integer('type_id'); 
    $table->date('date_start'); 
    $table->date('date_end'); 
    $table->softDeletes();
    $table
        ->exclude(['date_start', 'date_end'])
        ->using('type_id', '=')
        ->using('daterange(date_start, date_end)', '&&')
        ->method('gist')
        ->with('some_arg', 1)
        ->with('any_arg', 'some_value')
        ->whereNull('deleted_at');
});

An Exclude Constraint will be generated for your table:

ALTER TABLE test_table
    ADD CONSTRAINT test_table_date_start_date_end_excl
        EXCLUDE USING gist (type_id WITH =, daterange(date_start, date_end) WITH &&)
        WITH (some_arg = 1, any_arg = 'some_value')
        WHERE ("deleted_at" is null)

Check constraints creation

Using the example below:

Schema::create('table', function (Blueprint $table) {
    $table->integer('type_id'); 
    $table->date('date_start'); 
    $table->date('date_end'); 
    $table
        ->check(['date_start', 'date_end'])
        ->whereColumn('date_end', '>', 'date_end')
        ->whereIn('type_id', [1, 2, 3]);
});

An Check Constraint will be generated for your table:

ALTER TABLE test_table
    ADD CONSTRAINT test_table_date_start_date_end_chk
        CHECK ("date_end" > "date_start" AND "type_id" IN [1, 2, 3])

Partitions

Support for attaching and detaching partitions.

Example:

Schema::table('table', function (Blueprint $table) {
    $table->attachPartition('partition')->range([
        'from' => now()->startOfDay(), // Carbon will be converted to date time string
        'to' => now()->tomorrow(),
    ]);
});

Check existing index

Schema::table('some_table', function (Blueprint $table) {
   // check unique index exists on column
   if ($table->hasIndex(['column'], true)) {
      $table->dropUnique(['column']);
   }
   $table->uniquePartial('column')->whereNull('deleted_at');
});

Numeric column type

Unlike standard laravel decimal type, this type can be with variable precision

Schema::table('some_table', function (Blueprint $table) {
   $table->numeric('column_with_variable_precision');
   $table->numeric('column_with_defined_precision', 8);
   $table->numeric('column_with_defined_precision_and_scale', 8, 2);
});

Custom Extensions

1). Create a repository for your extension.

2). Add this package as a dependency in composer.

3). Inherit the classes you intend to extend from abstract classes with namespace: namespace Umbrellio\Postgres\Extensions

4). Implement extension methods in closures, example:

use Umbrellio\Postgres\Extensions\Schema\AbstractBlueprint;
class SomeBlueprint extends AbstractBlueprint
{
   public function someMethod()
   {
       return function (string $column): Fluent {
           return $this->addColumn('someColumn', $column);
       };
   }
}

5). Create Extension class and mix these methods using the following syntax, ex:

use Umbrellio\Postgres\PostgresConnection;
use Umbrellio\Postgres\Schema\Blueprint;
use Umbrellio\Postgres\Schema\Grammars\PostgresGrammar;
use Umbrellio\Postgres\Extensions\AbstractExtension;

class SomeExtension extends AbstractExtension
{
    public static function getMixins(): array
    {
        return [
            SomeBlueprint::class => Blueprint::class,
            SomeConnection::class => PostgresConnection::class,
            SomeSchemaGrammar::class => PostgresGrammar::class,
            ...
        ];
    }
    
    public static function getTypes(): string
    {
        // where SomeType extends Doctrine\DBAL\Types\Type
        return [
            'some' => SomeType::class,
        ];
    }

    public static function getName(): string
    {
        return 'some';
    }
}

6). Register your Extension in ServiceProvider and put in config/app.php, ex:

use Illuminate\Support\ServiceProvider;
use Umbrellio\Postgres\PostgresConnection;

class SomeServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        PostgresConnection::registerExtension(SomeExtension::class);
    }
}

TODO features

• Extend CreateCommand with inherits and partition by
• Extend working with partitions
• COPY support
• DISTINCT on specific columns
• INSERT ON CONFLICT support
• ...

License

Released under MIT License.

Authors

Created by Vitaliy Lazeev & Korben Dallas.

Contributing

• Fork it ( https://github.com/umbrellio/laravel-pg-extensions )
• Create your feature branch (git checkout -b feature/my-new-feature)
• Commit your changes (git commit -am 'Add some feature')
• Push to the branch (git push origin feature/my-new-feature)
• Create new Pull Request