numtel
Unable to connect on Sails JS
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');
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.
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 [email protected] 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 https://github.com/numtel/mysql-live-select/issues/20#issuecomment-148758735 .
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.
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 [email protected] 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 https://github.com/numtel/mysql-live-select/issues/20#issuecomment-148805092 .
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