Unable to connect on Sails JS

[rishab-yumist]

Controller:
test : function( req, res ){

    var settings = module.exports = {
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'leaderboard',
        port : 3307
    };

    var liveConnection = new sails.LiveSelect(settings);

    return res.send(liveConnection);

}

bootstrap.js

sails.LiveSelect = require('mysql-live-select');

[numtel]

See my response further down for a better way to integrate with Sails.js

I've never used sails before you sent me that email and this is not yet the optimal way to do it but it seems to work.

First, the sails-mysql adapter must be modified to allow returning the query responsible for finding the data. I have accomplished this by hacking the find method on the model to respond to a returnQueryOnly option.

node_modules/sails-mysql/lib/adapter.js at about line 800:

    find: function(connectionName, collectionName, options, cb, connection) {

      if(_.isUndefined(connection)) {
        return spawnConnection(connectionName, __FIND__, cb);
      } else {
        __FIND__(connection, cb);
      }

      function __FIND__(connection, cb) {
        // Begin modification #1
        var returnQueryOnly = false;
        if(options.where && options.where.returnQueryOnly) {
          returnQueryOnly = true;
          delete options.where.returnQueryOnly;
          if(Object.keys(options.where).length === 0) {
            delete options.where;
          }
        }
        // End modification #1

        // Check if this is an aggregate query and that there is something to return
        if(options.groupBy || options.sum || options.average || options.min || options.max) {
          if(!options.sum && !options.average && !options.min && !options.max) {
            return cb(Errors.InvalidGroupBy);
          }
        }

        var connectionObject = connections[connectionName];
        var collection = connectionObject.collections[collectionName];

        // Build find query
        var schema = connectionObject.schema;
        var _query;

        var sequel = new Sequel(schema, sqlOptions);

        // Build a query for the specific query strategy
        try {
          _query = sequel.find(collectionName, options);
        } catch(e) {
          return cb(e);
        }

        // Run query
        log('MYSQL.find: ', _query.query[0]);

        // Begin modification #2
        if(returnQueryOnly) {
          cb(null, {query: _query.query[0], collectionName: collectionName});
        } else {
          connection.query(_query.query[0], function(err, result) {
            if(err) return cb(err);
            cb(null, result);
          });
        }
        // End modification #2

      }
    },

Then I have a controller for a model called chat that I modified from this tutorial on using sockets with sails

var LiveMysql = require('mysql-live-select');
var mysqlConnSettings = {
  host        : 'localhost',
  user        : 'todouser',
  password    : 'todopass',
  database    : 'sails_todo',
  serverId    : 1337,
  minInterval : 200
};
var liveDb = new LiveMysql(mysqlConnSettings);


module.exports = {

  addConv:function (req,res) {

    var data_from_client = req.params.all();

    if(req.isSocket && req.method === 'POST'){

      // This is the message from connected client
      // So add new conversation
      Chat.create(data_from_client)
        .exec(function(error,data_from_client){
          // New chat message completed
        }); 
    }
  },
  testStream: function(req, res){

    if (req.isSocket){
      console.log( 'User subscribed on ' + req.socket.id );


        // Start the stream.  Pipe it to sockets.
        Chat.find({returnQueryOnly:true}).exec(function(error, result) {
          liveDb.select(result[0].query, [ { table: 'chat' } ])
            .on('update', function(diff, data) {
              sails.sockets.emit(req.socket.id, 'chatDiff', diff);
            });
        });

    }


  }
};

The angular controller on the frontend now listens for the chatDiff event:

    var socketApp = angular.module('socketApp',[]);

    socketApp.controller('ChatController',['$http','$log','$scope',function($http,$log,$scope){


      $scope.predicate = '-id';
      $scope.reverse = false;
      $scope.chatList =[];

      io.socket.get('/chat/testStream');

      $scope.chatUser = "nikkyBot"
      $scope.chatMessage="";

      io.socket.on('chatDiff', function(diff) {
        console.log("got diff", diff);
        $scope.chatList = window.applyDiff($scope.chatList, diff);
        $scope.$digest();
      });

      $scope.sendMsg = function(){
        $log.info($scope.chatMessage);
        io.socket.post('/chat/addconv/',{user:$scope.chatUser,message: $scope.chatMessage});
        $scope.chatMessage = "";
      };
    }]);

In order to apply the diff, you need to copy this function from this npm module to be used on the client. Underscore or lodash is required for this function as well.

window.applyDiff = function(data, diff) {
  data = _.clone(data, true).map(function(row, index) {
    row._index = index + 1;
    return row;
  });

  var newResults = data.slice();

  diff.removed !== null && diff.removed.forEach(
    function(removed) { newResults[removed._index - 1] = undefined; });

  // Deallocate first to ensure no overwrites
  diff.moved !== null && diff.moved.forEach(
    function(moved) { newResults[moved.old_index - 1] = undefined; });

  diff.copied !== null && diff.copied.forEach(function(copied) {
    var copyRow = _.clone(data[copied.orig_index - 1]);
    copyRow._index = copied.new_index;
    newResults[copied.new_index - 1] = copyRow;
  });

  diff.moved !== null && diff.moved.forEach(function(moved) {
    var movingRow = data[moved.old_index - 1];
    movingRow._index = moved.new_index;
    newResults[moved.new_index - 1] = movingRow;
  });

  diff.added !== null && diff.added.forEach(
    function(added) { newResults[added._index - 1] = added; });

  var result = newResults.filter(function(row) { return row !== undefined; });

  return result.map(function(row) {
    row = _.clone(row);
    delete row._index;
    return row;
  });
}

It will definitely leak memory because there's no way for the server to know when to stop listening for changes to a query. Hopefully this helps though.

[rishab-yumist]

Hey ben.

Thanks for a quick response. What do you suggest in order to overcome the
memory leak issue? Listening to DB needs to be done in order to retrieve
the changes in the DB. Thus, I'm using MySql Live package made by you.

On Fri, Oct 16, 2015 at 9:48 PM, Ben Green notifications@github.com wrote:

I've never used sails before you sent me that email and this is not yet
the optimal way to do it but it seems to work.

First, the sails-mysql adapter must be modified to allow returning the
query responsible for finding the data. I have accomplished this by hacking
the find method on the model to respond to a returnQueryOnly option.

node_modules/sails-mysql/lib/adapter.js at about line 800:

find: function(connectionName, collectionName, options, cb, connection) {

  if(_.isUndefined(connection)) {
    return spawnConnection(connectionName, __FIND__, cb);
  } else {
    __FIND__(connection, cb);
  }

  function __FIND__(connection, cb) {
    // Begin modification #1
    var returnQueryOnly = false;
    if(options.where && options.where.returnQueryOnly) {
      returnQueryOnly = true;
      delete options.where.returnQueryOnly;
      if(Object.keys(options.where).length === 0) {
        delete options.where;
      }
    }
    // End modification #1

    // Check if this is an aggregate query and that there is something to return
    if(options.groupBy || options.sum || options.average || options.min || options.max) {
      if(!options.sum && !options.average && !options.min && !options.max) {
        return cb(Errors.InvalidGroupBy);
      }
    }

    var connectionObject = connections[connectionName];
    var collection = connectionObject.collections[collectionName];

    // Build find query
    var schema = connectionObject.schema;
    var _query;

    var sequel = new Sequel(schema, sqlOptions);

    // Build a query for the specific query strategy
    try {
      _query = sequel.find(collectionName, options);
    } catch(e) {
      return cb(e);
    }

    // Run query
    log('MYSQL.find: ', _query.query[0]);

    // Begin modification #2
    if(returnQueryOnly) {
      cb(null, {query: _query.query[0], collectionName: collectionName});
    } else {
      connection.query(_query.query[0], function(err, result) {
        if(err) return cb(err);
        cb(null, result);
      });
    }
    // End modification #2

  }
},

Then I have a controller for a model called chat that I modified from this
tutorial on using sockets with sails
http://maangalabs.com/blog/2014/12/04/socket-in-sails/

var LiveMysql = require('mysql-live-select');var mysqlConnSettings = {
host : 'localhost',
user : 'todouser',
password : 'todopass',
database : 'sails_todo',
serverId : 1337,
minInterval : 200
};var liveDb = new LiveMysql(mysqlConnSettings);

module.exports = {

addConv:function (req,res) {

var data_from_client = req.params.all();

if(req.isSocket && req.method === 'POST'){

  // This is the message from connected client
  // So add new conversation
  Chat.create(data_from_client)
    .exec(function(error,data_from_client){
      // New chat message completed
    });
}

},
testStream: function(req, res){

if (req.isSocket){
  console.log( 'User subscribed on ' + req.socket.id );


    // Start the stream.  Pipe it to sockets.
    Chat.find({returnQueryOnly:true}).exec(function(error, result) {
      liveDb.select(result[0].query, [ { table: 'chat' } ])
        .on('update', function(diff, data) {
          sails.sockets.emit(req.socket.id, 'chatDiff', diff);
        });
    });

}

}
};

The angular controller on the frontend now listens for the chatDiff event:

var socketApp = angular.module('socketApp',[]);

socketApp.controller('ChatController',['$http','$log','$scope',function($http,$log,$scope){


  $scope.predicate = '-id';
  $scope.reverse = false;
  $scope.chatList =[];

  io.socket.get('/chat/testStream');

  $scope.chatUser = "nikkyBot"
  $scope.chatMessage="";

  io.socket.on('chatDiff', function(diff) {
    console.log("got diff", diff);
    $scope.chatList = window.applyDiff($scope.chatList, diff);
    $scope.$digest();
  });

  $scope.sendMsg = function(){
    $log.info($scope.chatMessage);
    io.socket.post('/chat/addconv/',{user:$scope.chatUser,message: $scope.chatMessage});
    $scope.chatMessage = "";
  };
}]);

In order to apply the diff, you need to copy this function from this npm
module to be used on the client. Underscore or lodash is required for this
function as well.

window.applyDiff = function(data, diff) {
data = _.clone(data, true).map(function(row, index) {
row._index = index + 1;
return row;
});

var newResults = data.slice();

diff.removed !== null && diff.removed.forEach(
function(removed) { newResults[removed._index - 1] = undefined; });

// Deallocate first to ensure no overwrites
diff.moved !== null && diff.moved.forEach(
function(moved) { newResults[moved.old_index - 1] = undefined; });

diff.copied !== null && diff.copied.forEach(function(copied) {
var copyRow = _.clone(data[copied.orig_index - 1]);
copyRow._index = copied.new_index;
newResults[copied.new_index - 1] = copyRow;
});

diff.moved !== null && diff.moved.forEach(function(moved) {
var movingRow = data[moved.old_index - 1];
movingRow._index = moved.new_index;
newResults[moved.new_index - 1] = movingRow;
});

diff.added !== null && diff.added.forEach(
function(added) { newResults[added._index - 1] = added; });

var result = newResults.filter(function(row) { return row !== undefined; });

return result.map(function(row) {
row = _.clone(row);
delete row._index;
return row;
});
}

It will definitely leak memory because there's no way for the server to
know when to stop listening for changes to a query. Hopefully this helps
though.

—
Reply to this email directly or view it on GitHub
#20 (comment)
.

[numtel]

I'm working on cleaning this up, give me a little bit to make either a pull request to make it a feature of sails-mysql or a separate package then it should be a simple function call with a method available for closing the liveSelect.

[rishab-yumist]

Ben,

It'll be amazing if you could do this :)

Thanks in advance. Appreciate your support.

I'm keenly waiting for this.

On Sat, Oct 17, 2015 at 12:19 AM, Ben Green notifications@github.com
wrote:

I'm working on cleaning this up, give me a little bit to make either a
pull request to make it a feature of sails-mysql or a separate package
then it should be a simple function call with a method available for
closing the liveSelect.

—
Reply to this email directly or view it on GitHub
#20 (comment)
.

[numtel]

Ok, check out the new NPM package and the associated example application. Should be pretty simple to integrate now.

https://github.com/numtel/sails-mysql-live-select