lib/SQL/Translator/Parser/DBI/Sybase.pm

   1 package SQL::Translator::Parser::DBI::Sybase;
   2
   3 # $Id: Sybase.pm,v 1.2 2003-10-03 20:56:40 kycl4rk Exp $
   4
   5 =head1 NAME
   6
   7 SQL::Translator::Parser::DBI::Sybase - parser for DBD::Sybase
   8
   9 =head1 SYNOPSIS
  10
  11 See SQL::Translator::Parser::DBI.
  12
  13 =head1 DESCRIPTION
  14
  15 Uses DBI Catalog Methods.
  16
  17 =cut
  18
  19 use strict;
  20 use DBI;
  21 use SQL::Translator::Schema;
  22 use Data::Dumper;
  23
  24 use vars qw[ $DEBUG $VERSION @EXPORT_OK ];
  25 $VERSION = sprintf "%d.%02d", q$Revision: 1.2 $ =~ /(\d+)\.(\d+)/;
  26 $DEBUG   = 0 unless defined $DEBUG;
  27
  28 no strict 'refs';
  29
  30 # -------------------------------------------------------------------
  31 sub parse {
  32     my ( $tr, $dbh ) = @_;
  33
  34     if ($dbh->{FetchHashKeyName} ne 'NAME_uc') {
  35         warn "setting dbh attribute {FetchHashKeyName} to NAME_uc";
  36         $dbh->{FetchHashKeyName} = 'NAME_uc';
  37     }
  38
  39     if ($dbh->{ChopBlanks} != 1) {
  40         warn "setting dbh attribute {ChopBlanks} to 1";
  41         $dbh->{ChopBlanks} = 1;
  42     }
  43
  44     my $schema = $tr->schema;
  45
  46     my ($sth, @tables, $columns);
  47     my $stuff;
  48
  49     ### Columns
  50
  51     # it is much quicker to slurp back everything all at once rather
  52     # than make repeated calls
  53
  54     $sth = $dbh->column_info();
  55
  56
  57     foreach my $c (@{$sth->fetchall_arrayref({})}) {
  58         $columns
  59             ->{$c->{TABLE_CAT}}
  60                 ->{$c->{TABLE_SCHEM}}
  61                     ->{$c->{TABLE_NAME}}
  62                         ->{columns}
  63                             ->{$c->{COLUMN_NAME}}= $c;
  64     }
  65
  66     ### Tables and views
  67
  68     # Get a list of the tables and views.
  69     $sth = $dbh->table_info();
  70     @tables   = @{$sth->fetchall_arrayref({})};
  71
  72     my $h = $dbh->selectall_arrayref(q{
  73 SELECT o.name, colid,colid2,c.text
  74   FROM syscomments c
  75   JOIN sysobjects o
  76     ON c.id = o.id
  77  WHERE o.type ='V'
  78 ORDER BY o.name,
  79          c.colid,
  80          c.colid2
  81 }
  82 );
  83
  84     # View text
  85     # I had always thought there was something 'hard' about
  86     # reconstructing text from syscomments ..
  87     # this seems to work fine and is certainly not complicated!
  88
  89     foreach (@{$h}) {
  90         $stuff->{view}->{$_->[0]}->{text} .= $_->[3];
  91     }
  92
  93     #### objects with indexes.
  94     map {
  95         $stuff->{indexes}->{$_->[0]}++
  96             if defined;
  97     } @{$dbh->selectall_arrayref("SELECT DISTINCT object_name(id) AS
  98 name
  99                                         FROM sysindexes
 100                                        WHERE indid > 0")};
 101
 102
 103     ## slurp objects
 104     map {
 105         $stuff->{$_->[1]}->{$_->[0]} = $_;
 106     } @{$dbh->selectall_arrayref("SELECT name,type, id FROM
 107 sysobjects")};
 108
 109
 110     ### Procedures
 111     ### Defaults
 112     ### Rules
 113     ### Bind Defaults
 114     ### Bind Rules
 115
 116     ### Triggers
 117
 118     ### References
 119     ### Keys
 120
 121     ### Types
 122
 123     $stuff->{type_info_all} = $dbh->type_info_all;
 124
 125     ### Tables
 126     # According to the DBI docs, these can be
 127
 128     # "TABLE"
 129     # "VIEW"
 130     # "SYSTEM TABLE"
 131     # "GLOBAL TEMPORARY",
 132     # "LOCAL TEMPORARY"
 133     # "ALIAS"
 134     # "SYNONYM"
 135
 136     foreach my $table_info (@tables) {
 137         next
 138             unless (defined($table_info->{TABLE_TYPE}));
 139
 140         if ($table_info->{TABLE_TYPE} =~ /TABLE/) {
 141             my $table = $schema->add_table(
 142                                            name =>
 143 $table_info->{TABLE_NAME},
 144                                            type =>
 145 $table_info->{TABLE_TYPE},
 146                                           ) || die $schema->error;
 147
 148             # find the associated columns
 149
 150             my $cols =
 151                 $columns->{$table_info->{TABLE_QUALIFIER}}
 152                     ->{$table_info->{TABLE_OWNER}}
 153                         ->{$table_info->{TABLE_NAME}}
 154                             ->{columns};
 155
 156             foreach my $c (values %{$cols}) {
 157                 my $f = $table->add_field(name        =>
 158 $c->{COLUMN_NAME},
 159                                           data_type   =>
 160 $c->{TYPE_NAME},
 161                                           order       =>
 162 $c->{ORDINAL_POSITION},
 163                                           size        =>
 164 $c->{COLUMN_SIZE},
 165                                          ) || die $table->error;
 166                 $f->is_nullable(1)
 167                     if ($c->{NULLABLE} == 1);
 168             }
 169
 170             # add in primary key
 171             my $h = $dbh->selectall_hashref("sp_pkeys
 172 $table_info->{TABLE_NAME}", 'COLUMN_NAME');
 173             if (scalar keys %{$h} > 1) {
 174                 my @c = map {
 175                     $_->{COLUMN_NAME}
 176                 } sort {
 177                     $a->{KEY_SEQ} <=> $b->{KEY_SEQ}
 178                 } values %{$h};
 179
 180                 $table->primary_key(@c)
 181                     if (scalar @c);
 182             }
 183
 184             # add in any indexes ... how do we tell if the index has
 185             # already been created as part of a primary key or other
 186             # constraint?
 187
 188             if (defined($stuff->{indexes}->{$table_info->{TABLE_NAME}}))
 189 {
 190                 my $h = $dbh->selectall_hashref("sp_helpindex
 191 $table_info->{TABLE_NAME}", 'INDEX_NAME');
 192                 foreach (values %{$h}) {
 193                     my $fields = $_->{'INDEX_KEYS'};
 194                     $fields =~ s/\s*//g;
 195                     my $i = $table->add_index(
 196                                               name   =>
 197 $_->{INDEX_NAME},
 198                                               fields => $fields,
 199                                              );
 200                     if ($_->{'INDEX_DESCRIPTION'} =~ /unique/i) {
 201                         $i->type('unique');
 202
 203                         # we could make this a primary key if there
 204                         # isn't already one defined and if there
 205                         # aren't any nullable columns in thisindex.
 206
 207                         if (!defined($table->primary_key())) {
 208                             $table->primary_key($fields)
 209                                 unless grep {
 210                                     $table->get_field($_)->is_nullable()
 211                                 } split(/,\s*/, $fields);
 212                         }
 213                     }
 214                 }
 215             }
 216         } elsif ($table_info->{TABLE_TYPE} eq 'VIEW') {
 217             my $view =  $schema->add_view(
 218                                           name =>
 219 $table_info->{TABLE_NAME},
 220                                           );
 221
 222             my $cols =
 223                 $columns->{$table_info->{TABLE_QUALIFIER}}
 224                     ->{$table_info->{TABLE_OWNER}}
 225                         ->{$table_info->{TABLE_NAME}}
 226                             ->{columns};
 227
 228             $view->fields(map {
 229                 $_->{COLUMN_NAME}
 230             } sort {
 231                 $a->{ORDINAL_POSITION} <=> $b->{ORDINAL_POSITION}
 232                 } values %{$cols}
 233                          );
 234
 235
 236 $view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text})
 237                 if
 238 (defined($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}));
 239         }
 240     }
 241     ### Permissions
 242     ### Groups
 243     ### Users
 244     ### Aliases
 245     ### Logins
 246     return 1;
 247 }
 248
 249 1;
 250
 251 =pod
 252
 253 =head1 AUTHOR
 254
 255 Paul Harrington E<lt>harringp@deshaw.comE<gt>,
 256
 257 =head1 SEE ALSO
 258
 259 DBI, DBD::Sybase, SQL::Translator::Schema.
 260
 261 =cut
 262