344 lines
16 KiB
C#
344 lines
16 KiB
C#
using Tesses.WebServer;
|
|
using Scriban;
|
|
using Newtonsoft.Json;
|
|
using System.Data.SQLite;
|
|
using Dapper;
|
|
using System.Security.Cryptography;
|
|
//using YouTubeMusicAPI.Models.Info;
|
|
using YoutubeExplode;
|
|
using YoutubeExplode.Videos;
|
|
//using YouTubeMusicAPI.Models.Search;
|
|
|
|
namespace Tesses.YouTubeDownloader2025Companion
|
|
{
|
|
public class Database : IDisposable, IAsyncDisposable
|
|
{
|
|
public const long Expires = 86400 * 7;
|
|
SQLiteConnection conn;
|
|
public Database()
|
|
{
|
|
conn = new SQLiteConnection("Data Source=data/Config/database.db");
|
|
}
|
|
|
|
public void Init()
|
|
{
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS sessions (Id INTEGER PRIMARY KEY AUTOINCREMENT, AccountName TEXT, Key TEXT UNIQUE, Expires INTEGER, Flags INTEGER);");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS song_videos (Id INTEGER PRIMARY KEY AUTOINCREMENT, SongId TEXT UNIQUE, VideoId TEXT, VideoTitle TEXT);");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS songs (Id INTEGER PRIMARY KEY AUTOINCREMENT, SongId TEXT UNIQUE, SongName TEXT);");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS artists (Id INTEGER PRIMARY KEY AUTOINCREMENT, ArtistId TEXT UNIQUE, ArtistName TEXT, ArtistDisambiguation TEXT);");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS albums (Id INTEGER PRIMARY KEY AUTOINCREMENT, AlbumId TEXT UNIQUE, AlbumName TEXT, AlbumReleaseDate TEXT);");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS album_tracks (Id INTEGER PRIMARY KEY AUTOINCREMENT, AlbumId TEXT, SongId TEXT, Track INTEGER, Length INTEGER, UNIQUE(AlbumId, SongId, Track));");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS album_artists (Id INTEGER PRIMARY KEY AUTOINCREMENT, AlbumId TEXT, ArtistId TEXT, UNIQUE(AlbumId, ArtistId));");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS song_artists (Id INTEGER PRIMARY KEY AUTOINCREMENT, SongId TEXT, ArtistId TEXT, UNIQUE(SongId, ArtistId));");
|
|
conn.Execute("CREATE TABLE IF NOT EXISTS tytd_events (Id INTEGER PRIMARY KEY AUTOINCREMENT, Time INTEGER, Type TEXT, VideoTitle TEXT, VideoId TEXT, ChannelTitle TEXT, ChannelId TEXT);");
|
|
}
|
|
public async Task<IEnumerable<LogEntry>> GetLogEntriesAsync(int offset = 0, int count = 20)
|
|
{
|
|
return await conn.QueryAsync<LogEntry>("SELECT * FROM tytd_events ORDER BY Time DESC LIMIT @Count OFFSET @Offset", new DynamicParameters(new
|
|
{
|
|
Offset = offset,
|
|
Count = count
|
|
}));
|
|
}
|
|
public async Task<MediaEntry?> GetMediaEntryAsync(string albumId, string songId)
|
|
{
|
|
var song = await GetSongAsync(songId);
|
|
if (song is null) return null;
|
|
var album = await GetAlbumAsync(albumId);
|
|
if (album is null) return null;
|
|
var track = (await conn.QueryAsync<AlbumTrack>("SELECT * FROM album_tracks WHERE SongId = @SongId AND AlbumId = @AlbumId;", new DynamicParameters(new { SongId = songId, AlbumId = albumId }))).FirstOrDefault();
|
|
if (track is null) return null;
|
|
MediaEntry ent = new MediaEntry();
|
|
ent.TrackIndex = track.Track;
|
|
ent.AlbumId = album.AlbumId;
|
|
ent.SongName = song.SongName;
|
|
ent.AlbumArtist = (await GetAlbumArtistsAsync(track.AlbumId)).Select(e => e.ArtistName).FirstOrDefault() ?? "Unknown Artist";
|
|
ent.AlbumName = album.AlbumName;
|
|
ent.ContributingArtists.AddRange((await GetContributingArtistsAsync(songId)).Select(e => e.ArtistName));
|
|
return ent;
|
|
}
|
|
public async IAsyncEnumerable<MediaEntry> GetMediaEntriesAsync(string songId)
|
|
{
|
|
var song = await GetSongAsync(songId);
|
|
if (song is null) yield break;
|
|
List<string> contributingArtists = new List<string>();
|
|
contributingArtists.AddRange((await GetContributingArtistsAsync(songId)).Select(e => e.ArtistName));
|
|
|
|
|
|
foreach (var track_info in await conn.QueryAsync<AlbumTrack>("SELECT * FROM album_tracks WHERE SongId = @SongId;", new DynamicParameters(new { SongId = songId })))
|
|
{
|
|
MediaEntry ent = new MediaEntry();
|
|
ent.TrackIndex = track_info.Track;
|
|
ent.AlbumId = track_info.AlbumId;
|
|
ent.SongName = song.SongName;
|
|
ent.ContributingArtists.AddRange(contributingArtists);
|
|
ent.AlbumArtist = (await GetAlbumArtistsAsync(track_info.AlbumId)).Select(e => e.ArtistName).FirstOrDefault() ?? "Unknown Artist";
|
|
|
|
|
|
|
|
var album = await GetAlbumAsync(track_info.AlbumId);
|
|
if (album is not null)
|
|
{
|
|
ent.AlbumName = album.AlbumName;
|
|
|
|
}
|
|
else ent.AlbumName = "Unknown Album";
|
|
|
|
yield return ent;
|
|
}
|
|
}
|
|
public async Task<Session> CreateSessionTokenAsync(string username, long flags, bool expires = true)
|
|
{
|
|
Session session = new Session();
|
|
session.AccountName = username;
|
|
session.Expires = expires ? Program.NowEpoch + Expires : 0;
|
|
session.Flags = flags;
|
|
session.Key = Convert.ToBase64String(RandomNumberGenerator.GetBytes(32));
|
|
|
|
await conn.ExecuteAsync("INSERT INTO sessions (AccountName,Key,Flags,Expires) VALUES (@AccountName,@Key,@Flags,@Expires);", session);
|
|
|
|
return session;
|
|
}
|
|
public async Task<VideoId?> GetVideoIdAsync(string id)
|
|
{
|
|
var video = await GetSongVideoFromSongIdAsync(id);
|
|
if (video is not null)
|
|
{
|
|
return VideoId.TryParse(video.VideoId);
|
|
}
|
|
return null;
|
|
}
|
|
public async Task<string?> GetSongIdFromVideoId(VideoId id)
|
|
{
|
|
var video = await GetSongVideoFromVideoIdAsync(id);
|
|
if (video is not null)
|
|
{
|
|
return video.SongId;
|
|
}
|
|
return null;
|
|
}
|
|
public async Task AddVideoIdAsync(string songId, VideoId id, string title)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync($"INSERT INTO song_videos (SongId, VideoId, VideoTitle) VALUES (@SongId,@VideoId,@VideoTitle);", new DynamicParameters(new { SongId = songId, VideoId = id.Value, VideoTitle = title }));
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task UpdateVideoIdAsync(string songId, VideoId id, string title)
|
|
{
|
|
try
|
|
{
|
|
|
|
await conn.ExecuteAsync($"INSERT OR REPLACE INTO song_videos (SongId, VideoId, VideoTitle) VALUES (@SongId,@VideoId,@VideoTitle);", new DynamicParameters(new { SongId = songId, VideoId = id.Value, VideoTitle = title }));
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task AddAlbumArtistAsync(string albumId, string artistId)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync($"INSERT INTO album_artists (AlbumId, ArtistId) VALUES (@AlbumId,@ArtistId);", new DynamicParameters(new { AlbumId = albumId, ArtistId = artistId }));
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task AddContributingArtistAsync(string songId, string artistId)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync($"INSERT INTO song_artists (SongId, ArtistId) VALUES (@SongId,@ArtistId);", new DynamicParameters(new { SongId = songId, ArtistId = artistId }));
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task<SongVideo?> GetSongVideoFromVideoIdAsync(VideoId id)
|
|
{
|
|
return (await conn.QueryAsync<SongVideo>($"SELECT * FROM song_videos WHERE VideoId = @VideoId;", new DynamicParameters(new { VideoId = id.Value }))).FirstOrDefault();
|
|
}
|
|
public async Task<SongVideo?> GetSongVideoFromSongIdAsync(string id)
|
|
{
|
|
return (await conn.QueryAsync<SongVideo>($"SELECT * FROM song_videos WHERE SongId = @SongId;", new DynamicParameters(new { SongId = id }))).FirstOrDefault();
|
|
}
|
|
public async Task AddArtistAsync(Artist artist)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync("INSERT INTO artists (ArtistId,ArtistName,ArtistDisambiguation) VALUES (@ArtistId,@ArtistName,@ArtistDisambiguation);", artist);
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task AddAlbumAsync(Album album)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync("INSERT INTO albums (AlbumId,AlbumName,AlbumReleaseDate) VALUES (@AlbumId,@AlbumName,@AlbumReleaseDate);", album);
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task AddSongAsync(Song song)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync("INSERT INTO songs (SongId,SongName) VALUES (@SongId,@SongName);", song);
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task AddTrackAsync(AlbumTrack track)
|
|
{
|
|
try
|
|
{
|
|
await conn.ExecuteAsync("INSERT INTO album_tracks (AlbumId,SongId,Track,Length) VALUES (@AlbumId,@SongId,@Track,@Length);", track);
|
|
}
|
|
catch (Exception) { }
|
|
}
|
|
public async Task<Song?> GetSongAsync(string id)
|
|
{
|
|
return (await conn.QueryAsync<Song>($"SELECT * FROM songs WHERE SongId = @SongId;", new DynamicParameters(new { SongId = id }))).FirstOrDefault();
|
|
}
|
|
public async Task<IEnumerable<Artist>> GetContributingArtistsAsync(string songId)
|
|
{
|
|
return await conn.QueryAsync<Artist>($"SELECT * FROM song_artists s INNER JOIN artists a ON s.ArtistId = a.ArtistId WHERE s.SongId = @SongId ORDER BY s.Id ASC;", new DynamicParameters(new { SongId = songId }));
|
|
|
|
}
|
|
public async Task<IEnumerable<Artist>> GetArtistsAsync(string q = "", long offset = 0, long count = 20)
|
|
{
|
|
return await conn.QueryAsync<Artist>("SELECT * FROM artists WHERE ArtistName LIKE @Query LIMIT @Count OFFSET @Offset;", new DynamicParameters(new
|
|
{
|
|
Query = $"%{q}%",
|
|
Offset = offset,
|
|
Count = count
|
|
}));
|
|
}
|
|
public async Task<IEnumerable<Song>> GetSongsAsync(string q = "", long offset = 0, long count = 20)
|
|
{
|
|
return await conn.QueryAsync<Song>("SELECT * FROM songs WHERE SongName LIKE @Query LIMIT @Count OFFSET @Offset;", new DynamicParameters(new
|
|
{
|
|
Query = $"%{q}%",
|
|
Offset = offset,
|
|
Count = count
|
|
}));
|
|
}
|
|
public async Task<IEnumerable<Album>> GetAlbumsFromArtistAsync(string artistId, string q = "", long offset = 0, long count = 20)
|
|
{
|
|
return await conn.QueryAsync<Album>("SELECT * FROM albums al INNER JOIN album_artists ar ON al.AlbumId = ar.AlbumId WHERE AlbumName LIKE @Query AND ar.ArtistId = @ArtistId ORDER BY al.AlbumReleaseDate, al.Id ASC LIMIT @Count OFFSET @Offset;", new DynamicParameters(new
|
|
{
|
|
Query = $"%{q}%",
|
|
Offset = offset,
|
|
Count = count,
|
|
ArtistId = artistId
|
|
}));
|
|
}
|
|
public async Task<IEnumerable<Song>> GetUnavailableSongsAsync(long offset=0, long count=20)
|
|
{
|
|
//SELECT * FROM songs s WHERE NOT EXISTS (SELECT * FROM song_videos v WHERE v.SongId = s.SongId);
|
|
return await conn.QueryAsync<Song>("SELECT * FROM songs s WHERE NOT EXISTS (SELECT * FROM song_videos v WHERE v.SongId = s.SongId) LIMIT @Count OFFSET @Offset;", new DynamicParameters(new
|
|
{
|
|
Offset = offset,
|
|
Count = count
|
|
}));
|
|
}
|
|
|
|
public async Task<IEnumerable<Album>> GetAlbumsAsync(string q = "", long offset = 0, long count = 20)
|
|
{
|
|
return await conn.QueryAsync<Album>("SELECT * FROM albums WHERE AlbumName LIKE @Query ORDER BY AlbumReleaseDate, Id ASC LIMIT @Count OFFSET @Offset;", new DynamicParameters(new
|
|
{
|
|
Query = $"%{q}%",
|
|
Offset = offset,
|
|
Count = count
|
|
}));
|
|
}
|
|
public async Task<IEnumerable<SongVideo>> GetSongVideosAsync()
|
|
{
|
|
return await conn.QueryAsync<SongVideo>($"SELECT * FROM song_videos;");
|
|
|
|
}
|
|
public async Task<IEnumerable<Artist>> GetAlbumArtistsAsync(string albumId)
|
|
{
|
|
return await conn.QueryAsync<Artist>($"SELECT * FROM album_artists s INNER JOIN artists a ON s.ArtistId = a.ArtistId WHERE s.AlbumId = @AlbumId;", new DynamicParameters(new { AlbumId = albumId }));
|
|
|
|
}
|
|
public async Task<Artist?> GetArtistAsync(string id)
|
|
{
|
|
return (await conn.QueryAsync<Artist>($"SELECT * FROM artists WHERE ArtistId = @ArtistId;", new DynamicParameters(new { ArtistId = id }))).FirstOrDefault();
|
|
}
|
|
public async Task<Album?> GetAlbumAsync(string id)
|
|
{
|
|
return (await conn.QueryAsync<Album>("SELECT * FROM albums WHERE AlbumId = @AlbumId;", new DynamicParameters(new { AlbumId = id }))).FirstOrDefault();
|
|
}
|
|
public async Task<AlbumTrack?> GetTrackAsync(string songId, string albumId)
|
|
{
|
|
return (await conn.QueryAsync<AlbumTrack>("SELECT * FROM album_tracks WHERE AlbumId = @AlbumId AND SongId = @SongId", new DynamicParameters(new
|
|
{
|
|
SongId = songId,
|
|
AlbumId = albumId
|
|
}))).FirstOrDefault();
|
|
}
|
|
public async Task<IEnumerable<AlbumSongTrack>> GetTracksAsync(string albumId)
|
|
{
|
|
return await conn.QueryAsync<AlbumSongTrack>("SELECT * FROM songs s INNER JOIN album_tracks t ON s.SongId = t.SongId WHERE t.AlbumId = @AlbumId ORDER BY t.Track ASC;", new DynamicParameters(new { AlbumId = albumId }));
|
|
}
|
|
public async Task<Session?> GetSessionAsync(ServerContext ctx, string sessionToken)
|
|
{
|
|
var time = Program.NowEpoch;
|
|
var res = (await conn.QueryAsync<Session>("SELECT * FROM sessions WHERE Key = @Key;", new DynamicParameters(new { Key = sessionToken }))).FirstOrDefault();
|
|
|
|
if (res is not null)
|
|
{
|
|
var expires = res.Expires;
|
|
if (expires != 0 && time < expires && (expires - time) < (Expires - 3600))
|
|
{
|
|
long expiry = time + Expires;
|
|
res.Expires = expiry;
|
|
await conn.ExecuteAsync("UPDATE sessions SET Expires = @Expires WHERE Key = @Key;", res);
|
|
|
|
ctx.ResponseHeaders["Set-Cookie"] = [$"Session={res.Key}; SameSite=Lax; Expires={DateTimeOffset.FromUnixTimeSeconds(expiry).ToString("R")}; HttpOnly"];
|
|
}
|
|
else if (expires != 0 && time >= expires)
|
|
{
|
|
await conn.ExecuteAsync("DELETE FROM sessions WHERE Key = @Key;", res);
|
|
return null;
|
|
}
|
|
}
|
|
return res;
|
|
}
|
|
|
|
public void Dispose()
|
|
{
|
|
conn.Dispose();
|
|
}
|
|
|
|
public async ValueTask DisposeAsync()
|
|
{
|
|
await conn.DisposeAsync();
|
|
}
|
|
|
|
|
|
public async Task AddEventAsync(long epoch, Webhook webhook)
|
|
{
|
|
//conn.Execute("CREATE TABLE IF NOT EXISTS tytd_events (Id INTEGER PRIMARY KEY AUTOINCREMENT, Time INTEGER, Type TEXT, VideoTitle TEXT, VideoId TEXT, ChannelTitle TEXT, ChannelId TEXT);");
|
|
await conn.ExecuteAsync("INSERT INTO tytd_events (Time,Type,VideoTitle,VideoId,ChannelTitle,ChannelId) VALUES (@Time,@Type,@VideoTitle,@VideoId,@ChannelTitle,@ChannelId);", new DynamicParameters(new
|
|
{
|
|
Time = epoch,
|
|
Type = webhook.Type,
|
|
VideoTitle = webhook.Video.Title,
|
|
VideoId = webhook.Video.VideoId,
|
|
ChannelTitle = webhook.Video.Channel,
|
|
ChannelId = webhook.Video.ChannelId
|
|
}));
|
|
}
|
|
|
|
public class Session
|
|
{
|
|
public long Id { get; set; }
|
|
|
|
public string AccountName { get; set; } = "";
|
|
|
|
public string Key { get; set; } = "";
|
|
|
|
public long Flags {get;set;}
|
|
|
|
public long Expires { get; set; }
|
|
}
|
|
}
|
|
|
|
}
|