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