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