1
0

database.py 9.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243
  1. # This file is part of stov, written by Helmut Pozimski in 2014.
  2. #
  3. # stov is free software: you can redistribute it and/or modify
  4. # it under the terms of the GNU General Public License as published by
  5. # the Free Software Foundation, version 2 of the License.
  6. #
  7. # stov is distributed in the hope that it will be useful,
  8. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  9. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  10. # GNU General Public License for more details.
  11. #
  12. # You should have received a copy of the GNU General Public License
  13. # along with stov. If not, see <http://www.gnu.org/licenses/>.
  14. # -*- coding: utf8 -*-
  15. from __future__ import unicode_literals
  16. import sqlite3
  17. from lib_stov import stov_exceptions
  18. from lib_stov import youtube
  19. from lib_stov import subscription
  20. class db(object):
  21. def __init__(self, path, version):
  22. """Constructor of the db class, populates the object with the relevant
  23. attributes, connects to the database and creates it if asked to.
  24. """
  25. self.__path = path
  26. self.__version = version
  27. try:
  28. self.__connection = sqlite3.connect(self.__path)
  29. except sqlite3.OperationalError:
  30. raise stov_exceptions.DBConnectionFailedException()
  31. else:
  32. self.__cursor = self.__connection.cursor()
  33. def __del__(self):
  34. """Destructor, closes the connection to the database."""
  35. self.__connection.close()
  36. def __ExecuteStatement(self, statement, argument=None):
  37. """Executes a statement, works as a wrapper around cursor execute."""
  38. try:
  39. if argument is None:
  40. result = self.__cursor.execute(statement)
  41. else:
  42. result = self.__cursor.execute(statement, argument)
  43. except sqlite3.OperationalError:
  44. raise stov_exceptions.DBWriteAccessFailedException()
  45. else:
  46. self.__connection.commit()
  47. return result
  48. def Populate(self):
  49. """Populates the database with the initial structure."""
  50. self.__ExecuteStatement("""CREATE TABLE subscriptions (
  51. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  52. title TEXT,
  53. name TEXT,
  54. type TEXT,
  55. searchstring TEXT,
  56. directory TEXT,
  57. disabled INTEGER DEFAULT 0
  58. );""")
  59. self.__ExecuteStatement("""CREATE TABLE videos (
  60. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  61. title TEXT,
  62. description TEXT,
  63. ytid TEXT,
  64. subscription_id INTEGER,
  65. downloaded INT,
  66. failcnt INTEGER DEFAULT 0
  67. );""")
  68. def Update(self):
  69. """Updates the database structure to match the current version"""
  70. if int(self.__version) == 1:
  71. """Changes between version 1 and 2"""
  72. self.__ExecuteStatement("ALTER TABLE videos ADD COLUMN failcnt \
  73. INTEGER DEFAULT 0;")
  74. self.__version = 2
  75. if int(self.__version) == 2:
  76. """Changes between version 2 and 3"""
  77. self.__ExecuteStatement("ALTER TABLE subscriptions ADD COLUMN"
  78. " disabled INTEGER DEFAULT 0;")
  79. self.__ExecuteStatement("UPDATE subscriptions SET disabled=0;")
  80. self.__version = 3
  81. def GetVersion(self):
  82. """Simple getter method, returns the DB version"""
  83. return self.__version
  84. def DeleteSubscription(self, sub_id):
  85. """Deletes a subscription and all associated videos from the
  86. database
  87. """
  88. checkquery = "SELECT * FROM subscriptions WHERE id=?"
  89. checkresult = self.__ExecuteStatement(checkquery, (sub_id,))
  90. if not checkresult.fetchall():
  91. raise stov_exceptions.SubscriptionNotFoundException()
  92. else:
  93. deletevideos = "DELETE FROM videos WHERE subscription_id=?"
  94. self.__ExecuteStatement(deletevideos, (sub_id,))
  95. deletesubscription = "DELETE FROM subscriptions WHERE id=?"
  96. self.__ExecuteStatement(deletesubscription, (sub_id,))
  97. def GetVideos(self, subscription_id, conf):
  98. """Gets all videos of a given subscription id from the database and
  99. returns them
  100. """
  101. videos_list = []
  102. video_query = "SELECT id, title, description, ytid, downloaded, " \
  103. "failcnt FROM videos WHERE subscription_id=?"
  104. cursor = self.__ExecuteStatement(video_query, (subscription_id,))
  105. result = cursor.fetchall()
  106. for video in result:
  107. videos_list.append(youtube.video(id=video[0], title=video[1],
  108. description=video[2],
  109. ytid=video[3],
  110. downloaded=video[4],
  111. failcount=video[5],
  112. conf=conf))
  113. return videos_list
  114. def VideoInDatabase(self, ytid):
  115. """Checks if the video with a given youtube id already exists in the
  116. database
  117. """
  118. video_query = "SELECT id FROM videos WHERE ytid=?"
  119. cursor = self.__ExecuteStatement(video_query, (ytid,))
  120. result = cursor.fetchall()
  121. if not result:
  122. return False
  123. else:
  124. return True
  125. def InsertVideo(self, video, subscription_id):
  126. """Inserts a video with the given data into the database"""
  127. video_insert = "INSERT INTO videos (title, description, ytid, \
  128. subscription_id, downloaded) VALUES \
  129. (?, ?, ?, ?, ?)"
  130. insert_data = (video.title, video.description, video.ytid,
  131. subscription_id, 0)
  132. self.__ExecuteStatement(video_insert, insert_data)
  133. def InsertSubscription(self, data):
  134. """Inserts a subscription with the given data into the database"""
  135. subscription_insert = "INSERT INTO subscriptions (title, type, " \
  136. "searchstring, directory, name, disabled) " \
  137. "VALUES (?, ?, ?, ?, ?, ?)"
  138. self.__ExecuteStatement(subscription_insert, data)
  139. subscription_getid = "SELECT id from subscriptions where title=?"
  140. query_cursor = self.__ExecuteStatement(subscription_getid, (data[0],))
  141. subscription_id = query_cursor.fetchone()[0]
  142. return subscription_id
  143. def UpdateVideoDownloadStatus(self, video_id, status):
  144. """Updates the download status of a video in the database"""
  145. update_statement = "UPDATE videos SET downloaded = ? WHERE id = ?"
  146. self.__ExecuteStatement(update_statement, (status, video_id))
  147. def DisableFailedVideo(self, video_id):
  148. """Disables a video in the database"""
  149. update_statement = "UPDATE videos SET downloaded = -1 WHERE id = ?"
  150. self.__ExecuteStatement(update_statement, (video_id,))
  151. def UpdateVideoFailCount(self, count, video_id):
  152. """Updates the fail count of a video in the database"""
  153. update_statement = "UPDATE videos SET failcnt = ? WHERE id = ?"
  154. self.__ExecuteStatement(update_statement, (count, video_id))
  155. def DeleteVideo(self, video_id):
  156. """Deletes a video from the database"""
  157. delete_statement = "DELETE FROM videos where id = ?"
  158. self.__ExecuteStatement(delete_statement, (video_id,))
  159. def GetSubscription(self, sub_id):
  160. """Retrieves a specific subscription from the database"""
  161. sub_query = "SELECT id,title,type,name,searchstring, directory," \
  162. "disabled FROM subscriptions where id=?"
  163. result_cursor = self.__ExecuteStatement(sub_query, (sub_id,))
  164. result = result_cursor.fetchall()
  165. return result
  166. def GetSubscriptionTitle(self, sub_id):
  167. """Retrieves only the title of a specified subscription from the
  168. database
  169. """
  170. title_query = "SELECT title from subscriptions where id=?"
  171. result_cursor = self.__ExecuteStatement(title_query, (sub_id,))
  172. result = result_cursor.fetchall()
  173. return result
  174. def MarkVideosDownloaded(self, video_id):
  175. """ Marks all videos in a specified subscription as downloaded"""
  176. update_statement = "UPDATE videos SET downloaded = 1 " \
  177. "WHERE subscription_id =?"
  178. self.__ExecuteStatement(update_statement, (video_id,))
  179. def GetSubscriptions(self, conf):
  180. """Retrieves all subscriptions from the database"""
  181. subscriptions_list = []
  182. subscriptions_query = "SELECT id,title,type,name,searchstring," \
  183. "directory,disabled FROM subscriptions"
  184. result_cursor = self.__ExecuteStatement(subscriptions_query)
  185. result = result_cursor.fetchall()
  186. for sub in result:
  187. subscriptions_list.append(subscription.sub(id=sub[0], title=sub[1],
  188. type=sub[2],
  189. name=sub[3],
  190. search=sub[4],
  191. directory=sub[5],
  192. disabled=sub[6],
  193. conf=conf))
  194. return subscriptions_list
  195. def Vacuum(self):
  196. """Vacuums the database, shrinking it in size"""
  197. self.__ExecuteStatement("VACUUM")
  198. def ChangeSubscriptionState(self, sub_id, state):
  199. """Enables or disables a subscription depending on the parameter
  200. state
  201. """
  202. update_statement = "UPDATE subscriptions SET disabled=? WHERE id=?"
  203. self.__ExecuteStatement(update_statement, (state, sub_id))