database.py 9.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244
  1. # This file is part of stov, written by Helmut Pozimski 2012-2015.
  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. """This module is responsible for all database related operations."""
  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. """This class is used to cosntruct the module which will take care of all
  22. database related operations like opening the database, reading from and
  23. writing to it.
  24. """
  25. def __init__(self, path, version):
  26. """Constructor of the db class, populates the object with the relevant
  27. attributes, connects to the database and creates it if asked to.
  28. """
  29. self.__path = path
  30. self.__version = version
  31. try:
  32. self.__connection = sqlite3.connect(self.__path)
  33. except sqlite3.OperationalError:
  34. raise stov_exceptions.DBConnectionFailedException()
  35. else:
  36. self.__cursor = self.__connection.cursor()
  37. def __del__(self):
  38. """Destructor, closes the connection to the database."""
  39. self.__connection.close()
  40. def _execute_statement(self, statement, argument=None):
  41. """Executes a statement, works as a wrapper around cursor execute."""
  42. try:
  43. if argument is None:
  44. result = self.__cursor.execute(statement)
  45. else:
  46. result = self.__cursor.execute(statement, argument)
  47. except sqlite3.OperationalError:
  48. raise stov_exceptions.DBWriteAccessFailedException()
  49. else:
  50. self.__connection.commit()
  51. return result
  52. def populate(self):
  53. """Populates the database with the initial structure."""
  54. self._execute_statement("""CREATE TABLE subscriptions (
  55. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  56. title TEXT,
  57. name TEXT,
  58. type TEXT,
  59. searchstring TEXT,
  60. directory TEXT,
  61. disabled INTEGER DEFAULT 0
  62. );""")
  63. self._execute_statement("""CREATE TABLE videos (
  64. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  65. title TEXT,
  66. description TEXT,
  67. ytid TEXT,
  68. subscription_id INTEGER,
  69. downloaded INT,
  70. failcnt INTEGER DEFAULT 0
  71. );""")
  72. def update(self):
  73. """Updates the database structure to match the current version"""
  74. if int(self.__version) == 1:
  75. # Changes between version 1 and 2
  76. self._execute_statement("ALTER TABLE videos ADD COLUMN failcnt \
  77. INTEGER DEFAULT 0;")
  78. self.__version = 2
  79. if int(self.__version) == 2:
  80. # Changes between version 2 and 3
  81. self._execute_statement("ALTER TABLE subscriptions ADD COLUMN"
  82. " disabled INTEGER DEFAULT 0;")
  83. self._execute_statement("UPDATE subscriptions SET disabled=0;")
  84. self.__version = 3
  85. def get_version(self):
  86. """Simple getter method, returns the DB version"""
  87. return self.__version
  88. def delete_subscription(self, sub_id):
  89. """Deletes a subscription and all associated videos from the
  90. database
  91. """
  92. checkquery = "SELECT * FROM subscriptions WHERE id=?"
  93. checkresult = self._execute_statement(checkquery, (sub_id,))
  94. if not checkresult.fetchall():
  95. raise stov_exceptions.SubscriptionNotFoundException()
  96. else:
  97. deletevideos = "DELETE FROM videos WHERE subscription_id=?"
  98. self._execute_statement(deletevideos, (sub_id,))
  99. deletesubscription = "DELETE FROM subscriptions WHERE id=?"
  100. self._execute_statement(deletesubscription, (sub_id,))
  101. def get_videos(self, subscription_id, conf):
  102. """Gets all videos of a given subscription id from the database and
  103. returns them
  104. """
  105. videos_list = []
  106. video_query = "SELECT id, title, description, ytid, downloaded, " \
  107. "failcnt FROM videos WHERE subscription_id=?"
  108. cursor = self._execute_statement(video_query, (subscription_id,))
  109. result = cursor.fetchall()
  110. for video in result:
  111. videos_list.append(youtube.Video(video_id=video[0], title=video[1],
  112. description=video[2],
  113. ytid=video[3],
  114. downloaded=video[4],
  115. failcount=video[5],
  116. conf=conf))
  117. return videos_list
  118. def video_in_database(self, ytid):
  119. """Checks if the video with a given youtube id already exists in the
  120. database
  121. """
  122. video_query = "SELECT id FROM videos WHERE ytid=?"
  123. cursor = self._execute_statement(video_query, (ytid,))
  124. result = cursor.fetchall()
  125. if not result:
  126. return False
  127. else:
  128. return True
  129. def insert_video(self, video, subscription_id):
  130. """Inserts a video with the given data into the database"""
  131. video_insert = "INSERT INTO videos (title, description, ytid, \
  132. subscription_id, downloaded) VALUES \
  133. (?, ?, ?, ?, ?)"
  134. insert_data = (video.title, video.description, video.ytid,
  135. subscription_id, 0)
  136. self._execute_statement(video_insert, insert_data)
  137. def insert_subscription(self, data):
  138. """Inserts a subscription with the given data into the database"""
  139. subscription_insert = "INSERT INTO subscriptions (title, type, " \
  140. "searchstring, directory, name, disabled) " \
  141. "VALUES (?, ?, ?, ?, ?, ?)"
  142. self._execute_statement(subscription_insert, data)
  143. subscription_getid = "SELECT id from subscriptions where title=?"
  144. query_cursor = self._execute_statement(subscription_getid, (data[0],))
  145. subscription_id = query_cursor.fetchone()[0]
  146. return subscription_id
  147. def update_video_download_status(self, video_id, status):
  148. """Updates the download status of a video in the database"""
  149. update_statement = "UPDATE videos SET downloaded = ? WHERE id = ?"
  150. self._execute_statement(update_statement, (status, video_id))
  151. def disable_failed_video(self, video_id):
  152. """Disables a video in the database"""
  153. update_statement = "UPDATE videos SET downloaded = -1 WHERE id = ?"
  154. self._execute_statement(update_statement, (video_id,))
  155. def update_video_fail_count(self, count, video_id):
  156. """Updates the fail count of a video in the database"""
  157. update_statement = "UPDATE videos SET failcnt = ? WHERE id = ?"
  158. self._execute_statement(update_statement, (count, video_id))
  159. def delete_video(self, video_id):
  160. """Deletes a video from the database"""
  161. delete_statement = "DELETE FROM videos where id = ?"
  162. self._execute_statement(delete_statement, (video_id,))
  163. def get_subscription(self, sub_id):
  164. """Retrieves a specific subscription from the database"""
  165. sub_query = "SELECT id,title,type,name,searchstring, directory," \
  166. "disabled FROM subscriptions where id=?"
  167. result_cursor = self._execute_statement(sub_query, (sub_id,))
  168. result = result_cursor.fetchall()
  169. return result
  170. def get_subscription_title(self, sub_id):
  171. """Retrieves only the title of a specified subscription from the
  172. database
  173. """
  174. title_query = "SELECT title from subscriptions where id=?"
  175. result_cursor = self._execute_statement(title_query, (sub_id,))
  176. result = result_cursor.fetchall()
  177. return result
  178. def mark_video_downloaded(self, video_id):
  179. """ Marks all videos in a specified subscription as downloaded"""
  180. update_statement = "UPDATE videos SET downloaded = 1 " \
  181. "WHERE subscription_id =?"
  182. self._execute_statement(update_statement, (video_id,))
  183. def get_subscriptions(self, conf):
  184. """Retrieves all subscriptions from the database"""
  185. subscriptions_list = []
  186. subscriptions_query = "SELECT id,title,type,name,searchstring," \
  187. "directory,disabled FROM subscriptions"
  188. result_cursor = self._execute_statement(subscriptions_query)
  189. result = result_cursor.fetchall()
  190. for sub in result:
  191. subscriptions_list.append(subscription.Sub(
  192. subscription_id=sub[0], title=sub[1], subscription_type=sub[2],
  193. name=sub[3], search=sub[4], directory=sub[5], disabled=sub[6],
  194. conf=conf))
  195. return subscriptions_list
  196. def vacuum(self):
  197. """Vacuums the database, shrinking it in size"""
  198. self._execute_statement("VACUUM")
  199. def change_subscription_state(self, sub_id, state):
  200. """Enables or disables a subscription depending on the parameter
  201. state
  202. """
  203. update_statement = "UPDATE subscriptions SET disabled=? WHERE id=?"
  204. self._execute_statement(update_statement, (state, sub_id))