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