gem-hunter
This project extends Laravel's database layer to allow use specific Postgres features without raw queries.
Run this command to install:
composer require umbrellio/laravel-pg-extensions
Schema::create()Schema with USINGSchema for viewsExample:
Schema::create('table', function (Blueprint $table) { $table->like('other_table')->includingAll(); $table->ifNotExists(); });
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(); });
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(); });
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 }
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.
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)
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])
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(), ]); });
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'); });
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); });
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); } }
CreateCommand with inherits and partition byReleased under MIT License.
Created by Vitaliy Lazeev & Korben Dallas.
git checkout -b feature/my-new-feature)git commit -am 'Add some feature')git push origin feature/my-new-feature)