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 | |
e42ec4ef |
9 | our $VERSION = '0.05003'; |
fe67d343 |
10 | |
11 | =head1 NAME |
12 | |
13 | DBIx::Class::Schema::Loader::DBI::MSSQL - DBIx::Class::Schema::Loader::DBI MSSQL Implementation. |
14 | |
acfcc1fb |
15 | =head1 DESCRIPTION |
fe67d343 |
16 | |
acfcc1fb |
17 | Base driver for Microsoft SQL Server, used by |
18 | L<DBIx::Class::Schema::Loader::DBI::Sybase::Microsoft_SQL_Server> for support |
19 | via L<DBD::Sybase> and |
20 | L<DBIx::Class::Schema::Loader::DBI::ODBC::Microsoft_SQL_Server> for support via |
21 | L<DBD::ODBC>. |
fe67d343 |
22 | |
acfcc1fb |
23 | See L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base> for |
24 | usage information. |
fe67d343 |
25 | |
acfcc1fb |
26 | =cut |
fe67d343 |
27 | |
acfcc1fb |
28 | sub _tables_list { |
29 | my $self = shift; |
fe67d343 |
30 | |
acfcc1fb |
31 | my $dbh = $self->schema->storage->dbh; |
32 | my $sth = $dbh->prepare(<<'EOF'); |
020f3c3a |
33 | SELECT t.table_name |
34 | FROM information_schema.tables t |
35 | WHERE t.table_schema = ? |
acfcc1fb |
36 | EOF |
37 | $sth->execute($self->db_schema); |
fe67d343 |
38 | |
acfcc1fb |
39 | my @tables = map lc $_, map @$_, @{ $sth->fetchall_arrayref }; |
40 | |
41 | return $self->_filter_tables(@tables); |
42 | } |
fe67d343 |
43 | |
fe67d343 |
44 | sub _table_pk_info { |
45 | my ($self, $table) = @_; |
46 | my $dbh = $self->schema->storage->dbh; |
47 | my $sth = $dbh->prepare(qq{sp_pkeys '$table'}); |
48 | $sth->execute; |
49 | |
50 | my @keydata; |
51 | |
52 | while (my $row = $sth->fetchrow_hashref) { |
65f74457 |
53 | push @keydata, lc $row->{COLUMN_NAME}; |
fe67d343 |
54 | } |
55 | |
56 | return \@keydata; |
57 | } |
58 | |
59 | sub _table_fk_info { |
60 | my ($self, $table) = @_; |
61 | |
62 | my ($local_cols, $remote_cols, $remote_table, @rels); |
63 | my $dbh = $self->schema->storage->dbh; |
64 | my $sth = $dbh->prepare(qq{sp_fkeys \@FKTABLE_NAME = '$table'}); |
65 | $sth->execute; |
66 | |
67 | while (my $row = $sth->fetchrow_hashref) { |
65f74457 |
68 | my $fk = $row->{FK_NAME}; |
69 | push @{$local_cols->{$fk}}, lc $row->{FKCOLUMN_NAME}; |
70 | push @{$remote_cols->{$fk}}, lc $row->{PKCOLUMN_NAME}; |
020f3c3a |
71 | $remote_table->{$fk} = lc $row->{PKTABLE_NAME}; |
fe67d343 |
72 | } |
73 | |
74 | foreach my $fk (keys %$remote_table) { |
65f74457 |
75 | push @rels, { |
76 | local_columns => \@{$local_cols->{$fk}}, |
77 | remote_columns => \@{$remote_cols->{$fk}}, |
78 | remote_table => $remote_table->{$fk}, |
79 | }; |
fe67d343 |
80 | |
81 | } |
82 | return \@rels; |
83 | } |
84 | |
85 | sub _table_uniq_info { |
86 | my ($self, $table) = @_; |
87 | |
88 | my $dbh = $self->schema->storage->dbh; |
020f3c3a |
89 | local $dbh->{FetchHashKeyName} = 'NAME_lc'; |
90 | |
91 | my $sth = $dbh->prepare(qq{ |
92 | SELECT ccu.constraint_name, ccu.column_name |
93 | FROM information_schema.constraint_column_usage ccu |
94 | JOIN information_schema.table_constraints tc on (ccu.constraint_name = tc.constraint_name) |
95 | JOIN information_schema.key_column_usage kcu on (ccu.constraint_name = kcu.constraint_name and ccu.column_name = kcu.column_name) |
96 | wHERE lower(ccu.table_name) = @{[ $dbh->quote($table) ]} AND constraint_type = 'UNIQUE' ORDER BY kcu.ordinal_position |
97 | }); |
fe67d343 |
98 | $sth->execute; |
99 | my $constraints; |
100 | while (my $row = $sth->fetchrow_hashref) { |
020f3c3a |
101 | my $name = $row->{constraint_name}; |
102 | my $col = lc $row->{column_name}; |
fe67d343 |
103 | push @{$constraints->{$name}}, $col; |
104 | } |
105 | |
106 | my @uniqs = map { [ $_ => $constraints->{$_} ] } keys %$constraints; |
107 | return \@uniqs; |
108 | } |
109 | |
9c9197d6 |
110 | sub _columns_info_for { |
111 | my $self = shift; |
112 | my ($table) = @_; |
113 | |
114 | my $result = $self->next::method(@_); |
115 | |
116 | while (my ($col, $info) = each %$result) { |
117 | my $dbh = $self->schema->storage->dbh; |
020f3c3a |
118 | |
9c9197d6 |
119 | my $sth = $dbh->prepare(qq{ |
020f3c3a |
120 | SELECT column_name |
121 | FROM information_schema.columns |
122 | WHERE columnproperty(object_id(@{[ $dbh->quote($table) ]}, 'U'), @{[ $dbh->quote($col) ]}, 'IsIdentity') = 1 |
123 | AND lower(table_name) = @{[ $dbh->quote($table) ]} AND lower(column_name) = @{[ $dbh->quote($col) ]} |
9c9197d6 |
124 | }); |
020f3c3a |
125 | if (eval { $sth->execute; $sth->fetchrow_array }) { |
9c9197d6 |
126 | $info->{is_auto_increment} = 1; |
127 | $info->{data_type} =~ s/\s*identity//i; |
128 | delete $info->{size}; |
129 | } |
fe67d343 |
130 | |
5c6fb0a1 |
131 | # get default |
9c9197d6 |
132 | $sth = $dbh->prepare(qq{ |
020f3c3a |
133 | SELECT column_default |
134 | FROM information_schema.columns |
135 | wHERE lower(table_name) = @{[ $dbh->quote($table) ]} AND lower(column_name) = @{[ $dbh->quote($col) ]} |
9c9197d6 |
136 | }); |
020f3c3a |
137 | my ($default) = eval { $sth->execute; $sth->fetchrow_array }; |
9c9197d6 |
138 | |
139 | if (defined $default) { |
140 | # strip parens |
141 | $default =~ s/^\( (.*) \)\z/$1/x; |
142 | |
143 | # Literal strings are in ''s, numbers are in ()s (in some versions of |
144 | # MSSQL, in others they are unquoted) everything else is a function. |
145 | $info->{default_value} = |
146 | $default =~ /^['(] (.*) [)']\z/x ? $1 : |
147 | $default =~ /^\d/ ? $default : \$default; |
148 | } |
5c6fb0a1 |
149 | } |
150 | |
9c9197d6 |
151 | return $result; |
fe67d343 |
152 | } |
153 | |
fe67d343 |
154 | =head1 SEE ALSO |
155 | |
acfcc1fb |
156 | L<DBIx::Class::Schema::Loader::DBI::Sybase::Microsoft_SQL_Server>, |
157 | L<DBIx::Class::Schema::Loader::DBI::ODBC::Microsoft_SQL_Server>, |
fe67d343 |
158 | L<DBIx::Class::Schema::Loader>, L<DBIx::Class::Schema::Loader::Base>, |
159 | L<DBIx::Class::Schema::Loader::DBI> |
160 | |
161 | =head1 AUTHOR |
162 | |
9cc8e7e1 |
163 | See L<DBIx::Class::Schema::Loader/AUTHOR> and L<DBIx::Class::Schema::Loader/CONTRIBUTORS>. |
fe67d343 |
164 | |
be80bba7 |
165 | =head1 LICENSE |
0852b7b8 |
166 | |
be80bba7 |
167 | This library is free software; you can redistribute it and/or modify it under |
168 | the same terms as Perl itself. |
0852b7b8 |
169 | |
fe67d343 |
170 | =cut |
171 | |
172 | 1; |