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