/**
* Items that appeared only in OLD Table are marked as REMOVED
*/
protected virtual void CheckRemovedItems(IndexColumnMapping idColumn,
IEnumerable <IndexColumnMapping> primaryColumns,
IEnumerable <IndexColumnMapping> valueColumns)
{
var limit = 100;
var offset = 0;
var indexer = GetIndexModel();
string comparePrimarySQL = $@"o.[Id] = n.[Id]";
if (primaryColumns?.Count() > 0)
{
comparePrimarySQL = string.Join(" AND ", primaryColumns
.Select(c => $@"o.[{c.MappingName}] = n.[{c.MappingName}]")
.Union(new List <string> {
$"o.[Id] = n.[Id]"
}));
}
var oldItemsSQL = $@"
SELECT o.*
FROM [{indexer.OldValueTableName}] AS o
LEFT JOIN [{indexer.NewValueTableName}] AS n ON {comparePrimarySQL}
WHERE n.[Id] IS NULL
ORDER BY o.[Id]
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY;
";
var updateRemovedSQL = $@"
UPDATE [{indexer.ValueTableName}]
SET [State] = CASE
WHEN [State] = 0 THEN @State
WHEN [State] IS NULL THEN @State
ELSE [State] | @State
END,
[LastUpdated] = @LastUpdated
WHERE [SourceId] IN @SourceIds";
var affectedRows = 0;
while (true)
{
var oldItems = Connection
.Query(oldItemsSQL, new { Limit = limit, Offset = offset }, transaction: Transaction)
.Select(i => i.Id).ToList();
if (oldItems.Count <= 0)
{
break;;
}
affectedRows += Connection.Execute(updateRemovedSQL, new
{
State = ItemState.Removed | ItemState.Changed,
SourceIds = oldItems,
LastUpdated = DateTime.Now.ToUnixTimestamp(),
}, transaction: Transaction);
offset += limit;
}
Report($@"Found {affectedRows} item(s) that are removed.");
}
/**
*
*
*/
protected virtual void Insert(IndexColumnMapping idColumn,
IEnumerable <IndexColumnMapping> primaryColumns,
IEnumerable <IndexColumnMapping> valueColumns,
IEnumerable <IndexColumnMapping> columnsNotId,
IEnumerable <object> data,
string tmpTable)
{
var schemaSQL = string.Join(", ", columnsNotId.Select(c => $"[{c.MappingName}]"));
var paramsSQL = string.Join(", ", columnsNotId.Select(c => $"@{c.MappingName}"));
var insertValueSQL = string.Join(", ", columnsNotId.Select(c => $"s.[{c.MappingName}]"));
var indexer = GetIndexModel();
var createColumns = columnsNotId.Union(new List <IndexColumnMapping> {
idColumn
}).Select(p => p.MappingName).ToArray();
using (var bcp = new SqlBulkCopy(Connection as SqlConnection, SqlBulkCopyOptions.TableLock, Transaction as SqlTransaction))
using (var tbl = data.ToDataTable())
{
bcp.DestinationTableName = tmpTable;
bcp.WriteToServer(tbl);
var mergeCondition = primaryColumns == null || primaryColumns.Count() <= 0
? $"s.[Id] = t.[Id]"
: string.Join(" AND ", primaryColumns.Select(c => $"s.[{c.MappingName}] = t.[{c.MappingName}]").Union(new List <string> {
$"s.[Id] = t.[Id]"
}));
var mappingColumns = columnsNotId.Union(new List <IndexColumnMapping> {
idColumn
});
var sourceColumns = mappingColumns.Select(c => $"[{c.SourceName}] AS [{c.MappingName}]");
var mapColumns = mappingColumns.Select(c => $"[{c.MappingName}]");
var sql = $@"
MERGE INTO [{indexer.NewValueTableName}] as t
USING (
SELECT {string.Join(",\n", sourceColumns)}
FROM {tmpTable}
)
AS s({string.Join(",\n", mapColumns)})
ON {mergeCondition}
WHEN NOT MATCHED THEN
INSERT ([Id], {schemaSQL})
VALUES(s.[Id], {insertValueSQL});
";
var affectedRows = Connection.Execute(sql, transaction: Transaction);
Report($"Inserted {affectedRows} to {indexer.NewValueTableName}");
}
}
/**
* Items that are both in NEW & OLD tables
* but with different lookup values (not primary keys)
* are marked as UPDATED
*/
protected virtual void CheckChangedItems(IndexColumnMapping idColumn,
IEnumerable <IndexColumnMapping> primaryColumns,
IEnumerable <IndexColumnMapping> valueColumns,
IEnumerable <IndexColumnMapping> columnsNotId)
{
if (valueColumns == null || valueColumns.Count() <= 0)
{
// No value columns means no different check
return;
}
var indexer = GetIndexModel();
string comparePrimarySQL = $@"o.[Id] = n.[{idColumn.SourceName}]";
if (primaryColumns?.Count() > 0)
{
comparePrimarySQL = string.Join(" AND ", primaryColumns
.Select(c => $@"o.[{c.MappingName}] = n.[{c.SourceName}]")
.Union(new List <string> {
$"o.[Id] = n.[{idColumn.SourceName}]"
}));
}
var compareValueSQL = string.Join(" OR ", valueColumns.Select(c => $@"o.[{c.MappingName}] <> n.[{c.SourceName}]"));
var mergeCondition = $@"s.[Id] = t.[SourceId]";
if (primaryColumns?.Count() > 0)
{
mergeCondition = string.Join(" AND ", primaryColumns.Select(c => $@"s.[{c}] = t.[{c.MappingName}]").Union(new List <string> {
$"s.[Id] = t.[SourceId]"
}));
}
var mappingColumns = columnsNotId.Union(new List <IndexColumnMapping> {
idColumn
});
var sourceColumns = mappingColumns.Select(c => $"n.[{c.SourceName}] AS [{c.MappingName}]");
var mergeSQL = $@"
MERGE INTO [{indexer.ValueTableName}] as t
USING (
SELECT {string.Join(", ", sourceColumns)}
FROM {indexer.NewValueTableName}_tmp n
LEFT JOIN [{indexer.OldValueTableName}] AS o ON {comparePrimarySQL}
LEFT JOIN [{indexer.ValueTableName}] AS v ON v.SourceId = o.Id
WHERE o.[Id] IS NOT NULL AND (
({compareValueSQL}) -- Some values is not matched
OR (v.[State] IS NOT NULL AND (v.[State] & {(int)ItemState.Removed}) > 0) -- Item was marked as 'Removed' but now it is back again
)
)
AS s
ON {mergeCondition}
WHEN MATCHED THEN
UPDATE SET
[State] = CASE
WHEN [State] = 0 THEN @State
WHEN [State] IS NULL THEN @State
ELSE ([State] | @State | @StatesToExclude) ^ @StatesToExclude
END,
[LastUpdated] = @LastUpdated,
{string.Join(",\n", valueColumns.Select(c => $"[{c.MappingName}] = s.[{c.MappingName}]"))};
";
var affectedRows = Connection.Execute(mergeSQL, param: new
{
State = ItemState.Changed,
StatesToExclude = ItemState.Removed,
LastUpdated = DateTime.Now.ToUnixTimestamp()
}, transaction: Transaction);
Report($@"Found {affectedRows} updated item(s)");
}
/**
* Items that are in NEW table
* but not in OLD table are
* marked as CREATED
*/
protected virtual void CheckNewItems(IndexColumnMapping idColumn,
IEnumerable <IndexColumnMapping> primaryColumns,
IEnumerable <IndexColumnMapping> valueColumns,
IEnumerable <IndexColumnMapping> columnsNotId)
{
var indexer = GetIndexModel();
var comparePrimarySQL = $@"o.[Id] = n.[{idColumn.SourceName}]";
if (primaryColumns?.Count() > 0)
{
comparePrimarySQL = string.Join(" AND ", primaryColumns
.Select(c => $@"o.[{c.MappingName}] = n.[{c.SourceName}]")
.Union(new List <string> {
$"o.[Id] = n.[{idColumn.SourceName}]"
}));
}
var mergeCondition = $@"s.[Id] = t.[SourceId]"; // column Id in NewTable is [SourceId] in ValueTable
if (primaryColumns?.Count() > 0) // Both tables have same Primary Columns and Value Columns
{
mergeCondition = string.Join(" AND ", primaryColumns
.Select(c => $@"s.[{c.MappingName}] = t.[{c.MappingName}]")
.Union(new List <string> {
$"s.[Id] = t.[SourceId]"
}));
}
var mappingColumns = columnsNotId.Union(new List <IndexColumnMapping> {
idColumn
});
var sourceColumns = mappingColumns.Select(c => $"n.[{c.SourceName}] AS [{c.MappingName}]");
var mergeSQL = $@"
MERGE INTO [{indexer.ValueTableName}] as t
USING (
SELECT {string.Join(", ", sourceColumns)}
FROM {indexer.NewValueTableName}_tmp n
LEFT JOIN [{indexer.OldValueTableName}] AS o ON {comparePrimarySQL}
WHERE o.[Id] IS NULL
)
AS s
ON {mergeCondition}
WHEN NOT MATCHED THEN
INSERT (
[SourceId],
[DestinationId],
[State],
[LastUpdated],
{string.Join(",\n", columnsNotId.Select(c => $"[{c.MappingName}]"))}
)
VALUES(
s.[Id],
NULL,
@State,
@LastUpdated,
{string.Join(",\n", columnsNotId.Select(c => $"s.[{c.MappingName}]"))}
)
WHEN MATCHED AND ([State] & {(int)ItemState.Removed}) > 0 THEN
UPDATE SET
[State] = @State,
[LastUpdated] = @LastUpdated,
{string.Join(",\n", columnsNotId.Select(c => $"[{c.MappingName}] = s.[{c.MappingName}]"))};
";
var affectedRows = Connection.Execute(mergeSQL, param: new {
State = ItemState.Changed,
LastUpdated = DateTime.Now.ToUnixTimestamp()
}, transaction: Transaction);
Report($@"Found {affectedRows} new item(s)");
}
