Using Nodejs & Expressjs to Upload and save Image Files into MongoDB (Part 2/3)

1101-sdt-mongoThis is a follow-on to Part 1, an introductory post

This example will use the Expressjs templating engine. Expressjs can be a deep and mind boggling domain but we’ll use only two of its key features, routes and views.

This example will NOT use mongdb GridFS. In the interests of keeping this as simple as possible, we’ll limit our image file size to < 2MB and avoid helper libraries that obfuscate what’s going on.

So assuming you have nodejs working you can stub out a templated app. I’m going to use Express Application Generator to get a directory structure and some file templates going. I’ll also install nodemon, a useful utility which restarts the nodejs service each time you save a file change.

sudo npm install express-generator nodemon -g

I’m choosing to use the ejs templating engine instead of the default Jade engine. If you’re unfamiliar with Jade, it looks weird. A bit like going straight to Coffeescript and bypassing Javascript.

express --ejs --git --testdir

Install two NPM packages

install mongodb multer --save

You may want to run this to make sure packages and dependencies are up to date.

npm install

Now start your node service from within your testdir and you should be able to access it from your browser with http://localhost:3000 (or whatever port it starts on)

nodemon bin/www

Only two files are really needed for this example,

  1. routes/index.js
  2. views/index.ejs

Open each in your code editor and we’ll start with views/index.ejs. This is the minimum HTML necessary to get a ‘choose file’ button and file selector going. It also includes a text field for some user entered meta data.

<form action="/uploadpicture" method="POST" enctype="multipart/form-data">
<input type="file" name="picture" accept="application/x-zip-compressed,image/*">

<input class="form-control" type="text" name="description" placeholder="Description or Message">

<input class="btn btn-primary" type="submit" value="submit">
</form>

Save this file and goto the routes/index.js

We’re going to create two routes. You’ll want it to look something like this,

var express = require('express')
   , router = express.Router()
   , MongoClient = require('mongodb').MongoClient
   , ObjectId = require('mongodb').ObjectId
   , fs = require('fs-extra')
   // Your mLabs connection string
   , url = 'mongodb://username:password@yourinstanced.mlab.com:29459/yourdb'
   , multer = require('multer')
   , util = require('util')
   , upload = multer({limits: {fileSize: 2000000 },dest:'/uploads/'})

// Default route http://localhost:3000/
router.get('/', function(req, res){ res.render('index'); });

// Form POST action handler
router.post('/uploadpicture', upload.single('picture'), function (req, res){

if (req.file == null) {
  // If Submit was accidentally clicked with no file selected...
  res.render('index', { title:'Please select a picture file to submit!'); });
} else {

MongoClient.connect(url, function(err, db){
   // read the img file from tmp in-memory location
   var newImg = fs.readFileSync(req.file.path);
   // encode the file as a base64 string.
   var encImg = newImg.toString('base64');
   // define your new document
   var newItem = {
      description: req.body.description,
      contentType: req.file.mimetype,
      size: req.file.size,
      img: Buffer(encImg, 'base64')
   };

db.collection('yourcollectionname')
   .insert(newItem, function(err, result){
   if (err) { console.log(err); };
      var newoid = new ObjectId(result.ops[0]._id);
      fs.remove(req.file.path, function(err) {
         if (err) { console.log(err) };
         res.render('index', {title:'Thanks for the Picture!'});
         });
      });
   });
   };
}); 

This should be about all you need to get a basic uploader running…

One important note here is that we’re converting the binary image file (jpg) into a text string that is base64 encoded. This is not the nodejs default (utf-8). The reason for this is not well documented according to Google but will become apparent in the next post.

The next post will examine how to extract these image files from mongodb and serve them up over http.

Free Nodejs & MongoDB Hosting at Heroku & mLabs (Part 1/3)

Node.js has been all the rage for several years now. The momentum and traction since ~2014 seems to have caught the ‘enterprise community’ off guard. Data center penetration is growing and raising alarms with Chief Information and Computer Security Officers, mostly concerned over source code pollution at GitHub and NPM. Justified or not, there is still no credible evidence based answer to this concern.

As developers continue to pile into this stack, more are asking where to host their prototype or hobby apps. Preferably free, or at least modestly priced.

I’ve been using and recommending Heroku for a few years. Not that it’s the best, cheapest or most performant but because it’s the one I’m most familiar with.

I’ve been meaning to get to grips with Google’s Cloud hosting of Nodejs, but have yet to find the time. Also, price transparency remains a deterrent.

herokucaptureHeroku was around long before all the nodejs excitement started. Founded ~2007 I think.

As has the other critical piece of infrastructure – free MongoDB hosting at mLab.

mLabs is closely integrated into Heroku as an ‘add-on’ so separate registration is not required.

Heroku dashboard serves as the portal to manage your nodejs instance or Dyno as Heroku calls it (not to be confused with a Slug, but I am) and your mongodb collections.

Hclourflarecaptureeroku and mLabs are now wholly owned subsidiaries of Salesforce.com and both host their offerings on Amazon AWS S3.

A third piece of crucial free infrastructure is DNS hosting, robust Content Delivery Networking and SSL encryption.

This too can be had for free by hobbyists and developers via Cloudflare.

It’s been a well kept secret for a long time now.

So what we have is,

Free Nodejs & MongoDB Hosting = Heroku + mLabs + Cloudflare

All you need to get these three pieces up and running is an email address and about a half hour (probably more if you need DNS to propagate).

Fast forward and you’ll find the Heroku Documentation is pretty clear and will get your developer tool chain installed (Heroku CLI and MongoDB Shell) and your first test app deployed with Git.

In the next post we’ll see how to get a simple app running that allows users to upload an image file and store it in mongodb.

The running example is here.

Data Visualization : Jawbone UP

ScreenShot1

IMG_0432What originally drew me to the UP back in 2013, was the offer of access to my own data. I was hoping to get sensor data. The actual discrete time stamped measurements from the accelerometer and the stop watch. Instead what I got was daily aggregations. I suspect Jawbone retain the meta data from the phone app like geotags,  network details etc.

Downloading the aggregated data from the website involves finding link buried at the bottom of the Accounts section of the User Profile page.

JBScreenshot

Interpreting the column headings required some hunting around the Jawbone Support Forums. These community Forums have since disappeared form the Jawbone website. So the table below may be the only data dictionary still floating around the internet!

It to have been deciphered by an enthusiastic user rather than an official spec from Jawbone. I’d link to the forum post and credit the user but I couldn’t find them even in Google Cache.

Essentially, this is what’s available in the CSV files.

Column name

Type

Description

e_calcium

meal

calcium content in milligrams

e_calories

meal

energy content in kcal

e_carbs

meal

carbohydrates content in grams

e_cholesterol

meal

cholesterol content in milligrams

e_fiber

meal

fiber content in grams

e_protein

meal

protein content in grams

e_sat_fat

meal

saturated fat content in grams

e_sodium

meal

sodium content in milligrams

e_sugar

meal

sugar content in grams

e_unsat_fat

meal

unsaturated fat (monounsaturated + polyunsaturated) content in grams

m_active_time

movement

total number of seconds the user has been moving

m_calories

movement

total number of calories burned in the day

m_distance

movement

total distance in meters

m_lcat

movement

longest consecutive active time in seconds

m_lcit

movement

longest consecutive idle time in seconds

m_steps

movement

total number of steps in the day

m_workout_count

movement

number of workouts in the day

m_workout_time

movement

total number of seconds the user has workedout

o_count

mood

number of workouts in the day

o_mood

mood

total sum of mood ratings in the day

s_asleep_time

sleep

number of minutes, since previous midnight, when the user fell asleep (first time the user fell into light or sleep mode).

s_awake

sleep

seconds the user was awake

s_awake_time

sleep

number of minutes, since previous midnight, when the user woke up (either the band was taken out of sleep mode, or the beginning of the last awake period)

s_awakenings

sleep

number of times the user woke up

s_bedtime

sleep

number of minutes, since previous midnight, when the user set the band into sleep mode

s_deep

sleep

number of seconds the user was in deep sleep

s_duration

sleep

total number of seconds the user slept

s_light

sleep

number of seconds the user was in light sleep

s_quality

sleep

quality score (0-100)

n_asleep_time

nap

number of minutes, since previous midnight, when the user fell asleep (first time the user fell into light or sleep mode).

n_awake

nap

seconds the user was awake

n_awake_time

nap

number of minutes, since previous midnight, when the user woke up (either the band was taken out of sleep mode, or the beginning of the last awake period)

n_awakenings

nap

number of times the user woke up

n_bedtime

nap

number of minutes, since previous midnight, when the user set the band into sleep mode

n_deep

nap

number of seconds the user was in deep sleep

n_duration

nap

total number of seconds the user slept

n_light

nap

number of seconds the user was in light sleep

n_quality

nap

quality score (0-100)

I chose to explore this data visually with D3.js and Crossfilter.js. You could just have easily done the same in MS Excel or Google Sheets.

IMG_0076My experience suggests my band’s distance estimates are off by about +20% when running. Consequently, this overestimates the speed (mph) calculations performed by the app. It’s true, I could calibrate it to my own running cadence and stride. I didn’t.

Given what I know about my own Basal Metabolic Rate (BMR) and Total Energy Expenditure (TEE), I assume the calorie expenditure estimates to be equally flattering (about 15% over estimated).

I believe the band assumes approximately 2,000 steps per mile. This would be consistent with prevailing average estimates. Hence, the further you are from “average” height and weight, the higher your margin of error.

If you’re an athletic outlier (skinny distance runner or a stocky body builder) these measurements are not useful for improving performance. If you’re “average” (overweight and inactive) you’ll get more accurate measurements out of the box. Which I suppose says a lot about who this was designed for.ScreenShot2

In performing this simple visual exploration, I was unable to learn anything I didn’t already know.

  1. I’m more active on the weekends. Not a surprise given my desk job.
  2. I sleep better at the weekends. Not a surprise given I’m more active.
  3. I appear to be more active in the warmer months. Not a surprise given I live in SD.
  4. I’m not close to the mean. Not a surprise given my heritage and genetics.

No unexpected patterns emerged. No ah-ha moments. In fact, I suspect I was happier during the missing data periods when I wasn’t wearing a band to obsessively measure my runs, workouts, sleep, vitals, macro-nutrients etc.

I’ve owned at least six UP bands (if not eight). To be honest, I’ve lost count. None lasted beyond the 90 day warranty period and all except the first were replacements. While the customer service has been excellent the durability of the hardware was disappointing. This reflects heavily in the data and what you can do with it.

Replacement bands typically take 2-3 weeks to arrive, explaining the lengthy gaps in the illustrations. My apparently choppy performance (wide swings from the mean in the horizon charts) is a device reliability issue rather than inconsistent lifestyle choices or behavior patterns.

ScreenShot3

The UP band is unlikely to have any long lasting impact on my overall fitness, health or wellbeing. I’m pretty confident in saying, it won’t improve my Quality Adjusted Life Years. At best, the inactivity warnings remind me how much of a sedentary slob I can be.

If you’ve had better luck with your Jawbone UP and are interested in trying this analysis for yourself, my source code can be found on my Github repository.

D3.js & Crossfilter.js: Brushing Ordinal Scales for Bars & Choropleths

ScreenShotThere are surprisingly few online examples of solving this problem with D3. There’s Mike’s original example and this one.

This problem would seem to be simple until you try to use ordinal values that are actually numeric to produce something like a distribution chart.

The reference examples for Brushing D3 Charts typically involve a handy helper library called Dimensional Charting (dc.js) that abstracts you from most of the D3 and Crossfilter details. Useful if you just need something simple but unhelpful if you need to do things it doesn’t support. In which case, you’ll find yourself extending dc.js with D3 and thinking, “I should have just learned how to do this in D3 and saved myself the time spent picking through the dc.js API docs and SO questions”.

If you get to this point (like I did) you’ll find only a few examples of SVG brushing with Crossfilters done natively in D3. And all are pretty inaccessible for new comers. They involve iterating over arrays of reusable Chart objects (very sensible) peppered with ternary operators and terse variable and function names. This original and frankly overly sophisticated pattern seems to have become the basis for all subsequent work. It all has the feel of a secret society or private members club…

So, here it is. A very simple and hopefully well documented example of native D3 SVG Brushing of Ordinal Scales to filter a Crossfilter domain and update a Choropleth. And as a bonus I’ve thrown in some real 2013 Medicare Hospital Spend Per Beneficiary (MSPB) data.

Screen Shot barChart

In the end I have to thank Couchand for taking my +50 StackOverflow reputation for his insight in how to get this working.

An important obstacle in working with ordinal scales and SVG Brushing is the lack of an .invert() method to convert pixel coordinates to your ordinal data range. I have to thank AmeliaBR for this answer which involves using ordinal.range() and ordinal.rangeBand() to calculate brush extent values.

The X axis similarly involves some hackery to filter the ordinal scales to get a sensible looking axis scales and labels.

All in all, this turned out to be a more challenging visualization project than I expected. Full source code and data files on the Github repo with technical instructions in the README.md

[gist https://gist.github.com/nyquist212/1c83356daf0bf79653e5]

Example SQL Server TSQL Stored Procedure to produce JSON

With no native support for JSON in MS SQL Server 2012, hacks and work-arounds abound.

One of the best I’ve come across is a tidy little stored procedure from from Jamin Quimby.

It works like a treat and seems to produce JSON that validates cleanly every time.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SerializeJSON] (@ParameterSQL AS VARCHAR(MAX))
AS
BEGIN
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @XMLString VARCHAR(MAX)
	DECLARE @XML XML
	DECLARE @Paramlist NVARCHAR(1000)

	SET @Paramlist = N'@XML XML OUTPUT'
	SET @SQL = 'WITH PrepareTable (XMLString)'
	SET @SQL = @SQL + 'AS('
	SET @SQL = @SQL + @ParameterSQL + 'FOR XML RAW,TYPE,ELEMENTS'
	SET @SQL = @SQL + ')'
	SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'

	EXEC sp_executesql @SQL
		, @Paramlist
		, @XML = @XML OUTPUT

	SET @XMLString = CAST(@XML AS VARCHAR(MAX))

	DECLARE @JSON VARCHAR(MAX)
	DECLARE @Row VARCHAR(MAX)
	DECLARE @RowStart INT
	DECLARE @RowEnd INT
	DECLARE @FieldStart INT
	DECLARE @FieldEnd INT
	DECLARE @KEY VARCHAR(MAX)
	DECLARE @Value VARCHAR(MAX)
	DECLARE @StartRoot VARCHAR(100);

	SET @StartRoot = '<row>'

	DECLARE @EndRoot VARCHAR(100);

	SET @EndRoot = '</row>'

	DECLARE @StartField VARCHAR(100);

	SET @StartField = '<'

	DECLARE @EndField VARCHAR(100);

	SET @EndField = '>'
	SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
	SET @JSON = ''

	WHILE @RowStart > 0
	BEGIN
		SET @RowStart = @RowStart + Len(@StartRoot)
		SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
		SET @Row = SubString(@XMLString, @RowStart, @RowEnd - @RowStart)
		SET @JSON = @JSON + '{'
		-- for each row
		SET @FieldStart = CharIndex(@StartField, @Row, 0)

		WHILE @FieldStart > 0
		BEGIN
			-- parse node key
			SET @FieldStart = @FieldStart + Len(@StartField)
			SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
			SET @KEY = SubString(@Row, @FieldStart, @FieldEnd - @FieldStart)
			SET @JSON = @JSON + '"' + @KEY + '":'
			-- parse node value
			SET @FieldStart = @FieldEnd + 1
			SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
			SET @Value = SubString(@Row, @FieldStart, @FieldEnd - @FieldStart)
			SET @JSON = @JSON + '"' + @Value + '",'
			SET @FieldStart = @FieldStart + Len(@StartField)
			SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
			SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
		END

		IF LEN(@JSON) > 0
			SET @JSON = SubString(@JSON, 0, LEN(@JSON))
		SET @JSON = @JSON + '},'
		--/ for each row
		SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
	END

	IF LEN(@JSON) > 0
		SET @JSON = SubString(@JSON, 0, LEN(@JSON))
	SET @JSON = '[' + @JSON + ']'

	SELECT @JSON
END

It takes any SQL query as a parameter.

EXEC [dbo].[SerializeJSON] "SELECT * FROM myTable;";

When you setup this stored procedure you’ll need to grant execute permissions. I have a small team of seasoned high performers (lucky me!), so I can afford to be more liberal with permissions. In which case I’ll grant execute permission to the SQL Server public role.


GRANT EXECUTE ON dbo.SerializeJSON  TO public

Now you could schedule this as a SQL Agent Job to dump your JSON file regularly to a wwwroot directory but in a shared development environment this is cumbersome. Especially if coders need perms to modify (or copy) the Agent Job on the SQL Server. Or if you’re using SQLExpress, you don’t even have Agents Jobs.

An alternative is to create a PowerShell script with promiscuous permissions.

Import-Module 'sqlps' -DisableNameChecking;

Invoke-Sqlcmd `
 -ServerInstance "myHostName\myInstanceName" `
 -Database "myDatabaseName" `
 -Query "EXEC [dbo].[SerializeJSON] `"SELECT TOP 100 * FROM myTableName`";";

Set permissions with this command in the PowerShell Console,

Set-ExecutionPolicy Unrestricted

Now this little .ps1 script can be copied, shared and edited with a text editor. You could use Windows Task Scheduler to run this periodically to refresh your test data.

Simple. Effective.