library/database.py

   1 import sqlite3
   2
   3 from  library import permissions
   4
   5 _catalogue_db_file = '/users/libcom/catalogue.db'
   6 _book_table = 'books'
   7 _book_category_table='book_categories'
   8 _category_table = 'categories'
   9
  10 _checkout_db_file = '/users/libcom/checkout.db'
  11 _checkout_table = 'checked_out'
  12 _return_table = 'returned'
  13
  14 _checkout_table_creation = '''
  15 CREATE TABLE IF NOT EXISTS checked_out
  16     (id INTEGER UNIQUE, uwid STRING, date_out DATETIME DEFAULT current_timestamp);
  17
  18 CREATE TABLE IF NOT EXISTS returned
  19     (id INTEGER, uwid STRING, date_out DATETIME, date_in DATETIME DEFAULT current_timestamp);
  20 '''
  21
  22 _book_table_creation = '''
  23 CREATE TABLE IF NOT EXISTS books
  24     (id INTEGER PRIMARY KEY AUTOINCREMENT,
  25      isbn, lccn, title, subtitle, authors, edition,
  26      publisher, publish_year, publish_month, publish_location,
  27      pages, pagination, weight,
  28      last_updated DATETIME DEFAULT current_timestamp,
  29      deleted BOOLEAN DEFAULT 0);
  30
  31 CREATE TABLE IF NOT EXISTS categories
  32     (cat_id INTEGER PRIMARY KEY, category STRING UNIQUE ON CONFLICT IGNORE);
  33
  34 CREATE TABLE IF NOT EXISTS book_categories
  35     (id INTEGER, cat_id INTEGER);
  36 '''
  37
  38 columns = ['id', 'isbn', 'lccn',
  39            'title', 'subtitle', 'authors', 'edition',
  40            'publisher', 'publish year', 'publish month', 'publish location',
  41            'pages', 'pagination', 'weight', 'last updated', 'deleted']
  42
  43 _book_trigger_creation = '''
  44
  45 CREATE TRIGGER IF NOT EXISTS update_books_time AFTER UPDATE ON books
  46 BEGIN
  47     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
  48 END;
  49
  50 CREATE TRIGGER IF NOT EXISTS delete_book AFTER DELETE ON books
  51 BEGIN
  52     DELETE FROM book_categories WHERE id = old.rowid;
  53 END;
  54
  55 CREATE TRIGGER IF NOT EXISTS delete_category AFTER DELETE ON categories
  56 BEGIN
  57     DELETE FROM book_categories WHERE cat_id = old.cat_id;
  58 END;
  59
  60 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT
  61 ON book_categories
  62 BEGIN
  63     UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
  64 END;
  65 '''
  66
  67 #################################
  68 # character escaping, etc for sql queries
  69 #################################
  70 def _colify(s):
  71     return s.replace(" ","_").lower()
  72
  73 def _stringify(v):
  74     return '"' + str(v).strip().replace('"','""') + '"'
  75
  76 ###################################
  77 # book functions
  78 ##################################
  79 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
  80 def addBook(book):
  81     conn = sqlite3.connect(_catalogue_db_file)
  82     c = conn.cursor()
  83     cols = []
  84     vals = []
  85     for k,v in book.items():
  86         if v!="":
  87             cols.append(_colify(k))
  88             vals.append(_stringify(v))
  89
  90     query = ("INSERT INTO "+_book_table+" ("+", ".join(cols)+") VALUES ("+
  91              ", ".join(vals)+");")
  92     c.execute(query)
  93     conn.commit()
  94     c.close()
  95
  96 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
  97 def updateBook(book, bookID):
  98     '''
  99     Takes book attribute dictionary and a string representating the book ID
 100     number, and returns updates the book accordingly
 101     '''
 102     conn = sqlite3.connect(_catalogue_db_file)
 103     c = conn.cursor()
 104     updates=[]
 105     for k,v in book.items():
 106         updates.append(_colify(k)+"="+_stringify(v))
 107     query = ("UPDATE "+_book_table+" SET " +  ", ".join(updates)+" WHERE id = " +
 108              str(bookID)+";")
 109     c.execute(query)
 110     conn.commit()
 111     c.close()
 112
 113 def get_books():
 114     conn = sqlite3.connect(_catalogue_db_file)
 115     c = conn.cursor()
 116     query = "SELECT * FROM "+_book_table+" WHERE deleted=0;"
 117     c.execute(query)
 118     books = [_query_to_book(b) for b in c]
 119     c.close()
 120     return books
 121
 122 def getBooksByCategory(cat):
 123     '''
 124     Takes a string representating the category ID number, and returns
 125     non-deleted books in that category
 126     '''
 127     conn = sqlite3.connect(_catalogue_db_file)
 128     c = conn.cursor()
 129     query = ("SELECT "+",".join(map(_colify,columns))+" FROM "+_book_table+
 130              " JOIN "+_book_category_table+
 131              " USING (id) WHERE cat_id = :id AND deleted=0;")
 132     c.execute(query,cat)
 133     books = [_query_to_book(b) for b in c]
 134     c.close()
 135     return books
 136
 137 def getRemovedBooks():
 138     conn = sqlite3.connect(_catalogue_db_file)
 139     c = conn.cursor()
 140     query = "SELECT * FROM "+_book_table+" WHERE DELETED=1;"
 141     c.execute(query)
 142     books = [_query_to_book(b) for b in c]
 143     c.close()
 144     return books
 145
 146 def get_book(bookid):
 147     conn = sqlite3.connect(_catalogue_db_file)
 148     c = conn.cursor()
 149     query = "SELECT * FROM "+_book_table+" WHERE id = "+str(bookid)+";"
 150     c.execute(query)
 151     book = _query_to_book(c.fetchone())
 152     c.close()
 153     return book
 154
 155 # removes book from catalogue
 156 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 157 def removeBook(bookid):
 158     conn = sqlite3.connect(_catalogue_db_file)
 159     c = conn.cursor()
 160     query = "UPDATE " +_book_table+ " SET deleted=1 WHERE id = "+str(bookid)+";"
 161     c.execute(query)
 162     conn.commit()
 163     c.close()
 164
 165 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 166 def removeBooks(books):
 167     conn = sqlite3.connect(_catalogue_db_file)
 168     c = conn.cursor()
 169     query1 = "UPDATE " +_book_table+ " 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 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 177 def restoreBooks(books):
 178     conn = sqlite3.connect(_catalogue_db_file)
 179     c = conn.cursor()
 180     query1 = "UPDATE " +_book_table+ " SET deleted=0 WHERE id = :id;"
 181     for book in books:
 182         c.execute(query1,book)
 183     conn.commit()
 184     c.close()
 185
 186 # fully deletes book from books table
 187 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 188 def deleteBook(bookid):
 189     conn = sqlite3.connect(_catalogue_db_file)
 190     c = conn.cursor()
 191     query = "DELETE FROM " +_book_table+ " WHERE id = "+str(bookid)+";"
 192     c.execute(query)
 193     conn.commit()
 194     c.close()
 195
 196 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 197 def deleteBooks(books):
 198     conn = sqlite3.connect(_catalogue_db_file)
 199     c = conn.cursor()
 200     query = "DELETE FROM " +_book_table+ " WHERE id = :id;"
 201     for book in books:
 202         c.execute(query, book)
 203     conn.commit()
 204     c.close()
 205
 206 def _query_to_book(book_query):
 207     # Make a dict out of column name and query results.
 208     # Empty entries return None, which are removed from the dict.
 209     return dict(filter(lambda t:t[1], zip(columns,book_query)))
 210
 211 def _query_to_book_checkout(book_query):
 212     # Make a dict out of column name and query results.
 213     # Empty entries return None, which are removed from the dict.
 214     b = _query_to_book(book_query)
 215     b['uwid'] = book_query[-2]
 216     b['date'] = book_query[-1]
 217     return b
 218
 219 #########################################
 220 # Category related functions
 221 ########################################
 222 def getBookCategories(book):
 223     conn = sqlite3.connect(_catalogue_db_file)
 224     c = conn.cursor()
 225     query = ("SELECT id,cat_id,category FROM "+_book_category_table+" JOIN "+
 226              _category_table+" USING (cat_id) WHERE id = :id ;")
 227     c.execute(query,book)
 228     cats = []
 229     for book_id,cat_id,cat_name in c:
 230         cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
 231     c.close()
 232     return cats
 233
 234 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 235 def categorizeBook(book, cats):
 236     conn = sqlite3.connect(_catalogue_db_file)
 237     c = conn.cursor()
 238     query = ("INSERT OR IGNORE INTO "+_book_category_table+
 239              " (id,cat_id) VALUES (?, ?);")
 240     for cat in cats:
 241         args = (book['id'],cat['id'])
 242         c.execute(query,args)
 243     conn.commit()
 244     c.close()
 245
 246 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 247 def uncategorizeBook(book, cats):
 248     conn = sqlite3.connect(_catalogue_db_file)
 249     c = conn.cursor()
 250     query = "DELETE FROM "+_book_category_table+" WHERE (id = ? AND cat_id = ?);"
 251     for cat in cats:
 252         args = (book['id'],cat['id'])
 253         c.execute(query,args)
 254     conn.commit()
 255     c.close()
 256
 257 def getCategories():
 258     conn = sqlite3.connect(_catalogue_db_file)
 259     c = conn.cursor()
 260     query = "SELECT cat_id, category FROM "+_category_table+";"
 261     c.execute(query)
 262     cats = []
 263     for cat_id,cat in c:
 264         cats.append({'id':cat_id, 'category':cat})
 265     c.close()
 266     return cats
 267
 268 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 269 def addCategory(cat):
 270     conn = sqlite3.connect(_catalogue_db_file)
 271     c = conn.cursor()
 272     query = ("INSERT OR IGNORE INTO "+_category_table+" (category) VALUES ("
 273              +_stringify(cat)+");")
 274     c.execute(query)
 275     conn.commit()
 276     c.close()
 277
 278 @permissions.check_permissions(permissions.PERMISSION_LIBCOM)
 279 def deleteCategories(cats):
 280     conn = sqlite3.connect(_catalogue_db_file)
 281     c = conn.cursor()
 282     query1 = "DELETE FROM " +_category_table+ " WHERE cat_id = :id;"
 283     for cat in cats:
 284         c.execute(query1, cat)
 285     conn.commit()
 286     c.close()
 287
 288 #########################################
 289 # Book Checkout functions
 290 #########################################
 291 @permissions.check_permissions(permissions.PERMISSION_OFFICE)
 292 def checkout_book(book_id, uwid):
 293     conn = sqlite3.connect(_checkout_db_file)
 294     c = conn.cursor()
 295     query = "INSERT INTO " + _checkout_table + " (id, uwid) VALUES (?, ?);"
 296     c.execute(query, (book_id, uwid))
 297     conn.commit()
 298     c.close()
 299
 300 @permissions.check_permissions(permissions.PERMISSION_OFFICE)
 301 def return_book(book_id):
 302     conn = sqlite3.connect(_checkout_db_file)
 303     c = conn.cursor()
 304     query = "SELECT uwid,date_out FROM "+ _checkout_table + " WHERE id = :id ;"
 305     c.execute(query, {"id": book_id})
 306     tmp = c.fetchone()
 307     uwid = tmp[0]
 308     date_out = tmp[1]
 309     query = "INSERT INTO " + _return_table + " (id, uwid, date_out) VALUES (?, ?, ?);"
 310     query2 = "DELETE FROM " + _checkout_table + " WHERE id= :id ;"
 311     c.execute(query, (book_id, uwid, date_out))
 312     c.execute(query2, {"id": book_id});
 313     conn.commit()
 314     c.close()
 315
 316 def get_checkedout_books():
 317     '''
 318     retrieves checked out books. The returned books also have the fields
 319     uwid: ID of person who signed out the book, and
 320     date: date when the book was checked out
 321     '''
 322     conn = sqlite3.connect(_catalogue_db_file)
 323     c = conn.cursor()
 324     query = 'ATTACH "' + _checkout_db_file + '" AS co'
 325     c.execute(query)
 326     query = ("SELECT "+",".join(map(_colify,columns))+",uwid,date_out FROM "+_book_table+
 327              " JOIN co."+_checkout_table+
 328              " USING (id) ;")
 329     c.execute(query)
 330     books = [_query_to_book_checkout(b) for b in c]
 331     c.close()
 332     return books
 333
 334 def get_onshelf_books():
 335     '''
 336     retrieves checked out books. The returned books also have the fields
 337     uwid: ID of person who signed out the book, and
 338     date: date when the book was checked out
 339     '''
 340     conn = sqlite3.connect(_catalogue_db_file)
 341     c = conn.cursor()
 342     query = 'ATTACH "' + _checkout_db_file + '" AS co'
 343     c.execute(query)
 344     query = ("SELECT "+",".join(map(_colify,columns))+" FROM "+_book_table+
 345              " LEFT JOIN co."+_checkout_table+
 346              " USING (id) WHERE uwid ISNULL;")
 347     c.execute(query)
 348     books = [_query_to_book(b) for b in c]
 349     c.close()
 350     return books
 351
 352 #########################################
 353 # Database initialization
 354 #########################################
 355 def _createBooksTable():
 356     conn = sqlite3.connect(_catalogue_db_file)
 357     c = conn.cursor()
 358     c.executescript(_book_table_creation)
 359     conn.commit()
 360     c.close()
 361
 362 def _createTriggers():
 363     conn = sqlite3.connect(_catalogue_db_file)
 364     c = conn.cursor()
 365     c.executescript(_book_trigger_creation)
 366     conn.commit()
 367     c.close()
 368
 369 def _create_checkout_table():
 370     conn = sqlite3.connect(_checkout_db_file)
 371     c = conn.cursor()
 372     c.executescript(_checkout_table_creation)
 373     conn.commit()
 374     c.close()
 375
 376 def initializeDatabase():
 377     _createBooksTable()
 378     _createTriggers()
 379     _create_checkout_table()