From a6b6a61f2a0fc2fe9e0ac18faac004687f607581 Mon Sep 17 00:00:00 2001 From: girst Date: Wed, 15 Dec 2021 16:07:14 +0100 Subject: [PATCH] [DATABASE CHANGE: Migration below] mark premiering videos with 'SOON' badge their premiering date is now captured correctly, too. ALTER TABLE videos ADD COLUMN premiere BOOLEAN DEFAULT 0; --- app/common/common.py | 29 +++++++++++++++++++++++------ app/youtube/__init__.py | 5 +++-- app/youtube/templates/index.html.j2 | 2 +- config/setup.sql | 1 + 4 files changed, 28 insertions(+), 9 deletions(-) diff --git a/app/common/common.py b/app/common/common.py index 81dc138..c17fefc 100644 --- a/app/common/common.py +++ b/app/common/common.py @@ -116,6 +116,7 @@ def update_channel(db, xmldata, from_webhook=False): c.execute("SELECT 1 FROM videos WHERE id=?",(video['video_id'],)) new_video = len(c.fetchall()) < 1 if new_video: + # TODO: call store_video_metadata(video_id) here instead and pass video-fallback-metadata to it _, _, meta, _, _ = get_video_info(video['video_id'], metaOnly=True) # The 'published' timestamp sent in websub POSTs are often wrong (e.g.: # video gets uploaded as unlisted on day A and set to public on day B; @@ -127,6 +128,7 @@ def update_channel(db, xmldata, from_webhook=False): published = dateutil.parser.parse(video['published']) length = None livestream = None + premiere = None if meta: meta = video_metadata(meta) published2 = dateutil.parser.parse(meta['published']) @@ -134,6 +136,7 @@ def update_channel(db, xmldata, from_webhook=False): published = published2 length = meta['length'] livestream = meta['livestream'] + premiere = meta['premiere'] now = datetime.now(timezone.utc) @@ -147,14 +150,15 @@ def update_channel(db, xmldata, from_webhook=False): c.execute(""" INSERT OR IGNORE INTO videos - (id, channel_id, title, length, livestream, published, crawled) - VALUES (?, ?, ?, ?, ?, datetime(?), datetime(?)) + (id, channel_id, title, length, livestream, premiere, published, crawled) + VALUES (?, ?, ?, ?, ?, ?, datetime(?), datetime(?)) """, ( video['video_id'], video['channel_id'], video['title'], length, livestream, + premiere, video['published'], timestamp )) @@ -301,8 +305,18 @@ def video_metadata(metadata): # yet populated. Nothing we can do about it. length = int(meta1.get('lengthSeconds',0)) or None - published_at = meta2.get('liveBroadcastDetails',{}) \ - .get('startTimestamp', f"{meta2.get('publishDate','1970-01-01')}T00:00:00Z") + scheduled_time = metadata.get('playabilityStatus',{}) \ + .get('liveStreamability',{}).get('liveStreamabilityRenderer',{}) \ + .get('offlineSlate',{}).get('liveStreamOfflineSlateRenderer',{}) \ + .get('scheduledStartTime') + if scheduled_time: + scheduled_time = datetime.fromtimestamp(int(scheduled_time)) \ + .strftime("%Y-%m-%dT%H:%M:%SZ") + published_at = ( + meta2.get('liveBroadcastDetails',{}) .get('startTimestamp') or + scheduled_time or + f"{meta2.get('publishDate','1970-01-01')}T00:00:00Z" + ) # Note: 'premiere' videos have livestream=False and published= will be the # start of the premiere. @@ -314,6 +328,7 @@ def video_metadata(metadata): 'views': int(meta1['viewCount']), 'length': length, 'livestream': meta1['isLiveContent'], + 'premiere': meta1.get('isUpcoming') and not meta1['isLiveContent'], } def store_video_metadata(video_id): @@ -327,13 +342,15 @@ def store_video_metadata(video_id): if meta: meta = video_metadata(meta) c.execute(""" - INSERT OR IGNORE INTO videos (id, channel_id, title, length, published, crawled) - VALUES (?, ?, ?, ?, datetime(?), datetime(?)) + INSERT OR IGNORE INTO videos (id, channel_id, title, length, livestream, premiere, published, crawled) + VALUES (?, ?, ?, ?, ?, ?, datetime(?), datetime(?)) """, ( video_id, meta['channel_id'], meta['title'], meta['length'], + meta['livestream'], + meta['premiere'], meta['published'], meta['published'], )) diff --git a/app/youtube/__init__.py b/app/youtube/__init__.py index 6bb528f..62dd1e1 100644 --- a/app/youtube/__init__.py +++ b/app/youtube/__init__.py @@ -34,7 +34,7 @@ def feed(): with sqlite3.connect(cf['global']['database']) as conn: c = conn.cursor() c.execute(""" - SELECT videos.id, channel_id, name, title, length, livestream, published, playlist_videos.playlist_id, display + SELECT videos.id, channel_id, name, title, length, livestream, premiere, published, playlist_videos.playlist_id, display FROM videos JOIN channels ON videos.channel_id = channels.id LEFT JOIN playlist_videos ON (videos.id = playlist_videos.video_id) @@ -53,10 +53,11 @@ def feed(): 'title': title, 'length': length, 'livestream': livestream, + 'premiere': premiere, 'published': published, 'playlist': playlist, 'pinned': display == 'pinned', - } for (video_id, channel_id, author, title, length, livestream, published, playlist, display) in c.fetchall()] + } for (video_id, channel_id, author, title, length, livestream, premiere, published, playlist, display) in c.fetchall()] return render_template('index.html.j2', rows=rows, page=page) @frontend.route('/watch') diff --git a/app/youtube/templates/index.html.j2 b/app/youtube/templates/index.html.j2 index ce0adf4..a5b843e 100644 --- a/app/youtube/templates/index.html.j2 +++ b/app/youtube/templates/index.html.j2 @@ -7,7 +7,7 @@ {{ super() }}
{% for row in rows %} - {% set badge = 'LIVE' if row.livestream else row.length|format_time %} + {% set badge = 'LIVE' if row.livestream else 'SOON' if row.premiere else row.length|format_time %} {% call macros.card(row.video_id, row.title, row.published|format_date, row.pinned, badge=badge) %} {{ macros.infobar_subscriptions(row.video_id, row.channel_id, row.author) }} {% endcall %} diff --git a/config/setup.sql b/config/setup.sql index 05cfd5f..4514e83 100644 --- a/config/setup.sql +++ b/config/setup.sql @@ -20,6 +20,7 @@ CREATE TABLE IF NOT EXISTS videos( title STRING, length INTEGER, livestream BOOLEAN DEFAULT 0, + premiere BOOLEAN DEFAULT 0, published DATETIME, crawled DATETIME DEFAULT CURRENT_TIMESTAMP); CREATE TABLE IF NOT EXISTS playlist_videos( -- 2.39.3