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 void ImportItemLink(XDocument linkDoc)
{
if (linkDoc == null)
{
throw new ArgumentNullException(nameof(linkDoc));
}
var cmd = CreateDbCommand("qp_update_m2m_values");
cmd.CommandTimeout = 120;
if (DatabaseType == DatabaseType.SqlServer)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandText = "call qp_update_m2m_values(@xmlParameter);";
}
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam(
"@xmlParameter", linkDoc.ToString(SaveOptions.None), DatabaseType)
);
ProcessData(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);
}
public DbCommand GetContentItemLinkCommand(int linkId, string itemIds, bool isManyToMany, bool returnAll)
{
if (linkId == LegacyNotFound)
{
return(null);
}
var ids = itemIds.Split(',');
string table;
string query;
if (isManyToMany)
{
table = IsStage ? "item_link_united" : "item_link";
if (ids.Length == 1)
{
query = $@"SELECT linked_item_id FROM {table} {WithNoLock} WHERE item_id = @itemId AND link_id = @linkId";
}
else
{
query = returnAll ? "item_id, linked_item_id" : "DISTINCT linked_item_id";
query = $@"SELECT {query} FROM {table} {WithNoLock} where item_id in ({itemIds}) AND link_id = @linkId";
}
}
else
{
var attr = GetContentAttributeObject(linkId);
if (attr == null)
{
return(null);
}
var attrName = SqlQuerySyntaxHelper.FieldName(DatabaseType, attr.Name);
table = IsStage ? $@"content_{attr.ContentId}_united" : $@"content_{attr.ContentId}";
if (ids.Length == 1)
{
query = $"SELECT content_item_id FROM {table} {WithNoLock} WHERE {attrName} = @itemId";
}
else
{
query = returnAll
? $"[{attr.Name}] as item_id, content_item_id as linked_item_id"
: "DISTINCT content_item_id";
query = $@"SELECT {query} FROM {table} {WithNoLock} WHERE {attrName} in ({itemIds})";
}
}
var cmd = CreateDbCommand(query);
cmd.Parameters.AddWithValue("@linkId", linkId);
cmd.Parameters.AddWithValue("@itemId", ids[0]);
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 GetImportContentDataCommand(XNode dataDoc)
{
string sql;
if (DatabaseType == DatabaseType.SqlServer)
{
sql = @"
WITH X (CONTENT_ITEM_ID, ATTRIBUTE_ID, DATA, BLOB_DATA)
AS
(
SELECT
doc.col.value('./@id', 'int') CONTENT_ITEM_ID
,doc.col.value('./@attrId', 'int') ATTRIBUTE_ID
,doc.col.value('(DATA)[1]', 'nvarchar(3500)') DATA
,doc.col.value('(BLOB_DATA)[1]', 'nvarchar(max)') BLOB_DATA
FROM @xmlParameter.nodes('/ITEMS/ITEM') doc(col)
)
UPDATE CONTENT_DATA
SET CONTENT_DATA.DATA = case when X.DATA = '' then NULL else X.DATA end,
CONTENT_DATA.BLOB_DATA = case when X.BLOB_DATA = '' then NULL else X.BLOB_DATA end,
NOT_FOR_REPLICATION = 1, MODIFIED = GETDATE()
FROM dbo.CONTENT_DATA
INNER JOIN X ON CONTENT_DATA.CONTENT_ITEM_ID = X.CONTENT_ITEM_ID AND dbo.CONTENT_DATA.ATTRIBUTE_ID = X.ATTRIBUTE_ID ";
}
else
{
sql = @"
WITH X
AS
(
select xml.content_item_id, xml.attribute_id,
case when xml.DATA = '' then NULL else xml.DATA end as data,
case when xml.BLOB_DATA = '' then NULL else xml.BLOB_DATA end
from XMLTABLE('/ITEMS/ITEM' PASSING @xmlParameter COLUMNS
CONTENT_ITEM_ID int PATH '@id',
ATTRIBUTE_ID int PATH '@attrId',
DATA text PATH 'DATA',
BLOB_DATA text PATH 'BLOB_DATA'
) xml
)
UPDATE CONTENT_DATA cd
SET DATA = coalesce(X.BLOB_DATA, X.DATA), NOT_FOR_REPLICATION = true, MODIFIED = now()
FROM X WHERE cd.CONTENT_ITEM_ID = X.CONTENT_ITEM_ID AND cd.ATTRIBUTE_ID = X.ATTRIBUTE_ID ";
}
var cmd = CreateDbCommand(sql);
cmd.CommandTimeout = 120;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam("@xmlParameter", dataDoc.ToString(SaveOptions.None), DatabaseType));
return(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 GetMassUpdateContentItemCommand(int contentId, int lastModifiedBy, XDocument doc, bool createVersions)
{
var createVersionsString = createVersions
? "exec qp_create_content_item_versions @OldIds, @lastModifiedBy"
: string.Empty;
var insertInto = $@"
DECLARE @Articles TABLE
(
CONTENT_ITEM_ID NUMERIC,
STATUS_TYPE_ID NUMERIC,
VISIBLE NUMERIC,
ARCHIVE NUMERIC
)
DECLARE @NewArticles [Ids]
DECLARE @OldIds [Ids]
DECLARE @OldNonSplittedIds [Ids]
DECLARE @NewSplittedIds [Ids]
DECLARE @OldSplittedIds [Ids]
DECLARE @NewNonSplittedIds [Ids]
INSERT INTO @Articles
SELECT
doc.col.value('(CONTENT_ITEM_ID)[1]', 'numeric') CONTENT_ITEM_ID
,doc.col.value('(STATUS_TYPE_ID)[1]', 'numeric') STATUS_TYPE_ID
,doc.col.value('(VISIBLE)[1]', 'numeric') VISIBLE
,doc.col.value('(ARCHIVE)[1]', 'numeric') ARCHIVE
FROM @xmlParameter.nodes('/ITEMS/ITEM') doc(col)
INSERT into CONTENT_ITEM (CONTENT_ID, VISIBLE, ARCHIVE, STATUS_TYPE_ID, LAST_MODIFIED_BY, NOT_FOR_REPLICATION)
OUTPUT inserted.[CONTENT_ITEM_ID] INTO @NewArticles
SELECT @contentId, VISIBLE, ARCHIVE, STATUS_TYPE_ID, @lastModifiedBy, @notForReplication
FROM @Articles a WHERE a.CONTENT_ITEM_ID = 0
INSERT INTO @OldIds
SELECT a.CONTENT_ITEM_ID from @Articles a INNER JOIN content_item ci with(rowlock, updlock) on a.CONTENT_ITEM_ID = ci.CONTENT_ITEM_ID
{createVersionsString}
INSERT INTO @OldNonSplittedIds
SELECT i.Id from @OldIds i INNER JOIN content_item ci on i.id = ci.CONTENT_ITEM_ID where ci.SPLITTED = 0
INSERT INTO @OldSplittedIds
SELECT i.Id from @OldIds i INNER JOIN content_item ci on i.id = ci.CONTENT_ITEM_ID where ci.SPLITTED = 1
UPDATE CONTENT_ITEM SET
VISIBLE = COALESCE(a.visible, ci.visible),
ARCHIVE = COALESCE(a.archive, ci.archive),
STATUS_TYPE_ID = COALESCE(a.STATUS_TYPE_ID, ci.STATUS_TYPE_ID),
LAST_MODIFIED_BY = @lastModifiedBy,
MODIFIED = GETDATE()
FROM @Articles a INNER JOIN content_item ci on a.CONTENT_ITEM_ID = ci.CONTENT_ITEM_ID
INSERT INTO @NewSplittedIds
SELECT i.Id from @OldNonSplittedIds i INNER JOIN content_item ci on i.ID = ci.CONTENT_ITEM_ID where ci.SPLITTED = 1
INSERT INTO @NewNonSplittedIds
SELECT i.Id from @OldSplittedIds i INNER JOIN content_item ci on i.ID = ci.CONTENT_ITEM_ID where ci.SPLITTED = 0
exec qp_split_articles @NewSplittedIds, @lastModifiedBy
exec qp_merge_articles @NewNonSplittedIds, @lastModifiedBy, 1
SELECT ID FROM @NewArticles ";
var cmd = CreateDbCommand(insertInto);
var xml = doc.ToString(SaveOptions.None);
if (DatabaseType != DatabaseType.SqlServer)
{
cmd.CommandText = "select id from qp_mass_update_content_item(@xmlParameter, @contentId, @lastModifiedBy, @notForReplication, @createVersions, @importOnly);";
}
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam("@xmlParameter", xml, DatabaseType));
cmd.Parameters.AddWithValue("@contentId", contentId);
cmd.Parameters.AddWithValue("@lastModifiedBy", lastModifiedBy);
cmd.Parameters.AddWithValue("@notForReplication", 1);
if (DatabaseType != DatabaseType.SqlServer)
{
cmd.Parameters.AddWithValue("@createVersions", createVersions);
cmd.Parameters.AddWithValue("@importOnly", false);
}
return(cmd);
}
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);
}
public string IdList(string name = "@ids", string alias = "i") => SqlQuerySyntaxHelper.IdList(DatabaseType, name, alias);
private void ImportContentItem(int contentId, IEnumerable <Dictionary <string, string> > values, int lastModifiedBy, XDocument doc)
{
const string insertInto = @"
DECLARE @Articles TABLE
(
CONTENT_ITEM_ID NUMERIC,
STATUS_TYPE_ID NUMERIC,
VISIBLE NUMERIC,
ARCHIVE NUMERIC
)
DECLARE @NewArticles TABLE(ID INT)
INSERT INTO @Articles
SELECT
doc.col.value('(CONTENT_ITEM_ID)[1]', 'numeric') CONTENT_ITEM_ID
,doc.col.value('(STATUS_TYPE_ID)[1]', 'numeric') STATUS_TYPE_ID
,doc.col.value('(VISIBLE)[1]', 'numeric') VISIBLE
,doc.col.value('(ARCHIVE)[1]', 'numeric') ARCHIVE
FROM @xmlParameter.nodes('/ITEMS/ITEM') doc(col)
INSERT into CONTENT_ITEM (CONTENT_ID, VISIBLE, ARCHIVE, STATUS_TYPE_ID, LAST_MODIFIED_BY, NOT_FOR_REPLICATION)
OUTPUT inserted.[CONTENT_ITEM_ID] INTO @NewArticles
SELECT @contentId, VISIBLE, ARCHIVE, STATUS_TYPE_ID, @lastModifiedBy, @notForReplication
FROM @Articles a WHERE a.CONTENT_ITEM_ID = 0
UPDATE CONTENT_ITEM with(rowlock) SET
VISIBLE = COALESCE(a.visible, ci.visible),
ARCHIVE = COALESCE(a.archive, ci.archive),
STATUS_TYPE_ID = COALESCE(a.STATUS_TYPE_ID, ci.STATUS_TYPE_ID),
LAST_MODIFIED_BY = @lastModifiedBy,
MODIFIED = GETDATE()
FROM @Articles a INNER JOIN content_item ci on a.CONTENT_ITEM_ID = ci.CONTENT_ITEM_ID
SELECT ID FROM @NewArticles
";
var cmd = CreateDbCommand(insertInto);
var xml = doc.ToString(SaveOptions.None);
if (DatabaseType != DatabaseType.SqlServer)
{
cmd.CommandText = "select id from qp_mass_update_content_item(@xmlParameter, @contentId, @lastModifiedBy, @notForReplication, @createVersions, @importOnly);";
}
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParam("@xmlParameter", xml, DatabaseType));
cmd.Parameters.AddWithValue("@contentId", contentId);
cmd.Parameters.AddWithValue("@lastModifiedBy", lastModifiedBy);
cmd.Parameters.AddWithValue("@notForReplication", 1);
if (DatabaseType != DatabaseType.SqlServer)
{
cmd.Parameters.AddWithValue("@createVersions", false);
cmd.Parameters.AddWithValue("@importOnly", true);
}
var ids = new Queue <int>(GetRealData(cmd).Select().Select(row => Convert.ToInt32(row["ID"])).ToArray());
foreach (var value in values)
{
if (value[SystemColumnNames.Id] == "0")
{
value[SystemColumnNames.Id] = ids.Dequeue().ToString();
}
}
}