1 package SQL::Translator::Parser::DBI::SQLServer;
5 SQL::Translator::Parser::DBI::SQLServer - parser for SQL Server through DBD::ODBC
9 See SQL::Translator::Parser::DBI.
13 Uses DBI Catalog Methods.
20 use SQL::Translator::Schema;
23 our ( $DEBUG, @EXPORT_OK );
24 our $VERSION = '1.59';
25 $DEBUG = 0 unless defined $DEBUG;
30 my ( $tr, $dbh ) = @_;
32 if ($dbh->{FetchHashKeyName} ne 'NAME_uc') {
33 warn "setting dbh attribute {FetchHashKeyName} to NAME_uc";
34 $dbh->{FetchHashKeyName} = 'NAME_uc';
37 if ($dbh->{ChopBlanks} != 1) {
38 warn "setting dbh attribute {ChopBlanks} to 1";
39 $dbh->{ChopBlanks} = 1;
42 my $schema = $tr->schema;
44 my ($sth, @tables, $columns);
49 # it is much quicker to slurp back everything all at once rather
50 # than make repeated calls
52 $sth = $dbh->column_info(undef, undef, undef, undef);
55 foreach my $c (@{$sth->fetchall_arrayref({})}) {
61 ->{$c->{COLUMN_NAME}}= $c;
66 # Get a list of the tables and views.
67 $sth = $dbh->table_info();
68 @tables = @{$sth->fetchall_arrayref({})};
70 my $h = $dbh->selectall_arrayref(q{
71 SELECT o.name, colid,c.text
82 # I had always thought there was something 'hard' about
83 # reconstructing text from syscomments ..
84 # this seems to work fine and is certainly not complicated!
87 $stuff->{view}->{$_->[0]}->{text} .= $_->[2];
90 #### objects with indexes.
92 $stuff->{indexes}->{$_->[0]}++
94 } @{$dbh->selectall_arrayref("SELECT DISTINCT object_name(id)
96 WHERE indid > 0 and indid < 255 and
97 name not like '_WA_Sys%'")};
101 $stuff->{$_->[1]}->{$_->[0]} = $_;
102 } @{$dbh->selectall_arrayref("SELECT name,type, id FROM sysobjects")};
107 # This gets legitimate procedures by used the 'supported' API: sp_stored_procedures
109 my $n = $_->{PROCEDURE_NAME};
110 $n =~ s/;\d+$//; # Ignore versions for now
112 $stuff->{procedures}->{$n} = $_;
113 } values %{$dbh->selectall_hashref("sp_stored_procedures", 'PROCEDURE_NAME')};
116 # And this blasts in the text of 'legit' stored procedures. Do
117 # this rather than calling sp_helptext in a loop.
119 $h = $dbh->selectall_arrayref(q{
120 SELECT o.name, colid,c.text
124 WHERE o.type in ('P', 'FN', 'TF', 'IF')
129 $stuff->{procedures}->{$_->[0]}->{text} .= $_->[2]
130 if (defined($stuff->{procedures}->{$_->[0]}));
139 # Since the 'target' of the trigger is defined in the text, we will
140 # just create them independently for now rather than associating them
143 $h = $dbh->selectall_arrayref(q{
144 SELECT o.name, colid,c.text
149 ON (o.id = o1.instrig OR o.id = o1.deltrig or o.id = o1.updtrig)
156 $stuff->{triggers}->{$_->[0]}->{text} .= $_->[2];
163 # Not sure what to do with these?
164 $stuff->{type_info_all} = $dbh->type_info_all;
167 # According to the DBI docs, these can be
172 # "GLOBAL TEMPORARY",
177 foreach my $table_info (@tables) {
179 unless (defined($table_info->{TABLE_TYPE}));
181 if ($table_info->{TABLE_TYPE} eq "TABLE") {
182 my $table = $schema->add_table(
184 $table_info->{TABLE_NAME},
186 $table_info->{TABLE_TYPE},
187 ) || die $schema->error;
189 # find the associated columns
192 $columns->{$table_info->{TABLE_CAT}}
193 ->{$table_info->{TABLE_SCHEM}}
194 ->{$table_info->{TABLE_NAME}}
197 foreach my $c (values %{$cols}) {
198 my $is_auto_increment = $c->{TYPE_NAME} =~ s#(\(\))? identity##i;
199 my $f = $table->add_field(
200 name => $c->{COLUMN_NAME},
201 data_type => $c->{TYPE_NAME},
202 order => $c->{ORDINAL_POSITION},
203 size => $c->{COLUMN_SIZE},
204 ) || die $table->error;
205 $f->is_nullable($c->{NULLABLE} == 1);
206 $f->is_auto_increment($is_auto_increment);
207 if ( defined $c->{COLUMN_DEF}) {
208 $c->{COLUMN_DEF} =~ s#\('?(.*?)'?\)#$1#;
209 $f->default_value($c->{COLUMN_DEF});
214 my $h = $dbh->selectall_hashref("sp_pkeys
215 $table_info->{TABLE_NAME}", 'COLUMN_NAME');
216 if (scalar keys %{$h} >= 1) {
220 $a->{KEY_SEQ} <=> $b->{KEY_SEQ}
223 $table->primary_key(@c)
227 # add in foreign keys
228 $h = $dbh->selectall_hashref("sp_fkeys NULL,
229 \@fktable_name = '$table_info->{TABLE_NAME}'", 'FK_NAME');
230 foreach my $fk ( values %{$h} ) {
231 my $constraint = $table->add_constraint( name => $fk->{FK_NAME},
232 fields => [$fk->{FKCOLUMN_NAME}],
234 $constraint->type("FOREIGN_KEY");
235 $constraint->on_delete(
236 $fk->{DELETE_RULE} == 0 ? "CASCADE" :
237 $fk->{DELETE_RULE} == 1 ? "NO ACTION" : "SET_NULL"
239 $constraint->on_update(
240 $fk->{UPDATE_RULE} == 0 ? "CASCADE" :
241 $fk->{UPDATE_RULE} == 1 ? "NO ACTION" : "SET_NULL"
243 $constraint->reference_table($fk->{PKTABLE_NAME});
246 # add in any indexes ... how do we tell if the index has
247 # already been created as part of a primary key or other
250 if (defined($stuff->{indexes}->{$table_info->{TABLE_NAME}})){
251 my $h = $dbh->selectall_hashref("sp_helpindex
252 $table_info->{TABLE_NAME}", 'INDEX_NAME');
253 foreach (values %{$h}) {
254 my $fields = $_->{'INDEX_KEYS'};
256 my $i = $table->add_index(
261 if ($_->{'INDEX_DESCRIPTION'} =~ /unique/i) {
264 # we could make this a primary key if there
265 # isn't already one defined and if there
266 # aren't any nullable columns in thisindex.
268 if (!defined($table->primary_key())) {
269 $table->primary_key($fields)
271 $table->get_field($_)->is_nullable()
272 } split(/,\s*/, $fields);
277 } elsif ($table_info->{TABLE_TYPE} eq 'VIEW') {
278 next if $table_info->{TABLE_NAME} eq 'sysconstraints'
279 || $table_info->{TABLE_NAME} eq 'syssegments';
280 next if !$stuff->{view}->{$table_info->{TABLE_NAME}}->{text};
281 my $view = $schema->add_view(
283 $table_info->{TABLE_NAME},
288 $columns->{$table_info->{TABLE_CAT}}
289 ->{$table_info->{TABLE_SCHEM}}
290 ->{$table_info->{TABLE_NAME}}
296 $a->{ORDINAL_POSITION} <=> $b->{ORDINAL_POSITION}
300 $view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text})
301 if (defined($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}));
305 foreach my $p (values %{$stuff->{procedures}}) {
307 my $proc = $schema->add_procedure(
309 owner => $p->{PROCEDURE_OWNER},
310 comments => $p->{REMARKS},
330 Chris Hilton E<lt>chris@dctank.comE<gt> - Bulk of code from
331 DBI-Sybase parser, I just tweaked it for SQLServer. Thanks.
335 DBI, DBD::ODBC, SQL::Translator::Schema.