From 60fe4b2e75e8475b20001c84e01b642a3b3ebcc3 Mon Sep 17 00:00:00 2001 From: James Taylor Date: Fri, 7 Jun 2019 21:15:16 -0700 Subject: Add subscription manager system --- youtube/subscriptions.py | 284 +++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 275 insertions(+), 9 deletions(-) (limited to 'youtube/subscriptions.py') diff --git a/youtube/subscriptions.py b/youtube/subscriptions.py index 93d064d..4591f32 100644 --- a/youtube/subscriptions.py +++ b/youtube/subscriptions.py @@ -5,10 +5,17 @@ import sqlite3 import os import time import gevent +import html +import json +import traceback with open('yt_subscriptions_template.html', 'r', encoding='utf-8') as f: subscriptions_template = Template(f.read()) +with open('yt_subscription_manager_template.html', 'r', encoding='utf-8') as f: + subscription_manager_template = Template(f.read()) + + thumbnails_directory = os.path.join(settings.data_dir, "subscription_thumbnails") # https://stackabuse.com/a-sqlite-tutorial-with-python/ @@ -25,19 +32,26 @@ def open_database(): cursor = connection.cursor() cursor.execute('''CREATE TABLE IF NOT EXISTS subscribed_channels ( id integer PRIMARY KEY, - channel_id text UNIQUE NOT NULL, + yt_channel_id text UNIQUE NOT NULL, channel_name text NOT NULL, time_last_checked integer )''') cursor.execute('''CREATE TABLE IF NOT EXISTS videos ( id integer PRIMARY KEY, - uploader_id integer NOT NULL REFERENCES subscribed_channels(id) ON UPDATE CASCADE ON DELETE CASCADE, + sql_channel_id integer NOT NULL REFERENCES subscribed_channels(id) ON UPDATE CASCADE ON DELETE CASCADE, video_id text UNIQUE NOT NULL, title text NOT NULL, duration text, time_published integer NOT NULL, description text )''') + cursor.execute('''CREATE TABLE IF NOT EXISTS tag_associations ( + id integer PRIMARY KEY, + tag text NOT NULL, + sql_channel_id integer NOT NULL REFERENCES subscribed_channels(id) ON UPDATE CASCADE ON DELETE CASCADE, + UNIQUE(tag, sql_channel_id) + )''') + connection.commit() except: connection.rollback() @@ -55,7 +69,7 @@ def _subscribe(channels): connection = open_database() try: cursor = connection.cursor() - cursor.executemany("INSERT OR IGNORE INTO subscribed_channels (channel_id, channel_name, time_last_checked) VALUES (?, ?, ?)", channels) + cursor.executemany("INSERT OR IGNORE INTO subscribed_channels (yt_channel_id, channel_name, time_last_checked) VALUES (?, ?, ?)", channels) connection.commit() except: connection.rollback() @@ -63,12 +77,13 @@ def _subscribe(channels): finally: connection.close() +# TODO: delete thumbnails def _unsubscribe(channel_ids): ''' channel_ids is a list of channel_ids ''' connection = open_database() try: cursor = connection.cursor() - cursor.executemany("DELETE FROM subscribed_channels WHERE channel_id=?", ((channel_id, ) for channel_id in channel_ids)) + cursor.executemany("DELETE FROM subscribed_channels WHERE yt_channel_id=?", ((channel_id, ) for channel_id in channel_ids)) connection.commit() except: connection.rollback() @@ -82,7 +97,7 @@ def _get_videos(number, offset): cursor = connection.cursor() db_videos = cursor.execute('''SELECT video_id, title, duration, channel_name FROM videos - INNER JOIN subscribed_channels on videos.uploader_id = subscribed_channels.id + INNER JOIN subscribed_channels on videos.sql_channel_id = subscribed_channels.id ORDER BY time_published DESC LIMIT ? OFFSET ?''', (number, offset)) @@ -99,10 +114,98 @@ def _get_videos(number, offset): finally: connection.close() +def _get_subscribed_channels(): + connection = open_database() + try: + cursor = connection.cursor() + for item in cursor.execute('''SELECT channel_name, yt_channel_id + FROM subscribed_channels + ORDER BY channel_name'''): + yield item + except: + connection.rollback() + raise + finally: + connection.close() + +def _add_tags(channel_ids, tags): + connection = open_database() + try: + cursor = connection.cursor() + + pairs = [(tag, yt_channel_id) for tag in tags for yt_channel_id in channel_ids] + + cursor.executemany('''INSERT OR IGNORE INTO tag_associations (tag, sql_channel_id) + SELECT ?, id FROM subscribed_channels WHERE yt_channel_id = ? ''', pairs) + connection.commit() + except: + connection.rollback() + raise + finally: + connection.close() + + + +def _remove_tags(channel_ids, tags): + connection = open_database() + try: + cursor = connection.cursor() + + pairs = [(tag, yt_channel_id) for tag in tags for yt_channel_id in channel_ids] + cursor.executemany('''DELETE FROM tag_associations WHERE tag = ? AND sql_channel_id = ( + SELECT id FROM subscribed_channels WHERE yt_channel_id = ? + )''', pairs) + connection.commit() + except: + connection.rollback() + raise + finally: + connection.close() +def _get_tags(channel_id): + connection = open_database() + try: + cursor = connection.cursor() + return [row[0] for row in cursor.execute('''SELECT tag + FROM tag_associations + WHERE sql_channel_id = ( + SELECT id FROM subscribed_channels WHERE yt_channel_id = ? + )''', (channel_id,))] + except: + connection.rollback() + raise + finally: + connection.close() + +def _get_all_tags(): + connection = open_database() + try: + cursor = connection.cursor() + return [row[0] for row in cursor.execute('''SELECT DISTINCT tag FROM tag_associations''')] + except: + connection.rollback() + raise + finally: + connection.close() + +def _get_channel_names(channel_ids): + ''' returns list of (channel_id, channel_name) ''' + connection = open_database() + try: + cursor = connection.cursor() + result = [] + for channel_id in channel_ids: + row = cursor.execute('''SELECT channel_name FROM subscribed_channels WHERE yt_channel_id = ?''', (channel_id,)).fetchone() + result.append( (channel_id, row[0]) ) + return result + except: + connection.rollback() + raise + finally: + connection.close() units = { @@ -166,11 +269,156 @@ def _get_upstream_videos(channel_id): +def import_subscriptions(env, start_response): + content_type = env['parameters']['subscriptions_file'][0] + file = env['parameters']['subscriptions_file'][1] + + file = file.decode('utf-8') + + if content_type == 'application/json': + try: + file = json.loads(file) + except json.decoder.JSONDecodeError: + traceback.print_exc() + start_response('400 Bad Request', () ) + return b'400 Bad Request: Invalid json file' + + try: + channels = ( (item['snippet']['resourceId']['channelId'], item['snippet']['title']) for item in file) + except (KeyError, IndexError): + traceback.print_exc() + start_response('400 Bad Request', () ) + return b'400 Bad Request: Unknown json structure' + else: + raise NotImplementedError() + + _subscribe(channels) + + start_response('303 See Other', [('Location', util.URL_ORIGIN + '/subscription_manager'),] ) + return b'' + + + +sub_list_item_template = Template(''' +
  • + $channel_name + $tags + +
    • ''') + +def get_subscription_manager_page(env, start_response): + + sub_list_html = '' + for channel_name, channel_id in _get_subscribed_channels(): + sub_list_html += sub_list_item_template.substitute( + channel_url = util.URL_ORIGIN + '/channel/' + channel_id, + channel_name = html.escape(channel_name), + channel_id = channel_id, + tags = ', '.join(_get_tags(channel_id)), + ) + + + + start_response('200 OK', [('Content-type','text/html'),]) + return subscription_manager_template.substitute( + header = html_common.get_header(), + sub_list = sub_list_html, + page_buttons = '', + ).encode('utf-8') + +def list_from_comma_separated_tags(string): + tags = [] + prev_comma = -1 + next_comma = string.find(',') + while next_comma != -1: + tag = string[prev_comma+1:next_comma].strip() + if tag: + tags.append(tag) + + prev_comma = next_comma + next_comma = string.find(',', prev_comma+1) + + last_tag = string[prev_comma+1:].strip() + if last_tag: + tags.append(last_tag) + return tags + + +unsubscribe_list_item_template = Template(''' +
  • $channel_name
    • ''') +def post_subscription_manager_page(env, start_response): + params = env['parameters'] + action = params['action'][0] + + if action == 'add_tags': + _add_tags(params['channel_ids'], [tag.lower() for tag in list_from_comma_separated_tags(params['tags'][0])]) + elif action == 'remove_tags': + _remove_tags(params['channel_ids'], [tag.lower() for tag in list_from_comma_separated_tags(params['tags'][0])]) + elif action == 'unsubscribe': + _unsubscribe(params['channel_ids']) + elif action == 'unsubscribe_verify': + page = ''' + Are you sure you want to unsubscribe from these channels? +
    ''' + + for channel_id in params['channel_ids']: + page += '\n' + + page += ''' + + +
    + ''' + + start_response('200 OK', [('Content-type','text/html'),]) + return html_common.yt_basic_template.substitute( + page_title = 'Unsubscribe?', + style = '', + header = html_common.get_header(), + page = page, + ).encode('utf-8') + else: + start_response('400 Bad Request', ()) + return b'400 Bad Request' + + start_response('303 See Other', [('Location', util.URL_ORIGIN + '/subscription_manager'),] ) + return b'' + + + +sidebar_tag_item_template = Template(''' +
  • + $tag_name +
    + + + + +
    +
    • ''') + + +sidebar_channel_item_template = Template(''' +
  • + $channel_name +
    + + + + +
    +
    • ''') + def get_subscriptions_page(env, start_response): items_html = '''''' + + tag_list_html = '' + for tag_name in _get_all_tags(): + tag_list_html += sidebar_tag_item_template.substitute(tag_name = tag_name) + + + sub_list_html = '' + for channel_name, channel_id in _get_subscribed_channels(): + sub_list_html += sidebar_channel_item_template.substitute( + channel_url = util.URL_ORIGIN + '/channel/' + channel_id, + channel_name = html.escape(channel_name), + channel_id = channel_id, + ) + + + start_response('200 OK', [('Content-type','text/html'),]) return subscriptions_template.substitute( header = html_common.get_header(), items = items_html, + tags = tag_list_html, + sub_list = sub_list_html, page_buttons = '', ).encode('utf-8') @@ -201,9 +467,9 @@ def post_subscriptions_page(env, start_response): connection = open_database() try: cursor = connection.cursor() - for uploader_id, channel_id in cursor.execute('''SELECT id, channel_id FROM subscribed_channels''').fetchall(): - db_videos = ( (uploader_id, info['id'], info['title'], info['duration'], info['time_published'], info['description']) for info in _get_upstream_videos(channel_id) ) - cursor.executemany('''INSERT OR IGNORE INTO videos (uploader_id, video_id, title, duration, time_published, description) VALUES (?, ?, ?, ?, ?, ?)''', db_videos) + for sql_channel_id, yt_channel_id in cursor.execute('''SELECT id, yt_channel_id FROM subscribed_channels''').fetchall(): + db_videos = ( (sql_channel_id, info['id'], info['title'], info['duration'], info['time_published'], info['description']) for info in _get_upstream_videos(yt_channel_id) ) + cursor.executemany('''INSERT OR IGNORE INTO videos (sql_channel_id, video_id, title, duration, time_published, description) VALUES (?, ?, ?, ?, ?, ?)''', db_videos) cursor.execute('''UPDATE subscribed_channels SET time_last_checked = ?''', ( int(time.time()), ) ) connection.commit() -- cgit v1.2.3