can now add categories from the category viewer
[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 CREATE TABLE IF NOT EXISTS book_deleted_categories
30     (id INTEGER, cat_id INTEGER);
31 '''
32
33 columns = ['id', 'isbn', 'lccn',
34            'title', 'subtitle', 'authors', 'edition', 
35            'publisher', 'publish year', 'publish month', 'publish location', 
36            'pages', 'pagination', 'weight', 'last updated']
37
38 bookTriggerCreation = '''
39 CREATE TRIGGER IF NOT EXISTS insert_books_time AFTER INSERT ON books
40 BEGIN
41     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
42 END;
43
44 CREATE TRIGGER IF NOT EXISTS update_books_time AFTER UPDATE ON books
45 BEGIN
46     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
47 END;
48
49 CREATE TRIGGER IF NOT EXISTS delete_books_backup BEFORE DELETE ON books
50 BEGIN
51     INSERT INTO books_deleted (isbn, lccn, 
52                 title, subtitle, authors, edition, 
53                 publisher, publish_year, publish_month, publish_location, 
54                 pages, pagination, weight, last_updated)
55             SELECT isbn, lccn, 
56                    title, subtitle, authors, edition, 
57                    publisher, publish_year, publish_month, publish_location, 
58                    pages, pagination, weight, last_updated
59                    FROM books
60                    WHERE rowid = old.rowid;
61 END;
62
63 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
64 BEGIN
65     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
66 END;
67
68 CREATE TRIGGER IF NOT EXISTS delete_book_category_time AFTER DELETE ON book_categories
69 BEGIN
70     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = old.id;
71 END;
72 '''
73
74 ################################3
75 # character escaping, etc for sql queries
76 #################################
77 def colify(s):
78     return s.replace(" ","_").lower()
79
80 def stringify(v):
81     return '"' + str(v).strip().replace('"','""') + '"'
82
83 ###################################
84 # book functions
85 ##################################
86 def addBook(book):
87     conn = sqlite3.connect(dbFile)
88     c = conn.cursor()
89     cols = []
90     vals = []
91     for k,v in book.items():
92         if v!="":
93             cols.append(colify(k))
94             vals.append(stringify(v))
95     
96     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
97     c.execute(query)
98     conn.commit()
99     c.close()
100
101 def updateBook(book, bookID):
102     conn = sqlite3.connect(dbFile)
103     c = conn.cursor()
104     updates=[]
105     for k,v in book.items():
106         updates.append(colify(k)+"="+stringify(v))
107     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
108     c.execute(query)
109     conn.commit()
110     c.close()
111
112 def getBooks():
113     conn = sqlite3.connect(dbFile)
114     c = conn.cursor()
115     query = "SELECT * FROM "+bookTable+";"
116     c.execute(query)
117     books = []
118     for b in c:
119         book = {}
120         i = 0
121         for k in columns:
122             if b[i]!=None:
123                 book[k]=b[i]
124             i+=1
125         books.append(book)
126     c.close()
127     return books
128
129 def getRemovedBooks():
130     conn = sqlite3.connect(dbFile)
131     c = conn.cursor()
132     query = "SELECT * FROM "+bookRemovedTable+";"
133     c.execute(query)
134     books = []
135     for b in c:
136         book = {}
137         i = 0
138         for k in columns:
139             if b[i]!=None:
140                 book[k]=b[i]
141             i+=1
142         books.append(book)
143     c.close()
144     return books
145
146 def getBookByID(bookid):
147     conn = sqlite3.connect(dbFile)
148     c = conn.cursor()
149     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
150     c.execute(query)
151     b = c.fetchone()
152     book = {}
153     i=0
154     for k in columns:
155         if b[i]!=None:
156             book[k]=b[i]
157         i+=1
158     c.close()
159     return book
160
161
162 # removes book from catalogue
163 def removeBook(bookid):
164     conn = sqlite3.connect(dbFile)
165     c = conn.cursor()
166     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
167     c.execute(query)
168     query = "DELETE FROM " +bookCategoryTable+ " WHERE id = "+str(bookid)+";"
169     c.execute(query)
170     conn.commit()
171     c.close()
172
173 # fully deletes book from removedBooks table
174 def deleteBook(bookid):
175     conn = sqlite3.connect(dbFile)
176     c = conn.cursor()
177     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
178     c.execute(query)
179     conn.commit()
180     c.close()
181
182 #########################################
183 # Category related functions
184 ########################################
185 def categorizeBook(bookid, category):
186     conn = sqlite3.connect(dbFile)
187     c = conn.cursor()
188     if isinstance(category,str):
189         query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
190         conn.commit()
191         c.execute(query)
192         query = "SELECT cat_id FROM "+categoryTable+" WHERE category = "+stringify(category)+";"
193         c.execute(query)
194         category = c.fetchone()
195     query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES ("+str(bookid)+", "+str(category)+");"
196     conn.commit()
197     c.close()
198         
199
200 def getCategories():
201     conn = sqlite3.connect(dbFile)
202     c = conn.cursor()
203     query = "SELECT category FROM "+categoryTable+";"
204     c.execute(query)
205     cats = []
206     for category in c:
207         cats.append(category[0])
208     c.close()
209     return cats
210
211 def addCategory(cat):
212     conn = sqlite3.connect(dbFile)
213     c = conn.cursor()
214     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
215     print >>sys.stderr, query
216     c.execute(query)
217     conn.commit()
218     c.close()
219
220 #########################################
221 # Database initialization
222 #########################################
223 def createBooksTable():
224     conn = sqlite3.connect(dbFile)
225     c = conn.cursor()
226     c.executescript(bookTableCreation)
227     conn.commit()
228     c.close()
229
230 def createTriggers():
231     conn = sqlite3.connect(dbFile)
232     c = conn.cursor()
233     c.executescript(bookTriggerCreation)
234     conn.commit()
235     c.close()
236
237
238 createBooksTable()
239 createTriggers()