Extensions

myDBR allows you to create new extensions and modify existing extensions using the extension API. The API is very simple and allows you to write new commands for the myDBR language and implement new functionality.

Overview

A myDBR report can contain multiple result sets (single query result rows). Each of these result sets (the raw data) is passed to the result set handlers. myDBR comes with built-in result set handlers (table, cross-tabulation, charts, Graphviz elements, etc.). myDBR extensions consist of myDBR language commands and associated result set handlers you can define yourself. For example, the Google Maps extension defines the dbr.googlemaps command and implements the Google Maps mashup functionality.

myDBR extensions consist of one or more myDBR commands (dbr.*) associated with the actual implementation of the extension. Commands can have a variable number of parameters.

Extensions are written in PHP. Extensions that ship with myDBR reside in the extensions directory. Each extension has its own subdirectory under extensions. The user can also define their own extensions, which will be placed in the user/extensions directory. The name of the directory is the same as the extension name. The PHP files, extensions.php and user/extensions.php, contain the declarations for each extension. This file is read during report processing, and all active extensions introduced in the file are taken into use.

A special case of a myDBR extension is a pass-through extension, whose purpose is to take raw myDBR query data, process it, and pass the result back to myDBR result handlers. The result set from the pass-through extension can be completely different from the original data.

Extension definition in extensions.php

The extensions.php-file and user/extensions.php-file declare the $dbr_extensions-array which contains the declarations for each extensions in following format:

$dbr_extensions = array (
  'MY_EXTENSION_NAME' => array(
    'enabled' => true,
    'php' => 'MY_EXTENSION_FILE.php', 
    'row_by_row_initialization' => 'ROW_BY_ROW_INIT_FUNCTION',
    'row_by_row_data_row' => 'ROW_BY_ROW_DATA_ROW_FUNCTION', 
    'row_by_row_finish' => 'ROW_BY_ROW_FINISH_FUNCTION',
    'single_pass_call' => 'SINGLE_PASS_CALL_FUNCTION', 
    'javascript' => array('MY_JAVASCRIPT.js'), 
    'passthrough' => false,
    'cmds' => array(
      array (
        'cmd' => 'dbr.MY_COMMAND',
        'sqleditor.doc' => array('info' => 'This is documentation for the command', 'example' => 'dbr.MY_COMMAND options', 'url' => 'http://url_to_help_page'),
        'MY_PARAMETER' => 1, // Has one parameter which is mandatory (1)
      ),
    ),
  ),
)

$dbr_extensions-array elements are the extensions declared. The key of each element (MY_EXTENSION_NAME) is the subdirectory where the extension implementation resides.

For each extension following array key elements are defined:

enabled Determines if the extension is active. If the extension is disabled, myDBR will not recognize the commands and will not include the extension's javascript in the <HEAD> section
autoload myDBR loads the extension's resources (JavaScript / CSS) automatically to all reports if the flag is enabled. If not, the extension needs to be marked part of a report in report info.
javascript If the extensions require a JavaScript file to be included in the <HEAD>-section, put the path to your JavaScript(s) in this array. If none is required, leave the array empty.
css If the extensions require a separate CSS file to be included in the <HEAD>-section, put the path to your CSS(s) in this array. If none is required, leave the array empty.
php Name of the extension implementation file. If the extension is enabled, myDBR will include this file when processing the report. Put only the filename here. The include is done from the extensions/MY_EXTENSION_NAME-directory.
row_by_row_initialization In row-by-row handling, this function is called before any data row is sent to the extension.
row_by_row_data_row This function is called for each data row when using the row-by-row handling. The function gets an array of data values as a parameter.
row_by_row_finish When all the rows have been processed by the extension in row-by-row handling, this function will be called without parameters.
single_pass_call myDBR will first read all the data from the database and then pass all of them in a array to extension
cmds This will declare the commands associated for the extension. The first command is treated as the main extension command that will trigger the execution. Subsequent commands can be used as options and should be used before the main extension command in the reports. For each command, there is an array element that contains the actual command ('cmd'), optional SQL Editor help (sqleditor.doc) definition, and the named parameters. The parameters ('MY_PARAMETER) can be set as mandatory (1) or optional (0).
mydbrextension If defined, the extension is part of myDBR supplied extensions and can be found in mydbr/extensions/extensions.php. User-defined extensions should be declared in mydbr/user/extensions/extensions.php.
passthrough The extension passes the processed data back to myDBR

Processing the Result Set

The result sets from myDBR can be processed in a single pass or row by row (a pass-through extension handles data in a single pass). The method you should choose depends on the functionality required and the number of data rows to be processed. If the number of data rows is small, it might be easier to use the single-pass approach. Choose the row-by-row method if the number of data rows is large, thus lowering the memory requirement for the server (for the cached data).

Single-pass Processing

In single-pass processing, myDBR parses the result into an array and then passes the data to the extension (see 'single_pass_call' below) in one call. In addition to the data array itself, additional information of the result set (column data types, column lengths, result set id) is passed on to the extension function.

Row by Row Processing

In row-by-row processing, myDBR first calls a function (see 'row_by_row_initialization' below) with the information of the result set:

$id ID of the result set (integer) to differentiate the result sets from each other

$options extension commands with parameters used in the report in an array

array( 'command1' => array ( 'cmd1param1' => value, 'cmd2param2' => value2, ... ),
       'command2' => array ( 'cmd2param1' => value, 'cmd2param2' => value2, ... ),
        ...
     )

array( 
    'name'     => array( 0, 'col1name', 1, 'col2name', ... ),
    'length'   => array( 0, col1Length, 1, col2Length, ... ),
    'datatype' => array( 0, 'col1datatype', 1, 'col2datatype', ... )
)

The data types are: char, int, float, bit, date or datetime

After each retrieved data row, myDBR then calls a function (see 'row_by_row_data_row' below). Data elements in a data row are in a simple array.

To finish up, a final function call (see 'row_by_row_finish' below) is done after all the data rows have been processed. The function has no parameters.

Extensions entry function

The entry function (defined by 'single_pass_call') has the following parameters:

$id ID of the result set (integer) to differentiate the result sets from each other

$options extension commands with parameters used in the report in an array

array( 'command1' => array ( 'cmd1param1' => value, 'cmd2param2' => value2, ... ),
       'command2' => array ( 'cmd2param1' => value, 'cmd2param2' => value2, ... ),
        ...
     )

$dataIn The data for the extension

$colInfo Meta information about the data columns: data type, name, ColumnReference

array( 
    'datatype' => array( 0 => 'col1datatype', 1 =>, 'col2datatype', ... )
    'name'     => array( 0 => 'col1name', 1 => 'col2name', ... ),
    'columnreference'   => array( 0 => 'col1colref', 1 => 'col2colref' ... ),
)

The data types are generic datatypes: char, int, float, bit, date or datetime

JavaScript

If your extension needs to include a JavaScript-file in the <HEAD>-section of the report, put the path to the JavaScript-file into the 'javascript'-element.

Extension example

As an example, here is the Google Maps-extension definition:

  'googlemaps' => array(
    'name' => 'Google Maps',
    'enabled' => true,
    'autoload' => 0,
    'php' => 'googlemaps.php', 
    'row_by_row_initialization' => '',
    'row_by_row_data_row' => '', 
    'row_by_row_finish' => '',
    'single_pass_call' => 'Ext_GoogleMaps', 
    'javascript' => array('http[s]://maps.google.com/maps/api/js?'.$google_maps_key.'libraries=visualization&amp','extensions/googlemaps/map.js'), 
    'css' => array(),
    'mydbrextension' => 1,
    'cmds' => array(
      array(
        'cmd' => 'dbr.googlemaps',
        'mode' => 1,  // 'coordinates' | 'address' (options for placing the marker)
        'title' => 0,  // Maps title
        'width' => 0,  // Width
        'height' => 0,  // Height
        'lat' => 0,    // X latitude
        'lng' => 0,    // Y longitude
        'zoom' => 0,  // Map zoom level
        'map_type' => 0,  // Map mode
      ),
      array(
        'cmd' => 'dbr.googlemaps.heatmap',
        'enabled' => 1,
        'options' => 0
      ),
      array(
        'cmd' => 'dbr.googlemaps.link_in_title',
        'enabled' => 1
      ),
      array(
        'cmd' => 'dbr.googlemaps.kml',
        'url' => 1
      ),
      array(
        'cmd' => 'dbr.googlemaps.geojson',
        'url' => 1
      ),
      array(
        'cmd' => 'dbr.googlemaps.polyline',
        'color' => 1,
        'width' => 1
      ),
      array(
        'cmd' => 'dbr.googlemaps.route',
        'travelmode' => 1
      ),
    ),
  ),

'googlemaps' is the name of the extension and therefore the PHP-file googlemaps.php, residing in the /extensions/googlemaps-directory, will contain the function GoogleMaps($id, $options, $dataIn). The extension is enabled, so the defined JavaScript is included to the report's <HEAD>-section.

'googlemaps' will introduce a command 'dbr.googlemaps' with one obligatory parameter (mode) and six optional ones (title, width, height, x, y and zoom). When the GoogleMaps-function is called, it's 2nd parameter ($options) will include the keys ['dbr.googlemaps']['parameter'] and values of these parameters.

As GoogleMaps extension has a flag 'autoload' set to 0, myDBR will not load Google JavaScript libraries automatically for each report. You can define in the report parameter page which non-autoload extensions will be loaded for the report.

Dummy extension example

Included is also (disabled by default) a dummy extension that just prints out the parsed data. You can use this as a basis for your own extensions.

A passthrough extension

A passthrough extension is an extension that takes a result set in, processes it, and passes back a result set to myDBR. This allows using extension's data in myDBR report elements. A passthrough extension works always as a single_pass_call.

A passthrough extension returns two arrays (data and columns) back to myDBR using Extension::result_set() method. The data array contains the data and the columns-array defines the column names and data types.

function Ext_Passthrough($id, $options, $dataIn, $colInfo )
{
    /*
        Do your stuff with the $dataIn which contains the data from the database.
    */

    // This is the data we'll send back to myDBR
    $data = array(
        array('ABC', 'Q1', 10, '2016-10-22'), 
        array('ABC', 'Q2', 20, '2016-10-23'),
        array('Third', 'Q1', 40, '2016-10-24')
    );

    // Define the columns: name & data type
    // Datatype needs to be one of following generic data types: char, float, int, datetime, date, time
    $columns = array(
        array('name' => 'Sector', 'datatype' => 'char'),
        array('name' => 'Quarter', 'datatype' => 'char'),
        array('name' => 'Value[v]', 'datatype' => 'int'),
        array('name' => 'Date[d]', 'datatype' => 'date')
    );

    // Pass it back to myDBR
    Extension::result_set($data, $columns);    
}