finishing fixing bugs from last commit
[public/library.git] / dbLayer.py
index cc46e73..e0e940c 100644 (file)
@@ -3,25 +3,19 @@ import sqlite3
 
 dbFile = 'sqLibrary.db'
 bookTable = 'books'
-bookRemovedTable='books_deleted'
 bookCategoryTable='book_categories'
 categoryTable = 'categories'
 
+
 bookTableCreation = '''
 CREATE TABLE IF NOT EXISTS books
-    (id INTEGER PRIMARY KEY, 
-     isbn, lccn, title, subtitle, authors, edition, 
-     publisher, publish_year, publish_month, publish_location, 
-     pages, pagination, weight, last_updated);
-
-CREATE TABLE IF NOT EXISTS books_deleted
-    (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);
+     pages, pagination, weight, last_updated DATETIME DEFAULT current_timestamp, deleted BOOLEAN DEFAULT 0);
 
 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);
@@ -30,41 +24,28 @@ CREATE TABLE IF NOT EXISTS book_categories
 columns = ['id', 'isbn', 'lccn',
            'title', 'subtitle', 'authors', 'edition', 
            'publisher', 'publish year', 'publish month', 'publish location', 
-           'pages', 'pagination', 'weight', 'last updated']
+           'pages', 'pagination', 'weight', 'last updated', 'deleted']
 
 bookTriggerCreation = '''
-CREATE TRIGGER IF NOT EXISTS insert_books_time AFTER INSERT ON books
-BEGIN
-    UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
-END;
 
 CREATE TRIGGER IF NOT EXISTS update_books_time AFTER UPDATE ON books
 BEGIN
     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
 END;
 
-CREATE TRIGGER IF NOT EXISTS delete_books_backup BEFORE DELETE ON books
+CREATE TRIGGER IF NOT EXISTS delete_book AFTER DELETE ON books
 BEGIN
-    INSERT INTO books_deleted (isbn, lccn, 
-                title, subtitle, authors, edition, 
-                publisher, publish_year, publish_month, publish_location, 
-                pages, pagination, weight, last_updated)
-            SELECT isbn, lccn, 
-                   title, subtitle, authors, edition, 
-                   publisher, publish_year, publish_month, publish_location, 
-                   pages, pagination, weight, last_updated
-                   FROM books
-                   WHERE rowid = old.rowid;
+    DELETE FROM book_categories WHERE id = old.rowid;
 END;
 
-CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
+CREATE TRIGGER IF NOT EXISTS delete_category AFTER DELETE ON categories
 BEGIN
-    UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
+    DELETE FROM book_categories WHERE cat_id = old.cat_id;
 END;
 
-CREATE TRIGGER IF NOT EXISTS delete_book_category_time AFTER DELETE ON book_categories
+CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
 BEGIN
-    UPDATE books SET last_updated = DATETIME('NOW') WHERE id = old.id;
+    UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
 END;
 '''
 
@@ -109,7 +90,7 @@ def updateBook(book, bookID):
 def getBooks():
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
-    query = "SELECT * FROM "+bookTable+";"
+    query = "SELECT * FROM "+bookTable+" WHERE deleted=0;"
     c.execute(query)
     books = []
     for b in c:
@@ -123,10 +104,27 @@ 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 AND deleted=0;"
+    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()
-    query = "SELECT * FROM "+bookRemovedTable+";"
+    query = "SELECT * FROM "+bookTable+" WHERE DELETED=1;"
     c.execute(query)
     books = []
     for b in c:
@@ -160,22 +158,27 @@ def getBookByID(bookid):
 def removeBook(bookid):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
-    query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
-    c.execute(query)
-    query = "DELETE FROM " +bookCategoryTable+ " WHERE id = "+str(bookid)+";"
+    query = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = "+str(bookid)+";"
     c.execute(query)
     conn.commit()
     c.close()
 
-def removeBooks(bookids):
+def removeBooks(books):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query1 = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = :id;"
+    for book in books:
+        c.execute(query1, book)
+    conn.commit()
+    c.close()
+
+# restores trashed books
+def restoreBooks(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 = "UPDATE " +bookTable+ " SET deleted=0 WHERE id = :id;"
+    for book in books:
+        c.execute(query1,book)
     conn.commit()
     c.close()
 
@@ -183,31 +186,53 @@ def removeBooks(bookids):
 def deleteBook(bookid):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
-    query = "DELETE FROM " +bookRemovedTable+ " WHERE id = "+str(bookid)+";"
+    query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
     c.execute(query)
     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 " +bookTable+ " 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 = "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 = "INSERT OR IGNORE INTO "+bookCategoryTable+" (id,cat_id) VALUES ("+str(bookid)+", "+str(cat_id)+");"
+    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)
@@ -224,11 +249,19 @@ def addCategory(cat):
     conn = sqlite3.connect(dbFile)
     c = conn.cursor()
     query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
-    print >>sys.stderr, query
     c.execute(query)
     conn.commit()
     c.close()
 
+def deleteCategories(cats):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query1 = "DELETE FROM " +categoryTable+ " WHERE cat_id = :id;"
+    for cat in cats:
+        c.execute(query1, cat)
+    conn.commit()
+    c.close()
+
 #########################################
 # Database initialization
 #########################################