Search forward and back now handles case-sensitive
[public/library.git] / db_layer.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 = [_query_to_book(b) for b in c]
96     c.close()
97     return books
98
99 def getBooksByCategory(cat):
100     conn = sqlite3.connect(dbFile)
101     c = conn.cursor()
102     query = "SELECT "+",".join(map(colify,columns))+" FROM "+bookTable+" JOIN "+bookCategoryTable+" USING (id) WHERE cat_id = :id AND deleted=0;"
103     c.execute(query,cat)
104     books = [_query_to_book(b) for b in c]
105     c.close()
106     return books
107
108 def getRemovedBooks():
109     conn = sqlite3.connect(dbFile)
110     c = conn.cursor()
111     query = "SELECT * FROM "+bookTable+" WHERE DELETED=1;"
112     c.execute(query)
113     books = [_query_to_book(b) for b in c]
114     c.close()
115     return books
116
117 def getBookByID(bookid):
118     conn = sqlite3.connect(dbFile)
119     c = conn.cursor()
120     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
121     c.execute(query)
122     book = _query_to_book(c.fetchone())
123     c.close()
124     return book
125
126 # removes book from catalogue
127 def removeBook(bookid):
128     conn = sqlite3.connect(dbFile)
129     c = conn.cursor()
130     query = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = "+str(bookid)+";"
131     c.execute(query)
132     conn.commit()
133     c.close()
134
135 def removeBooks(books):
136     conn = sqlite3.connect(dbFile)
137     c = conn.cursor()
138     query1 = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = :id;"
139     for book in books:
140         c.execute(query1, book)
141     conn.commit()
142     c.close()
143
144 # restores trashed books
145 def restoreBooks(books):
146     conn = sqlite3.connect(dbFile)
147     c = conn.cursor()
148     query1 = "UPDATE " +bookTable+ " SET deleted=0 WHERE id = :id;"
149     for book in books:
150         c.execute(query1,book)
151     conn.commit()
152     c.close()
153
154 # fully deletes book from removedBooks table
155 def deleteBook(bookid):
156     conn = sqlite3.connect(dbFile)
157     c = conn.cursor()
158     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
159     c.execute(query)
160     conn.commit()
161     c.close()
162
163 def deleteBooks(books):
164     conn = sqlite3.connect(dbFile)
165     c = conn.cursor()
166     query = "DELETE FROM " +bookTable+ " WHERE id = :id;"
167     for book in books:
168         c.execute(query, book)
169     conn.commit()
170     c.close()
171
172 def _query_to_book(book_query):
173     # Make a dict out of column name and query results.
174     # Empty entries return None, which are removed from the dict.
175     return dict(filter(lambda t:t[1], zip(columns,book_query)))
176
177 #########################################
178 # Category related functions
179 ########################################
180 def getBookCategories(book):
181     conn = sqlite3.connect(dbFile)
182     c = conn.cursor()
183     query = "SELECT id,cat_id,category FROM "+bookCategoryTable+" JOIN "+categoryTable+" USING (cat_id) WHERE id = :id ;"
184     c.execute(query,book)
185     cats = []
186     for book_id,cat_id,cat_name in c:
187         cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
188     c.close()
189     return cats
190
191 def categorizeBook(book, cats):
192     conn = sqlite3.connect(dbFile)
193     c = conn.cursor()
194     query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES (?, ?);"
195     for cat in cats:
196         args = (book['id'],cat['id'])
197         c.execute(query,args)
198     conn.commit()
199     c.close()
200
201 def uncategorizeBook(book, cats):
202     conn = sqlite3.connect(dbFile)
203     c = conn.cursor()
204     query = "DELETE FROM "+bookCategoryTable+" WHERE (id = ? AND cat_id = ?);"
205     for cat in cats:
206         args = (book['id'],cat['id'])
207         c.execute(query,args)
208     conn.commit()
209     c.close()
210
211 def getCategories():
212     conn = sqlite3.connect(dbFile)
213     c = conn.cursor()
214     query = "SELECT cat_id, category FROM "+categoryTable+";"
215     c.execute(query)
216     cats = []
217     for cat_id,cat in c:
218         cats.append({'id':cat_id, 'category':cat})
219     c.close()
220     return cats
221
222 def addCategory(cat):
223     conn = sqlite3.connect(dbFile)
224     c = conn.cursor()
225     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
226     c.execute(query)
227     conn.commit()
228     c.close()
229
230 def deleteCategories(cats):
231     conn = sqlite3.connect(dbFile)
232     c = conn.cursor()
233     query1 = "DELETE FROM " +categoryTable+ " WHERE cat_id = :id;"
234     for cat in cats:
235         c.execute(query1, cat)
236     conn.commit()
237     c.close()
238
239 #########################################
240 # Database initialization
241 #########################################
242 def createBooksTable():
243     conn = sqlite3.connect(dbFile)
244     c = conn.cursor()
245     c.executescript(bookTableCreation)
246     conn.commit()
247     c.close()
248
249 def createTriggers():
250     conn = sqlite3.connect(dbFile)
251     c = conn.cursor()
252     c.executescript(bookTriggerCreation)
253     conn.commit()
254     c.close()
255
256
257 createBooksTable()
258 createTriggers()