/// <summary>
/// Load Song
/// </summary>
/// <param name="SongId"></param>
/// <returns></returns>
public Song LoadSong(int SongId)
{
Song song = new Song();
try
{
using (SMLDBEntities context = new SMLDBEntities())
{
if (SongId > 0)
{
SongTable tblSong = context.SongTables.Where(t => t.SongID == SongId).FirstOrDefault();
if (tblSong != null)
{
song.SongID = tblSong.SongID;
song.SongName = tblSong.SongName;
song.Artist = tblSong.Artist;
song.Lyrics = tblSong.Lyrics;
song.UserId = tblSong.UserId;
}
}
}
}
catch (Exception)
{
return(null);
}
return(song);
}
/// <summary>
/// Handles the data serialization
/// </summary>
/// <param name="s">The serializer object</param>
public override void SerializeImpl(SerializerObject s)
{
InstrumentTable = s.SerializePointer <MusyX_InstrumentTable>(InstrumentTable, anchor: Offset, name: nameof(InstrumentTable));
Unknown2List1 = s.SerializePointer <MusyX_UnknownTable>(Unknown2List1, anchor: Offset, resolve: true, name: nameof(Unknown2List1));
Unknown2List2 = s.SerializePointer <MusyX_UnknownTable>(Unknown2List2, anchor: Offset, resolve: true, name: nameof(Unknown2List2));
Unknown2List3 = s.SerializePointer <MusyX_UnknownTable>(Unknown2List3, anchor: Offset, resolve: true, name: nameof(Unknown2List3));
UInt_10 = s.Serialize <uint>(UInt_10, name: nameof(UInt_10));
UInt_14 = s.Serialize <uint>(UInt_14, name: nameof(UInt_14));
SongTable = s.SerializePointer <MusyX_SongTable>(SongTable, anchor: Offset, name: nameof(SongTable));
SampleTable = s.SerializePointer <MusyX_SampleTable>(SampleTable, anchor: Offset, name: nameof(SampleTable));
// Read instrument table
InstrumentTable.Resolve(s, onPreSerialize: st => {
st.BaseOffset = Offset;
st.EndOffset = Unknown2List1.pointer;
});
// Read song table
SongTable.Resolve(s, onPreSerialize: st => {
st.BaseOffset = Offset;
st.EndOffset = SampleTable.pointer;
});
// Read sample table
SampleTable.Resolve(s, onPreSerialize: st => {
st.BaseOffset = Offset;
//st.EndOffset = SampleTable.pointer;
});
}
/// <summary>
/// Update Song
/// </summary>
/// <param name="song"></param>
/// <returns></returns>
public int UpdateSong(Song song)
{
using (SMLDBEntities context = new SMLDBEntities())
{
using (System.Data.Entity.DbContextTransaction transaction = context.Database.BeginTransaction())
{
try
{
SongTable tblSong = context.SongTables.Where(t => t.SongID == song.SongID).FirstOrDefault();
if (tblSong != null && tblSong.SongID > 0)
{
tblSong.SongID = song.SongID;
tblSong.SongName = song.SongName ?? tblSong.SongName;
tblSong.Artist = song.Artist ?? tblSong.Artist;
tblSong.Lyrics = song.Lyrics ?? tblSong.Lyrics;
tblSong.UserId = song.UserId > 0 ? song.UserId : tblSong.UserId;
}
context.SaveChanges();
transaction.Commit();
song.SongID = tblSong.SongID;
return(song.SongID);
}
catch (Exception)
{
transaction.Rollback();
return(0);
}
}
}
}
private static void CreateSongTable(SqliteConnection con, bool force = false)
{
SongTable songTable = new SongTable();
CreateTable(con, songTable, force);
CreateIndex(con, songTable, true, force, songTable.PartitionDirectory.Name);
}
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 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);
}
}
/// <summary>
/// Save Song
/// </summary>
/// <param name="song"></param>
/// <returns></returns>
public int SaveSong(Song song)
{
using (SMLDBEntities context = new SMLDBEntities())
{
using (System.Data.Entity.DbContextTransaction transaction = context.Database.BeginTransaction())
{
try
{
SongTable tblUser = new SongTable()
{
SongID = song.SongID,
SongName = song.SongName,
Artist = song.Artist,
Lyrics = song.Lyrics,
UserId = song.UserId
};
context.SongTables.Add(tblUser);
context.SaveChanges();
transaction.Commit();
song.SongID = tblUser.SongID;
return(song.SongID);
}
catch (Exception)
{
transaction.Rollback();
return(0);
}
}
}
}
public PlaylistSongTable() : base("PlaylistSong")
{
PlaylistTable playlistTable = new PlaylistTable();
SongTable songTable = new SongTable();
PlaylistId = new SqlColumn("PlaylistId", EType.Int, playlistTable.TableName, playlistTable.Id.Name, true);
SongId = new SqlColumn("SongId", EType.Int, songTable.TableName, songTable.Id.Name, true);
PosInPlaylist = new SqlColumn("PosInPlaylist", EType.Int);
}
public static bool IsSongExisting(string partitionDir)
{
SongTable songTable = new SongTable();
using (var con = CreateConnection())
{
con.Open();
return(Exists(con, songTable, new SqlColumn[] { songTable.PartitionDirectory }, partitionDir));
}
}
public static void DeleteSongs(int[] songIDs)
{
SongTable songTable = new SongTable();
string safeCondition = $"WHERE {songTable.Id.Name} IN ( {string.Join(", ", songIDs)})";
using (SqliteConnection con = CreateConnection())
{
con.Open();
DeleteRows(con, songTable, safeCondition);
}
}
//we suppose the song doesn't already exist!
//Adds the song and then adds at the end of the playlists
public static void AddSong(SongItem song)
{
SongTable songTable = new SongTable();
using (var con = CreateConnection())
{
con.Open();
InsertRow(con, songTable, song);
}
AddPlaylistSongLink(1, song.Id);
}
public static void SetSongMastery(SongItem song, MasteryItem mastery)
{
song.MasteryId = mastery.Id;
SongTable table = new SongTable();
using (SqliteConnection con = CreateConnection())
{
con.Open();
UpdateRow(con, table, song);
}
}
public SongModel SearchSongByName(string songName, int artistId)
{
SongTable songTable = DatabaseManager.Current.LookupSong(songName, artistId);
if (songTable == null)
{
return(null);
}
else
{
return(LookupSongById(songTable.SongId));
}
}
public static TrackInfo TrackInfoFromRowId(int rowId)
{
try
{
PlayQueueEntryTable playQueueEntry = DatabaseManager.Current.LookupPlayQueueEntryById(rowId);
if (playQueueEntry != null)
{
SongTable songTable = DatabaseManager.Current.LookupSongById(playQueueEntry.SongId);
if (songTable != null)
{
AlbumTable albumTable = DatabaseManager.Current.LookupAlbumById(songTable.AlbumId);
ArtistTable artistTable = DatabaseManager.Current.LookupArtistById(songTable.ArtistId);
if (albumTable != null && artistTable != null)
{
ArtistTable albumArtistTable = DatabaseManager.Current.LookupArtistById(albumTable.ArtistId);
if (albumArtistTable != null)
{
return(new TrackInfo(songTable.Name, artistTable.Name, albumTable.Name, albumArtistTable.Name, songTable.Source, albumTable.AlbumArt, rowId, playQueueEntry.NextId, playQueueEntry.PrevId, playQueueEntry.SongId));
}
else
{
Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no artistEntry {1} matches!", rowId, albumTable.ArtistId);
}
}
else
{
Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no albumEntry {1} or artistEntry {2} matches ({3} {4})!", rowId, songTable.AlbumId, songTable.ArtistId, albumTable != null, artistTable != null);
}
}
else
{
Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no songEntry for {1} matches!", rowId, playQueueEntry.SongId);
}
}
else
{
Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no playQueueEntry matches!", rowId);
}
}
catch (SQLiteException ex)
{
Logger.Current.Log(new CallerInfo(), LogLevel.Error, "Couldn't play row {0}, got an exception {1}!", rowId, ex.Message);
}
return(null);
}
/*
* public static SongItem GetSong(int songId)
* {
* SongTable songTable = new SongTable();
* SqliteParameter param = CreateParameter("@" + songTable.Id, songTable.Id.SqlType, songId);
* string condition = $"WHERE {songTable.TableName}.{songTable.Id} = {param.ParameterName}";
* using (var con = CreateConnection())
* {
* con.Open();
* SqliteDataReader dataReader = GetItem(con, songTable, condition, param);
* if (dataReader.Read())
* return new SongItem(dataReader);
* }
* throw new SqliteException("Could not find the song corresponding to the id: " + songId, 1);
* }
*/
public static SongItem GetSong(string partitionDir)
{
SongTable songTable = new SongTable();
SqliteParameter param = CreateParameter("@" + songTable.PartitionDirectory, songTable.PartitionDirectory.SqlType, partitionDir);
string condition = $"WHERE {songTable.TableName}.{songTable.PartitionDirectory} = {param.ParameterName}";
using (var con = CreateConnection())
{
con.Open();
SqliteDataReader dataReader = GetItem(con, songTable, condition, param);
if (dataReader.Read())
{
return(new SongItem(dataReader));
}
}
throw new SqliteException("Could not find the song corresponding to : " + partitionDir, 1);
}
public static void SetSongsMastery(IEnumerable <SongItem> songs, MasteryItem mastery)
{
SongTable table = new SongTable();
using (SqliteConnection con = CreateConnection())
{
con.Open();
bool transactionStarted = StartTransaction(con);
foreach (SongItem song in songs)
{
song.MasteryId = mastery.Id;
UpdateRow(con, table, song);
}
if (transactionStarted)
{
_transaction?.Commit();
}
}
}
private void UpdateFullTime()
{
if (CurrentEntry == null)
{
FullTime = TimeSpan.Zero;
}
else
{
SongTable song = DatabaseManager.Current.LookupSongById(CurrentEntry.SongId);
if (song == null)
{
DebugHelper.Alert(new CallerInfo(), "SongId {0} got from a playqueue row but couldn't find it in database!!", CurrentEntry.SongId);
FullTime = TimeSpan.Zero;
}
else
{
FullTime = TimeSpan.FromTicks(song.Duration);
}
}
}
private SongModel LookupSongByPath(string path)
{
SongTable songTable = DatabaseManager.Current.LookupSongByPath(path);
if (songTable == null)
{
return(null);
}
if (songLookupDictionary.ContainsKey(songTable.SongId))
{
return(songLookupDictionary[songTable.SongId]);
}
SongModel songModel = new SongModel(songTable);
_allSongs.Add(songModel);
songLookupDictionary.Add(songModel.SongId, songModel);
return(songModel);
}
public SongModel LookupSongById(int songId)
{
if (songLookupDictionary.ContainsKey(songId))
{
return(songLookupDictionary[songId]);
}
else
{
SongTable songTable = DatabaseManager.Current.LookupSongById(songId);
if (songTable == null)
{
return(null);
}
else
{
SongModel songModel = new SongModel(songTable);
_allSongs.Add(songModel);
songLookupDictionary.Add(songModel.SongId, songModel);
return(songModel);
}
}
}
public SongModel AddNewSong(string artist, string album, string albumArtist, string title, string path, SongOriginSource origin, long duration, uint rating, uint trackNumber)
{
ArtistModel artistModel = LookupArtistByName(artist);
ArtistModel albumArtistModel = LookupArtistByName(albumArtist);
AlbumModel albumModel = LookupAlbumByName(album, albumArtistModel.ArtistId);
SongModel currentTableEntry = LookupSongByPath(path);
if (currentTableEntry == null)
{
SongTable newSong = new SongTable(albumModel.AlbumId, artistModel.ArtistId, duration, 0, title, origin, 0, rating, path, trackNumber);
DatabaseManager.Current.AddSong(newSong);
SongModel songModel = new SongModel(newSong);
_allSongs.Add(songModel);
songLookupDictionary.Add(songModel.SongId, songModel);
return(songModel);
}
return(null);
}
static SongTable extract_song_table(byte[] file_contents, int song_table_address, int first_song_index, int last_song_index)
{
SongTable song_table = new SongTable();
song_table.song_header_ptrs = new List <int>();
song_table.track_group_numbers = new List <int>();
var working_address = song_table_address;
working_address += first_song_index * 8;
for (int idx = first_song_index; idx < last_song_index + 1; ++idx)
{
var song_header_ptr = read_32_bit_from_file_at_offset(file_contents, working_address);
working_address += 4;
var track_group_numbers = read_32_bit_from_file_at_offset(file_contents, working_address);
working_address += 4;
song_table.song_header_ptrs.Add(song_header_ptr);
song_table.track_group_numbers.Add(track_group_numbers);
}
return(song_table);
}
static void extract_song_data(byte[] file_contents, SongTable song_table, int patch_file_song_header_address, string game_name, int first_song_index)
{
// Extract song headers
// Extract instrument data
// Make sure all pointers are set correctly
// Extract tracks
// Combine everything into a single binary file for final patching
var song_number = first_song_index;
foreach (var song_header_ptr in song_table.song_header_ptrs)
{
try
{
System.Console.WriteLine("Extracting song header at address " + (song_header_ptr).ToString("X"));
var song_header = extract_song_header(file_contents, song_header_ptr);
var song_header_size = 4 * song_header.number_of_tracks + 8;
if (song_header.number_of_tracks == 0)
{
song_number += 1;
continue;
}
var used_instruments = new Dictionary <int, int>();
used_instruments[USED_INSTRUMENTS_MAX] = -1;
foreach (var track_data_ptr in song_header.track_data_ptrs)
{
extract_used_instruments(file_contents, track_data_ptr, used_instruments);
}
// Instruments start directly after the song header, which is at patch_file_song_header_address + song_header_size
var raw_instrument_data = extract_instrument_data(file_contents, song_header, patch_file_song_header_address + song_header_size, used_instruments);
song_header.instrument_def_ptr = (patch_file_song_header_address + song_header_size) + ROM_ADDRESS_MASK;
var raw_instrument_data_size = raw_instrument_data.Count;
var track_starting_offsets = new Dictionary <string, List <int> >();
var raw_track_data = extract_track_data(
file_contents,
song_header,
patch_file_song_header_address + song_header_size + raw_instrument_data_size,
song_header_size + raw_instrument_data_size,
track_starting_offsets
);
ensure_dir(System.IO.Path.Combine(DIRECTORY_OUT, game_name, "dummy.abc"));
var folder_path = System.IO.Path.Combine(DIRECTORY_OUT, game_name);
List <byte> all_patch_bytes = new List <byte>();
List <byte> raw_song_header = new List <byte>();
write_song_header_to_bytearray(song_header, raw_song_header);
all_patch_bytes.AddRange(raw_song_header);
all_patch_bytes.AddRange(raw_instrument_data);
all_patch_bytes.AddRange(raw_track_data);
System.IO.File.WriteAllBytes(System.IO.Path.Combine(folder_path, song_number.ToString() + FILE_EXTENSION_PATCH), all_patch_bytes.ToArray());
using (System.IO.StreamWriter out_file = new System.IO.StreamWriter(System.IO.Path.Combine(folder_path, song_number.ToString() + FILE_EXTENSION_TRANSPOSE)))
{
foreach (var ptr in track_starting_offsets[KEY_TRANSPOSE])
{
out_file.WriteLine(ptr.ToString());
}
}
using (System.IO.StreamWriter out_file = new System.IO.StreamWriter(System.IO.Path.Combine(folder_path, song_number.ToString() + FILE_EXTENSION_TEMPO)))
{
foreach (var ptr in track_starting_offsets[KEY_TEMPO])
{
out_file.WriteLine(ptr.ToString());
}
}
}
catch
{
// If we catch an exception, we're probably already in an invalid memory region.
return;
}
song_number += 1;
}
}
