Adding support for additional databases to ReportServer (Firebird)
By default ReportServer is shipped with support only for the most common database systems, although it can interact with virtually every database, that offers a JDBC compliant driver. In this post we will show how to develop a groovy script that adds support for the firebird database (www.firebirdsql.org) to ReportServer.
Although SQL is well standardized and the JDBC specification defines a vendor independent interface to the actual database driver, simply adding the database driver to the classpath does not suffice. ReportServer tries to use mostly ANSI-SQL whenever possible, but in some situations not using a vendor-specific extension bears a high performance penalty. One of these situations is the limit/offset-mechanism, that allows the application to request only a certain chunk of the resultset. Most databases offer some solution to this problem, but there is no standard approach. To manage these differences in different sql-dialects ReportServer uses a DatabaseHelper class for each supported sql dialect.
To add support for a new datatabase, what we need to do, is to implement a DatabaseHelper for the database and register it with ReportServer.
Lets start by looking at the DatabaseHelper for the H2 database engine:
public class H2 extends DatabaseHelper {
public static final String DB_NAME = "H2";
public static final String DB_DRIVER = "org.h2.Driver";
public static final String DB_DESCRIPTOR = "DBHelper_H2";
@Override
public String getDescriptor() {
return DB_DESCRIPTOR;
}
@Override
public String getDriver() {
return DB_DRIVER;
}
@Override
public String getName() {
return DB_NAME;
}
}
The three methods getDescriptor, getName and getDriver are the minimum, each DatabaseHelper has to implement.
getName() – The text the user is shown on the frontend, when selecting the database type
getDriver() – The name of the JDBC driver class
getDescriptor() – The key used internally to map datasources to DatabaseHelpers
To adapt this to the firebird database, we simply change the values of the three constants (and the name of the class).
class Firebird extends DatabaseHelper {
public static final String DB_NAME = "Firebird";
public static final String DB_DRIVER = "org.firebirdsql.jdbc.FBDriver";
public static final String DB_DESCRIPTOR = "DBHelper_Firebird";
@Override
public String getDescriptor() {
return DB_DESCRIPTOR;
}
@Override
public String getDriver() {
return DB_DRIVER;
}
@Override
public String getName() {
return DB_NAME;
}
}
If we added this class to ReportServer, we would already be able to execute Birt and Jasper Reports where the query is simply passed through, but using a dynamic list with a firbird datasource would still fail.
There are basically three more changes to be made to fully support firebird from within ReportServer.
To test a database connection ReportServer tries to execute a dummy query. Our default implementation of this is SELECT * FROM DUAL, but this does not work with firebird. So we override the createDummyQuery method of our DatabaseHelper and provide alternative implementation:
@Override
public String createDummyQuery() {
return 'select 1 dummy from rdb$database';
}
The other two enhancement concern the earlier mentioned matter of limit and offset. ReportServers default implementation creates queries with LIMIT and OFFSET keywords at the end, so we need to provide a different implementation for firebird.
@Override
public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
return new LimitQuery(nestedQuery, queryBuilder){
@Override
public void appendToBuffer(StringBuffer buf) {
buf.append("SELECT FIRST ");
buf.append(queryBuilder.getLimit());
buf.append(" * FROM (");
nestedQuery.appendToBuffer(buf);
buf.append(") limitQry");
}
}
}
@Override
public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
return new OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
@Override
public void appendToBuffer(StringBuffer buf) {
buf.append("SELECT FIRST ");
buf.append(queryBuilder.getLimit());
buf.append(" SKIP ");
buf.append(queryBuilder.getOffset());
buf.append(" * FROM (");
nestedQuery.appendToBuffer(buf);
buf.append(") limitQry");
}
}
}
The way ReportServer constructs dynamic queries is by basically wrapping multiple layers of sql around each other. The LimitQuery follows the same approach. It has access to an nestedQuery and is asked to write itself into the supplied buffer, wrapping this nested query.
The final step is to register the newly created DatabaseHelper with ReportServer.
To easily add extensions to ReportServer without the need to recompile, we implemented a mechanism we called hooks. A hook is a named extension-point to wich arbitrary objects (implementing a certain interface) can be attached. The hook responsible for the loading of available DatabaseHelpers is called DatabaseHelperProviderHook and defined by the following interface
package net.datenwerke.rs.base.service.dbhelper.hooks;
public interface DatabaseHelperProviderHook extends Hook {
public Collection provideDatabaseHelpers();
}
To attach a new Object to this hook, we use a script calling the callbackRegistries attachHook method.
def HOOK_NAME = "DATASOURCE_HELPER_FIREBIRD"
def callback = [
provideDatabaseHelpers : {
return Collections.singletonList(new Firebird());
}
] as DatabaseHelperProviderHook;
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, DatabaseHelperProviderHook.class, callback)
Specifying a hook name ensures, that this hook will not inadvertently get registered twice.
Putting it all together and adding all the necessary import statements we obtain a script similar to the one available for download here: AddFirebirdSupport.groovy
If you have not already placed the firebird jdbc driver on your classpath, do so now. Downlaod Jaybird from http://www.firebirdsql.org/en/jdbc-driver/ and put the files jaybird-2.2.3.jar and lib/connector-api-1.5.jar into the WEB-INF/lib subdirectory of your ReportServer installation. You might have to restart ReportServer afterwards.
Now open ReportServer and change to the filesystem admin module. Put the script somewhere below the bin directory in the fileserver.
To execute the script open the terminal by pressing CTRL+ALT+T and use the cd command to change into the directory where you placed the script
cd /fileserver/bin
Execute the script by typing exec -g AddFirebirdSupport.groovy
The -g argument is important, to execute the script in the global context. Otherwise the new DatabaseHelper will only be present in your current session. After pressing F5 to reload your browser you will be able to select Firebird as the database type, when creating a new datasource.
Beware, that after restarting ReportServer hooks attached from the terminal will no longer be present. To automatically attach a hook on startup put the script in the bin/onstartup.d directory in the fileserver.