Thursday, October 17, 2013

Angular ngGrid watch for filtered row changes

I have a grid and a chart.  When the user changes the grid either via a filter or a sort, I want the chart to automatically update.  Aside from writing a "converter" to convert the model data from the grid format to the chart format, I needed to get my "watch" right.  Get it wrong and you will get this error:

Error: 10 $digest() iterations reached. Aborting!

I don't like that error.  That means something is happening recursively, like your watch is triggering a function that changes the model which you are watching and the watch fires again!  Recursive nightmare!

Skip ahead to the EDIT section.

OK, aside from that, what do you have to watch on an ngGrid to see if it "changed".  Well, the $watch method allows you to pass a string name of the model object to watch, or you can pass in a function!  Watching a huge object is time consuming!  It's better to watch a number or a string!  (Angular will be comparing the "old" and "new" values quite a lot.  If the comparison is easy, your app will be faster).

So I implemented this code to watch the ngGrid filteredRows object and a few others to see if the filtered data in the grid has changed.  This works great.  The one thing I don't like about it is that I am dependent on these variables in the ngGrid.  What if in the future the variable names changed?  I would much prefer a built in method to see if something has changed, but maybe in the future.  Here's the code.  Leave a comment if you think there is a better way!

$scope.gridChangedIndicator = function(scope) {
var watchString = "";
try {
watchString += scope.gridOptions.ngGrid.filteredRows.length;
watchString += scope.gridOptions.ngGrid.lastSortedColumns[0].field;
watchString += scope.gridOptions.ngGrid.lastSortedColumns[0].sortDirection;
} catch (err) {}
return watchString;

// When the grid data changes, the chart needs to change too.



This code was killing me.  This watch was sending angular into infinite-watch-nightmare-evil-glare.  I found out that using watch in a controller can be dangerous.  So I changed the above code to this:

$scope.$on('ngGridEventRows', $scope.refreshChart);

This event gets fired a little more often than I want, but my chart refresh is really fast.  I'd rather have it fire a few extra times than a million extra times.  This documentation about ngGrid Events is really helpful.  As of this moment, I think the events are a little lacking, and some do not seem to fire at the correct time for me.  (For example, the ngGridEventFilter event fires before the filter is really done.  ngGridEventSorted fires after which seems more useful.


OK, I'm still getting CPU burning infinite loops.  It all seems to be within the angular digest looping.  I wish I knew why this was, but I've burned up enough hours trying to do this.  I simply added a button to update the chart.  That only took 2 minutes because Angular is awesome!  PLEASE If anyone else is doing this effectively out there in the great internet, leave a comment to tell me how you did it.

This is all in flux right now.  The issue is still happening even decoupling the chart from the grid.  In a way that is good news, but now I need to figure out what is killing the page.

Enough edits already?  I was being hit by a double whammy and it exhibits itself in the same code ($digest).  Indeed, when I was adding a $watch more than once in my controller, it was causing really bad performance in $digest.  But also, Angular grid has an issue clearing the search filter.  It goes nuts in the $digest.  It is issue 777 on github.

Tomorrow for EDIT 5, I'll put the grid and chart back together and simply disable filtering on my ngGrid to see if that will solve it.  Good night!


ngGrid has a bad issue when the search filter is cleared out.  See issue 777 on github.  That was causing issues in $digest.  But I also had a call to $scope.$watch that was happening more than once in my controller, which is also bad and causes a very similar issue.  It turns out the $on code above is called quite often... more than necessary.  So I reverted back to the $watch code instead.

Wednesday, October 16, 2013

sqlite3 performance

I've been tweaking sqlite3 performance today.

Some things I've learned:

  1. Sequelize is awesome, but it does not use bind variables.  Adding bind parameters to my SQL increased the speed a lot.  I'll use Sequelize for most of my stuff, but when I query a 1 million row table, I will just use node-sqlite3 because I can use bind parameters.
  2. Indexes are huge for sqlite.  However, sqlite will only use one index per table in the SELECT query.  I have a view with maybe 4 tables, so luckily I can use one index per table.  Sqlite does not always choose the best index tho.
  3. Using the sqlite3 command line with .timer on, .explain on, and explain query plan before your SQL statement are huge!  You want to be doing a search on an index, NOT a scan.

BTW, here's an example of the bind parameters.

var sql = "SELECT blah ";
sql += "FROM `mytable` ";
sql += "WHERE finishAt > ? and projectId=? and CustomerId = ? ORDER BY finishAt desc LIMIT 10000";

db = new sqlite3.Database('../mydb.sqlite', function() {
db.all(sql, ['2013-09-16',1,1], function(err, rows) {
if (err)
console.log("Ran sql " + sql);
console.timeEnd("timestamp ");
// Force a single returned object into an array.
rows = [].concat(rows);
console.log("Got " + rows length + " records.");
if (rows.length > 0) {
console.log("Got the rows 0] " + JSON.stringify(rows[0]));
console.log("Got the rows ...");



Saturday, October 12, 2013

sqlite in eclipse

I was able to download a JDBC driver for sqlite.

I put the jar file in my /opt/eclipse/plugins dir.

Then I followed the instructions to set up the driver and the database connection.

I found that I could not create the connection by right clicking "Database Connections".  It did not allow me to put in a connection NAME!  So I had to use the icon to create the connection.

Anyway, the default database name is "main" in sqlite.  In the Data Source Explorer, you literally see nothing when you drill into ANYTHING, but that's ok!  You can still run SQL which is the most important part!  And if you need to know the table names run:

SELECT name, type FROM sqlite_master WHERE type in ('table', 'view')

pragma table_info(customers);

Happy days!

Friday, October 11, 2013

Sequelize sqlite query taking a long time.

I had a pretty simple select statement.  It was taking like a few ms when submitting in the sqlite3 command line utility.  Using Sequelize, it was taking 2.5 seconds.  I found that you can choose to use "raw" data, meaning you get Objects back that you can transform into JSON instead of getting "Models" back.  It can take some time to convert all of the records returned from the database into Models.

Here's the documentation on raw: - search for "Raw queries"

Since I started using raw for certain things, my performance has improved a lot!  Sweet!

Thursday, October 3, 2013

Node.js POST service to a database

I just created a web service that I can use to POST data and insert the data into a database.

Here's the code from my server.js, which is an express app:

var history = require('./routes/history');
...'/history', history.create);

I have a route called history.js which services this route:

exports.create = function(req, res) {

console.log("Create the history");
// Sequelize does the db insert here.;

This is a very crude example with no real error handling, but you get the point.  The variable "req.body" has the "post data".  Now I can submit data via curl like this:

$ ./
+ curl -X POST -H 'Content-Type: application/json' --data @hist.json

I have a separate file in the same directory called hist.json which has a json object of the same model as my Sequelize table.

In the node application log you will see:

Create the history
[90mPOST /history [32m200 [90m17ms - 2 [0m
Executing: INSERT INTO `BaseHistories` (`blah1`,`blah1`) VALUES ('a','b');

This blog post assumes you have node, express and sequelize set up.  Happy databasing!

Wednesday, October 2, 2013

ShellEd vs ColorEditor Eclipse Plugins

I really like the ShellEd eclipse plugin, but it has killed my productivity.  I just opened a bash file and went to get coffee.  When I came back, it was still not open in the the editor (hourglass).  Bummer.  So I uninstalled the plugin and reinstalled.  Same issue.

I just installed a different editor for eclipse,  The ColorEditor install was really easy and now I have syntax highlighting for 100+ file formats.  Sweet.  I don't do a lot of shell script editing, so just having syntax highlighting is fine with me.