efureev / laravel-support-db
PHP Support Package for Laravel DB
Installs: 5 811
Dependents: 0
Suggesters: 0
Security: 0
Stars: 3
Watchers: 3
Forks: 0
Open Issues: 0
Requires
- php: ^8.2|^8.3
- ext-pdo: *
- illuminate/database: ^11.0
Requires (Dev)
- orchestra/testbench: ^9.0
- phpunit/phpunit: ^11.0
- squizlabs/php_codesniffer: ^3.9
README
Description
Install
composer require efureev/laravel-support-db "^2.0"
Contents
Ext Column Types
Bit
Bit String. Doc.
$table->bit(string $column, int $length = 1);
Geo Point
Points are the fundamental two-dimensional building block for geometric types. Doc.
$table->geoPoint(string $column);
Geo Path
Paths are represented by lists of connected points. Doc.
$table->geoPoint(string $column);
IP Network
The IP network datatype stores an IP network in CIDR notation. Doc.
IPv4 = 7 bytes
IPv6 = 19 bytes
$table->ipNetwork(string $column);
Ranges
The range data types store a range of values with optional start and end values. They can be used e.g. to describe the duration a meeting room is booked. Doc.
$table->dateRange(string $column); $table->tsRange(string $column); $table->timestampRange(string $column);
UUID
The primaryUUID
can be used to store UUID-type as primary key.
$table->primaryUUID(); // create PK UUID-column with name `id` $table->primaryUUID('custom_name'); // create PK UUID-column with name `custom_name`
The generateUUID
can be used to store UUID-type with/without index (or FK).
On a row creating generates a value with uuid_generate_v4()
by extension uuid-ossp
.
// create UUID-column with name `id`. Generate UUID-value by DB. $table->generateUUID(); // create UUID-column with name `cid`. Generate UUID-value by DB. $table->generateUUID('cid'); // create UUID-column with name `cid`. NOT generate UUID-value by DB. Set `nullable`. Default value: `NULL`. $table->generateUUID('id', null); // create UUID-column with name `cid`. NOT generate UUID-value by DB. Set `nullable`. Default value: `NULL`. Create Index by this column. $table->generateUUID('fk_id', null)->index(); // create UUID-column with name `fk_id`. NOT generate UUID-value by DB. $table->generateUUID('fk_id', false); // create UUID-column with name `fk_id`. Generate UUID-value by DB with custom value. $table->generateUUID('fk_id', fn($column)=>'uuid_generate_v5()'); // create UUID-column with name `fk_id`. Generate UUID-value by DB with custom value. $table->generateUUID('fk_id', new Expression('uuid_generate_v2()'));
XML
The xml data type can be used to store an XML document. Doc.
$table->xml(string $column);
Array of UUID
The array of UUID data type can be used to store an array of IDs (uuid type).
$table->uuidArray(string $column);
Array of Integer
The array of integer data type can be used to store a list of integers.
$table->intArray(string $column);
Array of Text
The array of text data type can be used to store a list of string.
$table->textArray(string $column);
Column Options
Compression
PostgreSQL 14 introduced the possibility to specify the compression method for toast-able data types. You can choose
between the default method pglz
, the recently added lz4
algorithm and the value default
to use the server default
setting.
Doc.
$table->string('col')->compression('lz4');
Views
Create views
// Facade methods: Schema::createView('active_users', "SELECT * FROM users WHERE active = 1"); Schema::createView('active_users', "SELECT * FROM users WHERE active = 1", true) ; Schema::createViewOrReplace('active_users', "SELECT * FROM users WHERE active = 1"); // Schema methods: use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint; Schema::create('users', function (Blueprint $table) { $table ->createView('active_users', "SELECT * FROM users WHERE active = 1") ->materialize(); });
Dropping views
// Facade methods: Schema::dropView('active_users'); Schema::dropViewIfExists('active_users');
Indexes
Partial indexes
See: https://www.postgresql.org/docs/current/indexes-partial.html
Example:
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint; Schema::create('table', static function (Blueprint $table) { $table->string('code'); $table->softDeletes(); $table ->partial('code') ->whereNull('deleted_at'); });
If you want to delete partial index, use this method:
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint; Schema::create('table', static function (Blueprint $table) { $table->dropPartial(['code']); });
Unique Partial indexes
Example:
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint; Schema::create('table', static 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:
use \Php\Support\Laravel\Database\Schema\Postgres\Blueprint; Schema::create('table', static 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.
Extended Schema
Create like another table
Create a table from a source-table. Creates a structure only.
includingAll
copies all dependencies from source-table.
Creating will be without a data.
Schema::create('target_table', function (Blueprint $table) { $table->like('source_table')->includingAll(); $table->ifNotExists(); });
Create as another table with full data
Copy a table from a source-table. Copy only columns and a data. Without indexes and so on...
Schema::create('target_table', function (Blueprint $table) { $table->fromTable('source_table'); });
Create as another table with data from select query
Create a table from a select query. Copy only columns and a data. Without indexes and so on...
Schema::create('target_table', function (Blueprint $table) { $table->fromSelect('select id, name from source_table'); }); // or Schema::create('target_table', function (Blueprint $table) { $table->fromSelect( 'select t1.id, t2.enabled, t2.extra from source_table t1 ' . 'join source_table_2 t2 on t1.id = t2.src_id ' . 'where t2.enabled = true' ); }); // or $tbl = 'source_table'; Schema::create( $tbl, static function (Blueprint $table) { $table->string('key', 16)->primary(); $table->string('title'); $table->integer('sort')->index(); } ); // or Schema::create(self::TGT_TABLE, function (Blueprint $table) use ($tbl) { Schema::createExtensionIfNotExists('uuid-ossp'); $table->fromSelect( 'select uuid_generate_v4() as id, key, title, sort from ' . $tbl ); }); // or Schema::create(self::TGT_TABLE, function (Blueprint $table) use ($tbl) { Schema::createExtensionIfNotExists('uuid-ossp'); $table->fromSelect( 'select uuid_generate_v4() as id, * ' . $tbl ); });
Drop Cascade If Exists
Automatically drop objects that depend on the table (such as views, indexes, seqs), and in turn all objects that depend on those objects.
Schema::dropIfExistsCascade('table');
Extended Query Builder
Update records and return updated records` columns
$list = Model::toBase()->updateAndReturn(['deleted_at' => now()], 'id', 'name');
$list = Model::where(['enabled' => true])->updateAndReturn(['enabled' => false], 'id');
Delete records and return deleted records` columns
$list = Model::toBase()->deleteAndReturn('id', 'name');
$list = Model::where(['enabled' => true])->deleteAndReturn('id');
Extensions
Create Extensions
The Schema facade supports the creation of extensions with the createExtension
and createExtensionIfNotExists
methods:
Schema::createExtension('uuid-ossp'); Schema::createExtensionIfNotExists('uuid-ossp');
Dropping Extensions
To remove extensions, you may use the dropExtensionIfExists
methods provided by the Schema facade:
Schema::dropExtensionIfExists('tablefunc');
You may drop many extensions at once by passing multiple extension names:
Schema::dropExtensionIfExists('tablefunc', 'fuzzystrmatch');
Usage
Simple example
<?php use Illuminate\Support\Facades\Schema; use Php\Support\Laravel\Database\Schema\Postgres\Blueprint; Schema::create( 'test_table', static function (Blueprint $table) { $table->primaryUUID(); $table->generateUUID('id', null); $table->tsRange('range'); $table->numeric('num'); } );
Test
composer test composer test-cover # with coverage