public static void ResetSongsInPlaylist(int playlistId, IEnumerable <int> songIDs) { PlaylistSongTable psTable = new PlaylistSongTable(); string safeCondition = $"WHERE {psTable.TableName}.{psTable.PlaylistId.Name} = {playlistId}"; int[] iDs = songIDs as int[] ?? songIDs.ToArray(); List <BaseModelItem> items = new List <BaseModelItem>(iDs.Length); int i = 0; foreach (int id in iDs) { items.Add(new PlaylistSongItem(playlistId, id, i++)); } using (SqliteConnection con = CreateConnection()) { con.Open(); bool transaction = StartTransaction(con); DeleteRows(con, psTable, safeCondition); InsertRows(con, psTable, items.ToArray()); if (transaction) { _transaction?.Commit(); } } }
public static List <SongItem> SortSongs(int playlistId, string colName, bool ascending) { PlaylistSongTable psTable = new PlaylistSongTable(); SongTable songTable = new SongTable(); string query = $"SELECT * FROM {psTable.TableName} INNER JOIN {songTable.TableName}" + $" ON {psTable.TableName}.{psTable.SongId} = {songTable.TableName}.{songTable.Id}" + $" WHERE {psTable.TableName}.{psTable.PlaylistId} = {playlistId}" + $" ORDER BY {songTable.TableName}.{colName}" + (ascending ? " ASC" : " DESC"); List <SongItem> orderedSongs = new List <SongItem>(); List <PlaylistSongItem> orderedPlSongs = new List <PlaylistSongItem>(); int newPos = 0; using (SqliteConnection con = CreateConnection()) { con.Open(); SqliteCommand command = con.CreateCommand(); command.CommandText = query; SqliteDataReader reader = command.ExecuteReader(); while (reader.Read()) { orderedSongs.Add(new SongItem(reader)); orderedPlSongs.Add(new PlaylistSongItem(reader) { PosInPlaylist = newPos++ }); } UpdateRows(con, psTable, orderedPlSongs); return(orderedSongs); } }
private static void CreatePlaylistSongTable(SqliteConnection con, bool force = false) { PlaylistSongTable table = new PlaylistSongTable(); CreateTable(con, table, force); CreateIndex(con, table, true, force, table.PlaylistId.Name, table.SongId.Name); }
public static void RemoveSongsFromPlaylist(int playlistId, int[] songIDs) { PlaylistSongTable psTable = new PlaylistSongTable(); string safeCondition = $"WHERE {psTable.PlaylistId.Name} = {playlistId} AND " + $"{psTable.SongId.Name} IN( {string.Join(", ", songIDs)})"; List <PlaylistSongItem> psItems = new List <PlaylistSongItem>(songIDs.Length); int newPos = 0; using (SqliteConnection con = CreateConnection()) { con.Open(); DeleteRows(con, psTable, safeCondition); SqliteDataReader reader = GetItems(con, psTable, $"WHERE {psTable.PlaylistId.Name} = {playlistId}"); while (reader.Read()) { psItems.Add(new PlaylistSongItem(reader) { PosInPlaylist = newPos++ }); } UpdateRows(con, psTable, psItems); } }
private static SongItem?FindPlayingSong(bool next, int currentSongId, int playlistId, params int[] masteryIDs) { SongTable songTable = new SongTable(); PlaylistSongTable psTable = new PlaylistSongTable(); SongItem? songFound = null; string comparator = next ? ">" : "<"; string minMax = next ? "MIN" : "MAX"; string[] formattedCols = songTable.GetAllColumns().Select(x => "st." + x).ToArray(); string query = $"SELECT {minMax}(ps1.{psTable.PosInPlaylist})," + $" {string.Join(", ", formattedCols)}" + $" FROM {psTable.TableName} ps1 INNER JOIN {songTable.TableName} st" + $" ON ps1.{psTable.SongId} = st.{songTable.Id}" + $" WHERE ps1.{psTable.PlaylistId} = @playlistId"; if (masteryIDs.Any()) { string[] masteryParams = new string[masteryIDs.Length]; for (int i = 0; i < masteryIDs.Length; i++) { masteryParams[i] = "@masteryId" + i; } query += $" AND st.{songTable.MasteryId} IN (" + string.Join(", ", masteryParams) + ")"; } query += $" AND ps1.{psTable.PosInPlaylist} {comparator} (" + $" SELECT ps2.{psTable.PosInPlaylist} FROM {psTable.TableName} ps2 WHERE ps2.{psTable.SongId} = @currentSongId" + $" AND ps2.{psTable.PlaylistId} = @playlistId)"; using (var con = CreateConnection()) { SqliteCommand cmd = con.CreateCommand(); cmd.CommandText = query; cmd.Parameters.AddWithValue("playlistId", playlistId); for (int i = 0; i < masteryIDs.Length; i++) { cmd.Parameters.AddWithValue("masteryId" + i, masteryIDs[i]); } cmd.Parameters.AddWithValue("currentSongId", currentSongId); con.Open(); SqliteDataReader dataReader = cmd.ExecuteReader(); if (dataReader.Read() && !dataReader.IsDBNull(0)) { songFound = new SongItem(dataReader); } else { string info = "No {next} song found for songId {songId}, playlistId {playlistId} and masteryIDs {masteryIDs}"; Log.Information(info, next? "next" : "previous", currentSongId, playlistId, masteryIDs); } if (dataReader.Read()) { string info = "More than one {next} song found for songId {songId}, playlistId {playlistId} and masteryIDs {masteryIDs}"; Log.Error(info, next ? "next" : "previous", currentSongId, playlistId, masteryIDs); } } return(songFound); }
/* * SELECT Song.ID, Song.Name, ... FROM Song INNER JOIN * (SELECT PlaylistSong.SongID FROM PlaylistSong WHERE PlaylistSong.PlaylistID = [playlistID] ON PlaylistSong.SongID = Song.SongID) ps * ON ps.SongID = Song.SongID * WHERE Song.MasteryID IN ([masteryIDs[0]], [masteryIDs[1]]...) * ORDER BY ps.PosInPlaylist ASC */ public static async Task <List <SongItem> > GetSongs(int playlistId) { return(await Task.Run(() => { List <SongItem> songs = new List <SongItem>(); SongTable songTable = new SongTable(); PlaylistSongTable playlistSongTable = new PlaylistSongTable(); string psName = playlistSongTable.TableName; string condition = $"INNER JOIN (SELECT * FROM {psName}" + $" WHERE {psName}.{playlistSongTable.PlaylistId.Name} = {playlistId}) ps" + $" ON ps.{playlistSongTable.SongId.Name} = {songTable.TableName}.{songTable.Id.Name}" + $" ORDER BY ps.{playlistSongTable.PosInPlaylist.Name} ASC"; using (var con = CreateConnection()) { con.Open(); SqliteDataReader dataReader = GetItems(con, songTable, condition); while (dataReader.Read()) { songs.Add(new SongItem(dataReader)); } } return songs; })); }
public static bool IsSongInPlaylist(int playlistId, int songId) { PlaylistSongTable table = new PlaylistSongTable(); using (SqliteConnection con = CreateConnection()) { con.Open(); return(Exists(con, table, new SqlColumn[] { table.PlaylistId, table.SongId }, playlistId, songId)); } }
/*private static int GetPositionInPlaylist(SqliteConnection con, int songId, int playlistId) * { * PlaylistSongTable psTable = new PlaylistSongTable(); * * SqliteCommand command = con.CreateCommand(); * command.CommandText = $"SELECT {psTable.TableName}.{psTable.PosInPlaylist}" + * $" FROM {psTable.TableName}" + * $" WHERE {psTable.TableName}.{psTable.SongId} = {songId}" + * $" AND {psTable.TableName}.{psTable.PlaylistId} = {playlistId}"; * object answer = command.ExecuteScalar(); * if (answer is null || answer is DBNull) * { * Log.Warning("Could not find the position of the songId {songId} in the playlistId {playlistId}", songId, playlistId); * return -1; * } * return Convert.ToInt32(answer); * }*/ //Returns the first free available position in the playlist private static int GetLastPositionInPlaylist(SqliteConnection con, int playlistId) { PlaylistSongTable table = new PlaylistSongTable(); SqliteCommand command = con.CreateCommand(); command.CommandText = $"SELECT MAX({table.TableName}.{table.PosInPlaylist})" + $" FROM {table.TableName}" + $" WHERE {table.TableName}.{table.PlaylistId} = {playlistId}"; object pos = command.ExecuteScalar(); if (pos is null || pos is DBNull) { return(0); } return(Convert.ToInt32(pos) + 1); }
//Adds at the end of the playlist public static void AddSongsToPlaylist(int playlistId, IEnumerable <int> songIDs) { PlaylistSongTable table = new PlaylistSongTable(); int[] iDs = songIDs as int[] ?? songIDs.ToArray(); List <BaseModelItem> items = new List <BaseModelItem>(iDs.Length); using (SqliteConnection con = CreateConnection()) { con.Open(); int pos = GetLastPositionInPlaylist(con, playlistId); foreach (int id in iDs) { items.Add(new PlaylistSongItem(playlistId, id, pos++)); } InsertRows(con, table, items.ToArray(), true); } }