6661d20b5605deae88e773aeb89f468d396ff756
[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 ################################3
74 # character escaping, etc for sql queries
75 #################################
76 def colify(s):
77     return s.replace(" ","_").lower()
78
79 def stringify(v):
80     return '"' + str(v).strip().replace('"','""') + '"'
81
82 ###################################
83 # book functions
84 ##################################
85 def addBook(book):
86     conn = sqlite3.connect(dbFile)
87     c = conn.cursor()
88     cols = []
89     vals = []
90     for k,v in book.items():
91         if v!="":
92             cols.append(colify(k))
93             vals.append(stringify(v))
94     
95     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
96     c.execute(query)
97     conn.commit()
98     c.close()
99
100 def updateBook(book, bookID):
101     conn = sqlite3.connect(dbFile)
102     c = conn.cursor()
103     updates=[]
104     for k,v in book.items():
105         updates.append(colify(k)+"="+stringify(v))
106     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
107     c.execute(query)
108     conn.commit()
109     c.close()
110
111 def getBooks():
112     conn = sqlite3.connect(dbFile)
113     c = conn.cursor()
114     query = "SELECT * FROM "+bookTable+";"
115     c.execute(query)
116     books = []
117     for b in c:
118         book = {}
119         i = 0
120         for k in columns:
121             if b[i]!=None:
122                 book[k]=b[i]
123             i+=1
124         books.append(book)
125     c.close()
126     return books
127
128 def getRemovedBooks():
129     conn = sqlite3.connect(dbFile)
130     c = conn.cursor()
131     query = "SELECT * FROM "+bookRemovedTable+";"
132     c.execute(query)
133     books = []
134     for b in c:
135         book = {}
136         i = 0
137         for k in columns:
138             if b[i]!=None:
139                 book[k]=b[i]
140             i+=1
141         books.append(book)
142     c.close()
143     return books
144
145 def getBookByID(bookid):
146     conn = sqlite3.connect(dbFile)
147     c = conn.cursor()
148     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
149     c.execute(query)
150     b = c.fetchone()
151     book = {}
152     i=0
153     for k in columns:
154         if b[i]!=None:
155             book[k]=b[i]
156         i+=1
157     c.close()
158     return book
159
160
161 # removes book from catalogue
162 def removeBook(bookid):
163     conn = sqlite3.connect(dbFile)
164     c = conn.cursor()
165     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
166     c.execute(query)
167     query = "DELETE FROM " +bookCategoryTable+ " WHERE id = "+str(bookid)+";"
168     c.execute(query)
169     conn.commit()
170     c.close()
171
172 # fully deletes book from removedBooks table
173 def deleteBook(bookid):
174     conn = sqlite3.connect(dbFile)
175     c = conn.cursor()
176     query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
177     c.execute(query)
178     conn.commit()
179     c.close()
180
181 #########################################
182 # Category related functions
183 ########################################
184 def getCategories():
185     conn = sqlite3.connect(dbFile)
186     c = conn.cursor()
187     query = "SELECT category FROM "+categoryTable+";"
188     c.execute(query)
189     cats = []
190     for category in c:
191         cats.append(category[0])
192     c.close()
193     return cats
194
195 def addCategory(cat):
196     conn = sqlite3.connect(dbFile)
197     c = conn.cursor()
198     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
199     c.execte(query)
200     c.close()
201
202 #########################################
203 # Database initialization
204 #########################################
205 def createBooksTable():
206     conn = sqlite3.connect(dbFile)
207     c = conn.cursor()
208     c.executescript(bookTableCreation)
209     conn.commit()
210     c.close()
211
212 def createTriggers():
213     conn = sqlite3.connect(dbFile)
214     c = conn.cursor()
215     c.executescript(bookTriggerCreation)
216     conn.commit()
217     c.close()
218
219
220 createBooksTable()
221 createTriggers()