Slide 1
Slide 1 text
MongoDB, e-commerce, and
transactions
with a side of RDBMS
Slide 2
Slide 2 text
My name is
Steve Francia
@spf13
Slide 3
Slide 3 text
• 15+ years building e-commerce
• Long time open source contributor
• Hacker, father, husband, skate punk
• Chief Solutions Architect @ 10gen
Slide 4
Slide 4 text
Before 10gen I worked
for
http://opensky.com
Slide 5
Slide 5 text
OpenSky was the first
e-commerce site built
on MongoDB
... also the first e-commerce site built on Symfony2
Slide 6
Slide 6 text
Why NoSQL for
e-commerce?
Using the right solution for each situation
Slide 7
Slide 7 text
Data dilemma of
e-commerce
Pick One
Slide 8
Slide 8 text
Data dilemma of
e-commerce
• Stick to one vertical (Sane schema)
• Flexibility (Insane schema)
Pick One
Slide 9
Slide 9 text
Sane schema
Slide 10
Slide 10 text
Sane schema
• Works ... for a while
• Fine for a few types of products
• Not possible when more product types
introduced
Slide 11
Slide 11 text
Let’s Use an Example
Slide 12
Slide 12 text
Let’s Use an Example
How about we start with books
Slide 13
Slide 13 text
Book Product Schema
General Product
attributes
Book Specific
attributes
Product {
id:
sku:
product dimensions:
shipping weight:
MSRP:
price:
description:
...
author: Orson Scott Card
title: Enders Game
binding: Hardcover
publication date: July 15, 1994
publisher name: Tor Science Fiction
number of pages: 352
ISBN: 0812550706
language: English
...
Slide 14
Slide 14 text
Seems simple enough
Slide 15
Slide 15 text
Seems simple enough
What happens when we add another vertical...
say music albums
Slide 16
Slide 16 text
Album Product Schema
General Product
attributes stay the
same
Album Specific
attributes are
different
Product {
id:
sku:
product dimensions:
shipping weight:
MSRP:
price:
description:
...
artist: MxPx
title: Panic
release date: June 7, 2005
label: Side One Dummy
track listing: [ The Darkest ...
language: English
format: CD
...
Slide 17
Slide 17 text
Okay, it’s getting hairy but
is still manageable, right?
Slide 18
Slide 18 text
Okay, it’s getting hairy but
is still manageable, right?
Now the business want to sell jeans
Slide 19
Slide 19 text
Jeans Product Schema
General Product
attributes stay the
same
Jeans specific
attributes are totally
different ... and not
consistent across
brands & make
Product {
id:
sku:
product dimensions:
shipping weight:
MSRP:
price:
description:
...
brand: Lucky
gender: Mens
make: Vintage
style: Straight Cut
length: 34
width: 34
color: Hipster
material: Cotten Blend
...
Slide 20
Slide 20 text
Now we’re screwed
Slide 21
Slide 21 text
Now we’re screwed
Slide 22
Slide 22 text
We need a flexible
schema in RDBMS
Slide 23
Slide 23 text
We need a flexible
schema in RDBMS
We got this ... right?
Slide 24
Slide 24 text
Many approaches
dealing with unknown
unknowns in RDBMS
Slide 25
Slide 25 text
Many approaches
dealing with unknown
unknowns in RDBMS
None work well
Slide 26
Slide 26 text
as popularized by Magento
“For purposes of flexibility, the Magneto database heavily utilizes
an Entity-Attribute-Value (EAV) data model.
As is often the case, the cost of flexibility is complexity -
Magento is no exception.
The process of manipulating data in Magento is often more
“involved” than that typically experienced using traditional
relational tables.”
- Varien
EAV
Slide 27
Slide 27 text
EAV
• Crazy SQL queries
• Hundreds of joins in a query...
or
• Hundreds of queries joined in
the application
• No database enforced integrity
Slide 28
Slide 28 text
Did I say crazy SQL
(this is a single query)
Slide 29
Slide 29 text
Did I say crazy SQL
(this is a single query)
You may have trouble reading this in the back
Slide 30
Slide 30 text
Selecting a single product
Slide 31
Slide 31 text
Single Table Inheritance
(insanely wide tables)
• No data integrity enforcement
• Only can use FK for common
elements
• Very wasteful (but disk is cheap!)
• Can’t effectively index
Slide 32
Slide 32 text
Generic Columns
• No data integrity enforcement
• No data type enforcement
• Only can use FK for common
elements
• Wasteful (but disk is cheap!)
• Can’t index
Slide 33
Slide 33 text
Serialized in Blob
• Not searchable
• No integrity
• All the disadvantages of a document
store, but none of the advantages
• Never should be used
• One exception is Oracle XML
which operates similar to a
document store
Slide 34
Slide 34 text
Concrete Table Inheritance
(a table for each product attribute set)
• Allows for data integrity
• Querying across attribute
sets quite hard to do (lots of
joins, OR statements and full
table scanning)
• New table needs to be
created for each new
attribute set
Slide 35
Slide 35 text
Class table inheritance
(single product table,
each attribute set in own table)
• Likely best solution within the
constraint of SQL
• Supports data type enforcement
• No data integrity enforcement
• Easy querying across categories (for
browse pages) since common data
in single table
• Every set needs a new table
• Requires a ton of forsight, as
changes are very complicated
Slide 36
Slide 36 text
MongoDB to the
Rescue
Slide 37
Slide 37 text
MongoDB to the
Rescue
• Flexible (and sane) Schema
Slide 38
Slide 38 text
MongoDB to the
Rescue
• Flexible (and sane) Schema
• Easily searchable
Slide 39
Slide 39 text
MongoDB to the
Rescue
• Flexible (and sane) Schema
• Easily searchable
• Easily accessible
Slide 40
Slide 40 text
MongoDB to the
Rescue
• Flexible (and sane) Schema
• Easily searchable
• Easily accessible
• Fast
Slide 41
Slide 41 text
Flexible schema
Slide 42
Slide 42 text
{
sku: "00e8da9c",
type: "Audio Album",
title: "Hoss",
description: "by Lagwagon",
asin: "B0000007QG",
shipping: {
weight: 6,
dimensions: {
width: 10,
height: 10,
depth: 1
},
},
pricing: {
list: 1000,
retail: 800,
savings: 200,
pct_savings: 20
},
details: {
title: "Hoss",
{
sku: "00e8da9d",
type: "Film",
title: "The Matrix",
description: "Set in the 22nd century, Th
asin: "B000P0J0AQ",
shipping: {
weight: 6,
dimensions: {
width: 10,
height: 10,
depth: 1
},
},
pricing: {
list: 1200,
retail: 1100,
savings: 100,
pct_savings: 8.5
},
details: {
title: "The Matrix",
Slide 43
Slide 43 text
pct_savings: 20
},
details: {
title: "Hoss",
artist: "Lagwagon",
genre: [ "Punk", "Hardcore", "Indie Rock" ],
label: "Fat Wreck Chords",
number_of_discs: 1,
issue_date: "November 21, 1995",
format: "CD",
alternate_formats: [ 'Vinyl', 'MP3' ],
tracks: [
"Kids Don't Like To Share",
"Violins",
"Name Dropping",
"Bombs Away",
"Move The Car",
"Sleep",
"Sick",
"Rifle",
"Weak",
"Black Eye",
"Bro Dependent",
"Razor Burn",
"Shaving Your Head",
"Ride The Snake",
],
pct_savings: 8.5
},
details: {
title: "The Matrix",
director: [ "Andy Wachowski", "Larry Wa
writer: [ "Andy Wachowski", "Larry Wach
actor: [ "Keanu Reeves" , "Lawrence Fis
genre: [ "Science Fiction", "Action" ],
number_of_discs: 1,
issue_date: "May 15 2007",
original_release_date: "1999",
disc_format: "DVD",
rating: "R",
alternate_formats: [ 'VHS', 'Bluray' ],
run_time: "136",
studio: "Warner Bros",
language: "English",
format: [ "AC-3", "Closed-captioned", "
aspect_ratio: "1.66:1"
},
}
Slide 44
Slide 44 text
Queries
Slide 45
Slide 45 text
db.products.find( { 'name': "The Matrix" } );
Slide 46
Slide 46 text
{
"_id": ObjectId("4d8ad78b46b731a22943d3d3"),
"sku": "00e8da9d",
"type": "Film",
"name": "The Matrix",
"description": "Set in the 22nd century, The Matrix...",
"asin": "B000P0J0AQ",
"shipping": {
"weight": 6,
"dimensions": {
"width": 10,
"height": 10,
"depth": 1
}
},
"pricing": {
db.products.find( { 'name': "The Matrix" } );
Slide 47
Slide 47 text
db.products.find( { 'details.actor': "Groucho Marx" } );
Slide 48
Slide 48 text
},
"pricing": {
"list": 1000,
"retail": 800,
"savings": 200,
"pct_savings": 20
},
"details": {
"title": "A Night at the Opera",
"director": "Sam Wood",
"actor": ["Groucho Marx", "Chico Marx", "Harpo Marx"],
"genre": "Comedy",
"number_of_discs": 1,
"issue_date": "May 4 2004",
"original_release_date": "1935",
"disc_format": "DVD",
db.products.find( { 'details.actor': "Groucho Marx" } );
Slide 49
Slide 49 text
db.products.find( {
'details.genre': "Jazz", 'details.format': "CD"
} );
Slide 50
Slide 50 text
"list": 1200,
"retail": 1100,
"savings": 100,
"pct_savings": 8
},
"details": {
"title": "A Love Supreme [Original Recording Reissued]",
"artist": "John Coltrane",
"genre": ["Jazz", "General"],
"format": "CD",
"label": "Impulse Records",
"number_of_discs": 1,
"issue_date": "December 9, 1964",
"alternate_formats": ["Vinyl", "MP3"],
"tracks": [
"A Love Supreme Part I: Acknowledgement",
db.products.find( {
'details.genre': "Jazz", 'details.format': "CD"
} );
Slide 51
Slide 51 text
db.products.find( { 'details.actor':
{ $all: ['James Stewart', 'Donna Reed'] }
} );
Slide 52
Slide 52 text
},
"details": {
"title": "It's a Wonderful Life",
"director": "Frank Capra",
"actor": ["James Stewart", "Donna Reed", "Lionel Barrymore"],
"writer": [
"Frank Capra",
"Albert Hackett",
"Frances Goodrich",
"Jo Swerling",
"Michael Wilson"
],
"genre": "Drama",
"number_of_discs": 1,
"issue_date": "Oct 31 2006",
"original_release_date": "1947",
db.products.find( { 'details.actor':
{ $all: ['James Stewart', 'Donna Reed'] }
} );
Slide 53
Slide 53 text
Wanna Play?
• grab products.js from
http://github.com/spf13/mongoProducts
• mongo --shell products.js
• > use mongoProducts
Slide 54
Slide 54 text
Embedded documents
are great for orders
• Ordered items need to be fixed at the time
of purchase
• Embed them right in the order
db.order.find( { 'items.sku': '00e8da9f' } );
db.order.find( {
'items.details.actor': 'James Stewart'
} ).count();
Slide 55
Slide 55 text
What about
transactions?
Using the right solution for each situation
Slide 56
Slide 56 text
Data (like people) are
really sensitive when it
comes to money
Slide 57
Slide 57 text
Stricter data
requirements for $$
Slide 58
Slide 58 text
Stricter data
requirements for $$
• For financial systems any data inconsistency
is unacceptable
Slide 59
Slide 59 text
Stricter data
requirements for $$
• For financial systems any data inconsistency
is unacceptable
• Perhaps you’ve heard of ACID?
Slide 60
Slide 60 text
What about ACID?
Slide 61
Slide 61 text
What about ACID?
Q: Is MongoDB ACID?
Slide 62
Slide 62 text
What about ACID?
Q: Is MongoDB ACID?
A: Kinda
Slide 63
Slide 63 text
Atomicity
Slide 64
Slide 64 text
Atomicity
• MongoDB does atomic writes
Slide 65
Slide 65 text
Atomicity
• MongoDB does atomic writes
... for single document changesets
Slide 66
Slide 66 text
Atomicity
• MongoDB does atomic writes
... for single document changesets
• $set, $unset, $inc, $push,
$pushAll, $pull, $pullAll, $bit
Slide 67
Slide 67 text
Consistency
Slide 68
Slide 68 text
Consistency
• MongoDB can enforce unique keys
Slide 69
Slide 69 text
Consistency
• MongoDB can enforce unique keys
... but only on keys shared by every
document in the collection
Slide 70
Slide 70 text
Consistency
• MongoDB can enforce unique keys
... but only on keys shared by every
document in the collection
• MongoDB can't enforce referential integrity
Slide 71
Slide 71 text
Isolation
Slide 72
Slide 72 text
Isolation
• // Pseudo-isolated updates
db.foo.update( { x : 1 } , { $inc : { y : 1 } } , false , true );
Slide 73
Slide 73 text
Isolation
• // Pseudo-isolated updates
db.foo.update( { x : 1 } , { $inc : { y : 1 } } , false , true );
• // Isolated updates
db.foo.update( { x : 1 , $atomic : 1 } , { $inc : { y : 1 } } , false , true );
Slide 74
Slide 74 text
Isolation
• // Pseudo-isolated updates
db.foo.update( { x : 1 } , { $inc : { y : 1 } } , false , true );
• // Isolated updates
db.foo.update( { x : 1 , $atomic : 1 } , { $inc : { y : 1 } } , false , true );
• But there are caveats...
Slide 75
Slide 75 text
Isolation
• // Pseudo-isolated updates
db.foo.update( { x : 1 } , { $inc : { y : 1 } } , false , true );
• // Isolated updates
db.foo.update( { x : 1 , $atomic : 1 } , { $inc : { y : 1 } } , false , true );
• But there are caveats...
• Despite the $atomic keyword, this is not an atomic update,
since atomicity implies “all or nothing”
Slide 76
Slide 76 text
Isolation
• // Pseudo-isolated updates
db.foo.update( { x : 1 } , { $inc : { y : 1 } } , false , true );
• // Isolated updates
db.foo.update( { x : 1 , $atomic : 1 } , { $inc : { y : 1 } } , false , true );
• But there are caveats...
• Despite the $atomic keyword, this is not an atomic update,
since atomicity implies “all or nothing”
• $atomic here means update is done without an interference
from any other operation (isolated)
Slide 77
Slide 77 text
Isolation
• // Pseudo-isolated updates
db.foo.update( { x : 1 } , { $inc : { y : 1 } } , false , true );
• // Isolated updates
db.foo.update( { x : 1 , $atomic : 1 } , { $inc : { y : 1 } } , false , true );
• But there are caveats...
• Despite the $atomic keyword, this is not an atomic update,
since atomicity implies “all or nothing”
• $atomic here means update is done without an interference
from any other operation (isolated)
• An isolated update can only act on a single collection. Multi-
collection updates are not transactional, thus not isolatable.
Slide 78
Slide 78 text
Durability
Slide 79
Slide 79 text
Durability
• Mongo has this one covered
Slide 80
Slide 80 text
What does
MongoDB Support?
Slide 81
Slide 81 text
No content
Slide 82
Slide 82 text
• Atomic single document writes
• If you need atomic writes across multi-document
transactions don't use Mongo
• Many if not most e-commerce transactions could be
accomplished within a single document write
Slide 83
Slide 83 text
• Atomic single document writes
• If you need atomic writes across multi-document
transactions don't use Mongo
• Many if not most e-commerce transactions could be
accomplished within a single document write
• Unique indexes
• This only works on keys used by the entire collection
Slide 84
Slide 84 text
• Atomic single document writes
• If you need atomic writes across multi-document
transactions don't use Mongo
• Many if not most e-commerce transactions could be
accomplished within a single document write
• Unique indexes
• This only works on keys used by the entire collection
• Isolated (not atomic) single collection updates.
• Mongo does not support locking
• There are ways to work around this
Slide 85
Slide 85 text
• Atomic single document writes
• If you need atomic writes across multi-document
transactions don't use Mongo
• Many if not most e-commerce transactions could be
accomplished within a single document write
• Unique indexes
• This only works on keys used by the entire collection
• Isolated (not atomic) single collection updates.
• Mongo does not support locking
• There are ways to work around this
• It’s durable
Slide 86
Slide 86 text
There are ways to
guarantee ACID properties
in MongoDB
Here are 3 good approaches useful for
E-commerce transactions
Slide 87
Slide 87 text
Optimistic
Concurrency
Slide 88
Slide 88 text
Optimistic
Concurrency
• Read the current state of a product
Slide 89
Slide 89 text
Optimistic
Concurrency
• Read the current state of a product
• Make your changes with the assertion that
your product has the same state as it did
when you last read it
Slide 90
Slide 90 text
Optimistic concurrency
in MongoDB
Slide 91
Slide 91 text
Optimistic concurrency
in MongoDB
We’ll use an update-if-current strategy.
Slide 92
Slide 92 text
Optimistic concurrency
in MongoDB
We’ll use an update-if-current strategy.
This example is straight from the documentation:
Slide 93
Slide 93 text
Optimistic concurrency
in MongoDB
We’ll use an update-if-current strategy.
This example is straight from the documentation:
> t = db.inventory
> p = t.findOne({sku:'abc'})
> t.update({_id:p._id, qty:p.qty}, {'$inc': {qty: -1}});
> db.$cmd.findOne({getlasterror:1});
{"err" : , "updatedExisting" : true , "n" : 1 , "ok" : 1}
// it worked
Slide 94
Slide 94 text
Optimistic concurrency
in MongoDB
We’ll use an update-if-current strategy.
This example is straight from the documentation:
> t = db.inventory
> p = t.findOne({sku:'abc'})
> t.update({_id:p._id, qty:p.qty}, {'$inc': {qty: -1}});
> db.$cmd.findOne({getlasterror:1});
{"err" : , "updatedExisting" : true , "n" : 1 , "ok" : 1}
// it worked
... If that didn't work, try again until it does.
Slide 95
Slide 95 text
Optimistic concurrency
• Read the current state of a product.
• Make your changes with the assertion that
your product has the same state as it did
when you last read it.
Slide 96
Slide 96 text
Optimistic concurrency
• Read the current state of a product.
• Make your changes with the assertion that
your product has the same state as it did
when you last read it.
• It is also possible to use OCC to bootstrap
pessimistic concurrency and fake row level
locking
Slide 97
Slide 97 text
Optimistic concurrency
control assumes an
environment with low
data contention
Slide 98
Slide 98 text
OCC works great for
companies like Amazon
• Amazon has a long-tail catalog
• A long tail catalog lends itself well to
optimistic concurrency, because it has low
data contention
Slide 99
Slide 99 text
OCC fails miserably for
Slide 100
Slide 100 text
OCC fails miserably for
•eBay
Slide 101
Slide 101 text
OCC fails miserably for
•eBay
•Gilt
Slide 102
Slide 102 text
OCC fails miserably for
•eBay
•Gilt
•Groupon
Slide 103
Slide 103 text
OCC fails miserably for
•eBay
•Gilt
•Groupon
•OpenSky
Slide 104
Slide 104 text
OCC fails miserably for
•eBay
•Gilt
•Groupon
•OpenSky
•Living Social
Slide 105
Slide 105 text
OCC fails miserably for
•eBay
•Gilt
•Groupon
•OpenSky
•Living Social
•InsertFlashSaleSiteOfTheMinute
Slide 106
Slide 106 text
Flash sales and auctions
are defined by high data
contention
Slide 107
Slide 107 text
Flash sales and auctions
are defined by high data
contention
• The model doesn't work otherwise
Slide 108
Slide 108 text
Flash sales and auctions
are defined by high data
contention
• The model doesn't work otherwise
• They can't afford to be optimistic
Slide 109
Slide 109 text
Flash sales and auctions
are defined by high data
contention
• The model doesn't work otherwise
• They can't afford to be optimistic
• Order really matters
Slide 110
Slide 110 text
What about high
contention
environments?
Slide 111
Slide 111 text
If we can avoid
concurrency we’ve got
it made
Slide 112
Slide 112 text
Commerce is ACID
In Real Life
Slide 113
Slide 113 text
No content
Slide 114
Slide 114 text
1. I go to Barneys and see a pair of shoes I just have to
buy.
2. I call “dibs” (by grabbing them off the shelf).
3. I take them up to the cash register and purchase them:
• Store inventory has been manually decremented.
• I pay for them with my trusty AmEx.
4. If all goes according to plan, I walk out of the store.
5. If my card was declined, the shoes are “rolled back”
... out onto the shelves and sold to the next customer
who wants them.
Slide 115
Slide 115 text
All of this is
accomplished without
concurrency
Slide 116
Slide 116 text
Each item can only be
held by a consumer
Slide 117
Slide 117 text
We follow the same
model for e-commerce
Slide 118
Slide 118 text
No content
Slide 119
Slide 119 text
1. Select a product.
Slide 120
Slide 120 text
1. Select a product.
2. Update the document to hold inventory.
Slide 121
Slide 121 text
1. Select a product.
2. Update the document to hold inventory.
• Store inventory has been decremented.
Slide 122
Slide 122 text
1. Select a product.
2. Update the document to hold inventory.
• Store inventory has been decremented.
3. Purchase the product(s)
Slide 123
Slide 123 text
1. Select a product.
2. Update the document to hold inventory.
• Store inventory has been decremented.
3. Purchase the product(s)
• Process payment
Slide 124
Slide 124 text
1. Select a product.
2. Update the document to hold inventory.
• Store inventory has been decremented.
3. Purchase the product(s)
• Process payment
4. Roll back if anything went wrong.
Slide 125
Slide 125 text
MongoDB e-commerce
transactions
Slide 126
Slide 126 text
MongoDB e-commerce
transactions
• Each Item (not SKU) has it’s own document
• Document consists of...
• a reference to the SKU (product)
• a state ( available / sold / ... )
• potentially other data (timestamp, order ref)
Slide 127
Slide 127 text
Transactions
in MongoDB
Slide 128
Slide 128 text
Transactions
in MongoDB
We’ll use a simple update statement here.
Slide 129
Slide 129 text
Transactions
in MongoDB
We’ll use a simple update statement here.
> t = db.inventory
> sku = sku.findOne({sku:'abc'})
> t.update({ref_id:sku._id, state: 'available'}, {'$set':
{state: 'ordered'}});
> db.$cmd.findOne({getlasterror:1});
{"err" : , "updatedExisting" : true , "n" : 1 , "ok" : 1}
// it worked
Slide 130
Slide 130 text
Transactions
in MongoDB
We’ll use a simple update statement here.
> t = db.inventory
> sku = sku.findOne({sku:'abc'})
> t.update({ref_id:sku._id, state: 'available'}, {'$set':
{state: 'ordered'}});
> db.$cmd.findOne({getlasterror:1});
{"err" : , "updatedExisting" : true , "n" : 1 , "ok" : 1}
// it worked
... If that didn't work, no inventory available
Slide 131
Slide 131 text
Cart in Cart Action
Slide 132
Slide 132 text
Cart in Cart Action
An added benefit, it can easily provide
inventory hold in cart.
Slide 133
Slide 133 text
Cart in Cart Action
An added benefit, it can easily provide
inventory hold in cart.
> t = db.inventory
> sku = sku.findOne({sku:'abc'})
> t.update({ref_id:sku._id, state: 'available'}, {'$set':
{state: 'in cart'}});
> db.$cmd.findOne({getlasterror:1});
{"err" : , "updatedExisting" : true , "n" : 1 , "ok" : 1}
// it worked
Slide 134
Slide 134 text
Cart in Cart Action
An added benefit, it can easily provide
inventory hold in cart.
> t = db.inventory
> sku = sku.findOne({sku:'abc'})
> t.update({ref_id:sku._id, state: 'available'}, {'$set':
{state: 'in cart'}});
> db.$cmd.findOne({getlasterror:1});
{"err" : , "updatedExisting" : true , "n" : 1 , "ok" : 1}
// it worked
just like reality, each item is either
available, in a cart, or purchased
Slide 135
Slide 135 text
If we cannot avoid
concurrency we will
take a more creative
approach
Slide 136
Slide 136 text
Blending
NoSQL & RDBMS
Using the right solution for each situation
Slide 137
Slide 137 text
Doctrine (ORM/ODM)
to the rescue
Slide 138
Slide 138 text
Doctrine (ORM/ODM)
to the rescue
It would be possible without them,
but we're not that masochistic
Slide 139
Slide 139 text
Data to store in SQL
• Order
• Order/Shipment
• Order/Transaction
• Inventory
Slide 140
Slide 140 text
Data to store in
MongoDB
Slide 141
Slide 141 text
Data to store in
MongoDB
• User
• Product
• Product/Sellable
• Address
• Cart
• CreditCard
• Event
• TaxRate
• ... and then I got tired of
typing them in
• Just imagine this list has
40 more classes
• ...
Slide 142
Slide 142 text
The most boring SQL
schema ever
Slide 143
Slide 143 text
CREATE TABLE `product_inventory` (
`product_id` char(32) NOT NULL,
`inventory` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
);
CREATE TABLE `sellable_inventory` (
`sellable_id` char(32) NOT NULL,
`inventory` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`sellable_id`)
);
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` char(32) NOT NULL,
`shippingName` varchar(255) DEFAULT NULL,
`shippingAddress1` varchar(255) DEFAULT NULL,
`shippingAddress2` varchar(255) DEFAULT NULL,
`shippingCity` varchar(255) DEFAULT NULL,
`shippingState` varchar(2) DEFAULT NULL,
`shippingZip` varchar(255) DEFAULT NULL,
`billingName` varchar(255) DEFAULT NULL,
`billingAddress1` varchar(255) DEFAULT NULL,
`billingAddress2` varchar(255) DEFAULT NULL,
`billingCity` varchar(255) DEFAULT NULL,
Slide 144
Slide 144 text
Wait. How does
inventory live in SQL?
Isn’t that a property in one of your Mongo collections?
Slide 145
Slide 145 text
I thought you’d
never ask!
Slide 146
Slide 146 text
CREATE TABLE `product_inventory` (
`product_id` char(32) NOT NULL,
`inventory` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
);
CREATE TABLE `sellable_inventory` (
`sellable_id` char(32) NOT NULL,
`inventory` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`sellable_id`)
);
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` char(32) NOT NULL,
`shippingName` varchar(255) DEFAULT NULL,
`shippingAddress1` varchar(255) DEFAULT NULL,
`shippingAddress2` varchar(255) DEFAULT NULL,
`shippingCity` varchar(255) DEFAULT NULL,
`shippingState` varchar(2) DEFAULT NULL,
`shippingZip` varchar(255) DEFAULT NULL,
`billingName` varchar(255) DEFAULT NULL,
`billingAddress1` varchar(255) DEFAULT NULL,
`billingAddress2` varchar(255) DEFAULT NULL,
`billingCity` varchar(255) DEFAULT NULL,
Slide 147
Slide 147 text
Inventory is transient
Slide 148
Slide 148 text
Inventory is transient
• Product::$inventory is effectively a
transient property
• Note how I said “effectively”? ... we cheat
and persist our transient property to
MongoDB as well
• We can do this because we never really
trust the value stored in Mongo
Slide 149
Slide 149 text
Accuracy is only important
when there’s contention
Slide 150
Slide 150 text
Accuracy is only important
when there’s contention
• For display, sorting and alerts, we can use
the value stashed in MongoDB
• It’s faster
• It’s accurate enough
Slide 151
Slide 151 text
Accuracy is only important
when there’s contention
• For display, sorting and alerts, we can use
the value stashed in MongoDB
• It’s faster
• It’s accurate enough
• For financial transactions, we want the
security and comfort of our RDBMS.
Slide 152
Slide 152 text
Inventory kept in sync
with listeners
Slide 153
Slide 153 text
Inventory kept in sync
with listeners
• Every time a new product is created, its
inventory is inserted in SQL
Slide 154
Slide 154 text
Inventory kept in sync
with listeners
• Every time a new product is created, its
inventory is inserted in SQL
• Every time an order is placed, inventory is
verified and decremented
Slide 155
Slide 155 text
Inventory kept in sync
with listeners
• Every time a new product is created, its
inventory is inserted in SQL
• Every time an order is placed, inventory is
verified and decremented
• Whenever the SQL inventory changes, it is
saved to MongoDB as well
Slide 156
Slide 156 text
Be careful what you lock
Slide 157
Slide 157 text
Be careful what you lock
1. Acquire inventory row lock and begin transaction
2. Check current product inventory
3. Decrement product inventory
4. Write the Order to SQL
5. Update affected MongoDB documents
6. Commit the transaction
7. Release product inventory lock
Slide 158
Slide 158 text
Making MongoDB
and RDBMS relations
play nice
Slide 159
Slide 159 text
Products are
documents stored
in MongoDB
Slide 160
Slide 160 text
/** @mongodb:Document(collection="products") */
class Product
{
/** @mongodb:Id */
private $id;
/** @mongodb:String */
private $title;
public function getId()
{
return $this->id;
}
public function getTitle()
{
return $this->title;
}
public function setTitle($title)
{
$this->title = $title;
}
}
Slide 161
Slide 161 text
Orders are entities
stored in an RDBMS
Slide 162
Slide 162 text
/**
* @orm:Entity
* @orm:Table(name="orders")
* @orm:HasLifecycleCallbacks
*/
class Order
{
/**
* @orm:Id @orm:Column(type="integer")
* @orm:GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @orm:Column(type="string")
*/
private $productId;
/**
* @var Documents\Product
*/
private $product;
// ...
}
Slide 163
Slide 163 text
So how does an
RDBMS have a
reference to something
outside the database?
Slide 164
Slide 164 text
Setting the Product
class Order {
// ...
public function setProduct(Product $product)
{
$this->productId = $product->getId();
$this->product = $product;
}
}
Slide 165
Slide 165 text
• $productId is mapped and persisted
• $product which stores the Product
instance is not a persistent entity property
Slide 166
Slide 166 text
Retrieving our
product later
Slide 167
Slide 167 text
OrderPostLoadListener
use Doctrine\ORM\Event\LifecycleEventArgs;
class OrderPostLoadListener
{
public function postLoad(LifecycleEventArgs $eventArgs)
{
// get the order entity
$order = $eventArgs->getEntity();
// get odm reference to order.product_id
$productId = $order->getProductId();
$product = $this->dm->getReference('MyBundle:Document\Product', $productId);
// set the product on the order
$em = $eventArgs->getEntityManager();
$productReflProp = $em->getClassMetadata('MyBundle:Entity\Order')
->reflClass->getProperty('product');
$productReflProp->setAccessible(true);
$productReflProp->setValue($order, $product);
}
}
Slide 168
Slide 168 text
All Together Now
// Create a new product and order
$product = new Product();
$product->setTitle('Test Product');
$dm->persist($product);
$dm->flush();
$order = new Order();
$order->setProduct($product);
$em->persist($order);
$em->flush();
// Find the order later
$order = $em->find('Order', $order->getId());
// Instance of an uninitialized product proxy
$product = $order->getProduct();
// Initializes proxy and queries the monogodb database
echo "Order Title: " . $product->getTitle();
print_r($order);
Slide 169
Slide 169 text
Read more about
this technique
Jon Wage, one of OpenSky’s engineers, first
wrote about this technique on his personal
blog: http://jwage.com
You can read the full article here:
http://jwage.com/2010/08/25/blending-the-
doctrine-orm-and-mongodb-odm/
Slide 170
Slide 170 text
Questions?
http://spf13.com
@spf13
PS: We’re hiring!! Contact us at [email protected]
Slide 171
Slide 171 text
No content