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 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 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);
}
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();
}
}
}