<?php
namespace Illuminate\Database\Schema\Grammars;
use Illuminate\Support\Fluent;
use Illuminate\Database\Schema\Blueprint;
class PostgresGrammar extends Grammar
{
/**
* If this Grammar supports schema changes wrapped in a transaction.
*
* @var bool
*/
protected $transactions = true;
/**
* The possible column modifiers.
*
* @var array
*/
protected $modifiers = ['Increment', 'Nullable', 'Default'];
/**
* The columns available as serials.
*
* @var array
*/
protected $serials = ['bigInteger', 'integer', 'mediumInteger', 'smallInteger', 'tinyInteger'];
/**
* The commands to be executed outside of create or alter command.
*
* @var array
*/
protected $fluentCommands = ['Comment'];
/**
* Compile the query to determine if a table exists.
*
* @return string
*/
public function compileTableExists()
{
return 'select * from information_schema.tables where table_schema = ? and table_name = ?';
}
/**
* Compile the query to determine the list of columns.
*
* @return string
*/
public function compileColumnListing()
{
return 'select column_name from information_schema.columns where table_schema = ? and table_name = ?';
}
/**
* Compile a create table command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileCreate(Blueprint $blueprint, Fluent $command)
{
return sprintf('%s table %s (%s)',
$blueprint->temporary ? 'create temporary' : 'create',
$this->wrapTable($blueprint),
implode(', ', $this->getColumns($blueprint))
);
}
/**
* Compile a column addition command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileAdd(Blueprint $blueprint, Fluent $command)
{
return sprintf('alter table %s %s',
$this->wrapTable($blueprint),
implode(', ', $this->prefixArray('add column', $this->getColumns($blueprint)))
);
}
/**
* Compile a primary key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compilePrimary(Blueprint $blueprint, Fluent $command)
{
$columns = $this->columnize($command->columns);
return 'alter table '.$this->wrapTable($blueprint)." add primary key ({$columns})";
}
/**
* Compile a unique key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileUnique(Blueprint $blueprint, Fluent $command)
{
return sprintf('alter table %s add constraint %s unique (%s)',
$this->wrapTable($blueprint),
$this->wrap($command->index),
$this->columnize($command->columns)
);
}
/**
* Compile a plain index key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileIndex(Blueprint $blueprint, Fluent $command)
{
return sprintf('create index %s on %s%s (%s)',
$this->wrap($command->index),
$this->wrapTable($blueprint),
$command->algorithm ? ' using '.$command->algorithm : '',
$this->columnize($command->columns)
);
}
/**
* Compile a spatial index key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileSpatialIndex(Blueprint $blueprint, Fluent $command)
{
$command->algorithm = 'gist';
return $this->compileIndex($blueprint, $command);
}
/**
* Compile a foreign key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileForeign(Blueprint $blueprint, Fluent $command)
{
$sql = parent::compileForeign($blueprint, $command);
if (! is_null($command->deferrable)) {
$sql .= $command->deferrable ? ' deferrable' : ' not deferrable';
}
if ($command->deferrable && ! is_null($command->initiallyImmediate)) {
$sql .= $command->initiallyImmediate ? ' initially immediate' : ' initially deferred';
}
return $sql;
}
/**
* Compile a drop table command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDrop(Blueprint $blueprint, Fluent $command)
{
return 'drop table '.$this->wrapTable($blueprint);
}
/**
* Compile a drop table (if exists) command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropIfExists(Blueprint $blueprint, Fluent $command)
{
return 'drop table if exists '.$this->wrapTable($blueprint);
}
/**
* Compile the SQL needed to drop all tables.
*
* @param string $tables
* @return string
*/
public function compileDropAllTables($tables)
{
return 'drop table "'.implode('","', $tables).'" cascade';
}
/**
* Compile the SQL needed to drop all views.
*
* @param string $views
* @return string
*/
public function compileDropAllViews($views)
{
return 'drop view "'.implode('","', $views).'" cascade';
}
/**
* Compile the SQL needed to retrieve all table names.
*
* @param string $schema
* @return string
*/
public function compileGetAllTables($schema)
{
return "select tablename from pg_catalog.pg_tables where schemaname = '{$schema}'";
}
/**
* Compile the SQL needed to retrieve all view names.
*
* @param string $schema
* @return string
*/
public function compileGetAllViews($schema)
{
return "select viewname from pg_catalog.pg_views where schemaname = '{$schema}'";
}
/**
* Compile a drop column command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropColumn(Blueprint $blueprint, Fluent $command)
{
$columns = $this->prefixArray('drop column', $this->wrapArray($command->columns));
return 'alter table '.$this->wrapTable($blueprint).' '.implode(', ', $columns);
}
/**
* Compile a drop primary key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropPrimary(Blueprint $blueprint, Fluent $command)
{
$index = $this->wrap("{$blueprint->getTable()}_pkey");
return 'alter table '.$this->wrapTable($blueprint)." drop constraint {$index}";
}
/**
* Compile a drop unique key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropUnique(Blueprint $blueprint, Fluent $command)
{
$index = $this->wrap($command->index);
return "alter table {$this->wrapTable($blueprint)} drop constraint {$index}";
}
/**
* Compile a drop index command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropIndex(Blueprint $blueprint, Fluent $command)
{
return "drop index {$this->wrap($command->index)}";
}
/**
* Compile a drop spatial index command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropSpatialIndex(Blueprint $blueprint, Fluent $command)
{
return $this->compileDropIndex($blueprint, $command);
}
/**
* Compile a drop foreign key command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropForeign(Blueprint $blueprint, Fluent $command)
{
$index = $this->wrap($command->index);
return "alter table {$this->wrapTable($blueprint)} drop constraint {$index}";
}
/**
* Compile a rename table command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileRename(Blueprint $blueprint, Fluent $command)
{
$from = $this->wrapTable($blueprint);
return "alter table {$from} rename to ".$this->wrapTable($command->to);
}
/**
* Compile a rename index command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileRenameIndex(Blueprint $blueprint, Fluent $command)
{
return sprintf('alter index %s rename to %s',
$this->wrap($command->from),
$this->wrap($command->to)
);
}
/**
* Compile the command to enable foreign key constraints.
*
* @return string
*/
public function compileEnableForeignKeyConstraints()
{
return 'SET CONSTRAINTS ALL IMMEDIATE;';
}
/**
* Compile the command to disable foreign key constraints.
*
* @return string
*/
public function compileDisableForeignKeyConstraints()
{
return 'SET CONSTRAINTS ALL DEFERRED;';
}
/**
* Compile a comment command.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileComment(Blueprint $blueprint, Fluent $command)
{
return sprintf('comment on column %s.%s is %s',
$this->wrapTable($blueprint),
$this->wrap($command->column->name),
"'".str_replace("'", "''", $command->value)."'"
);
}
/**
* Create the column definition for a char type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeChar(Fluent $column)
{
return "char({$column->length})";
}
/**
* Create the column definition for a string type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeString(Fluent $column)
{
return "varchar({$column->length})";
}
/**
* Create the column definition for a text type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeText(Fluent $column)
{
return 'text';
}
/**
* Create the column definition for a medium text type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeMediumText(Fluent $column)
{
return 'text';
}
/**
* Create the column definition for a long text type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeLongText(Fluent $column)
{
return 'text';
}
/**
* Create the column definition for an integer type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeInteger(Fluent $column)
{
return $this->generatableColumn('integer', $column);
}
/**
* Create the column definition for a big integer type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeBigInteger(Fluent $column)
{
return $this->generatableColumn('bigint', $column);
}
/**
* Create the column definition for a medium integer type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeMediumInteger(Fluent $column)
{
return $this->generatableColumn('integer', $column);
}
/**
* Create the column definition for a tiny integer type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeTinyInteger(Fluent $column)
{
return $this->generatableColumn('smallint', $column);
}
/**
* Create the column definition for a small integer type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeSmallInteger(Fluent $column)
{
return $this->generatableColumn('smallint', $column);
}
/**
* Create the column definition for a generatable column.
*
* @param string $type
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function generatableColumn($type, Fluent $column)
{
if (! $column->autoIncrement && is_null($column->generatedAs)) {
return $type;
}
if ($column->autoIncrement && is_null($column->generatedAs)) {
return with([
'integer' => 'serial',
'bigint' => 'bigserial',
'smallint' => 'smallserial',
])[$type];
}
$options = '';
if (! is_bool($column->generatedAs) && ! empty($column->generatedAs)) {
$options = sprintf(' (%s)', $column->generatedAs);
}
return sprintf(
'%s generated %s as identity%s',
$type,
$column->always ? 'always' : 'by default',
$options
);
}
/**
* Create the column definition for a float type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeFloat(Fluent $column)
{
return $this->typeDouble($column);
}
/**
* Create the column definition for a double type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeDouble(Fluent $column)
{
return 'double precision';
}
/**
* Create the column definition for a real type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeReal(Fluent $column)
{
return 'real';
}
/**
* Create the column definition for a decimal type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeDecimal(Fluent $column)
{
return "decimal({$column->total}, {$column->places})";
}
/**
* Create the column definition for a boolean type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeBoolean(Fluent $column)
{
return 'boolean';
}
/**
* Create the column definition for an enumeration type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeEnum(Fluent $column)
{
return sprintf(
'varchar(255) check ("%s" in (%s))',
$column->name,
$this->quoteString($column->allowed)
);
}
/**
* Create the column definition for a json type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeJson(Fluent $column)
{
return 'json';
}
/**
* Create the column definition for a jsonb type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeJsonb(Fluent $column)
{
return 'jsonb';
}
/**
* Create the column definition for a date type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeDate(Fluent $column)
{
return 'date';
}
/**
* Create the column definition for a date-time type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeDateTime(Fluent $column)
{
return "timestamp($column->precision) without time zone";
}
/**
* Create the column definition for a date-time (with time zone) type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeDateTimeTz(Fluent $column)
{
return "timestamp($column->precision) with time zone";
}
/**
* Create the column definition for a time type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeTime(Fluent $column)
{
return "time($column->precision) without time zone";
}
/**
* Create the column definition for a time (with time zone) type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeTimeTz(Fluent $column)
{
return "time($column->precision) with time zone";
}
/**
* Create the column definition for a timestamp type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeTimestamp(Fluent $column)
{
$columnType = "timestamp($column->precision) without time zone";
return $column->useCurrent ? "$columnType default CURRENT_TIMESTAMP" : $columnType;
}
/**
* Create the column definition for a timestamp (with time zone) type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeTimestampTz(Fluent $column)
{
$columnType = "timestamp($column->precision) with time zone";
return $column->useCurrent ? "$columnType default CURRENT_TIMESTAMP" : $columnType;
}
/**
* Create the column definition for a year type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeYear(Fluent $column)
{
return $this->typeInteger($column);
}
/**
* Create the column definition for a binary type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeBinary(Fluent $column)
{
return 'bytea';
}
/**
* Create the column definition for a uuid type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeUuid(Fluent $column)
{
return 'uuid';
}
/**
* Create the column definition for an IP address type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeIpAddress(Fluent $column)
{
return 'inet';
}
/**
* Create the column definition for a MAC address type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeMacAddress(Fluent $column)
{
return 'macaddr';
}
/**
* Create the column definition for a spatial Geometry type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeGeometry(Fluent $column)
{
return $this->formatPostGisType('geometry');
}
/**
* Create the column definition for a spatial Point type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typePoint(Fluent $column)
{
return $this->formatPostGisType('point');
}
/**
* Create the column definition for a spatial LineString type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeLineString(Fluent $column)
{
return $this->formatPostGisType('linestring');
}
/**
* Create the column definition for a spatial Polygon type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typePolygon(Fluent $column)
{
return $this->formatPostGisType('polygon');
}
/**
* Create the column definition for a spatial GeometryCollection type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeGeometryCollection(Fluent $column)
{
return $this->formatPostGisType('geometrycollection');
}
/**
* Create the column definition for a spatial MultiPoint type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeMultiPoint(Fluent $column)
{
return $this->formatPostGisType('multipoint');
}
/**
* Create the column definition for a spatial MultiLineString type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
public function typeMultiLineString(Fluent $column)
{
return $this->formatPostGisType('multilinestring');
}
/**
* Create the column definition for a spatial MultiPolygon type.
*
* @param \Illuminate\Support\Fluent $column
* @return string
*/
protected function typeMultiPolygon(Fluent $column)
{
return $this->formatPostGisType('multipolygon');
}
/**
* Format the column definition for a PostGIS spatial type.
*
* @param string $type
* @return string
*/
private function formatPostGisType(string $type)
{
return "geography($type, 4326)";
}
/**
* Get the SQL for a nullable column modifier.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $column
* @return string|null
*/
protected function modifyNullable(Blueprint $blueprint, Fluent $column)
{
return $column->nullable ? ' null' : ' not null';
}
/**
* Get the SQL for a default column modifier.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $column
* @return string|null
*/
protected function modifyDefault(Blueprint $blueprint, Fluent $column)
{
if (! is_null($column->default)) {
return ' default '.$this->getDefaultValue($column->default);
}
}
/**
* Get the SQL for an auto-increment column modifier.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $column
* @return string|null
*/
protected function modifyIncrement(Blueprint $blueprint, Fluent $column)
{
if ((in_array($column->type, $this->serials) || ($column->generatedAs !== null)) && $column->autoIncrement) {
return ' primary key';
}
}
}