# This file is part of stov, written by Helmut Pozimski 2012-2015. # # 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 . # -*- coding: utf8 -*- from __future__ import unicode_literals import sqlite3 from lib_stov import stov_exceptions from lib_stov import youtube from lib_stov import subscription 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 );""") 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, sub_id): """Deletes a subscription and all associated videos from the database """ checkquery = "SELECT * FROM subscriptions WHERE id=?" checkresult = self.__ExecuteStatement(checkquery, (sub_id,)) if not checkresult.fetchall(): raise stov_exceptions.SubscriptionNotFoundException() else: deletevideos = "DELETE FROM videos WHERE subscription_id=?" self.__ExecuteStatement(deletevideos, (sub_id,)) deletesubscription = "DELETE FROM subscriptions WHERE id=?" self.__ExecuteStatement(deletesubscription, (sub_id,)) def GetVideos(self, subscription_id, conf): """Gets all videos of a given subscription id from the database and returns them """ videos_list = [] video_query = "SELECT id, title, description, ytid, downloaded, " \ "failcnt FROM videos WHERE subscription_id=?" cursor = self.__ExecuteStatement(video_query, (subscription_id,)) result = cursor.fetchall() for video in result: videos_list.append(youtube.video(id=video[0], title=video[1], description=video[2], ytid=video[3], downloaded=video[4], failcount=video[5], conf=conf)) return videos_list 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 not 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) 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] return subscription_id def UpdateVideoDownloadStatus(self, video_id, status): """Updates the download status of a video in the database""" update_statement = "UPDATE videos SET downloaded = ? WHERE id = ?" self.__ExecuteStatement(update_statement, (status, video_id)) def DisableFailedVideo(self, video_id): """Disables a video in the database""" update_statement = "UPDATE videos SET downloaded = -1 WHERE id = ?" self.__ExecuteStatement(update_statement, (video_id,)) def UpdateVideoFailCount(self, count, video_id): """Updates the fail count of a video in the database""" update_statement = "UPDATE videos SET failcnt = ? WHERE id = ?" self.__ExecuteStatement(update_statement, (count, video_id)) def DeleteVideo(self, video_id): """Deletes a video from the database""" delete_statement = "DELETE FROM videos where id = ?" self.__ExecuteStatement(delete_statement, (video_id,)) def GetSubscription(self, sub_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, (sub_id,)) result = result_cursor.fetchall() return result def GetSubscriptionTitle(self, sub_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, (sub_id,)) result = result_cursor.fetchall() return result def MarkVideosDownloaded(self, video_id): """ Marks all videos in a specified subscription as downloaded""" update_statement = "UPDATE videos SET downloaded = 1 " \ "WHERE subscription_id =?" self.__ExecuteStatement(update_statement, (video_id,)) def GetSubscriptions(self, conf): """Retrieves all subscriptions from the database""" subscriptions_list = [] subscriptions_query = "SELECT id,title,type,name,searchstring," \ "directory,disabled FROM subscriptions" result_cursor = self.__ExecuteStatement(subscriptions_query) result = result_cursor.fetchall() for sub in result: subscriptions_list.append(subscription.sub(id=sub[0], title=sub[1], type=sub[2], name=sub[3], search=sub[4], directory=sub[5], disabled=sub[6], conf=conf)) return subscriptions_list def Vacuum(self): """Vacuums the database, shrinking it in size""" self.__ExecuteStatement("VACUUM") def ChangeSubscriptionState(self, sub_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, sub_id))