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