public async Task SetGuildPrefixAsync(IGuild guild, string prefix)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordGuildId == guild.Id);
if (existingGuild == null)
{
var newGuild = new Guild
{
DiscordGuildId = guild.Id,
TitlesEnabled = true,
ChartTimePeriod = ChartTimePeriod.Monthly,
FmEmbedType = FmEmbedType.embedmini,
Name = guild.Name,
Prefix = prefix
};
await db.Guilds.AddAsync(newGuild);
await db.SaveChangesAsync();
}
else
{
existingGuild.Prefix = prefix;
existingGuild.Name = guild.Name;
db.Entry(existingGuild).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
// Set LastFM Name
public async Task ResetChartTimerAsync(User user)
{
user.LastGeneratedChartDateTimeUtc = DateTime.Now;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync().ConfigureAwait(false);
}
// Remove user
public async Task DeleteUser(int userID)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
try
{
var user = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.UserId == userID);
this._cache.Remove($"user-settings-{user.DiscordUserId}");
this._cache.Remove($"user-isRegistered-{user.DiscordUserId}");
await using var connection = new NpgsqlConnection(ConfigData.Data.Database.ConnectionString);
connection.Open();
await using var deleteArtists = new NpgsqlCommand($"DELETE FROM public.user_artists WHERE user_id = {user.UserId};", connection);
await deleteArtists.ExecuteNonQueryAsync();
await using var deleteAlbums = new NpgsqlCommand($"DELETE FROM public.user_albums WHERE user_id = {user.UserId};", connection);
await deleteAlbums.ExecuteNonQueryAsync();
await using var deleteTracks = new NpgsqlCommand($"DELETE FROM public.user_tracks WHERE user_id = {user.UserId};", connection);
await deleteTracks.ExecuteNonQueryAsync();
db.Users.Remove(user);
await db.SaveChangesAsync();
}
catch (Exception e)
{
Log.Error(e, "Error while deleting user!");
throw;
}
}
// Random user
public async Task <string> GetRandomLastFMUserAsync()
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var featuredUser = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.Featured == true);
if (featuredUser != null)
{
featuredUser.Featured = false;
db.Entry(featuredUser).State = EntityState.Modified;
}
var users = db.Users
.AsQueryable()
.Where(w => w.Blacklisted != true).ToList();
var rand = new Random();
var user = users[rand.Next(users.Count)];
user.Featured = true;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync();
return(user.UserNameLastFM);
}
public async Task AddLastFMFriendAsync(ulong discordSenderId, string lastfmusername, int?discordFriendId)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = db.Users.FirstOrDefault(f => f.DiscordUserId == discordSenderId);
if (user == null)
{
var newUser = new User
{
DiscordUserId = discordSenderId,
UserType = UserType.User
};
await db.Users.AddAsync(newUser);
user = newUser;
}
var friend = new Friend
{
User = user,
LastFMUserName = lastfmusername,
FriendUserId = discordFriendId
};
await db.Friends.AddAsync(friend);
await db.SaveChangesAsync();
await Task.CompletedTask;
}
public async Task <string[]> AddDisabledCommandAsync(IGuild guild, string command)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordGuildId == guild.Id);
if (existingGuild == null)
{
var newGuild = new Guild
{
DiscordGuildId = guild.Id,
TitlesEnabled = true,
ChartTimePeriod = ChartTimePeriod.Monthly,
FmEmbedType = FmEmbedType.embedmini,
Name = guild.Name,
DisabledCommands = new[] { command }
};
await db.Guilds.AddAsync(newGuild);
await db.SaveChangesAsync();
return(newGuild.DisabledCommands);
}
else
{
if (existingGuild.DisabledCommands != null && existingGuild.DisabledCommands.Length > 0)
{
var newDisabledCommands = existingGuild.DisabledCommands;
Array.Resize(ref newDisabledCommands, newDisabledCommands.Length + 1);
newDisabledCommands[^ 1] = command;
public async Task AddUserToGuild(IGuild guild, User user)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.Include(i => i.GuildUsers)
.FirstAsync(f => f.DiscordGuildId == guild.Id);
if (existingGuild == null)
{
var newGuild = new Guild
{
DiscordGuildId = guild.Id,
ChartTimePeriod = ChartTimePeriod.Monthly,
FmEmbedType = FmEmbedType.embedmini,
Name = guild.Name,
TitlesEnabled = true,
};
await db.Guilds.AddAsync(newGuild);
await db.SaveChangesAsync();
Log.Information("Added guild {guildName} to database", guild.Name);
var guildId = db.Guilds.First(f => f.DiscordGuildId == guild.Id).GuildId;
await db.GuildUsers.AddAsync(new GuildUser
{
GuildId = guildId,
UserId = user.UserId
});
Log.Information("Added user {userId} to guild {guildName}", user.UserId, guild.Name);
}
else if (!existingGuild.GuildUsers.Select(g => g.UserId).Contains(user.UserId))
{
await db.GuildUsers.AddAsync(new GuildUser
{
GuildId = existingGuild.GuildId,
UserId = user.UserId
});
Log.Information("Added user {userId} to guild {guildName}", user.UserId, guild.Name);
}
await db.SaveChangesAsync();
}
public async Task <bool> SetUserTypeAsync(string discordUserID, UserType userType)
{
User user = await db.Users.FirstOrDefaultAsync(f => f.DiscordUserID == discordUserID);
if (user == null)
{
return(false);
}
user.UserType = userType;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync();
return(true);
}
public async Task ResetChartTimerAsync(User user)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
user.LastGeneratedChartDateTimeUtc = DateTime.Now;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync();
}
// Remove user
public async Task DeleteUser(int userID)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = await db.Users
.AsQueryable()
.FirstOrDefaultAsync(f => f.UserId == userID);
db.Users.Remove(user);
await db.SaveChangesAsync();
}
public async Task ChangeGuildSettingAsync(IGuild guild, ChartTimePeriod chartTimePeriod, ChartType chartType)
{
string guildId = guild.Id.ToString();
Guild existingGuild = await db.Guilds.FirstOrDefaultAsync(f => f.DiscordGuildID == guildId).ConfigureAwait(false);
if (existingGuild == null)
{
Guild newGuild = new Guild
{
DiscordGuildID = guildId,
ChartTimePeriod = chartTimePeriod,
ChartType = chartType,
Name = guild.Name,
TitlesEnabled = true,
};
db.Guilds.Add(newGuild);
await db.SaveChangesAsync().ConfigureAwait(false);
}
}
public async Task RemoveAllFriendsAsync(int userId)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var friends = db.Friends
.AsQueryable()
.Where(f => f.UserId == userId).ToList();
if (friends.Count > 0)
{
db.Friends.RemoveRange(friends);
await db.SaveChangesAsync();
}
}
public async Task <string> GetOrStoreArtistImageAsync(ArtistResponse lastFmArtist, string artistNameBeforeCorrect)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var artist = await db.Artists
.AsQueryable()
.FirstOrDefaultAsync(f => f.Name.ToLower() == lastFmArtist.Artist.Name.ToLower());
var imageUrlToReturn = "";
if (artist == null)
{
var spotifyArtist = await GetArtistFromSpotify(lastFmArtist.Artist.Name);
var artistToAdd = new Artist
{
Name = lastFmArtist.Artist.Name,
LastFmUrl = lastFmArtist.Artist.Url,
Mbid = lastFmArtist.Artist.Mbid
};
if (spotifyArtist != null)
{
artistToAdd.SpotifyId = spotifyArtist.Id;
if (spotifyArtist.Images.Any())
{
artistToAdd.SpotifyImageUrl = spotifyArtist.Images.OrderByDescending(o => o.Height).First().Url;
artistToAdd.SpotifyImageDate = DateTime.UtcNow;
imageUrlToReturn = artistToAdd.SpotifyImageUrl;
}
}
if (!string.Equals(artistNameBeforeCorrect, lastFmArtist.Artist.Name, StringComparison.CurrentCultureIgnoreCase))
{
artistToAdd.Aliases = new[] { artistNameBeforeCorrect };
}
await db.Artists.AddAsync(artistToAdd);
await db.SaveChangesAsync();
}
else
{
if (!string.Equals(artistNameBeforeCorrect, lastFmArtist.Artist.Name, StringComparison.CurrentCultureIgnoreCase))
{
if (artist.Aliases != null && artist.Aliases.Length > 0)
{
var aliases = artist.Aliases;
Array.Resize(ref aliases, aliases.Length + 1);
aliases[^ 1] = artistNameBeforeCorrect;
public async Task <bool> AddUserToBlacklistAsync(ulong discordUserID)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var user = await db.Users.FirstOrDefaultAsync(f => f.DiscordUserId == discordUserID);
if (user == null)
{
return(false);
}
user.Blacklisted = true;
db.Entry(user).State = EntityState.Modified;
await db.SaveChangesAsync();
return(true);
}
public async Task <bool> RemoveLastFMFriendAsync(int userID, string lastfmusername)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var friend = db.Friends
.Include(i => i.FriendUser)
.FirstOrDefault(f => f.UserId == userID &&
(f.LastFMUserName.ToLower() == lastfmusername.ToLower() || f.FriendUser != null && f.FriendUser.UserNameLastFM.ToLower() == lastfmusername.ToLower()));
if (friend != null)
{
db.Friends.Remove(friend);
await db.SaveChangesAsync();
return(true);
}
else
{
return(false);
}
}
public async Task ChangeGuildSettingAsync(IGuild guild, ChartTimePeriod chartTimePeriod, FmEmbedType fmEmbedType)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var existingGuild = await db.Guilds
.AsQueryable()
.FirstOrDefaultAsync(f => f.DiscordGuildId == guild.Id);
if (existingGuild == null)
{
var newGuild = new Guild
{
DiscordGuildId = guild.Id,
ChartTimePeriod = chartTimePeriod,
FmEmbedType = fmEmbedType,
Name = guild.Name,
TitlesEnabled = true
};
db.Guilds.Add(newGuild);
await db.SaveChangesAsync();
}
}
public async Task <string> GetOrStoreArtistImageAsync(ArtistResponse lastFmArtist, string artistNameBeforeCorrect)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var dbArtist = await db.Artists
.AsQueryable()
.FirstOrDefaultAsync(f => f.Name.ToLower() == lastFmArtist.Artist.Name.ToLower());
var imageUrlToReturn = "";
try
{
if (dbArtist == null)
{
var spotifyArtist = await GetArtistFromSpotify(lastFmArtist.Artist.Name);
var artistToAdd = new Artist
{
Name = lastFmArtist.Artist.Name,
LastFmUrl = lastFmArtist.Artist.Url,
Mbid = lastFmArtist.Artist.Mbid
};
if (spotifyArtist != null)
{
artistToAdd.SpotifyId = spotifyArtist.Id;
if (spotifyArtist.Images.Any())
{
artistToAdd.SpotifyImageUrl = spotifyArtist.Images.OrderByDescending(o => o.Height).First().Url;
artistToAdd.SpotifyImageDate = DateTime.UtcNow;
imageUrlToReturn = artistToAdd.SpotifyImageUrl;
}
}
if (!string.Equals(artistNameBeforeCorrect, lastFmArtist.Artist.Name, StringComparison.CurrentCultureIgnoreCase))
{
artistToAdd.Aliases = new[] { artistNameBeforeCorrect };
}
await db.Artists.AddAsync(artistToAdd);
await db.SaveChangesAsync();
}
else
{
if (!string.Equals(artistNameBeforeCorrect, lastFmArtist.Artist.Name, StringComparison.CurrentCultureIgnoreCase))
{
AddAliasToExistingArtist(artistNameBeforeCorrect, dbArtist);
db.Entry(dbArtist).State = EntityState.Modified;
}
if (dbArtist.SpotifyImageUrl == null || dbArtist.SpotifyImageDate < DateTime.UtcNow.AddMonths(-2))
{
var spotifyArtist = await GetArtistFromSpotify(lastFmArtist.Artist.Name);
if (spotifyArtist != null && spotifyArtist.Images.Any())
{
dbArtist.SpotifyImageUrl = spotifyArtist.Images.OrderByDescending(o => o.Height).First().Url;
imageUrlToReturn = dbArtist.SpotifyImageUrl;
}
dbArtist.SpotifyImageDate = DateTime.UtcNow;
db.Entry(dbArtist).State = EntityState.Modified;
}
else
{
imageUrlToReturn = dbArtist.SpotifyImageUrl;
}
await db.SaveChangesAsync();
}
return(!string.IsNullOrEmpty(imageUrlToReturn) ? imageUrlToReturn : null);
}
catch (Exception e)
{
Log.Error(e, "Something went wrong while retrieving artist image");
return(null);
}
}
private static async Task Main()
{
Console.WriteLine("Welcome to the migration tool for migrating the .fmbot database tool from localdb to PostgreSQL \n" +
"This tool requires you to have ran .fmbot with PostgreSQL at least once. \n\n" +
"Options: \n" +
"1. Migrate all data to new db \n" +
"2. Migrate only users and friends \n" +
"3. Migrate only guilds \n" +
"4. Clear new database \n" +
"5. Fix key values (use this if you have issues inserting new records) \n" +
"6. Migrate artists aliases to seperate records \n" +
"Select an option to continue...");
var key = Console.ReadKey().Key.ToString();
var saveToDatabase = true;
if (key == "D1")
{
await CopyUsers();
await CopyGuilds();
}
else if (key == "D2")
{
await CopyUsers();
}
else if (key == "D3")
{
await CopyGuilds();
}
else if (key == "D4")
{
await ClearNewDatabase();
saveToDatabase = false;
}
else if (key == "D6")
{
await MigrateArtistsToRecords();
saveToDatabase = true;
}
else if (key == "D5")
{
await FixKeyValues();
await Task.Delay(10000);
Environment.Exit(1);
}
else
{
Console.WriteLine("\nKey not recognized");
await Task.Delay(10000);
Environment.Exit(1);
}
if (saveToDatabase)
{
Console.WriteLine($"Saving changes to database...");
try
{
await NewDatabaseContext.SaveChangesAsync();
}
catch (Exception e)
{
Console.WriteLine("Something went wrong!");
Console.WriteLine(e);
if (e.InnerException != null)
{
Console.WriteLine(e.InnerException);
}
await Task.Delay(10000);
throw;
}
}
await FixKeyValues();
Console.WriteLine("Done! Press any key to quit program.");
Console.ReadKey();
}
public async Task <Track> GetOrStoreTrackAsync(ResponseTrack track)
{
await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString);
var dbTrack = await db.Tracks
.AsQueryable()
.FirstOrDefaultAsync(f => f.Name.ToLower() == track.Name.ToLower() && f.ArtistName.ToLower() == track.Artist.Name.ToLower());
if (dbTrack == null)
{
var trackToAdd = new FMBot.Persistence.Domain.Models.Track
{
Name = track.Name,
AlbumName = track.Album?.Title,
ArtistName = track.Artist?.Name
};
var artist = await db.Artists
.AsQueryable()
.FirstOrDefaultAsync(f => f.Name.ToLower() == track.Artist.Name.ToLower());
if (artist != null)
{
trackToAdd.Artist = artist;
}
var spotifyTrack = await GetTrackFromSpotify(track.Name, track.Artist.Name.ToLower());
if (spotifyTrack != null)
{
trackToAdd.SpotifyId = spotifyTrack.Id;
trackToAdd.DurationMs = spotifyTrack.DurationMs;
var audioFeatures = await GetAudioFeaturesFromSpotify(spotifyTrack.Id);
if (audioFeatures != null)
{
trackToAdd.Key = audioFeatures.Key;
trackToAdd.Tempo = audioFeatures.Tempo;
}
}
trackToAdd.SpotifyLastUpdated = DateTime.UtcNow;
await db.Tracks.AddAsync(trackToAdd);
await db.SaveChangesAsync();
return(trackToAdd);
}
else
{
if (dbTrack.Artist == null)
{
var artist = await db.Artists
.AsQueryable()
.FirstOrDefaultAsync(f => f.Name.ToLower() == track.Artist.Name.ToLower());
if (artist != null)
{
dbTrack.Artist = artist;
db.Entry(dbTrack).State = EntityState.Modified;
}
}
if (string.IsNullOrEmpty(dbTrack.SpotifyId) && dbTrack.SpotifyLastUpdated < DateTime.UtcNow.AddMonths(-2))
{
var spotifyTrack = await GetTrackFromSpotify(track.Name, track.Artist.Name.ToLower());
if (spotifyTrack != null)
{
dbTrack.SpotifyId = spotifyTrack.Id;
dbTrack.DurationMs = spotifyTrack.DurationMs;
var audioFeatures = await GetAudioFeaturesFromSpotify(spotifyTrack.Id);
if (audioFeatures != null)
{
dbTrack.Key = audioFeatures.Key;
dbTrack.Tempo = audioFeatures.Tempo;
}
}
dbTrack.SpotifyLastUpdated = DateTime.UtcNow;
db.Entry(dbTrack).State = EntityState.Modified;
}
await db.SaveChangesAsync();
return(dbTrack);
}
}