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