Dataset Definition

A dataset is a single conceptual “model class” within your application. It is defined in a .XML file and it provides a matching set of insert/update/delete/fetch methods.

Jarvis was originally developed to support relational databases using the DBI/DBD drivers, and some of that legacy is still visible despite the fact that it now supports non-SQL database agents including SDP and MongoDB.

This chapter describes the common and general features of datasets.

The subsequent chapter specifically describes how to use SQL-style datasets with DBI.

The boat_class.xml file we are using for our SQL-based relational DB examples is as follows:

<dataset read="*" write="*">
    <transform fetch="notnull" store="trim,null" />
    <select>
        SELECT id, class, active, description
        FROM boat_class
        ORDER BY class
    </select>
    <update>
        UPDATE boat_class
        SET class = {$class},
            active = {$active},
            description = {$description},
            change_user = {$__username},
            change_date = datetime ('now')
        WHERE id = {$id};
    </update>
    <insert returning="yes">
        INSERT INTO boat_class (class, active, description, change_user, change_date)
        VALUES ({$class}, {$active}, {$description}, {$__username}, datetime ('now'));
    </insert>
    <delete>
        DELETE FROM boat_class
        WHERE id = {$id};
    </delete>
</dataset>

Nothing the use of the following features:

We use <transform> element to request that data is pre-processed before fetch and save. The transform tag is described elsewhere. However, in this case we have requested the following transform options:

• On fetch, any NULL value fetched from the database is represented as a zero length string in the returned structure to the user.
• On store, leading and trailing white space should be removed before saving to database.
• On store, empty strings from the client should be stored as NULL in the database.

Per-dataset <hook> configuration can inject Perl code for additional processing of inputs and outputs at predefined points of the operation.

There is a single fetch (i.e. the <select>) SQL configuration. But for store we have three separate SQL statements, one for each of <insert>, <update>, and <delete>. For SQL-style datasets these are SQL statements. For non-relational agents they have a different structure.

For DBI datasets, two additional SQL parameters are supported:

• <before> execute an SQL statement once before any store requests are performed.
• <after> execute an SQL statement once after any store requests are performed.

See DBI Datasets for more information.

Dataset Attributes

The top-level attributes of all datasets definitions are as follows.

Dataset Elements

The elements within the <dataset> definition are as follows:

Note: For select/before/after/update/insert/delete/merge Implementation is specific to the database agent. For DBI databases these are SQL statements. For MongoDB these are object definitions with variables. For SDP these are MDX queries.

Note: Data store operations are not supported for SDP, and that MongoDB does not support before/after statements.

Access Identifiers

The access identifiers controlling Dataset are the same list of options as is used for the access control of exec and plugin datasets.

Specifically, the access identifier may be one of the following:

Parameter Expansion

All of the database agents support variables within their SQL, MDX, or operation objects.

Regardless of the type of agent, the values for these parameters are sourced from the same set of parameter sources, which is the following:

• Client-Supplied Parameters.
• Server-Only Safe Parameters.
• Default Parameters.

Client-Supplied Parameters

Client-Supplied Parameters are checked in the following order:

1. Parent Parameters (for linked child datasets only).
2. Object attributes from the JSON or XML request body.
3. Named/Numbered REST parameters from the Route.
4. URL parameters e.g. ?name=value.

Server-Only Safe Parameters

When supplying user variables via CGI GET and via JSON/XML request body, the parameter names must be limited to alphanumeric, plus underscore, colon and hyphen. They may contain a single leading hyphen, but not two. The first non-hyphen character must be [a-zA-Z]. Parameter names are always case-sensitive.

E.g. the following are invalid as user-supplied parameter in a Jarvis dataset request:

• my(param)
• __my_param
• _1param
• 1

The reason for the limitation on numeric parameters is to avoid conflict with the indexed RESTful parameters which are named simply 1, 2, etc.

The reason for the limitation of the leading double-underscore is because all parameters beginning with double-underscore are secure parameters, supplied by Jarvis.

A number of standard built-in parameters are generated by the login process. The built-in safe parameters available to be substituted into SQL statements are:

In addition, safe parameters may come from the following other sources:

• Login modules may define additional safe parameters, e.g. {$__user_id}.
• Hooks may define additional safe parameters.
• Default Parameters defined in the application config file may be safe parameters.

Remember. Client-supplied values will never be allowed to modify these safe parameters.

The following pattern is common in SQL for Jarvis datasets. Imagine that table t contains records owned by the username defined in column t.owner. Users in group admin may read and write all records. Other uses may see (read-only) just their own records.

<dataset read="*" write="admin">
    <select>
        SELECT * FROM t WHERE (owner = {$__username}) OR ({$__group:admin} IS NOT NULL)
    </select>
</dataset>

Default Parameters

Any parameter may be given a static default value using the default_parameters element within the application configuration file.

Within the default_parameters element is one or more parameter elements.

Default parameter values may also be specified for safe variables (i.e. with names that begin with double underscore). These safe variables may only be modified by the server code, and can never have their values specified by the client.

Custom Sort Module

Jarvis supports either lexical or numeric sorting by default when performing sorting operations on SQL dataset responses. In some cases it may be desirable to define a custom sorting module to perform custom sorting for different fields or use mixed lexical or numeric sorting.

The top level <dataset> definition supports defining a sort_module property. In this example we will define a sort module of sort_module="N2::CustomSorter".

Note: When defining a sorting module the module name and path must resolve to a Perl module in a defined library, hook or plugin path that is defined in the Jarvis includes definitions.

Jarvis expects a singular subroutine to be defined on the imported sort_module; being sort. Jarvis will validate that both:

1. The Perl module exists and
2. The Perl module can be loaded as a Perl module and has the method sort.

A base level sorting module matching our example can be defined as:

# Basic custom comparator example for Jarvis.
use strict;
use warnings;

package N2::CustomSorter;

# Define a comparator function that will perform both lexical and numeric sorting.
# If a dataset is mixed numbers will go ahead of strings.
sub sort {
    my ($sort_field, $a, $b) = @_;

   	# Extra values as required by the defined sort field.
    my $a_cmp = ($a->{$sort_field} || chr(255));
    my $b_cmp = ($b->{$sort_field} || chr(255));

    # Check if we are looking at numbers or text strings.
    my $a_is_num = ($a_cmp =~ /^-?\d+(\.\d+)?$/);
    my $b_is_num = ($b_cmp =~ /^-?\d+(\.\d+)?$/);

    # Both are numbers? Use the numeric comparator.
    if ($a_is_num && $b_is_num) {
        return $a_cmp <=> $b_cmp;

    # Numbers go head of strings.
    } elsif ($a_is_num) {
        return -1;

    # Strings go after numbers.
    } elsif ($b_is_num) {
        return -1;

    # Finally utilize standard lexical sorting.
    } else {
        return $a_cmp cmp $b_cmp;
    }
}

1;

Jarvis will pass three arguments to a defined sort method. These are defined as:

The expected return result of the sort subroutine call is either -1, 0 or 1 following standard comparator output.