Subversion-Projekte lars-tiefland.laravel_shop

<?php

namespace Illuminate\Database\Schema\Grammars;

use Illuminate\Database\Connection;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Support\Fluent;

class SqlServerGrammar extends Grammar

{

    /**

     * If this Grammar supports schema changes wrapped in a transaction.

     *

     * @var bool

     */

    protected $transactions = true;

    /**

     * The possible column modifiers.

     *

     * @var string[]

     */

    protected $modifiers = ['Increment', 'Collate', 'Nullable', 'Default', 'Persisted'];

    /**

     * The columns available as serials.

     *

     * @var string[]

     */

    protected $serials = ['tinyInteger', 'smallInteger', 'mediumInteger', 'integer', 'bigInteger'];

    /**

     * Compile a create database command.

     *

     * @param  string  $name

     * @param  \Illuminate\Database\Connection  $connection

     * @return string

     */

    public function compileCreateDatabase($name, $connection)

    {

        return sprintf(

            'create database %s',

            $this->wrapValue($name),

        );

    }

    /**

     * Compile a drop database if exists command.

     *

     * @param  string  $name

     * @return string

     */

    public function compileDropDatabaseIfExists($name)

    {

        return sprintf(

            'drop database if exists %s',

            $this->wrapValue($name)

        );

    }

    /**

     * Compile the query to determine if a table exists.

     *

     * @return string

     */

    public function compileTableExists()

    {

        return "select * from sys.sysobjects where id = object_id(?) and xtype in ('U', 'V')";

    }

    /**

     * Compile the query to determine the list of columns.

     *

     * @param  string  $table

     * @return string

     */

    public function compileColumnListing($table)

    {

        return "select name from sys.columns where object_id = object_id('$table')";

    }

    /**

     * 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)

    {

        $columns = implode(', ', $this->getColumns($blueprint));

        return 'create table '.$this->wrapTable($blueprint)." ($columns)";

    }

    /**

     * Compile a column addition table 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 add %s',

            $this->wrapTable($blueprint),

            implode(', ', $this->getColumns($blueprint))

        );

    }

    /**

     * Compile a rename column command.

     *

     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint

     * @param  \Illuminate\Support\Fluent  $command

     * @param  \Illuminate\Database\Connection  $connection

     * @return array|string

     */

    public function compileRenameColumn(Blueprint $blueprint, Fluent $command, Connection $connection)

    {

        return $connection->usingNativeSchemaOperations()

            ? sprintf("sp_rename '%s', %s, 'COLUMN'",

                $this->wrap($blueprint->getTable().'.'.$command->from),

                $this->wrap($command->to)

            )

            : parent::compileRenameColumn($blueprint, $command, $connection);

    }

    /**

     * 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)

    {

        return sprintf('alter table %s add constraint %s primary key (%s)',

            $this->wrapTable($blueprint),

            $this->wrap($command->index),

            $this->columnize($command->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('create unique index %s on %s (%s)',

            $this->wrap($command->index),

            $this->wrapTable($blueprint),

            $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)',

            $this->wrap($command->index),

            $this->wrapTable($blueprint),

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

    {

        return sprintf('create spatial index %s on %s (%s)',

            $this->wrap($command->index),

            $this->wrapTable($blueprint),

            $this->columnize($command->columns)

        );

    }

    /**

     * 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 sprintf('if exists (select * from sys.sysobjects where id = object_id(%s, \'U\')) drop table %s',

            "'".str_replace("'", "''", $this->getTablePrefix().$blueprint->getTable())."'",

            $this->wrapTable($blueprint)

        );

    }

    /**

     * Compile the SQL needed to drop all tables.

     *

     * @return string

     */

    public function compileDropAllTables()

    {

        return "EXEC sp_msforeachtable 'DROP TABLE ?'";

    }

    /**

     * 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->wrapArray($command->columns);

        $dropExistingConstraintsSql = $this->compileDropDefaultConstraint($blueprint, $command).';';

        return $dropExistingConstraintsSql.'alter table '.$this->wrapTable($blueprint).' drop column '.implode(', ', $columns);

    }

    /**

     * Compile a drop default constraint command.

     *

     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint

     * @param  \Illuminate\Support\Fluent  $command

     * @return string

     */

    public function compileDropDefaultConstraint(Blueprint $blueprint, Fluent $command)

    {

        $columns = "'".implode("','", $command->columns)."'";

        $tableName = $this->getTablePrefix().$blueprint->getTable();

        $sql = "DECLARE @sql NVARCHAR(MAX) = '';";

        $sql .= "SELECT @sql += 'ALTER TABLE [dbo].[{$tableName}] DROP CONSTRAINT ' + OBJECT_NAME([default_object_id]) + ';' ";

        $sql .= 'FROM sys.columns ';

        $sql .= "WHERE [object_id] = OBJECT_ID('[dbo].[{$tableName}]') AND [name] in ({$columns}) AND [default_object_id] <> 0;";

        $sql .= 'EXEC(@sql)';

        return $sql;

    }

    /**

     * 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($command->index);

        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 "drop index {$index} on {$this->wrapTable($blueprint)}";

    }

    /**

     * 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)

    {

        $index = $this->wrap($command->index);

        return "drop index {$index} on {$this->wrapTable($blueprint)}";

    }

    /**

     * 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 "sp_rename {$from}, ".$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("sp_rename N'%s', %s, N'INDEX'",

            $this->wrap($blueprint->getTable().'.'.$command->from),

            $this->wrap($command->to)

        );

    }

    /**

     * Compile the command to enable foreign key constraints.

     *

     * @return string

     */

    public function compileEnableForeignKeyConstraints()

    {

        return 'EXEC sp_msforeachtable @command1="print \'?\'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";';

    }

    /**

     * Compile the command to disable foreign key constraints.

     *

     * @return string

     */

    public function compileDisableForeignKeyConstraints()

    {

        return 'EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";';

    }

    /**

     * Compile the command to drop all foreign keys.

     *

     * @return string

     */

    public function compileDropAllForeignKeys()

    {

        return "DECLARE @sql NVARCHAR(MAX) = N'';

            SELECT @sql += 'ALTER TABLE '

                + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + + QUOTENAME(OBJECT_NAME(parent_object_id))

                + ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'

            FROM sys.foreign_keys;

            EXEC sp_executesql @sql;";

    }

    /**

     * Compile the command to drop all views.

     *

     * @return string

     */

    public function compileDropAllViews()

    {

        return "DECLARE @sql NVARCHAR(MAX) = N'';

            SELECT @sql += 'DROP VIEW ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name) + ';'

            FROM sys.views;

            EXEC sp_executesql @sql;";

    }

    /**

     * Compile the SQL needed to retrieve all table names.

     *

     * @return string

     */

    public function compileGetAllTables()

    {

        return "select name, type from sys.tables where type = 'U'";

    }

    /**

     * Compile the SQL needed to retrieve all view names.

     *

     * @return string

     */

    public function compileGetAllViews()

    {

        return "select name, type from sys.objects where type = 'V'";

    }

    /**

     * Create the column definition for a char type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeChar(Fluent $column)

    {

        return "nchar({$column->length})";

    }

    /**

     * Create the column definition for a string type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeString(Fluent $column)

    {

        return "nvarchar({$column->length})";

    }

    /**

     * Create the column definition for a tiny text type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeTinyText(Fluent $column)

    {

        return 'nvarchar(255)';

    }

    /**

     * Create the column definition for a text type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeText(Fluent $column)

    {

        return 'nvarchar(max)';

    }

    /**

     * Create the column definition for a medium text type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeMediumText(Fluent $column)

    {

        return 'nvarchar(max)';

    }

    /**

     * Create the column definition for a long text type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeLongText(Fluent $column)

    {

        return 'nvarchar(max)';

    }

    /**

     * Create the column definition for an integer type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeInteger(Fluent $column)

    {

        return 'int';

    }

    /**

     * Create the column definition for a big integer type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeBigInteger(Fluent $column)

    {

        return 'bigint';

    }

    /**

     * Create the column definition for a medium integer type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeMediumInteger(Fluent $column)

    {

        return 'int';

    }

    /**

     * Create the column definition for a tiny integer type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeTinyInteger(Fluent $column)

    {

        return 'tinyint';

    }

    /**

     * Create the column definition for a small integer type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeSmallInteger(Fluent $column)

    {

        return 'smallint';

    }

    /**

     * Create the column definition for a float type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeFloat(Fluent $column)

    {

        return 'float';

    }

    /**

     * Create the column definition for a double type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeDouble(Fluent $column)

    {

        return 'float';

    }

    /**

     * 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 'bit';

    }

    /**

     * Create the column definition for an enumeration type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeEnum(Fluent $column)

    {

        return sprintf(

            'nvarchar(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 'nvarchar(max)';

    }

    /**

     * Create the column definition for a jsonb type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeJsonb(Fluent $column)

    {

        return 'nvarchar(max)';

    }

    /**

     * 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 $this->typeTimestamp($column);

    }

    /**

     * 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 $this->typeTimestampTz($column);

    }

    /**

     * Create the column definition for a time type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeTime(Fluent $column)

    {

        return $column->precision ? "time($column->precision)" : 'time';

    }

    /**

     * Create the column definition for a time (with time zone) type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeTimeTz(Fluent $column)

    {

        return $this->typeTime($column);

    }

    /**

     * Create the column definition for a timestamp type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeTimestamp(Fluent $column)

    {

        $columnType = $column->precision ? "datetime2($column->precision)" : 'datetime';

        return $column->useCurrent ? "$columnType default CURRENT_TIMESTAMP" : $columnType;

    }

    /**

     * Create the column definition for a timestamp (with time zone) type.

     *

     * @link https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver15

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeTimestampTz(Fluent $column)

    {

        $columnType = $column->precision ? "datetimeoffset($column->precision)" : 'datetimeoffset';

        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 'varbinary(max)';

    }

    /**

     * Create the column definition for a uuid type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeUuid(Fluent $column)

    {

        return 'uniqueidentifier';

    }

    /**

     * Create the column definition for an IP address type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeIpAddress(Fluent $column)

    {

        return 'nvarchar(45)';

    }

    /**

     * Create the column definition for a MAC address type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    protected function typeMacAddress(Fluent $column)

    {

        return 'nvarchar(17)';

    }

    /**

     * Create the column definition for a spatial Geometry type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typeGeometry(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial Point type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typePoint(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial LineString type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typeLineString(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial Polygon type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typePolygon(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial GeometryCollection type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typeGeometryCollection(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial MultiPoint type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typeMultiPoint(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial MultiLineString type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typeMultiLineString(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a spatial MultiPolygon type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string

     */

    public function typeMultiPolygon(Fluent $column)

    {

        return 'geography';

    }

    /**

     * Create the column definition for a generated, computed column type.

     *

     * @param  \Illuminate\Support\Fluent  $column

     * @return string|null

     */

    protected function typeComputed(Fluent $column)

    {

        return "as ({$column->expression})";

    }

    /**

     * Get the SQL for a collation column modifier.

     *

     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint

     * @param  \Illuminate\Support\Fluent  $column

     * @return string|null

     */

    protected function modifyCollate(Blueprint $blueprint, Fluent $column)

    {

        if (! is_null($column->collation)) {

            return ' collate '.$column->collation;

        }

    }

    /**

     * 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)

    {

        if ($column->type !== 'computed') {

            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->autoIncrement) {

            return ' identity primary key';

        }

    }

    /**

     * Get the SQL for a generated stored column modifier.

     *

     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint

     * @param  \Illuminate\Support\Fluent  $column

     * @return string|null

     */

    protected function modifyPersisted(Blueprint $blueprint, Fluent $column)

    {

        if ($column->persisted) {

            return ' persisted';

        }

    }

    /**

     * Wrap a table in keyword identifiers.

     *

     * @param  \Illuminate\Database\Query\Expression|string  $table

     * @return string

     */

    public function wrapTable($table)

    {

        if ($table instanceof Blueprint && $table->temporary) {

            $this->setTablePrefix('#');

        }

        return parent::wrapTable($table);

    }

    /**

     * Quote the given string literal.

     *

     * @param  string|array  $value

     * @return string

     */

    public function quoteString($value)

    {

        if (is_array($value)) {

            return implode(', ', array_map([$this, __FUNCTION__], $value));

        }

        return "N'$value'";

    }

}