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.
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 the 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 myDBRreside in the extensions
-directory. Each extension has its own subdirectory
under extensions
-directory. The user can also define his/her 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-file the extensions.php
/ user/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 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.
The extensions.php
-file / 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
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 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).
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.
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 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.
'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
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.
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&','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.
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 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); }