]> git.somenet.org - pub/jan/adbs.git/blob - ex3/mongodb/vagrant_provision.sh
almost complete ex3.3
[pub/jan/adbs.git] / 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 #])