added some desired features
[public/library.git] / dbLayer.py
index 243b48a..3f7f252 100644 (file)
@@ -5,11 +5,13 @@ dbFile = 'sqLibrary.db'
 bookTable = 'books'
 bookRemovedTable='books_deleted'
 bookCategoryTable='book_categories'
+bookRemovedCategoryTable='books_deleted_categories'
 categoryTable = 'categories'
 
+
 bookTableCreation = '''
 CREATE TABLE IF NOT EXISTS books
-    (id INTEGER PRIMARY KEY, 
+    (id INTEGER PRIMARY KEY AUTOINCREMENT
      isbn, lccn, title, subtitle, authors, edition, 
      publisher, publish_year, publish_month, publish_location, 
      pages, pagination, weight, last_updated);
@@ -21,10 +23,13 @@ CREATE TABLE IF NOT EXISTS books_deleted
      pages, pagination, weight, last_updated);
 
 CREATE TABLE IF NOT EXISTS categories
-    (cat_id INTEGER PRIMARY KEY, category STRING);
+    (cat_id INTEGER PRIMARY KEY, category STRING UNIQUE ON CONFLICT IGNORE);
 
 CREATE TABLE IF NOT EXISTS book_categories
     (id INTEGER, cat_id INTEGER);
+
+CREATE TABLE IF NOT EXISTS books_deleted_categories
+    (id INTEGER, cat_id INTEGER);
 '''
 
 columns = ['id', 'isbn', 'lccn',
@@ -45,16 +50,30 @@ END;
 
 CREATE TRIGGER IF NOT EXISTS delete_books_backup BEFORE DELETE ON books
 BEGIN
-    INSERT INTO books_deleted (isbn, lccn, 
+    INSERT INTO books_deleted (id, isbn, lccn, 
                 title, subtitle, authors, edition, 
                 publisher, publish_year, publish_month, publish_location, 
                 pages, pagination, weight, last_updated)
-            SELECT isbn, lccn, 
+            SELECT id, isbn, lccn, 
                    title, subtitle, authors, edition, 
                    publisher, publish_year, publish_month, publish_location, 
                    pages, pagination, weight, last_updated
                    FROM books
                    WHERE rowid = old.rowid;
+    INSERT INTO books_deleted_categories (id, cat_id)
+            SELECT id, cat_id FROM book_categories WHERE id = old.rowid;
+    DELETE FROM book_categories WHERE id = old.rowid;
+END;
+
+CREATE TRIGGER IF NOT EXISTS delete_backup AFTER DELETE ON books_deleted
+BEGIN
+    DELETE FROM books_deleted_categories WHERE id = old.rowid;
+END;
+
+CREATE TRIGGER IF NOT EXISTS delete_category AFTER DELETE ON categories
+BEGIN
+    DELETE FROM book_categories WHERE cat_id = old.cat_id;
+    DELETE FROM books_deleted_categories WHERE cat_id = old.cat_id;
 END;
 
 CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
@@ -123,6 +142,23 @@ def getBooks():
     c.close()
     return books
 
+def getBooksByCategory(cat):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "SELECT "+",".join(map(colify,columns))+" FROM "+bookTable+" JOIN "+bookCategoryTable+" USING (id) WHERE cat_id = :id;"
+    c.execute(query,cat)
+    books = []
+    for b in c:
+        book = {}
+        i = 0
+        for k in columns:
+            if b[i]!=None:
+                book[k]=b[i]
+            i+=1
+        books.append(book)
+    c.close()
+    return books
+
 def getRemovedBooks():
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
@@ -167,15 +203,28 @@ def removeBook(bookid):
     conn.commit()
     c.close()
 
-def removeBooks(bookids):
+def removeBooks(books):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
-    query1 = "DELETE FROM " +bookTable+ " WHERE id = ?;"
-    query2 = "DELETE FROM " +bookCategoryTable+ " WHERE id = ?;"
-    for book in bookids:
-        bid=(book,)
-        c.execute(query1, bid)
-        c.execute(query2, bid)
+    query1 = "DELETE FROM " +bookTable+ " WHERE id = :id;"
+    query2 = "DELETE FROM " +bookCategoryTable+ " WHERE id = :id;"
+    for book in books:
+        c.execute(query1, book)
+        c.execute(query2, book)
+    conn.commit()
+    c.close()
+
+# restores trashed books
+def restoreBooks(books):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query1 =  "INSERT INTO "+bookTable+" ("+",".join(map(colify,columns))+") SELECT "+",".join(map(colify,columns))+" FROM "+bookRemovedTable+" WHERE id = :id;"
+    query2 =  "INSERT INTO "+bookCategoryTable+" (id, cat_id) SELECT id,cat_id FROM "+bookRemovedCategoryTable+" WHERE id = :id;"
+    query3 = "DELETE FROM " +bookRemovedTable+ " WHERE id = :id;"
+    for book in books:
+        c.execute(query1,book)
+        c.execute(query2,book)
+        c.execute(query3,book)
     conn.commit()
     c.close()
 
@@ -188,26 +237,48 @@ def deleteBook(bookid):
     conn.commit()
     c.close()
 
-def deleteBooks(bookids):
+def deleteBooks(books):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
-    query = "DELETE FROM " +bookRemovedTable+ " WHERE id = ?;"
-    for book in bookids:
-        bid=(book,)
-        c.execute(query, bid)
+    query = "DELETE FROM " +bookRemovedTable+ " WHERE id = :id;"
+    for book in books:
+        c.execute(query, book)
     conn.commit()
     c.close()
 
 #########################################
 # Category related functions
 ########################################
-def categorizeBook(bookid, cat_id):
+def getBookCategories(book):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
-    query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES ("+str(bookid)+", "+str(cat_id)+");"
+    query = "SELECT id,cat_id,category FROM "+bookCategoryTable+" JOIN "+categoryTable+" USING (cat_id) WHERE id = :id ;"
+    c.execute(query,book)
+    cats = []
+    for book_id,cat_id,cat_name in c:
+        cats.append({'id':book_id, 'cat_id':cat_id, 'category':cat_name})
+    c.close()
+    return cats
+
+def categorizeBook(book, cats):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES (?, ?);"
+    for cat in cats:
+        args = (book['id'],cat['id'])
+        c.execute(query,args)
+    conn.commit()
+    c.close()
+
+def uncategorizeBook(book, cats):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "DELETE FROM "+bookCategoryTable+" WHERE (id = ? AND cat_id = ?);"
+    for cat in cats:
+        args = (book['id'],cat['id'])
+        c.execute(query,args)
     conn.commit()
     c.close()
-        
 
 def getCategories():
     conn = sqlite3.connect(dbFile)
@@ -232,10 +303,8 @@ def deleteCategories(cats):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
     query1 = "DELETE FROM " +categoryTable+ " WHERE cat_id = :id;"
-    query2 = "DELETE FROM " +bookCategoryTable+ " WHERE cat_id = :id;"
     for cat in cats:
         c.execute(query1, cat)
-        c.execute(query2, cat)
     conn.commit()
     c.close()