mongo shell aggregate framework examples

!!!!!!!!!!!!!!!!
Group
!!!!!!!!!!!!!!!!

db.products.aggregate([ {$group: {_id:"$category" , num_products : {$sum:1} }} ])
will return document "result" that have ids of categories that there is in products collection with field num_products with value of number of items of specified category

db.products.aggregate([ {$group: {_id: {"manufacturer" : "$manufacturer" , "category":"$category" } , num_products : {$ sum:1} }} ])
group by compound key example

db.products.aggregate([ {$group: {_id:"$manufacturer" , sum_prices: {$sum:"$price"} }} ])
calculation of sum of totall price of devices of produced by every manufacturer

db.products.aggregate([ {$group: {_id:"$manufacturer" , avg_price: {$avg:"$price"} }} ])
calculation of the average price of devices of produced by every manufacturer

db.products.aggregate([ {$group: {_id:"$manufacturer" , categories: {$push:"$category"} }} ])
getting collection groupped by manufacturer with array (set) of categories where every category as many time in array as many such category of such manufacturer is exists in collection

db.products.aggregate([ {$group: {_id:"$manufacturer" , categories: {$push:"$category"} }} ])
getting collection groupped by manufacturer with array (set) of categories where every category as many time in array as many such category of such manufacturer is exists in collection

db.zips.aggregate([{$group:{_id:"$state","pop": {$max:"$pop"}}}])
The aggregation query that will return the population of the postal code in each state with the highest population.

db.group.aggregate([{
$group:{_id:   { class_id: "$class_id" , student_id :  "$student_id" } , average : {"$avg"  : "$score"}}} ,
{$group: {_id : "$_id.class_id" , average :{ "$avg" : "$average"}}
}])
The two step group example, on the first step we creating collection with unique pairs class_id and student_id evaluate average score for this pairs, on the second step we getting calculatting average score of certain student for certain class

!!!!!!!!!!!!!!!!
Project
!!!!!!!!!!!!!!!!
db.zips.aggregate([{$project:{_id:0, city:{$toLower:"$city"}, pop:1, state:1, zip:"$_id"}}])

projection example:
from such collection:
{
"city" : "ACMAR",
"loc" : [
-86.51557,
33.584132
],
"pop" : 6055,
"state" : "AL",
"_id" : "35004"
}

will return such:
{
"city" : "acmar",
"pop" : 6055,
"state" : "AL",
"zip" : "35004"
}

db.products.aggregate([ {$group: {_id:"$manufacturer" , sum_prices: {$sum:"$price"} }} ])
calculation of sum of totall price of devices of produced by every manufacturer

db.products.aggregate([ {$group: {_id:"$manufacturer" , avg_price: {$avg:"$price"} }} ])
calculation of the average price of devices of produced by every manufacturer


!!!!!!!!!!!!!!!!
Match
!!!!!!!!!!!!!!!!

db.zips.aggregate([{ $match : {state: NY} }])
kind of fillter, will return only documents where state is NY

!!!!!!!!!!!!!!!!!!!!!!!!!!!
Sort, Skip and Limit
!!!!!!!!!!!!!!!!!!!!!!!!!!!

db.zips.aggregate([{ $sort: {state : 1} }  , {$skip:10} , {$limit:5}]) 
query with just a sort stage to sort by state, ascending, skip the first 10 and get only next 5

!!!!!!!!!!!!!!!!
First and Last
!!!!!!!!!!!!!!!!

db.fun.aggregate([{$sort:{c:1}}, {$group:{_id:"$a", c:{$first:"$c"}}}])
will return collection of different "a" and for "c" key for the document from this collection will be the smallest "c" value for the corresponded "a"

!!!!!!!!!!!!!!!!
Unwind
!!!!!!!!!!!!!!!!

db.posts.aggregate([{ $unwind:"$comments"}]) 

from the posts collection will return new collection in which for every array element in comments arrat will be create new document

mongo shell indexes usage + miscellaneous comands

!!!!!!!!!!!!!!!!
Indexes
!!!!!!!!!!!!!!!!

db.students.ensureIndex({class:1, student_name:-1})
adding an index to a collection named students, having the index key be class, student_name.
class: ascending sorted index

student_name: descending sorted index

db.system.indexes.find()
getting all added indexes in the database

db.students.getIndexes()
getting all added indexes in the collection students

db.students.dropIndex({'class':1, 'student_name':-1})
dropping index created by db.students.ensureIndex({class:1, student_name:-1}) command

db.students.ensureIndex({''student_name':-1} , {unique:true})
create index on student_name that is unique (this action, actually, force student_name be unique)

db.students.ensureIndex({''student_name':-1} , {unique:true} , {dropDups:true})
create index on student_name that is unique and delete all duplicates of the same student_name

db.students.ensureIndex({''student_name':-1} , {unique:true} , {sparce:true})
create index on student_name that is unique and only on the documents that has student_name not null

db.scores.totalIndexSize()
getting size of al indexes in the collection scores

db.scores.find({type:"essay" , score:50} ).hint({type: 1})
if in the collection score 2 indexes were added (one on type and one on score), the hint command forces to use index on type

db.scores.find({type:"essay" , score:50}).hint({$natural:1})
forces not tu use index at all

!!!!!!!!!!!!!!!!
Miscellaneous
!!!!!!!!!!!!!!!!

db.scores.find({type:"essay" , score:50}).explain()
getting information how find was actually perfomed

db.scores.stats()
getting collection statistics

db.system.profile.find()
finding logs of the selected DB

db.students.drop()
dropping students collection

db.dropDatabase()
dropping database

mongoimport -d school -c students < students.json
import documents from students.json to the school database to the students collection

hw 3.2 jUnit coverage

//here the example of coverage hw 3.2 + 3.3 by unit tests

import com.mongodb.*;
import course.BlogPostDAO;
import org.junit.After;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import java.net.UnknownHostException;
import java.util.Arrays;
import java.util.List;
import static junit.framework.Assert.assertEquals;
import static org.junit.Assert.assertTrue;

public class BlogPostDAOTest {

    static MongoClient mongoClient;
    static DB blogDatabase;
    static DBCollection blogPostCollection;
    static BlogPostDAO blogPostDAO;

    // this method called before all test (once), to initialize DB variables
    @BeforeClass
    public static void setUp() throws UnknownHostException {
        mongoClient = new MongoClient();
        blogDatabase = mongoClient.getDB("blog");
        blogPostCollection = blogDatabase.getCollection("posts");
        blogPostDAO = new BlogPostDAO(blogDatabase);
        blogPostCollection.drop();
    }

    //this method calls after every test to clean usersCollection
    @After
    public void dropCollection() {
        blogPostCollection.drop();
    }

    @Test
    public void addUserTest() {

        System.out.println("Starting test on adding post");
        String body = "this is body of post";

        blogPostDAO.addPost("hello post", body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");

        DBObject find = new BasicDBObject("body", "this is body of post");

        DBObject post = blogPostCollection.find(find).next();

        assertTrue(post.get("body").equals(body));

        System.out.println("Post successfully added");
        System.out.println("");

    }

    @Test
    public void findPostByPermalinkTest() {
        System.out.println("Starting test on finding post by permalink");

        String body = "this is body of post";

        String permalink = blogPostDAO
                .addPost("hello post", body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");
        DBObject foundPost = blogPostCollection.findOne();

        assertEquals(foundPost, blogPostDAO.findByPermalink(permalink));

        System.out.println("Test on finding post by permalink successfully finished");
        System.out.println("");

    }

    @Test
    public void addCommentTest() {
        System.out.println("Starting test on adding comment with email");

        String body = "this is body of post";

        String permalink = blogPostDAO.
                addPost("Post number: ", body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");

        String theSecondCommentName = "The second comment";
        String theFirstCommentBody = "Comment 1 body";


        blogPostDAO.addPostComment("The first comment", "b1102@pochta.ru", theFirstCommentBody, permalink);
        blogPostDAO.addPostComment(theSecondCommentName, null, "Comment 2 body", permalink);


        DBObject commentedPost = blogPostDAO.findByPermalink(permalink);

        List<BasicDBObject> comments = (List<BasicDBObject>) commentedPost.get("comments");

        assertEquals(comments.size(), 2);

        Assert.assertEquals(comments.get(1).get("author"), theSecondCommentName);


        System.out.println("Test on adding comment with email successfully finished");
        System.out.println("");

    }

    // pretty long tests (takes 20 second), because wait 5 seconds after adding post, to get create posts in different time
    //comment if you don't want to use it and wait so long every build

    @Test
    public void findByDateDescendingTest() throws InterruptedException {
        System.out.println("Starting test on finding posts by date descending");

        String body = "this is body of post";

        for (int i = 1; i < 5; i++) {
            String permalink = blogPostDAO.
                    addPost("Post number: " + i, body, Arrays.asList(new String[]{"Awesome tag", "Tag 2", "Tag 3"}), "b1102");
            //waiting 10 seconds
            System.out.println("Waiting 5 seconds before adding new post");
            Thread.sleep(5000);
        }

        List<DBObject> foundedList = blogPostDAO.findByDateDescending(3);

        assertEquals(3, foundedList.size());

        System.out.println(foundedList.get(1).get("title"));

        assertEquals(foundedList.get(1).get("title"), "Post number: 3");

        System.out.println("Test on finding post by permalink successfully finished");
        System.out.println("");

    }

}

Easy way to insert List of Strings

//if, for example, you have some function that takes List of Strings as parameter
//the easiest way (for me) to test this function is to call it in such way

function (Arrays.asList(new String []{"List element 1", "List element 2", "List element 3"}))

Example of coping file

 //this is example of function that takes root to the folder where target file lies, and copy there file
 //Contracts_Postpaid_80.xls to the file Contracts_For_VVL.xls which will be created
    public void copyContractsForVVL(String folder) {

        InputStream inStream = null;
        OutputStream outStream = null;

        try {

            File fileToCopy = new File(folder + "//Contracts_Postpaid_80.xls");
            File copiedFile = new File(folder + "//Contracts_For_VVL.xls");

            inStream = new FileInputStream(fileToCopy);
            outStream = new FileOutputStream(copiedFile);

            byte[] buffer = new byte[1024];

            int length;
            //copy the file content in bytes
            while ((length = inStream.read(buffer)) > 0) {

                outStream.write(buffer, 0, length);

            }

            inStream.close();
            outStream.close();

            System.out.println("File is copied successful!");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

UserDAO covered by jUnit test example

import com.mongodb.*;
import course.UserDAO;
import org.junit.After;
import org.junit.BeforeClass;
import org.junit.Test;

import java.net.UnknownHostException;

import static org.junit.Assert.*;

public class UserDAOTest {
    static MongoClient mongoClient;
    static DB blogDatabase;
    static DBCollection usersCollection;
    static UserDAO userDAO;

    // this method called before all test (once), to initialize DB variables
    @BeforeClass
    public static void setUp() throws UnknownHostException {
        mongoClient = new MongoClient();
        blogDatabase = mongoClient.getDB("blog");
        usersCollection = blogDatabase.getCollection("users");
        userDAO = new UserDAO(blogDatabase);
        usersCollection.drop();
    }

    //this method calls after every test to clean usersCollection
    @After
    public void dropCollection(){
        usersCollection.drop();
    }

    @Test
    public void addUserTest()  {
        System.out.println("Starting test on adding user");

        String email =  "b1102@email.ru";

        userDAO.addUser("b1102" , "1111" , email );

        DBObject find   = new BasicDBObject("_id" , "b1102" );

        DBObject user = usersCollection.find(find).next();

        assertTrue(user.get("email").equals(email));

        System.out.println("User successfully added");
        System.out.println("");

    }

    @Test
    public void addUserThatExistsTest() throws UnknownHostException {
        System.out.println("Starting test on adding user that already exists");

        String email =  "b1102@email.ru";

        userDAO.addUser("b1102", "1111", email);
        assertFalse(userDAO.addUser("b1102", "dedede", "e3r3r"));

        System.out.println("Test on adding user that already exists passed");
        System.out.println("");

    }

    @Test
    public void CheckUserPassPositive() throws UnknownHostException {
        System.out.println("Starting positive test on checking user's pass");
        String login = "b1102";

        String email =  "b1102@email.ru";
        userDAO.addUser(login, "1111", email);

        DBObject validatedUser = userDAO.validateLogin(login , "1111");
        DBObject foundUser = usersCollection.find(new BasicDBObject("_id" , login)).next();

        assertEquals(foundUser, validatedUser);

        System.out.println("Positive test on checking user's pass passed");
        System.out.println("");

    }

    @Test
    public void CheckUserPassNegative() throws UnknownHostException {
        System.out.println("Starting negative test on checking user's pass");
        String login = "b1102";

        String email =  "b1102@email.ru";
        userDAO.addUser(login, "1111", email);

        assertNull(userDAO.validateLogin(login , "1112"));

        System.out.println("Negative test on checking user's pass passed");
        System.out.println("");

    }


    @Test
    public void GetNotExistUser() throws UnknownHostException {
        System.out.println("Starting test on getting not exists user");
        String login = "b1102";

        String email =  "b1102@email.ru";
        userDAO.addUser(login, "1111", email);

        assertNull(userDAO.validateLogin(login+"1" , "1111"));

        System.out.println("Test on getting not exists user passed");
        System.out.println("");

    }

}

UserDAO example which is based on MongoDB

/*
Here is an example of UserDAO base on mongoDB
This class can add user and validate passwords for user that already exists
In the next post there is exmap how to cover this class with jUnit tests
*/

package course;

import com.mongodb.*;
import sun.misc.BASE64Encoder;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.security.SecureRandom;
import java.util.Random;

public class UserDAO {
    private final DBCollection usersCollection;
    private Random random = new SecureRandom();

    public UserDAO(final DB blogDatabase) {
        usersCollection = blogDatabase.getCollection("users");
    }

    // validates that username is unique and insert into db
    public boolean addUser(String username, String password, String email) {

        String passwordHash = makePasswordHash(password, Integer.toString(random.nextInt()));

        DBObject newUser = new BasicDBObject("password", passwordHash).append("_id", username);

        if (email != null && !email.equals("")) {
            newUser = new BasicDBObject("password", passwordHash).append("_id", username).append("email", email);
        }

        try {
            usersCollection.insert(newUser);
            return true;
        } catch (MongoException.DuplicateKey e) {
            System.out.println("Username already in use: " + username);
            return false;
        }
    }

    public DBObject validateLogin(String username, String password) {
        DBObject user = null;

        // XXX look in the user collection for a user that has this username
        // assign the result to the user variable.
       try{
        user = usersCollection.find(new BasicDBObject("_id", username)).next();
       } catch (RuntimeException e){

       }
        if (user == null) {
            System.out.println("User not in database");
            return null;
        }

        String hashedAndSalted = user.get("password").toString();

        String salt = hashedAndSalted.split(",")[1];

        if (!hashedAndSalted.equals(makePasswordHash(password, salt))) {
            System.out.println("Submitted password is not a match");
            return null;
        }
        return user;
    }


    private String makePasswordHash(String password, String salt) {
        try {
            String saltedAndHashed = password + "," + salt;
            MessageDigest digest = MessageDigest.getInstance("MD5");
            digest.update(saltedAndHashed.getBytes());
            BASE64Encoder encoder = new BASE64Encoder();
            byte hashedBytes[] = (new String(digest.digest(), "UTF-8")).getBytes();
            return encoder.encode(hashedBytes) + "," + salt;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException("MD5 is not available", e);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("UTF-8 unavailable?  Not a chance", e);
        }
    }
}