1 package SQL::Translator::Parser::DBI::Sybase;
3 # -------------------------------------------------------------------
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-2009 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
25 SQL::Translator::Parser::DBI::Sybase - parser for DBD::Sybase
29 See SQL::Translator::Parser::DBI.
33 Uses DBI Catalog Methods.
39 use SQL::Translator::Schema;
42 use vars qw[ $DEBUG @EXPORT_OK ];
43 $DEBUG = 0 unless defined $DEBUG;
47 # -------------------------------------------------------------------
49 my ( $tr, $dbh ) = @_;
51 if ($dbh->{FetchHashKeyName} ne 'NAME_uc') {
52 warn "setting dbh attribute {FetchHashKeyName} to NAME_uc";
53 $dbh->{FetchHashKeyName} = 'NAME_uc';
56 if ($dbh->{ChopBlanks} != 1) {
57 warn "setting dbh attribute {ChopBlanks} to 1";
58 $dbh->{ChopBlanks} = 1;
61 my $schema = $tr->schema;
63 my ($sth, @tables, $columns);
68 # it is much quicker to slurp back everything all at once rather
69 # than make repeated calls
71 $sth = $dbh->column_info(undef, undef, undef, undef);
74 foreach my $c (@{$sth->fetchall_arrayref({})}) {
80 ->{$c->{COLUMN_NAME}}= $c;
85 # Get a list of the tables and views.
86 $sth = $dbh->table_info();
87 @tables = @{$sth->fetchall_arrayref({})};
89 my $h = $dbh->selectall_arrayref(q{
90 SELECT o.name, colid,colid2,c.text
102 # I had always thought there was something 'hard' about
103 # reconstructing text from syscomments ..
104 # this seems to work fine and is certainly not complicated!
107 $stuff->{view}->{$_->[0]}->{text} .= $_->[3];
110 #### objects with indexes.
112 $stuff->{indexes}->{$_->[0]}++
114 } @{$dbh->selectall_arrayref("SELECT DISTINCT object_name(id) AS name
120 $stuff->{$_->[1]}->{$_->[0]} = $_;
121 } @{$dbh->selectall_arrayref("SELECT name,type, id FROM sysobjects")};
126 # This gets legitimate procedures by used the 'supported' API: sp_stored_procedures
128 my $n = $_->{PROCEDURE_NAME};
129 $n =~ s/;\d+$//; # Ignore versions for now
131 $stuff->{procedures}->{$n} = $_;
132 } values %{$dbh->selectall_hashref("sp_stored_procedures", 'PROCEDURE_NAME')};
135 # And this blasts in the text of 'legit' stored procedures. Do
136 # this rather than calling sp_helptext in a loop.
138 $h = $dbh->selectall_arrayref(q{
139 SELECT o.name, colid,colid2,c.text
151 $stuff->{procedures}->{$_->[0]}->{text} .= $_->[3]
152 if (defined($stuff->{procedures}->{$_->[0]}));
161 # Since the 'target' of the trigger is defined in the text, we will
162 # just create them independently for now rather than associating them
165 $h = $dbh->selectall_arrayref(q{
166 SELECT o.name, colid,colid2,c.text
171 ON (o.id = o1.instrig OR o.id = o1.deltrig or o.id = o1.updtrig)
179 $stuff->{triggers}->{$_->[0]}->{text} .= $_->[3];
186 # Not sure what to do with these?
187 $stuff->{type_info_all} = $dbh->type_info_all;
190 # According to the DBI docs, these can be
195 # "GLOBAL TEMPORARY",
200 foreach my $table_info (@tables) {
202 unless (defined($table_info->{TABLE_TYPE}));
204 if ($table_info->{TABLE_TYPE} =~ /TABLE/) {
205 my $table = $schema->add_table(
207 $table_info->{TABLE_NAME},
209 $table_info->{TABLE_TYPE},
210 ) || die $schema->error;
212 # find the associated columns
215 $columns->{$table_info->{TABLE_QUALIFIER}}
216 ->{$table_info->{TABLE_OWNER}}
217 ->{$table_info->{TABLE_NAME}}
220 foreach my $c (values %{$cols}) {
221 my $f = $table->add_field(
222 name => $c->{COLUMN_NAME},
223 data_type => $c->{TYPE_NAME},
224 order => $c->{ORDINAL_POSITION},
225 size => $c->{COLUMN_SIZE},
226 ) || die $table->error;
229 if ($c->{NULLABLE} == 1);
233 my $h = $dbh->selectall_hashref("sp_pkeys
234 $table_info->{TABLE_NAME}", 'COLUMN_NAME');
235 if (scalar keys %{$h} > 1) {
239 $a->{KEY_SEQ} <=> $b->{KEY_SEQ}
242 $table->primary_key(@c)
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 my $view = $schema->add_view(
280 $table_info->{TABLE_NAME},
285 $columns->{$table_info->{TABLE_QUALIFIER}}
286 ->{$table_info->{TABLE_OWNER}}
287 ->{$table_info->{TABLE_NAME}}
293 $a->{ORDINAL_POSITION} <=> $b->{ORDINAL_POSITION}
297 $view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text})
298 if (defined($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}));
302 foreach my $p (values %{$stuff->{procedures}}) {
303 my $proc = $schema->add_procedure(
305 owner => $p->{PROCEDURE_OWNER},
306 comments => $p->{REMARKS},
322 # -------------------------------------------------------------------
328 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
332 DBI, DBD::Sybase, SQL::Translator::Schema.