private Differential SelectDifferential(long transactionId, string library = null)
{
string selectMaxKey = MediaQueryController.selectMaxKey;
string selectDeletedQuery = MediaQueryController.selectDeletedQuery;
string selectAddedQuery = MediaQueryController.selectAddedQuery;
string selectModifiedQuery = MediaQueryController.selectModifiedQuery;
QueryParameter parameters = new QueryParameter()
{
TransactionId = transactionId, Library = library
};
if (library != null)
{
selectAddedQuery += " AND MediaItem.LibraryName = @Library";
selectModifiedQuery += " AND MediaItem.LibraryName = @Library";
}
Differential result;
var connection = dapper.NewConnection();
connection.Open();
SqlMapper.AddTypeHandler(typeof(TimeSpan), new TimeSpanHandler());
SqlMapper.AddTypeHandler(typeof(DateTimeOffset), new DateTimeOffsetHandler());
using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
{
parameters.MaxLogKey = connection.QuerySingle <long>(selectMaxKey);
// Select items deleted.
var removed = connection.Query <Deletion>(selectDeletedQuery, parameters);
// Select distinct media items added but not deleted.
var added = connection.Query <VolyExports.MediaItem>(selectAddedQuery, parameters);
// Select distinct media items changed but not added or deleted.
var changed = connection.Query <VolyExports.MediaItem>(selectModifiedQuery, parameters);
result = new Differential()
{
CurrentKey = parameters.MaxLogKey,
Deletions = removed,
Additions = added,
Modifications = changed
};
transaction.Rollback(); // This was a read only transaction.
}
return(result);
}
public IActionResult GetPending()
{
using (var connection = dapper.NewConnection())
{
connection.Open();
SqlMapper.AddTypeHandler(typeof(TimeSpan), new TimeSpanHandler());
SqlMapper.AddTypeHandler(typeof(DateTimeOffset), new DateTimeOffsetHandler());
var added = connection.Query <VolyExports.MediaItem>(@"
SELECT
PendingDeletions.MediaId,
PendingDeletions.Version,
IndexName,
IndexHash,
LibraryName,
SeriesName,
ImdbId,
TmdbId,
TvdbId,
TvmazeId,
Name,
SeasonNumber,
EpisodeNumber,
AbsoluteEpisodeNumber,
CreateDate,
SourcePath,
SourceModified,
SourceHash,
Duration,
Metadata
FROM
PendingDeletions
LEFT JOIN
MediaItem ON PendingDeletions.MediaId = MediaItem.MediaId
");
return(new ObjectResult(Newtonsoft.Json.JsonConvert.SerializeObject(added)));
}
}