X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FParser%2FDBI%2FSybase.pm;h=d800fe0271f31e887a6718f515ca255210faa11f;hb=4ab3763d2ad756c236b757306989cafa08e7f35e;hp=f6a86bfc2507452895d5a3bc34ef1633ba8fec79;hpb=86ee0658cdecb71d7871b0e5df8e40fdf67b76cf;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Parser/DBI/Sybase.pm b/lib/SQL/Translator/Parser/DBI/Sybase.pm index f6a86bf..d800fe0 100644 --- a/lib/SQL/Translator/Parser/DBI/Sybase.pm +++ b/lib/SQL/Translator/Parser/DBI/Sybase.pm @@ -1,6 +1,22 @@ package SQL::Translator::Parser::DBI::Sybase; -# $Id: Sybase.pm,v 1.1 2003-10-03 20:48:38 kycl4rk Exp $ +# ------------------------------------------------------------------- +# Copyright (C) 2002-2009 SQLFairy Authors +# +# This program is free software; you can redistribute it and/or +# modify it under the terms of the GNU General Public License as +# published by the Free Software Foundation; version 2. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA +# 02111-1307 USA +# ------------------------------------------------------------------- =head1 NAME @@ -22,7 +38,7 @@ use SQL::Translator::Schema; use Data::Dumper; use vars qw[ $DEBUG $VERSION @EXPORT_OK ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/; +$VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; no strict 'refs'; @@ -51,7 +67,7 @@ sub parse { # it is much quicker to slurp back everything all at once rather # than make repeated calls - $sth = $dbh->column_info(); + $sth = $dbh->column_info(undef, undef, undef, undef); foreach my $c (@{$sth->fetchall_arrayref({})}) { @@ -83,7 +99,7 @@ ORDER BY o.name, # View text # I had always thought there was something 'hard' about -reconstructing text from syscomments .. + # reconstructing text from syscomments .. # this seems to work fine and is certainly not complicated! foreach (@{$h}) { @@ -94,32 +110,79 @@ reconstructing text from syscomments .. map { $stuff->{indexes}->{$_->[0]}++ if defined; - } @{$dbh->selectall_arrayref("SELECT DISTINCT object_name(id) AS -name - FROM sysindexes - WHERE indid > 0")}; - + } @{$dbh->selectall_arrayref("SELECT DISTINCT object_name(id) AS name + FROM sysindexes + WHERE indid > 0")}; ## slurp objects map { $stuff->{$_->[1]}->{$_->[0]} = $_; - } @{$dbh->selectall_arrayref("SELECT name,type, id FROM -sysobjects")}; + } @{$dbh->selectall_arrayref("SELECT name,type, id FROM sysobjects")}; ### Procedures + + # This gets legitimate procedures by used the 'supported' API: sp_stored_procedures + map { + my $n = $_->{PROCEDURE_NAME}; + $n =~ s/;\d+$//; # Ignore versions for now + $_->{name} = $n; + $stuff->{procedures}->{$n} = $_; + } values %{$dbh->selectall_hashref("sp_stored_procedures", 'PROCEDURE_NAME')}; + + + # And this blasts in the text of 'legit' stored procedures. Do + # this rather than calling sp_helptext in a loop. + + $h = $dbh->selectall_arrayref(q{ +SELECT o.name, colid,colid2,c.text + FROM syscomments c + JOIN sysobjects o + ON c.id = o.id + WHERE o.type ='P' +ORDER BY o.name, + c.colid, + c.colid2 +} +); + + foreach (@{$h}) { + $stuff->{procedures}->{$_->[0]}->{text} .= $_->[3] + if (defined($stuff->{procedures}->{$_->[0]})); + } + ### Defaults ### Rules ### Bind Defaults ### Bind Rules ### Triggers + # Since the 'target' of the trigger is defined in the text, we will + # just create them independently for now rather than associating them + # with a table. + + $h = $dbh->selectall_arrayref(q{ +SELECT o.name, colid,colid2,c.text + FROM syscomments c + JOIN sysobjects o + ON c.id = o.id + JOIN sysobjects o1 + ON (o.id = o1.instrig OR o.id = o1.deltrig or o.id = o1.updtrig) + WHERE o.type ='TR' +ORDER BY o.name, + c.colid, + c.colid2 +} +); + foreach (@{$h}) { + $stuff->{triggers}->{$_->[0]}->{text} .= $_->[3]; + } ### References ### Keys ### Types - + # Not sure what to do with these? $stuff->{type_info_all} = $dbh->type_info_all; ### Tables @@ -154,15 +217,13 @@ $table_info->{TABLE_TYPE}, ->{columns}; foreach my $c (values %{$cols}) { - my $f = $table->add_field(name => -$c->{COLUMN_NAME}, - data_type => -$c->{TYPE_NAME}, - order => -$c->{ORDINAL_POSITION}, - size => -$c->{COLUMN_SIZE}, + my $f = $table->add_field( + name => $c->{COLUMN_NAME}, + data_type => $c->{TYPE_NAME}, + order => $c->{ORDINAL_POSITION}, + size => $c->{COLUMN_SIZE}, ) || die $table->error; + $f->is_nullable(1) if ($c->{NULLABLE} == 1); } @@ -185,8 +246,7 @@ $table_info->{TABLE_NAME}", 'COLUMN_NAME'); # already been created as part of a primary key or other # constraint? - if (defined($stuff->{indexes}->{$table_info->{TABLE_NAME}})) -{ + if (defined($stuff->{indexes}->{$table_info->{TABLE_NAME}})){ my $h = $dbh->selectall_hashref("sp_helpindex $table_info->{TABLE_NAME}", 'INDEX_NAME'); foreach (values %{$h}) { @@ -219,6 +279,7 @@ $_->{INDEX_NAME}, $table_info->{TABLE_NAME}, ); + my $cols = $columns->{$table_info->{TABLE_QUALIFIER}} ->{$table_info->{TABLE_OWNER}} @@ -232,12 +293,21 @@ $table_info->{TABLE_NAME}, } values %{$cols} ); - -$view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}) - if -(defined($stuff->{view}->{$table_info->{TABLE_NAME}}->{text})); + $view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}) + if (defined($stuff->{view}->{$table_info->{TABLE_NAME}}->{text})); } } + + foreach my $p (values %{$stuff->{procedures}}) { + my $proc = $schema->add_procedure( + name => $p->{name}, + owner => $p->{PROCEDURE_OWNER}, + comments => $p->{REMARKS}, + sql => $p->{text}, + ); + + } + ### Permissions ### Groups ### Users @@ -248,15 +318,16 @@ $view->sql($stuff->{view}->{$table_info->{TABLE_NAME}}->{text}) 1; +# ------------------------------------------------------------------- + =pod =head1 AUTHOR -Paul Harrington Eharringp@deshaw.comE, +Paul Harrington Eharringp@deshaw.comE. =head1 SEE ALSO DBI, DBD::Sybase, SQL::Translator::Schema. =cut -