NAME

Querylet - simplified queries for the non-programmer

VERSION

version 0.402

SYNOPSIS

use Querylet;

database: dbi:SQLite:dbname=wafers.db

query:
  SELECT wafer_id, material, diameter, failurecode
  FROM   grown_wafers
  WHERE  reactor_id = 105
    AND  product_type <> 'Calibration'

add column surface_area:
  $value = $row->{diameter} * 3.14;

add column cost:
  $value = $row->{surface_area} * 100 if $row->{material} eq 'GaAs';
  $value = $row->{surface_area} * 200 if $row->{material} eq 'InP';

munge column failurecode:
  $value = 10 if $value == 3; # 3's have been reclassified

munge all values:
  $value = '(null)' unless defined $value;

output format: html

DESCRIPTION

Querylet provides a simple syntax for writing Perl-enhanced SQL queries with multiple output methods. It processes and renders a template SQL query, then processes the query results before returning them to the user.

The results can be returned in various formats.

PERL VERSION SUPPORT

This code is effectively abandonware. Although releases will sometimes be made to update contact info or to fix packaging flaws, bug reports will mostly be ignored. Feature requests are even more likely to be ignored. (If someone takes up maintenance of this code, they will presumably remove this notice.)

SYNTAX

The intent of Querylet is to provide a simple syntax for writing queries. Querylet will rewrite querylets from their simple form into complete Perl programs. The syntax described here is the "intended" and basic syntax, but savvy Perl hackers will realize that horrible things can be done by interspersing "real" Perl with querylet directives.

I am afraid I really cannot suggest that course of action, sir.

DIRECTIVES

In the directives below, a BLOCK begins after the colon preceding it and ends at the next line with something unindented.

database: VALUE

This directive provides information about the database to which to connect. Its syntax is likely to be better defined by the specific Querylet subclass you're using.

output format: VALUE

This directive names a format to be used by the output renderer. The default value is "csv".

output file: VALUE

This directive names a file to which the rendered output should be written. If not given, renderers will present output to the terminal, or otherwise interactively. If this doesn't make sense, an error should be thrown.

query: BLOCK

query:
  SELECT customer.customerid, lastname, firstname, COUNT(*)
  FROM   customers
  JOIN   orders ON customer.customerid = orders.customerid
  GROUP BY customer.customerid, lastname, firstname

This directive provides the query to be run by Querylet. The query can actually be a template, and will be rendered before running if (and only if) the munge query directive occurs in the querylet. The query can include bind parameters -- that is, you can put a ? in place of a value, and later use query parameter to replace the value. (See below.)

It is important that every selected column have a name or alias.

query parameter: BLOCK

This directive sets the value for the next bind parameter. You should have one (and only one) query parameter directive for each "?" in your query.

munge query: BLOCK

The directive informs Querylet that the given query is a template and must be rendered. The BLOCK must return a list of parameter names and values, which will be passed to the template toolkit to render the query.

set option NAME: BLOCK

This sets the name option to the given value, and is used to set up options for plugins and I/O handlers. Leading and trailing space is stripped from the block.

munge rows: BLOCK

This directive causes the given block of code to be run on every row. The row is made available to the block as $row, a hashref.

delete rows where: BLOCK

This directive will cause any row to be deleted where the given condition evaluates true. In that evaluation, $row is available.

munge all values: BLOCK

This directive causes the given block of code to be run on every value of every row. The row is made available to the block as $row, a hashref. The value is available as $value.

munge column NAME: BLOCK

This directive causes the given block of code to be run on the named column in every row. The row is made available to the block as $row, a hashref. The column value is available as $value.

add column NAME: BLOCK

This directive adds a column to the result set, evaluating the given block for each row. The row is made available as to the block as $row, and the new column value is available as $value.

delete column NAME

This directive deletes the named column from the result set.

delete columns where: BLOCK

This directive will cause any column to be deleted where the given condition evaluates true. In that evaluation, $column is available, containing the column name; @values contains all the values for that column.

no output

This directive instructs the Querylet not to output its results.

IMPLEMENTATION

Querylet is a source filter, implemented as a class suitable for subclassing. It rewrites the querylet to use the Querylet::Query class to perform its work.

METHODS

init

Querylet->init;

The init method is called to generate a header for the querylet, importing needed modules and creating the Query object. By default, the Query object is assigned to $q.

set_dbh

Querylet->set_dbh($text);

This method returns Perl code to set the database handle to be used by the Query object. The default implementation will attempt to use $text as a DBI connect string to create a dbh.

set_query

Querylet->set_query($sql_template);

This method returns Perl code to set the Query object's SQL query to the passed value.

bind_next_param

Querylet->bind_next_param($text)

This method produces Perl code to push the given parameters onto the list of bind parameters for the query. (The text should evaluate to a list of parameters to push.)

set_query_vars

Querylet->set_query_vars(%values);

This method returns Perl code to set the template variables to be used to render the SQL query template.

set_option

Querylet->set_option($option, $value);

This method returns Perl code to set the named query option to the given value. At present, this works by using the Querylet::Query scratchpad, but a more sophisticated method will probably be implemented. Someday.

input

Querylet->input($parameter);

This method returns code to instruct the Query object to get an input parameter with the given name.

set_input_type

Querylet->set_input_type($type);

This method returns Perl code to set the input format.

set_output_filename

Querylet->set_output_filename($filename);

This method returns Perl code to set the output filename.

set_output_method

Querylet->set_output_method($type);

This method returns Perl code to set the output method.

set_output_type

Querylet->set_output_type($type);

This method returns Perl code to set the output format.

munge_rows

Querylet->munge_rows($text);

This method returns Perl code to execute the Perl given in $text for every row in the result set, aliasing $row to the row on each iteration.

delete_rows

Querylet->delete_rows($text);

This method returns Perl code to delete from the result set any row for which $text evaluates true. The code iterates over every row in the result set, aliasing $row to the row.

munge_col

Querylet->munge_col($column, $text);

This method returns Perl code to evaluate the Perl code given in $text for each row, with the variables $row and $value aliased to the row and it's $column value respectively.

add_col

Querylet->add_col($column, $text);

This method returns Perl code, adding a column with the given name. The Perl given in $text is evaluated for each row, with the variables $row and $value aliased to the row and row column respectively.

If a column with the given name already exists, a warning issue and the directive is ignored.

delete_col

Querylet->delete_col($column);

This method returns Perl code, deleting the named column from the result set.

delete_cols

Querylet->delete_cols($text);

This method returns Perl code to delete from the result set any row for which $text evaluates true. The code iterates over every column in the result set, creating @values, which contains a copy of all the values in that columns, and $column, which contains the name of the current column.

column_headers

Querylet->column_headers($text);

This method returns Perl code to set up column headers. The $text should be Perl code describing a hash of column-header pairs.

munge_values

Querylet->munge_values($text);

This method returns Perl code to perform the code in $text on every value in every row in the result set.

output

Querylet->output;

This returns the Perl instructing the Query to output its results in the requested format, to the requested destination.

FUNCTIONS

once

once($id, $text);

This is a little utility function, used to ensure that a bit of text is only included once. If it has been called before with the given $id, an empty string is returned. Otherwise, $text is returned.

SEE ALSO

Querylet::Query

AUTHOR

Ricardo SIGNES <rjbs@semiotic.systems>

COPYRIGHT AND LICENSE

This software is copyright (c) 2004 by Ricardo SIGNES.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.