dbLayer.py

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