c926b1996a9e6ba18c2b22788c7fa34849f131b1
[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 #########################################
213 # Category related functions
214 ########################################
215 def getBookCategories(book):
216     conn = sqlite3.connect(_catalogue_db_file)
217     c = conn.cursor()
218     query = ("SELECT id,cat_id,category FROM "+_book_category_table+" JOIN "+
219              _category_table+" USING (cat_id) WHERE id = :id ;")
220     c.execute(query,book)
221     cats = []
222     for book_id,cat_id,cat_name in c:
223         cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
224     c.close()
225     return cats
226
227 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
228 def categorizeBook(book, cats):
229     conn = sqlite3.connect(_catalogue_db_file)
230     c = conn.cursor()
231     query = ("INSERT OR IGNORE INTO "+_book_category_table+
232              " (id,cat_id) VALUES (?, ?);")
233     for cat in cats:
234         args = (book['id'],cat['id'])
235         c.execute(query,args)
236     conn.commit()
237     c.close()
238
239 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
240 def uncategorizeBook(book, cats):
241     conn = sqlite3.connect(_catalogue_db_file)
242     c = conn.cursor()
243     query = "DELETE FROM "+_book_category_table+" WHERE (id = ? AND cat_id = ?);"
244     for cat in cats:
245         args = (book['id'],cat['id'])
246         c.execute(query,args)
247     conn.commit()
248     c.close()
249
250 def getCategories():
251     conn = sqlite3.connect(_catalogue_db_file)
252     c = conn.cursor()
253     query = "SELECT cat_id, category FROM "+_category_table+";"
254     c.execute(query)
255     cats = []
256     for cat_id,cat in c:
257         cats.append({'id':cat_id, 'category':cat})
258     c.close()
259     return cats
260
261 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
262 def addCategory(cat):
263     conn = sqlite3.connect(_catalogue_db_file)
264     c = conn.cursor()
265     query = ("INSERT OR IGNORE INTO "+_category_table+" (category) VALUES ("
266              +_stringify(cat)+");")
267     c.execute(query)
268     conn.commit()
269     c.close()
270
271 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
272 def deleteCategories(cats):
273     conn = sqlite3.connect(_catalogue_db_file)
274     c = conn.cursor()
275     query1 = "DELETE FROM " +_category_table+ " WHERE cat_id = :id;"
276     for cat in cats:
277         c.execute(query1, cat)
278     conn.commit()
279     c.close()
280
281 #########################################
282 # Book Checkout functions
283 #########################################
284 @permissions.check_permissions(permissions.PERMISSION_OFFICE)
285 def checkout_book(book_id, uwid):
286     conn = sqlite3.connect(_checkout_db_file)
287     c = conn.cursor()
288     query = "INSERT INTO " + _checkout_table + " (id, uwid) VALUES (?, ?);"
289     c.execute(query, (book_id, uwid))
290     conn.commit()
291     c.close()
292
293 @permissions.check_permissions(permissions.PERMISSION_OFFICE)
294 def return_book(book_id):
295     conn = sqlite3.connect(_checkout_db_file)
296     c = conn.cursor()
297     query = "SELECT uwid,date_out FROM "+ _checkout_table + " WHERE id = :id ;"
298     c.execute(query, {"id": book_id})
299     tmp = c.fetchone()
300     uwid = tmp[0]
301     date_out = tmp[1]
302     query = "INSERT INTO " + _return_table + " (id, uwid, date_out) VALUES (?, ?, ?);"
303     query2 = "DELETE FROM " + _checkout_table + " WHERE id= :id ;"
304     c.execute(query, (book_id, uwid, date_out))
305     c.execute(query2, {"id": book_id});
306     conn.commit()
307     c.close()
308
309 #########################################
310 # Database initialization
311 #########################################
312 def _createBooksTable():
313     conn = sqlite3.connect(_catalogue_db_file)
314     c = conn.cursor()
315     c.executescript(_book_table_creation)
316     conn.commit()
317     c.close()
318
319 def _createTriggers():
320     conn = sqlite3.connect(_catalogue_db_file)
321     c = conn.cursor()
322     c.executescript(_book_trigger_creation)
323     conn.commit()
324     c.close()
325
326 def _create_checkout_table():
327     conn = sqlite3.connect(_checkout_db_file)
328     c = conn.cursor()
329     c.executescript(_checkout_table_creation)
330     conn.commit()
331     c.close()
332
333 def initializeDatabase():
334     _createBooksTable()
335     _createTriggers()
336     _create_checkout_table()