1
0

database.py 15 KB

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