Bio::DB::Query SqlGenerator
SummaryIncluded librariesPackage variablesSynopsisDescriptionGeneral documentationMethods
Toolbar
WebCvs
Summary
Bio::DB::Query::SqlGenerator - Object representing an SQL Query
Package variables
No package variables defined.
Included modules
Bio::Root::Root
Inherit
Bio::Root::Root
Synopsis
    use Bio::DB::Query::BioQuery;
use Bio::DB::Query::SqlGenerator;
# get a Bio::DB::Query::AbstractQuery or derived query somehow my $query = Bio::DB::Query::BioQuery->new(-tables => ["table1, table2"]); $query->where(["and", ["or", "col4 = ?", "col5 = 'somevalue'"], ["col2 = col4", "col6 not like 'abcd*'"]]); # use the generator to turn a query object into SQL my $sqlgen = Bio::DB::Query::SqlGenerator->new(-query => $query); my $sql = $sqlgen->generate_sql(); print "SQL: $SQL\n"; # then bind as necessary, and execute ...
Description
This represents the strategy pattern for generating a SQL statement given
a Query object.
Eventually there should be interfaces for this pattern as well as for the
Query.
Methods
newDescriptionCode
queryDescriptionCode
generate_sqlDescriptionCode
flatten_qc
No description
Code
Methods description
newcode    nextTop
querycodeprevnextTop
 Title   : query
Usage : $obj->query($newval)
Function: Get/set query object (a Bio::DB::Query::AbstractQuery or derived
instance).
Example :
Returns : value of query (a Bio::DB::Query::AbstractQuery or derived)
Args : new value (a Bio::DB::Query::AbstractQuery or derived, optional)
generate_sqlcodeprevnextTop
  Usage:  $sqlstmt = $sqlgen->generate_sql; $dbh->do($sqlstmt);
$sqlstmt = $sqlgen->generate_sql($query); $dbh->do($sqlstmt);
Flattens query object into a SQL statement.
Methods code
newdescriptionprevnextTop
sub new {
    my ($class,@args) = @_;

    my $self = $class->SUPER::new(@args);

    my ($query) = $self->_rearrange([qw(QUERY)], @args);

    $self->query($query) if $query;

    return $self;
}
querydescriptionprevnextTop
sub query {
    my ($self,$value) = @_;
    if( defined $value) {
	$self->{'query'} = $value;
    }
    return $self->{'query'};
}
generate_sqldescriptionprevnextTop
sub generate_sql {
    my $self = shift;
    my $query = shift || $self->query();

    my @sel = @{$query->selectelts || []};
    my $str = 
	sprintf(
		"SELECT %s%s FROM %s",
		$query->flag("distinct") ? "DISTINCT " : "",
		@sel ? join(", ", @sel) : "*",
		join(", ", @{$query->datacollections}));
    my $qc = $query->where;
    my $wh = $self->flatten_qc($qc);
    $str.= " WHERE $wh" if $wh;
    
    my @ord = @{$query->orderelts || []};
    $str.= sprintf(" ORDER BY %s", join(", ", @ord)) if @ord;
    
    my @gp = @{$query->groupelts || []};
    $str.= sprintf(" GROUP BY %s", join(", ", @gp)) if @gp;
    
    return $str;
}
flatten_qcdescriptionprevnextTop
sub flatten_qc {
    my $self = shift;
    my $qc = shift;
    my $flat;
    if ($qc->is_composite) {
	my @subqcs = ();
	foreach my $subqc (@{$qc->value}) {
	    my $cond = $self->flatten_qc($subqc);
	    next unless $cond;
	    push(@subqcs, $subqc->is_composite ? "(".$cond.")" : $cond);
	}
	$flat = join(" ".uc($qc->operand)." ", @subqcs);
    }
    else {
	my $val = $qc->value();
	my $op = uc($qc->operand) || "=";
	if(($op =~ /LIKE/) && ($val =~ /.(\*|\?)./)) {
	    # we need to replace wildcard chars
$val =~ s/\*/%/g; $val =~ s/\?/_/g; } $flat = join(" ", $qc->name, $op, $val); $flat = "(NOT $flat)" if $qc->neg; } return $flat; } 1;
}
General documentation
AUTHOR Chris Mungall, Hilmar LappTop
Chris Mungall, cmungall@fruitfly.org
Hilmar Lapp, hlapp at gmx.net
This is essentially code ripped out of SqlQuery.pm and AbstractQuery.pm
(both by Chris Mungall), put into its own module as a strategy pattern.
APPENDIXTop
The rest of the documentation details each of the object
methods. Internal methods are usually preceded with a _