Categorizing and uncategorizing based on selection
[public/library.git] / library / database.py
1 import sqlite3
2
3 from library import permissions
4 from library.exceptions import *
5
6 # because of the way that SQLite works we need to have these two
7 # files be different, because the Office staff needs read/write
8 # permission to the directory that contains the checkout.db file.
9 # (sqlite needs to create temporary files in that directory)
10
11 _catalogue_db_file = '/users/libcom/catalogue.db'
12 _book_table = 'books'
13 _book_category_table='book_categories'
14 _category_table = 'categories'
15
16 _checkout_db_file = '/users/libcom/checkout/checkout.db'
17 _checkout_table = 'checked_out'
18 _return_table = 'returned'
19
20 _checkout_table_creation = '''
21 CREATE TABLE IF NOT EXISTS checked_out
22     (id INTEGER UNIQUE, uwid STRING, date_out DATETIME DEFAULT current_timestamp);
23
24 CREATE TABLE IF NOT EXISTS returned
25     (id INTEGER, uwid STRING, date_out DATETIME, date_in DATETIME DEFAULT current_timestamp);
26 '''
27
28 _book_table_creation = '''
29 CREATE TABLE IF NOT EXISTS books
30     (id INTEGER PRIMARY KEY AUTOINCREMENT, 
31      isbn, lccn, title, subtitle, authors, edition, 
32      publisher, publish_year, publish_month, publish_location, 
33      pages, pagination, weight,
34      last_updated DATETIME DEFAULT current_timestamp,
35      deleted BOOLEAN DEFAULT 0);
36
37 CREATE TABLE IF NOT EXISTS categories
38     (cat_id INTEGER PRIMARY KEY, category STRING UNIQUE ON CONFLICT IGNORE);
39
40 CREATE TABLE IF NOT EXISTS book_categories
41     (id INTEGER, cat_id INTEGER);
42 '''
43
44 columns = ['id', 'isbn', 'lccn',
45            'title', 'subtitle', 'authors', 'edition', 
46            'publisher', 'publish year', 'publish month', 'publish location', 
47            'pages', 'pagination', 'weight', 'last updated', 'deleted']
48
49 _book_trigger_creation = '''
50
51 CREATE TRIGGER IF NOT EXISTS update_books_time AFTER UPDATE ON books
52 BEGIN
53     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
54 END;
55
56 CREATE TRIGGER IF NOT EXISTS delete_book AFTER DELETE ON books
57 BEGIN
58     DELETE FROM book_categories WHERE id = old.rowid;
59 END;
60
61 CREATE TRIGGER IF NOT EXISTS delete_category AFTER DELETE ON categories
62 BEGIN
63     DELETE FROM book_categories WHERE cat_id = old.cat_id;
64 END;
65
66 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT
67 ON book_categories
68 BEGIN
69     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
70 END;
71 '''
72
73 #################################
74 # character escaping, etc for sql queries
75 #################################
76 def _colify(s):
77     return s.replace(" ","_").lower()
78
79 def _stringify(v):
80     return '"' + str(v).strip().replace('"','""') + '"'
81
82 ###################################
83 # book functions
84 ##################################
85 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
86 def addBook(book):
87     conn = sqlite3.connect(_catalogue_db_file)
88     c = conn.cursor()
89     cols = []
90     vals = []
91     for k,v in book.items():
92         if v!="":
93             cols.append(_colify(k))
94             vals.append(_stringify(v))
95
96     query = ("INSERT INTO "+_book_table+" ("+", ".join(cols)+") VALUES ("+
97              ", ".join(vals)+");")
98     c.execute(query)
99     conn.commit()
100     c.close()
101
102 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
103 def updateBook(book, bookID):
104     '''
105     Takes book attribute dictionary and a string representating the book ID
106     number, and returns updates the book accordingly
107     '''
108     conn = sqlite3.connect(_catalogue_db_file)
109     c = conn.cursor()
110     updates=[]
111     for k,v in book.items():
112         updates.append(_colify(k)+"="+_stringify(v))
113     query = ("UPDATE "+_book_table+" SET " +  ", ".join(updates)+" WHERE id = " +
114              str(bookID)+";")
115     c.execute(query)
116     conn.commit()
117     c.close()
118
119 def get_books():
120     conn = sqlite3.connect(_catalogue_db_file)
121     c = conn.cursor()
122     query = "SELECT * FROM "+_book_table+" WHERE deleted=0;"
123     c.execute(query)
124     books = [_query_to_book(b) for b in c]
125     c.close()
126     return books
127
128 def getBooksByCategory(cat):
129     '''
130     Takes a string representating the category ID number, and returns
131     non-deleted books in that category
132     '''
133     conn = sqlite3.connect(_catalogue_db_file)
134     c = conn.cursor()
135     query = ("SELECT "+",".join(map(_colify,columns))+" FROM "+_book_table+
136              " JOIN "+_book_category_table+
137              " USING (id) WHERE cat_id = :id AND deleted=0;")
138     c.execute(query,cat)
139     books = [_query_to_book(b) for b in c]
140     c.close()
141     return books
142
143 def getRemovedBooks():
144     conn = sqlite3.connect(_catalogue_db_file)
145     c = conn.cursor()
146     query = "SELECT * FROM "+_book_table+" WHERE DELETED=1;"
147     c.execute(query)
148     books = [_query_to_book(b) for b in c]
149     c.close()
150     return books
151
152 def getUncategorizedBooks():
153     conn = sqlite3.connect(_catalogue_db_file)
154     c = conn.cursor()
155     query = ("SELECT "+",".join(map(_colify,columns))+" FROM "+_book_table+
156              " WHERE id NOT IN (SELECT id FROM "+_book_category_table+")"+
157              " AND deleted=0;")
158     c.execute(query)
159     books = [_query_to_book(b) for b in c]
160     c.close()
161     return books
162
163
164 def get_book(bookid):
165     conn = sqlite3.connect(_catalogue_db_file)
166     c = conn.cursor()
167     query = "SELECT * FROM "+_book_table+" WHERE id = "+str(bookid)+";"
168     c.execute(query)
169     book = _query_to_book(c.fetchone())
170     c.close()
171     return book
172
173 # removes book from catalogue
174 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
175 def removeBook(bookid):
176     conn = sqlite3.connect(_catalogue_db_file)
177     c = conn.cursor()
178     query = "UPDATE " +_book_table+ " SET deleted=1 WHERE id = "+str(bookid)+";"
179     c.execute(query)
180     conn.commit()
181     c.close()
182
183 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
184 def removeBooks(books):
185     conn = sqlite3.connect(_catalogue_db_file)
186     c = conn.cursor()
187     query1 = "UPDATE " +_book_table+ " SET deleted=1 WHERE id = :id;"
188     for book in books:
189         c.execute(query1, book)
190     conn.commit()
191     c.close()
192
193 # restores trashed books
194 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
195 def restoreBooks(books):
196     conn = sqlite3.connect(_catalogue_db_file)
197     c = conn.cursor()
198     query1 = "UPDATE " +_book_table+ " SET deleted=0 WHERE id = :id;"
199     for book in books:
200         c.execute(query1,book)
201     conn.commit()
202     c.close()
203
204 # fully deletes book from books table
205 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
206 def deleteBook(bookid):
207     conn = sqlite3.connect(_catalogue_db_file)
208     c = conn.cursor()
209     query = "DELETE FROM " +_book_table+ " WHERE id = "+str(bookid)+";"
210     c.execute(query)
211     conn.commit()
212     c.close()
213
214 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
215 def deleteBooks(books):
216     conn = sqlite3.connect(_catalogue_db_file)
217     c = conn.cursor()
218     query = "DELETE FROM " +_book_table+ " WHERE id = :id;"
219     for book in books:
220         c.execute(query, book)
221     conn.commit()
222     c.close()
223
224 def _query_to_book(book_query):
225     # Make a dict out of column name and query results.
226     # Empty entries return None, which are removed from the dict.
227     return dict(filter(lambda t:t[1], zip(columns,book_query)))
228
229 def _query_to_book_checkout(book_query):
230     # Make a dict out of column name and query results.
231     # Empty entries return None, which are removed from the dict.
232     b = _query_to_book(book_query)
233     b['uwid'] = book_query[-2]
234     b['date'] = book_query[-1]
235     return b
236
237 #########################################
238 # Category related functions
239 ########################################
240 def getBookCategories(book):
241     conn = sqlite3.connect(_catalogue_db_file)
242     c = conn.cursor()
243     query = ("SELECT id,cat_id,category FROM "+_book_category_table+" JOIN "+
244              _category_table+" USING (cat_id) WHERE id = :id ;")
245     c.execute(query,book)
246     cats = []
247     for book_id,cat_id,cat_name in c:
248         cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
249     c.close()
250     return cats
251
252 def isStickered(book):
253     cats = getBookCategories(book)
254     for c in cats:
255         if c['category'] == "Stickered":
256             return True
257     return False
258
259 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
260 def categorizeBook(book, cats):
261     conn = sqlite3.connect(_catalogue_db_file)
262     c = conn.cursor()
263     query = ("INSERT OR IGNORE INTO "+_book_category_table+
264              " (id,cat_id) VALUES (?, ?);")
265     for cat in cats:
266         args = (book['id'],cat['id'])
267         c.execute(query,args)
268     conn.commit()
269     c.close()
270
271 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
272 def categorizeBooks(cat, books):
273     conn = sqlite3.connect(_catalogue_db_file)
274     c = conn.cursor()
275     query = ("INSERT OR IGNORE INTO "+_book_category_table+
276              " (id,cat_id) VALUES (?, ?);")
277     exists_query = "SELECT * FROM "+_book_category_table+" WHERE (id = ? AND cat_id = ?);"
278     for book in books:
279         args = (book['id'],cat['id'])
280         c.execute(exists_query, args)
281         if len(c.fetchall()) == 0:
282             c.execute(query,args)
283     conn.commit()
284     c.close()
285
286
287 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
288 def uncategorizeBook(book, cats):
289     conn = sqlite3.connect(_catalogue_db_file)
290     c = conn.cursor()
291     query = "DELETE FROM "+_book_category_table+" WHERE (id = ? AND cat_id = ?);"
292     for cat in cats:
293         args = (book['id'],cat['id'])
294         c.execute(query,args)
295     conn.commit()
296     c.close()
297
298 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
299 def uncategorizeBooks(books, cat):
300     conn = sqlite3.connect(_catalogue_db_file)
301     c = conn.cursor()
302     query = "DELETE FROM "+_book_category_table+" WHERE (id = ? AND cat_id = ?);"
303     for book in books:
304         args = (book['id'],cat['id'])
305         c.execute(query,args)
306     conn.commit()
307     c.close()
308
309 def getCategories():
310     conn = sqlite3.connect(_catalogue_db_file)
311     c = conn.cursor()
312     query = "SELECT cat_id, category FROM "+_category_table+";"
313     c.execute(query)
314     cats = []
315     for cat_id,cat in c:
316         cats.append({'id':cat_id, 'category':cat})
317     c.close()
318     return cats
319
320 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
321 def addCategory(cat):
322     conn = sqlite3.connect(_catalogue_db_file)
323     c = conn.cursor()
324     query = ("INSERT OR IGNORE INTO "+_category_table+" (category) VALUES ("
325              +_stringify(cat)+");")
326     c.execute(query)
327     conn.commit()
328     c.close()
329
330 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
331 def deleteCategories(cats):
332     conn = sqlite3.connect(_catalogue_db_file)
333     c = conn.cursor()
334     query1 = "DELETE FROM " +_category_table+ " WHERE cat_id = :id;"
335     for cat in cats:
336         c.execute(query1, cat)
337     conn.commit()
338     c.close()
339
340 #########################################
341 # Book Checkout functions
342 #########################################
343 @permissions.check_permissions(permissions.PERMISSION_OFFICE)
344 def checkout_book(book_id, uwid):
345     book = get_book(book_id)
346     if isStickered(book):
347         raise StickeredError()
348     conn = sqlite3.connect(_checkout_db_file)
349     c = conn.cursor()
350
351     try:
352         query = "INSERT INTO " + _checkout_table + " (id, uwid) VALUES (?, ?);"
353         c.execute(query, (book_id, uwid))
354     except sqlite3.IntegrityError:
355         raise CheckoutError()
356     finally:
357         conn.commit()
358         c.close()
359
360 def get_checkout_status(book_id):
361     conn = sqlite3.connect(_checkout_db_file)
362     c = conn.cursor()
363     query = "SELECT uwid,date_out FROM "+ _checkout_table + " WHERE id = :id ;"
364     c.execute(query, {"id": book_id})
365     data = c.fetchone()
366     c.close()
367     if data: return {"uwid":data[0], "date_out":data[1]}
368     return None
369
370 @permissions.check_permissions(permissions.PERMISSION_OFFICE)
371 def return_book(book_id):
372     conn = sqlite3.connect(_checkout_db_file)
373     c = conn.cursor()
374     query = "SELECT uwid,date_out FROM "+ _checkout_table + " WHERE id = :id ;"
375     c.execute(query, {"id": book_id})
376     tmp = c.fetchone()
377     uwid = tmp[0]
378     date_out = tmp[1]
379     query = "INSERT INTO " + _return_table + " (id, uwid, date_out) VALUES (?, ?, ?);"
380     query2 = "DELETE FROM " + _checkout_table + " WHERE id= :id ;"
381     c.execute(query, (book_id, uwid, date_out))
382     c.execute(query2, {"id": book_id});
383     conn.commit()
384     c.close()
385
386 def get_checkedout_books():
387     '''
388     retrieves checked out books. The returned books also have the fields
389     uwid: ID of person who signed out the book, and
390     date: date when the book was checked out
391     '''
392     conn = sqlite3.connect(_catalogue_db_file)
393     c = conn.cursor()
394     query = 'ATTACH "' + _checkout_db_file + '" AS co'
395     c.execute(query)
396     query = ("SELECT "+",".join(map(_colify,columns))+",uwid,date_out FROM "+_book_table+
397              " JOIN co."+_checkout_table+
398              " USING (id) ;")
399     c.execute(query)
400     books = [_query_to_book_checkout(b) for b in c]
401     c.close()
402     return books
403
404 def get_onshelf_books():
405     '''
406     retrieves checked out books. The returned books also have the fields
407     uwid: ID of person who signed out the book, and
408     date: date when the book was checked out
409     '''
410     conn = sqlite3.connect(_catalogue_db_file)
411     c = conn.cursor()
412     query = 'ATTACH "' + _checkout_db_file + '" AS co'
413     c.execute(query)
414     query = ("SELECT "+",".join(map(_colify,columns))+" FROM "+_book_table+
415              " LEFT JOIN co."+_checkout_table+
416              " USING (id) WHERE uwid ISNULL;")
417     c.execute(query)
418     books = [_query_to_book(b) for b in c]
419     c.close()
420     return books
421
422 #########################################
423 # Database initialization
424 #########################################
425 def _createBooksTable():
426     conn = sqlite3.connect(_catalogue_db_file)
427     c = conn.cursor()
428     c.executescript(_book_table_creation)
429     conn.commit()
430     c.close()
431
432 def _createTriggers():
433     conn = sqlite3.connect(_catalogue_db_file)
434     c = conn.cursor()
435     c.executescript(_book_trigger_creation)
436     conn.commit()
437     c.close()
438
439 def _create_checkout_table():
440     conn = sqlite3.connect(_checkout_db_file)
441     c = conn.cursor()
442     c.executescript(_checkout_table_creation)
443     conn.commit()
444     c.close()
445
446 def initializeDatabase():
447     _createBooksTable()
448     _createTriggers()
449     _create_checkout_table()