ICT 4485 Lesson 7

1. Overview
In lesson 6 we started discussing Document databases. This week we will learn how to use MongoDB, one of the column databases. In the process we will review the document database data model.

2. Checklist
o o o o o o o Review Textbook Chapter 9 if necessary Read MongoDB Through the JavaScript Shell chapter of the MongoDB in action book by Kyle Banker (http://it-ebooks.info/book/964/) Read the 4th Chapter of The Definitive Guide to MongoDB by Eelco Plugge, Peter Membrey and Tim Hawkins (http://it-ebooks.info/book/966/) Read the MongoDB CRUD Operations Documentation (http://docs.mongodb.org/manual/MongoDB-manual.pdf) Read Lesson 7 Notes Check the discussion board for Question/Comments, and post your own if you have any Complete the 3nd Homework Assignment.

3. Using MongoDB 3.1. Starting MongoDB

Starting MongoDB is quite easy. You need to know where you uncompressed / unzipped the MongoDB package you downloaded. Within the uncompressed folder find the bin folder. There will be a couple of files in that folder that we will need: mongod.exe and mongo.exe. The mongod.exe file is the server executable, so double click on that one first. You should get a window up that looks something like Figure 1. After you have the server piece up, double click on the mongo.exe executable, which will start the client. The client should look like Figure 2.

Figure 1

Figure 2

3.2.

Oil Change Data Model

At this point, lets revisit the scenario from the previous lesson and add some more data to our project. Here is data that needs to be entered into the database:

Owner Information
First Bob Ann Jill Bill Last Smith Brown Jones Street 123 Main St. 2314 Broadway 493 1St St. City ZIP Somewhereville 80999 Somewhereville 80998 Supertown 80997 Somewhereville State CO CO CO Car 588VJK3094JKFKJSD 47384FJKJ1298JSD23 8493JFKDJ19283SDH 874JGF34989NFS382 8DS302JDKJ0238812

Adams 848 47th St.

80999 CO

Car Information
Car ID 588VJK3094JKFKJSD 47384FJKJ1298JSD23 8493JFKDJ19283SDH 8DS302JDKJ0238812 874JGF34989NFS382 Make VW Toyota VW Subaru BMW Model Jetta Camry Passat Legacy X3 Trim and Engine 1.8T XLE 2.0D 2.5 Milage 89285 42851 99431 59041 57124 Next Service Due 3/25/2014 1/4/2014 1/6/2014 2/5/2014 1/15/2014

Invoice Information

Invoice ID

Service Services Date 167540983 588VJK3094JKFKJSD 3/25/2012 Oil Replacement : 30, Windshield Wiper Replacement: 10 4980987883 588VJK3094JKFKJSD 9/25/2013 Oil Replacement : 30, Oil Filter Replacment: 20, 847382974 8DS302JDKJ0238812 4/15/2013 Oil Replacement : 30 238478742 588VJK3094JKFKJSD 5/2/2013 Oil Replacement : 30, Oil Filter Replacment: 20, 438472937 47384FJKJ1298JSD23 5/24/2012 "Windshield Wiper Replacement: 10 234878724 8DS302JDKJ0238812 12/28/2012 Oil Replacement : 30, "Air Filter Replacment: 20, 489348023 8DS302JDKJ0238812 3/25/2012 Oil Replacement : 30 489348024 874JGF34989NFS382 9/25/2013 Oil Replacement : 30, Oil Filter Replacment: 20,

Car ID

Parts Oil : 20, Windshield Wipers : 60 Oil : 20, Oil Filter : 35 Oil : 20 Oil : 20, Oil Filter : 35 Windshield Wipers : 60 Oil : 20, "Air Filter : 55 Oil : 20 Oil : 20, Oil Filter : 35

From the discussion of last week the date structure will look as follows: Car Collection { CarID : 588VJK3094JKFKJSD Car Make : VW, Car Model: Jetta, Trim and Engine : 1.8 T, Mileage at Last Service: 89285, Due for Next Service: 3/25/2014, Owner: { First Name : Bob, Last Name: Smith, Address : { street : 123 Main St.,

city : Somewhereville, state : CO, ZIP : 80999 },

}, }
Invoice Collection { InvoiceID : 167540983, CarID : 588VJK3094JKFKJSD, Service Date: 3/25/2012, Service Charges: { Oil Replacement : 30, Windshield Wiper Replacement: 10, }, Parts : { Oil : 20, Windshield Wipers : 60, },

{ InvoiceID : 4980987883, CarID : 588VJK3094JKFKJSD, Service Date: 9/25/2013, Service Charges: { Oil Replacement : 30, Oil Filter Replacment: 20, }, Parts : { Oil : 20, Oil Filter : 35, }, }

3.3. Adding, Deleting, Updating Records Methods and General Commands

To use a database you would use the use command followed by the database name. Lets call our database CarService: use CarService This does not create a database just lets MongoDB know that the documents you will be adding need to go into the CarService database. Once you start adding the documents MongoDB will create the database by itself. By the same token we do not need to create a collection either. We can just start adding records to a collection. In order to do that we need to use the insert method. We will need to preface the insert method with the db designation and the name of the collection we want to add the document to. We also need to provide the insert method with data we need to add the actual document we want to add. Lets add a simple record: db.customer.insert({"FirstName":" Bob", "LastName":"Smith", "Street":"123 Main St.", "City":"Somewhereville", "State":"CO", "ZIP":"80999"}) We can search for the records using the find method: db.customer.find() The result should look something like shown in figure 3:

Figure 3

Note that along with the record you entered, an additional field has been created the _id field of ObjectId type. In the case above the value of the key is 525f679259a127dd8a2b8682. If we wanted to add a car ID to this owner we could use the update method. With the update method we need to supply two parameters the first parameter will serve to find

the record, the second one to add the new value we want to add. We also need to use the $set operator: db.customer.update({"_id": ObjectId("525f679259a127dd8a2b8682")}, {$set: {"carID":"588VJK3094JKFKJSD"}}) If you want to modify a value you would do it the same way, except of course that you would specify an existing field . If you do not specify the $set operator, the whole record will get overwritten. For example, if you issue the following command: db.customer.update({"_id": ObjectId("525f679259a127dd8a2b8682")}, {FirstName:"Robert Bobby Jones"}) you will lose all other data values, and the newly set FirstName field is the only field that will be in the record. When it comes to deletions you can delete anything from a part of a document to a whole collection. The remove method is used to delete data. The remove method can take a field and value you want to search for. To delete the above record we could issue a command like this: db.customer.remove({"_id" : ObjectId("525f679259a127dd8a2b8682")}) You can also look up how many documents are in the collection, so we can do that to find out if indeed our document has been deleted: db.customer.count() Since the document we just deleted was the only document in our collection so far, the result of this query will be 0. You could also give a parameter to the count method, for example: db.customer.count({FirstName: Robert) in which case only customers with the first name of Robert would be counted. Just because we deleted all the records in the collection, that does not take the collection itself away. You can check what all collections are available with the following command: show collections Among others the customer collection will be listed. If you want to get rid of the collection itself you can use the drop method. db.customer.drop()

You can also check what database exist in the system by issuing the following statement: show dbs

3.4.

Searching / Retrieving Records

In the previous section we used the find method to show all records in the collection. You could also provide the find method a field value pair you want to search for. In order to search for anything we need to add a some records. I am posting the code to add a single record that matches our database schema here, but the statements that add the rest of the data are in the text file posted under Doc Sharing for this lesson. db.car.insert( { "CarID" : "588VJK3094JKFKJSD", "CarMake" : "VW", "TrimAndEngine" : "1.8 T", "MileageatLastService" : 89285, "DueForNextService" : new Date(3/25/2014), "Owner": {"FirstName" : "Bob", "LastName" : "Smith", "Address": { "Street":"123 Main St.", "City":"Somewhereville", "State":"CO", "ZIP":"80999" }, }, } )

Again, db.car.find() will return all records in the car collection. If you wanted to find only VW cars, you could issue a query like this: db.car.find({CarMake: "VW"}) You can search in subdocuments as well: db.car.find({"Owner.LastName" : "Smith"}) There is more than one VW, so if you wanted to query your records by more than one criteria, to refine your results, you would do it like this: db.car.find({CarMake : "VW", "Owner.LastName" : "Smith"}) Note that multiple criteria are separated by a comma and are within a single block wrapped by {}. If you wanted to return only the CarMake, TrimAndEngine and MilageLastService fields, i.e. create a projection, you would specify them, like this: db.car.find({CarMake : "VW", "Owner.LastName" : "Smith"}, {CarMake: 1, TrimAndEngine: 1, MileageLastService:1}) In this case we added a second block separated by {} containing the fields we want to show along with a :1. The number 1 is a flag to MongoDB that you want to show these field. If you wanted to show all but these fields, you would use a 0 instead of the one, and all fields but the ones specified would be shown. Selecting only certain fields to be returned is called projection.

You can also sort through the results by using the sort method: db.car.find({CarMake : "VW", "Owner.LastName" : "Smith"}, {CarMake: 0, TrimAndEngine: 0, MilageLastService:0}).sort({"Owner.LastName" : 1}) The sort methods takes the field you want to sort your records by. The number 1 here is a flag to MongoDB to sort the records in ascending order. You would use -1 to sort them in descending order.

There are a number of cursor methods. The table below shows the listing from MongoDB documentation1:

Cursor Methods
Name
cursor.addOption()

Description Adds special wire protocol flags that modify the behavior of the query. Controls the number of documents MongoDB will return to the client in a single network message. Returns a count of the documents in a cursor. Reports on the query execution plan, including index use, for a cursor. Applies a JavaScript function for every document in a cursor. Returns true if the cursor has documents and can be iterated. Forces MongoDB to use a specific index for a query. Constrains the size of a cursors result set. Applies a function to each document in a cursor and collects the return values in an array. Specifies an exclusive upper index bound for a cursor. For use with cursor.hint() Specifies an inclusive lower index bound for a cursor. For use with cursor.hint() Returns the next document in a cursor. Returns the number of documents left in the current cursor batch.

cursor.batchSize()

cursor.count()

cursor.explain()

cursor.forEach()

cursor.hasNext()

cursor.hint() cursor.limit()

cursor.map()

cursor.max()

cursor.min()

cursor.next()

cursor.objsLeftInBatch()

http://docs.mongodb.org/manual/reference/method/js-cursor/

10

Name
cursor.readPref()

Description Specifies a read preference to a cursor to control how the client directs queries to a replica set. Returns a cursor with modified documents that include the on-disk location of the document. Returns a count of the documents in the cursor after applying skip() and limit() methods. Returns a cursor that begins returning results only after passing or skipping a number of documents. Forces the cursor to use the index on the _id field. Ensures that the cursor returns each document, with regards to the value of the _id field, only once. Returns results ordered according to a sort specification. Returns an array that contains all documents returned by the cursor.

cursor.showDiskLoc()

cursor.size()

cursor.skip()

cursor.snapshot()

cursor.sort()

cursor.toArray()

You can also use operators like greater then, less then, etc. Lets supposed we wanted to find all VW vehicles that had the mileage greater than 90,000 at the last service we could write a query like this: db.car.find({CarMake : "VW", MileageatLastService:{$gt: 90000}}, {CarMake: 0, TrimAndEngine: 0, MilageLastService:0}).sort({"Owner.LastName" : 1}) There are a number of query and projection modifiers. For a complete list with explanations look at : http://docs.mongodb.org/manual/reference/operator/query/.

3.5.

Aggregation

There are also a number of aggregation operators, which you can find at http://docs.mongodb.org/manual/reference/operator/aggregation/. Aggregation operators are comparable to SQL aggregation operators sum, avg, min, max, etc. Here is an example

11

of how we could use one to determine the car with the highest number of miles at the last service: db.car.aggregate([{$group: {_id:0, maxMilage: {$max: "$MilageatLastService"}}}]) The syntax here is different than in SQL, but we are using a similar procedure. We are specifying the aggregation function, but we are also grouping our records. The above query groups all the records, but if you wanted to group your records by a certain field you would write your query like this: db.car.aggregate([{$group: {_id:"$CarMake", maxMilage: {$max: "$MileageatLastService"}}}]) You can also select only certain records to be aggregated by using the $match operator. In the example below we are showing results only for the Subaru cars: db.car.aggregate( [ { $match: { CarMake: "Subaru" } }, { $group: { _id: 0, maxMilage: { $max: "$MilageatLastService"} } } ] )

3.6.

Arrays and Other Operators

In our scenario we do not have any need to use arrays, so lets use a different scenario for that. Lets suppose you wanted to keep track of students and their grades in your database. You could add a sample record like this: db.student.insert({First:"Bob", Last:"Smith", Grades: [82,95,98,100, 96, 89]}) Lets suppose the student completed another assignment and we want to add the grade into the array. We would do it like this: db.student.update({Last: "Smith"}, {$addToSet: {Grades: 100}}) Like in some of the prior examples we use the first argument of the update method to specify which record we want to update, and the second argument with the update we want to perform. This time we are using the $addToSet operator to add the value to the array. Arrays have a number of other operators, which you can find at http://docs.mongodb.org/manual/reference/operator/update-array/. Arrays are not the only data type you can use operators on. There are operators you can use on other fields and you can find them at: http://docs.mongodb.org/manual/reference/operator/update-field/.

12

3.7.

Handling Relationships

There are a couple of ways to work with relationships in MongoDB. One of them involves using DBRef. The MongoDB documentation recommends using manual references, so that will be the methodology we will be using in this class.2 Using manual references involves including the objectID of one document in a second document. In our scenario we have two document collections car and invoice. We will add an invoice with the _id of the appropriate car, so we can link the two. Since we let the _id fields be system generated, we first need to find the _id of a specific car: db.car.find({CarID: "588VJK3094JKFKJSD"}) It so happens that in my case the _id for that vehicle is ObjectId("525f7e939831fa69326e3cd0"), so I will add that _id to a new field in an invoice record for that car: db.invoice.update({CarID:"588VJK3094JKFKJSD"}, {$set: {Car_oid: ObjectId("525f7e939831fa69326e3cd0")}}) Now if I pulled an invoice and wanted to use that car_oid to find out more information about the car from the car collection, I would issue a couple of commands: invoice = db.invoice.findOne({CarID: "588VJK3094JKFKJSD"}) db.car.find({_id : invoice.Car_oid}) If you are wondering if we need the CarID in the invoice collection, the answer is no the Car_oid is the field we would use to link the two collections. Keep in mind that MongoDB does not maintain relational integrity, so your application needs to manage that. Meaning, if you chose to delete the document from the car collection with CarID 588VJK3094JKFKJSD, and you wanted all references for that car to be gone from the database, you will also need to delete the appropriate record(s) from the invoice collection. In this case if we wanted to remove the previously mentioned car we would issue a remove command using the Car_oid for all the pertinent invoices and use the same ObjectID to remove the car from the car collection.

3.8.

Indexing

MongoDB supports a number of index types, which you can familiarize yourself with at http://docs.mongodb.org/manual/core/indexes-introduction/. The simplest type of index
2

http://docs.mongodb.org/manual/reference/database-references/

13

you can use is a single field index, which is very much analogous to a single field index you would have used in a relational database. Lets suppose you wanted to create a secondary index on our database on the CarMake field you would do it like this: db.car.ensureIndex({CarMake:1})

3.9.

Setting up replication and sharding

MongoDB supports sharding and replication, but both need to be set up. In order to set up sharding you first need to set up configuration servers. The config server processes are mongod instances that store the clusters metadata. You designate a mongod as a config server using the --configsvr option. Each config server stores a complete copy o f the clusters metadata. In production deployments, you must deploy exactly three config server instances, each running on different servers to assure good uptime and data safety. In test environments, you can run all three instances on a single server. 3 Once the config server(s) is/are set up, you can add servers to the sharding cluster. Following that the database you want to shard and the collections have to be enabled for sharding. In the process of setting up the collection for sharding you also have to set up the sharding key. Very much like our previous discussions about sharding, choosing keys should be a carefully considered decision in order to insure good performance. Replication also requires a bit of set up. MongoDB allows for a couple of replication modes: master-slave, and a replica set. The major difference is that the replica set is more automated. There is still a master, but if a mater fails the replica set choses a new master, thus automatically taking care of the failover. 4

4. Conclusion
MongoDB allows you to create rich data structures, use sharding and replication all without too much difficulty or configuration. Since MongoDB uses BSON / JSON for data storage and display it may take a bit to get used to the syntax for MongoDB, especially all the parenthesis and brackets. So as you are working through the lesson and homework, be careful of syntax errors.

3 4

http://docs.mongodb.org/manual/tutorial/deploy-shard-cluster/ MongoDB The Definitive Guide, Chodorow Kristina, Dirolf Michael, OReilly, September 2010.

14