Database design using Closure Table for tagging system

Question

I created these database tables with the inspiration in NjDevPro github repository. The design uses Closure Table for implementation of hierarchical tagging system in Python, which I would like to improve. The database I use is SQLite.

def create_tables(option, opt, value, parser):
    """Create database tables"""
    con = sqlite.connect(connectionString)
    cur = con.cursor()
    cur.executescript('''
        -- DROP TABLE IF EXISTS Tag;
        -- DROP TABLE IF EXISTS TagTree;
        -- DROP TABLE IF EXISTS Book;
        -- DROP TABLE IF EXISTS BookTag;
        
        CREATE TABLE IF NOT EXISTS Tag
                (id INTEGER PRIMARY KEY,
                name TEXT UNIQUE NOT NULL
                );

        CREATE TABLE IF NOT EXISTS TagTree(
                parent INTEGER NOT NULL DEFAULT 0,
                child INTEGER NOT NULL DEFAULT 0,
                depth INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY (parent, child),
                FOREIGN KEY(parent) REFERENCES Tag(id),
                FOREIGN KEY(child) REFERENCES Tag(id));

        CREATE TABLE IF NOT EXISTS Book(
                id INTEGER PRIMARY KEY,
                title TEXT,
                authors TEXT,
                publisher TEXT,
                year TEXT,
                lang TEXT,
                isbn TEXT UNIQUE,
                paths TEXT);
   CREATE TABLE IF NOT EXISTS BookTag(
                id INTEGER PRIMARY KEY,
                book_id INTEGER,
                tag_id INTEGER,
                FOREIGN KEY(book_id) REFERENCES Book(id),
                FOREIGN KEY(tag_id) REFERENCES Tag(id));
                 

        CREATE UNIQUE INDEX IF NOT EXISTS tree_idx ON TagTree(parent, depth, child);
        CREATE UNIQUE INDEX IF NOT EXISTS tree_idx2 ON TagTree(child, parent, depth);''')
    con.commit()

    print "Tables created"

    # insert root
    cur.execute("INSERT OR IGNORE INTO Tag (name) values ('root')")
    cur.execute("INSERT OR IGNORE INTO TagTree (parent, child, depth) values (1, 1, 0)")
    con.commit()

    print "Root tag created"

    con.close()
    exit(0)

So far I created this hierarchy.

$ python books.py --print
tag: root
root
╠══ IT
║   ╠══ AI
║   ║   ╠══ knowledge_representation
║   ║   ╚══ machine_learning
║   ╠══ databases
║   ╚══ programming
║       ╚══ programming_languages
║           ╚══ python
╚══ health

The issue is with the add new tag functionality, because I want to add tag machine_learning to be a child of Python and a child of AI too.

Even better example is firewall as a child of networking and child of security.

How can I improve the database design to allow this? What are the approaches to this issue?

def createTag(option, opt, value, parser):
  """create a new tag"""
  tag_name = raw_input("enter new tag name: ")
  db = DB()
  setCompleter(db.getAllTags())
  tag_parent = raw_input("enter parent tag name: ")
  con = sqlite.connect(connectionString)
  cur = con.cursor()
  cur.execute('INSERT OR IGNORE INTO Tag (name) values (:tag_name)', locals())
  row_child = cur.lastrowid

  row_parent = cur.execute("SELECT id from Tag where name = :tag_parent", locals()).fetchone()[0]
  cur.execute('INSERT INTO TagTree(parent, child, depth) VALUES (?,?,0)', (row_child, row_child))
  cur.execute('''
        INSERT OR REPLACE INTO TagTree(parent, child, depth) 
            SELECT p.parent, c.child, p.depth + c.depth + 1
            FROM TagTree p, TagTree c
            WHERE p.child = ? AND c.parent = ?''', (row_parent, row_child))
  con.commit()
  print "new tag inserted"
  exit(0)

def tag_given_book(option, opt, value, parser):
  bookID = raw_input("enter book ID: ")
  book = Book(bookID = bookID)
  db = DB()
  setCompleter(db.getAllTags())
  tag_name = raw_input("enter tag: ")
  con = sqlite.connect(connectionString)
  cur = con.cursor()

  tag_id = cur.execute("SELECT id FROM Tag WHERE name = :tag_name", locals()).fetchone()[0]

  cur.execute("INSERT INTO BookTag(book_id, tag_id) VALUES (:bookID, :tag_id)", locals())
  con.commit()
  con.close()

  print "Book tagged."
  exit(0)

I apologize for Python 2.7. I came back to my older project. It will be converted to Python 3 someday.

Answer 1

I apologize for Python 2.7.

The time is now because 2.x is deprecated. It might not be too difficult. Slap some parentheses around the print statements, and replace raw_input with input to start.

Layout

The Hercule Poirot in me very badly wants you to nudge that second CREATE TABLE line below over:

        CREATE TABLE IF NOT EXISTS Book(
                id INTEGER PRIMARY KEY,
                title TEXT,
                authors TEXT,
                publisher TEXT,
                year TEXT,
                lang TEXT,
                isbn TEXT UNIQUE,
                paths TEXT);
   CREATE TABLE IF NOT EXISTS BookTag(

Please change the indentation to:

    CREATE TABLE IF NOT EXISTS Book(
            id INTEGER PRIMARY KEY,
            title TEXT,
            authors TEXT,
            publisher TEXT,
            year TEXT,
            lang TEXT,
            isbn TEXT UNIQUE,
            paths TEXT);
    CREATE TABLE IF NOT EXISTS BookTag(

You use inconsistent indentation elsewhere, too. The first code block uses the preferred 4-space indent levels. However, your second code block only uses 2-space. Use 4-space as recommended by the PEP 8 style guide.

Naming

PEP-8 also recommends snake_case for function names. Two of your three functions already do so; let's make it 3 for 3. Change:

createTag

to:

create_tag

Program flow

It seems strange that every function ends with:

exit(0)

Consider moving those statements out into the main flow of the program.

Documentation

It is good that you added docstrings to some of your functions. For example:

def createTag(option, opt, value, parser):
  """create a new tag"""

However, that docstring merely restates the name of the function, and it could use more elaboration. For example, you could describe the types of the input parameters. In fact, it looks like none of the inputs are used in the function. If that is the case, then you could simplify the code by removing them.