composer require efureev/laravel-support-db "^2.0"Bit String. Doc.
$table->bit(string $column, int $length = 1);Points are the fundamental two-dimensional building block for geometric types. Doc.
$table->geoPoint(string $column);Paths are represented by lists of connected points. Doc.
$table->geoPoint(string $column);The IP network datatype stores an IP network in CIDR notation. Doc.
IPv4 = 7 bytes
IPv6 = 19 bytes
$table->ipNetwork(string $column);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);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()'));The xml data type can be used to store an XML document. Doc.
$table->xml(string $column);The array of UUID data type can be used to store an array of IDs (uuid type).
$table->uuidArray(string $column);The array of integer data type can be used to store a list of integers.
$table->intArray(string $column);The array of text data type can be used to store a list of string.
$table->textArray(string $column);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');// 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();
});// Facade methods:
Schema::dropView('active_users');
Schema::dropViewIfExists('active_users');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']);
});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.
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();
});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 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
);
});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');$list = Model::toBase()->updateAndReturn(['deleted_at' => now()], 'id', 'name');$list = Model::where(['enabled' => true])->updateAndReturn(['enabled' => false], 'id');$list = Model::toBase()->deleteAndReturn('id', 'name');$list = Model::where(['enabled' => true])->deleteAndReturn('id');The Schema facade supports the creation of extensions with the createExtension and createExtensionIfNotExists
methods:
Schema::createExtension('uuid-ossp');
Schema::createExtensionIfNotExists('uuid-ossp');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');<?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');
}
);composer test
composer test-cover # with coverage