cc46e738d3b258f7119f7009ed503fa2acc090cd
[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(bookids):
171     conn = sqlite3.connect(dbFile)
172     c = conn.cursor()
173     query1 = "DELETE FROM " +bookTable+ " WHERE id = ?;"
174     query2 = "DELETE FROM " +bookCategoryTable+ " WHERE id = ?;"
175     for book in bookids:
176         bid=(book,)
177         c.execute(query1, bid)
178         c.execute(query2, bid)
179     conn.commit()
180     c.close()
181
182 # fully deletes book from removedBooks table
183 def deleteBook(bookid):
184     conn = sqlite3.connect(dbFile)
185     c = conn.cursor()
186     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
187     c.execute(query)
188     conn.commit()
189     c.close()
190
191 def deleteBooks(bookids):
192     conn = sqlite3.connect(dbFile)
193     c = conn.cursor()
194     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = ?;"
195     for book in bookids:
196         bid=(book,)
197         c.execute(query, bid)
198     conn.commit()
199     c.close()
200
201 #########################################
202 # Category related functions
203 ########################################
204 def categorizeBook(bookid, cat_id):
205     conn = sqlite3.connect(dbFile)
206     c = conn.cursor()
207     query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES ("+str(bookid)+", "+str(cat_id)+");"
208     conn.commit()
209     c.close()
210         
211
212 def getCategories():
213     conn = sqlite3.connect(dbFile)
214     c = conn.cursor()
215     query = "SELECT cat_id, category FROM "+categoryTable+";"
216     c.execute(query)
217     cats = []
218     for cat_id,cat in c:
219         cats.append({'id':cat_id, 'category':cat})
220     c.close()
221     return cats
222
223 def addCategory(cat):
224     conn = sqlite3.connect(dbFile)
225     c = conn.cursor()
226     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
227     print >>sys.stderr, query
228     c.execute(query)
229     conn.commit()
230     c.close()
231
232 #########################################
233 # Database initialization
234 #########################################
235 def createBooksTable():
236     conn = sqlite3.connect(dbFile)
237     c = conn.cursor()
238     c.executescript(bookTableCreation)
239     conn.commit()
240     c.close()
241
242 def createTriggers():
243     conn = sqlite3.connect(dbFile)
244     c = conn.cursor()
245     c.executescript(bookTriggerCreation)
246     conn.commit()
247     c.close()
248
249
250 createBooksTable()
251 createTriggers()