private void InsertDefaultLanguages(IDbConnection conn, IDbTransaction tran)
{
var profiles = GetLanguageProfiles(conn, tran);
var languageConverter = new EmbeddedDocumentConverter(new LanguageIntConverter());
foreach (var profile in profiles.OrderBy(p => p.Id))
{
using (IDbCommand insertNewLanguageProfileCmd = conn.CreateCommand())
{
var itemsJson = languageConverter.ToDB(profile.Languages);
insertNewLanguageProfileCmd.Transaction = tran;
insertNewLanguageProfileCmd.CommandText = "INSERT INTO LanguageProfiles (Id, Name, Cutoff, Languages) VALUES (?, ?, ?, ?)";
insertNewLanguageProfileCmd.AddParameter(profile.Id);
insertNewLanguageProfileCmd.AddParameter(profile.Name);
insertNewLanguageProfileCmd.AddParameter(profile.Cutoff.Id);
insertNewLanguageProfileCmd.AddParameter(itemsJson);
insertNewLanguageProfileCmd.ExecuteNonQuery();
}
using (IDbCommand updateSeriesCmd = conn.CreateCommand())
{
foreach (var profileId in profile.ProfileIds)
{
updateSeriesCmd.Transaction = tran;
updateSeriesCmd.CommandText = "UPDATE Series SET LanguageProfileId = ? WHERE ProfileId = ?";
updateSeriesCmd.AddParameter(profile.Id);
updateSeriesCmd.AddParameter(profileId);
updateSeriesCmd.ExecuteNonQuery();
}
}
}
}
private static void RegisterEmbeddedConverter()
{
var embeddedTypes = typeof(IEmbeddedDocument).Assembly.ImplementationsOf <IEmbeddedDocument>();
var embeddedConvertor = new EmbeddedDocumentConverter();
var genericListDefinition = typeof(List <>).GetGenericTypeDefinition();
foreach (var embeddedType in embeddedTypes)
{
var embeddedListType = genericListDefinition.MakeGenericType(embeddedType);
MapRepository.Instance.RegisterTypeConverter(embeddedType, embeddedConvertor);
MapRepository.Instance.RegisterTypeConverter(embeddedListType, embeddedConvertor);
}
}
private static void RegisterEmbeddedConverter()
{
var embeddedTypes = typeof(IEmbeddedDocument).Assembly.GetTypes()
.Where(c => c.GetInterfaces().Any(i => i == typeof(IEmbeddedDocument)));
var embeddedConvertor = new EmbeddedDocumentConverter();
var genericListDefinition = typeof(List <>).GetGenericTypeDefinition();
foreach (var embeddedType in embeddedTypes)
{
var embeddedListType = genericListDefinition.MakeGenericType(embeddedType);
MapRepository.Instance.RegisterTypeConverter(embeddedType, embeddedConvertor);
MapRepository.Instance.RegisterTypeConverter(embeddedListType, embeddedConvertor);
}
}
private void ConvertQualityModel(IDbConnection conn, IDbTransaction tran, string tableName)
{
var qualityModelConverter = new EmbeddedDocumentConverter(new QualityIntConverter());
using (IDbCommand qualityModelCmd = conn.CreateCommand())
{
qualityModelCmd.Transaction = tran;
qualityModelCmd.CommandText = @"SELECT Distinct Quality FROM " + tableName;
using (IDataReader qualityModelReader = qualityModelCmd.ExecuteReader())
{
while (qualityModelReader.Read())
{
var qualityJson = qualityModelReader.GetString(0);
SourceQualityModel036 sourceQuality;
if (!Json.TryDeserialize <SourceQualityModel036>(qualityJson, out sourceQuality))
{
continue;
}
var qualityNewJson = qualityModelConverter.ToDB(new DestinationQualityModel036
{
Quality = sourceQuality.Quality.Id,
Proper = sourceQuality.Proper
});
using (IDbCommand updateCmd = conn.CreateCommand())
{
updateCmd.Transaction = tran;
updateCmd.CommandText = "UPDATE " + tableName + " SET Quality = ? WHERE Quality = ?";
updateCmd.AddParameter(qualityNewJson);
updateCmd.AddParameter(qualityJson);
updateCmd.ExecuteNonQuery();
}
}
}
}
}
private void ConvertQualityProfiles(IDbConnection conn, IDbTransaction tran)
{
var qualityProfileItemConverter = new EmbeddedDocumentConverter(new QualityIntConverter());
// Convert 'Allowed' column in QualityProfiles from Json List<object> to Json List<int> (int = Quality)
using (IDbCommand qualityProfileCmd = conn.CreateCommand())
{
qualityProfileCmd.Transaction = tran;
qualityProfileCmd.CommandText = @"SELECT Id, Allowed FROM QualityProfiles";
using (IDataReader qualityProfileReader = qualityProfileCmd.ExecuteReader())
{
while (qualityProfileReader.Read())
{
var id = qualityProfileReader.GetInt32(0);
var allowedJson = qualityProfileReader.GetString(1);
var allowed = Json.Deserialize <List <Quality> >(allowedJson);
var items = Quality.DefaultQualityDefinitions.OrderBy(v => v.Weight).Select(v => new ProfileQualityItem {
Quality = v.Quality, Allowed = allowed.Contains(v.Quality)
}).ToList();
var allowedNewJson = qualityProfileItemConverter.ToDB(items);
using (IDbCommand updateCmd = conn.CreateCommand())
{
updateCmd.Transaction = tran;
updateCmd.CommandText = "UPDATE QualityProfiles SET Items = ? WHERE Id = ?";
updateCmd.AddParameter(allowedNewJson);
updateCmd.AddParameter(id);
updateCmd.ExecuteNonQuery();
}
}
}
}
}
private void InsertDefaultQualityProfiles(IDbConnection conn, IDbTransaction tran)
{
var profiles = GetDefaultQualityProfiles(conn);
var formatItemConverter = new EmbeddedDocumentConverter <List <ProfileFormatItem180> >(new CustomFormatIntConverter());
var profileItemConverter = new EmbeddedDocumentConverter <List <QualityProfileItem111> >(new QualityIntConverter());
var profileId = 1;
foreach (var profile in profiles.OrderBy(p => p.Id))
{
using (IDbCommand insertNewLanguageProfileCmd = conn.CreateCommand())
{
insertNewLanguageProfileCmd.Transaction = tran;
insertNewLanguageProfileCmd.CommandText = "INSERT INTO Profiles (Id, Name, Cutoff, Items, Language, FormatItems, MinFormatScore, CutoffFormatScore, UpgradeAllowed) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
insertNewLanguageProfileCmd.AddParameter(profileId);
insertNewLanguageProfileCmd.AddParameter(profile.Name);
insertNewLanguageProfileCmd.AddParameter(profile.Cutoff);
var paramItems = insertNewLanguageProfileCmd.CreateParameter();
profileItemConverter.SetValue(paramItems, profile.Items);
insertNewLanguageProfileCmd.Parameters.Add(paramItems);
insertNewLanguageProfileCmd.AddParameter(profile.Language.Id);
var paramFormats = insertNewLanguageProfileCmd.CreateParameter();
formatItemConverter.SetValue(paramFormats, profile.FormatItems);
insertNewLanguageProfileCmd.Parameters.Add(paramFormats);
insertNewLanguageProfileCmd.AddParameter(profile.MinFormatScore);
insertNewLanguageProfileCmd.AddParameter(profile.CutoffFormatScore);
insertNewLanguageProfileCmd.AddParameter(profile.UpgradeAllowed);
insertNewLanguageProfileCmd.ExecuteNonQuery();
}
profileId += 1;
}
}
private void UpdateLanguage(IDbConnection conn, IDbTransaction tran)
{
var languageConverter = new EmbeddedDocumentConverter <List <Language> >(new LanguageIntConverter());
var profileLanguages = new Dictionary <int, int>();
using (IDbCommand getProfileCmd = conn.CreateCommand())
{
getProfileCmd.Transaction = tran;
getProfileCmd.CommandText = "SELECT Id, Language FROM Profiles";
IDataReader profilesReader = getProfileCmd.ExecuteReader();
while (profilesReader.Read())
{
var profileId = profilesReader.GetInt32(0);
var movieLanguage = Language.English.Id;
try
{
movieLanguage = profilesReader.GetInt32(1) != -1 ? profilesReader.GetInt32(1) : 1;
}
catch (InvalidCastException e)
{
_logger.Debug("Language field not found in Profiles, using English as default." + e.Message);
}
profileLanguages[profileId] = movieLanguage;
}
}
var movieLanguages = new Dictionary <int, int>();
using (IDbCommand getSeriesCmd = conn.CreateCommand())
{
getSeriesCmd.Transaction = tran;
getSeriesCmd.CommandText = @"SELECT Id, ProfileId FROM Movies";
using (IDataReader moviesReader = getSeriesCmd.ExecuteReader())
{
while (moviesReader.Read())
{
var movieId = moviesReader.GetInt32(0);
var movieProfileId = moviesReader.GetInt32(1);
movieLanguages[movieId] = profileLanguages.GetValueOrDefault(movieProfileId, Language.English.Id);
}
}
}
var movieFileLanguages = new Dictionary <int, List <Language> >();
var releaseLanguages = new Dictionary <string, List <Language> >();
using (IDbCommand getSeriesCmd = conn.CreateCommand())
{
getSeriesCmd.Transaction = tran;
getSeriesCmd.CommandText = @"SELECT Id, MovieId, SceneName, MediaInfo FROM MovieFiles";
using (IDataReader movieFilesReader = getSeriesCmd.ExecuteReader())
{
while (movieFilesReader.Read())
{
var movieFileId = movieFilesReader.GetInt32(0);
var movieId = movieFilesReader.GetInt32(1);
var movieFileSceneName = movieFilesReader.IsDBNull(2) ? null : movieFilesReader.GetString(2);
var movieFileMediaInfo = movieFilesReader.IsDBNull(3) ? null : Json.Deserialize <MediaInfo154>(movieFilesReader.GetString(3));
var languages = new List <Language>();
if (movieFileMediaInfo != null && movieFileMediaInfo.AudioLanguages.IsNotNullOrWhiteSpace())
{
var mediaInfolanguages = movieFileMediaInfo.AudioLanguages.Split('/').Select(l => l.Trim()).Distinct().ToList();
foreach (var audioLanguage in mediaInfolanguages)
{
var language = IsoLanguages.FindByName(audioLanguage)?.Language;
languages.AddIfNotNull(language);
}
}
if (!languages.Any(l => l.Id != 0) && movieFileSceneName.IsNotNullOrWhiteSpace())
{
languages = LanguageParser.ParseLanguages(movieFileSceneName);
}
if (!languages.Any(l => l.Id != 0))
{
languages = new List <Language> {
Language.FindById(movieLanguages[movieId])
};
}
if (movieFileSceneName.IsNotNullOrWhiteSpace())
{
// Store languages for this scenerelease so we can use in history later
releaseLanguages[movieFileSceneName] = languages;
}
movieFileLanguages[movieFileId] = languages;
}
}
}
var historyLanguages = new Dictionary <int, List <Language> >();
using (IDbCommand getSeriesCmd = conn.CreateCommand())
{
getSeriesCmd.Transaction = tran;
getSeriesCmd.CommandText = @"SELECT Id, SourceTitle, MovieId FROM History";
using (IDataReader historyReader = getSeriesCmd.ExecuteReader())
{
while (historyReader.Read())
{
var historyId = historyReader.GetInt32(0);
var historySourceTitle = historyReader.IsDBNull(1) ? null : historyReader.GetString(1);
var movieId = historyReader.GetInt32(2);
var languages = new List <Language>();
if (historySourceTitle.IsNotNullOrWhiteSpace() && releaseLanguages.ContainsKey(historySourceTitle))
{
languages = releaseLanguages[historySourceTitle];
}
if (!languages.Any(l => l.Id != 0) && historySourceTitle.IsNotNullOrWhiteSpace())
{
languages = LanguageParser.ParseLanguages(historySourceTitle);
}
if (!languages.Any(l => l.Id != 0))
{
languages = new List <Language> {
Language.FindById(movieLanguages[movieId])
};
}
historyLanguages[historyId] = languages;
}
}
}
var blacklistLanguages = new Dictionary <int, List <Language> >();
using (IDbCommand getSeriesCmd = conn.CreateCommand())
{
getSeriesCmd.Transaction = tran;
getSeriesCmd.CommandText = @"SELECT Id, SourceTitle, MovieId FROM Blacklist";
using (IDataReader blacklistReader = getSeriesCmd.ExecuteReader())
{
while (blacklistReader.Read())
{
var blacklistId = blacklistReader.GetInt32(0);
var blacklistSourceTitle = blacklistReader.IsDBNull(1) ? null : blacklistReader.GetString(1);
var movieId = blacklistReader.GetInt32(2);
var languages = new List <Language>();
if (blacklistSourceTitle.IsNotNullOrWhiteSpace())
{
languages = LanguageParser.ParseLanguages(blacklistSourceTitle);
}
if (!languages.Any(l => l.Id != 0))
{
languages = new List <Language> {
Language.FindById(movieLanguages[movieId])
};
}
blacklistLanguages[blacklistId] = languages;
}
}
}
foreach (var group in movieFileLanguages.GroupBy(v => v.Value, v => v.Key))
{
var languages = group.Key;
var movieFileIds = group.Select(v => v.ToString()).Join(",");
using (IDbCommand updateMovieFilesCmd = conn.CreateCommand())
{
updateMovieFilesCmd.Transaction = tran;
updateMovieFilesCmd.CommandText = $"UPDATE MovieFiles SET Languages = ? WHERE Id IN ({movieFileIds})";
var param = updateMovieFilesCmd.CreateParameter();
languageConverter.SetValue(param, languages);
updateMovieFilesCmd.Parameters.Add(param);
updateMovieFilesCmd.ExecuteNonQuery();
}
}
foreach (var group in historyLanguages.GroupBy(v => v.Value, v => v.Key))
{
var languages = group.Key;
var historyIds = group.Select(v => v.ToString()).Join(",");
using (IDbCommand updateHistoryCmd = conn.CreateCommand())
{
updateHistoryCmd.Transaction = tran;
updateHistoryCmd.CommandText = $"UPDATE History SET Languages = ? WHERE Id IN ({historyIds})";
var param = updateHistoryCmd.CreateParameter();
languageConverter.SetValue(param, languages);
updateHistoryCmd.Parameters.Add(param);
updateHistoryCmd.ExecuteNonQuery();
}
}
foreach (var group in blacklistLanguages.GroupBy(v => v.Value, v => v.Key))
{
var languages = group.Key;
var blacklistIds = group.Select(v => v.ToString()).Join(",");
using (IDbCommand updateBlacklistCmd = conn.CreateCommand())
{
updateBlacklistCmd.Transaction = tran;
updateBlacklistCmd.CommandText = $"UPDATE Blacklist SET Languages = ? WHERE Id IN ({blacklistIds})";
var param = updateBlacklistCmd.CreateParameter();
languageConverter.SetValue(param, languages);
updateBlacklistCmd.Parameters.Add(param);
updateBlacklistCmd.ExecuteNonQuery();
}
}
}
private void UpdateLanguage(IDbConnection conn, IDbTransaction tran)
{
var LanguageConverter = new EmbeddedDocumentConverter(new LanguageIntConverter());
var profileLanguages = new Dictionary <int, int>();
using (IDbCommand getProfileCmd = conn.CreateCommand())
{
getProfileCmd.Transaction = tran;
getProfileCmd.CommandText = "SELECT Id, Language FROM Profiles";
IDataReader profilesReader = getProfileCmd.ExecuteReader();
while (profilesReader.Read())
{
var profileId = profilesReader.GetInt32(0);
var episodeLanguage = Language.English.Id;
try
{
episodeLanguage = profilesReader.GetInt32(1);
}
catch (InvalidCastException e)
{
_logger.Debug("Language field not found in Profiles, using English as default." + e.Message);
}
profileLanguages[profileId] = episodeLanguage;
}
}
var seriesLanguages = new Dictionary <int, int>();
using (IDbCommand getSeriesCmd = conn.CreateCommand())
{
getSeriesCmd.Transaction = tran;
getSeriesCmd.CommandText = @"SELECT Id, ProfileId FROM Series";
using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
{
while (seriesReader.Read())
{
var seriesId = seriesReader.GetInt32(0);
var seriesProfileId = seriesReader.GetInt32(1);
seriesLanguages[seriesId] = profileLanguages.GetValueOrDefault(seriesProfileId, Language.English.Id);
}
}
}
foreach (var group in seriesLanguages.GroupBy(v => v.Value, v => v.Key))
{
var languageJson = LanguageConverter.ToDB(Language.FindById(group.Key));
var seriesIds = group.Select(v => v.ToString()).Join(",");
using (IDbCommand updateEpisodeFilesCmd = conn.CreateCommand())
{
updateEpisodeFilesCmd.Transaction = tran;
updateEpisodeFilesCmd.CommandText = $"UPDATE EpisodeFiles SET Language = ? WHERE SeriesId IN ({seriesIds})";
updateEpisodeFilesCmd.AddParameter(languageJson);
updateEpisodeFilesCmd.ExecuteNonQuery();
}
using (IDbCommand updateHistoryCmd = conn.CreateCommand())
{
updateHistoryCmd.Transaction = tran;
updateHistoryCmd.CommandText = $"UPDATE History SET Language = ? WHERE SeriesId IN ({seriesIds})";
updateHistoryCmd.AddParameter(languageJson);
updateHistoryCmd.ExecuteNonQuery();
}
using (IDbCommand updateBlacklistCmd = conn.CreateCommand())
{
updateBlacklistCmd.Transaction = tran;
updateBlacklistCmd.CommandText = $"UPDATE Blacklist SET Language = ? WHERE SeriesId IN ({seriesIds})";
updateBlacklistCmd.AddParameter(languageJson);
updateBlacklistCmd.ExecuteNonQuery();
}
}
}
