1 package SQL::Translator::Parser::DBI::Sybase;
5 SQL::Translator::Parser::DBI::Sybase - parser for DBD::Sybase
9 See SQL::Translator::Parser::DBI.
13 Uses DBI Catalog Methods.
19 use SQL::Translator::Schema;
22 use vars qw[ $DEBUG $VERSION @EXPORT_OK ];
24 $DEBUG = 0 unless defined $DEBUG;
29 my ( $tr, $dbh ) = @_;
31 if ($dbh->{FetchHashKeyName} ne 'NAME_uc') {
32 warn "setting dbh attribute {FetchHashKeyName} to NAME_uc";
33 $dbh->{FetchHashKeyName} = 'NAME_uc';
36 if ($dbh->{ChopBlanks} != 1) {
37 warn "setting dbh attribute {ChopBlanks} to 1";
38 $dbh->{ChopBlanks} = 1;
41 my $schema = $tr->schema;
43 my ($sth, @tables, $columns);
48 # it is much quicker to slurp back everything all at once rather
49 # than make repeated calls
51 $sth = $dbh->column_info(undef, undef, undef, undef);
54 foreach my $c (@{$sth->fetchall_arrayref({})}) {
60 ->{$c->{COLUMN_NAME}}= $c;
65 # Get a list of the tables and views.
66 $sth = $dbh->table_info();
67 @tables = @{$sth->fetchall_arrayref({})};
69 my $h = $dbh->selectall_arrayref(q{
70 SELECT o.name, colid,colid2,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} .= $_->[3];
90 #### objects with indexes.
92 $stuff->{indexes}->{$_->[0]}++
94 } @{$dbh->selectall_arrayref("SELECT DISTINCT object_name(id) AS name
100 $stuff->{$_->[1]}->{$_->[0]} = $_;
101 } @{$dbh->selectall_arrayref("SELECT name,type, id FROM sysobjects")};
106 # This gets legitimate procedures by used the 'supported' API: sp_stored_procedures
108 my $n = $_->{PROCEDURE_NAME};
109 $n =~ s/;\d+$//; # Ignore versions for now
111 $stuff->{procedures}->{$n} = $_;
112 } values %{$dbh->selectall_hashref("sp_stored_procedures", 'PROCEDURE_NAME')};
115 # And this blasts in the text of 'legit' stored procedures. Do
116 # this rather than calling sp_helptext in a loop.
118 $h = $dbh->selectall_arrayref(q{
119 SELECT o.name, colid,colid2,c.text
131 $stuff->{procedures}->{$_->[0]}->{text} .= $_->[3]
132 if (defined($stuff->{procedures}->{$_->[0]}));
141 # Since the 'target' of the trigger is defined in the text, we will
142 # just create them independently for now rather than associating them
145 $h = $dbh->selectall_arrayref(q{
146 SELECT o.name, colid,colid2,c.text
151 ON (o.id = o1.instrig OR o.id = o1.deltrig or o.id = o1.updtrig)
159 $stuff->{triggers}->{$_->[0]}->{text} .= $_->[3];
166 # Not sure what to do with these?
167 $stuff->{type_info_all} = $dbh->type_info_all;
170 # According to the DBI docs, these can be
175 # "GLOBAL TEMPORARY",
180 foreach my $table_info (@tables) {
182 unless (defined($table_info->{TABLE_TYPE}));
184 if ($table_info->{TABLE_TYPE} =~ /TABLE/) {
185 my $table = $schema->add_table(
187 $table_info->{TABLE_NAME},
189 $table_info->{TABLE_TYPE},
190 ) || die $schema->error;
192 # find the associated columns
195 $columns->{$table_info->{TABLE_QUALIFIER}}
196 ->{$table_info->{TABLE_OWNER}}
197 ->{$table_info->{TABLE_NAME}}
200 foreach my $c (values %{$cols}) {
201 my $f = $table->add_field(
202 name => $c->{COLUMN_NAME},
203 data_type => $c->{TYPE_NAME},
204 order => $c->{ORDINAL_POSITION},
205 size => $c->{COLUMN_SIZE},
206 ) || die $table->error;
209 if ($c->{NULLABLE} == 1);
213 my $h = $dbh->selectall_hashref("sp_pkeys
214 $table_info->{TABLE_NAME}", 'COLUMN_NAME');
215 if (scalar keys %{$h} > 1) {
219 $a->{KEY_SEQ} <=> $b->{KEY_SEQ}
222 $table->primary_key(@c)
226 # add in any indexes ... how do we tell if the index has
227 # already been created as part of a primary key or other
230 if (defined($stuff->{indexes}->{$table_info->{TABLE_NAME}})){
231 my $h = $dbh->selectall_hashref("sp_helpindex
232 $table_info->{TABLE_NAME}", 'INDEX_NAME');
233 foreach (values %{$h}) {
234 my $fields = $_->{'INDEX_KEYS'};
236 my $i = $table->add_index(
241 if ($_->{'INDEX_DESCRIPTION'} =~ /unique/i) {
244 # we could make this a primary key if there
245 # isn't already one defined and if there
246 # aren't any nullable columns in thisindex.
248 if (!defined($table->primary_key())) {
249 $table->primary_key($fields)
251 $table->get_field($_)->is_nullable()
252 } split(/,\s*/, $fields);
257 } elsif ($table_info->{TABLE_TYPE} eq 'VIEW') {
258 my $view = $schema->add_view(
260 $table_info->{TABLE_NAME},
265 $columns->{$table_info->{TABLE_QUALIFIER}}
266 ->{$table_info->{TABLE_OWNER}}
267 ->{$table_info->{TABLE_NAME}}
273 $a->{ORDINAL_POSITION} <=> $b->{ORDINAL_POSITION}
277 $view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text})
278 if (defined($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}));
282 foreach my $p (values %{$stuff->{procedures}}) {
283 my $proc = $schema->add_procedure(
285 owner => $p->{PROCEDURE_OWNER},
286 comments => $p->{REMARKS},
306 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
310 DBI, DBD::Sybase, SQL::Translator::Schema.