Hi Team Helical,
I am using Helical Insight 3.0 and have also implemented dynamic DB switching. Is there a way to use cache along with dynamic db switching also? I am using MySQL
Thank You.
1 Like
Hello Anu,
Yes, it is possible to use caching even while you have implemented dynamic db switching. Basically the way caching works is we need to have a unique sqlquery. So in order to have a unique SQL Query even for different tenant dynamically, we can add something like the tenant/organization name as a comment in the SQLQuery. Hence even though the SQLquery gets changed (and thus cache can be used) but since the extra tenant text is added as a comment it will not make any affect in the output.
Now since you are using MySQL, you need to navigate to MySQL.js file present in the location (“C:\Program Files\Helical Insight\hi\hi-repository\System\Admin\SqlFunctions”). This file is responsible for creating the SQL. Replace the older content with the below content. Basically we have something called orgname within “” (via appendstring) which is added at the end of the sqlquery which is helping in changing the sqlquery for even different tenant.
function setQueryOffsetLimit(query, offset, limit, context) {
var obj = JSON.parse(context);
var username = obj.userName;
var orgname = obj.orgName;
var appendString=" \n/**"+username+orgname+"*/";
if (limit == 0) {
return query+appendString;
}
if (offset == 0) {
return query + " \nlimit " + limit+appendString;
}
return query + " \nlimit " + limit + " offset " + offset + appendString ;
}
Thank You
From Team Helical
Similarly this way a user can also open other DB JS files and make the required changes.
for instance below is the JS file for Spark which is being used by serveral DB like HIve for making query.
function setQueryOffsetLimit(query, offset, limit, context) {
var obj = JSON.parse(context);
var username = obj.userName;
var orgname = obj.orgName;
var appendString=" \n/**"+username+orgname+"*/";
var databaseName = obj.databaseName;
databaseName = "`" + databaseName + "`.";
var fromIndex = query.indexOf("\nfrom\n");
var whereIndex = query.indexOf("\nwhere\n");
var groupByIndex = query.indexOf("\ngroup by\n");
var havingIndex = query.indexOf("\nhaving\n");
var orderByIndex = query.indexOf("\norder by\n");
var fromEndIndex;
if (whereIndex > -1) {
fromEndIndex = whereIndex
} else if (groupByIndex > -1) {
fromEndIndex = groupByIndex;
} else if (havingIndex > -1) {
fromEndIndex = havingIndex;
} else if (orderByIndex > -1) {
fromEndIndex = orderByIndex
} else {
fromEndIndex = query.length;
}
var whereEndIndex;
if (groupByIndex > -1) {
whereEndIndex = groupByIndex;
} else if (havingIndex > -1) {
whereEndIndex = havingIndex;
} else if (orderByIndex > -1) {
whereEndIndex = orderByIndex
} else {
whereEndIndex = query.length;
}
var groupByEndIndex;
if (havingIndex > -1) {
groupByEndIndex = havingIndex;
} else if (orderByIndex > -1) {
groupByEndIndex = orderByIndex
} else {
groupByEndIndex = query.length;
}
var havingEndIndex;
if (orderByIndex > -1) {
havingEndIndex = orderByIndex
} else {
havingEndIndex = query.length;
}
var selectClause = query.substring(0, fromIndex);
var fromClause = query.substring(fromIndex, fromEndIndex);
var whereClause = whereIndex > -1 ? query.substring(whereIndex, whereEndIndex) : "";
var groupByClause = groupByIndex > -1 ? query.substring(groupByIndex, groupByEndIndex) : "";
var havingClause = havingIndex > -1 ? query.substring(havingIndex, havingEndIndex) : "";
var orderByClause = orderByIndex > -1 ? query.substring(orderByIndex, query.length) : "";
// databaseName = selectClause.substring(selectClause.indexOf("`"), selectClause.indexOf(".") + 1);
var selectWithoutDbName = selectClause.split(databaseName).join("");
var whereWithoutDbName = whereClause.split(databaseName).join("");
var groupByWithoutDbName = groupByClause.split(databaseName).join("");
var havingWithoutDbName = havingClause.split(databaseName).join("");
var orderByWithoutDbName = orderByClause.split(databaseName).join("");
var aliases = selectWithoutDbName.split(",");
var aliasesText = [];
var selectTexts = [];
for (aliasIndex = 0; aliasIndex < aliases.length; aliasIndex++) {
var columnText = aliases[aliasIndex].split(" as ")[1];
var selectText = aliases[aliasIndex].split(" as ")[0];
aliasesText.push(columnText);
selectTexts.push(selectText.replace("select", "") + " asc");
}
var fromToOrderBy;
if (orderByWithoutDbName) {
var splitOrder = orderByWithoutDbName.split(",");
for (var indx = 0; indx < splitOrder.length; indx++) {
splitOrder[indx] = formOrderByString(splitOrder[indx], selectWithoutDbName);
}
fromToOrderBy = splitOrder.join(",");
}
var startBracArray = [];
var endBracArray = [];
if (fromClause.includes("join")) {
for (var bracIndex = 0; bracIndex < fromClause.length; bracIndex++) {
if (fromClause[bracIndex] === "(") {
startBracArray.push(bracIndex);
}
if (fromClause[bracIndex] === ")") {
endBracArray.push(bracIndex);
}
}
var fromClauseExpected = [];
var fromClauseReplaced = [];
for (var bracCheckIndex = 0; bracCheckIndex < startBracArray.length; bracCheckIndex++) {
var bracString = "";
bracString = fromClause.substring(startBracArray[bracCheckIndex], endBracArray[bracCheckIndex]);
fromClauseExpected.push(bracString);
var replaceBracString = "";
replaceBracString = bracString.split(databaseName).join("");
fromClauseReplaced.push(replaceBracString);
}
fromClause = replaceOriginal(fromClauseExpected, fromClauseReplaced, fromClause);
}
// return fromToOrderBy ;
//Uncomment once done
var requiredQueryForSpark;
var aliasesJoined = aliasesText.join(",");
if (orderByWithoutDbName)
requiredQueryForSpark = 'select ' + aliasesJoined + 'from (' + selectWithoutDbName + ', dense_rank() over (ORDER BY ' + fromToOrderBy + ') as rank';
if (fromToOrderBy == undefined)
requiredQueryForSpark = 'select ' + aliasesJoined + 'from (' + selectWithoutDbName + ', dense_rank() over (ORDER BY ' + selectTexts.join(",") + ') as rank ';
requiredQueryForSpark += fromClause + whereWithoutDbName + groupByWithoutDbName + havingWithoutDbName + ' ) outerQuery where rank between ' + (parseInt(offset) + 1) + ' and ' + (parseInt(offset) + parseInt(limit) + '');
return requiredQueryForSpark;
//Uncomment once done
/*
//var restOfQuery = query.substring(fromIndex,query.length);
/* query = selectWithoutDbName + fromClause+ whereWithoutDbName+ groupByWithoutDbName + havingWithoutDbName +orderByWithoutDbName;
//query = databaseName;
// return query + " \nlimit " + limit + " offset " + offset;*/
}
function formOrderByString(orderByString, selectWithoutDbName) {
var ascOrDesc = “asc”;
if (orderByString.toLowerCase().indexOf(“desc”) > -1) {
ascOrDesc = “desc”
}
orderByString = orderByString.replace("order by", "").replace("asc", "").replace("desc", "").replace("\n\t", "").replace(" ", "").replace("\n", "").trim();
orderByString = getOriginalFromSelectClause(orderByString, selectWithoutDbName);
return orderByString + " " + ascOrDesc;
}
function getOriginalFromSelectClause(orderByString, selectWithoutDbName) {
var selectSplit = selectWithoutDbName.split(",");
for (var i = 0; i < selectSplit.length; i++) {
selectSplit[i] = selectSplit[i].replace(“select”, “”).replace(“distinct”, “”).replace("\n\t", “”).replace(" “, “”).replace(”\n", “”).trim();
if (selectSplit[i].split("as ")[1] === orderByString) {
return selectSplit[i].split("as ")[0];
}
}
return “”;
}
function replaceOriginal(expected, replace, query) {
for (var indx = 0; indx < expected.length; indx++) {
var actual = expected[indx];
var replaceValue = replace[indx];
if (query.includes(actual)) {
query = query.split(actual).join(replaceValue);
}
}
return query+appendString;
}