This chapter starts with a brief overview of the scenario used for the remaining chapters of the book. After a brief discussion on how to handle monetary data, the focus of this chapter switches to working with multiple collections. Developers who are used to formulating SQL JOIN statements that connect multiple tables are shown how to do the equivalent in MongoDB. You'll also learn how to process programming operations that require multiple updates across collections. The technique can be tricky, and this chapter outlines some of the potential pitfalls novices to MongoDB might experience. Lastly, GridFS technology is introduced as a way of handling large files and storing documents directly in the database.
The topics covered in this chapter include the following:
- Introducing BigLittle Micro Finance Ltd.
- Handling monetary data
- Referencing documents across collections
- Performing secondary updates
- Avoiding potential pitfalls
- Storing large files using GridFS
Technical requirements
Minimum recommended hardware:
- Desktop PC or laptop
- 2 GB free disk space
- 4 GB of RAM
- 500 Kbps or faster internet connection
Software requirements:
- OS (Linux or Mac): Docker, Docker Compose, Git (optional)
- OS (Windows): Docker for Windows and Git for Windows
- Python 3.x, PyMongo driver, and Apache (already installed in the Docker container used for the book)
- MongoDB Compass (needs to be installed on your host computer; instructions are in the chapter)
Installation of the required software and how to restore the code repository for the book is explained in Chapter 2, Setting Up MongoDB 4.x. To run the code examples in this chapter, open a Terminal window (Command Prompt) and enter these commands:
cd /path/to/repo
docker-compose build
docker-compose up -d
docker exec -it learn-mongo-server-1 /bin/bash
To run the demo website described in this chapter, once the Docker container from Chapter 3, Essential MongoDB Administration Techniques to Chapter 12, Developing in a Secured Environment has been configured and has been started, there are two more things you need to do:
- Add the following entry to the local hosts file on your computer:
172.16.0.11 chap10.booksomeplace.local
The local hosts file is located at C:\windows\system32\drivers\etc\hosts on Windows, and for Linux and Mac, /etc/hosts.
- Open a browser on your local computer (outside of the Docker container) to this URL:
http://chap09.booksomeplace.local/
When you are finished working with the examples covered in this chapter, return to the Terminal window (Command Prompt) and stop Docker as follows:
cd /path/to/repo
docker-compose down
The code used in this chapter can be found in the book's GitHub repository at https://github.com/PacktPublishing/Learn-MongoDB-4.x/tree/master/chapters/10 as well as https://github.com/PacktPublishing/Learn-MongoDB-4.x/tree/master/www/chapter_10.
Introducing BigLittle Micro Finance Ltd.
For our last use case scenario we introduce BigLittle Micro Finance Ltd. This is a fictitious online company that links borrowers with lenders. Although technically a bank, BigLittle Micro Finance Ltd. specializes in micro loans: loan amounts that do not exceed 20,000 USD. Accordingly, in order to make money, the company needs to have a much larger customer base than an ordinary commercial bank would. Borrowers could be either individuals or small businesses. Lenders, likewise, can be individuals or small businesses (or even small banks).
For the purposes of this scenario, assume the website is based on Django for reasons stated in Chapter 7, Advanced MongoDB Database Design, in the section entitled Using MongoDB with Django. Unlike the previous scenario (for example, Book Someplace Inc.), however, in the remaining chapters of this book, we do not show full Django code. Rather we provide the pertinent code examples and leave it to you to fit the code into a full Django implementation. Also, please bear in mind that the full code can be seen in the repository associated with this book.
First, let's have a look at how monetary data should be handled.
Handling monetary data in MongoDB
Normal floating-point data representations work well for most situations. In the case of financial operations, however, extreme precision is required. As an example, take the subject of currency conversion. If a customer is transferring money between United States Dollars(USD) and Indian Rupees (INR), for example, adding precision produces radically different results as the sums of money exchanged increase.
Here is an example. For the sake of argument, let's say that the actual exchange rate between USD and INR is the following:
ACTUAL: 1 USD = 70.64954999 INR
However, if the program application only extends precision to 4 decimal places, the rounded rate used might be this:
ROUNDED: 1 USD = 70.6495
The difference when transferring 1,000,000 USD starts to become significant:
Actual: 70649549.99
Rounded: 70649500.00
Even in the case of small amounts being exchanged, over time the differences could start to add up to significant amounts. Also, imagine the effect precision might have when calculating loan payments where the annual and effective percentage rates are stated without high precision! For the purposes of precision, MongoDB includes the NumberDecimal data type, which is based upon the BSON Decimal128 data type. This is preferred when dealing with financial data as extreme precision is required.
Although you might consider using
NumberDecimal (
Decimal128) format (
https://docs.mongodb.com/manual/core/shell-types/#numberdecimal) to store financial data, Python and Django are not able to handle it. Accordingly, you must perform a conversion in your code from
Decimal128 to the Python class
decimal.Decimal before using it in arithmetic operations. The conversion process is covered later in this chapter.
Let's now have a brief look at the data structures needed for BigLittle Micro Finance Ltd.
User collection data structures
As seen with Book Someplace, Inc., we break down each collection document structure into a series of smaller classes. Accordingly, the following data structures are exactly as shown in Chapter 7, Advanced MongoDB Data Design:
- Name
- Location
- Contact
- OtherContact
- OtherInfo
- LoginInfo
If you would like to review the complete data structures for these classes, please refer to /path/to/repo/chapters/07/document_structure_definitions.js.
The BigLittle user collection document structure includes a userType field that identifies whether the user is a borrower or a lender. Further, these two identifiers are present in the common collection under the userType key to maintain uniformity. Here is the formal JSON data structure for the user collection:
User = {
"userKey" : <string>,
"userType" : Common::userType,
"amountDue" : <NumberDecimal>,
"amountPaid" : <NumberDecimal>,
"name" : <Name>,
"address" : <Location>,
"contact" : <Contact>,
"otherContact" : <OtherContact>,
"otherInfo" : <OtherInfo>,
"login" : <LoginInfo>
}
In our scenario, in order to provide convenience to loan administrators, lenders, and borrowers, we have added two additional fields to the user collection: amountPaid and amountDue. In the case of a borrower, amountPaid represents how much money has been paid to the lender, including overpayments. The amountDue field represents a sum of the amountDue fields in the loan payments list (shown next).
In the case of a lender, amountPaid represents a sum of the amounts received from users who borrowed from this lender. The amountDue field represents the sum of all amounts due from borrowers for the lender.
Loan collection data structure
The heart of this business is the loan collection. Obviously, each loan document records not only the currency, amount, and interest, but the payment schedule as well. Accordingly, we need to first define a Payment class, as shown here. Each payment records the payment amount due, the amount paid, the due date, the payment received date, and the status. The status consists of a set of key words recorded in the common collection payStatus key:
Payment = {
"amountDue" : <NumberDecimal>,
"amountPaid" : <NumberDecimal>,
"dueDate" : <Date>,
"recvDate" : <Date>,
"status" : Common::payStatus
}
The LoanInfo class describes the amount borrowed (the principal), annual interest rate, effective interest rate, currency, and monthly payment amount:
LoanInfo = {
"principal" : <NumberDecimal>,
"numPayments" : <int>,
"annualRate" : <NumberDecimal>,
"effectiveRate" : <NumberDecimal>,
"currency" : Common::currency,
"monthlyPymt" : <NumberDecimal>
}
For the purposes of this book, we make the following assumptions:
- The annual interest rate is expressed as a percentage.
- The effective interest is calculated as Annual Interest / 100 / 12 (that is, the number of months in a year).
- Payments are always monthly: there is no provision for quarterly nor annual payments.
- There is no support for amortized loans, negative interest loans, nor balloon payments.
Please note that the bullet points include many financial terms. If you are not familiar with these, the quickest way to get up to speed is to either do a Google search or consult Wikipedia. A study of finances is beyond the scope of this book.
The formula for calculating monthly payments is as follows:
monthlyPymt = principal *
( effectiveRate / (1 - (1 + effectiveRate) ** -numPayments ))
Finally, the loan collection document structure simply links the borrower, lender, and payment schedule, as well as loan information. In addition, the loan document contains a payment schedule in the form of a list of payment documents, as well as a field representing any overpayments:
Loan = {
"loanKey" : <string>,
"borrowerKey" : <string>
"lenderKey" : <string>,
"loanInfo" : <LoanInfo>,
"overpayment" : <NumberDecimal>
"payments" : [<Payment>,<Payment>,etc.]
}
Next, we'll tackle common values used by BigLittle Micro Finance Ltd.
Common collections
Finally, as with our other scenarios, we introduce a common collection that serves to house choices used in drop-down menus. We also use this to enforce uniformity when collecting data. Here are the currently suggested definitions for BigLittle:
Common = [
{ "title" : ['Mr','Ms','Dr',etc.] },
{ "userType" : ['borrower','lender'] },
{ "phoneType" : ['home', 'work', 'mobile', 'fax'] },
{ "socMediaType" : ['google','twitter','facebook','skype',etc.] },
{ "genderType" : [{'M' : 'Male'},{'F' : 'Female'},{'X' : 'Other'}] },
{ "currency" : ['AUD','CAD','EUR','GBP','INR','NZD','SGD','USD'] },
{ "payStatus" : ['scheduled','received','overdue'] },
]
Now that you have an idea of the BigLittle data structures, as well as a general understanding of the NumberDecimal class, let's look at how financial data might be stored in the case of BigLittle Micro Finance Ltd.
Storing financial data
As with Book Someplace, Inc., our previous scenario, we use Django to build a web infrastructure. The source code can be seen at /path/to/repo/chapters/10. The web infrastructure is at /path/to/repo/www/chapter_10. In this chapter, we do not dive into the details of Django, but rather draw out the pertinent bits of code needed to illustrate the point.
In order to generate a loan proposal, we need to develop a domain service class that gives us access to the database loans collection. For this purpose, we created the biglittle.domain.loan.LoanService class. In this class, the key method generates a loan proposal, as shown here:
def generateProposal(self, principal, numPayments, annualRate, \
currency, borrowerKey, lenderKey, \
lenderName, lenderBusiness) :
effective_rate = annualRate / 100 / 12;
monthly_payment = principal * \
( effective_rate / (1 - (1 + effective_rate) ** -numPayments ))
loanInfo = {
'principal' : principal,
'numPayments' : numPayments,
'annualRate' : annualRate,
'effectiveRate' : effective_rate * 1000,
'currency' : currency,
'monthlyPymt' : monthly_payment
}
loan = {
'borrowerKey' : borrowerKey,
'lenderKey' : lenderKey,
'lenderName' : lenderName,
'lenderBusiness' : lenderBusiness,
'overpayment' : 0.00,
'loanInfo' : loanInfo,
'payments' : []
}
return loan
The save() method does the actual database insertion, shown here. Note that we first convert the data format of the financial data fields to BSON Decimal128 before storage:
def save(self, loan) :
loan = self.generateLoanKey(loan)
loan.convertDecimalToBson()
return self.collection.insert(loan)
As mentioned earlier, we need to use the Python class decimal.Decimal for processing, but wish to store our information in MongoDB using the BSON Decimal128 (MongoDB NumberDecimal) data format. The conversion process could be used any time data needs to be stored and retrieved, however, this could lead to redundant code. In this case, the best solution is to have the entity class perform the data conversion. Accordingly, from the top of the loan entity class, found in the module /path/to/repo/chapters/10/src/biglittle/entity/loan.py, we import the two data format classes:
from decimal import Decimal
from bson.decimal128 import Decimal128
We then add two methods to the biglittle.entity.loan.Loan class: convertDecimalToBson() and convertBsonToDecimal(). The convertDecimalToBson() method is shown first. As you can see, the conversion process simply takes the current value of the field and supplies a string representation to the constructor of bson.decimal.Decimal128:
def convertDecimalToBson(self) :
self['overpayment'] = Decimal128(str(self['overpayment']))
if 'payments' in self :
for doc in self['payments'] :
doc['amountPaid'] = Decimal128(str(doc['amountPaid']))
doc['amountDue'] = Decimal128(str(doc['amountDue']))
loanInfo = self['loanInfo']
loanInfo['principal'] = Decimal128(str(loanInfo['principal']))
loanInfo['annualRate'] = Decimal128(str(loanInfo['annualRate']))
loanInfo['effectiveRate'] = \
Decimal128(str(loanInfo['effectiveRate']))
loanInfo['monthlyPymt'] = Decimal128(str(loanInfo['monthlyPymt']))
return True
The reverse process, converting from Decimal128 to Decimal, is in one sense easier, and in another sense more difficult. The process is easier in that there is a method, Decimal128.to_decimal(), that produces a decimal instance. It is more difficult in that we need to first make sure that all financial data fields are of type Decimal128. This is accomplished by first calling convertDecimalToBson(), ensuring all financial data is in BSON Decimal128 format. The code for convertBsonToDecimal() is shown here:
def convertBsonToDecimal(self) :
self.convertDecimalToBson()
self['overpayment'] = self['overpayment'].to_decimal()
if 'payments' in self :
for doc in self['payments'] :
doc['amountPaid'] = doc['amountPaid'].to_decimal()
doc['amountDue'] = doc['amountDue'].to_decimal()
loanInfo = self['loanInfo']
loanInfo['principal'] = loanInfo['principal'].to_decimal()
loanInfo['annualRate'] = loanInfo['annualRate'].to_decimal()
loanInfo['effectiveRate'] = loanInfo['effectiveRate'].to_decimal()
loanInfo['monthlyPymt'] = loanInfo['monthlyPymt'].to_decimal()
return True
Now that you have an idea how to handle monetary data, it's time to examine how to connect documents across multiple collections.
Referencing documents across collections
As we have discussed throughout this book, one major objective in your initial database design should be to design a database document structure such that there is no need to cross-reference data between collections. The temptation, especially for those of us steeped in traditional RDBMS database techniques, is to normalize the database structure and then define a series of relationships that are contingent upon a series of interlocking foreign keys. This is the beginning of many problems ... for legacy RDBMS developers! For those of us fortunate enough to be operating in the MongoDB NoSQL world, such nightmares are a thing of the past, given a proper design. Even with a proper database design, however, you might still find occasions requiring a reference to documents across collections in MongoDB.
Let's first have a look at a number of techniques commonly used to reference documents across collections.
Techniques for referencing documents across collections
There are several techniques available to MongoDB developers. The more popular ones include the following:
- The building block approach
- Embedded documents
- Document references
A less common cross-reference technique would be to use database references (DBRefs). For on-the-fly cross-references, another extremely useful tool is to use the $lookup pipeline aggregation operator. Although the building block approach has actually already been used in the scenarios describing Book Someplace, we present a short discussion of this approach next.
The building block approach
In this approach, the developer defines a set of small classes (that is, building blocks) that represent objects needed in your application that must ultimately be stored. You then define a collection of document structures as a super-set of these small classes. We have already seen an example of this approach in the design for Book Someplace. For example, we designed an object location, which includes fields such as streetAddress, city, postalCode, and more. This class was then used as part of the document structure for both the customers and the properties collections.
The advantage of this approach is that the size of each document is smaller and more manageable. The disadvantage is that you might need to perform a secondary lookup if additional information from the other collection is needed.
As an example, recalling the Book Someplace scenario, when we create an entry in the bookings collection, we include the following small objects containing customer information: Name, Location, and Contact. But what if we need to produce a booking demographic report that also includes the customer's gender? Using the building block approach, we would have two choices: expand what is included in each booking document to also include the OtherInfo class (includes gender), or perform a secondary lookup and get the full document from the customers collection.
Embedded documents
This approach is very similar to the one we've just described, except, in this case, the entire document from one collection is embedded directly inside the document from another collection. When using the building block approach, only a sub-class from one collection is inserted into the other collection.
The advantage of the embedded document approach is that there is absolutely no need for a further lookup should additional information from the other collection be needed. The disadvantage of this approach is that we now have duplicate data, which in turn also means potentially wasted space as we probably do not need all of this information all of the time.
As an example, in the case of Book Someplace, for each booking, instead of using the building block approach and storing just fragments of the customer, we could instead embed the entire document for each customer making a booking.
Document references
The third approach, most commonly used by MongoDB developers, is to use document references. In this approach, instead of storing a partial document (that is, a building block), or instead of storing an entire document (embedded document), you only store the equivalent of a foreign key, which then allows you to perform a secondary lookup to retrieve the needed information.
Although you could conceivably store the _id (ObjectID instance) field as the reference, it is considered a best practice in the MongoDB world to have a developer-generated unique key of some sort, which can be used to perform the lookup.
As an example, when storing loan information, instead of storing partial or complete borrower and lender information, just store the userKey fields. This can then be used in conjunction with find() to retrieve lender or borrower information. You will note that this is the approach taken with BigLittle Micro Finance Ltd. When creating a loan document, the userKey field for the lender is stored in lenderKey, and the userKey value for the borrower is stored in borrowerKey.
In order to extract information from related documents on the fly, the most effective approach is to use the MongoDB aggregation framework and create a new pipeline stage using the $lookup stage operator (discussed later in this chapter).
Database references (DBRefs)
Yet another approach, used less frequently, is to use a DBRef. Technically speaking, there is actually no difference between creating a DBRef as compared to simply adding a document reference (see the previous sub-topic). In both cases, an additional lookup is required. The main difference between using a DBRef compared to using a document reference is that the DBRef is a uniform, formalized way of stating the reference.
DBRef is actually not a data type in and of itself. Rather, it's a formal JSON structure that completely identifies the document in the target collection. Here are the fields required to insert a properly recognized DBRef:
- $ref: The name of the target collection
- $id: The value of the _id field in the document to be referenced in the target collection
In addition, a third field, $db, containing the name of the target collection's database, can also be added when defining a DBRef, however, this is not supported by all programming language drivers.
The generic syntax for creating a DBRef would appear as follows:
var = { "$ref" : <collection name>, "$id" : ObjectId(<value of _id field>) }
Alternatively, you can directly reference the DBref and create the instance by supplying these two arguments to the constructor:
var = DBRef(<collection name>, ObjectId(<value of _id field>))
Using DBRefs does not automatically create document associations. Retrieving full documents across collections involves multiple queries. For this reason, the MongoDB documentation actually recommends that you create your own unique document references because using DBRefs involves extra unnecessary steps. If you wish to retrieve full document information across collections in a single operation, use the $lookup aggregation pipeline operator (discussed next).
Now let's have a quick look at how you can use the aggregation framework to perform operations across collections.
$lookup aggregation pipeline stage
One last document cross-reference approach we present is to use the aggregation pipeline $lookup stage operator (https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#lookup-aggregation). This operator performs the equivalent of an SQL LEFT OUTER JOIN. The prerequisite for this operation is to have a field that is common between the two collections referenced. The generic syntax is as follows:
db.<this_collection>.aggregate([
{ $lookup: {
from: <target_collection_name>,
localField: <common_field_in_this_collection>,
foreignField: <common_field_in_target_collection>,
as: <output_array_field> } }
]);
Two other options are available for more complex processing. let allows you to assign a value to a variable for temporary use in the pipeline. pipeline lets you run pipeline operations on the incoming data stream from the target collection. A practical example follows in the next several sections.
Practical example using document references
As we have already covered usage examples for building-block and embedded documents in earlier chapters, let's now focus on how to use and create a document that relates to other documents using a common reference. As an example, let's look at the process of creating a Loan document. As you recall from the Loan collection data structure section in this chapter, the Loan document includes two references to the users collection: one for the borrower and another for the lender.
The users collection includes a unique field named userKey. When we create a loan document, all we need to do is to add the value of the userKey property for the lender, and the userKey value for the borrower.
The final loan document might appear as follows:
{
"_id" : ObjectId("5da950636f165e472c37a98d"),
"loanKey" : "LAMOHOLM1595_CHRIMARQ4459_20200303",
"borrowerKey" : "LAMOHOLM1595",
"lenderKey" : "CHRIMARQ4459",
"overpayment" : NumberDecimal("0.00),
"payments" : [ <not shown> ],
"loanInfo" : {
"principal" : NumberDecimal("19700.0000000000"),
"numPayments" : 24,
"annualRate" : NumberDecimal("1.26000000000000"),
"effectiveRate" : NumberDecimal("0.00105000000000000"),
"monthlyPymt" : NumberDecimal("831.650110710560")
}}
Let's first look at the process of collecting loan information from a potential borrower and generating loan proposals.
Gathering information to generate a loan proposal
In our scenario, we build the loan document after collecting information from the potential borrower using a Django form. Here is the code, located in /path/to/repo/www/chapter_10/loan/views.py. At the beginning of the code file are the import statements:
from django.template.loader import render_to_string
from django.http import HttpResponse
from config.config import Config
from db.mongodb.connection import Connection
from biglittle.domain.common import CommonService
from biglittle.domain.loan import LoanService
from biglittle.domain.user import UserService
from biglittle.entity.loan import Loan
from biglittle.entity.user import User
from utils.utils import Utils
The method that presents the form to the fictitious borrower is index(). We first initialize the important variables:
def index(request) :
defCurr = 'USD'
proposals = {}
have_any = False
principal = 0.00
numPayments = 0
currency = defCurr
config = Config()
comSvc = CommonService(config)
userSvc = UserService(config, 'User')
maxProps = 3
utils = Utils()
borrowerKey = 'default'
We then use the biglittle.domain.common.CommonService class to retrieve a list of payment lengths and currencies that are offered by our fictitious lenders:
payments = comSvc.fetchByKey('paymentLen')
currencies = comSvc.fetchByKey('currency')
Next, we check to see if the request is an HTTP POST. If so, we gather the parameters needed to calculate loan payments:
if request.method == 'POST':
if 'borrower' in request.POST :
borrowerKey = request.POST['borrower']
if 'principal' in request.POST :
principal = float(request.POST['principal'])
if 'num_payments' in request.POST :
numPayments = int(request.POST['num_payments'])
if 'currency' in request.POST :
currency = request.POST['currency']
If the amount entered for principal is greater than zero, we proceed with the loan calculation. Please note that the pickRandomLenders() method shown here is a simulation. In actual practice, the web app would send a notification to potential lenders. The web app would then present proposals to the borrower for final selection. Although we use a custom cache class to store proposals for the next cycle, we could just as easily have used the Django session mechanism:
if principal > 0 :
utils = Utils()
have_any = True
loanSvc = LoanService(config, Loan())
lenders = userSvc.pickRandomLenders(maxProps)
have_any = True
proposals = loanSvc.generateMany(float(principal), \
int(numPayments), currency, borrowerKey, lenders)
utils.write_cache(borrowerKey, proposals)
Finally, as with the web apps shown for our previous scenario, Book Someplace, Inc., we render nested templates – first loan.html, and then layout.html, and return an HttpResponse:
params = {
'payments' : payments,
'currencies' : currencies,
'proposals' : proposals,
'have_any' : have_any,
'borrowers' : borrowers,
'principal' : principal,
'numPayments': numPayments,
'currency' : currency,
'borrower' : str(borrowerKey)
}
main = render_to_string('loan.html', params)
home = render_to_string('layout.html', {'contents' : main})
return HttpResponse(home)
The loan generation method is located in the /path/to/repo/chapters/10/src/biglittle/domain/loan.py module. Again, as mentioned above, this is a simulation where we pick annual rates at random. In actual practice, the web app would solicit loan proposals from lenders who would offer an appropriate annual rate:
def generateMany(self, principal, numPayments, currency, \
borrowerKey, lenders) :
proposals = {}
for item in lenders :
import random
annualRate = random.randint(1000,20000) / 1000
doc = self.generateProposal(principal, numPayments, \
annualRate, currency, borrowerKey, item['key'], \
item['name'], item['business'])
proposals.update({ item['key'] : doc })
return proposals
Please note that the actual loan generation process, generateProposal(), was discussed in detail in the Handling monetary data in MongoDB section at the beginning of this chapter, and is not duplicated here.
Now, it's time to have a look at the code in which a borrower accepts a loan proposal.
Creating a loan document using document references
Once a loan proposal has been accepted by the borrower, we are able to put together a Loan document. This is where we put document references to use. The code that captures the borrower's selection is also located in views.py, in the accept() method. We start by checking to see if the request is an HTTP POST. We also check to make sure the accept button was pressed, and that the key for lender was included in the post, shown here:
def accept(request) :
message = 'Sorry! Unable to process your loan request'
utils = Utils()
if request.method == 'POST':
if 'accept' in request.POST :
if 'lender' in request.POST :
lenderKey = request.POST['lender']
borrowerKey = request.POST['borrower']
We retrieve the proposals from our simple cache class and loop through them until we match the lender key, after which we use the loan domain service to save the document:
proposals = utils.read_cache(borrowerKey)
if lenderKey in proposals :
loan = proposals[lenderKey]
config = Config()
loanSvc = LoanService(config, Loan())
loanSvc.save(loan)
All that is left is to return to the previous web page – simply a matter of calling the index() method:
return index(request)
Let's now turn our attention to a practical example using the $lookup aggregation pipeline operator.
Practical example using the $lookup aggregation pipeline operator
For the sake of illustration, let's assume that management wishes to be able to generate a report of lender names and addresses sorted by the lender's country, and then by the total amounts loaned, from largest to smallest. Lender information is found in the users collection, however, loan amount information is in loans; accordingly, the aggregation framework is needed.
Connecting two collections using $lookup
The first aggregation pipeline stage uses the $lookup pipeline operator to join the users and loans collections. As with other complex examples discussed in this book, we first model the query using the mongo shell. Here is how the query might appear:
db.users.aggregate([
{ $match : { "userType" : "lender" }},
{ $project : { "userKey" : 1, "businessName" : 1, "address" : 1 }},
{ $lookup : { "from" : "loans", "localField" : "userKey",
"foreignField" : "lenderKey", "as" : "loans" }},
{ $addFields : { "total" : { "$sum" : "$loans.loanInfo.principal" }}},
{ $sort : { "address.country" : 1, "total" : -1 }}
]);
Now let's break this down into stages:
| Stage |
Notes |
| $match |
Adds documents to the pipeline from the users collection whose userType is lender. |
| $project |
Strips out all fields in the pipeline other than userKey, businessName, and address. |
| $lookup |
Adds documents from the loans collection into a new array field called loans. |
| $addFields |
Adds a new field, total, which represents a sum of the principal amounts in the loans array. |
| $sort |
Sorts all documents in the pipeline first by country and then by total in descending order. |
We do not have space to show the entire result set, however, here is how a single document might appear after launching the query just shown:
{
"_id" : ObjectId("5d9d3d008b1c565ae0d2813e"),
"userKey" : "CASSWHEE6724",
"businessName" : "Illuminati Trust",
"address" : {
"streetAddress" : "5787 Red Mountain Boulevard",
"geoSpatial" : [ "103.7", "17.2167"]
# not all fields shown
},
"loans" : [ {
"_id" : ObjectId("5da5981b72d8437f7436c419"),
"borrowerKey" : "SUNNCHAM4815",
"lenderKey" : "CASSWHEE6724",
"loanInfo" : {
"principal" : NumberDecimal("12800.0000000000"),
# not all fields shown
} } ],
"total" : NumberDecimal("12800.0000000000")
}
Having successfully modeled the query using the mongo shell, it's time to formulate it into Python code.
Producing Python code from the JSON query
For the purposes of this illustration, we create a new Django app called maintenance located in /path/to/repo/www/chapter_10/maintenance. The logic in views.py, shown here, is extremely simple. The main work is done by the user domain service. The resulting iteration is then rendered in the totals.html template and injected into the main layout.html template.
As usual, the module starts with a set of import statements:
from django.template.loader import render_to_string
from django.http import HttpResponse
from config.config import Config
from db.mongodb.connection import Connection
from biglittle.domain.user import UserService
We then define a method, totals(), which runs the report and sends the results to the view template:
def totals(request) :
config = Config()
userSvc = UserService(config, 'User')
totals = userSvc.fetchTotalsByLender()
params = { 'totals' : totals }
main = render_to_string('totals.html', params)
home = render_to_string('layout.html', {'contents' : main})
return HttpResponse(home)
Likewise, because most of the work is passed on to the MongoDB aggregation framework, the new method, fetchTotalsByLender(), in the biglittle.domain.user.UserService class is also quite simple. Here is the code:
def fetchTotalsByLender(self) :
pipe = [
{ "$match" : { "userType" : "lender" }},
{ "$project" : { "userKey":1, "businessName":1, "address":1 }},
{ "$lookup" : { "from" : "loans", "localField" : "userKey",
"foreignField" : "lenderKey", "as" : "loans" }},
{ "$addFields" : {"total":{"$sum":"$loans.loanInfo.principal"}}},
{ "$sort" : { "address.country" : 1, "total" : -1 }}
]
return self.collection.aggregate(pipe)
Finally, the view template logic is shown next. Notice that we use the Django template language to loop through the list of totals. We use loop.counter0 to create a double-width table to conserve screen space:
<h2>Total Amount Lent By Lender</h2>
<hr>
<table border=1>
<tr><th>Business</th><th>Country</th><th>Total Lent</th>
<th>Business</th><th>Country</th><th>Total Lent</th></tr>
{% for doc in totals %}
{% if doc.total %}
{% if forloop.counter0|divisibleby:2 %}<tr>{% endif %}
<td class="td-left">{{ doc.businessName }}</td>
<td class="td-left">{{ doc.address.country }}</td>
<td class="td-right">{{ doc.total }}</td>
{% if not forloop.counter0|divisibleby:2 %}</tr>{% endif %}
{% endif %}
{% endfor %}
</table>
The output appears similar to the following screenshot:
To test the working code using the Docker-based test environment, enter this URL in the browser of your host computer: http://chap10.biglittle.local/maintenance/totals/
In the next section, we take a look at situations where you need to update documents across collections.
Performing secondary updates
In the case of BigLittle Micro Finance, when a borrower makes a loan payment, the status field for that payment needs to be updated to reflect payments being received. However, given our example document structure, we also need to update the amountDue and amountPaid fields in the user document. This is referred to as a secondary update.
This is a step easily overlooked when developing financial applications. What can often happen is that the total amount paid as recorded in the users collection could fall out of sync with the sum of the amount field values in the loans collection. Accordingly, it's not a bad idea when performing secondary updates to either provide an immediate double-check and note any discrepancies in a log, or alternatively, provide management with a discrepancies report allowing administrators to perform the double-check themselves. Let's first look at the process of accepting a loan payment in our sample scenario.
Accepting a loan payment
For the purposes of this illustration, we make the following assumptions:
- Loan payments are entered by Big Little Micro Finance Ltd. loan administrators.
- If the loan amount is greater then the amount due, the overpayment is added to an overpayment field in the loan document and dealt with later.
- If the loan amount is less than the amount due, the amount paid is added to an overpayment field in the loan document, and the payment is considered overdue.
- Each borrower can have only one outstanding loan at a time. When the loan has been paid off, we assume, for the purposes of this illustration, that the loan information is moved into a yet to be defined history collection.
In order to accept a loan payment, logic needs to be devised in the Django views.py module of the newly created maintenance app. In addition, we need to add the appropriate methods to the loan domain service for BigLittle. Finally, a form template needs to be created that lets a loan administrator choose the borrower and process a loan payment. Also, in the urls.py file found in /path/to/repo/www/chapter_10/maintenance, we define two routes: one that lets a loan admin choose the borrower, and another to accept the payment. The contents of this file are shown here:
from django.urls import path
from . import views
urlpatterns = [
path('', views.index, name='maintenance.index'),
path('totals/', views.totals, name='maintenance.totals'),
path('payments/', views.payments, name='maintenance.payments'),
]
Now we can examine the view logic.
Django view logic for choosing the borrower
The view logic for this illustration is found in /path/to/repo/www/chapter_10/maintenance/views.py. As with other classes, we first import the necessary Python and Django classes. In addition, we import the necessary BigLittle entity and domain service classes:
from django.template.loader import render_to_string
from django.http import HttpResponse
from config.config import Config
from db.mongodb.connection import Connection
from biglittle.domain.user import UserService
from biglittle.domain.loan import LoanService
from biglittle.entity.loan import Loan,LoanInfo,Payment
from biglittle.entity.user import User
In the index() method, the loan admin chooses the borrower whose payment is processed. We first initialize key variables:
def index(request) :
loan = Loan()
amtPaid = 0.00
amtDue = 0.00
message = ''
borrower = None
borrowerKey = None
borrowerName = ''
payments = []
overpayment = 0.00
After that, we initialize domain services and fetch a list of borrower keys and names:
config = Config()
userSvc = UserService(config, 'User')
borrowers = userSvc.fetchBorrowerKeysAndNames()
Finally, we render the results using the inner template, maintenance.html, subsequently injected into the layout:
params = {
'loan' : loan,
'payments' : payments,
'borrowers' : borrowers,
'borrowerName' : borrowerName,
'borrowerKey' : borrowerKey,
'amountDue' : amtDue,
'overpayment' : overpayment,
'message' : message
}
main = render_to_string('maintenance.html', params)
home = render_to_string('layout.html', {'contents' : main})
return HttpResponse(home)
Next, we look at the view logic for processing a payment.
Django view logic for payment processing
As with the index() method, we first initialize the same variables as, ultimately, we'll be sending results to the same template for rendering:
def payments(request) :
loan = Loan()
amtPaid = 0.00
amtDue = 0.00
message = ''
borrower = None
borrowerKey = None
borrowerName = ''
overpayment = 0.00
We then initialize payments as a list with a single blank Payment instance:
payment = Payment()
payments = [payment.populate()]
Next, in a similar manner to the index() method, we get instances of domain service classes representing users and loans. With the user domain service, we retrieve a list of borrower keys and names:
config = Config()
userSvc = UserService(config, 'User')
loanSvc = LoanService(config, 'Loan')
borrowers = userSvc.fetchBorrowerKeysAndNames()
We then check to see if the HTTP request method was POST. If so, we check for the existence of borrowerKey and amount_paid values:
if request.method == 'POST':
if 'borrowerKey' in request.POST :
borrowerKey = request.POST['borrowerKey']
if 'amount_paid' in request.POST :
amtPaid = float(request.POST['amount_paid'])
If we have a borrowerKey, it is used to retrieve borrower information. If valid, we proceed to retrieve loan information using the validated borrowerKey:
if borrowerKey :
borrower = userSvc.fetchUserByBorrowerKey(borrowerKey)
if borrower :
borrowerName = borrower.getFullName()
loan = loanSvc.fetchLoanByBorrowerKey(borrowerKey)
A valid loan instance is returned. If the amount paid by the borrower in this processing pass is greater than zero, we process the loan and retrieve the modified loan document:
if loan :
if amtPaid > 0 :
if loanSvc.processPayment(borrowerKey, amtPaid, loan) :
message = 'Payment processed'
loan = loanSvc.fetchLoanByBorrowerKey(borrowerKey)
else :
message = 'Problem processing payment'
Inside the block created by the if loan statement, we add logic to check to see if we have a valid loan document. We also extract a list of payments and the overpayment amount. Because Python and Django do not directly support the BSON Decimal128 format, we first convert all financial information to Decimal before rendering:
loan.convertBsonToDecimal()
payments = loan.getPayments()
loanInfo = loan.getLoanInfo()
amtDue = loanInfo.getMonthlyPayment()
overpayment = loan.get('overpayment')
We then present all this information to the view rendering process. The parameters are initialized, and a the sub document maintenance.html is rendered first. It is then injected into layout.html to produce the final view:
params = {
'loan' : loan,
'payments' : payments,
'borrowers' : borrowers,
'borrowerName' : borrowerName,
'borrowerKey' : borrowerKey,
'amountDue' : amtDue,
'overpayment' : overpayment,
'message' : message
}
main = render_to_string('maintenance.html', params)
home = render_to_string('layout.html', {'contents' : main})
return HttpResponse(home)
Let's now have a look at the domain service methods referenced in the view logic.
Payment processing domain service methods
fetchBorrowerKeysAndNames() is the first method referenced in the view logic. This method is found in the biglittle.domain.user.UserService class. Its purpose is to return a list of documents containing the values of the _id, userKey, and name fields. This list is subsequently used in the view template to provide a drop-down list from which the loan administrator can choose. Here is that method:
def fetchBorrowerKeysAndNames(self) :
keysAndNames = []
lookup = self.collection.find({'userType':'borrower'})
for borrower in lookup :
doc = {
'id' : borrower.getId(),
'key' : borrower.getKey(),
'name' : borrower.getFullName()
}
keysAndNames.append(doc)
return keysAndNames
Another method used during payment acceptance is fetchUserByBorrowerKey(), found in the same class. Its purpose is to return a biglittle.entity.user.User instance from which the user's name is extracted. It is a simple lookup method, shown here:
def fetchUserByBorrowerKey(self, borrowerKey) :
return self.collection.find_one({ \
"userKey" : borrowerKey, "userType" : "borrower" })
In a similar manner, once the borrower has been identified, and the loan payment amount has been recorded, the fetchLoanByBorrowerKey() method from the LoanService class, found in the biglittle.domain.loan.py module is called. Again, it's a simple lookup, this time by the borrower key. The outstanding feature of this method is that we first convert all financial information from MongoDB NumberDecimal (BSON Decimal128) to Python decimal.Decimal, for ease of processing. Here is that method:
def fetchLoanByBorrowerKey(self, borrowerKey) :
loan = self.collection.find_one({"borrowerKey":borrowerKey})
loan.convertBsonToDecimal()
return loan
As you may recall from our list of assumptions discussed at the start of this subsection, we assume any given borrower can have at most one outstanding loan. Thus, if we do a lookup by borrower key, we are assured of retrieving a unique loan document.
Finally, we have the processPayment() method, located in the LoanService class, which applies the payment, updates the status, and records any overpayment information. As with most complex methods, we start by initializing key variables. We also split out loanInfo from the loan document in order to retrieve the expected payment amount due:
def processPayment(self, borrowerKey, amtPaid, loan) :
from decimal import Decimal
from bson.decimal128 import Decimal128
config = Config()
result = False
overpayment = 0.00
loanInfo = loan.getLoanInfo()
amtDue = loanInfo.getMonthlyPayment()
Next, we check the data type of amtPaid and convert it to decimal.Decimal if needed:
if not isinstance(amtPaid, Decimal) :
amtPaid = Decimal(amtPaid)
The heart of this method loops through the payments list, looking for the first document in the list for which the amountPaid field is zero. If found, we check to see if the amount paid is less than the amount due. If so, we apply that amount to overpayment, but do not otherwise touch the loan payment list:
for doc in loan['payments'] :
if doc['amountPaid'] == 0 :
if amtPaid < amtDue :
overpayment = amtPaid
Still in the loop, if the amount paid is equal to or greater than the amount due, we set the amountPaid field in the payments list equal to the amount due. Any excess amount is added to the overpayment field. status is updated to received, and the date the payment was accepted is stored in recvDate. We also break out of the loop as there is no need for further processing:
else :
overpayment = amtPaid - amtDue
doc['amountPaid'] = doc['amountDue']
doc['status'] = 'received'
from time import gmtime, strftime
now = strftime('%Y-%m-%d', gmtime())
doc['recvdate'] = now
break
Now out of the payments loop, we update the overpayment field, adding any excess payments:
currentOver = loan.get('overpayment')
loan.set('overpayment', currentOver + overpayment)
Lastly, we convert all decimal values to NumberDecimal, set the filter to borrowerKey, and perform an update:
loan.convertDecimalToBson()
filt = { "borrowerKey" : borrowerKey }
result = self.collection.replace_one(filt,loan)
return result
In this case, we perform the update using the replace_one() method. The reason this method is much faster in this case is because we are working at the entity level: we have a complete document ready to hand. If we were only updating one or a few fields, the update_one() method would be preferred.
Now, let's have a quick look at the loan maintenance template.
Loan payment view template
The loan maintenance view template is located at /path/to/repo/www/chapter_10/templates/maintenance.html. The first item of interest is how to present the drop-down list of borrowers. The view calls a method, fetchBorrowerKeysAndNames(), from the UserService class, subsequently sent to the template. We are then able to build the HTML SELECT element using the Django template language, as shown here:
<select name="borrowerKey">
{% if borrowerKey and borrowerName %}
<option value="{{ borrowerKey }}">{{ borrowerName }}</option>
{% endif %}
{% for user in borrowers %}
<option value="{{ user.key }}">{{ user.name }}</option>
{% endfor %}
</select>
We also provide the complete payment schedule for the borrower in the same template. This is so that the loan administrator can see which payments have been received. The view extracts the payments list from the loan document and sends it to the template, where we again use the Django template language to provide the list:
<h3>Payment Schedule</h3>
{% if payments %}
<table>
<tr><th>Amount Paid</th><th>Due Date</th><th>Status</th></tr>
{% for doc in payments %}
<tr><td>{{ doc.amountPaid|floatformat:2 }}</td>
<td>{{ doc.dueDate }}</td><td>{{ doc.status }}</td></tr>
{% endfor %}
</table>
{% endif %}
Now that you have an idea of how payment processing works, it's time to look at the secondary updates that need to occur.
Defining code to process secondary updates
In order to avoid the overhead involved in creating normalized data structures with lots of foreign keys and relationships, MongoDB DevOps may choose to store redundant information across collections. The main danger here is that if a change occurs in one collection, you, the developer, may need to ensure that a secondary update occurs in the collection with related information.
Accordingly, when a loan payment is accepted, the following secondary updates need to occur in the users collection:
- Add the amount paid to the amountPaid field for that borrower.
- Subtract the amount paid from the amountDue field.
Before we get started on the implementation, we need to first digress and discuss using listeners and events.
The publish/subscribe design pattern
What is now known as the Publish-Subscribe (or affectionately PubSub) software design pattern was originally proposed by Birman and Joseph in a paper entitled Exploiting Virtual Synchrony in Distributed Systems, presented to the 11th Association for Computing Machinery (ACM) symposium on operating system principles. To boil down a rather complex algorithm into simple terms, a publisher is a software class that sends a message before or after an event of significance takes place. A common example of such an event would be a database modification. A subscriber is a software class that listens to messages produced by its associated publisher. The subscriber examines the message, and may or may not take action, at its own determination. In our example scenario, we create a publisher class that sends a message to subscribers when a loan payment is received. The subscriber performs the secondary update on the relevant borrower document.
Beginning with MongoDB 4.0, the concept of multi-document ACID transactions were introduced. The PubSub concept can potentially introduce a point of failure for the overall transaction. If the two documents must be updated, they should be wrapped in a transaction.
At this point, you might ask yourself: why not just update the borrower document right away? The answer is that such an operation would violate an important software principal known as separation of concerns. Each software class and each method within that class should have a single logical focus. In our example, it's the job of the LoanService class to service the loans collection, and no other.
If we start to have one domain service interfere with the responsibilities of another domain service, soon we end up with what eventually came to be known as spaghetti code, made famous by a Dilbert cartoon published on June 10, 1994.
Important terms for you to be aware of include the following:
- Topic: A label used to represent an event of significance.
- Listener: Callable logic that performs an action when sent a message. A function, class method, or class defining __call__() is considered callable.
- Subscribe: The process of associating a listener with a topic.
- Message: A call from the publisher that includes the topic and any associated parameters. The parameters are sent to all listeners subscribing to this topic.
Rather than having to invent a custom Publish-Subscribe class, in this illustration, we take advantage of a very simple PubSub implementation released in 2015, called Python Simple PubSub. It is a simple matter to define a Publisher class that provides a wrapper for PubSub. The code can be found at /path/to/repo/chapters/10/src/biglittle/events/publisher.py. The main thing to note here is that the pubsub.pub.sendMessage() method takes its second argument in **kwargs format. Accordingly, you need to be careful to always use whatever key you define. In this example, the name of the key is arg:
import pubsub
class Publisher :
def attach(self, topic, listener) :
pubsub.subscribe(listener, topic)
def trigger(self, topic, obj) :
pubsub.sendMessage(topic, arg=obj)
Please refer to the following links for more information:
We'll look at creating listeners for loan events next.
Defining listeners for loan events
Since the secondary update needs to occur on the users collection, it's appropriate to define the listener in the UserService class already defined. We add a new method, updateBorrowerListener(), which is later subscribed as a listener. Note that the argument to the listener is arg, in order to match that defined in our Publisher wrapper class described in the section just before.
We first import the Python classes to convert between Decimal and Decimal128:
def updateBorrowerListener(self, arg) :
from decimal import Decimal
from bson.decimal128 import Decimal128
We then initialize two sets of two values representing amounts due, and amounts paid, as defined by information from the users collection and also the loans collection:
loan = arg['loan']
amtPaid = arg['amtPaid']
amtDueFromLoan = Decimal(0.00)
amtPaidFromLoan = Decimal(0.00)
amtDueFromUser = Decimal(0.00)
amtPaidFromUser = Decimal(0.00)
Next, we retrieve a User entity class instance based on the borrower key stored in the loan entity. If not found, an entry is made in the discrepancy log:
config = Config()
log_fh = open(config.getConfig('discrepancy_log'), 'a')
borrowerKey = loan.get('borrowerKey')
borrower = self.fetchUserByBorrowerKey(borrowerKey)
if not borrower :
message = now + ' : User ' + borrowerKey + ' not found'
log_fh.write(message + "\n")
We can now retrieve and update the information on the amount due and paid as recorded in the User document:
else :
amtDueFromUser = borrower.get('amountDue').to_decimal()
amtPaidFromUser = borrower.get('amountPaid').to_decimal()
amtDueFromUser -= amtPaid
amtPaidFromUser += amtPaid
The secondary update is then performed using the pymongo update_one() method:
filt = {'userKey' : borrower.getKey()}
updateDoc = { '$set' : {
'amountDue' : Decimal128(str(amtDueFromUser)),
'amountPaid' : Decimal128(str(amtPaidFromUser))
}}
self.collection.update_one(filt, updateDoc)
Let's now look at how the publisher notifies the subscriber.
Event notification
Setting up the publisher to notify the subscriber involves two steps:
- Associating the listener with the topic
- Sending a message to the subscriber
The first step is achieved in the views.py module of the Django maintenance app. In the accept() method, the following is added immediately after variable initialization:
from biglittle.events.publisher import Publisher
publisher = Publisher()
config = Config()
userSvc = UserService(config, 'User', publisher)
loanSvc = LoanService(config, 'Loan', publisher)
publisher.attach(publisher.EVENT_LOAN_UPDATE_BORROWER, \
userSvc.updateBorrowerListener)
In the biglittle.domain.base.Base class, we modify the __init__() method to accept the publisher as an argument:
class Base :
def __init__(self, config, result_class = None, publisher = None) :
self.publisher = publisher
# other logic in this method now shown
In the processPayment() method of the biglittle.domain.loan.LoanService class (described earlier), we add a call to the publisher to notify (that is, send a message to) the subscriber. Note that we convert the loan entity back to Decimal for processing:
filt = { 'borrowerKey' : borrowerKey }
result = self.collection.replace_one(filt,loan)
if result :
loan.convertBsonToDecimal()
arg = { 'loan' : loan, 'amtPaid' : amtPaid }
self.publisher.trigger( \
self.publisher.EVENT_LOAN_UPDATE_BORROWER, arg)
return result
Next, we look at common problems encountered when working with documents across collections.
Avoiding cross-collection problems
Many of the show stoppers that are prevalent in the legacy relational database management system (RDBMS) arena are simply not an issue when using MongoDB due to differences in architecture. There are a few major issues in the area of cross-collection processing of which you need to be aware:
- Ensuring uniqueness
- Orphaned documents
- Value synchronization
Let's start this discussion by examining ways to ensure uniqueness when using MongoDB.
Ensuring uniqueness
The first question that naturally comes to mind is: what is uniqueness? In a traditional RDBMS database design, you must provide a mechanism whereby each database table row is different from every other row. If you had two RDMBS table rows, X and Y, which were identical, it would be impossible to guarantee access to X or to Y. Further, it would be a pointless waste of space to maintain duplicate data in such a manner. Accordingly, in the RDBMS world, when designing your database, you need to identify one or more database columns as part of the primary key.
Using ObjectId as a unique key
In the case of MongoDB, the equivalent of an RDBMS primary key would be the autogenerated ObjectId. You can reference this value via the alias _id. Unlike RDBMS sequences or auto-increment fields, however, the MongoDB _id field includes both sequential as well as random elements. It consists of 12 bytes broken down as follows:
- A 4-byte value representing the number of seconds since midnight on January 1, 1970 (Unix epoch)
- A 5-byte random value
- A 3-byte counter (seeded by a random value)
The _id field could potentially be used when conducting cross-collection operations (see the discussion on DBRefs earlier in this chapter). There is a method, ObjectId.valueOf(), that returns a hexadecimal string of 24 characters. However, within your application code, or when performing an operation using the aggregation framework, as this field is actually an object (for example, ObjectId), you would need to perform further conversions before being able to use it directly. Accordingly, it is highly recommended that within your application you create a custom unique and meaningful key that can be used to cross-reference documents between collections.
Defining a custom unique key
There are no specific rules, regulations, nor protocols that apply to define a unique custom key, however, there are two criteria that stand out. The key you choose should be the following:
Usually, in order to achieve uniqueness, some sort of random strategy needs to be employed. Using a value derived from the year, month, day, hours, minutes, and seconds might be of use. You can also use bits from other fields within the document to build a unique key that is meaningful.
As an example, in the BigLittle Micro Finance Ltd. scenario, each borrower or lender has information stored in the users collection. Each document, in addition to the automatically generated ObjectId, has a unique userKey field. As you can see from the following screenshot, the key consists of the first 4 letters of first and last names with a random 4-digit number:
Note that the ObjectId field does have a certain sequential aspect as these values were imported in bulk, thus the initial Unix epoch is almost the same for each one.
Now that you have an idea of how uniqueness might be defined, let's have a look at how it can be enforced.
Enforcing uniqueness
Once you have settled on an algorithm for choosing a unique key, is there a way to guarantee uniqueness? One very useful and effective solution is to create a unique index on the key field. This has two benefits:
- MongoDB prevents your application from adding a document with a duplicate key.
- Searches involving this key becomes faster and more efficient.
The general rule for creating indexes in MongoDB is to create indexes on fields frequently used in queries. Do not go overboard on creating indexes, however, as they introduce extra overhead to maintain them.
Using the mongo shell, you can create an index on a field as follows:
db.<name_of_collection>.createIndex(keys, options);
Let's jump right into an example involving the users collection and the userKey field mentioned earlier in this subsection. Here is the query that creates a unique index on the userKey field in ascending order:
db.users.createIndex( { "userKey" : 1 }, { "unique" : true } );
If we then attempt to insert a document with a duplicate userKey, MongoDB refuses to perform the operation, thus enforcing uniqueness. Here is a screenshot of this attempt:
Next, we'll look at the subject of orphans.
Avoiding creating orphans across collections
The first question to be addressed is: what is an orphan? In the context of RDBMS, a database table row becomes an orphan when its parent relation is deleted. As an example, suppose you have one table for customers and another table for phone numbers. Each customer can have multiple phone numbers. In the phone number table, you would have a foreign key that refers back to the customer table. If you then delete a given customer, all the related rows in the phone numbers table become orphans.
As we mentioned before, MongoDB is not relational, so such a situation would not arise. A lot depends on good MongoDB database design. Let's look at two approaches that avoid creating orphans: embedded arrays and documents.
Using embedded arrays to avoid orphans
Using the example in the previous subsection, instead of creating a separate table for phone numbers, in MongoDB, simply include the phone numbers as an embedded array. Here is an example from the users collection. Note how we store multiple email addresses and phone numbers as embedded arrays within the otherContact field:
db.users.findOne({},{"userKey":1,"otherContact":1,"_id":0});
{
"userKey" : "CHARROSS3456",
"otherContact" : {
"emails" : [
"cross100@swisscom.com",
"cross100@megafon.com"
],
"phoneNumbers" : [
"100-688-6884",
"100-431-1074"
]
}
Thus, when a user document is deleted, all of the related information is deleted at the same time, completely avoiding the issue of orphaned information.
Using embedded documents to avoid orphans
In a similar manner, as discussed earlier in this chapter, instead of using DBRefs, or unique keys to reference documents across collections, you can simply embed the entire document, or a subset of that document, where the information is needed. Going back to the Book Someplace, Inc. scenario as an example, a Booking document includes fragments of both Customer and Property. To refresh your memory, have a look at the Booking, CustBooking, and PropBooking document structures:
Booking = {
"bookingKey" : <string>,
"customer" : <CustBooking>,
"property" : <PropBooking>,
"bookingInfo" : <BookingInfo>,
"rooms" : [<RoomBooking>,<RoomBooking>,etc.],
"totalPrice" : <float>
}
CustBooking = {
"customerKey" : <string>,
"customerName" : <Name>,
"customerAddr" : <Location>,
"customerContact" : <Contact>,
"custParty" : [<CustParty>,<CustParty>,etc.]
}
PropBooking = {
"propertyKey" : <string>,
"propertyName" : <string>,
"propertyAddr" : <Location>
"propertyContact" : <Contact>
}
Full definitions of these document structures can be found at /path/to/repo/chapters/07/document_structure_definitions.js.
This approach has its own disadvantages. If a customer document representing a customer who has made a booking is deleted, no orphaned documents are created. However, we now have a strange situation where the booking document contains a value for customerKey, but the corresponding customer document no longer exists! So, in a certain sense, although there are no orphaned documents, we now have an orphaned field. One way this situation can be avoided is to move deleted documents into an archive collection. You could then perform a secondary update on all booking documents associated with the removed customer, adding a new field: archived.
We'll now look at considerations for keeping values across collections properly synchronized.
Keeping values properly synchronized
Continuing the discussion from the previous subsection, we once again look at the database structure for Book Someplace, Inc. Another issue with the embedded document approach is what happens when the original customer document is updated? Now you have two places where customer information is stored: once in the customers collection, another in the bookings collection. The solution to this problem depends on the company's philosophy with regard to bookings. On the one hand, you could argue that at the time of the booking the customer information was correct, and must be maintained as is for historic reasons. On the other hand, you could argue that the latest information should always be present in bookings regardless of the situation that existed at the time of the booking. In the latter case, following a customer update, you would need to perform a series of secondary updates on all relevant bookings.
Returning to the BigLittle Micro Finance Ltd. scenario, the situation gets slightly more complicated because financial amounts are involved. Accordingly, it's highly important that your programming code includes a double-check for accuracy. In this case, we need to add programming logic that produces a sum of the amountDue and amountPaid fields in the loan.payments list and compare it with the same fields in the associated user document. If the information does not match, a discrepancies log file entry must be made. What would normally follow would be a tedious manual process of going through all loan payments, beyond the scope of this book.
In order to implement this secondary accuracy check, we add additional logic to the updateBorrowerListener() method in the UserService class already defined. We draw upon our config.Config() class to provide the name and location of the discrepancies log file. In addition, we pull today's date for logging purposes. This logic would then appear immediately after variables are initialized:
def updateBorrowerListener(self, arg) :
# imports (not shown)
# init vars (not shown)
config = Config()
log_fh = open(config.getConfig('discrepancy_log'), 'a')
from time import gmtime, strftime
now = strftime('%Y-%m-%d', gmtime())
# other code already shown earlier in this chapter
Subsequently, just after we perform the update, we add logic to double-check the accuracy of the updated information. First, we calculate the amount due and paid from the list of payments:
# earlier code not shown
self.collection.update_one(filt, updateDoc)
for doc in loan.getPayments() :
amtDueFromLoan += doc['amountDue']
amtPaidFromLoan += doc['amountPaid']
If the amount due or paid does not match, discrepancy log entries are made. Also, of course, it's good practice to close the log file, which ends this method:
if amtDueFromUser != amtDueFromLoan :
log_fh.write(now + ':Amount due discrepancy ' + \
borrowerKey+"\n")
log_fh.write('--"users" collection: ' + \
str(amtDueFromUser)+"\n")
log_fh.write('--"loans" collection: ' + \
str(amtDueFromLoan)+"\n")
if amtPaidFromUser != amtPaidFromLoan :
log_fh.write(now + ' :Amount paid discrepancy ' + \
borroweKey+"\n")
log_fh.write('--"users" collection: ' + \
str(amtPaidFromUser)+"\n")
log_fh.write('--"loans" collection: ' + \
str(amtPaidFromLoan)+"\n")
log_fh.close()
In many cases, you can have your app perform updates to make sure the information is in sync in the case of a discrepancy. In a financial application, however, this is not recommended as information that is out of sync is a warning sign that either there is a logic error somewhere in the application (that is, secondary updates are not being performed properly), or an indicator that amounts are not being reported correctly, in which case manual investigation and intervention are needed.
In the next section, we introduce a feature related to storing images in the database called GridFS.
Uploading files into GridFS
GridFS (https://docs.mongodb.com/manual/core/gridfs/#gridfs) is a technology included with MongoDB that allows you to store files directly in the database. GridFS is a virtual filesystem mainly designed to handle files larger than 16 MB in size. Each document is broken down into chunks of 255 KB except for the last chunk, which could be less than 255 KB. By default, two collections assigned to an arbitrary bucket are used. The chunks collection stores the actual contents of the file. The files collection stores file metadata. Within your database, you can designate as many buckets as needed.
Let's start by discussing why you might want to incorporate GridFS into your applications.
Why use GridFS?
One big advantage is that once stored, MongoDB offers the same advantages to large files as it does to any other type of data: replication to ensure high availability, sharding to facilitate scalability, and the ability to access a file regardless of the host operating system. Accordingly, if the server's operating system places a limit on the number of files in a single directory, you can use GridFS to store the files and get around OS limitations.
Another big advantage is that due to the way GridFS breaks files into 255 KB chunks, reading extremely large files is not as memory intensive as when you try to directly access the same file stored on the server's filesystem. Finally, using GridFS is a great way to keep files synchronized across servers.
It is recommended that you do not use GridFS if all the files you need to store are less than 16 MB in size. If this is the case, and you still want to take advantage of using MongoDB, simply store the file directly in a normal MongoDB document. The maximum size of a BSON document is 16 MB, so if all your files are smaller, using GridFS is a waste of resources.
Also, it is not recommended to use GridFS if the entire document needs to be updated atomically. As an example, if you are maintaining legal documents, each revision needs to be stored as a separate document with changes clearly marked. In this case, either store the document using the server's filesystem or clone the document and store clearly marked revisions.
How to use GridFS from the command line
GridFS is available from the command line via the mongofiles utility. The generic syntax is as follows:
mongofiles <options> <commands> <filename>
Most of the options are identical to those of the mongo shell and include command-line flags to identify a URI style connection string, host, port, and authentication information. You can also specify the IP version (for example, --ipv6) and SSL/TLS secure connectivity options.
GridFS command-line options
Some options are not relevant to the database connection. These additional options, peculiar to GridFS, are summarized here:
| Option |
Notes |
| --local=<filename> |
Use this option if you want the filename recorded in GridFS to be different from the local filename on the server's OS. In place of <filename>, enter the actual name of the file on the local server filesystem. At the end of the entire command string, enter the filename as you wish it to appear in GridFS. |
| --replace |
When you use the put command (described in the next subsection) to store a file, existing files of the same name are not overwritten. Instead, a new entry is created. If you want the currently stored file to be completely replaced, use this option. |
| --prefix=<string> |
Use this option to specify a different bucket. The default bucket is fs. Think of a bucket as being like a subdirectory. |
| --db=<database> |
This option specifies which database to use for GridFS storage. |
| --uri="<string>" |
Just as with the mongo shell, you can consolidate command-line options into a single URI-style string. |
In addition to options, the mongofiles utility has commands, discussed next.
GridFS commands
In the following table is a summary of the more important commands used with the mongofiles utility:
| Command |
Notes |
| list <filter> |
This command operates much like the Linux ls or the Windows dir commands. If you specify one or more characters in the filter, you get a list of filenames starting with those characters.
Using Linux as an example, this command is similar to ls xyz* (returns a list of files starting with xyz). |
| search <filter> |
This command is the same as list except that a list of filenames matching any part of filter are displayed.
Using Linux as an example, this command is similar to ls *xyz* (returns a list of filenames containing xyz). |
| put <filename> |
Copies a file from the local filesystem to GridFS. By default, GridFS uses the local filename.
If you need to have the GridFS filename be different, use the --local option.
Use the --replace option to overwrite an existing GridFS of the same name. |
| get <filename> |
The opposite of put. Copies a file from GridFS into the local filesystem. |
| get_id "<_id>" |
The same as get except that the file to be copied is identified by its _id field. |
| delete <filename> |
Erases a file from GridFS storage. |
| delete_id "<_id>" |
The same as delete except that the file to be deleted from GridFS is identified by its _id field. |
Now let's look at a usage example.
Command-line usage examples
In this example, we copy a number of large files drawn from the open source GeoNames project representing world city data and postal codes. The data provided by the project, authored by Marc Wick, is licensed under a Creative Commons Attributions v4.0 license. In the book repository, there is a script, /path/to/repo/sample_data/geonames_downloads.sh, that imports and unzips two large GeoNames project sample data files. Here is that download script:
#!/bin/bash
export STATS_URL="https://download.geonames.org/export/dump/allCountries.zip"
export ZIP_URL="https://download.geonames.org/export/zip/allCountries.zip"
wget -O /tmp/stats.zip $STATS_URL
wget -O /tmp/postcodes.zip $ZIP_URL
unzip /tmp/stats.zip -d /tmp
mv /tmp/allCountries.txt /tmp/allCountriesStats.txt
unzip /tmp/postcodes.zip -d /tmp
mv /tmp/allCountries.txt /tmp/allCountriesPostcodes.txt
rm /tmp/*.zip
ls -l /tmp/all*
Here is a list of the files to be copied into GridFS:
As you can see, most files are well over 16 MB in size. We use the put command to store the files in GridFS in the biglittle database on localhost. Here is an example using the --local option. In this example, we want the filename in GridFS to appear as /postalCodes/world_postal_codes.txt. We can then use list to view results (search could also be used):
From the mongo shell, you can see the new fs collection. fs.chunks contains the actual contents of the files. fs.files contains file metadata, as shown here:
Next, let's look at how GridFS is supported by the PyMongo driver.
PyMongo GridFS support
The PyMongo driver provides a package, gridfs, that provides support for GridFS from a programming context. The core class is gridfs.GridFS. There are a number of supporting packages, however, in order to retain our focus on the essentials, we concentrate here only on the core class. GridFS class methods mirror the commands discussed in the previous subsection. The following table provides a summary of the more important methods:
| gridfs.GridFS.method |
Notes |
| find(*args, **kwargs) |
This method operates in a similar manner to pymongo.collection.find().
*args is a JSON document with key/value pairs. The key can be anything in the fs.files collection. The most common key is filename.
**kwargs is a series of key-value keypairs including the keys filter, skip, limit, no_cursor_timeout and sort. find_one() takes the same arguments except only a single file is returned. |
| list() |
Operates much like the mongofiles list command. It returns a list of of files in GridFS. |
| put(data, **kwargs) |
Operates like the mongofiles put command. It writes a file to GridFS.
Data can take the form of a bytes data type or an object (for example, a file handle) that provides a read() method. **kwargs can be any of the options mentioned in the documentation for gridfs.grid_file.GridIn. |
| get(file_id) |
Much like the mongofiles get command, returns the file based on its _id field value. The return value is the gridfs.grid_file.GridOut instance (much like a file handle). The GridOut object provides methods that allow you to get metadata on the file. Of special interest is its read (NNN) method, giving access to the file contents, NNN number of bytes at a time. There is also a readLine() method, which reads a single line, and readChunk(), which reads out the file in chunks (preset to 255KB). |
| exists(file_id) |
Returns a Boolean set to True if the given file _id field value exists; False otherwise. |
| delete(file_id) |
Deletes a file with a given value of the _id field. |
Please refer to the following links for more information:
Now it's time to have a look at a practical example using this class in our sample scenario.
Example using GridFS to store loan documents
We need a way to upload loan documents, so we add a file upload HTML element to the maintenance.html template. We also add a loop that displays the currently uploaded documents for this borrower:
<tr><th class="th-right">Loan Documents Stored</th>
<td class="td-left">
{% for name in loan_docs %} <br>{{ name }} {% endfor %}
</td>
</tr>
<tr>
<th class="th-right">Upload Loan Document</th>
<td class="td-left"><input type="file" name="loan_doc" /></td>
</tr>
In the view logic (/path/to/repo/www/chapter_10/maintenance/views.py), we add the following to the payments() method. If there is a borrowerKey value available, we import GridFS and os, and retrieve borrower information (discussed earlier):
if borrowerKey :
from gridfs import GridFS
import os
borrower = userSvc.fetchUserByBorrowerKey(borrowerKey)
We then check to see whether any files have been uploaded. If so, we prepend the value of the borrowerKey property and store it in GridFS:
grid = GridFS(loanSvc.getDatabase())
if request.FILES and 'loan_doc' in request.FILES :
fn = request.FILES['loan_doc']
newFn = borrowerKey + '/' + os.path.basename(fn.name)
grid.put(fn, filename=newFn)
message = 'File uploaded'
We then get a list of only filenames, subsequently sent to the view template for rendering:
loan_docs = []
temp = grid.list()
for name in temp :
if name.find(borrowerKey) >= 0 :
loan_docs.append(os.path.basename(name))
Now that you have an idea of how to use GridFS from both the command line and from a program script, it's time to jump to the chapter summary.
Summary
In this chapter, you learned about the MongoDB NumberDecimal class, based upon the BSON Decimal128 class, used to store financial numbers with high precision. You also learned that not all programming languages provide direct support for this class, which means a conversion might be necessary in order to perform processing.
Next, you learned how to handle database operations across collections. Several approaches were covered, including building block, embedded document, database reference, and DBRef approaches. You also learned how to produce a single iteration of documents across collections using the $lookup aggregation pipeline operator. After this, you learned about secondary updates and where they might be performed, as well as common pitfalls when working with documents across collections. These include problems associated with ensuring uniqueness, orphaned documents or fields, and keeping values synchronized.
Finally, at the end of this chapter, you learned about GridFS, a technology provided by MongoDB that lets you store large documents directly in the database. You learned that GridFS files can be accessed using the mongofiles command from the command line, or via a programming driver. You then learned how to use the gridfs.GridFS class provided by the pymongo driver to store and then access files.
In the next chapter, continuing with the BigLittle Micro Finance Ltd. scenario, you'll learn how to secure connections to the database at both the user level as well as at the SSL/TLS level.
