statsd/endpoint/mysql/index.js

var express = require('express');
var morgan = require('morgan');
var bodyParser = require('body-parser');
var _ = require('lodash');
var app = express();
var curlify = require('request-as-curl');

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'mysql',
    user: 'root',
    password: '235r2342gtfsw',
    database: 'statsd_db',
    multipleStatements: true
});

app.use(morgan('combined'))

app.use(bodyParser.json());

function setCORSHeaders(res) {
    res.setHeader("Access-Control-Allow-Origin", "*");
    res.setHeader("Access-Control-Allow-Methods", "POST");
    res.setHeader("Access-Control-Allow-Headers", "accept, content-type");
}

app.all('/', function(req, res) {
    setCORSHeaders(res);
    console.log(curlify(req, req.body));
    res.send('https://grafana.com/plugins/grafana-simple-json-datasource\n');
    res.end();
});

var tables = ['gauges_statistics', 'counters_statistics', 'timers_statistics', 'sets_statistics'];

app.all('/search', function(req, res) {
    console.log(curlify(req, req.body));
    var mysql_search_result = [];
    var target = req.body.target; // metric

    var results = tables.map(function(table) {
        return new Promise((resolve, reject) => {
            connection.query('SELECT DISTINCT(`name`) FROM `' + table + '` WHERE `name` LIKE ("%' + target + '%") ORDER BY `name`', function(err, rows, fields) {
                if (err) {
                    console.log(err);
                    reject(err);
                }
		var rtr = [];
                for (var i = 0; i < rows.length; i++) {
                    if (rtr.indexOf(rows[i].name) === -1) { // Se agregan solo los name que no esten en result
                        rtr.push(rows[i].name);
                    }
                }

                resolve(rtr);
            });
        });
    });

    Promise.all(results).then(v => {
        setCORSHeaders(res);
	console.log(v);
	for(var i = 0; i < v.length; i++){
	    mysql_search_result = mysql_search_result.concat(v[i]);   
	}
        res.json(mysql_search_result);
        res.end();
    }, reason => {
        console.log(reason)
        res.status(500).send(reason);
    });
});

app.all('/query', function(req, res) {
    console.log(curlify(req, req.body));
    var mysql_query_result = [];

    var from = new Date(req.body.range.from);
    var to = new Date(req.body.range.to);
    var from_str = Math.floor(from.getTime() / 1000);
    var to_str = Math.floor(to.getTime() / 1000);
    var names = _.map(req.body.targets, function(t) {
        return t.target;
    });

    var results = tables.map(function(table) {
        return new Promise((resolve, reject) => {
            var interval = req.body.intervalMs / 1000;
            var mode = "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ";
            var sql = mode + 'SELECT `timestamp`, `name`, `value` FROM `' + table + '` WHERE (`timestamp` BETWEEN ' + from_str + ' AND ' + to_str + ') AND (`name` IN ("' + names.join('", "') + '"))';
            var group_by = '';
            var order_by = ' ORDER BY `timestamp` ASC';
            var maxDataPoints = req.body.maxDataPoints;
            var limit = ' LIMIT ' + maxDataPoints + ';';
            sql += group_by + order_by + limit;
	    console.log(sql);	
            connection.query(sql, function(err, rows, fields) {
                if (err) {
                    console.log(err);
                    reject(err);
                }

                var result = [];
                var results = rows[1];
                for (var i = 0; i < results.length; i++) {
                    var name = results[i].name;
                    if (name !== undefined) {
                        if (result[name] === undefined) {
                            result[name] = [];
                        }
                        result[name].push([results[i].value, 1000 * results[i].timestamp]);
                    }
                }
                var keys = _.keys(result);
		        var return_data = [];
                for (var i = 0; i < keys.length; i++) {
		            if(result[keys[i]].length > 0){
                       var data = {
                           target: keys[i],
                           datapoints: result[keys[i]]
                       };
                       return_data.push(data);
		            }
                }
                resolve(return_data);
            });
        });
    });

    Promise.all(results).then(val => {
    var rtr = []
    for(var i=0; i<val.length; ++i){
        for(var j=0; j<val[i].length; ++j) {
            console.log(val[i][j]);
            if(val[i][j]){
                rtr.push(val[i][j]);
            }
        }
	}
	console.log(rtr);
        setCORSHeaders(res);
        res.json(rtr);
        res.end();
    }, reason => {
        console.log(reason)
        res.status(500).send(reason);
    });
});

function last(table, req, mysql_query_result, res) {
    var names = _.map(req.body.targets, function(t) {
        return t.target;
    });

    var mode = "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ";
    var sql = mode + 'SELECT `timestamp`, `name`, `value` FROM `' + table + '` WHERE (`name` IN ("' + names.join('", "') + '"))';
    var group_by = '';
    var order_by = ' ORDER BY `timestamp` DESC';
    var maxDataPoints = req.body.maxDataPoints;
    var limit = ' LIMIT ' + maxDataPoints + ';';
    sql += group_by + order_by + limit;

    connection.query(sql, function(err, rows, fields) {
        if (err) {
            console.log(err);
            throw err;
        }

        var result = [];
        var results = rows[1];
        for (var i = 0; i < results.length; i++) {
            var name = results[i].name;
            if (name !== undefined) {
                if (result[name] === undefined) {
                    result[name] = [];
                }
                result[name].push([results[i].value, 1000 * results[i].timestamp]);
            }
        }
        var keys = _.keys(result);
        for (var i = 0; i < keys.length; i++) {
            var data = {
                target: keys[i],
                datapoints: result[keys[i]]
            };
            mysql_query_result.push(data);
        }
        if (res !== undefined) {
            setCORSHeaders(res);
            res.json(mysql_query_result);
            res.end();
        }
    });
}

app.all('/last', function(req, res) {
    var mysql_query_result = [];

    last('gauges_statistics', req, mysql_query_result, res);
});

app.listen(8000);

console.log("Server is listening to port 8000");