Commit | Line | Data |
1ea76bff |
1 | package SQL::Translator::Generator::DDL::SQLServer; |
2 | |
3 | use Moo; |
4 | use SQL::Translator::Generator::Utils; |
7a16a53e |
5 | use SQL::Translator::Schema::Constants; |
1ea76bff |
6 | |
7 | with 'SQL::Translator::Generator::Role::DDL'; |
8 | |
9 | sub _build_shim { SQL::Translator::Generator::Utils->new( quote_chars => [qw( [ ] )] ) } |
10 | |
f699ffaa |
11 | sub _build_numeric_types { |
12 | +{ |
13 | int => 1, |
14 | } |
15 | } |
16 | |
17 | sub _build_unquoted_defaults { |
18 | +{ |
19 | NULL => 1, |
20 | } |
21 | } |
22 | |
1ea76bff |
23 | sub _build_type_map { |
24 | +{ |
25 | date => 'datetime', |
26 | 'time' => 'datetime', |
27 | } |
28 | } |
29 | |
30 | has sizeless_types => ( |
31 | is => 'ro', |
32 | builder => '_build_sizeless_types', |
33 | ); |
34 | |
35 | sub _build_sizeless_types { |
36 | +{ map { $_ => 1 } |
37 | qw( tinyint smallint int integer bigint text bit image datetime ) } |
38 | } |
39 | |
40 | sub field { |
41 | my ($self, $field) = @_; |
42 | |
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), |
47 | } |
48 | |
49 | sub field_type_size { |
50 | my ($self, $field) = @_; |
51 | |
52 | ($field->size && !$self->sizeless_types->{$field->data_type} |
53 | ? '(' . $field->size . ')' |
54 | : '' |
55 | ) |
56 | } |
57 | |
58 | sub field_autoinc { ( $_[1]->is_auto_increment ? 'IDENTITY' : () ) } |
59 | |
e6fcfabf |
60 | sub primary_key_constraint { |
61 | 'CONSTRAINT ' . |
38d0ddf0 |
62 | $_[0]->quote($_[1]->name || $_[1]->table->name . '_pk') . |
e6fcfabf |
63 | ' PRIMARY KEY (' . |
38d0ddf0 |
64 | join( ', ', map $_[0]->quote($_), $_[1]->fields ) . |
e6fcfabf |
65 | ')' |
66 | } |
67 | |
68 | sub index { |
69 | 'CREATE INDEX ' . |
38d0ddf0 |
70 | $_[0]->quote($_[1]->name || $_[1]->table->name . '_idx') . |
71 | ' ON ' . $_[0]->quote($_[1]->table->name) . |
72 | ' (' . join( ', ', map $_[0]->quote($_), $_[1]->fields ) . ');' |
e6fcfabf |
73 | } |
74 | |
2363a629 |
75 | sub unique_constraint_single { |
76 | my ($self, $constraint) = @_; |
77 | |
78 | 'CONSTRAINT ' . |
79 | $self->unique_constraint_name($constraint) . |
38d0ddf0 |
80 | ' UNIQUE (' . join( ', ', map $self->quote($_), $constraint->fields ) . ')' |
2363a629 |
81 | } |
82 | |
83 | sub unique_constraint_name { |
84 | my ($self, $constraint) = @_; |
38d0ddf0 |
85 | $self->quote($constraint->name || $constraint->table->name . '_uc' ) |
2363a629 |
86 | } |
87 | |
88 | sub unique_constraint_multiple { |
89 | my ($self, $constraint) = @_; |
90 | |
91 | 'CREATE UNIQUE NONCLUSTERED INDEX ' . |
92 | $self->unique_constraint_name($constraint) . |
38d0ddf0 |
93 | ' ON ' . $self->quote($constraint->table->name) . ' (' . |
2363a629 |
94 | join( ', ', $constraint->fields ) . ')' . |
95 | ' WHERE ' . join( ' AND ', |
38d0ddf0 |
96 | map $self->quote($_->name) . ' IS NOT NULL', |
2363a629 |
97 | grep { $_->is_nullable } $constraint->fields ) . ';' |
98 | } |
99 | |
38d0ddf0 |
100 | sub foreign_key_constraint { |
101 | my ($self, $constraint) = @_; |
102 | |
103 | my $on_delete = uc ($constraint->on_delete || ''); |
104 | my $on_update = uc ($constraint->on_update || ''); |
105 | |
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' |
110 | } |
111 | |
112 | 'ALTER TABLE ' . $self->quote($constraint->table->name) . |
113 | ' ADD CONSTRAINT ' . |
114 | $self->quote($constraint->name || $constraint->table->name . '_fk') . |
115 | ' FOREIGN KEY' . |
116 | ' (' . join( ', ', map $self->quote($_), $constraint->fields ) . ') REFERENCES '. |
117 | $self->quote($constraint->reference_table) . |
118 | ' (' . join( ', ', map $self->quote($_), $constraint->reference_fields ) . ')' |
119 | . ( |
120 | $on_delete && $on_delete ne "NO ACTION" |
121 | ? ' ON DELETE ' . $on_delete |
122 | : '' |
123 | ) . ( |
124 | $on_update && $on_update ne "NO ACTION" |
125 | ? ' ON UPDATE ' . $on_update |
126 | : '' |
127 | ) . ';'; |
128 | } |
129 | |
280d92ba |
130 | sub enum_constraint_name { |
131 | my ($self, $field_name) = @_; |
132 | $self->quote($field_name . '_chk' ) |
133 | } |
134 | |
135 | sub enum_constraint { |
136 | my ( $self, $field_name, $vals ) = @_; |
137 | |
138 | return ( |
139 | 'CONSTRAINT ' . $self->enum_constraint_name($field_name) . |
140 | ' CHECK (' . $self->quote($field_name) . |
141 | ' IN (' . join( ',', map qq('$_'), @$vals ) . '))' |
142 | ) |
143 | } |
144 | |
2ce8cf9b |
145 | sub constraints { |
146 | my ($self, $table) = @_; |
147 | |
148 | (map $self->enum_constraint($_->name, { $_->extra }->{list} || []), |
149 | grep { 'enum' eq lc $_->data_type } $table->get_fields), |
150 | |
151 | (map $self->primary_key_constraint($_), |
152 | grep { $_->type eq PRIMARY_KEY } $table->get_constraints), |
153 | |
154 | (map $self->unique_constraint_single($_), |
155 | grep { |
156 | $_->type eq UNIQUE && |
157 | !grep { $_->is_nullable } $_->fields |
158 | } $table->get_constraints), |
159 | } |
160 | |
7a16a53e |
161 | sub table { |
162 | my ($self, $table) = @_; |
163 | 'CREATE TABLE ' . $self->quote($table->name) . " (\n". |
164 | join( ",\n", |
165 | map { " $_" } |
2ce8cf9b |
166 | $self->fields($table), |
167 | $self->constraints($table), |
7a16a53e |
168 | ) . |
169 | "\n);", |
170 | } |
171 | |
11bfa991 |
172 | sub drop_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')" . |
9b76e208 |
177 | " DROP TABLE $q_name;" |
11bfa991 |
178 | } |
179 | |
97a16cf4 |
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')" . |
9b76e208 |
185 | " ALTER TABLE $q_name NOCHECK CONSTRAINT all;" |
97a16cf4 |
186 | } |
187 | |
1ea76bff |
188 | 1; |
189 | |