deletion of books now works
[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 getRemovedBooks():
127     conn = sqlite3.connect(dbFile)
128     c = conn.cursor()
129     query = "SELECT * FROM "+bookRemovedTable+";"
130     c.execute(query)
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 getBookByID(bookid):
144     conn = sqlite3.connect(dbFile)
145     c = conn.cursor()
146     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
147     c.execute(query)
148     b = c.fetchone()
149     book = {}
150     i=0
151     for k in columns:
152         if b[i]!=None:
153             book[k]=b[i]
154         i+=1
155     c.close()
156     return book
157
158
159 # removes book from catalogue
160 def removeBook(bookid):
161     conn = sqlite3.connect(dbFile)
162     c = conn.cursor()
163     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
164     c.execute(query)
165     query = "DELETE FROM " +bookCategoryTable+ " WHERE id = "+str(bookid)+";"
166     c.execute(query)
167     conn.commit()
168     c.close()
169
170 def removeBooks(books):
171     conn = sqlite3.connect(dbFile)
172     c = conn.cursor()
173     query1 = "DELETE FROM " +bookTable+ " WHERE id = :id;"
174     query2 = "DELETE FROM " +bookCategoryTable+ " WHERE id = :id;"
175     for book in books:
176         c.execute(query1, book)
177         c.execute(query2, book)
178     conn.commit()
179     c.close()
180
181 # fully deletes book from removedBooks table
182 def deleteBook(bookid):
183     conn = sqlite3.connect(dbFile)
184     c = conn.cursor()
185     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
186     c.execute(query)
187     conn.commit()
188     c.close()
189
190 def deleteBooks(books):
191     conn = sqlite3.connect(dbFile)
192     c = conn.cursor()
193     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = :id;"
194     for book in books:
195         c.execute(query, book)
196     conn.commit()
197     c.close()
198
199 #########################################
200 # Category related functions
201 ########################################
202 def categorizeBook(bookid, cat_id):
203     conn = sqlite3.connect(dbFile)
204     c = conn.cursor()
205     query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES ("+str(bookid)+", "+str(cat_id)+");"
206     conn.commit()
207     c.close()
208         
209
210 def getCategories():
211     conn = sqlite3.connect(dbFile)
212     c = conn.cursor()
213     query = "SELECT cat_id, category FROM "+categoryTable+";"
214     c.execute(query)
215     cats = []
216     for cat_id,cat in c:
217         cats.append({'id':cat_id, 'category':cat})
218     c.close()
219     return cats
220
221 def addCategory(cat):
222     conn = sqlite3.connect(dbFile)
223     c = conn.cursor()
224     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
225     c.execute(query)
226     conn.commit()
227     c.close()
228
229 def deleteCategories(cats):
230     conn = sqlite3.connect(dbFile)
231     c = conn.cursor()
232     query1 = "DELETE FROM " +categoryTable+ " WHERE cat_id = :id;"
233     query2 = "DELETE FROM " +bookCategoryTable+ " WHERE cat_id = :id;"
234     for cat in cats:
235         c.execute(query1, cat)
236         c.execute(query2, cat)
237     conn.commit()
238     c.close()
239
240 #########################################
241 # Database initialization
242 #########################################
243 def createBooksTable():
244     conn = sqlite3.connect(dbFile)
245     c = conn.cursor()
246     c.executescript(bookTableCreation)
247     conn.commit()
248     c.close()
249
250 def createTriggers():
251     conn = sqlite3.connect(dbFile)
252     c = conn.cursor()
253     c.executescript(bookTriggerCreation)
254     conn.commit()
255     c.close()
256
257
258 createBooksTable()
259 createTriggers()