Metabase::Query - Generic Metabase query language role
version 1.001
package Metabase::Query::SQLite; use Moose; with 'Metabase::Query'; # define Moose attributes sub prepare { my ( $self, $query ) = @_; # extract '-where' translate to SQL my $pred = $query->{-where}; my $where = !$pred ? "" : "WHERE " . $self->dispatch_query_op($pred); # extract '-limit' and '-order' and translate to SQL ... return "$where $order $limit"; } sub op_eq { my ( $self, @args ) = @_; return [ "$field = ?", $arg[1] ]; } sub op_and { my ( $self, @args ) = @_; my @predicates = map { "($_)" } map { $self->dispatch_query_op($_) } @args; return join(" AND ", @predicates); } # ... implement all other required ops ...
This role describes the simplified query language for use with Metabase and defines the necessary methods to implement it for any particular Metabase backend.
A query is expressed as a data structure of the form:
{ -where => [ $operator => @arguments ] -order => [ $direction => $field, ... ] -limit => $number, }
Arguments to an operator must be scalar values, or in the case of logic operators, must be array references of operator/argument pairs.
A where clause predicate must be given as an arrayref consisting of an operator name and a list of one or more arguments.
-where => [ $operator => @arguments ]
Some operators take a field name as the first argument. A field name must match the expression qr/\A[a-z._]+\z/i
qr/\A[a-z._]+\z/i
Logic operators take predicates as arguments. The -and and -or operators take a list of predicates. The -not operator takes only a single predicate as an argument.
-and
-or
-not
[ -and => @predicates ] [ -or => @predicates ] [ -not => $one_predicate ]
Most comparison operators are binary and take two arguments. The first must be the field name to which the operation applies. The second argument must be a non-reference scalar value that the operation is comparing against.
[ -eq => $field => $value ] # equal [ -ne => $field => $value ] # not equal [ -gt => $field => $value ] # greater than [ -ge => $field => $value ] # greater than or equal to [ -lt => $field => $value ] # less than [ -le => $field => $value ] # less than or equal to
The exception is the -between operator, which takes a field, a low value and a high value:
-between
[ -between => $field => $low, $high ]
The matching operator provides rudimentary pattern matching.
[ -like => $field => $match_string ]
The match string specifies a pattern to match. A percent sign (%) matches zero or more characters and a period (.) matches a single character.
%
.
A desired order of results may be specified with an array reference containing direction and field name pairs. Field names must follow the same rules as for "Where clauses". Valid directions are -asc and -desc.
-asc
-desc
-order => [ -asc => $field1 ] -order => [ -asc => $field1, -desc => $field2 ]
Not all backend will support mixed ascending and descending field ordering and backends may throw an error if ordering is not possible.
A limit on the number of results returned is specified by a simple key-value pair:
-limit => NUMBER
The number must be a non-negative integer. A given backend should make a best efforts basis to respect the limit request, but the success of a limit request may be constrained by the nature of a particular backend index.
$result = $self->dispatch_query_op([-eq => $field, $value]);
Validates that a predicate has a valid operator name, validates the arguments are correctly specified, and dispatches to the appropriate method for the operator name (e.g. op_eq).
op_eq
$result = $self->get_native_query( $query ); @result = $self->get_native_query( $query );
Translates the Metabase query data structure into a backend-native scalar (string, data-structure, etc). It validates the structure of the query and then calls the translate_query method, which must be provided by the class that implements this role.
translate_query
To support the old key-value API, any keys that do not match -where, -order, or -limit and do not begin with a minus sign will be treated as field names and appended to a -where parameter as equality checks.
-where
-order
-limit
The translate_query method will be called with the same context (scalar or list) as the call to get_native_query.
get_native_query
Here is an example example query to return the 10 most recent CPAN Testers reports by a single submitter (specified by creator URI), excluding 'NA' reports:
{ -where => [ -and => [ -eq => 'core.creator' => $creator_uri ], [ -eq => 'core.type' => 'CPAN-Testers-Report'], ], -order => [ -desc => 'core.update_time' ], -limit => 10, }
my $native = $self->translate_query( $query );
This method should take a query data structure in the form described in this document and return a backend-native query scalar (WHERE/ORDER/LIMIT clauses or comparable data structure). In practice, this means calling dispatch on individual predicates and assembling the results appropriately.
dispatch
Classes implementing this role must provide the following methods to implement the query operations in the appropriate backend-specific syntax.
op_not
op_or
op_and
op_ne
op_gt
op_lt
op_ge
op_le
op_like
op_between
David Golden <dagolden@cpan.org>
Ricardo Signes <rjbs@cpan.org>
Leon Brocard <acme@cpan.org>
This software is Copyright (c) 2012 by David Golden.
This is free software, licensed under:
The Apache License, Version 2.0, January 2004
To install Metabase, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Metabase
CPAN shell
perl -MCPAN -e shell install Metabase
For more information on module installation, please visit the detailed CPAN module installation guide.