Monthly Archives: July 2014

MongoDB-Architecure

In our previous posts we have discussed about installation steps and general database concepts used in MongoDB. In this post we will discuss about Architecture of MongoDB.

As we have discussed in our last post that the table in relational databases is equivalent to collection in MongoDB and rows is equivalent to documents.

Comparison

MongoDB is a document oriented database and supports dynamic schema. The document data model replaces the row in relation model with document-BSON (Binary JSON) in MongoDB. BSON documents can have one or more fields with predefined data type which can also contain Arrays and sub documents. This approach of allowing arrays and sub documents gives more flexibility to MongoDB – we can store complex hierarchy in a single record.

The maximum BSON document size is 16 MB, and every document is stored in records and every record has document as well as some extra space which is used by BSON documents when any update happen which cause to grow BSON document. Like in SQL Server all pages are stored contiguously; all records are contiguously stored in disk and when size of document grow more than 16MB, MongoDB allocate a new record. When MongoDB creates a new record it moves the document into that record and update all the indexes which used the reference of that document. All records are parts of Collection (Table in relational Database) and collection is made of logical grouped documents. Document in collection can have indexes also.

MongoDB documents can have can have all the data of a single record in a single document where as in relational database we can think multiple related tables to store the same. This is one more feature of MongoDB – more localized.

By dynamic schema in MongoDB we can say that documents can vary in structure- where as that is not possible in relational databases. MongoDB collections do not enforce you to define document structure. As an example of Student Collection in our previous post, we can say that for some documents students can have only four fields- Student_ID, F_Name, L_Name and Status but some documents have five fields- Student_ID, F_Name, L_Name, Status and Class. Fields can vary from document to document; if we need to add a new field in document then we can add this without affecting all other documents.

Now, we see the two approaches to store data in MongoDB collections- reference and embedded documents.
References: Like in any relational database, MongoDB also support references among collections by making relationship between documents.

RelationalEmbedded Data: In this new approach we make relationship by storing related data into a single document. We can do it in MongoDB by using arrays or subdocument. This approach helps to retrieve all related information at a single place. 

Embedded1

 

 

 

 

 

 

 

 

In our next posts we will continue our discussion on Architecture of MongoDB. We will see how Indexes are working, How Query is processed.

mongoDB – Create Database

In my recent posts on mongoDB we have seen how to install mongoDB and some basics comparison of SQL commands and mongoDB ad-hoc query language. In this post we will see how to create database in mongoDB.
Since I am from database background and very first question which comes in my mind when I want to create an object is- Which database I need to create the object?
In mongoDB, there is no direct command to create database; mongoDB creates database automatically when we insert values in any collection. When we run db.createCollection() , this creates collection only not the database.
In this post we will discuss some of the operations which we can perform with Database in mongoDB.

1. Show all the databases
    show dbs is the command to list all the databases on mongoDB server.

dbs

We can say that we have two databases at this point of time.

2. Create the database
Use <databasename> command will create new database (if it does not exists) or will use it for current operations to perform.
use myNewDatabase
Now the catch is, when we execute above command mongoDb will not create a database until we save some collections in it. Let’s check this by an example:
Show dbs

CreateDB1

At this point we can see that the newly created database is not listed by show dbs command.
To save it, let’s create a collection and insert one value in it.
db.myNewCollection.insert({Test_ID:1,Test_Name: “Deepak Sharma”})
Now again test the same by using Show dbs command.

NewDBTo drop the database, you need to execute db.dropDatabase()

DropDatabase

Commands in mongoDB

Okay, so, in our last post we have seen how to install mongoDB on Windows environment and how to run basic commands using query language of it. In today’s post we will check how to compare the normal SQL commands with mongoDB commands. MongoDB supports its own ad-hoc query language. Since mongoDB don’t support SQL like commands so here we can compare the terminology of SQL and mongoDB.

SQL Terms/Concepts  MongoDB Terms/Concepts
Database  database
Table  collection
Row  document or BSON document
Column  field
Index  index
Joins  embedded documents and linking
Primary key  primary key
In MongoDB, the primary key is automatically set to the_id field.
Aggregation (e.g. group by) aggregation pipeline

Examples: All below examples based on assumption that you have a SQL table named “Student” and in mongoDB collection name (table equivalent) is “Student” which has 4 columns: – Student_ID, F_Name, L_Name and status, mongoDB documents “Student” collection as:
{
_id: ObjectId(“53ce42294069473a367859d1″),
Student_ID: 1,
F_Name: “Deepak “,
L_Name: “Sharma”,
status: “Active”
}
Below is the comparison between SQL syntax and mongoDB ad-hoc query language.
–Create table Student
SQL Statement:

CREATE TABLE Student
(
Student_ID INT IDENTITY,
F_Name VARCHAR(10),
L_Name VARCHAR(10),
Status varchar(10)
)
mongoDB statement:
We can create collection by using two ways:
1. db.createCollection(“Student”)
This statement will create a collection name Student and later we can add columns into it.
2. Insert one value in collection (look like insert into command in SQL), when we insert values in collection – first the collection is created and then values are get inserted into that. The _id field is added automatically as primary key.
db.Student.insert( {
Student_ID :1,
F_Name : “Deepak”,
L_Name: “Sharma”,
Status: “Active”
} )

–Alter table Student
SQL Statement:
ALTER TABLE Student
ADD Admission_Date DATE

mongoDB statement:
db.Student.update({ },{ $set: { Admission_Date: new Date() } },{ multi: true })
$set operator is used to update the data of a field to the specified value, which is same as Set in SQL server to update the value with UPDATE statement. If the column does not exist, then $set operator will add the column. Later we will see the use of keyword multi.

SQL Statement:
ALTER TABLE Student
DROP COLUMN Admission_Date

mongoDB statement:
db.Student.update({ },{ $unset: { Admission_Date: “” } },{ multi: true })
$unset operator is used to delete the particular column. If the column does not exist, then $unset operator will not do anything.

SQL Statement:
DROP TABLE Student
mongoDB statement:
db.Student.drop()

SQL Statement:
INSERT INTO Student (Student_ID,
F_Name,
L_Name,
Status)
VALUES (2, ‘Sachin ‘, ‘Sharma’, ‘Active’)

mongoDB statement:
db.Student.insert({Student_ID : 2, F_Name: “Sachin”,L_Name: “Sharma”,Status: “Active” })

SQL Statements:
1. SELECT * FROM Student
2. SELECT * FROM Students WHERE Student_ID=1
3. SELECT F_Name,L_Name,Status FROM Student
4. SELECT F_Name,L_Name,Status FROM Student WHERE Student_ID=1
5. SELECT F_Name,L_Name,Status FROM Student WHERE Student_ID=1 AND Status= ‘Active’
6. SELECT F_Name,L_Name,Status FROM Student WHERE L_Name =’Sharma’ OR Status= ‘Active’
7. SELECT F_Name,L_Name,Status FROM Student WHERE L_Name LIKE ‘Sha%’
8. SELECT TOP 1 * FROM Student
9. SELECT COUNT(*) FROM Student
10. UPDATE Student SET L_Name=’Singh’ WHERE Student_ID=2
11. DELETE FROM Student WHERE Student_ID=2

mongoDB statements:
1. db.Student.find()
2. db.Student.find({Student_ID: 1 })
3. db.Student.find({},{F_Name:1,L_Name:1,Status:1})
4. db.Student.find({Student_ID:1},{F_Name:1,L_Name:1,Status:1})
5. db.Student.find({Student_ID:1,Status:”Active”},{F_Name:1,L_Name:1,Status:1})
6. db.Student.find({$or:[{L_Name:"Sharma"},{Status:"Active"}]},{F_Name:1,L_Name:1,Status:1})
7. db.Student.find({L_Name: /^Sha/ },{F_Name:1,L_Name:1,Status:1})
8. db.Student.find({},{F_Name:1,L_Name:1,Status:1}).limit(1)
9. db.Student.find({}).count()
10. db.Student.update( {Student_ID:2},{$set:{L_Name : “Singh”}} )
11. db.Student.remove({Student_ID:2})

Point to remember: mongoDB is case sensitive, like in last statement if we write db.Student.Remove({Student_ID:2})  (R in remove in caps-lock) it will throw an error; or if we write db.Student.remove({Student_id:2}) (id in small) it will also throw an error.

Conclusion: In this post we have learned basic comparison of SQL syntax and its equivalent in mongoDB. In our next post we will see some more concepts on mongoDB and work on GUI based tool instead of command line.

mongoDB – Installation-First Step

In today’s post we will see how to install mongoDB on Windows. Since, it is very first article in this category so we will see:

  1. Where to download.
  2. How to install
  3. Run some simple scripts to check the database, users and tables.

MongoDB is the leading NoSQL (Not Only SQL) database and most important point is – it is open-source. It is an agile database – allow change in schema as change/evolve in applications – use BSON (Binary – JSON (Java Script Object Notation)) documents. You can download mongoDB from here.

When you download your copy, you will see below 15 files.
MongoDB

In first step, unzip the files and paste them in any of your hard drive (in my case I’ve copied them in C drive). Now, to install mongoDB, open command prompt by running it as an Administrator and go up to the Bin directory and then type mongod (open mongoDB server) and press enter:
mongoD

Sometimes you will check that it hangs the operation at 3 (in above screen) – admin web console waiting for connections on port 28017. Do not panic this is not the error, open another command prompt using Administrator again go to up to bin directory and type mongo (open mongoDB client)  and press enter, this command will initialize your services and when you check previous command prompt it has accepted the connection.
mongo

Now, you are ready to run some commands:
db.user: will give you the list of all users.
db.databse: will give you the list of databases.
db.table: will give you the list of tables.
db.user.insert({user: “yourusername”,password:”yourpassword”}): will add user and password.
MongoUsers

In our next post we will see some more commands on mongoDB and GUI for mongoDB .

Using Google static API in SSRS reports

In this post we will see how we can use Google API in SSRS reports, this will help us to facilitate Google Maps in our report. You can get all the information about Google API here.  In this post we will see:
1. How to use API as default.
2. How to pass parameters in API.
First lets create a simple SSRS report. For this go to Start->All Programs->Microsoft SQL Server 2012->SQL Server Data Tools and create a new Report Server Project.

SSDT Step-1

 

 

 

 

 

 

 

 

 

 

 

Now, add one report and name it- MapAPI as per below image:

Step-2

Now drag an Image from toolbox to report surface and select external from Image source and click on expression button and paste the below expression in it and click OK
=”http://maps.googleapis.com/maps/api/staticmap?center=India&zoom=4&size=500×500&maptype=hybrid&sensor=false” (Copy this expression from here)

Step-3 Step-4
Now go to size properties of Image and select Original Size as display and click OK.

Step-5

 

 

 

 

 

 

 

 

 

 

 

We are done, check the preview of your report which looks like below image:
Step-6

 

 

 

 

 

 

 

 

 

 

 


Now the next step, to pass a parameter in this image. We can pass different type of parameters in Google API (list is
here). We will see Map Type parameter in this post. As per Google documentation:

maptype (optional) defines the type of map to construct. There are several possible maptype values, including roadmap, satellite, hybrid, and terrain.

For this go to report design and do right click on Parameters in Report Data and click on Add Parameter, enter MapType in name as well as in Prompt and click on Available Values and select specify values and add roadmap, satellite, hybrid, and terrain in value and click on OK.
Step-7

 

 

 

 

 

 

 

 

 

Again to go report design and do right click on Image properties and change the existing expression to below expression:
=”http://maps.googleapis.com/maps/api/staticmap?center=India&zoom=4&size=500×500&maptype=”&Parameters!MapType.Value & “&sensor=false” (Copy this expression from here)
Click on OK twice to complete the report.

Step-8

Now check the report preview. Your report will display the map type as per parameter value. Below is the example of Satellite view of report.

Step-9

 

 

 

 

 

 

 

 

 

 

 

 

In next post we will see how we can do the same by using Map control in SSRS report.