added some desired features
[public/library.git] / dbLayer.py
1 import sys
2 import sqlite3
3
4 dbFile = 'sqLibrary.db'
5 bookTable = 'books'
6 bookRemovedTable='books_deleted'
7 bookCategoryTable='book_categories'
8 bookRemovedCategoryTable='books_deleted_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, last_updated);
18
19 CREATE TABLE IF NOT EXISTS books_deleted
20     (id INTEGER PRIMARY KEY, 
21      isbn, lccn, title, subtitle, authors, edition, 
22      publisher, publish_year, publish_month, publish_location, 
23      pages, pagination, weight, last_updated);
24
25 CREATE TABLE IF NOT EXISTS categories
26     (cat_id INTEGER PRIMARY KEY, category STRING UNIQUE ON CONFLICT IGNORE);
27
28 CREATE TABLE IF NOT EXISTS book_categories
29     (id INTEGER, cat_id INTEGER);
30
31 CREATE TABLE IF NOT EXISTS books_deleted_categories
32     (id INTEGER, cat_id INTEGER);
33 '''
34
35 columns = ['id', 'isbn', 'lccn',
36            'title', 'subtitle', 'authors', 'edition', 
37            'publisher', 'publish year', 'publish month', 'publish location', 
38            'pages', 'pagination', 'weight', 'last updated']
39
40 bookTriggerCreation = '''
41 CREATE TRIGGER IF NOT EXISTS insert_books_time AFTER INSERT ON books
42 BEGIN
43     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
44 END;
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_books_backup BEFORE DELETE ON books
52 BEGIN
53     INSERT INTO books_deleted (id, isbn, lccn, 
54                 title, subtitle, authors, edition, 
55                 publisher, publish_year, publish_month, publish_location, 
56                 pages, pagination, weight, last_updated)
57             SELECT id, isbn, lccn, 
58                    title, subtitle, authors, edition, 
59                    publisher, publish_year, publish_month, publish_location, 
60                    pages, pagination, weight, last_updated
61                    FROM books
62                    WHERE rowid = old.rowid;
63     INSERT INTO books_deleted_categories (id, cat_id)
64             SELECT id, cat_id FROM book_categories WHERE id = old.rowid;
65     DELETE FROM book_categories WHERE id = old.rowid;
66 END;
67
68 CREATE TRIGGER IF NOT EXISTS delete_backup AFTER DELETE ON books_deleted
69 BEGIN
70     DELETE FROM books_deleted_categories WHERE id = old.rowid;
71 END;
72
73 CREATE TRIGGER IF NOT EXISTS delete_category AFTER DELETE ON categories
74 BEGIN
75     DELETE FROM book_categories WHERE cat_id = old.cat_id;
76     DELETE FROM books_deleted_categories WHERE cat_id = old.cat_id;
77 END;
78
79 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
80 BEGIN
81     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
82 END;
83
84 CREATE TRIGGER IF NOT EXISTS delete_book_category_time AFTER DELETE ON book_categories
85 BEGIN
86     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = old.id;
87 END;
88 '''
89
90 ################################3
91 # character escaping, etc for sql queries
92 #################################
93 def colify(s):
94     return s.replace(" ","_").lower()
95
96 def stringify(v):
97     return '"' + str(v).strip().replace('"','""') + '"'
98
99 ###################################
100 # book functions
101 ##################################
102 def addBook(book):
103     conn = sqlite3.connect(dbFile)
104     c = conn.cursor()
105     cols = []
106     vals = []
107     for k,v in book.items():
108         if v!="":
109             cols.append(colify(k))
110             vals.append(stringify(v))
111     
112     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
113     c.execute(query)
114     conn.commit()
115     c.close()
116
117 def updateBook(book, bookID):
118     conn = sqlite3.connect(dbFile)
119     c = conn.cursor()
120     updates=[]
121     for k,v in book.items():
122         updates.append(colify(k)+"="+stringify(v))
123     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
124     c.execute(query)
125     conn.commit()
126     c.close()
127
128 def getBooks():
129     conn = sqlite3.connect(dbFile)
130     c = conn.cursor()
131     query = "SELECT * FROM "+bookTable+";"
132     c.execute(query)
133     books = []
134     for b in c:
135         book = {}
136         i = 0
137         for k in columns:
138             if b[i]!=None:
139                 book[k]=b[i]
140             i+=1
141         books.append(book)
142     c.close()
143     return books
144
145 def getBooksByCategory(cat):
146     conn = sqlite3.connect(dbFile)
147     c = conn.cursor()
148     query = "SELECT "+",".join(map(colify,columns))+" FROM "+bookTable+" JOIN "+bookCategoryTable+" USING (id) WHERE cat_id = :id;"
149     c.execute(query,cat)
150     books = []
151     for b in c:
152         book = {}
153         i = 0
154         for k in columns:
155             if b[i]!=None:
156                 book[k]=b[i]
157             i+=1
158         books.append(book)
159     c.close()
160     return books
161
162 def getRemovedBooks():
163     conn = sqlite3.connect(dbFile)
164     c = conn.cursor()
165     query = "SELECT * FROM "+bookRemovedTable+";"
166     c.execute(query)
167     books = []
168     for b in c:
169         book = {}
170         i = 0
171         for k in columns:
172             if b[i]!=None:
173                 book[k]=b[i]
174             i+=1
175         books.append(book)
176     c.close()
177     return books
178
179 def getBookByID(bookid):
180     conn = sqlite3.connect(dbFile)
181     c = conn.cursor()
182     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
183     c.execute(query)
184     b = c.fetchone()
185     book = {}
186     i=0
187     for k in columns:
188         if b[i]!=None:
189             book[k]=b[i]
190         i+=1
191     c.close()
192     return book
193
194
195 # removes book from catalogue
196 def removeBook(bookid):
197     conn = sqlite3.connect(dbFile)
198     c = conn.cursor()
199     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
200     c.execute(query)
201     query = "DELETE FROM " +bookCategoryTable+ " WHERE id = "+str(bookid)+";"
202     c.execute(query)
203     conn.commit()
204     c.close()
205
206 def removeBooks(books):
207     conn = sqlite3.connect(dbFile)
208     c = conn.cursor()
209     query1 = "DELETE FROM " +bookTable+ " WHERE id = :id;"
210     query2 = "DELETE FROM " +bookCategoryTable+ " WHERE id = :id;"
211     for book in books:
212         c.execute(query1, book)
213         c.execute(query2, book)
214     conn.commit()
215     c.close()
216
217 # restores trashed books
218 def restoreBooks(books):
219     conn = sqlite3.connect(dbFile)
220     c = conn.cursor()
221     query1 =  "INSERT INTO "+bookTable+" ("+",".join(map(colify,columns))+") SELECT "+",".join(map(colify,columns))+" FROM "+bookRemovedTable+" WHERE id = :id;"
222     query2 =  "INSERT INTO "+bookCategoryTable+" (id, cat_id) SELECT id,cat_id FROM "+bookRemovedCategoryTable+" WHERE id = :id;"
223     query3 = "DELETE FROM " +bookRemovedTable+ " WHERE id = :id;"
224     for book in books:
225         c.execute(query1,book)
226         c.execute(query2,book)
227         c.execute(query3,book)
228     conn.commit()
229     c.close()
230
231 # fully deletes book from removedBooks table
232 def deleteBook(bookid):
233     conn = sqlite3.connect(dbFile)
234     c = conn.cursor()
235     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
236     c.execute(query)
237     conn.commit()
238     c.close()
239
240 def deleteBooks(books):
241     conn = sqlite3.connect(dbFile)
242     c = conn.cursor()
243     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = :id;"
244     for book in books:
245         c.execute(query, book)
246     conn.commit()
247     c.close()
248
249 #########################################
250 # Category related functions
251 ########################################
252 def getBookCategories(book):
253     conn = sqlite3.connect(dbFile)
254     c = conn.cursor()
255     query = "SELECT id,cat_id,category FROM "+bookCategoryTable+" JOIN "+categoryTable+" USING (cat_id) WHERE id = :id ;"
256     c.execute(query,book)
257     cats = []
258     for book_id,cat_id,cat_name in c:
259         cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
260     c.close()
261     return cats
262
263 def categorizeBook(book, cats):
264     conn = sqlite3.connect(dbFile)
265     c = conn.cursor()
266     query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES (?, ?);"
267     for cat in cats:
268         args = (book['id'],cat['id'])
269         c.execute(query,args)
270     conn.commit()
271     c.close()
272
273 def uncategorizeBook(book, cats):
274     conn = sqlite3.connect(dbFile)
275     c = conn.cursor()
276     query = "DELETE FROM "+bookCategoryTable+" WHERE (id = ? AND cat_id = ?);"
277     for cat in cats:
278         args = (book['id'],cat['id'])
279         c.execute(query,args)
280     conn.commit()
281     c.close()
282
283 def getCategories():
284     conn = sqlite3.connect(dbFile)
285     c = conn.cursor()
286     query = "SELECT cat_id, category FROM "+categoryTable+";"
287     c.execute(query)
288     cats = []
289     for cat_id,cat in c:
290         cats.append({'id':cat_id, 'category':cat})
291     c.close()
292     return cats
293
294 def addCategory(cat):
295     conn = sqlite3.connect(dbFile)
296     c = conn.cursor()
297     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
298     c.execute(query)
299     conn.commit()
300     c.close()
301
302 def deleteCategories(cats):
303     conn = sqlite3.connect(dbFile)
304     c = conn.cursor()
305     query1 = "DELETE FROM " +categoryTable+ " WHERE cat_id = :id;"
306     for cat in cats:
307         c.execute(query1, cat)
308     conn.commit()
309     c.close()
310
311 #########################################
312 # Database initialization
313 #########################################
314 def createBooksTable():
315     conn = sqlite3.connect(dbFile)
316     c = conn.cursor()
317     c.executescript(bookTableCreation)
318     conn.commit()
319     c.close()
320
321 def createTriggers():
322     conn = sqlite3.connect(dbFile)
323     c = conn.cursor()
324     c.executescript(bookTriggerCreation)
325     conn.commit()
326     c.close()
327
328
329 createBooksTable()
330 createTriggers()