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'; |
fe67d343 |
6 | use Carp::Clan qw/^DBIx::Class/; |
7 | use Class::C3; |
8 | |
3559ae79 |
9 | __PACKAGE__->mk_group_accessors('simple', qw/ |
10 | case_sensitive_collation |
11 | /); |
12 | |
2a8e93e9 |
13 | our $VERSION = '0.06000'; |
fe67d343 |
14 | |
15 | =head1 NAME |
16 | |
17 | DBIx::Class::Schema::Loader::DBI::MSSQL - DBIx::Class::Schema::Loader::DBI MSSQL Implementation. |
18 | |
acfcc1fb |
19 | =head1 DESCRIPTION |
fe67d343 |
20 | |
acfcc1fb |
21 | Base driver for Microsoft SQL Server, used by |
22 | L<DBIx::Class::Schema::Loader::DBI::Sybase::Microsoft_SQL_Server> for support |
23 | via L<DBD::Sybase> and |
24 | L<DBIx::Class::Schema::Loader::DBI::ODBC::Microsoft_SQL_Server> for support via |
25 | L<DBD::ODBC>. |
fe67d343 |
26 | |
acfcc1fb |
27 | See L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base> for |
28 | usage information. |
fe67d343 |
29 | |
acfcc1fb |
30 | =cut |
fe67d343 |
31 | |
3559ae79 |
32 | sub _is_case_sensitive { |
33 | my $self = shift; |
34 | |
35 | return $self->case_sensitive_collation ? 1 : 0; |
36 | } |
37 | |
38 | sub _setup { |
39 | my $self = shift; |
40 | |
41 | $self->next::method; |
42 | |
43 | my $dbh = $self->schema->storage->dbh; |
44 | |
b7a0a040 |
45 | # We use the sys.databases query for the general case, and fallback to |
46 | # databasepropertyex() if for some reason sys.databases is not available, |
47 | # which does not work over DBD::ODBC with unixODBC+FreeTDS. |
48 | # |
49 | # XXX why does databasepropertyex() not work over DBD::ODBC ? |
50 | my ($collation_name) = |
51 | eval { $dbh->selectrow_array('SELECT collation_name FROM sys.databases WHERE name = DB_NAME()') } |
52 | || eval { $dbh->selectrow_array("SELECT databasepropertyex(DB_NAME(), 'collation')") }; |
53 | |
54 | if (not $collation_name) { |
55 | $self->case_sensitive_collation(0); # most likely not |
56 | return; |
57 | } |
3559ae79 |
58 | |
59 | my ($sensitivity) = $collation_name =~ /(C\w)_[A-z]+\z/; |
60 | |
61 | $self->case_sensitive_collation($sensitivity eq 'CS' ? 1 : 0); |
62 | } |
63 | |
64 | sub _lc { |
65 | my ($self, $name) = @_; |
66 | |
67 | return $self->case_sensitive_collation ? $name : lc($name); |
68 | } |
69 | |
acfcc1fb |
70 | sub _tables_list { |
bfb43060 |
71 | my ($self, $opts) = @_; |
fe67d343 |
72 | |
acfcc1fb |
73 | my $dbh = $self->schema->storage->dbh; |
74 | my $sth = $dbh->prepare(<<'EOF'); |
020f3c3a |
75 | SELECT t.table_name |
76 | FROM information_schema.tables t |
43170884 |
77 | WHERE lower(t.table_schema) = ? |
acfcc1fb |
78 | EOF |
43170884 |
79 | $sth->execute(lc $self->db_schema); |
fe67d343 |
80 | |
bfb43060 |
81 | my @tables = map @$_, @{ $sth->fetchall_arrayref }; |
acfcc1fb |
82 | |
bfb43060 |
83 | return $self->_filter_tables(\@tables, $opts); |
acfcc1fb |
84 | } |
fe67d343 |
85 | |
fe67d343 |
86 | sub _table_pk_info { |
87 | my ($self, $table) = @_; |
88 | my $dbh = $self->schema->storage->dbh; |
89 | my $sth = $dbh->prepare(qq{sp_pkeys '$table'}); |
90 | $sth->execute; |
91 | |
92 | my @keydata; |
93 | |
94 | while (my $row = $sth->fetchrow_hashref) { |
3559ae79 |
95 | push @keydata, $self->_lc($row->{COLUMN_NAME}); |
fe67d343 |
96 | } |
97 | |
98 | return \@keydata; |
99 | } |
100 | |
101 | sub _table_fk_info { |
102 | my ($self, $table) = @_; |
103 | |
a3e05a4b |
104 | my ($local_cols, $remote_cols, $remote_table, @rels, $sth); |
fe67d343 |
105 | my $dbh = $self->schema->storage->dbh; |
a3e05a4b |
106 | eval { |
ea8d5d7c |
107 | $sth = $dbh->prepare(qq{sp_fkeys \@fktable_name = '$table'}); |
a3e05a4b |
108 | $sth->execute; |
109 | }; |
fe67d343 |
110 | |
a3e05a4b |
111 | while (my $row = eval { $sth->fetchrow_hashref }) { |
65f74457 |
112 | my $fk = $row->{FK_NAME}; |
3559ae79 |
113 | push @{$local_cols->{$fk}}, $self->_lc($row->{FKCOLUMN_NAME}); |
114 | push @{$remote_cols->{$fk}}, $self->_lc($row->{PKCOLUMN_NAME}); |
bfb43060 |
115 | $remote_table->{$fk} = $row->{PKTABLE_NAME}; |
fe67d343 |
116 | } |
117 | |
118 | foreach my $fk (keys %$remote_table) { |
65f74457 |
119 | push @rels, { |
120 | local_columns => \@{$local_cols->{$fk}}, |
121 | remote_columns => \@{$remote_cols->{$fk}}, |
122 | remote_table => $remote_table->{$fk}, |
123 | }; |
fe67d343 |
124 | |
125 | } |
126 | return \@rels; |
127 | } |
128 | |
129 | sub _table_uniq_info { |
130 | my ($self, $table) = @_; |
131 | |
132 | my $dbh = $self->schema->storage->dbh; |
020f3c3a |
133 | local $dbh->{FetchHashKeyName} = 'NAME_lc'; |
134 | |
135 | my $sth = $dbh->prepare(qq{ |
136 | SELECT ccu.constraint_name, ccu.column_name |
137 | FROM information_schema.constraint_column_usage ccu |
138 | JOIN information_schema.table_constraints tc on (ccu.constraint_name = tc.constraint_name) |
139 | JOIN information_schema.key_column_usage kcu on (ccu.constraint_name = kcu.constraint_name and ccu.column_name = kcu.column_name) |
bfb43060 |
140 | wHERE lower(ccu.table_name) = @{[ $dbh->quote(lc $table) ]} AND constraint_type = 'UNIQUE' ORDER BY kcu.ordinal_position |
020f3c3a |
141 | }); |
fe67d343 |
142 | $sth->execute; |
143 | my $constraints; |
144 | while (my $row = $sth->fetchrow_hashref) { |
020f3c3a |
145 | my $name = $row->{constraint_name}; |
3559ae79 |
146 | my $col = $self->_lc($row->{column_name}); |
fe67d343 |
147 | push @{$constraints->{$name}}, $col; |
148 | } |
149 | |
150 | my @uniqs = map { [ $_ => $constraints->{$_} ] } keys %$constraints; |
151 | return \@uniqs; |
152 | } |
153 | |
9c9197d6 |
154 | sub _columns_info_for { |
155 | my $self = shift; |
156 | my ($table) = @_; |
157 | |
158 | my $result = $self->next::method(@_); |
159 | |
160 | while (my ($col, $info) = each %$result) { |
161 | my $dbh = $self->schema->storage->dbh; |
020f3c3a |
162 | |
9c9197d6 |
163 | my $sth = $dbh->prepare(qq{ |
020f3c3a |
164 | SELECT column_name |
165 | FROM information_schema.columns |
bfb43060 |
166 | WHERE columnproperty(object_id(@{[ $dbh->quote(lc $table) ]}, 'U'), @{[ $dbh->quote(lc $col) ]}, 'IsIdentity') = 1 |
167 | AND lower(table_name) = @{[ $dbh->quote(lc $table) ]} AND lower(column_name) = @{[ $dbh->quote(lc $col) ]} |
9c9197d6 |
168 | }); |
020f3c3a |
169 | if (eval { $sth->execute; $sth->fetchrow_array }) { |
9c9197d6 |
170 | $info->{is_auto_increment} = 1; |
171 | $info->{data_type} =~ s/\s*identity//i; |
172 | delete $info->{size}; |
173 | } |
fe67d343 |
174 | |
5c6fb0a1 |
175 | # get default |
9c9197d6 |
176 | $sth = $dbh->prepare(qq{ |
020f3c3a |
177 | SELECT column_default |
178 | FROM information_schema.columns |
bfb43060 |
179 | wHERE lower(table_name) = @{[ $dbh->quote(lc $table) ]} AND lower(column_name) = @{[ $dbh->quote(lc $col) ]} |
9c9197d6 |
180 | }); |
020f3c3a |
181 | my ($default) = eval { $sth->execute; $sth->fetchrow_array }; |
9c9197d6 |
182 | |
183 | if (defined $default) { |
184 | # strip parens |
185 | $default =~ s/^\( (.*) \)\z/$1/x; |
186 | |
187 | # Literal strings are in ''s, numbers are in ()s (in some versions of |
188 | # MSSQL, in others they are unquoted) everything else is a function. |
189 | $info->{default_value} = |
190 | $default =~ /^['(] (.*) [)']\z/x ? $1 : |
191 | $default =~ /^\d/ ? $default : \$default; |
192 | } |
5c6fb0a1 |
193 | } |
194 | |
9c9197d6 |
195 | return $result; |
fe67d343 |
196 | } |
197 | |
fe67d343 |
198 | =head1 SEE ALSO |
199 | |
acfcc1fb |
200 | L<DBIx::Class::Schema::Loader::DBI::Sybase::Microsoft_SQL_Server>, |
201 | L<DBIx::Class::Schema::Loader::DBI::ODBC::Microsoft_SQL_Server>, |
fe67d343 |
202 | L<DBIx::Class::Schema::Loader>, L<DBIx::Class::Schema::Loader::Base>, |
203 | L<DBIx::Class::Schema::Loader::DBI> |
204 | |
205 | =head1 AUTHOR |
206 | |
9cc8e7e1 |
207 | See L<DBIx::Class::Schema::Loader/AUTHOR> and L<DBIx::Class::Schema::Loader/CONTRIBUTORS>. |
fe67d343 |
208 | |
be80bba7 |
209 | =head1 LICENSE |
0852b7b8 |
210 | |
be80bba7 |
211 | This library is free software; you can redistribute it and/or modify it under |
212 | the same terms as Perl itself. |
0852b7b8 |
213 | |
fe67d343 |
214 | =cut |
215 | |
216 | 1; |
bfb43060 |
217 | # vim:et sts=4 sw=4 tw=0: |