Commit | Line | Data |
fe67d343 |
1 | package DBIx::Class::Schema::Loader::DBI::MSSQL; |
2 | |
3 | use strict; |
4 | use warnings; |
de82711a |
5 | use base 'DBIx::Class::Schema::Loader::DBI::Sybase::Common'; |
942bd5e0 |
6 | use mro 'c3'; |
afcd3c32 |
7 | use Carp::Clan qw/^DBIx::Class/; |
8 | use Try::Tiny; |
9 | use namespace::clean; |
fe67d343 |
10 | |
c8845f2e |
11 | our $VERSION = '0.07009'; |
fe67d343 |
12 | |
13 | =head1 NAME |
14 | |
15 | DBIx::Class::Schema::Loader::DBI::MSSQL - DBIx::Class::Schema::Loader::DBI MSSQL Implementation. |
16 | |
acfcc1fb |
17 | =head1 DESCRIPTION |
fe67d343 |
18 | |
acfcc1fb |
19 | Base driver for Microsoft SQL Server, used by |
20 | L<DBIx::Class::Schema::Loader::DBI::Sybase::Microsoft_SQL_Server> for support |
21 | via L<DBD::Sybase> and |
22 | L<DBIx::Class::Schema::Loader::DBI::ODBC::Microsoft_SQL_Server> for support via |
23 | L<DBD::ODBC>. |
fe67d343 |
24 | |
acfcc1fb |
25 | See L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base> for |
26 | usage information. |
fe67d343 |
27 | |
b065e3df |
28 | =head1 CASE SENSITIVITY |
29 | |
30 | Most MSSQL databases use C<CI> (case-insensitive) collation, for this reason |
31 | generated column names are lower-cased as this makes them easier to work with |
32 | in L<DBIx::Class>. |
33 | |
34 | We attempt to detect the database collation at startup, and set the column |
35 | lowercasing behavior accordingly, as lower-cased column names do not work on |
36 | case-sensitive databases. |
37 | |
81ade4d9 |
38 | To manually control case-sensitive mode, put: |
b065e3df |
39 | |
bc1cb85e |
40 | preserve_case => 1|0 |
b065e3df |
41 | |
103e90da |
42 | in your Loader options. |
b065e3df |
43 | |
bc1cb85e |
44 | See L<preserve_case|DBIx::Class::Schema::Loader::Base/preserve_case>. |
fe67d343 |
45 | |
bc1cb85e |
46 | B<NOTE:> this option used to be called C<case_sensitive_collation>, but has |
47 | been renamed to a more generic option. |
3559ae79 |
48 | |
bc1cb85e |
49 | =cut |
3559ae79 |
50 | |
51 | sub _setup { |
52 | my $self = shift; |
53 | |
bc1cb85e |
54 | $self->next::method(@_); |
3559ae79 |
55 | |
bc1cb85e |
56 | return if defined $self->preserve_case; |
b065e3df |
57 | |
3559ae79 |
58 | my $dbh = $self->schema->storage->dbh; |
59 | |
b7a0a040 |
60 | # We use the sys.databases query for the general case, and fallback to |
61 | # databasepropertyex() if for some reason sys.databases is not available, |
62 | # which does not work over DBD::ODBC with unixODBC+FreeTDS. |
63 | # |
64 | # XXX why does databasepropertyex() not work over DBD::ODBC ? |
40914006 |
65 | # |
66 | # more on collations here: http://msdn.microsoft.com/en-us/library/ms143515.aspx |
b7a0a040 |
67 | my ($collation_name) = |
68 | eval { $dbh->selectrow_array('SELECT collation_name FROM sys.databases WHERE name = DB_NAME()') } |
103e90da |
69 | || eval { $dbh->selectrow_array("SELECT CAST(databasepropertyex(DB_NAME(), 'Collation') AS VARCHAR)") }; |
b7a0a040 |
70 | |
71 | if (not $collation_name) { |
b065e3df |
72 | warn <<'EOF'; |
73 | |
74 | WARNING: MSSQL Collation detection failed. Defaulting to case-insensitive mode. |
bc1cb85e |
75 | Override the 'preserve_case' attribute in your Loader options if needed. |
76 | |
77 | See 'preserve_case' in |
78 | perldoc DBIx::Class::Schema::Loader::Base |
b065e3df |
79 | EOF |
bc1cb85e |
80 | $self->preserve_case(0); |
b7a0a040 |
81 | return; |
82 | } |
3559ae79 |
83 | |
5ac72caa |
84 | my $case_sensitive = $collation_name =~ /_(?:CS|BIN2?)(?:_|\z)/; |
3559ae79 |
85 | |
bc1cb85e |
86 | $self->preserve_case($case_sensitive ? 1 : 0); |
3559ae79 |
87 | } |
88 | |
acfcc1fb |
89 | sub _tables_list { |
bfb43060 |
90 | my ($self, $opts) = @_; |
fe67d343 |
91 | |
acfcc1fb |
92 | my $dbh = $self->schema->storage->dbh; |
93 | my $sth = $dbh->prepare(<<'EOF'); |
020f3c3a |
94 | SELECT t.table_name |
060f5ecd |
95 | FROM INFORMATION_SCHEMA.TABLES t |
8c41c3ce |
96 | WHERE t.table_schema = ? |
acfcc1fb |
97 | EOF |
8c41c3ce |
98 | $sth->execute($self->db_schema); |
fe67d343 |
99 | |
bfb43060 |
100 | my @tables = map @$_, @{ $sth->fetchall_arrayref }; |
acfcc1fb |
101 | |
bfb43060 |
102 | return $self->_filter_tables(\@tables, $opts); |
acfcc1fb |
103 | } |
fe67d343 |
104 | |
fe67d343 |
105 | sub _table_pk_info { |
106 | my ($self, $table) = @_; |
107 | my $dbh = $self->schema->storage->dbh; |
108 | my $sth = $dbh->prepare(qq{sp_pkeys '$table'}); |
109 | $sth->execute; |
110 | |
111 | my @keydata; |
112 | |
113 | while (my $row = $sth->fetchrow_hashref) { |
3559ae79 |
114 | push @keydata, $self->_lc($row->{COLUMN_NAME}); |
fe67d343 |
115 | } |
116 | |
117 | return \@keydata; |
118 | } |
119 | |
120 | sub _table_fk_info { |
121 | my ($self, $table) = @_; |
122 | |
a3e05a4b |
123 | my ($local_cols, $remote_cols, $remote_table, @rels, $sth); |
fe67d343 |
124 | my $dbh = $self->schema->storage->dbh; |
a3e05a4b |
125 | eval { |
ea8d5d7c |
126 | $sth = $dbh->prepare(qq{sp_fkeys \@fktable_name = '$table'}); |
a3e05a4b |
127 | $sth->execute; |
128 | }; |
fe67d343 |
129 | |
a3e05a4b |
130 | while (my $row = eval { $sth->fetchrow_hashref }) { |
65f74457 |
131 | my $fk = $row->{FK_NAME}; |
3559ae79 |
132 | push @{$local_cols->{$fk}}, $self->_lc($row->{FKCOLUMN_NAME}); |
133 | push @{$remote_cols->{$fk}}, $self->_lc($row->{PKCOLUMN_NAME}); |
bfb43060 |
134 | $remote_table->{$fk} = $row->{PKTABLE_NAME}; |
fe67d343 |
135 | } |
136 | |
137 | foreach my $fk (keys %$remote_table) { |
65f74457 |
138 | push @rels, { |
139 | local_columns => \@{$local_cols->{$fk}}, |
140 | remote_columns => \@{$remote_cols->{$fk}}, |
141 | remote_table => $remote_table->{$fk}, |
142 | }; |
fe67d343 |
143 | |
144 | } |
145 | return \@rels; |
146 | } |
147 | |
148 | sub _table_uniq_info { |
149 | my ($self, $table) = @_; |
150 | |
151 | my $dbh = $self->schema->storage->dbh; |
020f3c3a |
152 | local $dbh->{FetchHashKeyName} = 'NAME_lc'; |
153 | |
154 | my $sth = $dbh->prepare(qq{ |
155 | SELECT ccu.constraint_name, ccu.column_name |
060f5ecd |
156 | FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu |
157 | JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on (ccu.constraint_name = tc.constraint_name) |
158 | JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on (ccu.constraint_name = kcu.constraint_name and ccu.column_name = kcu.column_name) |
8c41c3ce |
159 | wHERE ccu.table_name = @{[ $dbh->quote($table) ]} AND constraint_type = 'UNIQUE' ORDER BY kcu.ordinal_position |
020f3c3a |
160 | }); |
fe67d343 |
161 | $sth->execute; |
162 | my $constraints; |
163 | while (my $row = $sth->fetchrow_hashref) { |
020f3c3a |
164 | my $name = $row->{constraint_name}; |
3559ae79 |
165 | my $col = $self->_lc($row->{column_name}); |
fe67d343 |
166 | push @{$constraints->{$name}}, $col; |
167 | } |
168 | |
169 | my @uniqs = map { [ $_ => $constraints->{$_} ] } keys %$constraints; |
170 | return \@uniqs; |
171 | } |
172 | |
9c9197d6 |
173 | sub _columns_info_for { |
174 | my $self = shift; |
175 | my ($table) = @_; |
176 | |
177 | my $result = $self->next::method(@_); |
178 | |
afcd3c32 |
179 | my $dbh = $self->schema->storage->dbh; |
180 | |
9c9197d6 |
181 | while (my ($col, $info) = each %$result) { |
afcd3c32 |
182 | # get type info |
183 | my $sth = $dbh->prepare(qq{ |
184 | SELECT character_maximum_length, data_type, datetime_precision |
185 | FROM INFORMATION_SCHEMA.COLUMNS |
186 | WHERE table_name = @{[ $dbh->quote($table) ]} AND column_name = @{[ $dbh->quote($col) ]} |
187 | }); |
188 | $sth->execute; |
189 | my ($char_max_length, $data_type, $datetime_precision) = $sth->fetchrow_array; |
190 | |
191 | $info->{data_type} = $data_type; |
192 | |
193 | if (defined $char_max_length) { |
194 | $info->{size} = $char_max_length; |
195 | $info->{size} = 0 if $char_max_length < 0; |
196 | } |
020f3c3a |
197 | |
81ade4d9 |
198 | # find identities |
afcd3c32 |
199 | $sth = $dbh->prepare(qq{ |
020f3c3a |
200 | SELECT column_name |
060f5ecd |
201 | FROM INFORMATION_SCHEMA.COLUMNS |
8c41c3ce |
202 | WHERE columnproperty(object_id(@{[ $dbh->quote($table) ]}, 'U'), @{[ $dbh->quote($col) ]}, 'IsIdentity') = 1 |
203 | AND table_name = @{[ $dbh->quote($table) ]} AND column_name = @{[ $dbh->quote($col) ]} |
9c9197d6 |
204 | }); |
afcd3c32 |
205 | if (try { $sth->execute; $sth->fetchrow_array }) { |
9c9197d6 |
206 | $info->{is_auto_increment} = 1; |
207 | $info->{data_type} =~ s/\s*identity//i; |
208 | delete $info->{size}; |
209 | } |
fe67d343 |
210 | |
81ade4d9 |
211 | # fix types |
afcd3c32 |
212 | if ($data_type eq 'int') { |
81ade4d9 |
213 | $info->{data_type} = 'integer'; |
214 | } |
afcd3c32 |
215 | elsif ($data_type eq 'timestamp') { |
81ade4d9 |
216 | $info->{inflate_datetime} = 0; |
217 | } |
afcd3c32 |
218 | elsif ($data_type =~ /^(?:numeric|decimal)\z/) { |
81ade4d9 |
219 | if (ref($info->{size}) && $info->{size}[0] == 18 && $info->{size}[1] == 0) { |
220 | delete $info->{size}; |
221 | } |
222 | } |
afcd3c32 |
223 | elsif ($data_type eq 'float') { |
81ade4d9 |
224 | $info->{data_type} = 'double precision'; |
afcd3c32 |
225 | delete $info->{size}; |
81ade4d9 |
226 | } |
afcd3c32 |
227 | elsif ($data_type =~ /^(?:small)?datetime\z/) { |
81ade4d9 |
228 | # fixup for DBD::Sybase |
229 | if ($info->{default_value} && $info->{default_value} eq '3') { |
230 | delete $info->{default_value}; |
231 | } |
232 | } |
afcd3c32 |
233 | elsif ($data_type =~ /^(?:datetime(?:2|offset)|time)\z/) { |
ae38ed69 |
234 | $info->{size} = $datetime_precision; |
81ade4d9 |
235 | |
236 | delete $info->{size} if $info->{size} == 7; |
237 | } |
afcd3c32 |
238 | elsif ($data_type eq 'varchar' && $info->{size} == 0) { |
ae38ed69 |
239 | $info->{data_type} = 'text'; |
240 | delete $info->{size}; |
81ade4d9 |
241 | } |
afcd3c32 |
242 | elsif ($data_type eq 'nvarchar' && $info->{size} == 0) { |
ae38ed69 |
243 | $info->{data_type} = 'ntext'; |
244 | delete $info->{size}; |
245 | } |
afcd3c32 |
246 | elsif ($data_type eq 'varbinary' && $info->{size} == 0) { |
ae38ed69 |
247 | $info->{data_type} = 'image'; |
248 | delete $info->{size}; |
81ade4d9 |
249 | } |
250 | |
afcd3c32 |
251 | if ($data_type !~ /^(?:n?char|n?varchar|binary|varbinary|numeric|decimal|float|datetime(?:2|offset)|time)\z/) { |
81ade4d9 |
252 | delete $info->{size}; |
253 | } |
254 | |
5c6fb0a1 |
255 | # get default |
9c9197d6 |
256 | $sth = $dbh->prepare(qq{ |
020f3c3a |
257 | SELECT column_default |
060f5ecd |
258 | FROM INFORMATION_SCHEMA.COLUMNS |
8c41c3ce |
259 | wHERE table_name = @{[ $dbh->quote($table) ]} AND column_name = @{[ $dbh->quote($col) ]} |
9c9197d6 |
260 | }); |
020f3c3a |
261 | my ($default) = eval { $sth->execute; $sth->fetchrow_array }; |
9c9197d6 |
262 | |
263 | if (defined $default) { |
264 | # strip parens |
265 | $default =~ s/^\( (.*) \)\z/$1/x; |
266 | |
267 | # Literal strings are in ''s, numbers are in ()s (in some versions of |
268 | # MSSQL, in others they are unquoted) everything else is a function. |
269 | $info->{default_value} = |
270 | $default =~ /^['(] (.*) [)']\z/x ? $1 : |
271 | $default =~ /^\d/ ? $default : \$default; |
8a64178e |
272 | |
268cc246 |
273 | if ((eval { lc ${ $info->{default_value} } }||'') eq 'getdate()') { |
6e566cc4 |
274 | ${ $info->{default_value} } = 'current_timestamp'; |
701cd3e3 |
275 | |
276 | my $getdate = 'getdate()'; |
277 | $info->{original}{default_value} = \$getdate; |
8a64178e |
278 | } |
9c9197d6 |
279 | } |
5c6fb0a1 |
280 | } |
281 | |
9c9197d6 |
282 | return $result; |
fe67d343 |
283 | } |
284 | |
fe67d343 |
285 | =head1 SEE ALSO |
286 | |
acfcc1fb |
287 | L<DBIx::Class::Schema::Loader::DBI::Sybase::Microsoft_SQL_Server>, |
288 | L<DBIx::Class::Schema::Loader::DBI::ODBC::Microsoft_SQL_Server>, |
fe67d343 |
289 | L<DBIx::Class::Schema::Loader>, L<DBIx::Class::Schema::Loader::Base>, |
290 | L<DBIx::Class::Schema::Loader::DBI> |
291 | |
292 | =head1 AUTHOR |
293 | |
9cc8e7e1 |
294 | See L<DBIx::Class::Schema::Loader/AUTHOR> and L<DBIx::Class::Schema::Loader/CONTRIBUTORS>. |
fe67d343 |
295 | |
be80bba7 |
296 | =head1 LICENSE |
0852b7b8 |
297 | |
be80bba7 |
298 | This library is free software; you can redistribute it and/or modify it under |
299 | the same terms as Perl itself. |
0852b7b8 |
300 | |
fe67d343 |
301 | =cut |
302 | |
303 | 1; |
bfb43060 |
304 | # vim:et sts=4 sw=4 tw=0: |