view release on metacpan or  search on metacpan

DBIx/DBStag/SQLTemplate.pm  view on Meta::CPAN

#	    print $fh $c;
#	    $c = '';
	}
	while ($c =~ /(\S+)(\s*)(.*)/s) {
	    my ($w, $sp, $next) = ($1, $2, $3);
	    if ($w =~ /^[A-Z]+$/) {
		print $fh $keyword->($w);
	    }
	    else {
		print $fh $w;
	    }
	    print $fh $sp;
	    $c = $next;
	}
	print $fh "\n";
    }
    print $fh $comment->("// -- METADATA --\n");
    foreach my $p (@$props) {    
	my ($n, $v) = ($p->{name}, $p->{value});
	print $fh $property->("$n: ");
	print $fh $v;
	print $fh "\n";
    }
}

sub _quote {
    my $v = shift;
    $v =~ s/\'/\'\'/g;
    "'$v'";
}


1;

__END__

=head1 NAME

  DBIx::DBStag::SQLTemplate - A Template for an SQL query

=head1 SYNOPSIS

  # find template by name
  $template = $dbh->find_template("mydb-personq");

  # execute this template, filling in the 'name' attribute
  $xml = $dbh->selectall_xml(-template=>$template, 
                             -bind=>{name => "fred"});

=cut

=head1 DESCRIPTION

A template represents a canned query that can be parameterized.

Templates are collected in directories (in future it will be possible
to store them in files or in the db itself).

To tell DBStag where your templates are, you should set:

  setenv DBSTAG_TEMPLATE_DIRS "$HOME/mytemplates:/data/bioconf/templates"

Your templates should end with the suffix B<.stg>, otherwise they will
not be picked up

You can name templates any way you like, but the standard way is to
use 2 or 3 fields

  SCHEMA-OBJECT

or

  SCHEMA-OBJECT-QUALIFIERS

(with underscores used within fields)

A template file should contain at minimum some SQL; for example:

=over

=item Example template 1

  SELECT 
               studio.*,
               movie.*,
               star.*
  FROM
               studio NATURAL JOIN 
               movie NATURAL JOIN
               movie_to_star NATURAL JOIN
               star
  WHERE
               [movie.genre = &genre&] [star.lastname = &lastname&]
  USE NESTING (set(studio(movie(star))))

Thats all! However, there are ways to make your template more useful

=item Example template 2

  :SELECT 
               studio.*,
               movie.*,
               star.*
  :FROM
               studio NATURAL JOIN 
               movie NATURAL JOIN
               movie_to_star NATURAL JOIN
               star
  :WHERE
               [movie.genre = &genre&] [star.lastname = &lastname&]
  :USE NESTING (set(studio(movie(star))))

  //
  schema: movie
  desc: query for fetching movies

By including B<:> at the beginning it makes it easier for parsers to
assemble SQL (this is not necessary for DBStag however)

After the // you can add tag: value data.