The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-RoboQuery-0.031
River stage zero No dependents
++
/ DBIx::RoboQuery

NAME

DBIx::RoboQuery - Very configurable/programmable query object

VERSION

version 0.031

SYNOPSIS

  my $template_string = <<SQL;
  [%
    CALL query.key_columns('user_id');
    CALL query.drop_columns('favorite_smell');
    CALL query.prefer('favorite_smell != "wet dog"');
    CALL query.transform('format_date', {fields => 'birthday'});
  %]
    SELECT
      name,
      user_id,
      dob as birthday,
      favorite_smell
    FROM users
    WHERE dob < [% query.bind(minimum_birthdate()) %]
  SQL

  # create query object from template
  my $query = DBIx::RoboQuery->new(
    sql => $template_string,       # (or use file => $filepath)
    dbh => $dbh,                   # handle returned from DBI->connect()
    transformations => {           # functions available for transformation
      format_date => \&arbitrary_date_format,
      trim => sub { (my $s = $_[0]) =~ s/^\s+|\s+$//g; $s },
    },
    variables => {                 # variables for use in template
      minimum_birthdate => \&arbitrary_date_function,
    }
  );

  # transformations (and other configuration) can be specified in the sql
  # template or in your code if you know you'll always want certain ones
  $query->transform('trim', group => 'non_key_columns');

  my $resultset = $query->resultset;

  $resultset->execute;
  my @non_key = $resultset->non_key_columns;
  # do something where i want to know the difference between key and non-key columns

  # get records (with transformations applied and specified columns dropped)
  my $records = $resultset->hash;            # like DBI/fetchall_hashref
  # OR: my $records = $resultset->array;     # like DBI/fetchall_arrayref

DESCRIPTION

This robotic query object can be configured to help you get exactly the result set that you want.

It was designed to run in a completely automated (unmanned) environment and read in a template that both builds the desired SQL query dynamically and configures the query output. It should be usable anywhere you desire a highly configurable query and result set.

It (and its companion ResultSet) provide various methods for configuring/declaring what to expect and what to return. It aims to be as informative as you might need it to be.

The following enhancements are possible:

  • The query can be built with templates (currently Template::Toolkit) which allows for perl variables and functions to interpolate and/or generate the SQL

  • The output can be transformed (using Sub::Chain::Group)

  • TODO: list more

See note about "SECURITY".

METHODS

new

  my $query = DBIx::RoboQuery->new(%opts); # or \%opts

Constructor; Accepts a hash or hashref of options:

  • sql

    The SQL query [template] in a string; This can be a reference to a string in case your template [query] is large and it makes you feel better to pass it by reference.

  • file

    The file path of a SQL query [template] (mutually exclusive with sql)

  • dbh

    A database handle (the return of DBI->connect())

  • default_slice

    The default slice of the records returned; It is not used by the query but merely passed to the ResultSet object. See "array" in DBIx::RoboQuery::ResultSet.

  • drop_columns

    An arrayref of columns to be dropped from the resultset; See "drop_columns".

  • key_columns

    An arrayref of [primary key] column names; See "key_columns".

  • order

    An arrayref of column names to specify the sort order; See "order".

  • prefix

    A string to be prepended to the SQL before parsing the template

  • squeeze_blank_lines

    Boolean; If enabled, empty lines (or lines with only whitespace) will be removed from the compiled template. This can make it easier to look at sql that has a lot of template directives. (Disabled by default.)

  • suffix

    A string to be appended to the SQL before parsing the template

  • template_options

    A hashref of options that will be merged into the options to Template->new() You can use this to overwrite the default options, but be sure to use the variables options rather than including VARIABLES in this hash unless you don't want the default variables to be available to the template.

  • template_private_vars

    Not normally needed

    This is a regexp (which defaults to $Template::Stash::PRIVATE (which defaults to qr/^[_.]/)). Any template variables that match will not be accessible in the template (but will return undef, which will throw an error under STRICT mode). If you want to access "private" variables (including "private" hash keys) in your templates (the main query template or any templates passed to "prefer") you should set this to undef to tell Template not to check variable names.

  • transformations

    An instance of Sub::Chain::Group (or a hashref (See "prepare_transformations".))

  • variables

    A hashref of variables made available to the template

bind

  $query->bind($value);
  $query->bind($value, \%attr);
  $query->bind($p_num, $value, \%attr);

Bind a value to a placeholder in the query. The provided arguments are saved and eventually passed to "bind_param" in DBI.

This can be useful for passing dynamic values through the database driver's quoting mechanism.

For convenience a placeholder is returned so that the method can be called in place in a query template:

  # in template:
  WHERE field = [% query.bind(value) %]

The placeholder will be the standard ? if the index is an integer, or it will simply return the placeholder otherwise which can be useful for drivers that allow named parameters:

  WHERE field = [% query.bind(':foo', value, {}) %]
  # becomes 'WHERE field = :foo'

If you don't want the placeholder added to your query use the template's syntax to discard it. For example, with Template::Toolkit:

  [% CALL query.bind(value) %]

For convenience the placeholder ($p_num) will be filled in automatically (a simple incrementing integer starting at 1) unless you provide all three arguments (in which case they are passed as-is to "bind_param" in DBI).

Note that the index only auto-increments if you don't supply one (by sending all three arguments):

  $query->bind($a);         # placeholder 1
  $query->bind($b, {});     # placeholder 2
  $query->bind(2, $c, {});  # overwrite placeholder 2
  $query->bind($d);         # placeholder 3   (a total of 3 bound parameters)
  $query->bind(4, $e, {});  # placeholder 4   (a total of 4 bound parameters)
  $query->bind($f);  # auto-inc to 4 (which will overwrite the previous item)

So don't mix the auto-increment with explicit indexes unless you know what you are doing.

Consistency and simplicity was chosen over the complexity added by special cases based on comparing the provided index to the current (if any) auto-increment.

bound_params

  my @bound = $query->bound_params;
  # returns ( [ 1, "foo" ], [ 2, "bar", { TYPE => SQL_VARCHAR } ] )

Returns a list of arrayrefs representing parameters bound to the query. Each arrayref is structured to be flattened and passed to "bind_param" in DBI. Each will contain it's index (or placeholder), value, and possibly a hashref or value to hint at the data-type.

bound_values

This is a wrapper around "bound_params" that returns only the values:

  my @bound = $query->bound_values;
  # returns ("foo", "bar")

Note: Values are returned in the order they were bound. If "bind" is used in any way other than the default auto-increment manner the order (or even the number) of the values may be confusing and unhelpful. In that case you probably want to use "bound_params" and get the values out manually. This behavior may be improved in the future and should not be relied upon. (Suggestions and patches for improved behavior are welcome.) The behavior of this method when "bind" is used only in the default auto-increment manner will not change.

drop_columns

  # get
  my @drop_columns = $query->drop_columns;
  # set
  $query->drop_columns(@columns_to_ignore);

Accessor for the list of columns to drop (remove) from the resultset; This works like the "key_columns" method.

Drop columns can be useful if you need a particular column in the query but don't really want the column in the resultset. Some databases are inconsistent with allowing the use of a non-selected column in an ORDER BY clause, for instance.

Drop columns can also be useful if you want to compare the value of a column in a preference statement (see "prefer") but don't want the column in the actual resultset.

It may be most useful to set this value from within the template (see "SYNOPSIS").

key_columns

  # get
  my @key_columns = $query->key_columns;
  # set
  $query->key_columns('id', 'fk_id');
  # empty
  $query->key_columns([]);

Accessor for the list of [primary] key columns for the query;

Any arrayrefs provided (when setting the list) will be flattened. This allows you to empty the list by sending an empty arrayref (if you have a reason to do so).

"hash" in DBIx::RoboQuery::ResultSet sends the key columns to "fetchall_hashref" in DBI to define unique records.

It may be most useful to set this value from within the template (see "SYNOPSIS").

order

  # get
  my @order = $query->order;
  # set
  $query->order(@column_order);

Accessor for the list of the column names of the sort order of the query;

This is a getter/setter which works like "key_columns" with one exception: If the value has never been set it is initialized to the list of columns from the ORDER BY clause of the sql statement as returned from "order_from_sql" in DBIx::RoboQuery::Util. If there is no ORDER BY clause or the statement cannot be parsed an empty list will be returned.

It may be most useful to set this value from within the template (see "SYNOPSIS"), especially if your ORDER BY clause is complex.

prepare_transformations

This method (called from the constructor) prepares the transformations attribute (if one was passed to the constructor).

This method provides a shortcut for convenience: If transformations is a simple hash, it is assumed to be a hash of named subs and is passed to "new" in Sub::Chain::Group as the subs key of the chain_args hashref. See Sub::Chain::Group and Sub::Chain::Named for more information about these.

If you pass your own instance of Sub::Chain::Group this method will do nothing. It is mostly here to help a subclass use a different module for transformations if desired.

pre_process_sql

Prepend prefix and append suffix. Called from "sql" before processing the template with the template engine.

prefer

  $query->prefer("color == 'red'", "color == 'green'");
  $query->prefer("smell == 'good'");

Accepts one or more rules to determine which record to choose if you use resultset->hash() and multiple records are found for any given key field(s).

The "rules" are strings that will be processed by the templating engine of the query object (currently Template::Toolkit). The record's fields will be available as variables.

Each rule will be tested with each record and the first one to match will be returned.

So considering the above example, the following code will return the second record since it will match one of the rules first.

  $resultset->preference(
    {color => 'blue',  smell => 'good'},
    {color => 'green', smell => 'bad'}
  );

The rules are tested in the order they are set, and the records are processed in reverse order (to be compatible with the "last one in wins" logic of "fetchall_hashref" in DBI).

See "hash" in DBIx::RoboQuery::ResultSet and "preference" in DBIx::RoboQuery::ResultSet for more information.

resultset

  my $resultset = $query->resultset;

This is a convenience method which returns a DBIx::RoboQuery::ResultSet object based upon this query.

To avoid confusion it caches the result so that multiple calls to resultset() will return the same object (rather than creating new ones).

If you desire a new resultset (which will create a new DBI statement handle) or you desire to pass options different than the attributes on the query, you can manually call "new" in DBIx::RoboQuery::ResultSet:

  my $resultset = DBIx::ResultSet->new($query, %other_options);

NOTE: The ResultSet constructor calls "sql" before initializing the object so that any configuration done to the query in the template will be passed to the object at initialization.

sql

  $query->sql;
  $query->sql({extra => variable});

Process the SQL template and return the result.

This method caches the result of the processed template to avoid unexpected side effects of calling any configuration directives (that might be in the template) multiple times.

NOTE: This method gets called (without arguments) when a resultset is created (to ensure that the query is fully configured before copying its attributes to the ResultSet). If you need to pass extra template variables (that were not passed to "new") you should call this method (with those variables) before instantiating any resultset objects.

transform

  $query->transform($sub, %opts);
  $query->transform($sub, fields => [qw(fld1 fld2)], args => []);

Add a transformation to be applied to the result data.

The default implementation simply passes the arguments to "append" in Sub::Chain::Group.

tr_fields

Shortcut for calling "transform" on fields.

  $query->tr_fields("func", "fld1", "arg1", "arg2");

Is equivalent to

  $query->transform("func", fields => "fld1", args => ["arg1", "arg2"]);

The second parameter (the fields) can be either a single string or an array ref.

tr_groups

Just like "tr_fields" but the second parameter is for groups.

SECURITY

NOTE: Obviously this module is not designed to take in external user input since the SQL queries are passed through a templating engine.

This module is intended for use in internal environments where you are the source of the query templates.

SEE ALSO

TODO

  • Allow for other templating engines (or none at all)

  • Consider an option for including direction (ASC/DESC) in "order"

  • Write a lot more tests

  • Allow binding an arrayref and returning '?,?,?'

  • Accept transformations or callbacks that operate on the whole row?

  • Accept bind variables in the constructor?

  • Add support for DBIx::Connector?

SUPPORT

Perldoc

You can find documentation for this module with the perldoc command.

  perldoc DBIx::RoboQuery

Websites

The following websites have more information about this module, and may be of help to you. As always, in addition to those websites please use your favorite search engine to discover more resources.

Bugs / Feature Requests

Please report any bugs or feature requests by email to bug-dbix-roboquery at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-RoboQuery. You will be automatically notified of any progress on the request by the system.

Source Code

https://github.com/rwstauner/DBIx-RoboQuery

  git clone https://github.com/rwstauner/DBIx-RoboQuery.git

AUTHOR

Randy Stauner <rwstauner@cpan.org>

COPYRIGHT AND LICENSE

This software is copyright (c) 2010 by Randy Stauner.

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