Extensions

myDBR allows you to create new extensions and modify existing extensions using the extension API. The API is very simple and it 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. As an example, the Google Maps extension has one command (dbr.googlemaps) with parameters.

Extensions are written in PHP and reside in the extensions directory. Each extension has its own subdirectory under extensions-directory. The name of the directory is the same as the extension name. The PHP-file the extensions.php contains 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 extensions 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.

Processing the result set

The result sets from myDBR can be processed in a single pass or by 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 or 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_extension_function' below) in one call. In addition to the data array itself, additional information of the result set (column datatypes, column lengths, result set id) is passed on to the extension function.

Row by row processing

In row by row processing myDBR fist 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 datatypes 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.

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.

extensions.php

The extensions.php-file declares 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',
        'MY_PARAMETER' => 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 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 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 in row-by-row handling, this function will be called without parameters.
  • single_pass_call When the result set has been processed by myDBR, it will call a function defined here
  • cmds This will declare the commands associated for the extension. The first command is treated as the extension command. Subsequent commands can be used as options. For each command, there is an array element which contains the actual command ('cmd') and the named parameters. The parameters ('MY_PARAMETER) can be set as obligatory (1) or optional (0).
  • mydbrextension If enabled, 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

Extension example

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

'googlemaps' => array(
  'enabled' => true,
  'php' => 'googlemaps.php', 
  'row_by_row_initialization' => '',
  'row_by_row_data_row' => '', 
  'row_by_row_finish' => '',
  'single_pass_call' => 'Ext_GoogleMaps', 
  'javascript' => array('http://maps.google.com/maps?file=api&v=2&key='.
        'ABQEBBCEees6cEI61aec2XA1XaA-iBK1zZo_VBU9_vcD2CFGhFOW5bxWknAOxF_EPvDl-sEBB381ecgyOS6CaD'), // YOUR_GOOGLE_MAPS_API_KEY
  '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
        'x' => 0,       // X latitude
        'y' => 0,       // Y longitude
        'zoom' => 0,    // Map zoom level
    ),
  ),
),

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

You need to enable the Google Maps extension before you can use it. See more information about this under the Google Maps extension.

Dummy extension example

Included is also (disabled by default) a dummy extension which 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 which takes a result set in, processes it and passes back a result set to myDBR. This allows using extensions 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 datatypes.

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 & datatype
    // Datatype needs to be one of following generic datatypes: 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);    
}