mongodb

mongod

mongod -datapath
mongod --dbpath /data/db
mongod.exe --dbpath "C:\mongodbwin32-x86_64-3.0.3\data"

mongod --dbpath db1 --port 27001 --smallfiles --oplogSize 50

mongod --dbpath "c://1" --replSet abc --port 27002
mongod --dbpath "c://2" --replSet abc --port 27002
mongod --dbpath "c://3" --replSet abc --port 27003

mongo

mongo
mongo --shell --port 27003 a.js
mongo --shell pcat homework2.js
mongo --shell localhost/performance performance.js
mongo --port 27001 --shell replication.js

db and collection

db
show dbs

show collections
use pcat

db.createCollection('logs', {capped: true, size: 1048576})

db.help()
db.stats()

db.COLLECTION_NAME
db.unicorns.count()
db.unicorns.help()

db.unicorns.find()

data dictionary

db.system.indexes.find()

create user

db.createUser(
{
user: "administrator",
pwd: "mypassword",
roles: [ "root" ]
}

db.shutdownServer()

mongod --dbpath "C:\mongodb-win32-x86_64-3.0.3\data" --auth

mongo -u administrator -p mypassword --authenticationDatabase admin

.. ....
use admin
db.auth('admin','mypassword')

pretty or print

db.javastuff.find().pretty()

jason

jason example
{
"name": "john",
"age": 25,
"address": {
          "city": "New York",
          "postalcode": 10021
          },
"phones": [
           {"phone":"212-555-1234","type":"home"},
           {"phone":"646-555-1234","type":"mobil"}
          ]
}

find

# regular expression
db.media.find ( { Title : /Matrix*/i } )

db.foo.find()

db.javastuff.find().pretty()

db.myCollection.find({lastname: "Meier"}, {firstname: true}).limit(10).skip(20)
SELECT firstname FROM myTable WHERE lastname = 'Meier' LIMIT 20,10

db.unicorns.find({}, {name: 1});

db.media.find ( { Released : {$lt : 1999 } }, { "Cast" : 0 } )

db.users.find({}).sort({"name":1})

db.media.find().sort( { Title: 1 })

db.media.find().limit( 10 )

db.media.find().skip( 20 )

db.media.find().sort ( { Title : -1 } ).limit ( 10 ).skip ( 20 )

By default, the _id field is always returned. 
We can explicitly exclude it by specifying {name:1, _id: 0}.

-- first 3
db.media.find({"Title" : "Matrix, The"}, {"Cast" : {$slice: 3}})

-- last 3
db.media.find({"Title" : "Matrix, The"}, {"Cast" : {$slice: -3}})

-- skip the first two items and limit the results to three
db.media.find({"Title" : "Matrix, The"}, {"Cast" : {$slice: [2,3] }})

-- skip to the last five items and limit the results to four
db.media.find({"Title" : "Matrix, The"}, {"Cast" : {$slice: [-5,4] }})

# $not (meta-operator)
db.media.find ( { Tracklist : { $not : { "$elemMatch" : { Title: "Smells Like Teen Spirit", "Track" : "1" } } } } )

db.media.find ( { "Type" : "DVD", "Released" : { $lt : 1995 } } )

db.media.find ( { "Type" : "DVD", $where: "this.Released < 1995" } )

db.media.find ("this.Released < 1995")

db.postings.find( { 'comments.flagged' : true } )
db.products.find({'brand':'ACME'})
db.products.find({"limits.voice":{$exists:1}}).count()
db.products.find({},{for:1})

db.products.find({type:"case"}).count()

db.products.find({'brand':'ACME'})
db.products.find({brand : "ACME"})

db.products.find()
db.products.count()

db.unicorns.find({_id: ObjectId("TheObjectId")})

db.users.find({}, {"name": 1,"age": 1})

# 0 do not display
By setting the projection values for the name and age to 0, the phone number is returned instead:
> db.users.find({}, {"name": 0,"age": 0})

db.users.find({ age: { $gt: 40 } })
db.users.find({ age: { $gte: 32 } })

db.unicorns.find({gender: 'm', weight: {$gt: 700}})
db.unicorns.find({gender: {$ne: 'f'}, weight: {$gte: 701}})
db.unicorns.find({vampires: {$exists: false}})
-- set
db.unicorns.find({loves: {$in:['apple','orange']}})
-- or
db.unicorns.find({gender: 'f',$or: [{loves: 'apple'},{weight: {$lt: 500}}]})

db.persons.insert(firstname: "Meier", loves: ['apple', 'orange', 'tomato']) 
db.persons.find($or: [{loves: 'apple'}, {loves: 'orange'}])

db.users.find( { $and: [ { "age": { $lt: 35 } }, { "name": "john" } ] } )
db.users.find( { $or: [ { "age": { $lt: 35 } }, { "name": "john" } ]} )

db.nodes.find_one({url:url})

- db.products.insert({"_id" : "ac9", "name" : "AC9 Phone", "brand" : "ACME","type" : "phone", "price" : 333, "warranty_years" : 0.25, "available" : true})

- db.products.findOne( { _id : ObjectId("507d95d5719dbef170f15c00") } )

- myobj = db.products.findOne( { _id : ObjectId("507d95d5719dbef170f15c00") } )

db.products.find({"limits.voice":{$exists:1}}).count()

db.policies.find( { status : { $ne : "expired" }, coverages : { $elemMatch : { type : "liability", rates : { $elemMatch : { rate : { $gte : 100 }, current : true } } } } } )

-- using 1 for ascending and -1 for descending

//heaviest unicorns first
db.unicorns.find().sort({weight: -1})

//by unicorn name then vampire kills:
db.unicorns.find().sort({name: 1, vampires: -1})

-- -------------------------------------

db.unicorns.find().sort({weight: -1}).limit(2).skip(1)

db.unicorns.count({vampires: {$gt: 50}})

db.unicorns.find({vampires: {$gt: 50}}).count()

insert

db.m102.insert([{"a":1},{"b":2},{"c":3
db.unicorns.insert({name: 'Aurora', gender: 'f', weight: 450})
db.unicorns.insert({name: 'Leto', gender: 'm', home: 'Arrakeen', worm: false})

db.employees.insert({_id: ObjectId(
    "4d85c7039ab0fd70a117d734"),
    name: 'Ghanima',
    family: {mother: 'Chani',
        father: 'Paul',
        brother: ObjectId(
    "4d85c7039ab0fd70a117d730")}})

db.users.insert({name: 'leto',
    email: 'leto@dune.gov',
    addresses: [{street: "229 W. 43rd St",
                city: "New York", state:"NY",zip:"10036"},
               {street: "555 University",
                city: "Palo Alto", state:"CA",zip:"94107"}]})

db.employees.find({'family.mother': 'Chani'})
> manga = ( { "Type" : "Manga", "Title" : "One Piece", "Volumes" : 612,"Read" : 520 } )
{
"Type" : "Manga",
"Title" : "One Piece",
"Volumes" : "612",
"Read" : "520"
}
> db.media.insert(manga)

remove or delete

db.myCollection.remove({firstname: "Hans"});
db.contacts.remove({'_id': 3})
db.numbers.remove({'contact_id': 3})

update

db.postings.update( { _id: . . . , voters:{$ne:.joe.} }, { $inc : {votes:1}, $push : {voters:.joe.} } );

db.unicorns.find({name: 'Roooooodles'})
db.unicorns.update({name: 'Roooooodles'},{weight: 590})

db.unicorns.update({name: 'Pilot'},{$inc: {vampires: -2}})

db.users.update({name: "owen"}, {$set: {"age": 39}})

-- set
db.unicorns.update({name: 'Aurora'},{$push: {loves: 'sugar'}})

db.cms.nodes.update(
{ ... node specification ... },
{ '$push': { 'metadata.comments': {
'posted': datetime.utcnow(),
'author': author_info,
'text': comment_text } } } )


db.unicorns.update({weight: 590}, {$set: {
    name: 'Roooooodles',
    dob: new Date(1979, 7, 18, 18, 44),
    loves: ['apple'],
    gender: 'm',
    vampires: 99}})

db.myCollection.update({id: 123}, {$set : {a : 4}})

db.categories.update(
{'_id':bop_id}, {'$set': { 'name': 'BeBop' } } )

db.categories.update(
{'ancestors._id': bop_id},
{'$set': { 'ancestors.$.name': 'BeBop' } },
multi=True)

db.media.update( { "Title" : "Matrix, The"}, {"Type" : "DVD", "Title" : "Matrix, The", "Released" : 1999, "Genre" : "Action"}, { upsert: true} )

-- multiple 
db.unicorns.update({},{$set: {vaccinated: true }});
db.unicorns.find({vaccinated: true});

db.nodes.update(
{},
{$set: { short_description: '' } },
false, // upsert
true // multi
);

upserts

-- Upserts
db.users.update({user: "frank"}, {age: 40},{ upsert: true} )

db.hits.update({page: 'unicorns'},{$inc: {hits: 1}}, {upsert:true});
db.hits.find();

db.hits.update({page: 'unicorns'},{$inc: {hits: 1}});
db.hits.find();

db.hits.update({page: 'unicorns'},{$inc: {hits: 1}}, {upsert:true});
db.hits.find();

save

# save command
db.media.save( { "Title" : "Matrix, The"}, {"Type" : "DVD", "Title" : "Matrix, The", "Released" : "1999", "Genre" : "Action"})

predicate

-- $unset $exists $group $sum $match
-- $exists
db.products.find({"limits.voice":{$exists:1}}).count()

The $unset operator lets you delete a given field:
> db.media.update ( {"Title": "Matrix, The"}, {$unset : { "Genre" : 1 } } )
> db.users.update({name: "owen"}, {$unset : { "age" : 1} })

count, group, aggreagate

db.vehicle.count({make:"FORD"});
# Group by make
db.vehicle.group(
           {key: { make:true},
            reduce: function(obj,prev) { prev.count++;},
            initial: { count: 0 }
            });

db.vehicle.group(
           {key: { make:true, model:true},
            reduce: function(obj,prev) { prev.count++;},
            initial: { count: 0 }
            });

db.elegans.aggregate([{$match:{N2:"T"}},{$group:{_id:"$N2",n:{$sum:1}}}]).result[0].n

--- group by
db.unicorns.aggregate([{$group:{_id:'$gender', total: {$sum:1}}}])

db.unicorns.aggregate([{$match: {weight:{$lt:600}}},
    {$group: {_id:'$gender',  total:{$sum:1},
      avgVamp:{$avg:'$vampires'}}},
    {$sort:{avgVamp:-1}} ])

db.unicorns.aggregate([{$unwind:'$loves'},
    {$group: {_id:'$loves',  total:{$sum:1},
    unicorns:{$addToSet:'$name'}}},
    {$sort:{total:-1}},
    {$limit:1} ])

explain

db.unicorns.find().explain()
db.unicorns.find({name: 'Pilot'}).explain()

hints

db.media.find( { ISBN: " 978-1-4302-5821-6"} ) . hint ( { ISBN: 1 } )

Profiling and stats

db.setProfilingLevel(2);
db.unicorns.find({weight: {$gt: 600}});
db.system.profile.find()

index

db.sensor_readings.ensureIndex({tstamp:1, active:1})
db.products.ensureIndex({for:1});

db.products.ensureIndex({for:1});

db.sensor_readings.ensureIndex({tstamp:1, active:1})

db.products.ensureIndex({for:1})

db.cart.ensure_index([('status', 1), ('last_modified', 1)])

// where "name" is the field name
db.unicorns.ensureIndex({name: 1});

db.unicorns.dropIndex({name: 1});

db.unicorns.ensureIndex({name: 1}, {unique: true});

// Ensure descending index
db.media.ensureIndex( { Title :-1 } )

-- 1 for ascending, -1 for descending
db.unicorns.ensureIndex({name: 1, vampires: -1});

-- -----
# the first command.s background parameter ensures that the indexing is done on the background:
> db.media.ensureIndex({ISBN: 1}, {background: true});
> db.media.find( { ISBN: " 978-1-4302-5821-6"} ) . hint ( { ISBN: 1 } )

currentop and killop

db.currentOp() check current running operation 

db.killOp(?) kill the running operation
? - will be operation id
{opid}

shell and javascript

var c = db.products.find({}).sort({name:1}); c.forEach( function(doc){ print(doc.name) } );
var c = db.products.find({},{name:1,_id:0}).sort({name:1}); while( c.hasNext() ) print( c.next().name); 

var temp = db.products.findOne({_id:ObjectId("507d95d5719dbef170f15c00")})

temp.term_years = 3
db.products.update({_id:temp._id},temp)

temp.limits.sms.over_rate = 0.01
db.products.update({_id:temp._id},temp)

def deactivate_campaign(campaign_id):
db.ad.zone.update(
{ 'ads.campaign_id': campaign_id },
{' $pull': { 'ads', { 'campaign_id': campaign_id } } },
multi=True)

f = function() { return this.Released < 1995 }
db.media.find(f)

def get_node_by_url(url):
node = db.nodes.find_one({'url': url})
node.setdefault('short_description', '')
return node

-- -------------------
def get_node_by_url(url):
node = db.nodes.find_one({'url': url})
return node
-- -------------------

def add_short_descriptions():
node_ids_to_migrate = db.nodes.find(
{'short_description': {'$exists':False}}).limit(100)
db.nodes.update(
{ '_id': {'$in': node_ids_to_migrate } },
{ '$set': { 'short_description': '' } },
multi=True)

-- -----------------------

# journal is true
db.events.insert(event, j=True)

-- -----------------------

db.events.insert(event, w=2)
db.events.insert(event, j=True, w=2)

-- ------------------------

db.command('collstats', 'events')['indexSizes']

-- -------------------------

q_events = db.events.find('time':
... { '$gte':datetime(2000,10,10),'$lt':datetime(2000,10,11)})

-- --------------------------

# TTL collections, time to live

db.events.ensureIndex('time', expireAfterSeconds=3600)

M102

download 

video 

Replication

db.isMaster().maxBsonObjectSize

-- Checking the Replica Set Status
rs.status()

using rs.stepDown() (perhaps also rs.freeze())
rs.slaveOk()

shard or partition

mongodb --shardsvr --dbpath db/s1 --port 27501
mongodb --shardsvr --dbpath db/s2 --port 27601

mongo localhost:27019/config
db.shards.find()

mongos --configdb localhost:27019

# configuration server
mongo localhost:27019/config
configsvr> 
configsvr> db
config
configsvr> db.chunks.find().sort({_id:1}).next().lastmodEpoch.getTimestamp().toUTCString().substr(20,6)

mongoexport and mongoimport

mongoexport --db learn --collection unicorns
mongoexport --db learn --collection unicorns --csv --fields name,weight,vampires
mongoexport --db sampledb --collection users --limit 100 --out export.json

mongoimport -d pcat -c products --type json --file C:\products.json
mongoimport -d pcat -c products --drop products.json

mongorestore and monodump

mongodump --db test --collection users
mongodump --db learn --out backup

mongorestore --db learn --collection unicorns backup/learn/unicorns.bson
mongorestore --collection users --db sampledb dump/sampledb/users.bson
mongorestore --oplogReplay --verbose --host localhost --port 27501 --dbpath db/s1 --drop s1
mongorestore --oplogReplay --verbose --host localhost --port 27601 --dbpath db/s2 --drop s2

bsondump

the bsondump utility on that file
bsondump test.foo.2013-03-11T10-25-48.0.bson 

GridFS

-- GridFS
mongofiles -d images put mastersword.jpg

mongofiles list

mongofiles put /tmp/dictionary

mongofiles search hello

mongofiles delete /tmp/hello_world

mongofiles get /tmp/dictionary

java

-classpath mongo-java-driver-2.13.0.jar YourApplication.java

curl https://stream.twitter.com/1/statuses/sample.json -umongodb:secret| mongoimport -c tweets

mongostat

mongosniff: viewing operations sent to the database.

bsondump and monfiles



db.users.save({username: "jones"})
db.users.count()
db.users.find()
db.users.find({username: "jones"})

db.users.update( {username: "smith"},
{ $set: {favorites:
{
cities: ["Chicago", "Cheyenne"],
movies: ["Casablanca", "The Sting"]
}
}
})

db.users.update( {username: "jones"},
{"$set": {favorites:
{
movies: ["Casablanca", "Rocky"]
}
}
})

db.users.update( {"favorites.movies": "Casablanca"},
{$addToSet: {"favorites.movies": "The Maltese Falcon"} },
false,
true )

db.users.find({"favorites.movies": "Casablanca"})


# remove a collection
# that the remove() operation doesn.t actually delete the collection; it merely removes documents from a collection
db.foo.remove()  

# remove a collection
db.users.drop()

-- --------
for(i=0; i<200000; i++) {
db.numbers.save({num: i});
}
-- --------

> it --> like more

-- --------
db.foo.get --> will list function start with get

-- --------
db.stats()
db.runCommand( {dbstats: 1} )

-- ---------
# dict
db.system.namespaces.find();

-- ---------

db.mycollection.stats() 
db.runCommand( {collstats: 'numbers'} )
db.$cmd.findOne( {collstats: 'numbers'} );

-- ---------
getIndexes() --> db.numbers.getIndexes()

-- --------

db.numbers.find({num: {"$gt": 199995 }}).explain()  --> display as jason format

-- --------

db.runCommand
function (obj) {
  if (typeof obj == "string") {
    var n = {};
    n[obj] = 1;
    obj = n;
  }
  return this.getCollection("$cmd").findOne(obj);
}
-- ---------

db.numbers.save({n: 5});
db.numbers.save({ n: NumberLong(5) });


> db.numbers.save
function (obj) {
if (obj == null || typeof obj == "undefined") {
throw "can't save a null";
}

if (typeof obj._id == "undefined") {
obj._id = new ObjectId;
return this.insert(obj);
} else {
return this.update({_id:obj._id}, obj, true);
}

}

-- ----------

db.createCollection("users")

db.createCollection("users", {size: 20000})

db.createCollection("users.actions",{capped: true, size: 1024}) 

db.products.renameCollection("store_products")

-- ----------

# use the $natural sort operator
db.user.actions.find().sort({"$natural": -1});

-- ----------
db.users.find({age: {$gte: 0}, age: {$lte: 30})
db.users.find({age: {$gte: 0, $lte: 30})

# last names not beginning with B
db.users.find(last_name: {$not: /^B/} }

db.users.find({addresses: {$elemMatch: {name: 'home', state: 'NY'}}})
-- ----------

# javascript
db.reviews.find({$where: "function() { return this.helpful_votes > 3; }"}})
db.reviews.find({$where: "this.helpful_votes > 3"}})

db.reviews.find({user_id: ObjectId("4c4b1476238d3b4dd5000001"),$where: "(this.rating * .92) > 3"})

@users.find({$where => "this.#{attribute} == #{value}"})

# find best or worst, case insensitive
db.reviews.find({user_id: ObjectId("4c4b1476238d3b4dd5000001"), text: /best|worst/i })

# using mongo shell
db.reviews.find({user_id: ObjectId("4c4b1476238d3b4dd5000001"), text: {$regex: "best|worst", $options: "i" })

-- ---------

# Map or Reduce:

# step 1): map
map = function() {
var shipping_month = this.purchase_date.getMonth() + '-' + this.purchase_data.getFullYear();
var items = 0;

this.line_items.forEach(function(item) {
  tmpItems += item.quantity;
  });
emit(shipping_month, {order_total: this.sub_total, items_total: 0});
}


# step 2): reduce
reduce = function(key, values) {
var tmpTotal = 0;
var tmpItems = 0;
  tmpTotal += doc.order_total;
  tmpItems += doc.items_total;
return ( {total: tmpTotal, items: tmpItems} );
}

# step 3: map-reduce
filter = {purchase_date: {$gte: new Date(2010, 0, 1)}}
db.orders.mapReduce(map, reduce, {query: filter, out: 'totals'})

# step 4: output
> db.totals.find()

-- -----------

# sort: 1 order, -1 reverse order
db.reviews.find({}).sort({helpful_votes: -1}).limit(1)
db.reviews.find({}).sort({helpful_votes: 1}).limit(1)

-- ------------
db.values.reIndex();

# javascript, insert
spec = {ns: "green.users", key: {'addresses.zip': 1}, name: 'zip'}
db.system.indexes.insert(spec, true)

db.users.ensureIndex({zip: 1})
db.runCommand({deleteIndexes: "users", index: "zip"})
db.users.dropIndex("zip_1")

db.system.indexes.find()
db.users.getIndexSpecs()
db.values.getIndexKeys()

db.values.ensureIndex({open: 1, close: 1})
db.values.ensureIndex({open: 1, close: 1}, {background: true})

db.values.dropIndex("stock_symbol_1_close_1")

-- ------------

# distinct tags
db.products.distinct("tags")

-- --------------

# find and modify
db.orders.findAndModify({
query: {user_id: ObjectId("4c4b1476238d3b4dd5000001"),
state: "AUTHORIZING" },
update: {"$set":
{"state": "PRE-SHIPPING"},
"authorization": auth}
})

-- --------------

db.users.ensureIndex({username: 1}, {unique: true})
db.users.ensureIndex({username: 1}, {unique: true, dropDups: true})

db.products.ensureIndex({sku: 1}, {unique: true, sparse: true})
db.reviews.ensureIndex({user_id: 1}, {sparse: true})

db.products.find({category_ids: null})

-- -------------

mongorestore -d stocks -c values dump/stocks

-- -------------

http://www.tutorialspoint.com/mongodb/mongodb_data_modeling.htm

http://www.tutorialspoint.com/tutorialslibrary.htm

http://cs.indstate.edu/CS473/sqlite_tutorial.pdf
https://www.sqlite.org/quickstart.html
http://www.tutorialspoint.com/sqlite/
http://www.techonthenet.com/sqlite/

https://www.sqlite.org/faq.html
https://www.sqlite.org/doclist.html