(function () {
var myConnector = tableau.makeConnector();
var schema = [];
var server = 'localhost';
var port = 8086;
var db = '';
var debug = true; // set to true to enable JS Console debug messages
var protocol = 'http://'; // default to non-encrypted. To setup InfluxDB with https see https://docs.influxdata.com/influxdb/v1.2/administration/https_setup/
var useAuth = false; // bool to include/prompt for username/password
var username = '';
var password = '';
var queryString_Auth; // string to hold the &u=_user_&p=_pass_ part of the query string
var queryString_Auth_Log; // use for logging the redacted password
var queryType = 'all'; // var to store query type
var interval_time = '30'; // value for the group by time
var interval_measure = 'm'; // h=hour, m=min, etc
var interval_measure_string = 'minutes'; // full string for interval
var aggregation = 'mean'; // value for aggregating database value
var customSql = ''; // value for custom SQL as user typed it
var customSqlSplit = {}; // values of query part of custom sql; to be used with getData
// from https://docs.influxdata.com/influxdb/v1.2/write_protocols/line_protocol_tutorial/#special-characters-and-keywords
// Influx allows <, = space "> which can't be used as a Tableau id field (https://github.com/tagyoureit/InfluxDB_WDC/issues/3)
// Tableau only allows letters, numbers or underscores
function replaceSpecialChars_forTableau_ID(str) {
var newStr = str.replace(/ /g, '_')
.replace(/"/g, '_doublequote_')
.replace(/,/g, '_comma_')
.replace(/=/g, '_equal_')
.replace(/\//g, '_fslash_')
.replace(/-/g, '_dash_')
.replace(/\./g, '_dot_')
.replace(/[^A-Za-z0-9_]/g, '_');
return newStr;
}
function influx_escape_char_for_URI(str) {
var newStr = str.replace(/\\/g, '\\\\');
newStr = newStr.replace(/\//g, '//');
newStr = newStr.replace(/ /g, '%20');
newStr = newStr.replace(/"/g, '\\"');
return newStr;
}
function resetSchema() {
schema = [];
console.log('Schema has been reset');
}
resetSchema();
function queryStringTags(index, queryString_tags) {
if (debug) console.log('Retrieving tags with query: %s', queryString_tags);
// Create a JQuery Promise object
var deferred = $.Deferred();
$.getJSON(queryString_tags, function (tags) {
if (debug) console.log('tag query string for ' + index + ': ' + JSON.stringify(tags));
// this if statement checks to see if there is an empty series (just skip it)
// empty resultset: tag query string for 7: {"results":[{"statement_id":0}]}
if (tags.results[0].hasOwnProperty('series')) {
// Create a factory (array) of async functions
var deferreds = (tags.results[0].series[0].values).map(function (tag, tag_index) {
if (debug) console.log('in queryStringTags. tag: ' + tag[0] + ' tag_index: ' + tag_index);
schema[index].columns.push({
id: replaceSpecialChars_forTableau_ID(tag[0]),
alias: tag[0],
dataType: tableau.dataTypeEnum.string,
});
if (debug) console.log(JSON.stringify(schema));
});
}
// Execute all async functions in array
return $.when.apply($, deferreds)
.then(function () {
if (debug) console.log('finished processing tags');
deferred.resolve();
});
})
.fail(function (jqXHR, textStatus, errorThrown) {
console.log(JSON.stringify(errorThrown));
console.log(errorThrown)
tableau.abortWithError(errorThrown);
doneCallback();
});
return deferred.promise();
}
function queryStringFields(index, queryString_fields) {
var deferred = $.Deferred();
if (debug) console.log('Retrieving fields with query: ' + queryString_fields);
$.getJSON(queryString_fields, function (fields) {
// this if statement checks to see if there is an empty series (just skip it)
// empty resultset: tag query string for 7: {"results":[{"statement_id":0}]}
if (fields.results[0].hasOwnProperty('series')) {
var deferreds = (fields.results[0].series[0].values).map(function (field, field_index) {
if (debug) console.log('in queryStringFields. field: ' + field[0] + ' field_index: ' + field_index);
var id_str,
alias_str;
if (queryType === 'aggregation') {
id_str = aggregation + '_' + replaceSpecialChars_forTableau_ID(field[0]);
alias_str = aggregation + '_' + field[0];
} else if (queryType === 'all') {
id_str = replaceSpecialChars_forTableau_ID(field[0]);
alias_str = field[0];
}
// force the correct mapping of data types
var tabDataType;
switch (field[1]) {
case 'float':
tabDataType = tableau.dataTypeEnum.float;
break;
case 'integer':
tabDataType = tableau.dataTypeEnum.int;
break;
case 'string':
tabDataType = tableau.dataTypeEnum.string;
break;
case 'boolean':
tabDataType = tableau.dataTypeEnum.bool;
break;
}
schema[index].columns.push({
id: id_str,
alias: alias_str,
dataType: tabDataType,
});
});
}
return $.when.apply($, deferreds)
.then(function () {
if (debug) console.log('finished processing fields');
deferred.resolve();
});
})
.fail(function (jqXHR, textStatus, errorThrown) {
tableau.abortWithError(errorThrown);
console.log('INFLUX ERROR!');
console.log(errorThrown);
doneCallback();
});
return deferred.promise();
}
function addTimeTag() {
// the "time" tag isn't returned by the schema. Add it to every measurement.
$.each(schema, function (index, e) {
schema[index].columns.unshift({
id: 'time',
dataType: tableau.dataTypeEnum.datetime,
});
});
}
function sleep(ms) {
return new Promise(function(resolve){setTimeout(resolve, ms)});
}
function checkForDuplicateNames() {
// Duplicate fields are too hard to use
// https://docs.influxdata.com/influxdb/v1.8/troubleshooting/frequently-asked-questions/#tag-and-field-key-with-the-same-name
// Remove the measurement and raise an alert
var s = schema.slice();
var removed = [];
console.log(Object.keys(s))
// loop through each column
for (var c = 0; c < schema.length; c++){
var measurement = schema[c];
console.log(measurement.id)
var list = [measurement.columns[0].id];
console.log(list);
for (var f = 1; f < measurement.columns.length; f++){
var curr = measurement.columns[f].id;
if (list.indexOf(curr) === -1) {
list.push(curr); // no match
}
else {
console.log('MATCH: duplicate field/tag: ' + measurement.id + ', ' + curr);
removed.push(measurement.id+'/'+curr);
// remove from original schema
// find new index
var idx = s.findIndex(function(el){return el.id === measurement.id});
s.splice(idx, 1);
}
}
}
if (removed.length){
schema = s;
influx_alert('Duplicate tag/keys found in the following measurements. Please use custom sql to query', removed.join(", ")+ '\nThis window will close automatically in 5s.');
console.log(removed)
return sleep(5000);
}
}
function getMeasurements(db, queryString) {
// Get all measurements (aka Tables) from the DB
$.getJSON(queryString, function (resp) {
if (debug) console.log('retrieved all measurements: ' + resp);
if (debug) console.log('resp.results[0].series[0].values: ' + resp.results[0].series[0].values);
// for each measurement, save the async function to a "factory" array
var deferreds = (resp.results[0].series[0].values).map(function (measurement, index) {
schema[index] = {
id: replaceSpecialChars_forTableau_ID(measurement[0]),
alias: measurement[0],
incrementColumnId: 'time',
columns: [],
};
if (debug) console.log(schema);
if (debug) console.log('analyzing index: ' + index + ' measurement: ' + measurement[0]);
if (debug) console.log('schema now is: ' + schema);
var deferred_tags_and_fields = [];
// Get the tags (items that can be used in a where clause) in the measurement
var newM = influx_escape_char_for_URI(measurement[0]);
var queryString_tags = protocol + server + ':' + port + '/query?q=SHOW+TAG+KEYS+FROM+%22' + newM + '%22&db=' + db;
if (useAuth) {
setAuth();
queryString_tags += queryString_Auth;
}
// Get fields/values
var queryString_fields = protocol + server + ':' + port + '/query?q=SHOW+FIELD+KEYS+FROM+%22' + newM + '%22&db=' + db;
if (useAuth) {
setAuth();
queryString_fields += queryString_Auth;
}
deferred_tags_and_fields.push(queryStringTags(index, queryString_tags));
deferred_tags_and_fields.push(queryStringFields(index, queryString_fields));
return $.when.apply($, deferred_tags_and_fields)
.then(function () {
if (debug) console.log('finished processing queryStringTags and queryStringFields for ' + measurement[0]);
});
});
return $.when.apply($, deferreds)
.then(function () {
if (debug) console.log('Finished getting ALL tags and fields for ALL measurements. Hooray!');
if (debug) console.log('schema is now: ' + JSON.stringify(schema));
})
.then(addTimeTag)
.then(checkForDuplicateNames)
.then(function () {
if (debug) console.log('schema finally: ' + JSON.stringify(schema));
// Once we have the tags/fields enable the Load button
loadSchemaIntoTableau();
});
})
.fail(function (jqXHR, textStatus, errorThrown) {
console.log('INFLUX ERROR!');
console.log(errorThrown);
tableau.abortWithError(errorThrown);
doneCallback();
});
}
function modifyLimitAndSlimit(sql) {
// this function modifies/add series and row limits so we only get 1 row of data back for the schema.
// On the getData side, we will union all of these series together.
console.log('sql before regex:', sql);
var limitRegex = /\b(limit\s\d{0,10})/gmi;
var slimitRegex = /\b(slimit\s\d{0,10})/gmi;
if (sql.search(limitRegex) === -1) {
// no limit x in sql
sql += ' limit 1';
}
else {
// limit x found; replace with limit 1
sql = sql.replace(limitRegex, ' limit 1');
}
if (sql.search(slimitRegex) === -1) {
// no slimit x in sql
sql += ' slimit 1';
}
else {
// slimit x found; replace with limit 1
sql = sql.replace(slimitRegex, ' slimit 1');
}
return sql;
}
function buildCustomSqlString(db, _customSql) {
var modifiedCustomSql = modifyLimitAndSlimit(_customSql);
var queryString = protocol + server + ':' + port + '/query?q=' + encodeURIComponent(modifiedCustomSql) + '&db=' + db;
if (useAuth) {
setAuth();
queryString += queryString_Auth;
}
if (debug) console.log('Custom SQL url: ' + queryString);
return queryString;
}
function getCustomSqlSchema(queryString, originalSql) {
var deferred = new $.Deferred();
$.getJSON(queryString)
.done(function (resp) {
var _schema = [];
if (!resp.results[0].hasOwnProperty('series')) {
influx_alert('No rows returned', JSON.stringify(resp));
}
else {
if (debug) console.log('retrieved custom sql response: ' + JSON.stringify(resp));
if (debug) console.log('resp.results[0].series[0].values: ' + JSON.stringify(resp.results[0].series[0].values));
var cols = [];
// columns/fields
resp.results[0].series[0].columns.forEach(function (el, index) {
if (el === 'time') {
type = tableau.dataTypeEnum.datetime;
}
else {
type = enumType(resp.results[0].series[0].values[0][index]);
}
cols.push({
id: replaceSpecialChars_forTableau_ID(el),
alias: el,
dataType: type,
});
});
// tags; will only be present with multiple group by clauses
if (resp.results[0].series[0].hasOwnProperty('tags')) {
for (var el in resp.results[0].series[0].tags) {
cols.push({
id: replaceSpecialChars_forTableau_ID(el),
alias: el,
dataType: tableau.dataTypeEnum.string,
sql: queryString,
});
}
}
_schema = {
id: replaceSpecialChars_forTableau_ID(resp.results[0].series[0].name),
alias: resp.results[0].series[0].name,
//incrementColumnId: "time",
columns: cols,
};
customSqlSplit[resp.results[0].series[0].name] = originalSql;
if (debug) console.log('schema for query: ' + JSON.stringify(_schema));
schema.push(_schema);
deferred.resolve();
}
})
.fail(function (jqXHR, textStatus, errorThrown) {
console.log('INFLUX ERROR getCustomSqlSchema!');
console.log('jqXHR: ' + JSON.stringify(jqXHR));
console.log('textStatus: ' + JSON.stringify(textStatus));
console.log('errorThrown: ' + JSON.stringify(errorThrown));
influx_alert('Error parsing sql', 'Response error: ' + errorThrown + '
Response text: ' + JSON.stringify(jqXHR.responseJSON));
});
return deferred.promise();
}
function parseCustomSql(db, _customSql) {
// Get all measurements (aka Tables) from the DB
/*
Sample of return values for single series
{
"results"
:
[
{
"statement_id": 0,
"series": [
{
"name": "tank_level",
"columns": [
"time",
"max_gallons_of_chemical",
"max_gallons_of_water",
"max_status",
"max_strength_of_chemical",
"max_total_gallons"
],
"values": [
[
"1970-01-01T00:00:00Z",
1,
1,
1,
0.145,
2
]
]
}
]
}
]
}
Sample of return for multiple series
{
"results"
:
[{
"statement_id": 0,
"series": [{
"name": "tank_pump",
"tags": {"pump": "acid"},
"columns": ["time", "integral"],
"values": [["2018-06-29T05:00:00Z", 2.881666666666667], ["2018-06-29T06:00:00Z", 3.4783333333333335], ["2018-06-29T07:00:00Z", 3.4008333333333334], ["2018-06-29T08:00:00Z", 3.974166666666667], ["2018-06-29T09:00:00Z", 4.004166666666667], ["2018-06-29T10:00:00Z", 3.9775], ["2018-06-29T11:00:00Z", 3.9000000000000004], ["2018-06-29T12:00:00Z", 3.9608333333333334]]
}],
"partial": true
}]
}
{
"results"
:
[{
"statement_id": 0,
"series": [{
"name": "tank_pump",
"tags": {"pump": "chlorine"},
"columns": ["time", "integral"],
"values": [["2018-06-29T04:00:00Z", 3.706666666666667], ["2018-06-29T05:00:00Z", 1.0125]]
}],
"partial": true
}]
}
{
"results"
:
[{
"statement_id": 0,
"series": [{
"name": "tank_pump",
"tags": {"pump": "acid"},
"columns": ["time", "integral"],
"values": [["2018-06-29T13:00:00Z", 0.8616666666666667]]
}]
}]
}
*/
customSql = _customSql;
deferred_array = [];
if (customSql.indexOf(';') !== -1) {
customSqlArray = customSql.split(';');
// can have select * from measurement; and still be a single query
if (customSqlArray.length > 1) {
if (debug) console.log('Multiple sql statements (${customSqlArray.length}) found for ' + customSql + ':' + customSqlArray);
}
// for each query, get tables
for (var i = 0; i < customSqlArray.length; i++) {
if (customSqlArray[i].length > 6){
var newsql = buildCustomSqlString(db, customSqlArray[i]);
deferred_array.push(getCustomSqlSchema(newsql, customSql));
}
else {
console.log('Skipping SQL fragment: ' + customSqlArray[i]);
}
}
}
else {
var newsql = buildCustomSqlString(db, customSql);
deferred_array.push(getCustomSqlSchema(newsql, customSql));
}
resetSchema();
$.when.apply($, deferred_array)
.then(function () {
if (debug) console.log('finished processing all cust sql for ' + JSON.stringify(customSql));
// Once we have the schema enable the Load button
if (debug) console.log('custom sql schema finally: ' + JSON.stringify(schema));
loadSchemaIntoTableau();
});
}
function enumType(type) {
if (isNaN(type) === true) {
return tableau.dataTypeEnum.string;
}
else {
return tableau.dataTypeEnum.float;
}
}
function setAuth() {
username = $('#username')
.val();
password = $('#password')
.val();
queryString_Auth = '&u=' + username + '&p=' + password;
queryString_Auth_Log = '&u=' + username + '&p=[redacted]';
}
function getDBs() {
try {
$('.proto_sel')
.click(function () {
if (debug) {
console.log('Protocol changed to: ' + $(this)
.text());
}
$('.proto_sel').parent().parent().find('.btn').html($(this)
.text() + ' ')
// $(this)
// .html($(this)
// .text() + ' ');
// $(this)
// .val($(this)
// .data('value'));
protocol = $(this)
.text();
})
$('#interval_time')
.change(function () {
if ($(this)
.val() === '') {
interval_time = $(this)
.prop('placeholder');
} else {
interval_time = $(this)
.val();
}
});
// retrieve the list of databases from the server
$('#tableButton')
.click(function () {
// Reset the dropdown in case the user selects another server
$('.selectpicker')
.html('');
$('.selectpicker')
.selectpicker('refresh');
if ($('#servername')
.val() !== '') {
server = $('#servername')
.val();
} else {
server = 'localhost';
}
if ($('#serverport')
.val() !== '') {
port = $('#serverport')
.val();
} else {
port = 8086;
}
var queryString_DBs = protocol + server + ':' + port + '/query?q=SHOW+DATABASES';
if (useAuth) {
setAuth();
queryString_DBs += queryString_Auth;
}
if (debug) console.log('Retrieving databases with querystring: ' + queryString_DBs);
$.ajax({
url: queryString_DBs,
dataType: 'json',
timeout: 3000,
success: function (resp) {
if (debug) console.log(resp.results[0].series[0].values);
$('.selectpicker')
.html('');
$.each(resp.results[0].series[0].values, function (index, value) {
$('')
.appendTo('.selectpicker');
});
$('.selectpicker')
.selectpicker('refresh');
// Once we have the databases, enable the 'load schema' button
$('#getSchemaButton')
.prop('disabled', false);
},
})
.done(function () {
// alert("done")
})
.fail(function (err) {
console.log('INFLUX ERROR!');
console.log(JSON.stringify(err))
console.log(err);
influx_alert('Error loading database', JSON.stringify(err) + '\n If you are using 2019.4 or later you may be experiencing a CORS limitation. You need to enable HTTPS on Influx (https://docs.influxdata.com/influxdb/v1.8/administration/https_setup/) or install this extension locally and run it from an http server.');
});
});
$('#db_dropdown')
.on('changed.bs.select', function (e) {
if (debug) console.log(e.target.value + ' has been selected');
// reset the schema if the database selection changes
resetSchema();
});
$('#getSchemaButton')
.click(function () {
db = $('#db_dropdown option:selected')
.text();
if (queryType === 'custom'){
parseCustomSql(db, $('#customSql')
.val());
}
else {
var queryString = protocol + server + ':' + port + '/query?q=SHOW+MEASUREMENTS&db=' + db;
if (useAuth) {
setAuth();
queryString += queryString_Auth;
}
getMeasurements(db, queryString);
}
});
console.log('done with getDBs')
} catch (err) {
console.log(JSON.stringify(err));
tableau.abortWithError(err);
doneCallback();
}
}
function influx_alert(errorType, err) {
console.log(err);
$('#influx_alert')
.html('×