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