Added help bar
[public/library.git] / dbLayer.py
1 import sqlite3
2
3 dbFile = 'sqLibrary.db'
4 bookTable = 'books'
5
6 bookTableCreation = '''
7 CREATE TABLE IF NOT EXISTS books
8     (id INTEGER PRIMARY KEY, 
9      isbn, lccn, title, subtitle, authors, edition, 
10      publisher, publish_year, publish_month, publish_location, 
11      pages, pagination, weight, last_updated);
12
13 CREATE TABLE IF NOT EXISTS books_deleted
14     (id INTEGER PRIMARY KEY, 
15      isbn, lccn, title, subtitle, authors, edition, 
16      publisher, publish_year, publish_month, publish_location, 
17      pages, pagination, weight, last_updated);
18 '''
19
20 columns = ['id', 'isbn', 'lccn',
21            'title', 'subtitle', 'authors', 'edition', 
22            'publisher', 'publish year', 'publish month', 'publish location', 
23            'pages', 'pagination', 'weight', 'last updated']
24
25 bookTriggerCreation = '''
26 CREATE TRIGGER IF NOT EXISTS insert_books_time AFTER INSERT ON books
27 BEGIN
28     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
29 END;
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_books_backup BEFORE DELETE ON books
37 BEGIN
38     INSERT INTO books_deleted (isbn, lccn, 
39                 title, subtitle, authors, edition, 
40                 publisher, publish_year, publish_month, publish_location, 
41                 pages, pagination, weight, last_updated)
42             SELECT isbn, lccn, 
43                    title, subtitle, authors, edition, 
44                    publisher, publish_year, publish_month, publish_location, 
45                    pages, pagination, weight, last_updated
46                    FROM books
47                    WHERE rowid = old.rowid;
48 END;
49 '''
50
51 def colify(s):
52     return s.replace(" ","_").lower()
53
54 # escapes strings and such
55 def stringify(v):
56     return '"' + str(v).strip().replace('"','""') + '"'
57
58 def addBook(book):
59     conn = sqlite3.connect(dbFile)
60     c = conn.cursor()
61     cols = []
62     vals = []
63     for k,v in book.items():
64         if v!="":
65             cols.append(colify(k))
66             vals.append(stringify(v))
67     
68     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
69     c.execute(query)
70     conn.commit()
71     c.close()
72
73 def updateBook(book, bookID):
74     conn = sqlite3.connect(dbFile)
75     c = conn.cursor()
76     updates=[]
77     for k,v in book.items():
78         updates.append(colify(k)+"="+stringify(v))
79     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
80     c.execute(query)
81     conn.commit()
82     c.close()
83
84 def getBooks():
85     conn = sqlite3.connect(dbFile)
86     c = conn.cursor()
87     query = "SELECT * FROM "+bookTable+";"
88     c.execute(query)
89     books = []
90     for b in c:
91         book = {}
92         i = 0
93         for k in columns:
94             if b[i]!=None:
95                 book[k]=b[i]
96             i+=1
97         books.append(book)
98     c.close()
99     return books
100
101 def getBookByID(bookid):
102     conn = sqlite3.connect(dbFile)
103     c = conn.cursor()
104     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
105     c.execute(query)
106     b = c.fetchone()
107     book = {}
108     i=0
109     for k in columns:
110         if b[i]!=None:
111             book[k]=b[i]
112         i+=1
113     c.close()
114     return book
115
116
117 def removeBook(bookid):
118     conn = sqlite3.connect(dbFile)
119     c = conn.cursor()
120     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
121     c.execute(query)
122     conn.commit()
123     c.close()
124
125 def createBooksTable():
126     conn = sqlite3.connect(dbFile)
127     c = conn.cursor()
128     c.executescript(bookTableCreation)
129     conn.commit()
130     c.close()
131
132 def createTriggers():
133     conn = sqlite3.connect(dbFile)
134     c = conn.cursor()
135     c.executescript(bookTriggerCreation)
136     conn.commit()
137     c.close()
138
139 createBooksTable()
140 createTriggers()