jwmud/jwmudlib/database.py

# This file is part of jwmud, written by Helmut Pozimski in 2014.
#
#       jwmud is free software: you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation, version 2 of the License.
#
#       jwmud is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.
#
#       You should have received a copy of the GNU General Public License
#       along with jwmud.  If not, see <http://www.gnu.org/licenses/>.

# -*- coding: utf8 -*-

import sqlite3

from jwmudlib import jwmu_exceptions


class db:
    def __init__(self, db_path):
        """Constructor, establishes the initial connection to the database."""
        try:
            self.__connection = sqlite3.connect(db_path)
        except sqlite3.OperationalError:
            raise jwmu_exceptions.DataBaseAccessFailed()
        else:
            self.__cursor = self.__connection.cursor()

    def __del__(self):
        """Destructor, closes the connection to the database"""
        self.__connection.close()

    def __ExecuteQuery(self, query, arguments=None):
        """Executes any query and returns the result"""
        if arguments is None:
            query_result = self.__cursor.execute(query)
        else:
            query_result = self.__cursor.execute(query, arguments)
        return query_result

    def __ExecuteStatement(self, statement, arguments=None):
        """Executes any statements that perform a write access to the
        database

        """
        try:
            if arguments is None:
                self.__cursor.execute(statement)
            else:
                self.__cursor.execute(statement, arguments)
        except sqlite3.OperationalError:
            raise jwmu_exceptions.DataBaseWriteFailed()
        else:
            self.__connection.commit()

    def Populate(self):
        """Creates the initial database structure with all the necessary
        tables.

        """
        create_weekdays = """CREATE TABLE weekdays(
        day_id INTEGER PRIMARY KEY NOT NULL,
        alarm_time TEXT,
        name TEXT
        );"""
        create_categories = """CREATE TABLE categories(
        category_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        name TEXT NOT NULL,
        alarm_time TEXT
        );"""
        create_alarmdays = """CREATE TABLE alarmdays(
        date TEXT NOT NULL,
        alarm_time TEXT,
        category INTEGER DEFAULT NULL,
        FOREIGN KEY(category) REFERENCES category(category_id),
        PRIMARY KEY(date, alarm_time)
        );"""
        # create the empty tables
        self.__ExecuteStatement(create_weekdays)
        self.__ExecuteStatement(create_categories)
        self.__ExecuteStatement(create_alarmdays)
        # fill the weekdays table with the existing 7 days of the week,
        # these are always hardcoded
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (1, 'undefined', 'Monday')")
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (2, 'undefined', 'Tuesday')")
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (3, 'undefined', 'Wednesday')")
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (4, 'undefined', 'Thursday')")
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (5, 'undefined', 'Friday')")
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (6, 'undefined', 'Saturday')")
        self.__ExecuteStatement("INSERT INTO weekdays (day_id, alarm_time, "
                                "name) VALUES (7, 'undefined', 'Sunday')")