Technology

MONGO DB - Basics (CRUD)

2015-09-03T02:20:00.001-07:00

Objective:

I recently helped our business and development team to get up to speed with basic query operation and thought share the same with the community. Some of the information I pulled from mongo db site(there is no better place than the source).

What is Mongo DB

MongoDB is a Document type NoSQL database,

NoSQL = Not Only SQL

MongoDB has document oriented storage, dynamic schemas and data is stored as JSON-style documents. In No-SQL world you do not have to stick to a schema as with relational databases.

Two documents (rows) in the same collection (table) can have different schemas.

DB/Scripting Language= JavaScript

Reference to RDBMS DB terms

SQL Terms	MongoDB Concepts
database	database
table	collection
row	document or BSON document
column	field
index	index
table joins	embedded documents and linking
primary key Specify any unique column or column combination as primary key.	primary key In MongoDB, the primary key is automatically set to the_id field.
aggregation (e.g. group by)	aggregation pipeline

SQL Operators	MongoDB Aggregation Operators
WHERE	$match
GROUP BY	$group
HAVING	$match
SELECT	$project
ORDER BY	$sort
LIMIT	$limit
SUM()	$sum
COUNT()	$sum
join	No direct corresponding operator; however, the $unwind operator allows for somewhat similar functionality, but with fields embedded within the document.

*** Source: MongoDB website

Important data structure : JSON/BSON:

JSON (Java Script Object Notation):

JSON is a lightweight data-interchange format, which is completely independent from programming languages. JSON primarily works with two different structured data:

A collection of name/value pairs.

An ordered list of values.

To learn more about JSON, visit : http://www.json.org/

BSON (Binary Java Script Object Notation):

MongoDB doesn't actually use JSON to store the data; rather, it uses an open data format developed by the MongoDB team called BSON. BSON makes MongoDB even faster by making it much easier for a computer to process and search documents. BSON also adds a couple of features that aren't available in standard JSON, including the ability to add types for handling binary data.

CRUD OPERATION

Before we start working with the Mongo DB, we should take a moment to talk about the case-sensitive rules for SQL commands. Mongo uses camel case.

Before we dive in to CRUD operation with example, there are few important commands to do the basic operations like Listing all databases, connecting to database, listing all documents, etc...

use	To switch to a db library
show dbs	To view available databases and collections
show collections	To view all available collections for your current database
system.indexes	All the indexed are stored here

CREATE operation

RDBMS:

INSERT INTO crew(name, age, job, email, technology) values ("Sreenath Gotur", 40 , "Technology", "sreenathgotur@gmail.com",

"Mongo, C, Java, Oracle, MEAN, Python");

//Array values need to be stored in a different table or stored with a separator in RDBMS

MONGO:

You can Use insert or save method to insert a record

***Creating a simple document***

db.crew.insert(

{

name:"Jack Bauer",

age: 24,

job: "CTU",

email:"jack.Bauer@24.com"

}

);

***Creating a document with a array***

db.crew.insert(

{

name:"Sreenath Gotur",

age: 40,

job: "Technology",

email:"sreenath.gotur@24.com",

technology: ["Mongo", "C++", "Java", "Oracle", "MEAN", "ROR", "Python", "DotNet"]

}

);

db.crew.save(

{

name:"Tony Almeida",

age: 41,

job: "Technology",

email:"Tony.Almeida@24.com",

technology: ["Java", "C#", "J2EE", "Oracle", "SQL Server", "etc"]

}

);

***Creating a document with a array and an embedded document***

**** Note: in RDBMS, this is done using two different tables and queried by joining two tables***

db.crew.insert(

{

name:"David Palmer",

age: 25,

job: "Technology",

email:"David Palmer@24.com",

technology: ["BA", "C#", "Ms-Office", "Oracle", "SQL Server", "etc"],

address:[

{address1: "525, North Tryon Street", address2: "ODell Building", city:"Charlotte", state:"NC", zip:"28255", type: "work"},

{address1: "100, North Tryon Street", address2: "Corporate Tower", city:"Charlotte", state:"NC", zip:"28255", type: "Home"}

]

}

);

READ Operation:

RDBMS: Select * from crew

MONGO:

db.crew.find();

db.crew.find().pretty(); // Pretty looking document... :-) it will display record in Tree view structure

RDBMS: Select * from crew where name="Sreenath Gotur"

MONGO:

db.crew.find({"name" :"Sreenath Gotur"}).pretty();

db.crew.find({name :"Sreenath Gotur"}).pretty();

RDBMS: Select name, age, job from crew where name="Sreenath Gotur"

MONGO:

db.crew.find({"name" :"Sreenath Gotur"}, {name:1, age:1, job:1}).pretty();

*** By Default, _id field will be returned and to supress the "_id" field from the results ***

db.crew.find({"name" :"Sreenath Gotur"}, {name:1, age:1, job:1, _id:0}).pretty();

*** to display the embedded document***

db.crew.find({"name" :"Sreenath Gotur"}, {name:1, age:1, job:1,"address.city":1, _id:0}).pretty();

*** filter by sub document***

db.crew.find({"address.city":"Charlotte"}, {name:1, age:1, job:1,_id:0}).pretty();

RDBMS: Select * from crew where age > 40

db.crew.find( { age: { $gt: 40 } } ).pretty()

RDBMS: Select * from crew where age >= 40

db.crew.find( { age: { $gte: 40 } } ).pretty()

UPDATE Operation

RDBMS: Update crew set age=42 where name="Sreenath Gotur"

db.crew.update({ "name" :"Sreenath Gotur"}, {$set: {"age": "42"}});

*** Update as well as create a new field(column), which is impossible in RDBMS without touching the database schema***

db.crew.update({ "name" :"Sreenath Gotur"}, {$set: {"age": "42","sex": "Male"}});

*** update embedded document value ***

db.crew.update({ "name" :"David Palmer"}, {$set: {"age": "21","sex": "Male","address":{state" : "NH"}}});

*** If no record found mongo won't create or update a record ***

db.crew.update(

{ "name" :"Michelle Dessler"},

{$set:

{

name:"Michelle Dessler",

age: 42,

job: "Technology",

email:"Michelle.Dessler@24.com",

technology: ["ASP.net", "C#", "J2EE", "Oracle", "SQL Server", "etc"]

}

);

*** UPSERT, use Upsert to insert a record if no matching record found for update ***

** Basically Update if one found else create a record ***

db.crew.update(

{

name :"Michelle Dessler"}, {$set: {name:"Michelle Dessler",

age: 42,

job: "Technology",

email:"Michelle Dessler@24.com",

technology: ["ASP.net", "C#", "J2EE", "Oracle", "SQL Server", "etc"]

}

}, { upsert: true }

);

DELETE Operation

*** Delete the entire table/collection****

RDBMS: Delete from crew;

db.crew.remove({})

*** Delete records from a table based on matching condition ***

RDBMS: Delete from crew where job = "Technology";

db.crew.remove({job: "Technology"})

Enabling sa account in SQL Express 2008

2012-04-27T07:56:00.003-07:00

Problem:

How to enable 'sa' account in SQL server Express edition.

Problem Description:

By default Microsoft sets the SQL Express login mode as Windows Authentication and disables the default 'sa' account. This is done for several security reasons and best practice. But sometime we need to overwrite this for various reasons, one that I come across recently was when I try to install an open source CMS product which requires an Admin account and it did not provide me the option to change the account to windows authentication.

Solution:

If you have installed SQL Server express with Windows Authentication mode and want to change it, you need follow these steps.

Login to SQL Express with your windows authentication account
Right-click on your SQL Server Express root (see the image below) and select Properties

3. Go to Security tab and change the mode to SQL Server authentication mode and click 'OK'

4. Go to security/logins, open SA login properties

5. Uncheck "Enforce password policy" and "Enforce password expiration" check box

6. Assign password to SA user

7. Open "Status" tab and enable login (this can be also done via SQL editor by running the following command: ALTER LOGIN [sa] ENABLE)

Now login to your SQL server with 'sa' account....

Happy SQL Programming!!!

MVC3 deployment check-list

2012-04-06T04:41:00.001-07:00

Overview:

When deploying ASP.NET MVC3.0, there are many challenges to solve, especially when use external hosting and I come across similar issue and sharing my solution here.

Problem:

After hosting the application, the application may not run and page not found error(404.x / 403.x)

Solution:

The following check-list helps to avoid deployment issues with MVC 3.0

1. Check your web.config for the following code under .webServer> section

2. Make sure the App pool is set to ASP.Net 4.0 with Integrated Mode.

3. In your Project References set Copy Local to True for the following assemblies

1. System.Web.Abstractions

2. System.Web.Helpers

3. System.Web.Routing

4. System.Web.Mvc

5. System.Web.WebPages

4. Add the following assemblies to your project, and then set Copy Local to True

1. Microsoft.Web.Infrastructure

2. System.Web.Razor

3. System.Web.WebPages.Deployment

4. System.Web.WebPages.Razor

Opening Multiple Instance of VISIO 2010

2011-12-12T07:17:00.000-08:00

I had a strange issue with Visio 2010, unable to open multiple instance of Visio and its hard sometime to switch back between the diagrams.

I found the fix for the issue and posting the solution here for others to follow

Open your registry (Start >> Run>> regedit
Open the following registry path "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Visio\Application\"
Add a new Key>> choose Binary Key and give it a name called "SingleInstanceFileOpen"
leave the default value "0 0 0".
close the regedit window as well as all instance of Visio

Open different instance of Visio and you should be good to go now...

Not sure why Microsoft did this injustice to Visio alone

Mongo DB - Schema less database - Series 2

2011-12-01T08:09:00.001-08:00

Overview: In this section we'll see how to start the Mongo DB engine, create database and basic CRUD operations

Starting Mongo DB

The two "most important" applications are the mongo and mongod

applications.

The mongo application allows you to use the database shell; this shell enables you to

accomplish practically anything you'd want to do with MongoDB.

The mongod application starts the service or daemon, as it's also called. There are also many flags you can set when launching the MongoDB applications

executing the mongod.exe from command prompt (Tips: Set the path in the environment variables, so that you don't need to navigate to the actual folders)

There is a nice comparison between Mongo DB and Oracle & My SQL in Mongo web site, a helpful tip for newbies http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

I've listed down a key comparison between Mongo and other RDBMS

RDBMS	MONGO
Database	Database
Tables	Collections
Rows	Documents

In MongoDB, a document is an item that contains the actual data, comparable to a row in SQL.

Important Note:

Mongo DB is case-sensitive

$ symbol is reserved for many built-in functions

DATATYPES

String	Text values
Integer (32b and 64b)	Numerical values
Boolean	True or False
Double	Floating point values
Min/Max	Comparing values against Lowest and Highest BSON elements
Arrays	Store array of values
Timestamp	Store Time stamp
Object	Object type values
Null	Null values
Date*	Current Date
ObjectID*	Documents unique ID
Binary Data*	Binary format data
JavaScript Code*	Store JavaScript code
Regex*	Regular Expression type

*denotes BSON data types

CRUD Operations

Before i dive in to CRUD operation with example, there are few important commands to do the basic operations like Listing all databases, connecteing to database, listing all documents, etc...

One interesting command to remember is : 'Show' this is almost a replacement for 'Select * from'

use	To switch to a db library
show dbs	To view available databases and collections
show collections	To view all available collections for your current database
system.indexes	All the indexed are stored here
db..renameCollection("Target DB")	Renaming a Collection
db..distinct("xxxxx")	Getting distinct data collection

Mongo DB - schema less database - Series 1

2011-11-14T13:03:00.001-08:00

Overview:

I'm leveraging the new schema less database called Mongo DB and thought write a blog and share my experiences/knowledge to the community. I'm creating a series of post to explaining from overview to install to implementation using both Python as well as from C#.

In this post I'm going to give you an overview and explain installation option and some silent feature.

Series 1 : Overview, Install and key feature

What is Mango DB?

A MongoDB database is non-relational and schema less database and its designed based on document storage concept. This means that a MongoDB database isn't bound to any predefined rows, columns, constraints, data types, etc... the way that traditional relational databases are (Oracle, SQL-Server, DB2, MYSQL, etc…).

The philosophy in which the MongoDB was designed is "one size does not fit all". What is this means in reality? It is simple , lets say we have a situation where we need to store different data attributes based on a type of document (RDBMS: Rows).

example:

We have a table called Audit and based on the audit type one row can have a type that is defined as a string and another type can be Date Time and another one may have more than one value (a collection of data), this is impossible in RDBMS from a single table. We need to normalize and every time there is a need for another type of collection, we may need to build another table, in contrast MongoDB or any other Document DB, it is simple since they are based on the key value pair. The key is a string and the value in it can be anything. That’s the beauty of this DB. Is it a replacement for Relational Database? The answer is a big 'No', it depends on the need or complexity.

How does I query the data?

It is really simple, since MongoDB is all about document Object type, JSON is the ideal choices.

JSON (Java Script Object Notation) is a lightweight data-interchange format, which is completely independent from programming languages. JSON primarily works with two different structured data:

A collection of name/value pairs.
An ordered list of values.

To learn more about JSON, visit : http://www.json.org/

BSON (Binary Java Script Object Notation):

Does this mean learning a new Query Language? No, if you are familiar with JSON, it is straightforward, BSON is built on top of JSON and supports current JSON query on top of few additional features that are specific to binary data.

How does the MongoDB actually stores data? It is simple as a document with a keys and values, it generated an uniquely identifier key and assigns to each document, unless we specify a unique key field. Think this is similar to RowID in Oracle or ROW_NUMBER() in SQL server.

Installing MongoDB under Windows

Prerequisites

Before we can start the MongoDB service, we need to create a data directory for MongoDB to

store its files in. The following folders are configured to use by default by MongoDB

C:\data\db directory

But, they are not created by default, so we need to create them manually; otherwise, MongoDB will fail to run and throw an error message.

Once the folders are created set the read, write, and directory creation permissions to function properly.

Note: If you wish to use a directory other than the default , then we need to tell MongoDB to look at the custom directory by using the "dbpath" flag when executing the service.

Download & Install

Select the latest windows build (2.0.1) of MongoDB from http://www.mongodb.org/downloads

After you download the Zip file, extract the zip file and drop it in a location you prefer to, I like all the software's to be located under C:\Program files directory

C:\Program Files\mongodb-win32-i386-2.0.1

Once you extracted the zip file and moved to preferred location, you'll see the following directory structure

Under the bin directory where you'll see bunch of executable (these are the only executable' s we need to run the entire database engine)

The main executable are listed below

mongod.exe	core database server
mongo.exe	Database shell
mongorestore.exe	Utility to import /restore
mongodump.exe	Utility to export/backup
mongos.exe	auto-sharding process

Installing additional drivers for application integration

Currently, MongoDB includes drivers for the following programming languages and can be accessible from

http://www.mongodb.org/display/DOCS/Drivers

Haskell

Java

Scala

As I mentioned earlier in the blog , I'm trying to leverage my development using C# as well as Python, I'm showing here the download and install instructions for the same technologies:

.Net Driver (C#): is very straightforward: Just download the Microsoft installer version (.msi file) from http://github.com/mongodb/mongo-csharp-driver/downloads and run the msi. This will install the required driver file (C#) and also the help files

Python Driver: Download pymongo-2.0.1.tar.gz from http://pypi.python.org/pypi/pymongo/#downloads and install using the 'easy_install' utility, which is available under scripts directory

To confirm Python MongoDB driver is working in python run the following command from python shell (command line) and should expect no result

import pymongo

in the next Series 2, we'll see how to start creating database, documents and accessing the same using JSON (BSON object).

Custom Description for Enumerator

2011-03-03T07:08:00.000-08:00

Here I would like to show how we can create custom description with enum types. Typically enum types are a single word without a space, lets assume we have country enum types, but we also like to use the enum to show a fully qualified country name rather an abbreviated one: How is this possible? by defining Description attribute to enum type and by simply using reflector to read the description. I have listed down a code snippet to show how to define and consume.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Reflection;
namespace EnumCustomDescription
{
public class MyEnum
{
///

/// Adding Custom Description attribute to enumerator type helps to define more descriptive enum values rather than a single word or abbriviated value
/// use reflector class to retun the description instead of the actual enum value.
///

public enum enumCountry
{
[Description("United State of America")]
USA,
[Description("Australia")]
AUS,
[Description("India")]
IND,
[Description("South Africa")]
SA,
[Description("United Arab Emites")]
UAE,
[Description("United Kingdon")]
UK
}
}

public class GetDescription
{
///

/// GetEnumeratorDescription method will return the value defined in the Enum description, this uses the reflector
/// to read the description from the enum class
///

/// enumerator
/// description of the custom attribute
public static string GetEnumeratorDescription(Enum value)
{
FieldInfo _fieldInfo = value.GetType().GetField(value.ToString());
DescriptionAttribute[] _attribute = (DescriptionAttribute[])_fieldInfo.GetCustomAttributes(typeof(DescriptionAttribute), false); ;
if (_attribute.Length > 0) { return _attribute[0].Description.ToString(); }
return value.ToString();
}

}
}

WCF vs WebServices

2010-10-27T10:00:00.000-07:00

What is really the difference between ASP.NET web service and WCF service?
Before we jump in too detail, few important things to remember about the service architecture

Web Service (.asmx) relies on XMLSeralizer object to transform data back and forth.
Only Public types and properties can be transformed using Web Service
it supports classes that implements IEnumerable interface and do not support classes that implements IDictionary object Such as HashTable.

WCF on the other hand uses DataContractSerializer, it uses DataContractAttribute and DataMemberAttribute to translate .Net Types to XML. DataContractSerializer can transform IDictionary obejct.

The main contracts that makes WCF powerful are
DataContract : Defines the structure of data included in the payloads of the messages flowing in and out of the service
Service Contract : Defines which operations the service makes available to be invoked as request messages are sent to the service from the client
Message Contract: Enables you to control the headers that appear in the messages and how the messages are structured
Fault Contract : Service methods communicate processing using SOAP fault messages. SOAP faults are message types that are included in the metadata for a service operation and these exceptions can be handled via Fault contract.

Creating Proxy Object for Client Consumption of the service:
WSDL.EXE tool is used by consuming application to create Web services proxy object or class..
SVCUTIL.EXE is used by consuming application to create WCF proxy object or class.

Data Replication Architecture

2010-10-26T17:47:00.000-07:00

I'm currently working on an infrastructure challenge on data replication and thought of updating my learning in my blog:

There are several things to consider while working on server farms, where servers are located in multiple sites across the globe.

Peer to peer replication and Transaction Replications are few options that are available and are commonly used. This require not only configuration changes on the server but also require development consideration.

Peer to peer replication are available only in SQL server 2008

The servers on the farm should have identical O/S version, SQL server version, including the patches that are applied.

Database Schema should be identical

Identity column do not work in replication (atleast from SQL 2005)

Primary key with alpha-numeric or composite key will help resolve idenity column

WCF Channel Error

2010-07-29T14:54:00.000-07:00

When you consume WCF service and hit the service frequently sometime you get Channel exception, this can be avioded by closing the channelfactory properly on your client.

client.ChannelFactory.Close();

Cloud Computing

2010-07-19T18:38:00.001-07:00

I'm trying to understand myself about cloud computing and the fundamental principles behind the new technology and like to add it to my blog as a reference

Cloud computing is simply a set of services that are provided by some third party companies or within an enterprise as a centralized service. There are lots of advantages and I listed some of them below:

1. Separation of Hardware from the development team - Developers job is to develop the application based on the business requirement and not worrying about the hardware availability, space, licenses, software’s/tools, etc... Provider of this service will be responsible for all these services. In short no licensing or server expenses, development team simply register their services with the provider.

2. Maintenance: The provider of these services handles the traffic, security, network, etc... and it reduces the maintenance cost as well as the resource cost.

3. The cost incurred for these services are truly based on the usage, rather than paying for the complete hardware, network, etc...

The services that are provided by the industry are classified as three different services

1. Software as a service (SaaS)

othis service provides you an application package or a piece of software that allows you to do some tasks that you normally do at work

oe.g.: MS-Office 360, Google Docs, Drop box, Sales Force, Skype, Workday, Custom application, etc...

2. Platform as a service (PaaS)

othis service provides a platform with the tools that allows to develop and deploy application on the cloud

oe.g.: MS-Visual Studio, Ruby on Rails, Java, LAMP, Python, etc...

3. Infrastructure as a service (IaaS)

othis service provides you a physical/virtual hardware like servers, storage and networking related hardware that you can leverage for infrastructure needs

Cloud service providers (I listed few that I'm aware of):

Microsoft introduced Cloud computing called 'AZURE' in two platforms, one as a Development platform and other one as a Delivery platform. They provide all the above three services (SaaS, PaaS, IaaS). We can completely outsource the application service on Microsoft Azure platform or we can buy the complete package that includes the hardware, Azure service, SQL Azure Service, etc... as a bundle that can be housed in the enterprise.

Amazon introduce Cloud Computing called 'Amazon EC2/AWS' and they provide all the above three services (SaaS, PaaS, IaaS) and support wide range of development platform. It supports wide range of database from SQL Server, Oracle, MySQL to some open source database. Amazon also supports the Hadoop technology on their platform.

Other Cloud providers like VMWare, Rackspace, Red Hat, they generally provide IaaS and PaaS service.

The Future of Cloud Computing: though cloud computing seems like a brand new concept, this has been in the market for quite few years and some of these providers already worked on the private cloud model for some time. Some of the companies already started consuming these services, especially the new ventures are all targeting towards the cloud technology.

Risks: though the cloud services providers offers a great level of security and SLA, still there are some risk that needs to be considered before choosing the service/s. Sometime the external factor also plays a role not to choose the cloud option like Compliance, legal and other regulatory needs, also when the organization heavily rely on the vendor applications as well as application that are heavily dependent on frequent configuration changes.

Characteristics of Cloud: there are several characteristics cloud computing provides but I would like to pick the key important characteristics that I believe cloud computing offers

Self-Servicing

Consumers can provision or de-provision on the fly, you can increase the storage capacity, bandwidth capacity, load balancing, etc...

Elasticity and Scalability

As the name implies, cloud is all about elasticity, you can stretch and shrink based on your need at given point of time.
You can scale your application by increasing or decreasing the capacity

Standardization

It provides a standard interface, once you are familiar with couple of application on the cloud network; it follows the same mechanism for the rest (in most cases).
You can define the standardized security mechanism for your business.

Pay as per usage

Pay for what you use. Cloud service is more like our electricity meter, pay for the service you consumed and you can turn on or off when needed.

Resource management:

A cloud service provides a great deal of cost saving especially resource management, since most of critical tasks are managed by the cloud providers.

Deployment Models: Cloud services can be deployed and consumed in different ways

Public Cloud - in this model the entire service (IaaS, SaaS, PaaS) are hosted externally

Private Cloud- in this model the entire services are setup internally or sometime by utilizing the appliances provided by the Cloud Service providers.

Hybrid Cloud - in this model the services are in mix mode, some part of the cloud sits in public loud and some are internally hosted e.g.: Database can be managed internally and the application that consumes the database sits on the cloud.