Add subscription manager system

5 files changed, 489 insertions, 20 deletions

diff --git a/youtube/shared.css b/youtube/shared.css
index 1b25d7f..cd82164 100644
--- a/youtube/shared.css
+++ b/youtube/shared.css
@@ -1,7 +1,10 @@
+* {
+    box-sizing: border-box;
+}
+
 h1, h2, h3, h4, h5, h6, div, button{
     margin:0;
     padding:0;
-    
 }
 
 
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('''
+<li>
+    <a href="$channel_url" class="sub-list-item-name" title="$channel_name">$channel_name</a>
+    <span class="tag-list">$tags</span>
+    <input class="sub-list-checkbox" name="channel_ids" value="$channel_id" form="subscription-manager-form" type="checkbox">
+</li>''')
+
+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('''
+<li><a href="$channel_url" title="$channel_name">$channel_name</a></li>''')
+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 = '''
+        <span>Are you sure you want to unsubscribe from these channels?</span>
+        <form class="subscriptions-import-form" action="/youtube.com/subscription_manager" method="POST">'''
+
+        for channel_id in params['channel_ids']:
+            page += '<input type="hidden" name="channel_ids" value="' + channel_id + '">\n'
+
+        page += '''
+            <input type="hidden" name="action" value="unsubscribe">
+            <input type="submit" value="Yes, unsubscribe">
+        </form>
+        <ul>'''
+        for channel_id, channel_name in _get_channel_names(params['channel_ids']):
+            page += unsubscribe_list_item_template.substitute(
+                channel_url = util.URL_ORIGIN + '/channel/' + channel_id,
+                channel_name = html.escape(channel_name),
+            )
+        page += '''</ul>'''
+
+        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('''
+<li>
+    <span class="sidebar-item-name">$tag_name</span>
+    <form method="POST" class="sidebar-item-refresh">
+        <input type="submit" value="Check">
+        <input type="hidden" name="action" value="refresh">
+        <input type="hidden" name="type" value="tag">
+        <input type="hidden" name="tag_name" value="$tag_name">
+    </form>
+</li>''')
+
+
+sidebar_channel_item_template = Template('''
+<li>
+    <a href="$channel_url" class="sidebar-item-name" title="$channel_name">$channel_name</a>
+    <form method="POST" class="sidebar-item-refresh">
+        <input type="submit" value="Check">
+        <input type="hidden" name="action" value="refresh">
+        <input type="hidden" name="type" value="channel">
+        <input type="hidden" name="channel_id" value="$channel_id">
+    </form>
+</li>''')
+
 def get_subscriptions_page(env, start_response):
     items_html = '''<nav class="item-grid">\n'''
 
     for item in _get_videos(30, 0):
-        print("Downloading_thumbnails: ", downloading_thumbnails)
         if item['id'] in downloading_thumbnails:
             item['thumbnail'] = util.get_thumbnail_url(item['id'])
         else:
@@ -178,10 +426,28 @@ def get_subscriptions_page(env, start_response):
         items_html += html_common.video_item_html(item, html_common.small_video_item_template)
     items_html += '''\n</nav>'''
 
+
+    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()
diff --git a/youtube/youtube.py b/youtube/youtube.py
index c629bbb..c0be4fe 100644
--- a/youtube/youtube.py
+++ b/youtube/youtube.py
@@ -1,6 +1,7 @@
 import mimetypes
 import urllib.parse
 import os
+import re
 from youtube import local_playlist, watch, search, playlist, channel, comments, post_comment, accounts, util, subscriptions
 import settings
 YOUTUBE_FILES = (
@@ -25,7 +26,8 @@ get_handlers = {
     'delete_comment':   post_comment.get_delete_comment_page,
     'login':            accounts.get_account_login_page,
 
-    'subscriptions':    subscriptions.get_subscriptions_page,
+    'subscriptions':            subscriptions.get_subscriptions_page,
+    'subscription_manager':     subscriptions.get_subscription_manager_page,
 }
 post_handlers = {
     'edit_playlist':    local_playlist.edit_playlist,
@@ -37,6 +39,8 @@ post_handlers = {
     'delete_comment':   post_comment.delete_comment,
 
     'subscriptions':    subscriptions.post_subscriptions_page,
+    'subscription_manager':     subscriptions.post_subscription_manager_page,
+    'import_subscriptions':     subscriptions.import_subscriptions,
 }
 
 def youtube(env, start_response):
@@ -90,9 +94,56 @@ def youtube(env, start_response):
             return channel.get_channel_page_general_url(env, start_response)
 
     elif method == "POST":
-        post_parameters = urllib.parse.parse_qs(env['wsgi.input'].read().decode())
-        env['post_parameters'] = post_parameters
-        env['parameters'].update(post_parameters)
+        content_type = env['CONTENT_TYPE']
+        if content_type == 'application/x-www-form-urlencoded':
+            post_parameters = urllib.parse.parse_qs(env['wsgi.input'].read().decode())
+            env['post_parameters'] = post_parameters
+            env['parameters'].update(post_parameters)
+
+        # Ugly hack that will be removed once I clean up this trainwreck and switch to a microframework
+        # Only supports a single file with no other fields
+        elif content_type.startswith('multipart/form-data'):
+            content = env['wsgi.input'].read()
+
+            # find double line break
+            file_start = content.find(b'\r\n\r\n')
+            if file_start == -1:
+                start_response('400 Bad Request', ())
+                return b'400 Bad Request'
+
+            file_start += 4
+
+            lines = content[0:file_start].splitlines()
+            boundary = lines[0]
+
+            file_end = content.find(boundary, file_start)
+            if file_end == -1:
+                start_response('400 Bad Request', ())
+                return b'400 Bad Request'
+            file_end -= 2  # Subtract newlines
+            file = content[file_start:file_end]
+
+            properties = dict()
+            for line in lines[1:]:
+                line = line.decode('utf-8')
+                colon = line.find(':')
+                if colon == -1:
+                    continue
+                properties[line[0:colon]] = line[colon+2:]
+
+            mime_type = properties['Content-Type']
+            field_name = re.search(r'name="([^"]*)"' , properties['Content-Disposition'])
+            if field_name is None:
+                start_response('400 Bad Request', ())
+                return b'400 Bad Request'
+            field_name = field_name.group(1)
+
+            env['post_parameters'] = {field_name: (mime_type, file)}
+            env['parameters'][field_name] = (mime_type, file)
+
+        else:
+            start_response('400 Bad Request', ())
+            return b'400 Bad Request'
 
         try:
             handler = post_handlers[path_parts[0]]
diff --git a/yt_subscription_manager_template.html b/yt_subscription_manager_template.html
new file mode 100644
index 0000000..76cd864
--- /dev/null
+++ b/yt_subscription_manager_template.html
@@ -0,0 +1,99 @@
+<!DOCTYPE html>
+<html>
+    <head>
+        <meta charset="utf-8">
+        <title>Subscription Manager</title>
+        <link href="/youtube.com/shared.css" type="text/css" rel="stylesheet">
+        <link href="/youtube.com/favicon.ico" type="image/x-icon" rel="icon">
+        <link title="Youtube local" href="/youtube.com/opensearch.xml" rel="search" type="application/opensearchdescription+xml">
+        <style type="text/css">
+
+            .import-export{
+                display: flex;
+                flex-direction: row;
+            }
+                .subscriptions-import-form{
+                    background-color: #dadada;
+                    display: flex;
+                    flex-direction: column;
+                    align-items: flex-start;
+                    max-width: 300px;
+                    padding:10px;
+                }
+                    .subscriptions-import-form h2{
+                        font-size: 20px;
+                        margin-bottom: 10px;
+                    }
+
+                    .import-submit-button{
+                        margin-top:15px;
+                        align-self: flex-end;
+                    }
+
+
+                .subscriptions-export-links{
+                    margin: 0px 0px 0px 20px;
+                    background-color: #dadada;
+                    list-style: none;
+                    max-width: 300px;
+                    padding:10px;
+                }
+
+            .sub-list-controls{
+                background-color: #dadada;
+                padding:10px;
+            }
+
+            .sub-list{
+                list-style: none;
+                padding:10px;
+                column-width: 300px;
+                column-gap: 40px;
+            }
+                .sub-list > li{
+                    display:flex;
+                    justify-content: space-between;
+                    margin-bottom: 10px;
+                    background-color: #dadada;
+                }
+                    .sub-list-checkbox{
+                        height: 1.5em;
+                        width: 1.5em;
+                    }
+        </style>
+    </head>
+    <body>
+$header
+        <main>
+
+            <div class="import-export">
+                <form class="subscriptions-import-form" enctype="multipart/form-data" action="/youtube.com/import_subscriptions" method="POST">
+                    <h2>Import subscriptions</h2>
+                    <input type="file" id="subscriptions-import" accept="application/json, application/xml, text/x-opml" name="subscriptions_file">
+                    <input type="submit" value="Import" class="import-submit-button">
+                </form>
+
+                <ul class="subscriptions-export-links">
+                    <li><a href="/youtube.com/subscriptions.opml">Export subscriptions (OPML)</a></li>
+                    <li><a href="/youtube.com/subscriptions.xml">Export subscriptions (RSS)</a></li>
+                </ul>
+            </div>
+
+            <hr>
+
+            <form id="subscription-manager-form" class="sub-list-controls" method="POST">
+                <a class="sort-button" href="/youtube.com/subscription_manager?group_by_tags=1">Group by tags</a>
+                <input type="text" name="tags">
+                <button type="submit" name="action" value="add_tags">Add tags</button>
+                <button type="submit" name="action" value="remove_tags">Remove tags</button>
+                <button type="submit" name="action" value="unsubscribe_verify">Unsubscribe</button>
+                <input type="reset" value="Clear Selection">
+            </form>
+
+            <ol class="sub-list">
+$sub_list
+            </ol>
+
+        </main>
+    </body>
+</html>
diff --git a/yt_subscriptions_template.html b/yt_subscriptions_template.html
index 6395b6c..a6d3e38 100644
--- a/yt_subscriptions_template.html
+++ b/yt_subscriptions_template.html
@@ -7,19 +7,69 @@
         <link href="/youtube.com/favicon.ico" type="image/x-icon" rel="icon">
         <link title="Youtube local" href="/youtube.com/opensearch.xml" rel="search" type="application/opensearchdescription+xml">
         <style type="text/css">
-            .item-list{
-                width:1000px;
+            main{
+                display:flex;
+                flex-direction: row;
             }
+            .item-grid{
+                flex-grow: 1;
+            }
+            .subscriptions-sidebar{
+                flex-basis: 300px;
+                background-color: #dadada;
+                border-left: 2px;
+            }
+                .sidebar-links{
+                    display:flex;
+                    justify-content: space-between;
+                    padding-left:10px;
+                    padding-right: 10px;
+                }
+
+                .sidebar-list{
+                    list-style: none;
+                    padding-left:10px;
+                    padding-right: 10px;
+                }
+                    .sidebar-list > li{
+                        display:flex;
+                        justify-content: space-between;
+                        margin-bottom: 5px;
+                    }
+                        .sub-refresh-list .sidebar-item-name{
+                            text-overflow: clip;
+                            white-space: nowrap;
+                            overflow: hidden;
+                            max-width: 200px;
+                        }
         </style>
     </head>
     <body>
 $header
         <main>
-            <form method="POST">
-                <input type="submit" value="refresh">
-                <input type="hidden" name="action" value="refresh">
-            </form>
+
 $items
+
+            <div class="subscriptions-sidebar">
+                <div class="sidebar-links">
+                    <a href="/youtube.com/subscription_manager" class="sub-manager-link">Subscription Manager</a>
+                    <form method="POST" class="refresh-all">
+                        <input type="submit" value="Check All">
+                        <input type="hidden" name="action" value="refresh">
+                    </form>
+                </div>
+
+                <hr>
+                <ol class="sidebar-list tags">
+$tags
+                </ol>
+
+                <ol class="sidebar-list sub-refresh-list">
+$sub_list
+                </ol>
+
+            </div>
+
             <nav class="page-button-row">
 $page_buttons
             </nav>