Download the PHP package efureev/laravel-support-db without Composer

PHP Laravel Database Support

Description

Install

Contents

• Ext Column Types
  • Bit
  • GeoPoint
  • GeoPath
  • IP Network
  • Ranges
  • UUID
  • XML
  • Array of UUID
  • Array of Integer
  • Array of Text
• Column Options
  • Compression
• Views
• Indexes
  • Partial indexes
  • Unique Partial indexes
• Extended Schema
  • Create like another table
  • Create as another table with full data
  • Create as another table with data from select query
  • Drop Cascade If Exists
• Extended Query Builder
  • Update records and return deleted records` columns
  • Delete records and return deleted records` columns
• Extensions

Ext Column Types

Bit

Bit String. Doc.

Geo Point

Points are the fundamental two-dimensional building block for geometric types. Doc.

Geo Path

Paths are represented by lists of connected points. Doc.

IP Network

The IP network datatype stores an IP network in CIDR notation. Doc.

IPv4 = 7 bytes
IPv6 = 19 bytes

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.

UUID

The primaryUUID can be used to store UUID-type as primary key.

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.

XML

The xml data type can be used to store an XML document. Doc.

Array of UUID

The array of UUID data type can be used to store an array of IDs (uuid type).

Array of Integer

The array of integer data type can be used to store a list of integers.

Array of Text

The array of text data type can be used to store a list of string.

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.

Views

Create views

Dropping views

Indexes

Partial indexes

See: https://www.postgresql.org/docs/current/indexes-partial.html

Example:

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

Unique Partial indexes

Example:

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

$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.

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.

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...

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...

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.

Extended Query Builder

Update records and return updated records` columns

Delete records and return deleted records` columns

Extensions

Create Extensions

The Schema facade supports the creation of extensions with the createExtension and createExtensionIfNotExists methods:

Dropping Extensions

To remove extensions, you may use the dropExtensionIfExists methods provided by the Schema facade:

You may drop many extensions at once by passing multiple extension names:

Usage

Simple example

Test