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