1 package SQL::Translator::Generator::DDL::SQLServer;
4 use SQL::Translator::Generator::Utils;
5 use SQL::Translator::Schema::Constants;
7 with 'SQL::Translator::Generator::Role::DDL';
9 sub _build_shim { SQL::Translator::Generator::Utils->new( quote_chars => [qw( [ ] )] ) }
11 sub _build_numeric_types {
17 sub _build_unquoted_defaults {
30 has sizeless_types => (
32 builder => '_build_sizeless_types',
35 sub _build_sizeless_types {
37 qw( tinyint smallint int integer bigint text bit image datetime ) }
41 my ($self, $field) = @_;
43 return join ' ', $self->field_name($field), ($self->field_type($field)||die 'type is required'),
44 $self->field_autoinc($field),
45 $self->field_nullable($field),
46 $self->field_default($field),
50 my ($self, $field) = @_;
52 ($field->size && !$self->sizeless_types->{$field->data_type}
53 ? '(' . $field->size . ')'
58 sub field_autoinc { ( $_[1]->is_auto_increment ? 'IDENTITY' : () ) }
60 sub primary_key_constraint {
62 $_[0]->quote($_[1]->name || $_[1]->table->name . '_pk') .
64 join( ', ', map $_[0]->quote($_), $_[1]->fields ) .
70 $_[0]->quote($_[1]->name || $_[1]->table->name . '_idx') .
71 ' ON ' . $_[0]->quote($_[1]->table->name) .
72 ' (' . join( ', ', map $_[0]->quote($_), $_[1]->fields ) . ');'
75 sub unique_constraint_single {
76 my ($self, $constraint) = @_;
79 $self->unique_constraint_name($constraint) .
80 ' UNIQUE (' . join( ', ', map $self->quote($_), $constraint->fields ) . ')'
83 sub unique_constraint_name {
84 my ($self, $constraint) = @_;
85 $self->quote($constraint->name || $constraint->table->name . '_uc' )
88 sub unique_constraint_multiple {
89 my ($self, $constraint) = @_;
91 'CREATE UNIQUE NONCLUSTERED INDEX ' .
92 $self->unique_constraint_name($constraint) .
93 ' ON ' . $self->quote($constraint->table->name) . ' (' .
94 join( ', ', $constraint->fields ) . ')' .
95 ' WHERE ' . join( ' AND ',
96 map $self->quote($_->name) . ' IS NOT NULL',
97 grep { $_->is_nullable } $constraint->fields ) . ';'
100 sub foreign_key_constraint {
101 my ($self, $constraint) = @_;
103 my $on_delete = uc ($constraint->on_delete || '');
104 my $on_update = uc ($constraint->on_update || '');
106 # The default implicit constraint action in MSSQL is RESTRICT
107 # but you can not specify it explicitly. Go figure :)
108 for (map uc $_ || '', $on_delete, $on_update) {
109 undef $_ if $_ eq 'RESTRICT'
112 'ALTER TABLE ' . $self->quote($constraint->table->name) .
114 $self->quote($constraint->name || $constraint->table->name . '_fk') .
116 ' (' . join( ', ', map $self->quote($_), $constraint->fields ) . ') REFERENCES '.
117 $self->quote($constraint->reference_table) .
118 ' (' . join( ', ', map $self->quote($_), $constraint->reference_fields ) . ')'
120 $on_delete && $on_delete ne "NO ACTION"
121 ? ' ON DELETE ' . $on_delete
124 $on_update && $on_update ne "NO ACTION"
125 ? ' ON UPDATE ' . $on_update
130 sub enum_constraint_name {
131 my ($self, $field_name) = @_;
132 $self->quote($field_name . '_chk' )
135 sub enum_constraint {
136 my ( $self, $field_name, $vals ) = @_;
139 'CONSTRAINT ' . $self->enum_constraint_name($field_name) .
140 ' CHECK (' . $self->quote($field_name) .
141 ' IN (' . join( ',', map qq('$_'), @$vals ) . '))'
146 my ($self, $table) = @_;
148 (map $self->enum_constraint($_->name, { $_->extra }->{list} || []),
149 grep { 'enum' eq lc $_->data_type } $table->get_fields),
151 (map $self->primary_key_constraint($_),
152 grep { $_->type eq PRIMARY_KEY } $table->get_constraints),
154 (map $self->unique_constraint_single($_),
156 $_->type eq UNIQUE &&
157 !grep { $_->is_nullable } $_->fields
158 } $table->get_constraints),
162 my ($self, $table) = @_;
163 'CREATE TABLE ' . $self->quote($table->name) . " (\n".
166 $self->fields($table),
167 $self->constraints($table),
173 my ($self, $table) = @_;
174 my $name = $table->name;
175 my $q_name = $self->quote($name);
176 "IF EXISTS (SELECT name FROM sysobjects WHERE name = '$name' AND type = 'U')" .
177 " DROP TABLE $q_name;"
180 sub remove_table_constraints {
181 my ($self, $table) = @_;
182 my $name = $table->name;
183 my $q_name = $self->quote($name);
184 "IF EXISTS (SELECT name FROM sysobjects WHERE name = '$name' AND type = 'U')" .
185 " ALTER TABLE $q_name NOCHECK CONSTRAINT all;"