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