From d6528c2001b3ddd1d3cd4dcbd6dd0cf2e924e1be Mon Sep 17 00:00:00 2001
From: Jan Vales <jan@jvales.net>
Date: Mon, 17 Jun 2019 04:14:00 +0200
Subject: [PATCH] [ex3.2] mongodb report.

---
 ex3/main_2.tex | 68 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 68 insertions(+)

diff --git a/ex3/main_2.tex b/ex3/main_2.tex
index fcedfae..37610fc 100644
--- a/ex3/main_2.tex
+++ b/ex3/main_2.tex
@@ -1 +1,69 @@
 %ex3.2
+
+\begin{enumerate}[label=(\alph*)]
+	\item\textbf{New data model}\\
+	It only makes sense to merge books and editions, as everything else would ultimately lead to too many redundancies, instead of a reduction of complexity.
+	
+	\begin{lstlisting}[style=command]
+books:{
+	"_id":ObjectId("..."),
+	"name":"...",
+	"author":"...",
+	"year":"...",             - from edition
+	"isbn":"...",             - from edition
+	"owned":NumberInt(...)   - from edition
+}
+
+person:{
+	"_id":"...",
+	"address":"..."
+}
+
+borrowings:{
+	"who":"...",
+	"book":ObjectId("..."),
+	"from":ISODate("yyyy-mm-dd"),
+	"to":ISODate("yyyy-mm-dd") or "to":null
+}
+	\end{lstlisting}
+	
+	\item\textbf{Queries}\\
+	The ''inserts'' of our data model are in \textbf{vagrant\_provision.sh}.\\
+	It is safe to re-run the following commands.\\
+	They will reset and re-populate the mongodb every time.
+	\begin{lstlisting}[style=command]
+$ cd mongodb
+$ vagrant up --provision
+# wait a while
+$ vagrant ssh
+	\end{lstlisting}
+	
+	\begin{enumerate}[label=(\roman*)]
+		\item\textbf{Listing all people who have borrowed a book for more than two weeks and not
+		returned it yet}
+		\begin{lstlisting}[style=command]
+mongo adbs --quiet --eval 'db.borrowings.find({$and:[ {"from":{ $lt: new Date(new Date().getTime()-(1000*3600*24*14)) }}, {"to":null}]}, {who:1, _id:0})'
+		\end{lstlisting}
+		\item\textbf{Asking if any edition of a book is currently available to be borrowed.}
+		\begin{lstlisting}[style=command]
+mongo adbs --quiet --eval '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}}}
+])'
+		\end{lstlisting}
+	\end{enumerate}
+
+\end{enumerate}
\ No newline at end of file
-- 
2.43.0