MongoDB
ACID
"Atomicity, Consistency, Isolation, Durability". A set of properties that guarantee that database transactions are processed reliably.
CRUD
"Create Read Update Delete" --> "Insert Find Update Remove". Things that can be performed on records.
like $push except only adds unique items to set
$addToSet instead of $push
$all vs $in
$all returns document if all in query array match ...plus $all does expect itself to be working on an array field, whereas $in could work on sub-object like name.first ($in can work within { name: {first: 'John'} }
negative form of $in
$nin (not in)
what to use if specialized negation operator does not exist
$not...eg: $mod does not have a specialized negation operator (queries-3.mov)
What are the two data structure that live within JSON?
(1) Arrays - lists of things, (2) Dictionaries - associative maps
Which features did MongoDB omit to retain scalability (2)?
(1) Joins, (2) Transactions across multiple collections
What are the goals of normalization in relational databases? (3)
(1) free the database of modification anomalies, (2) minimize redesign when extending, and (3) avoid bias toward any particular access pattern
batch insert
...
how update works
.update({query}, {replacement object}, upsert boolean)
What are two reasons you might want to keep two documents that are related to each other one-to-one in separate collections?
1) to reduce the working set size of your applications, (2) because the combined size of the documents would be larger than 16MB.
embedded document
A document contained by a parent document. The embedded document is a value of a key in the parent document.
What is MongoDB?
A non-relational, schemaless store of JSON documents
dynamic typing
A programming language is said to be dynamically typed, or just 'dynamic', when the majority of its type checking is performed at run-time as opposed to at compile-time. In dynamic typing, types are associated with values not variables.
static typing
A programming language is said to use static typing when type checking is performed during compile-time as opposed to run-time. In static typing, types are associated with variables not values.
database transaction
A transaction comprises of a unit of work performed within a DBMS against a database and treated in a coherent and reliable way independent of other transactions.
Which optimization will typically have the greatest impact on the performance of a database?
Adding appropriate indexes on large collections so that only a small percentage of queries need to scan the collection.
db.<collection>.insert({document});
Adds a document to a collection.
$addToSet
Adds a value to the array only if its not in the array already. Otherwise, if the field is not present, it sets the field to the array value. ex: { $addToSet : { a : { $each : [ 3 , 5 , 6 ] } } }
Access pattern
An access pattern is a specification of an access mode for every attribute of a relation schema, i.e., it is an indication of which attributes are used as input and which ones are used as output.
Atomic operations
An operation during which a processor can simultaneously read a location and write it in the same bus operation. This prevents any other processor or I/O device from writing or reading memory until the operation is complete.
$push
Appends a value to field, if the field is an existing array. Otherwise it sets the field to the array [value] if field is not present. ex: { $push : { field : value } }
$pushAll
Appends each value in the value_array to the field, if field is an existing array. Otherwise, it sets the field to the array value_array, if field is not present ex: { $pushAll : { field : value_array } }
$all
Array specific conditional allowing matching on multiple elements within an array. The operation is non strict on the sequence of the values in the passed selector array. ex: db.<col>.find({arrField:{$all: [1,2,3]}});
$size
Array specific conditional used to select based on the length or set-size of an array field value. ex: db.<col>.find{{arrVal: {$size: {$gt:0}}});
BSON
Binary representation of JSON. Supports basic data types that Mongo contains.
BSON
Binary-encoded serialization of mongo documents.
db.cloneDatabase(fromhost)
Clone the current database (implied by 'db') from another host.
db.<col>.help([commandName]);
Command to get help docs on a specific command at the collection level.
db.help([commandName]);
Command to get help docs on a specific command at the db level.
help
Command to list commands and descriptions of those commands in the context of the database.
$in
Contains query conditional used to check if a value contains the field value. ex: db.<col>.find({numVal: {$in: [1,2,3]}});
db.copyDatabase(frmDBName, toDBName, [frmHostName], [user], [pwd]);
Copies an entire database from one name on one server to another name on another server. Omit frmHostName to copy from one name to another on the same server.
db.myCollection.ensureIndex({keypattern},[{options}])
Creates an index on property(s). Options can describe the type of sorting, uniqueness, etc.
document
Data structure used to store all other data in mongo databases.
how javascript writes april 30, 2012
Date(2012, 3, 30) ...ISOdate uses 2012-4-30
$unset
Deletes a given field. ex: { $unset : { field : 1} }
db.<col>.remove({selector});
Deletes documents from the collection based on the selector.
$nin
Does not contain query conditional used to select documents with the field that is not in the selector list of values. ex: db.<col>.find({numVal: {$nin: [1,2,3]}});
What can you do to make your MongoDB 'schema' more effective.
Embed and pre-join anywhere you can.
db.runCommand({fsync:1,lock:1,async:1})
Flushes all pending writes to data files. The lock option allows one to safely snapshot the database's data files. The async option returns focus to the console immediately while the task runs in the background.
$gte
Greater than or equal query conditional. ex: db.<col>.find({numVal: {$gte:3}});
$gt
Greater than query conditional. ex: db.<col>.find({numVal: {$gt:3}});
consistency (ACID)
Guarantee that database constraints are not violated, particularly once a transaction commits.
durability (ACID)
Guarantee that transactions that have committed will survive permanently.
isolation (ACID)
How transaction integrity is visible to other users and systems.
What is the biggest advantage of embedding?
Improved read performance - only one round trip needed to the DB.
maximum value
In BSON, represents the largest possible value.
minimum value
In BSON, represents the smallest possible value.
What's the primary difference between Python dicts and JSON dictionaries?
JSON dictionaries preserve the order of the key-value pairs whereas Python dicts are orderless.
What is the interactive language for the MongoDB shell?
JavaScript - can type it right in the shell!
code
Javascript code. Documents can contain code as values.
$lte
Less than or equal query conditional. ex: db.<col>.find({numVal: {$lte:3}});
$lt
Less than query conditional. ex: db.<col>.find({numVal: {$lt:3}});
show collections
Lists all the collections in the database in use.
What's the single most important factor in designing your application schema with MongoDB?
Matching the data access patterns of your application
$mod
Meta query conditional generating a modulus with the selector operand. Checks to see if the field value when divided by one value has the remainder of the second value. ex: db.<col>.find({age: {$mod: [2,3]}});
$not
Meta query conditional used to negate the selector. ex: db.<col>.find({numVal: {$not:{{$in: [1,2,3]}}});
Rich documents
More than just tabular data. Includes arrays, key-values, etc. Allows for "pre-joins" or embedded data
Does MongoDB have its own query language?
No, Mongo's CRUD operations exist as methods/functions in programming language APIs, not as separate language
Are you allowed to build MultiKey Indexes when both keys are array?
No, cartesian match too big. However, you can build one if one is an index and the other isn't.
Does MongoDB have constraints? (ex: FK constraints)
Nope. Less of an issue b/c of embbedding
$ne
Not equal query conditional. ex: db.<col>.find({numVal: {$ne:3}});
32-Bit Integer
Not supported by the javascript shell and are converted to 64-bit floating point numbers.
64-Bit Integer
Not supported by the javascript shell and are converted to 64-bit floating point numbers.
$where
Operator allowing the execution of arbitrary javascript as part of the selector. The function or boolean comparative statement which is the arbitrary javascript returns a boolean indicating whether or not the document on which the function operated should be included in the result set.
$elemMatch
Operator allowing the partial specification of fields in an embedded document in an array. Otherwise, an exact match would be required on the embedded document in that position in the array to return the parent document. ex: db.<col>.find({arrDocs: {$elemMatch: {field1Of10:"bob", field8Of10:"sally"}}});
$slice
Operator used in the second argument of the find() call to specify which slice of an array to return for an array field. ex: db.<col>.find({selector}, {arrVal: {$slice: [1,3]}});
$
Positional operator that holds the position of the first matched array item in the query. Used to find an array member and then manipulate it. ex: {$inc:{'comments.$.votes':1}} where comments looks like: "comments" : [ { "by" : "joe", "votes" : 3 }, { "by" : "jane", "votes" : 7 } ]
$or
Query conditional allowing the selector to choose between more than one optional criteria. ex: db.<col>.find({$or:{numVal:1,numVal:8}});
$pull
Removes all occurrences of a value from a field. ex: { $pull : { field : _value } }
$pullAll
Removes all occurrences of each value in the passed in array from the field. ex: { $pullAll : { field : value_array } }
$pop
Removes an element from an array based on the value of the field option being 1 or -1 for last or first element respectively. ex: { $pop : { field : -1 } }
$rename
Renames a field from the one indicated to the new field name. ex: { $rename : { old_field_name : new_field_name } }
How does MongoDB's < / > query operators work for strings?
Retrieves based on Ascii character order. Case sensitive. Ex: db.uses.find({name:{$gte :"F",$lte:"Q"}}) <> db.uses.find({name:{$gte :"f",$lte:"Q"}})
db.<collection>.find([properties]);
Returns all the documents matching the optional properties.
db.commandHelp("commandName")
Returns help information for a command.
db.getLastError()
Returns information about the last operation. Not necessarily error data, the return can contain things like number of records updated.
db.<collection>.findOne([properties]);
Returns one document matching the optional properties.
Object.bsonsize({document});
Returns the size of a document after it is converted to BSON from the JSON-like original.
What does $type do while using the "find" method
Returns values from the specified field that match the character type. $type values are numeric and correspond to the BSON type as specified in the BSON documentation.
array
Set or list of values.
Update Modifiers
Special keys passed in an update call to specify complex update operations, such as alter, adding, or removing keys, and even manipulating arrays and embedded documents.
boolean
Standard binary data type.
regular expression
Standard javascript regular expressions. These can be stored in documents
date
Stored as milliseconds since the epoch.
binary data
String of arbitrary bytes.
key.index
Syntax used for selectors to be specific to a certain element position within an array. ex: db.<col>.find({arrField.2:"eggs"});
Cursor
The local variable representation of a query. Uniquely, the cursor does not hold the results but instead access to the results.
64-bit floating point numbers
The standard number representation in the javascript shell.
What are the costs of indexes?
They take up space on disk and they need to be updated when new records are added.
Schemaless
Two documents do not have to have the same schema
object id
Unique 12-byte ID for documents.
db.<col>.update({selector},{document});
Update a document in the collection.
Upsert
Update call that inserts a new document if no update match is found. Allows the same code to be used for inserts as well as updates. Upsert is indicated by a third parameter which is a boolean in the update statement. ex: db.<col>.update({selector},{document},true);
$set
Update modifier that set the value of a key and if the key does not exist, it will create the key. ex: db.<col>.update({selector},{"$set":{"someKey":"someValue"}});
$inc
Update modifier used to increment a value. ex: {"$inc":{"counter":2}}
Multi-updates
Updates all matching documents, not just the first, that match the selector. This behavior is chosen by passing a boolean to the fourth update call parameter. ex: db.<col>.update({selector},{document},<upsertBool>,true});
undefined
Used to describe a variable as not having been initialized.
null
Used to represent both a null value and a nonexistent field.
atomicity (ACID)
When a series of database operations either all occur or nothing occurs. Prevents updates to the database occurring only partially.
When is it recommended to represent a one to many relationship in multiple collections?
When the "many" is large.
GRIDFS
Will break-up large file (>64 MB) into chunks and store chunks in a collection AND will store meta-data about the chunks in another collection.
$push operator
add item into an array []
query info in bookmarks.js in terminal
after above, do mongo bookmarks to connect to bookmarks database in terminal. can then do db.links.find() to list everything in links database
$where operator benefits
allows us to write javascript, and have javascript executed as the query
caveat of include and exclude
can't run include and exclude in same query, with exception of can exclude _id field { title:1, url: 1, _id: 0}
use <database name>
changes the database on which you are working.
run code in bookmarks.js on localhost, using (& querying) bookmarks database
clay$: mongo 127.0.0.1/bookmarks bookmarks.js
mongo
command to start the mongo shell.
mongod
command to start the mongodb process.
find all method
db.collection_name.distinct('card_set')
remove collection & name itself
db.collection_name.drop()
remove Database & name itself
db.dropDatabase()
get all favourites values
db.links.distinct('favourites')...will return an array
find all users where name.first is john or jane
db.links.find({ 'name.first': { $in: ['John', 'Jane'] } }, { 'name.first':1, _id:0});
basic query 'greater than' syntax
db.links.find({ favourites: { $gt: 50 } });
find all documents where favourites is gt 50 and less than 150
db.links.find({ favourites: {$gt:50, $lt:150} }, {title:1, favourites:1, _id:0});
if one of the values in tags is 'marketplace' or 'code', return document
db.links.find({ tags: { $in: ['marketplace', 'code'] } });
find all tags whose value is not equal to code
db.links.find({ tags: { $ne: 'code' }}); ...y not $nin
search using regex
db.links.find({ title: /tuts\+$/ }); ...find all documents where title ends in "tuts+"
search using regex and include another operator
db.links.find({ title: { $regex: /tuts\+$/, $ne: "Mobiletuts+" } } );
find all documents and display their titles
db.links.find({ }, { title: 1, _id:0}); ...remember that db.links.find() returns all documents in links
sort alphabetically
db.links.find({}, { title: 1, _id: 0}).sort({ title: 1}); (can also sort by -1)
return top favourites score with .limit()
db.links.find({}, { title:1, favourites:1, _id: 0}).sort({ favourites: -1}).limit(1);
"paging" with .skip()
db.links.find({}, {title:1, _id:0}).skip(0*3).limit(3); db.links.find({}, {title:1, _id:0}).skip(1*3).limit(3); db.links.find({}, {title:1, _id:0}).skip(2*3).limit(3);
grouping values by userID
db.links.group({ key: {userId: true }, initial: { favCount: 0 }, reduce: function (doc, o) { o.favCount += doc.favourites; }, finalize: function (o) {o.name = db.users.findOne({_id: o.userId}).name; } });
$addToSet when we want to add multiple items without duplicating any already existing
db.links.update(n, { $addToSet: { tags: { $each: ['one', 'four'] } } });
$push example
db.links.update(n, { $push { tags: 'blog' } });
$pushAll
db.links.update(n, { $pushAll { tags: ['one', 'two'] } });
update by modification...$inc
db.links.update({title: "Nettuts+"}, { $inc: { favourites: 5} }); ... can also increment by -5
find all documents and count how many there are
db.users.find().count() OR db.users.count()
make find results pretty in terminal
db.users.find().forEach(printjson)
$and operator...find all documents where first name is John and last name is Jones
db.users.find({ $and: [{ 'name.first': "John"}, { 'name.last' : "Jones" }] }); ...RETURNS AN ARRAY!
$or operator...find all documents where first name is John OR last name is Wilson
db.users.find({ $or: [ {'name.first': "John" }, {'name.last': "Wilson"} ] }, {name: 1});
$where operator
db.users.find({ $where: 'this.name.first === "John" ', age: 30 }) ..."this" refers to a given document
positional update within array
db.users.find({ 'logins.minutes' : 20}, { $inc: { 'logins.$.minutes':10} }, false, true);
how to avoid doing "select *" equivalent in mongo...how to return desired fields only
db.users.find({ email: '[email protected]' } , { title: 1, url: true }) ... this query will return "_id", "title", and "url" fields
return entire document where email: '[email protected]'
db.users.find({ email: '[email protected]' }) ...note that in the document's json object, email is in quotations, as is the address.
$exists operator
db.users.find({ email: { $exists: true } });
$elemMatch example
db.users.find({ logins: { $elemMatch: { minutes: 20} } }); (logins is an array ... "logins" :[ { "at" : asldfjaljf, "minutes": 20}, {"at: jfkdjf, "minutes": 18}]
same query as above, written differently
db.users.find({ name: { first: "John", last: "Jones" }});
.findAndModify()
db.users.findAndModify({ query: { name: "Kate Wills" }, update: { $set: { age: 20} }, new: true }); default for new is false...if you want .findAndModify to return new (instead of prior) object, set to true.
query within sub-objects
db.users.findOne({ 'name.first' : 'John' }, { 'name.last' : 1}) ...this will return specific document, including "id" and name.last fields only.
remove all documents within collection
db.users.remove() .. will remove users collection
remove document within users collection
db.users.remove({ 'name.first': 'John' });
update by inserting text
db.users.update(q, { $set: { email: '[email protected]'} });
update by modifying text
db.users.update(q, { $set: { job: 'Web Developer' } });
update by removing text
db.users.update(q, { $unset: { job: 'Web Developer" } });
update by replacement
db.users.update({ 'name.first' : 'John' }, { job: 'developer'}); ... completely replaced all fields with {job: 'developer'}
find vs findOne
find returns a cursor object...so can't do db.users.find({ email: '[email protected]'}).name ...this returns nothing! however if you do db.users.findOne({ email: '[email protected]'}).name this returns first & last names! ...also findOne makes pretty output
how to search with javascript string w/o where
if a single string...can write db.users.find('this.name.first === "John" ')
if upsert field set to true
if query object does not exist, will insert replacement object
Python exception handling
import sys. try: "code" , except: print "exception ", sys.exc_info()[0]
update's fourth parameter
multi...set to true if you want to update documents other than the first one returned...however only works with modification, so upsert (3rd parameter) must be set to false
$pull
pull an item out of an ARRAY
$pullAll
pull multiple items out of an Array
$rename
renames field but does not change value... db.users.update({ random: true}, { $rename: { 'random': 'something_else'} }, false, true);
how to exclude field from search results
same as above, but do {title: 0 (or false)} ...will return all fields except title
string
set of UTF-8 characters.
.explain()
stuff
$elemMatch
to find element within an ARRAY
two ways to update
update by replacement & update by modification
.save() (save method)
var bob = db.users.findOne({ 'name.first' : 'Bob' }); bob.job = "Server Admin" db.users.save(bob)
the above w/o passing a javascript string inside a query
var f = function () { return this.name.first === "John"}; db.users.find(f)
select john from user collection and query which links he's made from the links collection using his userid
var john = db.users.findOne({'name.first':'John'}); db.links.find({ userId: john._id }, { title: 1, _id: 0});
when to use operators in MongoDB
when we don't know exact value of field
query vs modification operator orders
with query operators, key is outside and operator is inside...with modification operators, operator is outside
