I was going about it all wrong

MongoDB Aggregation Framework, which, if it didn't work right, I would call the Aggrevation Framework. Well it works wonderfully. So that name shall be put in my back pocket for a special kind of software.

For this blog, there is a list on the right which has the years months and posts for that month. It's pretty easy to represent in SQL, like so:

select Month(Post.Date), Year(Post.Date), count(*) from Post group by Month(Post.Date), Year(Post.Date)

Well, in my current version of this site, which is written in Node.JS using MongoDB, I wrote something which after looking at it, and the new solution, is not something I want to post here. :)

I think it was written before I knew about the Aggregation Framework. It was like this:

Loop from 2005 to now creating Date objects for each month along the way, loop over these Date objects, grab post counts for each Date object, update object with count.

Anyway, it was correct but could be done way better. Like instead of multiple calls to the database, how about just 1? Seems like an improvement.

Here it is in straight up MongoDB shell.

db.posts.aggregate (
    [ 
        { $match: {} }, 
        { $project: {    
            "postMonth": { "$month": "$date" }, 
            "postYear": { "$year": "$date" }, _id: 0 } 
            },  
        { $group: { 
            _id: { "postMonth": "$postMonth", "postYear":"$postYear" }, 
            "count": { "$sum": 1 } }    
        } 
    ]
)

Here it is in mgo (apparently pronounced mango)

pipe := posts.Pipe([]bson.M{
    { "$match": bson.M{} },
    { "$project": bson.M{ "postMonth": bson.M{ "$month": "$date" }, "postYear": bson.M{ "$year": "$date" } } },
    { "$group": bson.M{ "_id": bson.M{ "postMonth": "$postMonth", "postYear": "$postYear" }, "count": bson.M{ "$sum": 1 } } },
    { "$sort": bson.M{ "_id.postYear": -1, "_id.postMonth": -1 } },
})

Here's the whole GetPostDateCounts method:

func (postRepo PostRepository) GetPostDateCounts() []YearCount {
    posts := postRepo.OpenCollection(postRepo.collection)
    pipe := posts.Pipe([]bson.M{
        { "$match": bson.M{} },
        { "$project": bson.M{ "postMonth": bson.M{ "$month": "$date" }, "postYear": bson.M{ "$year": "$date" } } },
        { "$group": bson.M{ "_id": bson.M{ "postMonth": "$postMonth", "postYear": "$postYear" }, "count": bson.M{ "$sum": 1 } } },
        { "$sort": bson.M{ "_id.postYear": -1, "_id.postMonth": -1 } },
    })

    itr := pipe.Iter()
    p := YearMonthCount{}
    mapped := make(map[int][]MonthCount, 30) //this will work for 30 years. this code should be re-architected to accommodate advances in medicine

    for itr.Next(&p) {
        mapped[p.Date.Year] = append(mapped[p.Date.Year], MonthCount{ Month: p.Date.Month, Count: p.Count, MonthNum: int(p.Date.Month) })
    }

    yc := []YearCount{}
    for year, months := range mapped {
        yc = append(yc, YearCount{ Year: year, Months: months })
    }

    sorter := &YearCountSorter{ Entries: yc }
    sort.Sort(sort.Reverse(sorter))
    return sorter.Entries
}

And some structs used within that code

type YearMonth struct {
    Year int     `bson:"postYear"`
    Month time.Month     `bson:"postMonth"`
}

type YearMonthCount struct {
    Count int `bson:"count"`
    Date YearMonth     `bson:"_id"`
}

type MonthCount struct {
    Count int
    Month time.Month
    MonthNum int
}

type YearCount struct {
    Year int
    Months []MonthCount
}

More Go MongoDB Testing Code

This is generally moving towards how I would structure a final product that was written with MongoDB as the backend.  It's big, so I'm going to play with new ways of putting code on my site. Here it is on github

Fantasy Golf Tracking with Node.js, MongoDB, AngularJS, and Bootstrap - Part 2

I'll address this in parts since the first post really didn't cover anything technical, and was just a bunch of screenshots.

MongoDB Implementation

I previously went over the basic data structure for this app in the previous post.  Teams consist of the team name. Tournaments consist of the key, the name, the start and end date, the course name, the par for the course, and the current round. Also, bools for finished and in progress. Then there is the teams players per tournament which I called teamTournament. It also keeps historical records of their total for that tournament. This lead me to be able to build a leaderboard widget, since the lowest score at the end of the season gets a prize.

Tournament Sample Data

> db.tournaments.find().pretty()
{
        "_id" : ObjectId("53a0964d4fcdf5c39c912acd"),
        "key" : "us-open-2014",
        "name" : "U.S. Open",
        "scoresLocation" : "http://www.pgatour.com/data/r/026/leaderboard-v2.json",
        "startDate" : ISODate("2014-06-12T07:00:00Z"),
        "endDate" : ISODate("2014-06-15T19:00:00Z"),
        "latestRound" : 4,
        "inProgress" : false,
        "isFinished" : true,
        "course" : "Pinehurst No. 2",
        "par" : 70
}

Granted, that inProgress and isFinished can be determined real time, but that's ok.

Team Tournament Sample Data

> db.teamTournament.find().pretty()
{
        "_id" : ObjectId("53a2e61ed42498e823000001"),
        "players" : [
                "28259",
                "28087",
                "21209",
                "08075",
                "31202",
                "28486"
        ],
        "teamId" : ObjectId("53a193fc4fcdf5c39c912af7"),
        "tournamentId" : ObjectId("53a20488d7aee2e01b000001"),
        "tournamentTotal" : 1073
}

I used to use DBRef for referencing other collections, but then I found out that unless you don't know at runtime, you should just use ObjectID.  So my ObjectID of 53a19 etc is in the field of teamId, so I know it's a team reference. If it were called "documentId" and I had collections of "images" and "html snippets" and "swf files", then I could use the DBRef, since it could be one of 3 different collections I want to reference. But since I know, MongoDB says it's much more efficient to just use ObjectID references.

Node.js to Access the Database

As for writing code to access this, I use the Node MongoDB Native library for accessing node, and a helper class that I wrote so I'm not writing lots of code to do things that I do frequently. For instance, my code for getting tournaments looks like this:

this.getTournament = function(db, key, callback){
    dbhelp.findOne(db, "tournaments", { key: key }, function(tournament){
        callback(tournament);
    });
}

this.getCurrentTournament = function(db, callback){
    var upcoming = new Date().addDays(3);
    var past = new Date();

    dbhelp.find(db, "tournaments", { "$or": 
            [ 
                { "startDate": { "$lt": upcoming } },  
                { "endDate": { "$lt": past } }
            ] 
        }, null, { startDate: 1 }, function(tournaments){
        if (tournaments != null && tournaments.length > 0){ return callback(tournaments[0]); }
        else return callback(null);
    });
}

this.getTournaments = function(db, year, callback){
    var start = new Date(year, 0, 1);
    var end = new Date(year, 11, 31);

    dbhelp.find(db, "tournaments", { startDate: { "$gt": start }, endDate: { "$lt": end } }, function(tournaments){
        callback(tournaments);
    });
}

So, you can see, not a lot of code for getting a specific tournament, getting the current tournament, and getting all tournaments this year. (Also I've modified the Date prototype to include a .addDays method).

That is some sample code that I've written for this application. It covers the back end. Next up I'll cover the front end, using AngularJS and Bootstrap to make it work well and look great!

Fantasy Golf Tracking with Node.js, MongoDB, AngularJS, and Bootstrap - Part 1

My family and a bunch of friends are in a fantasy golf league together. The rules are pretty straightforward, although probably not standard.

Rules:
1. Pay $50
2. 10 Tournaments that span the PGA Tour season.
3. Pick 6 golfers before the first tee times on Thursday typically.
4. 4 lowest scores are added and that's your score for each day.
5. If 3 of your players miss the cut, you are assigned the worst score at the end of round 3, pretty much destroying your chance to win.
6. Lowest score wins. $50 payoff for majors (Masters, US Open, British Open, PGA Championship), $25 for the other tournaments.

My brother Pat is the score keeper and chairman of the league. The data collection and reporting was pretty much done in Excel.  This is a fine method for doing such things. The scores would be emailed out along with entertaining commentary.

But then it was my turn to do the data since Pat was going to Boston for the US Open weekend to visit some friends.

Excel was not a viable solution.

I happened to stumble on the PGA Tour Leaderboard website. I noticed that the data is loading in asynchronously, which could only mean AJAX. The question was, which format, and what data is available?

The answer was the start of this application. The data is JSON. And EVERYTHING is available. (I hope I don't get in too much trouble for grabbing it :). Well, everything I needed and some extra stuff.

The first step to building this app was to determine what the Information Architecture was going to look like.  Here's what I came up with:

Teams: Name, Email address of a person in the league.
Tournaments:  Tournament info include URL to the JSON file on pgatour.com, data like start / end date, is it in progress, is it finished.
Team Tournament: Each team's 6 initial golfers for each tournament, and total score recording.

Pulling in the tournament from the pgatour.com JSON file pulls in all of the information required for a tournament in my system, so all that is needed as input is the JSON file URL!

Next you assign teams. There can be 6 max.

Then scores are calculated.

And updated throughout the round, each day, for the whole tournament.

If a player doesn't have 4 players make the cut, they are given a substitute. The worst score from the 3rd round.

That is pretty much everything!  Once again, working with AngularJS is a breeze.

Check out the site at fantasygolf.jasontconnell.com! There you can view the source to get an idea of how it was written. It was very simple that I don't even feel like I can post any difficult code that I wrote, but it's just that it's really cool. Our next tournament is the Congressional next week, I hope everyone in the league uses the site.  I added GA tracking so I'll know for sure :)

Tag List Added

I recently went about aggregating the tags used on my posts to create a sort of tag cloud. I never liked the display of tag clouds, so I just list them out in order of occurrence, with the most frequent showing first.

This should help me get some traffic. Node.js and MongoDB are super fast. It doesn't even stutter when loading the site, across 500+ posts. Actually, I have no idea how many there are.  Super fast.
Here's the code which pretty much finishes in -5 seconds
var db = require("../db"); this.tagCloud = []; this.initialize = function(site, callback){ var self = this; while (self.tagCloud.pop()); db.getPosts(site.db, {}, -1, -1, function(posts){ var tags = {}; posts.forEach(function(post){ if (post == null) return; for (var i = 0; i < post.tags.length; i++){ if (tags[post.tags[i]] == null) tags[post.tags[i]] = { tag: post.tags[i], count: 0 }; tags[post.tags[i]].count++; } }); for(var tag in tags){ if (tags[tag].count > 8) // arbitrary limit so we don't list like 200 tags with 1 post each self.tagCloud.push(tags[tag]); } self.tagCloud.sort(function(a,b){ return b.count - a.count; }); callback(); }); }

This site now running on MongoDB 2.4.3

It does not take advantage of it in any way though... But I wanted to get it updated because a new site I'm writing takes advantage of some new features, like aggregate. Fun stuff!

MongoDB has an aggregate method

And I'm writing a disc golf score keeping and statistics tracking application.  I wanted to see what course a particular user played the most.

db.scores.aggregate([{$match: { user: userId } }, {$group: { _id: { course: "$course", variant: "$variant", tees: "$tees" }, played: { "$sum": 1 } } }]);

It shows that in the score keeping data, I've played the Blue Tees at the Front Nine at Sedgley Woods 6 times, and the Back Nine 5 times, since last year.

[
{"_id":{"course":"50acfe3b91b5581439a05ed2","variant":"back-9","tees":"blue"},"played":5},
{"_id":{"course":"50acfe3b91b5581439a05ed2","variant":"front-9","tees":"blue"},"played":6}
]

I will be able to use this to get a whole bunch of different statistics :)  I will make an announcement when that goes live, I want to put it on the web, it'll be fun.  It's written on my latest of the software I'm writing in Node.js.

JsViews and JsRender Use Case...

I came across JsRender a few weeks ago, looking for an updated javascript template rendering system. I was in need for a side-project at work (yes, we have side projects sometimes, although honestly, I did most of it at home).  The basic gist of this side project is this: we have many clients, with many servers that we have to maintain, and each of those has different passwords. This would replace an Excel spreadsheet that sits on a shared drive, that when a password changes, we have to go in and update it, and it might be locked because someone else is viewing it.

The technological philosophy behind the creation of this application is this:  Users can log in, add clients, environments, credentials at will. The updates are seen almost immediately to other users logged in, through ajax polling. And an extensive history is kept so we can keep track of who updated what, and what it was before. Passwords are encrypted and viewable upon request.

Some definitions... environments are like, production, staging, dev. Credentials can be username / password for remote desktop access, database, the CMS admin, FTP, etc.

Also listed for each client is their subversion URL if they have one, the version of the development environment to use, which database and version, and what CMS and version they are using, if any.\

Here is a picture of it:


So here, the client is Delphic Sage (for our website, etc), none of these pieces of data are real.  There are four environments.

Data structure is as follows:

Client: Active, Name, Host (Rackspace, Self hosted, etc), Software list, comments, Environment list, History list
Software: version, DB reference to software (type (cms, devenv, database), name, versions list)
Environment: type (free text, usually production, staging, dev), url, credentials list
Credential: username, password, server or url (could be localhost, server name, url to admin login etc), comment

The whole thing is really cool. Maybe I'll put up a sample of it somewhere, but our app will be run internally of course.  I'll post another screenshot when the styles have been updated! The data structure I created in MongoDB is pretty much literally what you see on screen, and it taught me a lot about MongoDB. I also wrote a lot of helper methods to shorten the code a lot when dealing with MongoDB. The back end is my Node.js web server (what is running this website).  I can't think of anything else to add at the moment, so that means it might go into production very soon.

I'll make follow up posts with some code.

Website is Back Up

As you can see.  Very simple, no images, but the bells and whistles are there.  I wrote it up over a weekend, probably 6-7 hours total, converting the original mysql database over to mongodb, and writing some quick code in node.js.

There's only one table, posts, so it was easy.  There is a mechanism for me to edit the posts, and add new ones, but it's very crude!

Take it easy. Peace.

Borrow the good, discard the bad

In my many years of web development, I've come across a lot of good ways that platform authors did stuff, and a lot of bad ways. So I'm writing my version of a web platform on Node.js, and I decided to keep the good stuff, and get rid of what I didn't like. It wasn't easy but I'm pretty much finished by now.

As with most things I develop, I'll decide on an architecture that allows for changes to be made in a way that makes sense, but I'll start with what I want the code to look like. Yes. When I wrote my ORM, I started with the simple line, db.save(obj); (it turns out that's how you do it in MongoDB so I didn't have to write an ORM with Mongo :) When starting a web platform, I started out the same way.

I wanted to write:

<list value="${page.someListVariable}" var="item">
Details for ${item.name}
<include value="/template/item-template.html" item="item" />
</list>


Obvious features here are code and presentation separation, SSIs, simple variable replacement with ${} syntax.

There aren't a lot of tags in my platform. There's an if, which you can use to decide whether to output something. There's an include, which you can pass variables from the main page so you can reuse it on many pages. This one takes an "item" object, which it will refer to in its own code with ${item}.

Recently I added a layout concept. So you can have your layout html in another file, and just put things into the page in the page's actual html. For instance, you might reach the file index.html, which would look like this:

<layout name="main">
<content name="left-column">
<include value="/template/navigation.html" />
</content>
<content name="main-column">
<include value="/template/home-content.html" />
</content>
</layout>


Java Server Faces used a two way data binding mechanism which was really helpful. But then you need controls, like input[type=text] or whatever. My pages will not have two way data binding, but you can use plain html. Which I like better. (However, those controls were very simple to swap due to the generous use of interfaces by Java, and their documentation pretty much mandating their use. e.g. using ValueHolder in Java instead of TextBox, and if you were to make it a "select" or input[type=hidden], your Java code would not have to change, which is one thing I absolutely hate about ASP.NET).

I borrow nothing from PHP.

ASP.NET pretty much does nothing that I like, other than it's easy to keep track of what code gets run when you go to /default.aspx. The code in /default.aspx.cs and whatever Page class that inherits, or master page that it's on. In Java Server Faces you're scrounging through xml files to see which session bean got named "mybean".

My platform is similar to ASP.NET in that for /index.html there's a /site/pages/index.js (have I mentioned that it's built on node.js), that can optionally exist, and can have 1-2 functions implemented in it, which are "load" and "handlePost", if your page is so inclined to handle posts. Another option is to have this file exist, implement neither load nor handlePost, and just have properties in it. It's up to youme.

Here's a sample sitemap page for generating a Google Sitemap xml file:

Html:

<!--?xml version="1.0" encoding="UTF-8"?-->

<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
<loc>http://${config.hostUrl}/index</loc>
<lastmod>2011-06-16</lastmod>
<changefreq>monthly</changefreq>
<priority>0.5</priority>
</url>
<jsn:foreach value="${page.entries}" var="entry">
<url>
<loc>${entry.loc}</loc>
<lastmod>${entry.lastmod}</lastmod>
<changefreq>${entry.changefreq}</changefreq>
<priority>${entry.priority}</priority>
</url>
</jsn:foreach>
</urlset>


I use the jsn prefix, which just stands for (now, anyway) Javascript Node. I wasn't creative. I guess I can call it "Jason's Site N..." I can't think of an N.

And the javascript:

var date = require("dates"), common = require("../common");

this.entries = [];

this.load = function(site, query, finishedCallback){
var self = this;
var now = new Date(Date.now());
var yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate());
var yesterdayFormat = date.formatDate("YYYY-MM-dd", yesterday);
common.populateCities(site.db, function(states){
for (var i = 0; i < states.length; i++){
states[i].cities.forEach(function(city){
var entry = {
loc: "http://" + site.hostUrl + "/metro/" + city.state.toLowerCase() + "/" + city.key,
lastmod: yesterdayFormat,
changefreq: "daily",
priority: "1"
}
self.entries.push(entry);
});
}
finishedCallback({contentType: "text/xml"});
});
}


My finishedCallback function can take more parameters, say for handling a JSON request, I could add {contentType: "text/plain", content: JSON.stringify(obj)}.

That's about all there is to it! It's pretty easy to work with so far :) My site will launch soon!