readparagraph.py

#!/usr/bin/python

import sqlite3
import sys
import os
import inspect


def eprint(*args, **kwargs):
    print(*args, file=sys.stderr, **kwargs)


def connectDB():
    try:
        path = os.path.dirname(os.path.realpath(__file__)) + "/rParagraphDB.db"
        con = sqlite3.connect(path)
        return con
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))


def createDB():
    con = connectDB()
    c = con.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS FILES(
        id INTEGER PRIMARY KEY ,
        file text  NOT NULL UNIQUE,
        current INTEGER DEFAULT 0 );""")
    c.execute("""CREATE TABLE IF NOT EXISTS HISTORY(
        fid INTEGER UNIQUE , paragraph INTEGER );""")
    c.execute("""CREATE TABLE IF NOT EXISTS WORDS(
        id INTEGER PRIMARY KEY ,
        fid INTEGER,
        word text  NOT NULL,
        UNIQUE (fid,word)
    );""")
    c.execute("""CREATE TABLE IF NOT EXISTS FAVS(
        id INTEGER PRIMARY KEY ,
        fid INTEGER,
        paragraph INTEGER,
        UNIQUE (fid,paragraph)
    );""")
    con.commit()
    c.close()
    con.close()


def addFile(f):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute("""
                  INSERT OR IGNORE INTO FILES (file)
                  VALUES ("{f}") """ .format(f=f))
        con.commit()
        print(c.lastrowid)
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def addFav(fid, p):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute("""
                  INSERT OR IGNORE INTO FAVS (fid,paragraph)
                  VALUES("{i}", "{p}" )
                  """ .format(i=fid, p=p))
        con.commit()
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def getFavs(fid):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute(""" select id, paragraph from FAVS
                  where fid="{f}" ORDER BY id ASC
                  """.format(f=fid))
        rows = c.fetchall()
        for row in rows:
            print(row[0], ":", row[1])
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def getFav(fid, i):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute(""" select paragraph from FAVS
                  where fid="{f}" and id="{i}"
                  """.format(f=fid, i=i))
        row = c.fetchone()
        if (row):
            print(row[0])
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def setParagraph(fid, p):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute("""
                  INSERT OR REPLACE INTO HISTORY (fid,paragraph)
                  VALUES("{i}", "{p}" )
                  """ .format(i=fid, p=p))
        con.commit()
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def getParagraph(fid):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute(""" select paragraph from HISTORY
                  where fid="{i}" """.format(i=fid))
        row = c.fetchone()
        if (row):
            print(row[0])
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def addWord(fid, word):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute("""
        INSERT OR IGNORE INTO WORDS (fid,word)
        VALUES("{i}","{w}")
        """ .format(i=fid, w=word))
        con.commit()
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def rmWord(fid, word):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute("""
        DELETE FROM WORDS
        WHERE fid="{i}" and word="{w}"
        """ .format(i=fid, w=word))
        con.commit()
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def getWord(fid):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute("""
        SELECT word from WORDS
        where fid="{i}"
        ORDER BY id ASC """ .format(i=fid))
        rows = c.fetchall()
        for row in rows:
            print(row[0])
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def getFile():
    try:
        con = connectDB()
        c = con.cursor()
        c.execute(""" select id, file from FILES
        where current="1" """)
        row = c.fetchone()
        if (row):
            print("%d:%s" % (row[0], row[1]))
        c.close()
    except sqlite3.Error as error:
        eprint("@%s:%s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def getALL():
    try:
        con = connectDB()
        c = con.cursor()
        c.execute(""" select id, file from FILES
        ORDER BY id ASC """)
        rows = c.fetchall()
        for row in rows:
            print("%d:%s" % (row[0], row[1]))
        c.close()
    except sqlite3.Error as error:
        eprint("@%s:%s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def setFile(fid):
    try:
        con = connectDB()
        c = con.cursor()
        c.execute(""" UPDATE FILES set current=0
        where id <> "{i}" """.format(i=fid))
        c.execute(""" UPDATE FILES set current=1
        where id="{i}" """.format(i=fid))
        con.commit()
        c.close()
    except sqlite3.Error as error:
        eprint("@%s: %s" % (inspect.stack()[0][3], error))
    finally:
        if (con):
            con.close()


def myfuncSwitch(arg):
    cmd = arg[1]
    switcher = {
        "add": addFile,
        "get": getFile,
        "set": setFile,
        "all": getALL,
        "setp": setParagraph,
        "getp": getParagraph,
        "addw": addWord,
        "getw": getWord,
        "rmw": rmWord,
        "addf": addFav,
        "getf": getFav,
        "getfs": getFavs
    }
    func = switcher.get(cmd)
    func(*arg[2:])


def main():
    createDB()
    myfuncSwitch(sys.argv)


if __name__ == "__main__":
    main()