We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Bug in bookshelves.py add method when changing edition
When calling the add method in openlibrary/core/bookshelves.py (e.g. POSTing to bookshelves.json) with a specific edition_id, the result is not as expected if another edition of the same work already exists on the user's bookshelves. 

### Steps to Reproduce
Submit a POST to bookshelves.json in a work context (e.g. https://openlibrary.org/works/OL460810W/bookshelves.json) with an add action and a specific edition_id: {'action':'add', 'redir':false, 'bookshelf_id':1, 'edition_id':'/books/OL7656518M', 'dont_remove':true}

If the user already has a different edition of the work on any bookshelf:

* If dont_remove is true, no change is made
* If dont_remove is false, the existing edition is removed from the bookshelf

Expected behavior:

The record for the work on the user's bookshelf should be updated to reflect the passed edition_id if dont_remove is true.

### Details

- **Logged in (Y/N)?**  Y
- **Browser type/version?** N/A
- **Operating system?** N/A
- **Environment (prod/dev/local)?** prod
<!-- If not sure, put prod -->

### Proposal & Constraints

Changing the update call at line 189 from

`return oldb.update('bookshelves_books', where=where, bookshelf_id=bookshelf_id, vars=data)`

to 

`return oldb.update('bookshelves_books', where=where, bookshelf_id=bookshelf_id, edition_id=edition_id, vars=data)`

should create the expected behavior without affecting other use cases.

### Stakeholders
@mheiman
@mekarpeles


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `openlibrary/core/bookshelves.py`
Content:
```
1 from openlibrary.utils.dateutil import DATE_ONE_MONTH_AGO, DATE_ONE_WEEK_AGO
2 
3 from . import db
4 
5 
6 class Bookshelves(object):
7 
8     PRESET_BOOKSHELVES = {
9         'Want to Read': 1,
10         'Currently Reading': 2,
11         'Already Read': 3
12     }
13 
14     PRESET_BOOKSHELVES_JSON = {
15         'want_to_read': 1,
16         'currently_reading': 2,
17         'already_read': 3,
18     }
19 
20     @classmethod
21     def summary(cls):
22         return {
23             'total_books_logged': {
24                 'total': Bookshelves.total_books_logged(),
25                 'month': Bookshelves.total_books_logged(since=DATE_ONE_MONTH_AGO),
26                 'week': Bookshelves.total_books_logged(since=DATE_ONE_WEEK_AGO)
27             },
28             'total_users_logged': {
29                 'total': Bookshelves.total_unique_users(),
30                 'month': Bookshelves.total_unique_users(since=DATE_ONE_MONTH_AGO),
31                 'week': Bookshelves.total_unique_users(since=DATE_ONE_WEEK_AGO)
32             }
33         }
34 
35     @classmethod
36     def total_books_logged(cls, shelf_ids=None, since=None):
37         """Returns (int) number of books logged across all Reading Log shelves (e.g. those
38         specified in PRESET_BOOKSHELVES). One may alternatively specify a
39         `list` of `shelf_ids` to isolate or span multiple
40         shelves. `since` may be used to limit the result to those
41         books logged since a specific date. Any python datetime.date
42         type should work.
43 
44         Args:
45             shelf_ids (list) - one or more bookshelf_id values, see
46                 also the default values specified in PRESET_BOOKSHELVES
47             since (datetime.date) - returns all logged books after date
48 
49         """
50 
51         oldb = db.get_db()
52         query = "SELECT count(*) from bookshelves_books"
53         if shelf_ids:
54             query += " WHERE bookshelf_id IN ($shelf_ids)"
55             if since:
56                 query += " AND created >= $since"
57         elif since:
58             query += " WHERE created >= $since"
59         results = oldb.query(query, vars={'since': since, 'shelf_ids': shelf_ids})
60         return results[0] if results else None
61 
62     @classmethod
63     def total_unique_users(cls, since=None):
64         """Returns the total number of unique users who have logged a
65         book. `since` may be provided to only return the number of users after
66         a certain datetime.date.
67         """
68         oldb = db.get_db()
69         query = "select count(DISTINCT username) from bookshelves_books"
70         if since:
71             query += " WHERE created >= $since"
72         results = oldb.query(query, vars={'since': since})
73         return results[0] if results else None
74 
75     @classmethod
76     def most_logged_books(cls, shelf_id, limit=10, since=False):
77         """Returns a ranked list of work OLIDs (in the form of an integer --
78         i.e. OL123W would be 123) which have been most logged by
79         users. This query is limited to a specific shelf_id (e.g. 1
80         for "Want to Read").
81         """
82         oldb = db.get_db()
83         query = 'select work_id, count(*) as cnt from bookshelves_books WHERE bookshelf_id=$shelf_id '
84         if since:
85             query += " AND created >= $since"
86         query += ' group by work_id order by cnt desc limit $limit'
87         return list(oldb.query(query, vars={'shelf_id': shelf_id, 'limit': limit, 'since': since}))
88 
89     @classmethod
90     def count_total_books_logged_by_user(cls, username, bookshelf_ids=None):
91         """Counts the (int) total number of books logged by this `username`,
92         with the option of limiting the count to specific bookshelves
93         by `bookshelf_id`
94         """
95         return sum(cls.count_total_books_logged_by_user_per_shelf(
96             username, bookshelf_ids=bookshelf_ids).values())
97 
98     @classmethod
99     def count_total_books_logged_by_user_per_shelf(cls, username, bookshelf_ids=None):
100         """Returns a dict mapping the specified user's bookshelves_ids to the
101         number of number of books logged per each shelf, i.e. {bookshelf_id:
102         count}. By default, we limit bookshelf_ids to those in PRESET_BOOKSHELVES
103 
104         TODO: add `since` to fetch books logged after a certain
105         date. Useful for following/subscribing-to users and being
106         notified of books they log. Also add to
107         count_total_books_logged_by_user
108         """
109         oldb = db.get_db()
110         data = {'username': username}
111         _bookshelf_ids = ','.join([str(x) for x in bookshelf_ids or cls.PRESET_BOOKSHELVES.values()])
112         query = ("SELECT bookshelf_id, count(*) from bookshelves_books WHERE "
113                  "bookshelf_id=ANY('{" + _bookshelf_ids + "}'::int[]) "
114                  "AND username=$username GROUP BY bookshelf_id")
115         result = oldb.query(query, vars=data)
116         return dict([(i['bookshelf_id'], i['count']) for i in result]) if result else {}
117 
118     @classmethod
119     def get_users_logged_books(cls, username, bookshelf_id=None, limit=100, page=1):
120         """Returns a list of Reading Log database records for books which
121         the user has logged. Records are described in core/schema.py
122         and include:
123 
124         username (str) - who logged this book
125         work_id (int) - the Open Library work ID as an int (e.g. OL123W becomes 123)
126         bookshelf_id (int) - the ID of the bookshelf, see: PRESET_BOOKSHELVES.
127             If bookshelf_id is None, return books from all bookshelves.
128         edition_id (int) [optional] - the specific edition logged, if applicable
129         created (datetime) - date the book was logged
130 
131         """
132         oldb = db.get_db()
133         page = int(page) if page else 1
134         data = {
135             'username': username,
136             'limit': limit,
137             'offset': limit * (page - 1),
138             'bookshelf_id': bookshelf_id
139         }
140         query = ("SELECT * from bookshelves_books WHERE "
141                  "bookshelf_id=$bookshelf_id AND username=$username "
142                  "LIMIT $limit OFFSET $offset")
143         if bookshelf_id is None:
144             query = ("SELECT * from bookshelves_books WHERE "
145                  "username=$username")
146             # XXX Removing limit, offset, etc from data looks like a bug
147             # unrelated / not fixing in this PR.
148             data = { 'username': username }
149         return list(oldb.query(query, vars=data))
150 
151     @classmethod
152     def get_users_read_status_of_work(cls, username, work_id):
153         """A user can mark a book as (1) want to read, (2) currently reading,
154         or (3) already read. Each of these states is mutually
155         exclusive. Returns the user's read state of this work, if one
156         exists.
157         """
158         oldb = db.get_db()
159         data = {
160             'username': username,
161             'work_id': int(work_id)
162         }
163         bookshelf_ids = ','.join([str(x) for x in cls.PRESET_BOOKSHELVES.values()])
164         query = ("SELECT bookshelf_id from bookshelves_books WHERE "
165                  "bookshelf_id=ANY('{" + bookshelf_ids + "}'::int[]) "
166                  "AND username=$username AND work_id=$work_id")
167         result = list(oldb.query(query, vars=data))
168         return result[0].bookshelf_id if result else None
169 
170     @classmethod
171     def add(cls, username, bookshelf_id, work_id, edition_id=None):
172         """Adds a book with `work_id` to user's bookshelf designated by
173         `bookshelf_id`"""
174         oldb = db.get_db()
175         work_id = int(work_id)
176         bookshelf_id = int(bookshelf_id)
177         data = {
178             'work_id': work_id,
179             'username': username,
180         }
181 
182         users_status = cls.get_users_read_status_of_work(username, work_id)
183         if not users_status:
184             return oldb.insert('bookshelves_books', username=username,
185                                bookshelf_id=bookshelf_id,
186                                work_id=work_id, edition_id=edition_id)
187         else:
188             where = "work_id=$work_id AND username=$username"
189             return oldb.update('bookshelves_books', where=where,
190                                bookshelf_id=bookshelf_id, vars=data)
191 
192     @classmethod
193     def remove(cls, username, work_id, bookshelf_id=None):
194         oldb = db.get_db()
195         where = {
196             'username': username,
197             'work_id': int(work_id)
198         }
199         if bookshelf_id:
200             where['bookshelf_id'] = int(bookshelf_id)
201 
202         try:
203             return oldb.delete('bookshelves_books',
204                                where=('work_id=$work_id AND username=$username'), vars=where)
205         except:  # we want to catch no entry exists
206             return None
207 
208     @classmethod
209     def get_works_shelves(cls, work_id, lazy=False):
210         """Bookshelves this work is on"""
211         oldb = db.get_db()
212         query = "SELECT * from bookshelves_books where work_id=$work_id"
213         try:
214             result = oldb.query(query, vars={'work_id': int(work_id)})
215             return result if lazy else list(result)
216         except:
217             return None
218 
219     @classmethod
220     def get_num_users_by_bookshelf_by_work_id(cls, work_id):
221         """Returns a dict mapping a work_id to the
222         number of number of users who have placed that work_id in each shelf, i.e. {bookshelf_id:
223         count}.
224         """
225         oldb = db.get_db()
226         query = ("SELECT bookshelf_id, count(DISTINCT username) as user_count from bookshelves_books where"
227                  " work_id=$work_id"
228                  " GROUP BY bookshelf_id")
229         result = oldb.query(query, vars={'work_id': int(work_id)})
230         return dict([(i['bookshelf_id'], i['user_count']) for i in result]) if result else {}
231 
232     @classmethod
233     def user_with_most_books(cls):
234         """
235         Which super patrons have the most books logged?
236 
237         SELECT username, count(*) AS counted from bookshelves_books WHERE bookshelf_id=ANY('{1,3,2}'::int[]) GROUP BY username ORDER BY counted DESC, username LIMIT 10
238         """
239         oldb = db.get_db()
240         _bookshelf_ids = ','.join([str(x) for x in cls.PRESET_BOOKSHELVES.values()])
241         query = ("SELECT username, count(*) AS counted "
242                  "FROM bookshelves_books WHERE "
243                  "bookshelf_id=ANY('{" + _bookshelf_ids + "}'::int[]) "
244                  "GROUP BY username "
245                  "ORDER BY counted DESC, username LIMIT 100")
246         result = oldb.query(query)
247         return list(result)
248
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/openlibrary/core/bookshelves.py b/openlibrary/core/bookshelves.py
--- a/openlibrary/core/bookshelves.py
+++ b/openlibrary/core/bookshelves.py
@@ -187,7 +187,7 @@
         else:
             where = "work_id=$work_id AND username=$username"
             return oldb.update('bookshelves_books', where=where,
-                               bookshelf_id=bookshelf_id, vars=data)
+                               bookshelf_id=bookshelf_id, edition_id=edition_id, vars=data)
 
     @classmethod
     def remove(cls, username, work_id, bookshelf_id=None):

{"golden_diff": "diff --git a/openlibrary/core/bookshelves.py b/openlibrary/core/bookshelves.py\n--- a/openlibrary/core/bookshelves.py\n+++ b/openlibrary/core/bookshelves.py\n@@ -187,7 +187,7 @@\n         else:\n             where = \"work_id=$work_id AND username=$username\"\n             return oldb.update('bookshelves_books', where=where,\n-                               bookshelf_id=bookshelf_id, vars=data)\n+                               bookshelf_id=bookshelf_id, edition_id=edition_id, vars=data)\n \n     @classmethod\n     def remove(cls, username, work_id, bookshelf_id=None):\n", "issue": "Bug in bookshelves.py add method when changing edition\nWhen calling the add method in openlibrary/core/bookshelves.py (e.g. POSTing to bookshelves.json) with a specific edition_id, the result is not as expected if another edition of the same work already exists on the user's bookshelves. \r\n\r\n### Steps to Reproduce\r\nSubmit a POST to bookshelves.json in a work context (e.g. https://openlibrary.org/works/OL460810W/bookshelves.json) with an add action and a specific edition_id: {'action':'add', 'redir':false, 'bookshelf_id':1, 'edition_id':'/books/OL7656518M', 'dont_remove':true}\r\n\r\nIf the user already has a different edition of the work on any bookshelf:\r\n\r\n* If dont_remove is true, no change is made\r\n* If dont_remove is false, the existing edition is removed from the bookshelf\r\n\r\nExpected behavior:\r\n\r\nThe record for the work on the user's bookshelf should be updated to reflect the passed edition_id if dont_remove is true.\r\n\r\n### Details\r\n\r\n- **Logged in (Y/N)?**  Y\r\n- **Browser type/version?** N/A\r\n- **Operating system?** N/A\r\n- **Environment (prod/dev/local)?** prod\r\n<!-- If not sure, put prod -->\r\n\r\n### Proposal & Constraints\r\n\r\nChanging the update call at line 189 from\r\n\r\n`return oldb.update('bookshelves_books', where=where, bookshelf_id=bookshelf_id, vars=data)`\r\n\r\nto \r\n\r\n`return oldb.update('bookshelves_books', where=where, bookshelf_id=bookshelf_id, edition_id=edition_id, vars=data)`\r\n\r\nshould create the expected behavior without affecting other use cases.\r\n\r\n### Stakeholders\r\n@mheiman\r\n@mekarpeles\r\n\n", "before_files": [{"content": "from openlibrary.utils.dateutil import DATE_ONE_MONTH_AGO, DATE_ONE_WEEK_AGO\n\nfrom . import db\n\n\nclass Bookshelves(object):\n\n    PRESET_BOOKSHELVES = {\n        'Want to Read': 1,\n        'Currently Reading': 2,\n        'Already Read': 3\n    }\n\n    PRESET_BOOKSHELVES_JSON = {\n        'want_to_read': 1,\n        'currently_reading': 2,\n        'already_read': 3,\n    }\n\n    @classmethod\n    def summary(cls):\n        return {\n            'total_books_logged': {\n                'total': Bookshelves.total_books_logged(),\n                'month': Bookshelves.total_books_logged(since=DATE_ONE_MONTH_AGO),\n                'week': Bookshelves.total_books_logged(since=DATE_ONE_WEEK_AGO)\n            },\n            'total_users_logged': {\n                'total': Bookshelves.total_unique_users(),\n                'month': Bookshelves.total_unique_users(since=DATE_ONE_MONTH_AGO),\n                'week': Bookshelves.total_unique_users(since=DATE_ONE_WEEK_AGO)\n            }\n        }\n\n    @classmethod\n    def total_books_logged(cls, shelf_ids=None, since=None):\n        \"\"\"Returns (int) number of books logged across all Reading Log shelves (e.g. those\n        specified in PRESET_BOOKSHELVES). One may alternatively specify a\n        `list` of `shelf_ids` to isolate or span multiple\n        shelves. `since` may be used to limit the result to those\n        books logged since a specific date. Any python datetime.date\n        type should work.\n\n        Args:\n            shelf_ids (list) - one or more bookshelf_id values, see\n                also the default values specified in PRESET_BOOKSHELVES\n            since (datetime.date) - returns all logged books after date\n\n        \"\"\"\n\n        oldb = db.get_db()\n        query = \"SELECT count(*) from bookshelves_books\"\n        if shelf_ids:\n            query += \" WHERE bookshelf_id IN ($shelf_ids)\"\n            if since:\n                query += \" AND created >= $since\"\n        elif since:\n            query += \" WHERE created >= $since\"\n        results = oldb.query(query, vars={'since': since, 'shelf_ids': shelf_ids})\n        return results[0] if results else None\n\n    @classmethod\n    def total_unique_users(cls, since=None):\n        \"\"\"Returns the total number of unique users who have logged a\n        book. `since` may be provided to only return the number of users after\n        a certain datetime.date.\n        \"\"\"\n        oldb = db.get_db()\n        query = \"select count(DISTINCT username) from bookshelves_books\"\n        if since:\n            query += \" WHERE created >= $since\"\n        results = oldb.query(query, vars={'since': since})\n        return results[0] if results else None\n\n    @classmethod\n    def most_logged_books(cls, shelf_id, limit=10, since=False):\n        \"\"\"Returns a ranked list of work OLIDs (in the form of an integer --\n        i.e. OL123W would be 123) which have been most logged by\n        users. This query is limited to a specific shelf_id (e.g. 1\n        for \"Want to Read\").\n        \"\"\"\n        oldb = db.get_db()\n        query = 'select work_id, count(*) as cnt from bookshelves_books WHERE bookshelf_id=$shelf_id '\n        if since:\n            query += \" AND created >= $since\"\n        query += ' group by work_id order by cnt desc limit $limit'\n        return list(oldb.query(query, vars={'shelf_id': shelf_id, 'limit': limit, 'since': since}))\n\n    @classmethod\n    def count_total_books_logged_by_user(cls, username, bookshelf_ids=None):\n        \"\"\"Counts the (int) total number of books logged by this `username`,\n        with the option of limiting the count to specific bookshelves\n        by `bookshelf_id`\n        \"\"\"\n        return sum(cls.count_total_books_logged_by_user_per_shelf(\n            username, bookshelf_ids=bookshelf_ids).values())\n\n    @classmethod\n    def count_total_books_logged_by_user_per_shelf(cls, username, bookshelf_ids=None):\n        \"\"\"Returns a dict mapping the specified user's bookshelves_ids to the\n        number of number of books logged per each shelf, i.e. {bookshelf_id:\n        count}. By default, we limit bookshelf_ids to those in PRESET_BOOKSHELVES\n\n        TODO: add `since` to fetch books logged after a certain\n        date. Useful for following/subscribing-to users and being\n        notified of books they log. Also add to\n        count_total_books_logged_by_user\n        \"\"\"\n        oldb = db.get_db()\n        data = {'username': username}\n        _bookshelf_ids = ','.join([str(x) for x in bookshelf_ids or cls.PRESET_BOOKSHELVES.values()])\n        query = (\"SELECT bookshelf_id, count(*) from bookshelves_books WHERE \"\n                 \"bookshelf_id=ANY('{\" + _bookshelf_ids + \"}'::int[]) \"\n                 \"AND username=$username GROUP BY bookshelf_id\")\n        result = oldb.query(query, vars=data)\n        return dict([(i['bookshelf_id'], i['count']) for i in result]) if result else {}\n\n    @classmethod\n    def get_users_logged_books(cls, username, bookshelf_id=None, limit=100, page=1):\n        \"\"\"Returns a list of Reading Log database records for books which\n        the user has logged. Records are described in core/schema.py\n        and include:\n\n        username (str) - who logged this book\n        work_id (int) - the Open Library work ID as an int (e.g. OL123W becomes 123)\n        bookshelf_id (int) - the ID of the bookshelf, see: PRESET_BOOKSHELVES.\n            If bookshelf_id is None, return books from all bookshelves.\n        edition_id (int) [optional] - the specific edition logged, if applicable\n        created (datetime) - date the book was logged\n\n        \"\"\"\n        oldb = db.get_db()\n        page = int(page) if page else 1\n        data = {\n            'username': username,\n            'limit': limit,\n            'offset': limit * (page - 1),\n            'bookshelf_id': bookshelf_id\n        }\n        query = (\"SELECT * from bookshelves_books WHERE \"\n                 \"bookshelf_id=$bookshelf_id AND username=$username \"\n                 \"LIMIT $limit OFFSET $offset\")\n        if bookshelf_id is None:\n            query = (\"SELECT * from bookshelves_books WHERE \"\n                 \"username=$username\")\n            # XXX Removing limit, offset, etc from data looks like a bug\n            # unrelated / not fixing in this PR.\n            data = { 'username': username }\n        return list(oldb.query(query, vars=data))\n\n    @classmethod\n    def get_users_read_status_of_work(cls, username, work_id):\n        \"\"\"A user can mark a book as (1) want to read, (2) currently reading,\n        or (3) already read. Each of these states is mutually\n        exclusive. Returns the user's read state of this work, if one\n        exists.\n        \"\"\"\n        oldb = db.get_db()\n        data = {\n            'username': username,\n            'work_id': int(work_id)\n        }\n        bookshelf_ids = ','.join([str(x) for x in cls.PRESET_BOOKSHELVES.values()])\n        query = (\"SELECT bookshelf_id from bookshelves_books WHERE \"\n                 \"bookshelf_id=ANY('{\" + bookshelf_ids + \"}'::int[]) \"\n                 \"AND username=$username AND work_id=$work_id\")\n        result = list(oldb.query(query, vars=data))\n        return result[0].bookshelf_id if result else None\n\n    @classmethod\n    def add(cls, username, bookshelf_id, work_id, edition_id=None):\n        \"\"\"Adds a book with `work_id` to user's bookshelf designated by\n        `bookshelf_id`\"\"\"\n        oldb = db.get_db()\n        work_id = int(work_id)\n        bookshelf_id = int(bookshelf_id)\n        data = {\n            'work_id': work_id,\n            'username': username,\n        }\n\n        users_status = cls.get_users_read_status_of_work(username, work_id)\n        if not users_status:\n            return oldb.insert('bookshelves_books', username=username,\n                               bookshelf_id=bookshelf_id,\n                               work_id=work_id, edition_id=edition_id)\n        else:\n            where = \"work_id=$work_id AND username=$username\"\n            return oldb.update('bookshelves_books', where=where,\n                               bookshelf_id=bookshelf_id, vars=data)\n\n    @classmethod\n    def remove(cls, username, work_id, bookshelf_id=None):\n        oldb = db.get_db()\n        where = {\n            'username': username,\n            'work_id': int(work_id)\n        }\n        if bookshelf_id:\n            where['bookshelf_id'] = int(bookshelf_id)\n\n        try:\n            return oldb.delete('bookshelves_books',\n                               where=('work_id=$work_id AND username=$username'), vars=where)\n        except:  # we want to catch no entry exists\n            return None\n\n    @classmethod\n    def get_works_shelves(cls, work_id, lazy=False):\n        \"\"\"Bookshelves this work is on\"\"\"\n        oldb = db.get_db()\n        query = \"SELECT * from bookshelves_books where work_id=$work_id\"\n        try:\n            result = oldb.query(query, vars={'work_id': int(work_id)})\n            return result if lazy else list(result)\n        except:\n            return None\n\n    @classmethod\n    def get_num_users_by_bookshelf_by_work_id(cls, work_id):\n        \"\"\"Returns a dict mapping a work_id to the\n        number of number of users who have placed that work_id in each shelf, i.e. {bookshelf_id:\n        count}.\n        \"\"\"\n        oldb = db.get_db()\n        query = (\"SELECT bookshelf_id, count(DISTINCT username) as user_count from bookshelves_books where\"\n                 \" work_id=$work_id\"\n                 \" GROUP BY bookshelf_id\")\n        result = oldb.query(query, vars={'work_id': int(work_id)})\n        return dict([(i['bookshelf_id'], i['user_count']) for i in result]) if result else {}\n\n    @classmethod\n    def user_with_most_books(cls):\n        \"\"\"\n        Which super patrons have the most books logged?\n\n        SELECT username, count(*) AS counted from bookshelves_books WHERE bookshelf_id=ANY('{1,3,2}'::int[]) GROUP BY username ORDER BY counted DESC, username LIMIT 10\n        \"\"\"\n        oldb = db.get_db()\n        _bookshelf_ids = ','.join([str(x) for x in cls.PRESET_BOOKSHELVES.values()])\n        query = (\"SELECT username, count(*) AS counted \"\n                 \"FROM bookshelves_books WHERE \"\n                 \"bookshelf_id=ANY('{\" + _bookshelf_ids + \"}'::int[]) \"\n                 \"GROUP BY username \"\n                 \"ORDER BY counted DESC, username LIMIT 100\")\n        result = oldb.query(query)\n        return list(result)\n", "path": "openlibrary/core/bookshelves.py"}], "after_files": [{"content": "from openlibrary.utils.dateutil import DATE_ONE_MONTH_AGO, DATE_ONE_WEEK_AGO\n\nfrom . import db\n\n\nclass Bookshelves(object):\n\n    PRESET_BOOKSHELVES = {\n        'Want to Read': 1,\n        'Currently Reading': 2,\n        'Already Read': 3\n    }\n\n    PRESET_BOOKSHELVES_JSON = {\n        'want_to_read': 1,\n        'currently_reading': 2,\n        'already_read': 3,\n    }\n\n    @classmethod\n    def summary(cls):\n        return {\n            'total_books_logged': {\n                'total': Bookshelves.total_books_logged(),\n                'month': Bookshelves.total_books_logged(since=DATE_ONE_MONTH_AGO),\n                'week': Bookshelves.total_books_logged(since=DATE_ONE_WEEK_AGO)\n            },\n            'total_users_logged': {\n                'total': Bookshelves.total_unique_users(),\n                'month': Bookshelves.total_unique_users(since=DATE_ONE_MONTH_AGO),\n                'week': Bookshelves.total_unique_users(since=DATE_ONE_WEEK_AGO)\n            }\n        }\n\n    @classmethod\n    def total_books_logged(cls, shelf_ids=None, since=None):\n        \"\"\"Returns (int) number of books logged across all Reading Log shelves (e.g. those\n        specified in PRESET_BOOKSHELVES). One may alternatively specify a\n        `list` of `shelf_ids` to isolate or span multiple\n        shelves. `since` may be used to limit the result to those\n        books logged since a specific date. Any python datetime.date\n        type should work.\n\n        Args:\n            shelf_ids (list) - one or more bookshelf_id values, see\n                also the default values specified in PRESET_BOOKSHELVES\n            since (datetime.date) - returns all logged books after date\n\n        \"\"\"\n\n        oldb = db.get_db()\n        query = \"SELECT count(*) from bookshelves_books\"\n        if shelf_ids:\n            query += \" WHERE bookshelf_id IN ($shelf_ids)\"\n            if since:\n                query += \" AND created >= $since\"\n        elif since:\n            query += \" WHERE created >= $since\"\n        results = oldb.query(query, vars={'since': since, 'shelf_ids': shelf_ids})\n        return results[0] if results else None\n\n    @classmethod\n    def total_unique_users(cls, since=None):\n        \"\"\"Returns the total number of unique users who have logged a\n        book. `since` may be provided to only return the number of users after\n        a certain datetime.date.\n        \"\"\"\n        oldb = db.get_db()\n        query = \"select count(DISTINCT username) from bookshelves_books\"\n        if since:\n            query += \" WHERE created >= $since\"\n        results = oldb.query(query, vars={'since': since})\n        return results[0] if results else None\n\n    @classmethod\n    def most_logged_books(cls, shelf_id, limit=10, since=False):\n        \"\"\"Returns a ranked list of work OLIDs (in the form of an integer --\n        i.e. OL123W would be 123) which have been most logged by\n        users. This query is limited to a specific shelf_id (e.g. 1\n        for \"Want to Read\").\n        \"\"\"\n        oldb = db.get_db()\n        query = 'select work_id, count(*) as cnt from bookshelves_books WHERE bookshelf_id=$shelf_id '\n        if since:\n            query += \" AND created >= $since\"\n        query += ' group by work_id order by cnt desc limit $limit'\n        return list(oldb.query(query, vars={'shelf_id': shelf_id, 'limit': limit, 'since': since}))\n\n    @classmethod\n    def count_total_books_logged_by_user(cls, username, bookshelf_ids=None):\n        \"\"\"Counts the (int) total number of books logged by this `username`,\n        with the option of limiting the count to specific bookshelves\n        by `bookshelf_id`\n        \"\"\"\n        return sum(cls.count_total_books_logged_by_user_per_shelf(\n            username, bookshelf_ids=bookshelf_ids).values())\n\n    @classmethod\n    def count_total_books_logged_by_user_per_shelf(cls, username, bookshelf_ids=None):\n        \"\"\"Returns a dict mapping the specified user's bookshelves_ids to the\n        number of number of books logged per each shelf, i.e. {bookshelf_id:\n        count}. By default, we limit bookshelf_ids to those in PRESET_BOOKSHELVES\n\n        TODO: add `since` to fetch books logged after a certain\n        date. Useful for following/subscribing-to users and being\n        notified of books they log. Also add to\n        count_total_books_logged_by_user\n        \"\"\"\n        oldb = db.get_db()\n        data = {'username': username}\n        _bookshelf_ids = ','.join([str(x) for x in bookshelf_ids or cls.PRESET_BOOKSHELVES.values()])\n        query = (\"SELECT bookshelf_id, count(*) from bookshelves_books WHERE \"\n                 \"bookshelf_id=ANY('{\" + _bookshelf_ids + \"}'::int[]) \"\n                 \"AND username=$username GROUP BY bookshelf_id\")\n        result = oldb.query(query, vars=data)\n        return dict([(i['bookshelf_id'], i['count']) for i in result]) if result else {}\n\n    @classmethod\n    def get_users_logged_books(cls, username, bookshelf_id=None, limit=100, page=1):\n        \"\"\"Returns a list of Reading Log database records for books which\n        the user has logged. Records are described in core/schema.py\n        and include:\n\n        username (str) - who logged this book\n        work_id (int) - the Open Library work ID as an int (e.g. OL123W becomes 123)\n        bookshelf_id (int) - the ID of the bookshelf, see: PRESET_BOOKSHELVES.\n            If bookshelf_id is None, return books from all bookshelves.\n        edition_id (int) [optional] - the specific edition logged, if applicable\n        created (datetime) - date the book was logged\n\n        \"\"\"\n        oldb = db.get_db()\n        page = int(page) if page else 1\n        data = {\n            'username': username,\n            'limit': limit,\n            'offset': limit * (page - 1),\n            'bookshelf_id': bookshelf_id\n        }\n        query = (\"SELECT * from bookshelves_books WHERE \"\n                 \"bookshelf_id=$bookshelf_id AND username=$username \"\n                 \"LIMIT $limit OFFSET $offset\")\n        if bookshelf_id is None:\n            query = (\"SELECT * from bookshelves_books WHERE \"\n                 \"username=$username\")\n            # XXX Removing limit, offset, etc from data looks like a bug\n            # unrelated / not fixing in this PR.\n            data = { 'username': username }\n        return list(oldb.query(query, vars=data))\n\n    @classmethod\n    def get_users_read_status_of_work(cls, username, work_id):\n        \"\"\"A user can mark a book as (1) want to read, (2) currently reading,\n        or (3) already read. Each of these states is mutually\n        exclusive. Returns the user's read state of this work, if one\n        exists.\n        \"\"\"\n        oldb = db.get_db()\n        data = {\n            'username': username,\n            'work_id': int(work_id)\n        }\n        bookshelf_ids = ','.join([str(x) for x in cls.PRESET_BOOKSHELVES.values()])\n        query = (\"SELECT bookshelf_id from bookshelves_books WHERE \"\n                 \"bookshelf_id=ANY('{\" + bookshelf_ids + \"}'::int[]) \"\n                 \"AND username=$username AND work_id=$work_id\")\n        result = list(oldb.query(query, vars=data))\n        return result[0].bookshelf_id if result else None\n\n    @classmethod\n    def add(cls, username, bookshelf_id, work_id, edition_id=None):\n        \"\"\"Adds a book with `work_id` to user's bookshelf designated by\n        `bookshelf_id`\"\"\"\n        oldb = db.get_db()\n        work_id = int(work_id)\n        bookshelf_id = int(bookshelf_id)\n        data = {\n            'work_id': work_id,\n            'username': username,\n        }\n\n        users_status = cls.get_users_read_status_of_work(username, work_id)\n        if not users_status:\n            return oldb.insert('bookshelves_books', username=username,\n                               bookshelf_id=bookshelf_id,\n                               work_id=work_id, edition_id=edition_id)\n        else:\n            where = \"work_id=$work_id AND username=$username\"\n            return oldb.update('bookshelves_books', where=where,\n                               bookshelf_id=bookshelf_id, edition_id=edition_id, vars=data)\n\n    @classmethod\n    def remove(cls, username, work_id, bookshelf_id=None):\n        oldb = db.get_db()\n        where = {\n            'username': username,\n            'work_id': int(work_id)\n        }\n        if bookshelf_id:\n            where['bookshelf_id'] = int(bookshelf_id)\n\n        try:\n            return oldb.delete('bookshelves_books',\n                               where=('work_id=$work_id AND username=$username'), vars=where)\n        except:  # we want to catch no entry exists\n            return None\n\n    @classmethod\n    def get_works_shelves(cls, work_id, lazy=False):\n        \"\"\"Bookshelves this work is on\"\"\"\n        oldb = db.get_db()\n        query = \"SELECT * from bookshelves_books where work_id=$work_id\"\n        try:\n            result = oldb.query(query, vars={'work_id': int(work_id)})\n            return result if lazy else list(result)\n        except:\n            return None\n\n    @classmethod\n    def get_num_users_by_bookshelf_by_work_id(cls, work_id):\n        \"\"\"Returns a dict mapping a work_id to the\n        number of number of users who have placed that work_id in each shelf, i.e. {bookshelf_id:\n        count}.\n        \"\"\"\n        oldb = db.get_db()\n        query = (\"SELECT bookshelf_id, count(DISTINCT username) as user_count from bookshelves_books where\"\n                 \" work_id=$work_id\"\n                 \" GROUP BY bookshelf_id\")\n        result = oldb.query(query, vars={'work_id': int(work_id)})\n        return dict([(i['bookshelf_id'], i['user_count']) for i in result]) if result else {}\n\n    @classmethod\n    def user_with_most_books(cls):\n        \"\"\"\n        Which super patrons have the most books logged?\n\n        SELECT username, count(*) AS counted from bookshelves_books WHERE bookshelf_id=ANY('{1,3,2}'::int[]) GROUP BY username ORDER BY counted DESC, username LIMIT 10\n        \"\"\"\n        oldb = db.get_db()\n        _bookshelf_ids = ','.join([str(x) for x in cls.PRESET_BOOKSHELVES.values()])\n        query = (\"SELECT username, count(*) AS counted \"\n                 \"FROM bookshelves_books WHERE \"\n                 \"bookshelf_id=ANY('{\" + _bookshelf_ids + \"}'::int[]) \"\n                 \"GROUP BY username \"\n                 \"ORDER BY counted DESC, username LIMIT 100\")\n        result = oldb.query(query)\n        return list(result)\n", "path": "openlibrary/core/bookshelves.py"}]}