View on
MetaCPAN is shutting down
For details read Perl NOC. After June 25th this page will redirect to
Randy Stauner > DBIx-RoboQuery > DBIx::RoboQuery



Annotate this POD


View/Report Bugs
Module Version: 0.032   Source  


DBIx::RoboQuery - Very configurable/programmable query object


version 0.032


  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'});
      dob as birthday,
    FROM users
    WHERE dob < [% query.bind(minimum_birthdate()) %]

  # 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;

  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


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:

See note about "SECURITY".



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

Constructor; Accepts a hash or hashref of options:


  $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.


  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.


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.


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

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").


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

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").


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

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.


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.

Additionally, if you pass in a hash ref it will add a sub to the transformations hash named template (or the value you pass as template_tr_name to the constructor) if a sub by that name doesn't already exist. It uses "template_tr_callback" to create the code ref.


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


  $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.

    {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.


  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.


  $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.


  $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.


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.


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


  $query->tr_row("func", "before", @args);

This is a shortcut for calling "transform" with a "before" or "after" hook that operates on the whole row:

  $query->transform("func", hook => "before", @args);


This returns a code ref that can be included in the transformations hash. This is used internally by "prepare_transformations" but is available separately in case you need to add it manually (if you're passing a transformations object to the constructor rather than a hash ref).

The sub returned by this method accepts a hashref and a template string (without the [% %]), processes the template string (passing the hashref as a var named "row"), and returns the hash ref (in case it was modified by the template):

  my $cb = $query->template_tr_callback;
  $cb->({foo => 'bar'}, q[ row.baz = "qux" ]);
  # returns { foo => 'bar', baz => 'qux' };


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.





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

  perldoc DBIx::RoboQuery


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, or through the web interface at You will be automatically notified of any progress on the request by the system.

Source Code

  git clone


Randy Stauner <>


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.

syntax highlighting: