more dbLayer category additions
[public/library.git] / dbLayer.py
1 import sqlite3
2
3 dbFile = 'sqLibrary.db'
4 bookTable = 'books'
5 bookRemovedTable='books_deleted'
6 bookCategoryTable='book_categories'
7 categoryTable = 'categories'
8
9 bookTableCreation = '''
10 CREATE TABLE IF NOT EXISTS books
11     (id INTEGER PRIMARY KEY, 
12      isbn, lccn, title, subtitle, authors, edition, 
13      publisher, publish_year, publish_month, publish_location, 
14      pages, pagination, weight, last_updated);
15
16 CREATE TABLE IF NOT EXISTS books_deleted
17     (id INTEGER PRIMARY KEY, 
18      isbn, lccn, title, subtitle, authors, edition, 
19      publisher, publish_year, publish_month, publish_location, 
20      pages, pagination, weight, last_updated);
21
22 CREATE TABLE IF NOT EXISTS categories
23     (cat_id INTEGER PRIMARY KEY, category STRING);
24
25 CREATE TABLE IF NOT EXISTS book_categories
26     (id INTEGER, cat_id INTEGER);
27
28 CREATE TABLE IF NOT EXISTS book_deleted_categories
29     (id INTEGER, cat_id INTEGER);
30 '''
31
32 columns = ['id', 'isbn', 'lccn',
33            'title', 'subtitle', 'authors', 'edition', 
34            'publisher', 'publish year', 'publish month', 'publish location', 
35            'pages', 'pagination', 'weight', 'last updated']
36
37 bookTriggerCreation = '''
38 CREATE TRIGGER IF NOT EXISTS insert_books_time AFTER INSERT ON books
39 BEGIN
40     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
41 END;
42
43 CREATE TRIGGER IF NOT EXISTS update_books_time AFTER UPDATE ON books
44 BEGIN
45     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
46 END;
47
48 CREATE TRIGGER IF NOT EXISTS delete_books_backup BEFORE DELETE ON books
49 BEGIN
50     INSERT INTO books_deleted (isbn, lccn, 
51                 title, subtitle, authors, edition, 
52                 publisher, publish_year, publish_month, publish_location, 
53                 pages, pagination, weight, last_updated)
54             SELECT isbn, lccn, 
55                    title, subtitle, authors, edition, 
56                    publisher, publish_year, publish_month, publish_location, 
57                    pages, pagination, weight, last_updated
58                    FROM books
59                    WHERE rowid = old.rowid;
60 END;
61
62 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
63 BEGIN
64     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
65 END;
66
67 CREATE TRIGGER IF NOT EXISTS delete_book_category_time AFTER DELETE ON book_categories
68 BEGIN
69     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = old.id;
70 END;
71 '''
72
73 def colify(s):
74     return s.replace(" ","_").lower()
75
76 # escapes strings and such
77 def stringify(v):
78     return '"' + str(v).strip().replace('"','""') + '"'
79
80 def addBook(book):
81     conn = sqlite3.connect(dbFile)
82     c = conn.cursor()
83     cols = []
84     vals = []
85     for k,v in book.items():
86         if v!="":
87             cols.append(colify(k))
88             vals.append(stringify(v))
89     
90     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
91     c.execute(query)
92     conn.commit()
93     c.close()
94
95 def updateBook(book, bookID):
96     conn = sqlite3.connect(dbFile)
97     c = conn.cursor()
98     updates=[]
99     for k,v in book.items():
100         updates.append(colify(k)+"="+stringify(v))
101     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
102     c.execute(query)
103     conn.commit()
104     c.close()
105
106 def getBooks():
107     conn = sqlite3.connect(dbFile)
108     c = conn.cursor()
109     query = "SELECT * FROM "+bookTable+";"
110     c.execute(query)
111     books = []
112     for b in c:
113         book = {}
114         i = 0
115         for k in columns:
116             if b[i]!=None:
117                 book[k]=b[i]
118             i+=1
119         books.append(book)
120     c.close()
121     return books
122
123 def getRemovedBooks():
124     conn = sqlite3.connect(dbFile)
125     c = conn.cursor()
126     query = "SELECT * FROM "+bookRemovedTable+";"
127     c.execute(query)
128     books = []
129     for b in c:
130         book = {}
131         i = 0
132         for k in columns:
133             if b[i]!=None:
134                 book[k]=b[i]
135             i+=1
136         books.append(book)
137     c.close()
138     return books
139
140 def getBookByID(bookid):
141     conn = sqlite3.connect(dbFile)
142     c = conn.cursor()
143     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
144     c.execute(query)
145     b = c.fetchone()
146     book = {}
147     i=0
148     for k in columns:
149         if b[i]!=None:
150             book[k]=b[i]
151         i+=1
152     c.close()
153     return book
154
155
156 # removes book from catalogue
157 def removeBook(bookid):
158     conn = sqlite3.connect(dbFile)
159     c = conn.cursor()
160     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
161     c.execute(query)
162     query = "DELETE FROM " +bookCategoryTable+ " WHERE id = "+str(bookid)+";"
163     c.execute(query)
164     conn.commit()
165     c.close()
166
167 # fully deletes book from removedBooks table
168 def deleteBook(bookid):
169     conn = sqlite3.connect(dbFile)
170     c = conn.cursor()
171     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
172     c.execute(query)
173     conn.commit()
174     c.close()
175
176 #########################################
177 # Category related functions
178 ########################################
179 def getCategories():
180     conn = sqlite3.connect(dbFile)
181     c = conn.cursor()
182     query = "SELECT category FROM "+categoryTable+";"
183     c.execute(query)
184     cats = []
185     for category in c:
186         cats.append(category[0])
187     c.close()
188     return cats
189
190 def addCategory(cat):
191     conn = sqlite3.connect(dbFile)
192     c = conn.cursor()
193     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)");"
194     c.execte(query)
195     c.close()
196
197 #########################################
198 # Database initialization
199 #########################################
200 def createBooksTable():
201     conn = sqlite3.connect(dbFile)
202     c = conn.cursor()
203     c.executescript(bookTableCreation)
204     conn.commit()
205     c.close()
206
207 def createTriggers():
208     conn = sqlite3.connect(dbFile)
209     c = conn.cursor()
210     c.executescript(bookTriggerCreation)
211     conn.commit()
212     c.close()
213
214
215 createBooksTable()
216 createTriggers()