Dynamic Database Switching

Introduction: Helical Insight metadata connects with a database which is predefined. Now there might be certain cases in which we would like the metadata to connect to different data sources based on certain condition, for example, different organization etc. Here we have explained how the metadata is connected to different data sources based on logged in organization or user.

Prerequisite: Database schema for all the databases must be the same. In this case, we have assumed that the logged in user or organization name is also the name of the database.
Please follow the below steps for dynamic DB switching

1. Create an EFWD file at the following location:  \hi\hi-repository

From 4.0 onwards, we have 2 options to create database connections:

1. Managed Groovy JDBC Connection – supports connection pooling and ideally this approach is more preferrable as compared to plain groovy jdbc.
2. Groovy Plain JDBC Connection

Either of the 2 options can be used.

First thing to do is go to the location “..\hi\hi-repository” and create a new folder here from the backend. Then you have to create an EFWD file and save it in this newly created folder. On the next steps EFWD file content is specified. The idea of creating it from the backend is so that the folder is public (and thus the created EFWD file is public) and it does not explicitly require sharing.



1. Managed Groovy JDBC Connection – If you want to use Managed Groovy JDBC Connection, add the below code in the EFWD file.

<EFWD>
<DataSources>
<Connection id="1" type="sql.jdbc.groovy.managed">
<Driver>dynamicSwitch</Driver>
<Condition>

<![CDATA[
import groovy.sql.Sql;
import net.sf.json.JSONObject;
import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
public JSONObject evalCondition() {
JSONObject responseJson = new JSONObject();
String profileValue = GroovyUsersSession.getValue('${profile[\'ConnectionID\']}');
profileValue = profileValue.replaceAll("'","");

int globalid = profileValue.toInteger()

responseJson.put("globalId", globalid);

responseJson.put("type","global.jdbc");
return responseJson;
}
]]>

</Condition>
</Connection>
</DataSources>
</EFWD>

2. If you want to use Groovy Plain JDBC Connection, add the below code to the EFWD file.

<EFWD>
<DataSources>
<Connection id="6" type="sql.jdbc.groovy">    //connectionid can be any number
<Driver>com.mysql.jdbc.Driver</Driver>		  
<Url>jdbc:mysql://192.168.2.94:3306/SampleTravelData</Url>  //URL will change according to connection string of your database, you can also pass additional parameters for SSL in this URL
<User>root</User>      //database user name
<Pass>root</Pass>		//database password
	<Condition>
	<![CDATA[
	      import groovy.sql.Sql;
	      import net.sf.json.JSONObject;
	      import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
	      public JSONObject evalCondition() {
		JSONObject responseJson = new JSONObject();
		String orgName = GroovyUsersSession.getValue('${org}.name');
//String userName = GroovyUsersSession.getValue('${user}.name');
		orgName = orgName.replaceAll("'","");
//userName  = userName .replaceAll("'","");
		responseJson.put("url","jdbc:mysql://192.168.2.94:3306/"+orgName);
		//responseJson.put("url","jdbc:mysql://192.168.2.94:3306/"+userName );
	          return responseJson;
	      }
	    ]]>
</Condition>
</Connection>
</DataSources>
</EFWD>

2. In this above code, you have to pass your database connection details. Here if you want database switching based on logged in user then you can comment all the lines related to orgName and uncomment all the lines with userName.
3. Now go to the following location and open JS file of the database you are using.

Location:: \hi\hi-repository\System\Admin\SqlFunctions

Here I have used MySQL so I will open mysql.js. Replace the following code with previous code (The Highlighted portion is the changes that have been done):

function setQueryOffsetLimit(query, offset, limit,context) {

	var obj = JSON.parse(context);
	var orgName = obj.orgName;
	var userName = obj.userName;
	query = query.split("#org#").join(orgName);
//query = query.split("#user#").join(userName );
    if (limit == 0) {
        return query;
    }

    if (offset == 0) {
        return query + " \nlimit " + limit;
    }

    return query + " \nlimit " + limit + " offset " + offset;
}

4. Create organizations and users in Helical Insight. Note that here the organization name must be similar to the database which is created.
5. Now open Helical Insight application. Click on data source then click on Advance. You will see the various types of connections that can be established. If you are using Managed Groovy JDBC Connection, click on Managed Groovy JDBC Datasource and provide the details.

If you are using Groovy Plain JDBC connection, click on Groovy Plain JDBC Connection option and provide the details.

Now test connection for the created data source. We can see the connection id here which is provided in the EFWD file, also the name of the folder in which you have saved your EFWD file is visible in DIR.



6. Now create metadata by clicking on the “metadata icon”. (rightmost icon in Actions_ from the above Groovy source.) By default in current database field the database name is visible we have to remove that current database name and then save the metadata.
7. Share the metadata with organization/users etc who will be viewing the reports and dashboards.
8. Now create the report. When you log in as other organization/user you will see the metadata dynamically switching to other data sources.
9. Open report you will be able to see data from that particular database. The same can also be verified by clicking on SQLViewer and verifying the SQLQuery.