Umbrellio Laravel Pg Extensions

#Laravel PG extensions

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

#Installation

Run this command to install:

composer require umbrellio/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
• Getting foreign keys for table

#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::dropView('active_users')
 
// Schema methods:
Schema::create('users', function (Blueprint $table) {
    $table
        ->createView('active_users', "SELECT * FROM users WHERE active = 1")
        ->materialize();
});

#Get foreign keys

Example:

// Facade methods:
/** @var ForeignKeyDefinition[] $fks */
$fks = Schema::getForeignKeys('some_table');
 
foreach ($fks as $fk) {
    // $fk->source_column_name
    // $fk->target_table_name
    // $fk->target_column_name
}

#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_start')
        ->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