From 44b31e40c5c9c30313dd0099ffc5e073f8f2041a Mon Sep 17 00:00:00 2001
From: Jan Vales <jan@jvales.net>
Date: Mon, 17 Jun 2019 03:10:37 +0200
Subject: [PATCH] [ex3.2] Mongodb challenge FTW.

---
 ex3/mongodb/Vagrantfile          |  20 ++++++
 ex3/mongodb/mongodb.conf         |   3 +
 ex3/mongodb/vagrant_provision.sh | 120 +++++++++++++++++++++++++++++++
 3 files changed, 143 insertions(+)
 create mode 100644 ex3/mongodb/Vagrantfile
 create mode 100644 ex3/mongodb/mongodb.conf
 create mode 100755 ex3/mongodb/vagrant_provision.sh

diff --git a/ex3/mongodb/Vagrantfile b/ex3/mongodb/Vagrantfile
new file mode 100644
index 0000000..7245d45
--- /dev/null
+++ b/ex3/mongodb/Vagrantfile
@@ -0,0 +1,20 @@
+Vagrant.configure("2") do |config|
+  config.vm.box = "debian/stretch64"
+
+  config.vm.provider "virtualbox" do |v|
+    v.name = "adbs"
+    v.memory = 1024
+  end
+
+  config.vm.provider "libvirt" do |v|
+    v.memory = 1024
+
+    config.vm.synced_folder ".", "/vagrant", type: "rsync"
+  end
+
+  # design-choice: run file in vm.
+  config.vm.provision "shell", inline: "/vagrant/vagrant_provision.sh"
+
+  config.vm.network "forwarded_port", adapter: 1, guest: 27017, host: 27017
+
+end
diff --git a/ex3/mongodb/mongodb.conf b/ex3/mongodb/mongodb.conf
new file mode 100644
index 0000000..8195729
--- /dev/null
+++ b/ex3/mongodb/mongodb.conf
@@ -0,0 +1,3 @@
+dbpath=/var/lib/mongodb
+logpath=/var/log/mongodb/mongodb.log
+journal=true
diff --git a/ex3/mongodb/vagrant_provision.sh b/ex3/mongodb/vagrant_provision.sh
new file mode 100755
index 0000000..2f61878
--- /dev/null
+++ b/ex3/mongodb/vagrant_provision.sh
@@ -0,0 +1,120 @@
+#!/bin/bash
+
+cd /vagrant
+
+apt-get update
+apt-get install -y -q mongodb mongo-tools
+
+cp mongodb.conf /etc/mongodb.conf
+systemctl restart mongodb
+sleep 1
+
+
+echo "** ADBS stuff **"
+# cleanup
+mongo adbs --quiet --eval 'db.dropDatabase()'
+# merge books + editions -> 1 ID for borrowing.
+mongo adbs --quiet --eval 'db.books.insertMany([{
+    "_id":ObjectId("100000000000000000000011"),
+    "name":"Computational Complexity",
+    "author":"Papadimitrou",
+    "year":"1993",
+    "isbn":"0201530821",
+    "owned":NumberInt(3)
+},{
+    "_id":ObjectId("100000000000000000000021"),
+    "name":"Parameterized Complexity Theory",
+    "author":"Flum, Grohe",
+    "year":"2006",
+    "isbn":"3642067573",
+    "owned":NumberInt(3)
+},{
+    "_id":ObjectId("100000000000000000000032"),
+    "name":"Fundamentals of Database Systems",
+    "author":"Elmasri, Navathe",
+    "year":"1994",
+    "isbn":"0805317481",
+    "owned":NumberInt(6)
+},{
+    "_id":ObjectId("100000000000000000000036"),
+    "name":"Fundamentals of Database Systems",
+    "author":"Elmasri, Navathe",
+    "year":"2010",
+    "isbn":"0136086209",
+    "owned":NumberInt(2)
+},{
+    "_id":ObjectId("100000000000000000000037"),
+    "name":"Fundamentals of Database Systems",
+    "author":"Elmasri, Navathe",
+    "year":"2017",
+    "isbn":"1292097612",
+    "owned":NumberInt(1)
+}])'
+
+# Faster same-name-lookups, relevant for "borrowable-lookup" query.
+mongo adbs --quiet --eval 'db.books.createIndex({"name":1})'
+
+# irrelevant table
+mongo adbs --quiet --eval 'db.person.insertMany([{
+    "_id":"Charles",
+    "address":"420 Paper St"
+},{
+    "_id":"Rachel",
+    "address":"90 Bedford St"
+},{
+    "_id":"Tom",
+    "address":"41-505 Kalanianaole Highway"
+}])'
+
+# borrowings. not yet returned -> "to":null. Not "to" absent -> always be explicit about the data model.
+mongo adbs --quiet --eval 'db.borrowings.insertMany([{
+    "who":"Charles",
+    "book":ObjectId("100000000000000000000011"),
+    "from":ISODate("1995-03-21"),
+    "to":ISODate("1995-04-29")
+},{
+    "who":"Charles",
+    "book":ObjectId("100000000000000000000021"),
+    "from":ISODate("2009-03-03"),
+    "to":ISODate("2010-12-05")
+},{
+    "who":"Rachel",
+    "book":ObjectId("100000000000000000000032"),
+    "from":ISODate("1994-09-22"),
+    "to":ISODate("1997-01-17")
+},{
+    "who":"Rachel",
+    "book":ObjectId("100000000000000000000037"),
+    "from":ISODate("2010-11-05"),
+    "to":ISODate("2010-12-01")
+},{
+    "who":"Tom",
+    "book":ObjectId("100000000000000000000037"),
+    "from":ISODate("2019-05-01"),
+    "to":null
+}])'
+
+echo "** DONE **"
+
+
+
+#db.borrowings.find({$and:[ {"from":{ $lt: new Date(new Date().getTime()-(1000*3600*24*14)) }}, {"to":null}]}, {who:1, _id:0})
+
+
+#db.books.aggregate([
+#{$match:{"name":"Fundamentals of Database Systems"}}
+#,
+#{$lookup:{"from": "borrowings", "localField": "_id", "foreignField": "book", as: "borrowing"}}
+#,
+#{$project:{"name":1,"author":1,"year":1,"isbn":1,"owned":1,_id:1,
+#//    "borrowing": {$filter:{input: "$borrowing", as: "borrow", cond: { $eq: [ "$$borrow.to", null ] } }},
+#    "borrowing_size": {$size:{$filter:{input: "$borrowing", as: "borrow", cond: { $eq: [ "$$borrow.to", null ] } }}}
+#    }}
+#,
+#{$project:{"name":1,"author":1,"year":1,"isbn":1,_id:1,
+#    "borrow_avail": {$subtract: ["$owned", "$borrowing_size"] },
+#//    "owned":1, "borrowing": 1, "borrowing_size": 1
+#    }}
+#,
+#{$match:{"borrow_avail":{$gt:0}}}
+#])
-- 
2.43.0