Thursday, September 3, 2015

MONGO DB - Basics (CRUD)



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
table
row
document or BSON document
column
index
table joins
embedded documents and linking
primary key
Specify any unique column or column combination as 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
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
SUM()
COUNT()
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",
}
);

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


db.crew.insert(
{
name:"Sreenath Gotur",
age: 40,
job: "Technology",
technology: ["Mongo", "C++", "Java", "Oracle", "MEAN", "ROR", "Python", "DotNet"]
}
);


db.crew.save(
{
name:"Tony Almeida",
age: 41,
job: "Technology",
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();
                                 or
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",
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"})

Friday, April 27, 2012

Enabling sa account in SQL Express 2008


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.
  1. Login to SQL Express with your windows authentication account
  2. 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!!!

Friday, April 6, 2012

MVC3 deployment check-list

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