private DbCommand GetReplicateDataCommand(IEnumerable <Dictionary <string, string> > values, int[] attrIds)
{
var cmd = CreateDbCommand();
var ids = values.Select(n => Int32.Parse(n[SystemColumnNames.Id])).ToArray();
if (DatabaseType == DatabaseType.SqlServer)
{
cmd.CommandText = "qp_replicate_items";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.NVarChar, -1)
{
Value = string.Join(",", ids)
});
cmd.Parameters.Add(new SqlParameter("@attr_ids", SqlDbType.NVarChar, -1)
{
Value = string.Join(",", attrIds)
});
}
else
{
cmd.CommandText = attrIds.Any() ? "call qp_replicate_items(@ids, @attr_ids);" : "call qp_replicate_items(@ids);";
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseType));
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attr_ids", attrIds, DatabaseType));
}
return(cmd);
}
private DbCommand GetUpdateModifiedCommand(IEnumerable <int> existingIds, int[] newIds, int contentId)
{
var cmd = CreateDbCommand($@"
select content_item_id, modified from content_{contentId}_united {WithNoLock}
where content_item_id in (select id from {IdList()})
");
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", newIds.Union(existingIds), DatabaseType));
return(cmd);
}
private DataTable GetVersionDataValues(IEnumerable <int> versionIds, IEnumerable <int> attrIds)
{
var cmd = CreateDbCommand($@"
select attribute_id, content_item_version_id, data from version_content_data
where content_item_version_id in (select id from {IdList("@versionIds", "i")}) and attribute_id in (select id from {IdList("@attrIds", "a")})
");
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@versionIds", versionIds, DatabaseType));
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attrIds", attrIds, DatabaseType));
return(GetRealData(cmd));
}
private DbCommand GetVersionIdsToRemoveCommand(int[] ids, int maxNumber)
{
var cmd = CreateDbCommand($@" select content_item_version_id from
(
select content_item_id, content_item_version_id,
row_number() over(partition by civ.content_item_id order by civ.content_item_version_id desc) as num
from content_item_version civ
where content_item_id in (select id from {IdList()})
) c
where c.num >= @maxNumber");
cmd.Parameters.AddWithValue("@maxNumber", maxNumber);
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseType));
return(cmd);
}
private IEnumerable <int> GetAggregateVersionFunction(string function, int[] ids)
{
if (!ids.Any())
{
return(new int[0]);
}
using (var cmd = CreateDbCommand())
{
cmd.CommandText = $@"
select cast({function}(content_item_version_id) as int) as data
from content_item_version where content_item_id in (select id from {IdList()})
group by content_item_id
";
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseType));
return(GetRealData(cmd).Select().Select(row => Convert.ToInt32(row["data"])).ToArray());
}
}
private DbCommand GetValidateConstraintCommand(XContainer validatedDataDoc, IReadOnlyList <ContentAttribute> attrs, out string attrNames)
{
var sb = new StringBuilder();
var validatedIds = validatedDataDoc
.Descendants("ITEM")
.Where(n => !n.Descendants("MISSED_DATA").Any())
.Select(n => int.Parse(n.Attribute("id")?.Value ?? throw new InvalidOperationException()))
.ToArray();
var contentId = attrs[0].ContentId;
attrNames = string.Join(", ", attrs.Select(n => SqlQuerySyntaxHelper.FieldName(DatabaseType, n.Name)));
if (DatabaseType == DatabaseType.SqlServer)
{
sb.AppendLine("declare @default_num int, @default_date datetime;");
sb.AppendLine("set @default_num = -2147483648;");
sb.AppendLine("set @default_date = getdate();");
sb.AppendLine($"WITH X(CONTENT_ITEM_ID, {attrNames})");
sb.AppendLine(@"AS (SELECT doc.col.value('./@id', 'int') CONTENT_ITEM_ID");
foreach (var attr in attrs)
{
var aName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
sb.AppendLine($",doc.col.value('(DATA)[@id={attr.Id}][1]', 'nvarchar(max)') {aName}");
}
sb.AppendLine("FROM @xmlParameter.nodes('/ITEMS/ITEM') doc(col))");
}
else
{
sb.AppendLine($@"WITH X AS(
select xml.* from XMLTABLE('/ITEMS/ITEM' PASSING @xmlParameter COLUMNS
content_item_id int PATH '@id'" );
foreach (var attr in attrs)
{
var aName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
sb.AppendLine($@", {aName} text PATH 'DATA[@id={attr.Id}]'");
}
sb.AppendLine(") xml )");
}
var defaultDate = (DatabaseType == DatabaseType.SqlServer) ? "@default_date" : "now()";
var defaultNum = (DatabaseType == DatabaseType.SqlServer) ? "@default_num" :"-2147483648";
sb.AppendLine($" SELECT c.CONTENT_ITEM_ID FROM CONTENT_{contentId}_UNITED c {WithNoLock} INNER JOIN X ON c.CONTENT_ITEM_ID NOT IN (select v.id from {IdList("@validatedIds", "v")})");
foreach (var attr in attrs)
{
var attrName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
if (attr.IsNumeric)
{
sb.AppendLine($"AND coalesce(c.{attrName}, {defaultNum}) = case when X.{attrName} = '' then {defaultNum} else cast (X.{attrName} as numeric(18, {attr.Size})) end");
}
else if (attr.IsDateTime)
{
sb.AppendLine($"AND coalesce(c.{attrName}, {defaultDate}) = case when X.{attrName} = '' then {defaultDate} else cast (X.{attrName} as timestamp without time zone) end");
}
else
{
sb.AppendLine($"AND coalesce(c.{attrName}, '') = coalesce(X.{attrName}, '')");
}
}
var xml = validatedDataDoc.ToString(SaveOptions.None);
var cmd = CreateDbCommand(sb.ToString());
cmd.CommandTimeout = 120;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam("@xmlParameter", xml, DatabaseType));
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@validatedIds", validatedIds, DatabaseType));
return(cmd);
}
