Relational database data source driver
Description
The Relational Database Source Driver is a configurable generic driver to obtain data from relational databases (tables) and create XBRL reports inside the automatization framework provided by the XBRL Mapper engine designed by Reporting Standard.
Editors
There are currently no specific editors designed for the Relational Database Source Driver configuration files. They are XML files than can be created using standard XML editors.
Configuration file
The configuration file is provided during the driver declaration inside the instance document template file. See XBRL Template File#Definition_of_data_sources for more information. The driver class is com.ihr.xbrl.mapper.source.SQLDataSource
Sample SQL Driver data source declaration:
<source lang="xml">
<datasources>
<source
id="source0"
class="com.ihr.xbrl.mapper.source.SQLDataSource"
config="SQL-driver-config.xml"/>
</datasources>
</source>
The configuration file must be valid according to the XML Schema that has the following namespace http://www.reportingstandard.com/source/driver/SQLDriver-2009 and that is published in the following official location http://www.reportingstandard.com/schemas/source/XBRLDataSourceSQL-2009.xsd
Sample configuration file: <source lang="xml"> <XBRLDataSourceSQL xmlns="http://www.reportingstandard.com/source/driver/SQLDriver-2009" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.reportingstandard.com/source/driver/SQLDriver-2009 http://www.reportingstandard.com/schemas/source/XBRLDataSourceSQL-2009.xsd">
<connection id="con0"> <url>jdbc:mysql://localhost:3306/testdb</url> <user>root</user> <password>xbrl</password> </connection> <precision>INF</precision>GovernmentGrantsDisclosuresMasterTuple
<field identifier="ifrs-gp_GovernmentGrantsDisclosures" name="GovernmentGrantsDisclosures" /> <field identifier="ifrs-gp_DetailOfGovernmentGrants" name="DetailOfGovernmentGrants" /> <field identifier="ifrs-gp_AmountOfGovernmentGrantsNettedAgainstReportedExpenses" name="AmountOfGovernmentGrantsNettedAgainstReportedExpenses" /> <where>fiscalCode = '12345'</where>
<decimals>0</decimals>
GovernmentGrantsDisclosuresDetailsTuple<field identifier="ifrs-gp_DescriptionOfGovernmentGrant" name="DescriptionOfGovernmentGrant"/> <field identifier="ifrs-gp_AmountOfGovernmentGrantRecognised" name="AmountOfGovernmentGrantRecognised"/> <field identifier="ifrs-gp_ExplanationOfUnfulfilledConditionsAndOtherContingenciesAttachingToGovernmentAssistance" name="ExplanationOfUnfulfilledConditions"/>
<where>fiscalCode = '12345'</where>
<decimals>INF</decimals>
GovernmentGrantsDisclosuresAmounts<field identifier="GovermentGrantTuple" name="rut"/> <where>fiscalCode = '12345'</where>
<decimals>INF</decimals>
GeneralData <statement>
select
row_id as id,
companyname,
substring(companyid from 1 to length(companyid)-2) as compid,
from GeneralData
where row_id = ${MY_ROW_ID}
</statement>
<field identifier="regulator-gp_CompanyName" name="companyname"/>
<field identifier="regulator-fiscalCode" name="compid"/>
</XBRLDataSourceSQL> </source>
Each data element above defines a select statement to the database in order to retrieve information that may be relevant to create an XBRL report. Each data element has a connRef attribute that identify the database connection parameters.
The content of a data element is first either a decimals or a precision element that will be relevant for all numeric fields obtained by this select statement.
The next element is a table element that contains the SQL table o view name used to build the select statement.
After the table element there may be a statement element (this is optional) if present, the content of the statement element will replace the select statement that the driver creates for you automatically using the table name and selected fields.
Following the statement element there is a sequence of one or unlimited number of field elements. A field element maps a table field with a generation identifier name. The field name is indicated in the name attribute and the event generation identifier name is indicated in the identifier attribute.
Other optional attributes on the field element are:
- The count attribute. This attribute, if present, contains a select statement in order to allow the driver detect how many times there is data available for the field when creating facts in the instance document.
- The keys attribute. This attribute, if present, contains a sequence of keys that will be used to obtain the values for the elements returned in the count attribute. The existence of the keys attribute requires a keyRef attribute on a data element that will be executed for each key returned in the keys attribute.
Optionally, there may exist one where element that contains the text of a where clause that will be added to the end of the select statement.
Parameters
From the API, it is possible to send parameters to the SQL statements using a Properties object as a parameter to the map method. The following code adds a parameter that is later replaced in the SQL statements:
<source lang="java">
// uConfig is the URI of the configuration file MapEngine me = new MapEngine(uConfig);
Properties props = new Properties();
props.setProperty("SELECTED_RECORD", idSelectedRecord);
Object parameters[] = { props };
// Generamos el informe XBRL XBRLInstance instance = me.map(parameters);
</source>
The text ${SELECTED_RECORD} will be replaced by the value of the idSelectedRecord variable.
<source lang="xml">
<decimals>INF</decimals>
GovernmentGrantsDisclosuresAmounts<field identifier="GovermentGrantTuple" name="rut"/> <where>fiscalCode = ${SELECTED_RECORD}</where>
</source>
When the statement is executed
In the Mapper to driver data exchange dialog, during the 2nd question, the mapper asks the driver about how may fact items for a specific mapping event are in the data source (using the identifier as the key). At this time, if the query has not been executed before, it is executed now (and the result is cached during the live of the execution of the XBRL report).
