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