1 package SQL::Translator::Generator::DDL::SQLServer;
5 SQL::Translator::Generator::DDL::SQLServer - A Moo based MS SQL Server DDL
10 I<documentation volunteers needed>
15 use SQL::Translator::Schema::Constants;
17 with 'SQL::Translator::Generator::Role::Quote';
18 with 'SQL::Translator::Generator::Role::DDL';
20 sub quote_chars { [qw([ ])] }
23 sub _build_numeric_types {
29 sub _build_unquoted_defaults {
43 sub _build_sizeless_types {
45 qw( tinyint smallint int integer bigint text bit image datetime ) }
49 my ($self, $field) = @_;
51 return join ' ', $self->field_name($field), ($self->field_type($field)||die 'type is required'),
52 $self->field_autoinc($field),
53 $self->field_nullable($field),
54 $self->field_default($field),
57 sub field_autoinc { ( $_[1]->is_auto_increment ? 'IDENTITY' : () ) }
59 sub primary_key_constraint {
61 $_[0]->quote($_[1]->name || $_[1]->table->name . '_pk') .
63 join( ', ', map $_[0]->quote($_), $_[1]->fields ) .
69 $_[0]->quote($_[1]->name || $_[1]->table->name . '_idx') .
70 ' ON ' . $_[0]->quote($_[1]->table->name) .
71 ' (' . join( ', ', map $_[0]->quote($_), $_[1]->fields ) . ');'
74 sub unique_constraint_single {
75 my ($self, $constraint) = @_;
78 $self->unique_constraint_name($constraint) .
79 ' UNIQUE (' . join( ', ', map $self->quote($_), $constraint->fields ) . ')'
82 sub unique_constraint_name {
83 my ($self, $constraint) = @_;
84 $self->quote($constraint->name || $constraint->table->name . '_uc' )
87 sub unique_constraint_multiple {
88 my ($self, $constraint) = @_;
90 'CREATE UNIQUE NONCLUSTERED INDEX ' .
91 $self->unique_constraint_name($constraint) .
92 ' ON ' . $self->quote($constraint->table->name) . ' (' .
93 join( ', ', $constraint->fields ) . ')' .
94 ' WHERE ' . join( ' AND ',
95 map $self->quote($_->name) . ' IS NOT NULL',
96 grep { $_->is_nullable } $constraint->fields ) . ';'
99 sub foreign_key_constraint {
100 my ($self, $constraint) = @_;
102 my $on_delete = uc ($constraint->on_delete || '');
103 my $on_update = uc ($constraint->on_update || '');
105 # The default implicit constraint action in MSSQL is RESTRICT
106 # but you can not specify it explicitly. Go figure :)
107 for (map uc $_ || '', $on_delete, $on_update) {
108 undef $_ if $_ eq 'RESTRICT'
111 'ALTER TABLE ' . $self->quote($constraint->table->name) .
113 $self->quote($constraint->name || $constraint->table->name . '_fk') .
115 ' (' . join( ', ', map $self->quote($_), $constraint->fields ) . ') REFERENCES '.
116 $self->quote($constraint->reference_table) .
117 ' (' . join( ', ', map $self->quote($_), $constraint->reference_fields ) . ')'
119 $on_delete && $on_delete ne "NO ACTION"
120 ? ' ON DELETE ' . $on_delete
123 $on_update && $on_update ne "NO ACTION"
124 ? ' ON UPDATE ' . $on_update
129 sub enum_constraint_name {
130 my ($self, $field_name) = @_;
131 $self->quote($field_name . '_chk' )
134 sub enum_constraint {
135 my ( $self, $field_name, $vals ) = @_;
138 'CONSTRAINT ' . $self->enum_constraint_name($field_name) .
139 ' CHECK (' . $self->quote($field_name) .
140 ' IN (' . join( ',', map qq('$_'), @$vals ) . '))'
145 my ($self, $table) = @_;
147 (map $self->enum_constraint($_->name, { $_->extra }->{list} || []),
148 grep { 'enum' eq lc $_->data_type } $table->get_fields),
150 (map $self->primary_key_constraint($_),
151 grep { $_->type eq PRIMARY_KEY } $table->get_constraints),
153 (map $self->unique_constraint_single($_),
155 $_->type eq UNIQUE &&
156 !grep { $_->is_nullable } $_->fields
157 } $table->get_constraints),
161 my ($self, $table) = @_;
162 join ( "\n", $self->table_comments($table), '' ) .
164 'CREATE TABLE ' . $self->quote($table->name) . " (\n".
167 $self->fields($table),
168 $self->constraints($table),
171 $self->unique_constraints_multiple($table),
172 $self->indices($table),
176 sub unique_constraints_multiple {
177 my ($self, $table) = @_;
178 (map $self->unique_constraint_multiple($_),
180 $_->type eq UNIQUE &&
181 grep { $_->is_nullable } $_->fields
182 } $table->get_constraints)
186 my ($self, $table) = @_;
187 my $name = $table->name;
188 my $q_name = $self->quote($name);
189 "IF EXISTS (SELECT name FROM sysobjects WHERE name = '$name' AND type = 'U')" .
190 " DROP TABLE $q_name;"
193 sub remove_table_constraints {
194 my ($self, $table) = @_;
195 my $name = $table->name;
196 my $q_name = $self->quote($name);
197 "IF EXISTS (SELECT name FROM sysobjects WHERE name = '$name' AND type = 'U')" .
198 " ALTER TABLE $q_name NOCHECK CONSTRAINT all;"
202 my ($self, $schema) = shift;
204 if ($self->add_drop_table) {
205 my @tables = sort { $b->order <=> $a->order } $schema->get_tables;
207 ( $self->add_comments ? (
209 '-- Turn off constraints',
213 (map $self->remove_table_constraints($_), @tables),
214 ( $self->add_comments ? (
220 (map $self->drop_table($_), @tables),
226 sub foreign_key_constraints {
227 my ($self, $schema) = @_;
228 ( map $self->foreign_key_constraint($_),
229 grep { $_->type eq FOREIGN_KEY }
230 map $_->get_constraints,
231 $schema->get_tables )
235 my ($self, $schema) = @_;
237 $self->header_comments .
238 $self->drop_tables($schema) .
239 join("\n\n", map $self->table($_), grep { $_->name } $schema->get_tables) .
240 "\n" . join "\n", $self->foreign_key_constraints($schema)
247 See the included AUTHORS file:
248 L<http://search.cpan.org/dist/SQL-Translator/AUTHORS>
252 Copyright (c) 2012 the SQL::Translator L</AUTHORS> as listed above.
256 This code is free software and may be distributed under the same terms as Perl