Personal mongodb Cheatsheet

I'm trying to learn more mongodb (taking one of their free courses), so I figured I'll keep here the things that I use often (and maybe it will be useful to others).

Printing

You can use the print method to print in the shell print('hello')

Iterating over a cursor

The value returned by find and findOne is not a collection but a 'cursor', so you can't just do 

for(var obj in db.xyz.find())

you need to do

var cursor=db.xyz.find()

while(cursor.hasNext()) {

  obj=cursor.next();

  ...

}

or define a function and use forEach()

Azure for hosting HTML files

I'll be teaching a friend some HTML, and was looking for an easy, free host for playing sites; Azure has free websites, but I was afraid I'd need to teach them webmatrix of VS express (nothing against them, but it is one more thing to learn). I just discovered Azure can deploy sites from a git repo !! yipee !

XML queries in MS SQL Server

Recently I needed to extract some data that was stored as XML in a SQL Server 2012 db; I found the documentation lacking, mainly because you need to know both MS proprietary SQL extensions and XPath, to query the xml. Here's what I figured; it may not be optimal, but it works :) (BTW, I think Server 2008 has the same XML methods, but I only tested with 2012).

The xml type

SQL server supports an XML datatype; we use singlle-quotes to denote its values, but it is not quite a varchar type. Below we define a table Person, with id, name and an XML field called SkillsXML; we insert two rows. The xml looks like <skills>  <skill level="High"> SQL</skill> ... </skills>.

Simple queries

With this, we can use the query method of the xml datatype to select only certain nodes; for example, the following query would select only the first skill for each person. Notice we're using an XPath expression for selecting here; simple XPath expressions look like folders; here we're selecting from the root of the document, the skills node, and from there the first skill node (notice XPath starts counting at 1, not 0). Notice the value returned is of type xml, not a varchar, which is fine to just look at it, but you may want to cast it as a varchar to use it in other places.

Getting the value of an attribute

We can also use the value method, which allows us to get the value of an XML attribute; this method takes 2 arguments, an XPath expression and a string with a SQL datatype; the value of the XPath expression will be cast (or converted) to that type. The following query gets the value of the level attribute of the first skill.

Nodes queries

The most interesting queries use the nodes xml operator; with this, and cross apply (or outer apply) we can get one row per xml element; for example, the following query gets us each person with their skills; since each of our person rows has 2 skills, we will get 4 rows total. We can make this much nicer by using a WITH expression, selecting both the skill name (which is text inside the element and its level attribute, and casting each of them as a varchar; the following example selects only those skills with Medium level.

To Do

I will post examples using XML namespaces, and will add links to other references soon.

Web api and Dapper for super-simple REST APIs

Recently c# got the dynamic keyword, which allows for truly dynamic typing (like in Ruby, Python or Javascript), and allow you to define objects to which you can add fields at runtime. In fact, we now have ExpandoObject in the standard library.

An obvious place to take advantage of that is with communicating to databases; Most times, we're still using a relational database, and use SQL to communicate with it. SQL is dynamically typed (at least in the sense that your types are coming from a database, independently of your program), and translating it to our objects usually involves lots of gruntwork; it would be nice if the language helps with that gruntwork; with dynamic and ExpandoObject being available, there are now a few micro-frameworks for DB access; one of the simplest is Dapper ; it just adds a few extension methods to the SqlConnection class, and can either automatically put data into our classes, or into dynamic objects (which is what we'll do).

The new-ish web api in ASP.net automatically generates REST APIs with very little code; with that, and Dapper, we can give access to our database in very little time; for example, if we have a database with one table, called People, with two fields, id, and name; we can generate a controller with code as simple as:


Of course, eventually you may want to use a more complete ORM, like EntityFramework, but for quick prototyping, it doesn't get any simpler than this.

Vlookup - doing joins in excel

I've been doing some data analysis in excel (and watching somebody who really knows excel :) one of the useful functions I've learned is vlookup, which basically lets you do a join (like in SQL) ; you pass it the value to find, a range of cells over which to look, an index indicating which column to return and a boolean indicating whether to do exact or approximate matching (usually you pass false, to ask for exact matching, a behavior like SQL)

invoke-webrequest : wget for Windows

Many times you need to do a quick test that a web site or program works, or you want to download a web page within a script; in unix/linux, you'd probably use curl or wget ; Powershell (as of 3.0) has invoke-webrequest , which does a similar function.

Although it has many other options, the most basic invocation just takes a URL and it returns the 'output' of that request, as an object containing several fields; the Content field contains the actual information returned (the web page), so you can use something like this:

(Invoke-WebRequest http://okaram.com).Content.length

to find out its length, and could use String.Contains and such to verify that it returns the info you expected (so you could script a simpletest for your web app).

Other useful parameters include -OutFile to save the page to a file, and -UseDefaultCredentials to have the request use your windows credentials, in an intranet.

Powershell: Array2Object function

I've been using Powershell a lot recently (I'll be blogging a lot more about it soon); sometimes, you get an array and it is convenient to transform it into an object (so we can apply select and similar operators to it); objects in powershell can be created with the New-Object cmdlet, and we pass it the kind of object; the basic powershell object is of type psobject, and we can use Add-Member to add a field to it (powershell objects, like in most scripting languages, can have fields added dynamically to them)

The following function will create an object, given an array ; the object have fields f1,f2 ... corresponding to the array elements 0,1,...

function array2Object($arr)
{
    $obj=New-Object psobject
    $fn=0;
    foreach($field in $arr) {
        ++$fn;
        Add-Member -InputObject $obj -MemberType NoteProperty -Name "f$fn" -value $field
    }
    return $obj
}