path: root/src/db_sqlite.py

import os
import sys
from time import strptime
from PyQt5.QtCore import QDate
from PyQt5.QtSql import QSqlDatabase, QSqlQuery
import src.globals as Globals
from src.group import Group
from src.entry import Entry

def initDB():
    """
    Check for existing database. If it doesn't exist, build it
    """
    if not os.path.exists(Globals.db_path) or not os.stat(Globals.db_path).st_size:
        createTables()

    loadFromTables()

def createTables():
    """
    Create database at a specified Globals.db_path
    """
    print(Globals.db_path)
    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    # Create database parent directory if necessary
    if not os.path.exists(os.path.dirname(Globals.db_path)):
        try:
            os.mkdir(os.path.dirname(Globals.db_path))
        except:
            print("Unable to open data source file.")
            sys.exit(1)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()
    # Erase database contents so that we don't have duplicates
    query.exec_("DROP TABLE groups")
    query.exec_("DROP TABLE entries")

    query.exec_("""
        CREATE TABLE groups (
            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
            name VARCHAR(255) NOT NULL,
            column TINYINT(1) DEFAULT FALSE,
            link VARCHAR(255) NOT NULL,
            hidden TINYINT(1) DEFAULT FALSE
        )
        """)

    query.exec_("""
        CREATE TABLE entries (
            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
            parent_id REFERENCES groups (id),
            description VARCHAR(255) NOT NULL,
            due_date TEXT DEFAULT NULL,
            alt_due_date VARCHAR(255) DEFAULT NULL,
            link VARCHAR(255) DEFAULT NULL,
            color VARCHAR(255) DEFAULT NULL,
            highlight VARCHAR(255) DEFAULT NULL,
            done TINYINT(1) DEFAULT FALSE,
            hidden TINYINT(1) DEFAULT FALSE
        )
        """)
        
    database.close()

def loadFromTables():
    """
    Load groups and entries into global variables
    """
    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    # Load groups
    query.exec_("SELECT * FROM groups")
    while query.next():
        record = query.record()
        Globals.groups.append(
                Group(
                    record.field("id").value(), 
                    record.field("name").value(), 
                    record.field("column").value(),
                    record.field("link").value(),
                    record.field("hidden").value()))

    # Load entries
    query.exec_("SELECT * FROM entries")
    while query.next():
        record = query.record()
        # create a QDate if the due date is set
        if record.field("due_date").value():
            due_date_struct = strptime(record.field("due_date").value(), "%Y-%m-%d")
            due_date = QDate(due_date_struct.tm_year, due_date_struct.tm_mon, due_date_struct.tm_mday)
        else:
            due_date = ""
        Globals.entries.append(
                Entry(
                    record.field("id").value(),
                    record.field("parent_id").value(),
                    record.field("description").value(),
                    due_date,
                    record.field("alt_due_date").value(),
                    record.field("link").value(),
                    record.field("color").value(),
                    record.field("highlight").value(),
                    record.field("done").value(),
                    record.field("hidden").value()))

    database.close()

def insertGroup(new_group):
    """
    Insert group to the database at Globals.db_path
    """
    output = -1

    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    query.prepare("""
        INSERT INTO groups (name, column, link) VALUES (?, ?, ?)
        """)
    query.addBindValue(new_group.name)
    query.addBindValue(new_group.column)
    query.addBindValue(new_group.link)
    query.exec_()

    output = query.lastInsertId()

    database.close()

    return output

def insertEntry(new_entry):
    """
    Insert entry to the database at Globals.db_path
    """
    output = -1

    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    query.prepare("""
        INSERT INTO entries (parent_id, description, due_date, alt_due_date, link, color, highlight) VALUES (:p_id, :desc, :due, :alt_due, :link, :color, :highlight)
        """)
    query.bindValue(":p_id", new_entry.parent_id)
    query.bindValue(":desc", new_entry.desc)
    if new_entry.due:
        query.bindValue(":due", "{0}-{1}-{2}".format( 
                                                     new_entry.due.year(), 
                                                     new_entry.due.month(), 
                                                     new_entry.due.day()))
    else:
        query.bindValue(":due", "")
    query.bindValue(":alt_due", new_entry.due_alt)
    query.bindValue(":link", new_entry.link)
    query.bindValue(":color", new_entry.color)
    query.bindValue(":highlight", new_entry.highlight)
    success = query.exec_()
    # DEBUG
    #print(query.lastError().text())
    #print(query.boundValues())
    #if success:
    #    print("Query succeeded")
    #else:
    #    print("Query failed")

    output = query.lastInsertId()

    database.close()

    return output

def updateGroup(group):
    """
    Update group by its id
    """
    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    query.prepare("""
        UPDATE groups SET name = ?, column = ?, link = ?, hidden = ? WHERE id = ?
        """)
    query.addBindValue(group.name)
    query.addBindValue(group.column)
    query.addBindValue(group.link)
    query.addBindValue(group.hidden)
    query.addBindValue(group.id)
    query.exec_()

    database.close()

def updateEntry(entry):
    """
    Update entry by its id
    """
    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    query.prepare("""
        UPDATE entries SET 
            description = :desc, 
            due_date = :due, 
            alt_due_date = :alt_due, 
            link = :link,
            color = :color,
            highlight = :highlight,
            done = :done,
            hidden = :hidden
            WHERE id = :id
        """)
    query.bindValue(":desc", entry.desc)
    if entry.due:
        query.bindValue(":due", "{0}-{1}-{2}".format( 
                                                     entry.due.year(), 
                                                     entry.due.month(), 
                                                     entry.due.day()))
    else:
        query.bindValue(":due", "")
    query.bindValue(":alt_due", entry.due_alt)
    query.bindValue(":link", entry.link)
    query.bindValue(":color", entry.color)
    query.bindValue(":highlight", entry.highlight)
    query.bindValue(":done", entry.done)
    query.bindValue(":hidden", entry.hidden)
    query.bindValue(":id", entry.id)
    query.exec_()

    database.close()

def removeGroup(group_id):
    """
    Remove a group by id from the database 
    (actually set hidden to true, don't permanently delete it)
    """

    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    # First, set entries to hidden
    query.prepare("""
        UPDATE entries SET hidden = 1 WHERE parent_id = ?
        """)
    query.addBindValue(group_id)
    query.exec_()

    # Now, set the group to hidden
    query.prepare("""
        UPDATE groups SET hidden = 1 WHERE id = ?
        """)
    query.addBindValue(group_id)
    query.exec_()

    output = query.numRowsAffected()
    database.close()
    return output

def removeEntry(entry_id):
    """
    Remove a group by id from the database 
    (actually set hidden to true, don't permanently delete it)
    """
    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    # Set entry to hidden
    query.prepare("""
        UPDATE entries SET hidden = 1 WHERE id = ?
        """)
    query.addBindValue(entry_id)
    query.exec_()

    output = query.numRowsAffected()
    database.close()
    return output

def cleanHidden():
    """
    Permanently delete removed/hidden groups and entries
    """
    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
    database.setDatabaseName(Globals.db_path)

    if not database.open():
        print("Unable to open data source file.")
        sys.exit(1) # Error out. TODO consider throwing a dialog instead

    query = QSqlQuery()

    # Remove hidden entries
    query.exec_("""
        DELETE FROM entries WHERE hidden = 1
        """)

    # Remove hidden groups
    query.exec_("""
        DELETE FROM groups WHERE hidden = 1
        """)

    database.close()