dbLayer.py

   1 import sys
   2 import sqlite3
   3
   4 dbFile = 'sqLibrary.db'
   5 bookTable = 'books'
   6 bookCategoryTable='book_categories'
   7 categoryTable = 'categories'
   8
   9
  10 bookTableCreation = '''
  11 CREATE TABLE IF NOT EXISTS books
  12     (id INTEGER PRIMARY KEY AUTOINCREMENT,
  13      isbn, lccn, title, subtitle, authors, edition,
  14      publisher, publish_year, publish_month, publish_location,
  15      pages, pagination, weight, last_updated DATETIME DEFAULT current_timestamp, deleted BOOLEAN DEFAULT 0);
  16
  17 CREATE TABLE IF NOT EXISTS categories
  18     (cat_id INTEGER PRIMARY KEY, category STRING UNIQUE ON CONFLICT IGNORE);
  19
  20 CREATE TABLE IF NOT EXISTS book_categories
  21     (id INTEGER, cat_id INTEGER);
  22 '''
  23
  24 columns = ['id', 'isbn', 'lccn',
  25            'title', 'subtitle', 'authors', 'edition',
  26            'publisher', 'publish year', 'publish month', 'publish location',
  27            'pages', 'pagination', 'weight', 'last updated', 'deleted']
  28
  29 bookTriggerCreation = '''
  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_book AFTER DELETE ON books
  37 BEGIN
  38     DELETE FROM book_categories WHERE id = old.rowid;
  39 END;
  40
  41 CREATE TRIGGER IF NOT EXISTS delete_category AFTER DELETE ON categories
  42 BEGIN
  43     DELETE FROM book_categories WHERE cat_id = old.cat_id;
  44 END;
  45
  46 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
  47 BEGIN
  48     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
  49 END;
  50 '''
  51
  52 ################################3
  53 # character escaping, etc for sql queries
  54 #################################
  55 def colify(s):
  56     return s.replace(" ","_").lower()
  57
  58 def stringify(v):
  59     return '"' + str(v).strip().replace('"','""') + '"'
  60
  61 ###################################
  62 # book functions
  63 ##################################
  64 def addBook(book):
  65     conn = sqlite3.connect(dbFile)
  66     c = conn.cursor()
  67     cols = []
  68     vals = []
  69     for k,v in book.items():
  70         if v!="":
  71             cols.append(colify(k))
  72             vals.append(stringify(v))
  73
  74     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
  75     c.execute(query)
  76     conn.commit()
  77     c.close()
  78
  79 def updateBook(book, bookID):
  80     conn = sqlite3.connect(dbFile)
  81     c = conn.cursor()
  82     updates=[]
  83     for k,v in book.items():
  84         updates.append(colify(k)+"="+stringify(v))
  85     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
  86     c.execute(query)
  87     conn.commit()
  88     c.close()
  89
  90 def getBooks():
  91     conn = sqlite3.connect(dbFile)
  92     c = conn.cursor()
  93     query = "SELECT * FROM "+bookTable+" WHERE deleted=0;"
  94     c.execute(query)
  95     books = []
  96     for b in c:
  97         book = {}
  98         i = 0
  99         for k in columns:
 100             if b[i]!=None:
 101                 book[k]=b[i]
 102             i+=1
 103         books.append(book)
 104     c.close()
 105     return books
 106
 107 def getBooksByCategory(cat):
 108     conn = sqlite3.connect(dbFile)
 109     c = conn.cursor()
 110     query = "SELECT "+",".join(map(colify,columns))+" FROM "+bookTable+" JOIN "+bookCategoryTable+" USING (id) WHERE cat_id = :id AND deleted=0;"
 111     c.execute(query,cat)
 112     books = []
 113     for b in c:
 114         book = {}
 115         i = 0
 116         for k in columns:
 117             if b[i]!=None:
 118                 book[k]=b[i]
 119             i+=1
 120         books.append(book)
 121     c.close()
 122     return books
 123
 124 def getRemovedBooks():
 125     conn = sqlite3.connect(dbFile)
 126     c = conn.cursor()
 127     query = "SELECT * FROM "+bookTable+" WHERE DELETED=1;"
 128     c.execute(query)
 129     books = []
 130     for b in c:
 131         book = {}
 132         i = 0
 133         for k in columns:
 134             if b[i]!=None:
 135                 book[k]=b[i]
 136             i+=1
 137         books.append(book)
 138     c.close()
 139     return books
 140
 141 def getBookByID(bookid):
 142     conn = sqlite3.connect(dbFile)
 143     c = conn.cursor()
 144     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
 145     c.execute(query)
 146     b = c.fetchone()
 147     book = {}
 148     i=0
 149     for k in columns:
 150         if b[i]!=None:
 151             book[k]=b[i]
 152         i+=1
 153     c.close()
 154     return book
 155
 156
 157 # removes book from catalogue
 158 def removeBook(bookid):
 159     conn = sqlite3.connect(dbFile)
 160     c = conn.cursor()
 161     query = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = "+str(bookid)+";"
 162     c.execute(query)
 163     conn.commit()
 164     c.close()
 165
 166 def removeBooks(books):
 167     conn = sqlite3.connect(dbFile)
 168     c = conn.cursor()
 169     query1 = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = :id;"
 170     for book in books:
 171         c.execute(query1, book)
 172     conn.commit()
 173     c.close()
 174
 175 # restores trashed books
 176 def restoreBooks(books):
 177     conn = sqlite3.connect(dbFile)
 178     c = conn.cursor()
 179     query1 = "UPDATE " +bookTable+ " SET deleted=0 WHERE id = :id;"
 180     for book in books:
 181         c.execute(query1,book)
 182     conn.commit()
 183     c.close()
 184
 185 # fully deletes book from removedBooks table
 186 def deleteBook(bookid):
 187     conn = sqlite3.connect(dbFile)
 188     c = conn.cursor()
 189     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
 190     c.execute(query)
 191     conn.commit()
 192     c.close()
 193
 194 def deleteBooks(books):
 195     conn = sqlite3.connect(dbFile)
 196     c = conn.cursor()
 197     query = "DELETE FROM " +bookTable+ " WHERE id = :id;"
 198     for book in books:
 199         c.execute(query, book)
 200     conn.commit()
 201     c.close()
 202
 203 #########################################
 204 # Category related functions
 205 ########################################
 206 def getBookCategories(book):
 207     conn = sqlite3.connect(dbFile)
 208     c = conn.cursor()
 209     query = "SELECT id,cat_id,category FROM "+bookCategoryTable+" JOIN "+categoryTable+" USING (cat_id) WHERE id = :id ;"
 210     c.execute(query,book)
 211     cats = []
 212     for book_id,cat_id,cat_name in c:
 213         cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
 214     c.close()
 215     return cats
 216
 217 def categorizeBook(book, cats):
 218     conn = sqlite3.connect(dbFile)
 219     c = conn.cursor()
 220     query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES (?, ?);"
 221     for cat in cats:
 222         args = (book['id'],cat['id'])
 223         c.execute(query,args)
 224     conn.commit()
 225     c.close()
 226
 227 def uncategorizeBook(book, cats):
 228     conn = sqlite3.connect(dbFile)
 229     c = conn.cursor()
 230     query = "DELETE FROM "+bookCategoryTable+" WHERE (id = ? AND cat_id = ?);"
 231     for cat in cats:
 232         args = (book['id'],cat['id'])
 233         c.execute(query,args)
 234     conn.commit()
 235     c.close()
 236
 237 def getCategories():
 238     conn = sqlite3.connect(dbFile)
 239     c = conn.cursor()
 240     query = "SELECT cat_id, category FROM "+categoryTable+";"
 241     c.execute(query)
 242     cats = []
 243     for cat_id,cat in c:
 244         cats.append({'id':cat_id, 'category':cat})
 245     c.close()
 246     return cats
 247
 248 def addCategory(cat):
 249     conn = sqlite3.connect(dbFile)
 250     c = conn.cursor()
 251     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
 252     c.execute(query)
 253     conn.commit()
 254     c.close()
 255
 256 def deleteCategories(cats):
 257     conn = sqlite3.connect(dbFile)
 258     c = conn.cursor()
 259     query1 = "DELETE FROM " +categoryTable+ " WHERE cat_id = :id;"
 260     for cat in cats:
 261         c.execute(query1, cat)
 262     conn.commit()
 263     c.close()
 264
 265 #########################################
 266 # Database initialization
 267 #########################################
 268 def createBooksTable():
 269     conn = sqlite3.connect(dbFile)
 270     c = conn.cursor()
 271     c.executescript(bookTableCreation)
 272     conn.commit()
 273     c.close()
 274
 275 def createTriggers():
 276     conn = sqlite3.connect(dbFile)
 277     c = conn.cursor()
 278     c.executescript(bookTriggerCreation)
 279     conn.commit()
 280     c.close()
 281
 282
 283 createBooksTable()
 284 createTriggers()