browser has scrolling now
[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     cols = []
77     vals = []
78     for k,v in book.items():
79         if v!="":
80             cols.append(colify(k))
81             vals.append(stringify(v))
82     
83     query = "UPDATE "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+") WHERE id = " +bookID+";"
84     c.execute(query)
85     conn.commit()
86     c.close()
87
88 def getBooks():
89     conn = sqlite3.connect(dbFile)
90     c = conn.cursor()
91     query = "SELECT * FROM "+bookTable+";"
92     c.execute(query)
93     books = []
94     for b in c:
95         book = {}
96         i = 0
97         for k in columns:
98             if b[i]!=None:
99                 book[k]=b[i]
100             i+=1
101         books.append(book)
102     c.close()
103     return books
104
105 def removeBook():
106     conn = sqlite3.connect(dbFile)
107     c = conn.cursor()
108     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(id)+";"
109     c.execute(query)
110     conn.commit()
111     c.close()
112
113 def createBooksTable():
114     conn = sqlite3.connect(dbFile)
115     c = conn.cursor()
116     c.executescript(bookTableCreation)
117     conn.commit()
118     c.close()
119
120 def createTriggers():
121     conn = sqlite3.connect(dbFile)
122     c = conn.cursor()
123     c.executescript(bookTriggerCreation)
124     conn.commit()
125     c.close()
126
127 createBooksTable()
128 createTriggers()