1
0

database.py 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339
  1. # This file is part of stov, written by Helmut Pozimski 2012-2017.
  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. import logging
  18. from lib_stov import stov_exceptions
  19. from lib_stov import generic_video
  20. from lib_stov import subscription
  21. LOGGER = logging.getLogger("stov")
  22. class Db(object):
  23. """This class is used to cosntruct the module which will take care of all
  24. database related operations like opening the database, reading from and
  25. writing to it.
  26. """
  27. def __init__(self, path, version):
  28. """Constructor of the db class, populates the object with the relevant
  29. attributes, connects to the database and creates it if asked to.
  30. """
  31. self.__path = path
  32. self.__version = version
  33. try:
  34. self.__connection = sqlite3.connect(self.__path)
  35. except sqlite3.OperationalError:
  36. raise stov_exceptions.DBConnectionFailedException()
  37. else:
  38. self.__cursor = self.__connection.cursor()
  39. def __del__(self):
  40. """Destructor, closes the connection to the database."""
  41. self.__connection.close()
  42. def _execute_statement(self, statement, argument=None):
  43. """Executes a statement, works as a wrapper around cursor execute."""
  44. try:
  45. if not argument:
  46. LOGGER.debug(_("Executing STATEMENT: %s"), statement)
  47. result = self.__cursor.execute(statement)
  48. else:
  49. LOGGER.debug(_("Executing STATEMENT: %s with arguments %s"),
  50. statement, argument)
  51. result = self.__cursor.execute(statement, argument)
  52. except sqlite3.OperationalError as error:
  53. LOGGER.debug(error)
  54. raise stov_exceptions.DBWriteAccessFailedException()
  55. else:
  56. self.__connection.commit()
  57. return result
  58. def populate(self):
  59. """Populates the database with the initial structure."""
  60. self._execute_statement("""CREATE TABLE subscriptions (
  61. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  62. title TEXT,
  63. name TEXT,
  64. type TEXT,
  65. searchstring TEXT,
  66. directory TEXT,
  67. disabled INTEGER DEFAULT 0,
  68. site INTEGER NOT NULL
  69. );""")
  70. self._execute_statement("""CREATE TABLE videos (
  71. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  72. title TEXT,
  73. ytid TEXT,
  74. subscription_id INTEGER,
  75. downloaded INT,
  76. failcnt INTEGER DEFAULT 0
  77. );""")
  78. self._execute_statement("""CREATE TABLE sites(
  79. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  80. title TEXT);""")
  81. def update(self):
  82. """Updates the database structure to match the current version"""
  83. if int(self.__version) == 1:
  84. # Changes between version 1 and 2
  85. self._execute_statement("ALTER TABLE videos ADD COLUMN failcnt \
  86. INTEGER DEFAULT 0;")
  87. self.__version = 2
  88. if int(self.__version) == 2:
  89. # Changes between version 2 and 3
  90. self._execute_statement("ALTER TABLE subscriptions ADD COLUMN"
  91. " disabled INTEGER DEFAULT 0;")
  92. self._execute_statement("UPDATE subscriptions SET disabled=0;")
  93. self.__version = 3
  94. if int(self.__version) == 3:
  95. # Pseudo version without changes to the database structure,
  96. # converts existing channel subscriptions into user ones.
  97. self._execute_statement("UPDATE subscriptions SET type='user' "
  98. "WHERE type='channel'")
  99. self.__version = 4
  100. if int(self.__version) == 4:
  101. # Changes between version 4 and 5
  102. self._execute_statement("""CREATE TABLE videos_backup (
  103. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  104. title TEXT,
  105. ytid TEXT,
  106. subscription_id INTEGER,
  107. downloaded INT,
  108. failcnt INTEGER DEFAULT 0
  109. );""")
  110. self._execute_statement("INSERT INTO videos_backup SELECT"
  111. " id, title, ytid, subscription_id, "
  112. "downloaded, failcnt FROM videos;")
  113. self._execute_statement("DROP TABLE videos;")
  114. self._execute_statement("ALTER TABLE videos_backup RENAME TO "
  115. "videos;")
  116. self.__version = 5
  117. if int(self.__version) == 5:
  118. self._execute_statement("""CREATE TABLE sites(
  119. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  120. title TEXT);""")
  121. self._execute_statement("""ALTER TABLE subscriptions ADD
  122. COLUMN site INTEGER;""")
  123. self.add_site("youtube")
  124. self._execute_statement("""UPDATE subscriptions SET site=1;""")
  125. self._execute_statement("""CREATE TABLE subscriptions_backup (
  126. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  127. title TEXT,
  128. name TEXT,
  129. type TEXT,
  130. searchstring TEXT,
  131. directory TEXT,
  132. disabled INTEGER DEFAULT 0,
  133. site INTEGER NOT NULL
  134. );""")
  135. self._execute_statement("INSERT INTO subscriptions_backup SELECT"
  136. " id, title, name, type, "
  137. "searchstring, directory, disabled, "
  138. "site FROM subscriptions;")
  139. self._execute_statement("DROP TABLE subscriptions;")
  140. self._execute_statement("ALTER TABLE subscriptions_backup "
  141. "RENAME TO subscriptions;")
  142. self.__version = 6
  143. def get_version(self):
  144. """Simple getter method, returns the DB version"""
  145. return self.__version
  146. def delete_subscription(self, sub_id):
  147. """Deletes a subscription and all associated videos from the
  148. database
  149. """
  150. checkquery = "SELECT * FROM subscriptions WHERE id=?"
  151. checkresult = self._execute_statement(checkquery, (sub_id,))
  152. if not checkresult.fetchall():
  153. raise stov_exceptions.SubscriptionNotFoundException()
  154. else:
  155. deletevideos = "DELETE FROM videos WHERE subscription_id=?"
  156. self._execute_statement(deletevideos, (sub_id,))
  157. deletesubscription = "DELETE FROM subscriptions WHERE id=?"
  158. self._execute_statement(deletesubscription, (sub_id,))
  159. def get_videos(self, subscription_id, conf):
  160. """Gets all videos of a given subscription id from the database and
  161. returns them
  162. """
  163. videos_list = []
  164. video_query = "SELECT id, title, ytid, downloaded, " \
  165. "failcnt FROM videos WHERE subscription_id=?"
  166. cursor = self._execute_statement(video_query, (subscription_id,))
  167. result = cursor.fetchall()
  168. for video in result:
  169. videos_list.append(generic_video.Video(
  170. video_id=video[0], title=video[1], site_id=video[2],
  171. downloaded=video[3], failcount=video[4], conf=conf))
  172. return videos_list
  173. def video_in_database(self, ytid):
  174. """Checks if the video with a given youtube id already exists in the
  175. database
  176. """
  177. video_query = "SELECT id FROM videos WHERE ytid=?"
  178. cursor = self._execute_statement(video_query, (ytid,))
  179. result = cursor.fetchall()
  180. if not result:
  181. return False
  182. else:
  183. return True
  184. def insert_video(self, video, subscription_id):
  185. """Inserts a video with the given data into the database"""
  186. video_insert = "INSERT INTO videos (title, ytid, \
  187. subscription_id, downloaded) VALUES \
  188. (?, ?, ?, ?)"
  189. insert_data = (video.title, video.video_id,
  190. subscription_id, 0)
  191. self._execute_statement(video_insert, insert_data)
  192. def insert_subscription(self, data):
  193. """Inserts a subscription with the given data into the database"""
  194. subscription_insert = """INSERT INTO subscriptions (title, type,
  195. searchstring, directory, name, disabled, site)
  196. VALUES (?, ?, ?, ?, ?, ?, ?)"""
  197. self._execute_statement(subscription_insert, data)
  198. subscription_getid = "SELECT id from subscriptions where title=?"
  199. query_cursor = self._execute_statement(subscription_getid, (data[0],))
  200. subscription_id = query_cursor.fetchone()[0]
  201. return subscription_id
  202. def update_video_download_status(self, video_id, status):
  203. """Updates the download status of a video in the database"""
  204. update_statement = "UPDATE videos SET downloaded = ? WHERE id = ?"
  205. self._execute_statement(update_statement, (status, video_id))
  206. def disable_failed_video(self, video_id):
  207. """Disables a video in the database"""
  208. update_statement = "UPDATE videos SET downloaded = -1 WHERE id = ?"
  209. self._execute_statement(update_statement, (video_id,))
  210. def update_video_fail_count(self, count, video_id):
  211. """Updates the fail count of a video in the database"""
  212. update_statement = "UPDATE videos SET failcnt = ? WHERE id = ?"
  213. self._execute_statement(update_statement, (count, video_id))
  214. def delete_video(self, video_id):
  215. """Deletes a video from the database"""
  216. delete_statement = "DELETE FROM videos where id = ?"
  217. self._execute_statement(delete_statement, (video_id,))
  218. def get_subscription(self, sub_id):
  219. """Retrieves a specific subscription from the database"""
  220. sub_query = """SELECT subscriptions.id, subscriptions.title,
  221. type, name,searchstring, directory,disabled, sites.title FROM
  222. subscriptions INNER JOIN sites ON subscriptions.site=sites.id WHERE
  223. subscriptions.id=?"""
  224. result_cursor = self._execute_statement(sub_query, (sub_id,))
  225. result = result_cursor.fetchall()
  226. return result
  227. def get_subscription_title(self, sub_id):
  228. """Retrieves only the title of a specified subscription from the
  229. database
  230. """
  231. title_query = "SELECT title from subscriptions where id=?"
  232. result_cursor = self._execute_statement(title_query, (sub_id,))
  233. result = result_cursor.fetchall()
  234. return result
  235. def mark_video_downloaded(self, video_id):
  236. """ Marks all videos in a specified subscription as downloaded"""
  237. update_statement = "UPDATE videos SET downloaded = 1 " \
  238. "WHERE subscription_id =?"
  239. self._execute_statement(update_statement, (video_id,))
  240. def get_subscriptions(self, conf):
  241. """Retrieves all subscriptions from the database"""
  242. subscriptions_list = []
  243. subscriptions_query = """SELECT subscriptions.id, subscriptions.title,
  244. type, name,searchstring, directory,disabled, sites.title FROM
  245. subscriptions INNER JOIN sites ON subscriptions.site=sites.id;"""
  246. result_cursor = self._execute_statement(subscriptions_query)
  247. result = result_cursor.fetchall()
  248. for sub in result:
  249. subscriptions_list.append(subscription.Sub(
  250. subscription_id=sub[0], title=sub[1], subscription_type=sub[2],
  251. name=sub[3], search=sub[4], directory=sub[5], disabled=sub[6],
  252. site=sub[7], conf=conf))
  253. return subscriptions_list
  254. def vacuum(self):
  255. """Vacuums the database, shrinking it in size"""
  256. self._execute_statement("VACUUM")
  257. def change_subscription_state(self, sub_id, state):
  258. """Enables or disables a subscription depending on the parameter
  259. state
  260. """
  261. update_statement = "UPDATE subscriptions SET disabled=? WHERE id=?"
  262. self._execute_statement(update_statement, (state, sub_id))
  263. def add_site(self, name):
  264. """
  265. Adds a site with the specified name to the database.
  266. :param name: name of the new site
  267. :type site: str
  268. """
  269. insert_statement = "INSERT INTO sites (title) VALUES (?)"
  270. self._execute_statement(insert_statement, (name,))
  271. def get_site_id(self, name):
  272. """
  273. Get the ID of a specific site
  274. :param name: name of the new site
  275. :type site: str
  276. :return: the site ID
  277. :rtype: int
  278. """
  279. query = "SELECT id FROM sites WHERE title=?"
  280. cursor = self._execute_statement(query, (name,))
  281. result = cursor.fetchone()[0]
  282. return result
  283. def get_sites(self):
  284. """
  285. Retrieves all sites from the database.
  286. :return: list of sites with their respective IDs
  287. :rtype: tuple
  288. """
  289. query = "SELECT id,title FROM sites"
  290. cursor = self._execute_statement(query)
  291. result = cursor.fetchall()
  292. return result