ex3/mongodb/vagrant_provision.sh

   1 #!/bin/bash
   2
   3 cd /vagrant
   4
   5 apt-get update
   6 apt-get install -y -q mongodb mongo-tools
   7
   8 cp mongodb.conf /etc/mongodb.conf
   9 systemctl restart mongodb
  10 sleep 1
  11
  12
  13 echo "** ADBS stuff **"
  14 # cleanup
  15 mongo adbs --quiet --eval 'db.dropDatabase()'
  16 # merge books + editions -> 1 ID for borrowing.
  17 mongo adbs --quiet --eval 'db.books.insertMany([{
  18     "_id":ObjectId("100000000000000000000011"),
  19     "name":"Computational Complexity",
  20     "author":"Papadimitrou",
  21     "year":"1993",
  22     "isbn":"0201530821",
  23     "owned":NumberInt(3)
  24 },{
  25     "_id":ObjectId("100000000000000000000021"),
  26     "name":"Parameterized Complexity Theory",
  27     "author":"Flum, Grohe",
  28     "year":"2006",
  29     "isbn":"3642067573",
  30     "owned":NumberInt(3)
  31 },{
  32     "_id":ObjectId("100000000000000000000032"),
  33     "name":"Fundamentals of Database Systems",
  34     "author":"Elmasri, Navathe",
  35     "year":"1994",
  36     "isbn":"0805317481",
  37     "owned":NumberInt(6)
  38 },{
  39     "_id":ObjectId("100000000000000000000036"),
  40     "name":"Fundamentals of Database Systems",
  41     "author":"Elmasri, Navathe",
  42     "year":"2010",
  43     "isbn":"0136086209",
  44     "owned":NumberInt(2)
  45 },{
  46     "_id":ObjectId("100000000000000000000037"),
  47     "name":"Fundamentals of Database Systems",
  48     "author":"Elmasri, Navathe",
  49     "year":"2017",
  50     "isbn":"1292097612",
  51     "owned":NumberInt(1)
  52 }])'
  53
  54 # Faster same-name-lookups, relevant for "borrowable-lookup" query.
  55 mongo adbs --quiet --eval 'db.books.createIndex({"name":1})'
  56
  57 # irrelevant table
  58 mongo adbs --quiet --eval 'db.person.insertMany([{
  59     "_id":"Charles",
  60     "address":"420 Paper St"
  61 },{
  62     "_id":"Rachel",
  63     "address":"90 Bedford St"
  64 },{
  65     "_id":"Tom",
  66     "address":"41-505 Kalanianaole Highway"
  67 }])'
  68
  69 # borrowings. not yet returned -> "to":null. Not "to" absent -> always be explicit about the data model.
  70 mongo adbs --quiet --eval 'db.borrowings.insertMany([{
  71     "who":"Charles",
  72     "book":ObjectId("100000000000000000000011"),
  73     "from":ISODate("1995-03-21"),
  74     "to":ISODate("1995-04-29")
  75 },{
  76     "who":"Charles",
  77     "book":ObjectId("100000000000000000000021"),
  78     "from":ISODate("2009-03-03"),
  79     "to":ISODate("2010-12-05")
  80 },{
  81     "who":"Rachel",
  82     "book":ObjectId("100000000000000000000032"),
  83     "from":ISODate("1994-09-22"),
  84     "to":ISODate("1997-01-17")
  85 },{
  86     "who":"Rachel",
  87     "book":ObjectId("100000000000000000000037"),
  88     "from":ISODate("2010-11-05"),
  89     "to":ISODate("2010-12-01")
  90 },{
  91     "who":"Tom",
  92     "book":ObjectId("100000000000000000000037"),
  93     "from":ISODate("2019-05-01"),
  94     "to":null
  95 }])'
  96
  97 echo "** DONE **"
  98
  99
 100
 101 #db.borrowings.find({$and:[ {"from":{ $lt: new Date(new Date().getTime()-(1000*3600*24*14)) }}, {"to":null}]}, {who:1, _id:0})
 102
 103
 104 #db.books.aggregate([
 105 #{$match:{"name":"Fundamentals of Database Systems"}}
 106 #,
 107 #{$lookup:{"from": "borrowings", "localField": "_id", "foreignField": "book", as: "borrowing"}}
 108 #,
 109 #{$project:{"name":1,"author":1,"year":1,"isbn":1,"owned":1,_id:1,
 110 #//    "borrowing": {$filter:{input: "$borrowing", as: "borrow", cond: { $eq: [ "$$borrow.to", null ] } }},
 111 #    "borrowing_size": {$size:{$filter:{input: "$borrowing", as: "borrow", cond: { $eq: [ "$$borrow.to", null ] } }}}
 112 #    }}
 113 #,
 114 #{$project:{"name":1,"author":1,"year":1,"isbn":1,_id:1,
 115 #    "borrow_avail": {$subtract: ["$owned", "$borrowing_size"] },
 116 #//    "owned":1, "borrowing": 1, "borrowing_size": 1
 117 #    }}
 118 #,
 119 #{$match:{"borrow_avail":{$gt:0}}}
 120 #])