Cleaned files, started to follow naming convention
authorJohn Ladan <jladan@uwaterloo.ca>
Wed, 23 Oct 2013 16:17:56 +0000 (12:17 -0400)
committerJohn Ladan <jladan@uwaterloo.ca>
Wed, 23 Oct 2013 16:17:56 +0000 (12:17 -0400)
14 files changed:
TODO
bookData.py [deleted file]
book_data.py [new file with mode: 0644]
browser.py
cursestest.py [deleted file]
dbLayer.py [deleted file]
db_layer.py [new file with mode: 0644]
echokey.py [deleted file]
gui.py [new file with mode: 0644]
helpBar.py [deleted file]
help_bar.py [new file with mode: 0644]
librarian.py
ncurses_tools/cursestest.py [new file with mode: 0755]
ncurses_tools/echokey.py [new file with mode: 0755]

diff --git a/TODO b/TODO
index befe8be..e3dcf13 100644 (file)
--- a/TODO
+++ b/TODO
@@ -2,15 +2,32 @@ _List of Desired Features_
 
 Categories work based on selection, not just highlight
  - i.e. assign categories to multiple books at once
-Support UTF-8 for everything
+ - this may involve extra logic if books don't have the same categories beforehand
 Search ignores Case
 Regex Search
 Choose shown columns in browser
 Support for multiple copies
 Text entry supports longer string
+ - implementation started in gui.py
+Home and End navigate to top and bottom of catalogue respectively.
+
+
+_Code Quality Improvements_
+Document all functions
+Conform to python naming conventions and code style
+Make db_layer use a helper function to handle most of the database queries
 
-_Implemented Features_
-Sort by column in browser
 
 _Bugs_
 Error when entering bad ISBN e.g. 02010798X (instead of 020107981X)
+ - seems to be an intermittant error, likely related to polling openLibrary
+When the top element shown is highlighted in the browser window, and pgUp is pressed (causing a scrollup), the bottom border of the window turns into 'qqqqqqqq'.
+ - does not happen when up_arrow is pressed.
+
+
+
+
+_Implemented Features_
+Sort by column in browser
+Support UTF-8 for everything
+
diff --git a/bookData.py b/bookData.py
deleted file mode 100644 (file)
index e4c7c7a..0000000
+++ /dev/null
@@ -1,129 +0,0 @@
-try:
-        # For Python 3.0 and later
-            from urllib.request import urlopen,URLError
-except ImportError:
-        # Fall back to Python 2's urllib2
-            from urllib2 import urlopen,URLError
-from json import loads,dumps
-import sys
-
-""" Library Book Type Description:
-The book is a dictionary of the form { string : a, ... }
-
-Keys:
-  required: (ideally)
-    title - Book/Article title
-    publisher - string containing semi-colon separated list eg. "UW Press; CSC, inc."
-    authors - as above. each name is of the form "First Initials. Last" eg. "Calum T. Dalek; Conan T.B. Ladan"
-  optional:
-    subtitle - string
-    edition - integer
-    isbn - integer (it's preferred to use the isbn-13 rather than isbn-10)
-    lccn - integer: library of congress catalogue number
-    publish date - string of date (to make things easier to code/catalogue (won't be stored)
-    publish year - int (this kind of thing will have to be confirmed by cataloguer)
-    publish month - int
-    publish location - like publisher
-    
-    pages - integer - just the number of pages
-    pagination - string eg. "xviii, 1327-1850"
-    weight - string (purely for interest's sake eg. "3lb." or "3 pounds"
-    categories - list of strings?
-"""
-
-
-# look up data from openlibrary.org using isbn
-def openLibrary_isbn(ISBN):
-    isbn = str(ISBN)
-    try:
-        jsondata = urlopen("http://openlibrary.org/api/books?format=json&jscmd=data&bibkeys=ISBN:"+isbn, timeout=3)
-    except URLError:
-        return {}
-    openBook = loads(jsondata.read().decode('utf-8'))
-    if "ISBN:"+isbn not in openBook:
-        return {'isbn':isbn,'title':'Book not found'}
-    openBook = openBook["ISBN:"+isbn]
-    # create my custom dict for books with the info we want.
-    book = dict({"isbn" : isbn})
-    book["title"]=openBook["title"]
-    book["authors"]=""
-    if "authors" in openBook:
-        for v in openBook["authors"]:
-            book['authors'] += "; " + v['name']
-        book['authors'] = book['authors'][2:]
-    book["publisher"]=""
-    if "publishers" in openBook:
-        for v in openBook["publishers"]:
-            book["publisher"] += "; " + v['name']
-        book['publisher'] = book['publisher'][2:]
-    if "publish_places" in openBook:
-        book["publish location"]=""
-        for v in openBook["publish_places"]:
-            book["publish location"] += "; " + v['name']
-        book['publish location'] = book['publish location'][2:]
-
-    # for lccn, there maybe be multiple values in the query. I'm just taking the first, but the full list may be useful
-    if "lccn" in openBook['identifiers']:
-        book["lccn"]=int(openBook['identifiers']['lccn'][0])
-    if "publish_date" in openBook:
-        book['publish date']=openBook['publish_date']
-        #code to pull out year and month (hopefully)
-    if "number_of_pages" in openBook:
-        book["pages"]=openBook["number_of_pages"]
-    if "pagination" in openBook:
-        book["pagination"]=openBook["pagination"]
-    if "weight" in openBook:
-        book["weight"]=openBook["weight"]
-    if "subtitle" in openBook:
-        book["subtitle"]=openBook["subtitle"]
-    return book
-
-# look up data from openlibrary.org using lccn
-def openLibrary_lccn(LCCN):
-    lccn = str(LCCN)
-    try:
-        jsondata = urlopen("http://openlibrary.org/api/books?format=json&jscmd=data&bibkeys=lccn:"+lccn, timeout=3)
-    except URLError:
-        return {}
-    openBook = loads(jsondata.read().decode('utf-8'))
-    if "lccn:"+lccn not in openBook:
-        return {'lccn':lccn,'title':'Book not found'}
-    openBook = openBook["lccn:"+lccn]
-    # create my custom dict for books with the info we want.
-    book = {"lccn" : lccn}
-    book["title"]=openBook["title"]
-    book["authors"]=""
-    if "authors" in openBook:
-        for v in openBook["authors"]:
-            book['authors'] += "; " + v['name']
-        book['authors'] = book['authors'][2:]
-    book["publisher"]=""
-    if "publishers" in openBook:
-        for v in openBook["publishers"]:
-            book["publisher"] += "; " + v['name']
-        book['publisher'] = book['publisher'][2:]
-    if "publish_places" in openBook:
-        book["publish location"]=""
-        for v in openBook["publish_places"]:
-            book["publish location"] += "; " + v['name']
-        book['publish location'] = book['publish location'][2:]
-
-    # for isbn, there maybe be multiple values in the query. I'm just taking the first, but the full list may be useful
-    # There are also ISBN's that have non-number values :(
-    if "isbn_10" in openBook['identifiers']:
-        book["isbn"]=openBook['identifiers']['isbn_10'][0]
-    if "isbn_13" in openBook['identifiers']:
-        book["isbn"]=openBook['identifiers']['isbn_13'][0]
-    if "publish_date" in openBook:
-        book['publish date']=openBook['publish_date']
-        #code to pull out year and month (hopefully)
-    if "number_of_pages" in openBook:
-        book["pages"]=openBook["number_of_pages"]
-    if "pagination" in openBook:
-        book["pagination"]=openBook["pagination"]
-    if "weight" in openBook:
-        book["weight"]=openBook["weight"]
-    if "subtitle" in openBook:
-        book["subtitle"]=openBook["subtitle"]
-    return book
-
diff --git a/book_data.py b/book_data.py
new file mode 100644 (file)
index 0000000..e4c7c7a
--- /dev/null
@@ -0,0 +1,129 @@
+try:
+        # For Python 3.0 and later
+            from urllib.request import urlopen,URLError
+except ImportError:
+        # Fall back to Python 2's urllib2
+            from urllib2 import urlopen,URLError
+from json import loads,dumps
+import sys
+
+""" Library Book Type Description:
+The book is a dictionary of the form { string : a, ... }
+
+Keys:
+  required: (ideally)
+    title - Book/Article title
+    publisher - string containing semi-colon separated list eg. "UW Press; CSC, inc."
+    authors - as above. each name is of the form "First Initials. Last" eg. "Calum T. Dalek; Conan T.B. Ladan"
+  optional:
+    subtitle - string
+    edition - integer
+    isbn - integer (it's preferred to use the isbn-13 rather than isbn-10)
+    lccn - integer: library of congress catalogue number
+    publish date - string of date (to make things easier to code/catalogue (won't be stored)
+    publish year - int (this kind of thing will have to be confirmed by cataloguer)
+    publish month - int
+    publish location - like publisher
+    
+    pages - integer - just the number of pages
+    pagination - string eg. "xviii, 1327-1850"
+    weight - string (purely for interest's sake eg. "3lb." or "3 pounds"
+    categories - list of strings?
+"""
+
+
+# look up data from openlibrary.org using isbn
+def openLibrary_isbn(ISBN):
+    isbn = str(ISBN)
+    try:
+        jsondata = urlopen("http://openlibrary.org/api/books?format=json&jscmd=data&bibkeys=ISBN:"+isbn, timeout=3)
+    except URLError:
+        return {}
+    openBook = loads(jsondata.read().decode('utf-8'))
+    if "ISBN:"+isbn not in openBook:
+        return {'isbn':isbn,'title':'Book not found'}
+    openBook = openBook["ISBN:"+isbn]
+    # create my custom dict for books with the info we want.
+    book = dict({"isbn" : isbn})
+    book["title"]=openBook["title"]
+    book["authors"]=""
+    if "authors" in openBook:
+        for v in openBook["authors"]:
+            book['authors'] += "; " + v['name']
+        book['authors'] = book['authors'][2:]
+    book["publisher"]=""
+    if "publishers" in openBook:
+        for v in openBook["publishers"]:
+            book["publisher"] += "; " + v['name']
+        book['publisher'] = book['publisher'][2:]
+    if "publish_places" in openBook:
+        book["publish location"]=""
+        for v in openBook["publish_places"]:
+            book["publish location"] += "; " + v['name']
+        book['publish location'] = book['publish location'][2:]
+
+    # for lccn, there maybe be multiple values in the query. I'm just taking the first, but the full list may be useful
+    if "lccn" in openBook['identifiers']:
+        book["lccn"]=int(openBook['identifiers']['lccn'][0])
+    if "publish_date" in openBook:
+        book['publish date']=openBook['publish_date']
+        #code to pull out year and month (hopefully)
+    if "number_of_pages" in openBook:
+        book["pages"]=openBook["number_of_pages"]
+    if "pagination" in openBook:
+        book["pagination"]=openBook["pagination"]
+    if "weight" in openBook:
+        book["weight"]=openBook["weight"]
+    if "subtitle" in openBook:
+        book["subtitle"]=openBook["subtitle"]
+    return book
+
+# look up data from openlibrary.org using lccn
+def openLibrary_lccn(LCCN):
+    lccn = str(LCCN)
+    try:
+        jsondata = urlopen("http://openlibrary.org/api/books?format=json&jscmd=data&bibkeys=lccn:"+lccn, timeout=3)
+    except URLError:
+        return {}
+    openBook = loads(jsondata.read().decode('utf-8'))
+    if "lccn:"+lccn not in openBook:
+        return {'lccn':lccn,'title':'Book not found'}
+    openBook = openBook["lccn:"+lccn]
+    # create my custom dict for books with the info we want.
+    book = {"lccn" : lccn}
+    book["title"]=openBook["title"]
+    book["authors"]=""
+    if "authors" in openBook:
+        for v in openBook["authors"]:
+            book['authors'] += "; " + v['name']
+        book['authors'] = book['authors'][2:]
+    book["publisher"]=""
+    if "publishers" in openBook:
+        for v in openBook["publishers"]:
+            book["publisher"] += "; " + v['name']
+        book['publisher'] = book['publisher'][2:]
+    if "publish_places" in openBook:
+        book["publish location"]=""
+        for v in openBook["publish_places"]:
+            book["publish location"] += "; " + v['name']
+        book['publish location'] = book['publish location'][2:]
+
+    # for isbn, there maybe be multiple values in the query. I'm just taking the first, but the full list may be useful
+    # There are also ISBN's that have non-number values :(
+    if "isbn_10" in openBook['identifiers']:
+        book["isbn"]=openBook['identifiers']['isbn_10'][0]
+    if "isbn_13" in openBook['identifiers']:
+        book["isbn"]=openBook['identifiers']['isbn_13'][0]
+    if "publish_date" in openBook:
+        book['publish date']=openBook['publish_date']
+        #code to pull out year and month (hopefully)
+    if "number_of_pages" in openBook:
+        book["pages"]=openBook["number_of_pages"]
+    if "pagination" in openBook:
+        book["pagination"]=openBook["pagination"]
+    if "weight" in openBook:
+        book["weight"]=openBook["weight"]
+    if "subtitle" in openBook:
+        book["subtitle"]=openBook["subtitle"]
+    return book
+
index 0bef6f3..d93fc2c 100644 (file)
@@ -1,6 +1,6 @@
 import sys
 import curses
-import dbLayer as db
+import db_layer as db
 from form import bookForm,categoryForm
 
 class browserWindow:
diff --git a/cursestest.py b/cursestest.py
deleted file mode 100755 (executable)
index 2d6b5c8..0000000
+++ /dev/null
@@ -1,15 +0,0 @@
-#!/usr/bin/env python
-
-import curses
-
-def windowLayout(stdscr):
-    w = curses.newwin(5,10,10,10)
-    w.box()
-    w.refresh()
-    w.getch()
-    w2 = w.derwin(2,7,2,2)
-    w2.box()
-    w2.refresh()
-    w2.getch()
-
-curses.wrapper(windowLayout)
diff --git a/dbLayer.py b/dbLayer.py
deleted file mode 100644 (file)
index e0e940c..0000000
+++ /dev/null
@@ -1,284 +0,0 @@
-import sys
-import sqlite3
-
-dbFile = 'sqLibrary.db'
-bookTable = 'books'
-bookCategoryTable='book_categories'
-categoryTable = 'categories'
-
-
-bookTableCreation = '''
-CREATE TABLE IF NOT EXISTS books
-    (id INTEGER PRIMARY KEY AUTOINCREMENT, 
-     isbn, lccn, title, subtitle, authors, edition, 
-     publisher, publish_year, publish_month, publish_location, 
-     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 UNIQUE ON CONFLICT IGNORE);
-
-CREATE TABLE IF NOT EXISTS book_categories
-    (id INTEGER, cat_id INTEGER);
-'''
-
-columns = ['id', 'isbn', 'lccn',
-           'title', 'subtitle', 'authors', 'edition', 
-           'publisher', 'publish year', 'publish month', 'publish location', 
-           'pages', 'pagination', 'weight', 'last updated', 'deleted']
-
-bookTriggerCreation = '''
-
-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_book AFTER DELETE ON books
-BEGIN
-    DELETE FROM book_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;
-END;
-
-CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
-BEGIN
-    UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
-END;
-'''
-
-################################3
-# character escaping, etc for sql queries
-#################################
-def colify(s):
-    return s.replace(" ","_").lower()
-
-def stringify(v):
-    return '"' + str(v).strip().replace('"','""') + '"'
-
-###################################
-# book functions
-##################################
-def addBook(book):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    cols = []
-    vals = []
-    for k,v in book.items():
-        if v!="":
-            cols.append(colify(k))
-            vals.append(stringify(v))
-    
-    query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
-    c.execute(query)
-    conn.commit()
-    c.close()
-
-def updateBook(book, bookID):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    updates=[]
-    for k,v in book.items():
-        updates.append(colify(k)+"="+stringify(v))
-    query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
-    c.execute(query)
-    conn.commit()
-    c.close()
-
-def getBooks():
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    query = "SELECT * FROM "+bookTable+" WHERE deleted=0;"
-    c.execute(query)
-    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 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 "+bookTable+" WHERE DELETED=1;"
-    c.execute(query)
-    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 getBookByID(bookid):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
-    c.execute(query)
-    b = c.fetchone()
-    book = {}
-    i=0
-    for k in columns:
-        if b[i]!=None:
-            book[k]=b[i]
-        i+=1
-    c.close()
-    return book
-
-
-# removes book from catalogue
-def removeBook(bookid):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    query = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = "+str(bookid)+";"
-    c.execute(query)
-    conn.commit()
-    c.close()
-
-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 = "UPDATE " +bookTable+ " SET deleted=0 WHERE id = :id;"
-    for book in books:
-        c.execute(query1,book)
-    conn.commit()
-    c.close()
-
-# fully deletes book from removedBooks table
-def deleteBook(bookid):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
-    c.execute(query)
-    conn.commit()
-    c.close()
-
-def deleteBooks(books):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    query = "DELETE FROM " +bookTable+ " WHERE id = :id;"
-    for book in books:
-        c.execute(query, book)
-    conn.commit()
-    c.close()
-
-#########################################
-# Category related functions
-########################################
-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 = "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)
-    c = conn.cursor()
-    query = "SELECT cat_id, category FROM "+categoryTable+";"
-    c.execute(query)
-    cats = []
-    for cat_id,cat in c:
-        cats.append({'id':cat_id, 'category':cat})
-    c.close()
-    return cats
-
-def addCategory(cat):
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
-    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
-#########################################
-def createBooksTable():
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    c.executescript(bookTableCreation)
-    conn.commit()
-    c.close()
-
-def createTriggers():
-    conn = sqlite3.connect(dbFile)
-    c = conn.cursor()
-    c.executescript(bookTriggerCreation)
-    conn.commit()
-    c.close()
-
-
-createBooksTable()
-createTriggers()
diff --git a/db_layer.py b/db_layer.py
new file mode 100644 (file)
index 0000000..e0e940c
--- /dev/null
@@ -0,0 +1,284 @@
+import sys
+import sqlite3
+
+dbFile = 'sqLibrary.db'
+bookTable = 'books'
+bookCategoryTable='book_categories'
+categoryTable = 'categories'
+
+
+bookTableCreation = '''
+CREATE TABLE IF NOT EXISTS books
+    (id INTEGER PRIMARY KEY AUTOINCREMENT, 
+     isbn, lccn, title, subtitle, authors, edition, 
+     publisher, publish_year, publish_month, publish_location, 
+     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 UNIQUE ON CONFLICT IGNORE);
+
+CREATE TABLE IF NOT EXISTS book_categories
+    (id INTEGER, cat_id INTEGER);
+'''
+
+columns = ['id', 'isbn', 'lccn',
+           'title', 'subtitle', 'authors', 'edition', 
+           'publisher', 'publish year', 'publish month', 'publish location', 
+           'pages', 'pagination', 'weight', 'last updated', 'deleted']
+
+bookTriggerCreation = '''
+
+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_book AFTER DELETE ON books
+BEGIN
+    DELETE FROM book_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;
+END;
+
+CREATE TRIGGER IF NOT EXISTS insert_book_category_time AFTER INSERT ON book_categories
+BEGIN
+    UPDATE books SET last_updated = DATETIME('NOW') WHERE id = new.id;
+END;
+'''
+
+################################3
+# character escaping, etc for sql queries
+#################################
+def colify(s):
+    return s.replace(" ","_").lower()
+
+def stringify(v):
+    return '"' + str(v).strip().replace('"','""') + '"'
+
+###################################
+# book functions
+##################################
+def addBook(book):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    cols = []
+    vals = []
+    for k,v in book.items():
+        if v!="":
+            cols.append(colify(k))
+            vals.append(stringify(v))
+    
+    query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
+    c.execute(query)
+    conn.commit()
+    c.close()
+
+def updateBook(book, bookID):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    updates=[]
+    for k,v in book.items():
+        updates.append(colify(k)+"="+stringify(v))
+    query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
+    c.execute(query)
+    conn.commit()
+    c.close()
+
+def getBooks():
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "SELECT * FROM "+bookTable+" WHERE deleted=0;"
+    c.execute(query)
+    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 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 "+bookTable+" WHERE DELETED=1;"
+    c.execute(query)
+    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 getBookByID(bookid):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
+    c.execute(query)
+    b = c.fetchone()
+    book = {}
+    i=0
+    for k in columns:
+        if b[i]!=None:
+            book[k]=b[i]
+        i+=1
+    c.close()
+    return book
+
+
+# removes book from catalogue
+def removeBook(bookid):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "UPDATE " +bookTable+ " SET deleted=1 WHERE id = "+str(bookid)+";"
+    c.execute(query)
+    conn.commit()
+    c.close()
+
+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 = "UPDATE " +bookTable+ " SET deleted=0 WHERE id = :id;"
+    for book in books:
+        c.execute(query1,book)
+    conn.commit()
+    c.close()
+
+# fully deletes book from removedBooks table
+def deleteBook(bookid):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
+    c.execute(query)
+    conn.commit()
+    c.close()
+
+def deleteBooks(books):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "DELETE FROM " +bookTable+ " WHERE id = :id;"
+    for book in books:
+        c.execute(query, book)
+    conn.commit()
+    c.close()
+
+#########################################
+# Category related functions
+########################################
+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 = "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)
+    c = conn.cursor()
+    query = "SELECT cat_id, category FROM "+categoryTable+";"
+    c.execute(query)
+    cats = []
+    for cat_id,cat in c:
+        cats.append({'id':cat_id, 'category':cat})
+    c.close()
+    return cats
+
+def addCategory(cat):
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    query = "INSERT OR IGNORE INTO "+categoryTable+" (category) VALUES ("+stringify(cat)+");"
+    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
+#########################################
+def createBooksTable():
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    c.executescript(bookTableCreation)
+    conn.commit()
+    c.close()
+
+def createTriggers():
+    conn = sqlite3.connect(dbFile)
+    c = conn.cursor()
+    c.executescript(bookTriggerCreation)
+    conn.commit()
+    c.close()
+
+
+createBooksTable()
+createTriggers()
diff --git a/echokey.py b/echokey.py
deleted file mode 100755 (executable)
index efab2c0..0000000
+++ /dev/null
@@ -1,19 +0,0 @@
-#!/usr/bin/env python
-
-import curses
-
-def echokey(w):
-    curses.noecho()
-    w.keypad(1)
-    curses.cbreak()
-    w.addstr(19,20, "value")
-    w.addstr(19,40, "name")
-    ch=w.getch()
-    while (1==1):
-        w.addstr(20,20,str(ch))
-        w.addstr(20,40,curses.keyname(ch))
-        ch = w.getch()
-        w.deleteln()
-
-
-curses.wrapper(echokey)
diff --git a/gui.py b/gui.py
new file mode 100644 (file)
index 0000000..0e35764
--- /dev/null
+++ b/gui.py
@@ -0,0 +1,292 @@
+import curses
+import sys
+
+class textEntry:
+    cursor = 0
+    start = 0
+    focus = False
+    x = 0
+    y = 0
+    width = 10
+    value = ""
+
+    def __init__(self, parent_window, value=""):
+        self.w = parent_window
+        self.value = value
+
+    def setGeom(self,y,x,width):
+        self.x = x
+        self.y = y
+        self.width = width
+
+    def redraw(self):
+        self.w.addnstr(self.y,self.x, self.value[self.start:]+" "*self.width, self.width)
+        if self.focus:
+            self.w.chgat(self.y, self.x, self.width, curses.A_UNDERLINE)
+            curses.curs_set(1)
+
+    def gainFocus(self):
+        sys.stderr.write('I have focus!\n')
+        self.focus = True
+        self.mvCursor(+len(self.value))
+        self.start = max(0,self.cursor-self.width) 
+        self.redraw()
+
+    def loseFocus(self):
+        self.focus = False
+        self.cursor = 0
+        self.start = 0
+        self.redraw()
+    
+    def mvCursor(self,delta):
+        n = self.cursor + delta
+        # make sure new position is legal
+        n = max(n,0)
+        n = min(n,len(self.value))
+        self.cursor = n
+        self.start = max(0,self.cursor-self.width+1) 
+        self.redraw()
+        col = self.x + self.cursor - self.start
+        self.w.move(self.y,col)
+
+    def insert(self,ch):
+        c = self.cursor
+        self.value = self.value[:c] +ch+  self.value[c:]
+        self.mvCursor(+1)
+
+    def backspace(self):
+        if self.cursor>0:
+            c = self.cursor
+            self.value=self.value[:c-1] + self.value[c:]
+            self.mvCursor(-1)
+
+    def delete(self):
+        c = self.cursor
+        self.value = self.value[:c] + self.value[c+1:]
+        self.mvCursor(0)
+
+    def handle_input(self,ch):
+        if ch==curses.KEY_LEFT:
+            self.mvCursor(-1)
+        elif ch==curses.KEY_HOME:
+            self.mvCursor(-len(self.value))
+        elif ch==curses.KEY_RIGHT:
+            self.mvCursor(+1)
+        elif ch==curses.KEY_END:
+            self.mvCursor(+len(self.value))
+
+        elif ch>=32 and ch<=126:
+            self.insert(curses.keyname(ch))
+        elif ch==curses.KEY_BACKSPACE:
+            self.backspace()
+        elif ch==curses.KEY_DC:
+            self.delete()
+
+
+
+class formWindow:
+    mx = my = 0
+    hl = 0
+    bt = -1
+    left = 0
+    top = 2
+    row = 2
+    caption = "Form"
+    blabel = "Done"
+    labels = ["label1"]
+    entries=[]
+
+    commands = [('pU', 'top'),('pD', 'bottom'),('Es', 'cancel')]
+
+    def clear(self):
+        self.w.erase()
+        self.w.refresh()
+
+    def __init__(self,window,helpbar,book={}):
+        self.w = window
+        self.w.resize(len(self.labels)+6,50)
+        self.hb = helpbar
+        self.makeEntries()
+        self.updateGeometry()
+        self.updateEntries(book)
+
+    def makeEntries(self):
+        for e in range(len(self.labels)):
+            self.entries.append(textEntry(self.w))
+
+    def updateGeometry(self):
+        (self.my, self.mx) = self.w.getmaxyx()
+        self.left=0
+        for l in self.labels:
+            self.left = max(len(l),self.left)
+        self.left += 4
+        width = self.mx-self.left-2
+        self.top = 2
+        for r in range(len(self.entries)):
+            self.entries[r].setGeom(r+self.top, self.left, width)
+        # next, the buttons
+        self.brow = self.top+len(self.labels)+1
+        self.bcol = [self.mx-len(self.blabel)-14, self.mx-len(self.blabel)-4]
+        self.bwidth = [8,len(self.blabel)+2]
+
+    def updateEntries(self,book):
+        e = 0
+        for l in self.labels:
+            sys.stderr.write('updating label: '+l+'\n')
+            if l.lower() in book:
+                sys.stderr.write('   '+l+' found\n')
+                self.entries[e].value = str(book[l.lower()])
+            else:
+                sys.stderr.write('   '+l+' notfound\n')
+                self.entries[e].value = ""
+            e += 1 
+
+    def redraw(self):
+        self.w.box()
+        self.w.addstr(0,(self.mx-len(self.caption))//2,self.caption)
+        r=0
+        for l in self.labels:
+            c = self.left-len(l)-2
+            self.w.addstr(r+self.top,c,l+":")
+            self.entries[r].redraw()
+            r+=1
+        self.w.addstr(self.brow,self.bcol[0], "<cancel>  <"+self.blabel+">")
+        self.w.refresh()
+
+    def refresh(self):
+        self.hb.commands = self.commands
+        self.hb.refresh()
+        self.updateGeometry()
+        self.redraw()
+
+    def highlightButton(self):
+        self.w.chgat(self.brow, self.bcol[self.bt], self.bwidth[self.bt], curses.A_REVERSE)
+        curses.curs_set(0)
+
+    def unHighlightButton(self):
+        self.w.chgat(self.brow,1,self.mx-2,curses.A_NORMAL)
+
+    def mvFocus(self,delta):
+        if self.bt==-1:
+            self.entries[self.hl].loseFocus()
+        else:
+            self.unHighlightButton()
+        new = self.hl+delta
+        new = max(new,0)
+        new = min(new,len(self.labels))   # the extra is for the buttons
+        self.hl = new
+        if new == len(self.labels):
+            self.bt = 1
+            self.bt = min(self.bt,1)
+            self.highlightButton()
+        else:
+            self.bt=-1
+            self.entries[self.hl].gainFocus()
+
+
+    def returnValues(self):
+        book = {}
+        for k,e in zip(self.labels, self.entries):
+            if v!="" and k.lower()!="publish date":
+                book[k.lower()]=e.value
+        return book
+
+    def eventLoop(self):
+        self.w.keypad(1)
+        self.refresh()
+        self.hl=0;
+        self.entries[self.hl].gainFocus()
+
+        ch = self.w.getch()
+        while ch != 27:
+            self.handleInput(ch)
+            if ch==10 or ch==curses.KEY_ENTER:
+                if self.bt==0:
+                    return {}
+                elif self.bt==1:
+                    return self.returnValues()
+                else:
+                    self.mvFocus(+1)
+            self.w.refresh()
+            ch = self.w.getch()
+        curses.curs_set(0)
+        return {}
+
+
+    def handleInput(self,ch):
+        if ch==curses.KEY_UP:
+            self.mvFocus(-1)
+        elif ch==curses.KEY_PPAGE:
+            self.mvFocus(-len(self.labels))
+        elif ch==curses.KEY_DOWN:
+            self.mvFocus(+1)
+        elif ch==curses.KEY_NPAGE:
+            self.mvFocus(+len(self.labels))
+        elif ch==curses.KEY_LEFT:
+            if self.bt==-1:
+                self.entries[self.hl].handle_input(ch)
+            else:
+                self.unHighlightButton()
+                self.bt=0
+                self.highlightButton()
+        elif ch==curses.KEY_HOME:
+            if self.bt==-1:
+                self.mvCursor(-len(self.entries[self.hl]))
+        elif ch==curses.KEY_RIGHT:
+            if self.bt==-1:
+                self.entries[self.hl].handle_input(ch)
+            else:
+                self.unHighlightButton()
+                self.bt=1
+                self.highlightButton()
+        else:
+            if self.bt==-1:
+                self.entries[self.hl].handle_input(ch)
+
+        
+        
+
+class bookForm(formWindow):
+    caption = "Add a Book"
+    blabel = "Add"
+    labels = ["ISBN", "LCCN", "Title", "Subtitle", "Authors", "Edition",
+              "Publisher", "Publish Date", "Publish Year", "Publish Month", "Publish location",
+              "Pages", "Pagination", "Weight"]
+    
+
+    # redefineable functions lookup is called when 'enter' is pressed on ISBN
+    # and returns the looked-up book. Default returns nothing
+    def lookup_isbn(self,isbn):
+        return {'isbn':isbn}
+    
+    def lookup_lccn(self,lccn):
+        return {'lccn':lccn}
+
+    def returnBook(self):
+        return self.returnValues()
+
+    def handleInput(self,ch):
+        if ch==10 or ch==curses.KEY_ENTER:
+            if self.hl==0:          # lookup by isbn
+                book = self.lookup_isbn(self.entries[0].value)
+                if book != {}:
+                    sys.stderr.write('updating entries\n')
+                    self.updateEntries(book)
+                self.refresh()
+                self.mvFocus(+7)
+            if self.hl==1:          # lookup by lccn
+                book = self.lookup_lccn(self.entries[1].value)
+                if book != {}:
+                    self.updateEntries(book)
+                self.refresh()
+                self.mvFocus(+6)
+        else:
+            formWindow.handleInput(self,ch)
+
+class categoryForm(formWindow):
+    caption = "Add a Category"
+    blabel = "Add"
+    labels = ["Category"]
+
+    def returnValues(self):
+        return self.entries
diff --git a/helpBar.py b/helpBar.py
deleted file mode 100644 (file)
index af18358..0000000
+++ /dev/null
@@ -1,69 +0,0 @@
-import curses
-
-class helpBar:
-    # commands is in the form (key, command_name)
-    commands = []
-    mx=my=0
-    x=y=0
-    colWidth = 25
-    numCols=1
-
-    def __init__(self, window):
-        self.w = window
-
-    def updateGeometry(self):
-        (self.my, self.mx) = self.w.getmaxyx()
-        (self.y, self.x) = self.w.getbegyx()
-        self.numCols = self.mx//self.colWidth
-        numRows = len(self.commands)//self.numCols +1
-        self.y += self.my - numRows
-        self.my = numRows
-        self.w.mvwin(0,0)
-        self.w.resize(self.my,self.mx)
-        self.w.mvwin(self.y,self.x)
-
-    def refresh(self):
-        self.clear()
-        self.updateGeometry()
-        r=0
-        c=0
-        for key,command in self.commands:
-            self.w.addnstr(r,c,key+" "+command+" "*self.colWidth,self.colWidth-1)
-            self.w.chgat(r,c,2,curses.A_REVERSE)
-            c+=self.colWidth
-            if c > self.colWidth*self.numCols:
-                c=0
-                r+=1
-        self.w.refresh()
-
-    def clear(self):
-        self.w.erase()
-        self.w.refresh()
-
-    def getSearch(self):
-        self.clear()
-        self.w.addstr(0,0,"/")
-        string = ""
-        done = False
-        self.w.keypad(1)
-        ch = self.w.getch()
-        while (not done):
-            if ch == curses.KEY_ENTER or ch == 10:
-                return string
-            elif ch == 27: # escape
-                return ""
-            elif ch == curses.KEY_BACKSPACE and string !="":
-                self.w.addstr(0,1," "*len(string))
-                string = string[0:len(string)-1]
-                self.w.addstr(0,1,string)
-            elif ch>=32 and ch<=126:
-                char = curses.keyname(ch)
-                string = string + char
-                self.w.addstr(0,1,string)
-            self.w.refresh()
-            ch = self.w.getch()
-
-    def display(self,string):
-        self.clear()
-        self.w.addstr(0,1,string)
-        self.w.refresh()
diff --git a/help_bar.py b/help_bar.py
new file mode 100644 (file)
index 0000000..af18358
--- /dev/null
@@ -0,0 +1,69 @@
+import curses
+
+class helpBar:
+    # commands is in the form (key, command_name)
+    commands = []
+    mx=my=0
+    x=y=0
+    colWidth = 25
+    numCols=1
+
+    def __init__(self, window):
+        self.w = window
+
+    def updateGeometry(self):
+        (self.my, self.mx) = self.w.getmaxyx()
+        (self.y, self.x) = self.w.getbegyx()
+        self.numCols = self.mx//self.colWidth
+        numRows = len(self.commands)//self.numCols +1
+        self.y += self.my - numRows
+        self.my = numRows
+        self.w.mvwin(0,0)
+        self.w.resize(self.my,self.mx)
+        self.w.mvwin(self.y,self.x)
+
+    def refresh(self):
+        self.clear()
+        self.updateGeometry()
+        r=0
+        c=0
+        for key,command in self.commands:
+            self.w.addnstr(r,c,key+" "+command+" "*self.colWidth,self.colWidth-1)
+            self.w.chgat(r,c,2,curses.A_REVERSE)
+            c+=self.colWidth
+            if c > self.colWidth*self.numCols:
+                c=0
+                r+=1
+        self.w.refresh()
+
+    def clear(self):
+        self.w.erase()
+        self.w.refresh()
+
+    def getSearch(self):
+        self.clear()
+        self.w.addstr(0,0,"/")
+        string = ""
+        done = False
+        self.w.keypad(1)
+        ch = self.w.getch()
+        while (not done):
+            if ch == curses.KEY_ENTER or ch == 10:
+                return string
+            elif ch == 27: # escape
+                return ""
+            elif ch == curses.KEY_BACKSPACE and string !="":
+                self.w.addstr(0,1," "*len(string))
+                string = string[0:len(string)-1]
+                self.w.addstr(0,1,string)
+            elif ch>=32 and ch<=126:
+                char = curses.keyname(ch)
+                string = string + char
+                self.w.addstr(0,1,string)
+            self.w.refresh()
+            ch = self.w.getch()
+
+    def display(self,string):
+        self.clear()
+        self.w.addstr(0,1,string)
+        self.w.refresh()
index 6be597b..387b692 100755 (executable)
@@ -1,12 +1,12 @@
 #!/usr/bin/env python3
 
 import curses
-import dbLayer as db
+import db_layer as db
 import browser
 import form as form
-import helpBar
+import help_bar as helpBar
 
-import bookData
+import book_data
 
 
 stdscr=0
diff --git a/ncurses_tools/cursestest.py b/ncurses_tools/cursestest.py
new file mode 100755 (executable)
index 0000000..2d6b5c8
--- /dev/null
@@ -0,0 +1,15 @@
+#!/usr/bin/env python
+
+import curses
+
+def windowLayout(stdscr):
+    w = curses.newwin(5,10,10,10)
+    w.box()
+    w.refresh()
+    w.getch()
+    w2 = w.derwin(2,7,2,2)
+    w2.box()
+    w2.refresh()
+    w2.getch()
+
+curses.wrapper(windowLayout)
diff --git a/ncurses_tools/echokey.py b/ncurses_tools/echokey.py
new file mode 100755 (executable)
index 0000000..efab2c0
--- /dev/null
@@ -0,0 +1,19 @@
+#!/usr/bin/env python
+
+import curses
+
+def echokey(w):
+    curses.noecho()
+    w.keypad(1)
+    curses.cbreak()
+    w.addstr(19,20, "value")
+    w.addstr(19,40, "name")
+    ch=w.getch()
+    while (1==1):
+        w.addstr(20,20,str(ch))
+        w.addstr(20,40,curses.keyname(ch))
+        ch = w.getch()
+        w.deleteln()
+
+
+curses.wrapper(echokey)