stov/lib_stov/database.py

#       This file is part of stov, written by Helmut Pozimski in 2014.
#
#       stov 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.
#
#       stov 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 stov.  If not, see <http://www.gnu.org/licenses/>.


# -*- coding: utf8 -*-

from __future__ import unicode_literals

import sqlite3

from lib_stov import stov_exceptions


class db(object):
    def __init__(self, path, version):
        """Constructor of the db class, populates the object with the relevant
        attributes, connects to the database and creates it if asked to.

        """
        self.__path = path
        self.__version = version

        try:
            self.__connection = sqlite3.connect(self.__path)
        except sqlite3.OperationalError:
            raise stov_exceptions.DBConnectionFailedException()
        else:
            self.__cursor = self.__connection.cursor()

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

        self.__connection.close()

    def __ExecuteStatement(self, statement, argument=None):
        """Executes a statement, works as a wrapper around cursor execute."""

        try:
            if argument is None:
                result = self.__cursor.execute(statement)
            else:
                result = self.__cursor.execute(statement, argument)
        except sqlite3.OperationalError:
            raise stov_exceptions.DBWriteAccessFailedException()
        else:
            self.__connection.commit()
            return result

    def Populate(self):
        """Populates the database with the initial structure."""

        self.__ExecuteStatement("""CREATE TABLE subscriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            title TEXT,
            name TEXT,
            type TEXT,
            searchstring TEXT,
            directory TEXT,
            disabled INTEGER DEFAULT 0
            );""")
        self.__ExecuteStatement("""CREATE TABLE videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            title TEXT,
            description TEXT,
            ytid TEXT,
            subscription_id INTEGER,
            downloaded INT,
            failcnt INTEGER DEFAULT 0
            );""")
        exit_message = _("Created initial database tables.")
        return exit_message

    def Update(self):
        """Updates the database structure to match the current version"""

        if int(self.__version) == 1:
            """Changes between version 1 and 2"""
            self.__ExecuteStatement("ALTER TABLE videos ADD COLUMN failcnt \
            INTEGER DEFAULT 0;")
            self.__version = 2
        if int(self.__version) == 2:
            """Changes between version 2 and 3"""
            self.__ExecuteStatement("ALTER TABLE subscriptions ADD COLUMN disabled \
            INTEGER DEFAULT 0;")
            self.__ExecuteStatement("UPDATE subscriptions SET disabled=0;")
            self.__version=3

    def GetVersion(self):
        """Simple getter method, returns the DB version"""

        return self.__version

    def DeleteSubscription(self, id):
        """Deletes a subscription and all associated videos from the database"""

        checkquery = "SELECT * FROM subscriptions WHERE id=?"
        checkresult = self.__ExecuteStatement(checkquery, (id,))
        if checkresult.fetchall() == []:
            raise stov_exceptions.SubscriptionNotFoundException()
        else:
            deletevideos = "DELETE FROM videos WHERE subscription_id=?"
            self.__ExecuteStatement(deletevideos, (id,))
            deletesubscription = "DELETE FROM subscriptions WHERE id=?"
            self.__ExecuteStatement(deletesubscription, (id,))
            message = "Subscription deleted successfully!"
        return message

    def GetVideos(self, id):
        """Gets all videos of a given subscription id from the database and returns them"""
        video_query = "SELECT id, title, description, ytid, downloaded, failcnt FROM videos WHERE subscription_id=?"
        cursor = self.__ExecuteStatement(video_query, (id,))
        result = cursor.fetchall()
        return result

    def VideoInDatabase(self, ytid):
        """Checks if the video with a given youtube id already exists in the database"""
        video_query = "SELECT id FROM videos WHERE ytid=?"
        cursor = self.__ExecuteStatement(video_query, (ytid,))
        result = cursor.fetchall()
        if result == []:
            return False
        else:
            return True

    def InsertVideo(self, video, subscription_id):
        """Inserts a video with the given data into the database"""
        video_insert = "INSERT INTO videos (title, description, ytid, \
                            subscription_id, downloaded) VALUES \
                            (?, ?, ?, ?, ?)"
        insert_data = (video.title, video.description, video.ytid,
                       subscription_id, 0)
        self.__ExecuteStatement(video_insert, insert_data)
        message = "Video %s successfully inserted into database." % video.title
        return message

    def InsertSubscription(self, data):
        """Inserts a subscription with the given data into the database"""
        subscription_insert = "INSERT INTO subscriptions (title, type, searchstring, \
                directory, name, disabled) VALUES (?, ?, ?, ?, ?, ?)"
        self.__ExecuteStatement(subscription_insert, data)
        subscription_getid = "SELECT id from subscriptions where title=?"
        query_cursor = self.__ExecuteStatement(subscription_getid, (data[0],))
        subscription_id = query_cursor.fetchone()[0]
        message = "Subscription sucessfully inserted into database."
        return (message, subscription_id)

    def UpdateVideoDownloadStatus(self, id, status):
        """Updates the download status of a video in the database"""
        update_statement = "UPDATE videos SET downloaded = ? WHERE id = ?"
        result = self.__ExecuteStatement(update_statement, (status, id))

    def DisableFailedVideo(self, id):
        """Disables a video in the database"""
        update_statement = "UPDATE videos SET downloaded = -1 WHERE id = ?"
        self.__ExecuteStatement(update_statement, (id,))

    def UpdateVideoFailCount(self,count, id):
        """Updates the fail count of a video in the database"""
        update_statement = "UPDATE videos SET failcnt = ? WHERE id = ?"
        self.__ExecuteStatement(update_statement, (count, id))

    def DeleteVideo(self,id):
        """Deletes a video from the database"""
        delete_statement = "DELETE FROM videos where id = ?"
        self.__ExecuteStatement(delete_statement, (id,))

    def GetSubscription(self, id):
        """Retrieves a specific subscription from the database"""
        sub_query = "SELECT id,title,type,name,searchstring, directory," \
                    "disabled FROM subscriptions where id=?"
        result_cursor = self.__ExecuteStatement(sub_query, (id,))
        result = result_cursor.fetchall()
        return result

    def GetSubscriptionTitle(self, id):
        """Retrieves only the title of a specified subscription from the
        database

        """
        title_query = "SELECT title from subscriptions where id=?"
        result_cursor = self.__ExecuteStatement(title_query, (id,))
        result = result_cursor.fetchall()
        return result

    def MarkVideosDownloaded(self, id):
        """ Marks all videos in a specified subscription as downloaded"""
        update_statement = "UPDATE videos SET downloaded = 1 WHERE subscription_id =?"
        self.__ExecuteStatement(update_statement, (id,))

    def GetSubscriptions(self):
        """Retrieves all subscriptions from the database"""
        subscriptions_query = "SELECT id,title,type,name,searchstring," \
                              "directory,disabled FROM subscriptions"
        result_cursor = self.__ExecuteStatement(subscriptions_query)
        result = result_cursor.fetchall()
        return result

    def Vacuum(self):
        """Vacuums the database, shrinking it in size"""
        self.__ExecuteStatement("VACUUM")

    def ChangeSubscriptionState(self, id, state):
        """Enables or disables a subscription depending on the parameter
        state

        """
        update_statement = "UPDATE subscriptions SET disabled=? WHERE id=?"
        self.__ExecuteStatement(update_statement, (state,id))