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);
}
}
