public static void UpdateChildDelayedSchedule(DbConnection connection, int articleId) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection); var sql = SqlQuerySyntaxHelper.SpCall(databaseType, "qp_copy_schedule_to_child_delays", articleId.ToString()); ExecuteSql(connection, sql); }
public static void ReplicateItems(DbConnection sqlConnection, int[] articleIds, int[] fieldIds) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var sql = (dbType == DatabaseType.SqlServer) ? "qp_replicate_items" : "call qp_replicate_items(@ids, @attr_ids);"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.NVarChar, -1) { Value = string.Join(",", articleIds) }); cmd.Parameters.Add(new SqlParameter("@attr_ids", SqlDbType.NVarChar, -1) { Value = string.Join(",", fieldIds) }); } else { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", articleIds, dbType)); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@attr_ids", fieldIds, dbType)); } cmd.Parameters.AddWithValue("@modification_update_interval", -1); cmd.ExecuteNonQuery(); } }
public static void PersistArticle(DbConnection currentDbConnection, string customerCode, string xml, out int id) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(currentDbConnection); var ns = SqlQuerySyntaxHelper.DbSchemaName(databaseType); var sql = $"select id, modified from {ns}.qp_persist_article(@xml)"; using (var cmd = DbCommandFactory.Create(sql, currentDbConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, databaseType)); var dt = new DataTable(); try { DataAdapterFactory.Create(cmd).Fill(dt); } catch (PostgresException ex) { Logger.Error() .Exception(ex) .Message("Error while persisting article with xml: {xml}\n Query: {sql}", xml, sql) .Property("customerCode", customerCode) .Write(); throw; } id = (int)dt.Rows[0]["id"]; } }
public static IEnumerable <DataRow> BatchInsert(DbConnection sqlConnection, DataTable articles, bool visible, int userId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); using (var cmd = DbCommandFactory.Create(BatchInsertQuery, sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured) { TypeName = "Values", Value = articles }); } else { cmd.CommandText = $@"select * from qp_batch_insert(@values, @visible, @userId);"; var xml = GetValuesDoc(articles).ToString(); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType)); } cmd.Parameters.AddWithValue("@visible", visible ? 1 : 0); cmd.Parameters.AddWithValue("@userId", userId); var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); return(dt.AsEnumerable().ToArray()); } }
public static void CreateContentViews(DbConnection cnn, int id, bool withUnited = true) { var dbType = GetDbType(cnn); var idStr = id.ToString(); if (dbType == DatabaseType.SqlServer) { if (withUnited) { var unitedSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_united_view_recreate", idStr); ExecuteSql(cnn, unitedSql); } var feSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_recreate", idStr); ExecuteSql(cnn, feSql); } else { if (withUnited) { var unitedSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_united_view_create", idStr); ExecuteSql(cnn, unitedSql); } var feSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_create", idStr); ExecuteSql(cnn, feSql); var newSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_create", idStr); ExecuteSql(cnn, newSql); } }
private static Dictionary <int, int?> GetPermissionLevels(DbConnection sqlConnection, int[] ids, int userId, int groupId, string entityTypeName, string parentEntityTypeName = "", int parentId = 0) { var result = new Dictionary <int, int?>(); var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, groupId, PermissionLevel.Deny, PermissionLevel.FullAccess, entityTypeName, parentEntityTypeName, parentId); var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var sql = $@" select i.id, pi.permission_level from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")} left join ({securitySql}) as pi on pi.{entityTypeName}_id = i.id " ; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType)); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { result[Convert.ToInt32(reader["id"])] = Converter.ToNullableInt32(reader["permission_level"]); } } } return(result); }
private static Dictionary <int, bool> CheckSecurity(DbConnection sqlConnection, int parentId, IEnumerable <int> testIds, int userId, int startLevel, string entityName, string parentEntityName, string columnName) { var granted = new Dictionary <int, bool>(); var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, 0, startLevel, PermissionLevel.FullAccess, entityName, parentEntityName, parentId); var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true); var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false); var sql = $@" select i.id, {SqlQuerySyntaxHelper.CastToBool(dbType, $"case when pi.{columnName} is null then {falseValue} else {trueValue} end")} as granted from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")} left join ({securitySql}) as pi on pi.{columnName} = i.id " ; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", testIds, dbType)); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { granted[Convert.ToInt32(reader["id"])] = (bool)reader["granted"]; } } } return(granted); }
public static DataRow[] GetRelationSecurityFields(DbConnection sqlConnection) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string contentLinkSql = $@" SELECT link_id AS link_id, l_content_id AS content_id, r_content_id AS linked_content_id FROM content_to_content UNION SELECT link_id AS link_id, r_content_id AS content_id, l_content_id AS linked_content_id FROM content_to_content "; var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true); var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false); var sqlText = $@" select coalesce(ca3.content_id, ca1.content_id) as path_content_id, coalesce(ca4.CONTENT_ID, cl.linked_content_id) as rel_content_id, ca1.content_id, {SqlQuerySyntaxHelper.CastToBool(dbType, $"case when ca1.link_id is not null then {trueValue} else {falseValue} end")} as is_m2m, {SqlQuerySyntaxHelper.CastToBool(dbType, $"case when ca2.attribute_id is not null then {trueValue} else {falseValue} end")} as is_ext, ca1.is_classifier, ca1.attribute_id, ca1.attribute_name, ca1.link_id, ca2.ATTRIBUTE_NAME as agg_attribute_name from CONTENT_ATTRIBUTE ca1 left join ({contentLinkSql}) cl on ca1.content_id = cl.content_id and ca1.link_id = cl.link_id left join CONTENT_ATTRIBUTE ca4 on ca1.RELATED_ATTRIBUTE_ID = ca4.ATTRIBUTE_ID left join content_attribute ca2 on ca1.content_id = ca2.content_id and ca2.AGGREGATED = {trueValue} left join content_attribute ca3 on ca2.RELATED_ATTRIBUTE_ID = ca3.attribute_Id where ca1.USE_RELATION_SECURITY = {trueValue} " ; return(Common.GetDataTableForQuery(sqlConnection, sqlText).AsEnumerable().ToArray()); }
public static void DropLinkView(DbConnection connection, ContentToContentDAL item) { var dbType = GetDbType(connection); var spName = dbType == DatabaseType.SqlServer ? "qp_drop_link_view" : "qp_link_view_drop"; var sql = SqlQuerySyntaxHelper.SpCall(dbType, spName, item.LinkId.ToString(CultureInfo.InvariantCulture)); ExecuteSql(connection, sql); }
public static List <int> InsertArticleIds(DbConnection sqlConnection, string xml, bool withGuids) { var result = new List <int>(); var withGuidsStr = withGuids ? ", UNIQUE_ID" : ""; var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string baseInsert = $@"INSERT INTO CONTENT_ITEM (VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr}) "; string baseSelect = $@"SELECT VISIBLE, STATUS_TYPE_ID, CONTENT_ID, LAST_MODIFIED_BY {withGuidsStr} "; string sql = dbType == DatabaseType.SqlServer ? $@" DECLARE @NewArticles TABLE (CONTENT_ID int, STATUS_TYPE_ID int, VISIBLE int, LAST_MODIFIED_BY int, UNIQUE_ID uniqueidentifier) DECLARE @NewIds TABLE ([ID] INT); INSERT INTO @NewArticles SELECT doc.col.value('./@contentId', 'int') CONTENT_ID ,doc.col.value('./@statusId', 'int') STATUS_TYPE_ID ,doc.col.value('./@visible', 'int') VISIBLE ,doc.col.value('./@userId', 'int') LAST_MODIFIED_BY ,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID FROM @xml.nodes('ITEMS/ITEM') doc(col) {baseInsert} OUTPUT inserted.CONTENT_ITEM_ID INTO @NewIds {baseSelect} from @NewArticles SELECT ID FROM @NewIds; " : $@" WITH inserted(id) AS ( {baseInsert} {baseSelect} from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS CONTENT_ID int PATH '@contentId', STATUS_TYPE_ID int PATH '@statusId', VISIBLE int PATH '@visible', LAST_MODIFIED_BY int PATH '@userId', UNIQUE_ID uuid PATH '@guid' ) x returning CONTENT_ITEM_ID ) select id from inserted;"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", xml, dbType)); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { int id; if (int.TryParse(reader.GetValue(0).ToString(), out id)) { result.Add(id); } } } return(result); } }
public static void CreateArticleVersions(DbConnection connection, int userId, int[] ids) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection); var sql = SqlQuerySyntaxHelper.SpCall(databaseType, "qp_create_content_item_versions", "@ids, @last_modified_by"); using (var cmd = DbCommandFactory.Create(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, databaseType)); cmd.Parameters.AddWithValue("@last_modified_by", userId); cmd.ExecuteNonQuery(); } }
private static string GetSelectExpression(DatabaseType dbType, RelationSecurityPathItem n) { if (n.IsClassifier) { return($"c{n.JoinOrder}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, n.AttributeName)}"); } var fieldName = dbType == DatabaseType.Postgres ? $"\"{n.RelContentId}\"" : $"'{n.RelContentId}'"; var castToInt = dbType == DatabaseType.Postgres ? "::integer" : string.Empty; return(n.LinkId.HasValue ? $"{SqlQuerySyntaxHelper.DbSchemaName(dbType)}.qp_link_ids({n.LinkId}{castToInt}, c{n.JoinOrder}.CONTENT_ITEM_ID{castToInt}, {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}) as {fieldName}" : $"{SqlQuerySyntaxHelper.CastToString(dbType, $"c{n.Order}.content_item_id")} as {fieldName}"); }
public static void UpdateNotForReplication(DbConnection sqlConnection, int[] ids, int userId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var setTrue = dbType == DatabaseType.SqlServer ? "1" : "true"; var sql = $@" UPDATE CONTENT_ITEM SET NOT_FOR_REPLICATION = {setTrue}, MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}, LAST_MODIFIED_BY = @userId WHERE {SqlQuerySyntaxHelper.IsFalse(dbType, "NOT_FOR_REPLICATION")} AND CONTENT_ITEM_ID IN (select id from {Common.IdList(dbType, "@ids")}); "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType)); cmd.Parameters.AddWithValue("@userId", userId); cmd.ExecuteNonQuery(); } }
public static void InsertArticleValues(DbConnection sqlConnection, string xmlParameter) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string sql = dbType == DatabaseType.SqlServer ? "qp_insertArticleValues" : PgTableValuesBlock + $@" update content_data cd set data = coalesce(new.blob_data, new.data) from new where cd.content_item_id = new.content_item_id and cd.attribute_id = new.attribute_id; "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); } }
public static void DropContentViews(DbConnection cnn, int id) { var dbType = GetDbType(cnn); var idStr = id.ToString(); if (dbType != DatabaseType.SqlServer) { var newSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_drop", idStr); ExecuteSql(cnn, newSql); var feSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_drop", idStr); ExecuteSql(cnn, feSql); var unitedSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_united_view_drop", idStr); ExecuteSql(cnn, unitedSql); } }
public static void UpdateArticleGuids(DbConnection sqlConnection, List <Tuple <int, Guid> > guidsByIdToUpdate) { if (guidsByIdToUpdate.Any()) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var doc = new XDocument(); doc.Add(new XElement("ITEMS")); foreach (var tuple in guidsByIdToUpdate) { var elem = new XElement("ITEM"); elem.Add(new XAttribute("id", tuple.Item1)); elem.Add(new XAttribute("guid", tuple.Item2)); doc.Root?.Add(elem); } string sql = dbType == DatabaseType.SqlServer ? $@" DECLARE @NewIds TABLE (CONTENT_ITEM_ID int, UNIQUE_ID uniqueidentifier) INSERT INTO @NewIds SELECT doc.col.value('./@id', 'int') CONTENT_ITEM_ID ,doc.col.value('./@guid', 'uniqueidentifier') UNIQUE_ID FROM @xml.nodes('ITEMS/ITEM') doc(col) UPDATE CONTENT_ITEM SET UNIQUE_ID = i.UNIQUE_ID FROM CONTENT_ITEM ci INNER JOIN @NewIds i on ci.CONTENT_ITEM_ID = i.CONTENT_ITEM_ID " : $@" WITH new AS ( select x.* from XMLTABLE('ITEMS/ITEM' passing @xml COLUMNS content_item_id int PATH '@id', unique_id uuid PATH '@guid' ) x ) update content_item ci set unique_id = new.unique_id from new where ci.content_item_id = new.content_item_id;"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), dbType)); cmd.ExecuteNonQuery(); } } }
public static void RecreateNewViews(DbConnection cnn, int id) { var dbType = GetDbType(cnn); var idStr = id.ToString(); if (dbType == DatabaseType.SqlServer) { var recreateSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_frontend_views_recreate", idStr); ExecuteSql(cnn, recreateSql); } else { var dropSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_drop", idStr); ExecuteSql(cnn, dropSql); var createSql = SqlQuerySyntaxHelper.SpCall(dbType, "qp_content_new_views_create", idStr); ExecuteSql(cnn, createSql); } }
public static void ValidateO2MValues(DbConnection sqlConnection, string xmlParameter, string message) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var tableDefintionBlock = dbType == DatabaseType.SqlServer ? $@" DECLARE @NewArticles TABLE (CONTENT_ITEM_ID int, ATTRIBUTE_ID int, DATA nvarchar(3500), BLOB_DATA nvarchar(max)) INSERT INTO @NewArticles SELECT doc.col.value('(CONTENT_ITEM_ID)[1]', 'int') CONTENT_ITEM_ID ,doc.col.value('(ATTRIBUTE_ID)[1]', 'int') ATTRIBUTE_ID ,doc.col.value('(DATA)[1]', 'nvarchar(3500)') DATA ,doc.col.value('(BLOB_DATA)[1]', 'nvarchar(max)') BLOB_DATA FROM @xmlParameter.nodes('/PARAMETERS/FIELDVALUE') doc(col) " : PgTableValuesBlock; var tableUsing = dbType == DatabaseType.SqlServer ? "@NewArticles a" : "new a"; string sql = $@"{tableDefintionBlock} select * from ( select a.*, ca.ATTRIBUTE_NAME, rca.CONTENT_ID as RELATED_CONTENT_ID from {tableUsing} inner join CONTENT_ATTRIBUTE ca on a.ATTRIBUTE_ID = ca.ATTRIBUTE_ID inner join CONTENT_ATTRIBUTE rca on ca.RELATED_ATTRIBUTE_ID = rca.ATTRIBUTE_ID and ca.CONTENT_ID <> rca.CONTENT_ID inner join CONTENT rc on rc.CONTENT_ID = rca.CONTENT_ID and rc.VIRTUAL_TYPE <> 3 where a.data != '' ) as a left join CONTENT_ITEM ci on ci.CONTENT_ITEM_ID = cast(a.data as numeric) where ci.CONTENT_ID is null or ci.CONTENT_ID <> a.RELATED_CONTENT_ID"; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); if (resultDt.AsEnumerable().Any()) { var dr = resultDt.Rows[0]; var title = dr.Field <string>("ATTRIBUTE_NAME"); var data = dr.Field <string>("DATA"); var id = dr.Field <int>(FieldName.ContentItemId).ToString(); throw new ArgumentException(string.Format(message, id, title, data)); } } }
public static int[] GetArticleIdsByGuids(DbConnection sqlConnection, Guid[] guids) { if (guids == null) { throw new ArgumentNullException(nameof(guids)); } var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var noLock = WithNoLock(databaseType); var textCast = (databaseType == DatabaseType.Postgres) ? "::text" : ""; string xmlSource; if (databaseType == DatabaseType.Postgres) { xmlSource = "XMLTABLE('/guids/guid' PASSING @xml COLUMNS unique_id uuid PATH '.')"; } else { xmlSource = "(SELECT doc.col.value('.[1]', 'nvarchar(max)') UNIQUE_ID FROM @xml.nodes('/guids/guid') doc(col))"; } var query = $"select coalesce(ci.content_item_id, 0), guids.unique_id{textCast} from {xmlSource} guids left join content_item ci {noLock} on ci.unique_id = guids.unique_id"; using (var cmd = DbCommandFactory.Create(query, sqlConnection)) { var doc = new XDocument(new XElement("guids")); doc.Root?.Add(guids.Select(n => new XElement("guid", n.ToString()))); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xml", doc.ToString(), databaseType)); var result = new Dictionary <Guid, int>(); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { result.Add(new Guid(dr.GetString(1)), (int)dr.GetDecimal(0)); } } return(guids.Select(n => result[n]).ToArray()); } }
public static void BatchUpdate(DbConnection sqlConnection, DataTable articles, int userId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var sql = dbType == DatabaseType.SqlServer ? SqlBatchUpdateQuery : PgBatchUpdateQuery; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured) { TypeName = "Values", Value = articles }); } else { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", GetValuesDoc(articles).ToString(), dbType)); } cmd.Parameters.AddWithValue("@userId", userId); cmd.ExecuteNonQuery(); } }
public static IEnumerable <DataRow> GetMenuStatusList( DbConnection sqlConnection, QPModelDataContext efContext, int userId, bool isAdmin, string menuCode, int entityId) { var useSecurity = !isAdmin; var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var menuId = efContext.ContextMenuSet.First(x => x.Code == menuCode).Id; string query; if (!useSecurity) { query = $@" SELECT ba.CODE, {SqlQuerySyntaxHelper.ToBoolSql(databaseType, true)} as visible FROM CONTEXT_MENU_ITEM cmi INNER JOIN BACKEND_ACTION ba on ba.ID = cmi.ACTION_ID WHERE cmi.context_menu_id = {menuId} "; } else { var level = GetEntityAccessLevel(sqlConnection, efContext, userId, 0, menuCode, entityId); var secQuery = PermissionHelper.GetActionPermissionsAsQuery(efContext, userId); var least = SqlQuerySyntaxHelper.Least(databaseType, "SEC.PERMISSION_LEVEL", level.ToString()); query = $@" SELECT ba.CODE, CAST(( CASE WHEN {least} >= PL.PERMISSION_LEVEL THEN 1 ELSE 0 END ) AS BIT) as visible FROM CONTEXT_MENU_ITEM cmi INNER JOIN BACKEND_ACTION ba on ba.ID = cmi.ACTION_ID INNER JOIN ACTION_TYPE at on ba.TYPE_ID = at.ID INNER JOIN PERMISSION_LEVEL PL ON PL.PERMISSION_LEVEL_ID = AT.REQUIRED_PERMISSION_LEVEL_ID INNER JOIN ({secQuery}) SEC ON SEC.BACKEND_ACTION_ID = ba.ID WHERE cmi.context_menu_id = {menuId} "; } return(Common.GetDataTableForQuery(sqlConnection, query)); }
public static void UpdateArticlesDateTime(DbConnection sqlConnection, string xmlParameter) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string sql = dbType == DatabaseType.SqlServer ? "qp_update_acrticle_modification_date" : $@" WITH new AS ( select x.* from XMLTABLE('ITEMS/ITEM' passing @xmlParameter COLUMNS content_item_id int PATH '@id', last_modified_by int PATH '@modifiedBy' ) x ) update content_item ci set modified = now(), last_modified_by = new.last_modified_by from new where ci.content_item_id = new.content_item_id; "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); } }
public static IEnumerable <DataRow> GetActionStatusList(QPModelDataContext efContext, DbConnection sqlConnection, int userId, string actionCode, int?actionId, int entityId, string entityCode, bool isAdmin) { var useSecurity = !isAdmin; var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string query; if (!useSecurity) { query = $@" SELECT ba.CODE, {SqlQuerySyntaxHelper.ToBoolSql(databaseType, true)} as visible FROM ACTION_TOOLBAR_BUTTON atb INNER JOIN BACKEND_ACTION ba on ba.ID = atb.ACTION_ID INNER JOIN ACTION_TYPE at on ba.TYPE_ID = at.ID WHERE atb.PARENT_ACTION_ID = {actionId} AND at.items_affected = 1 "; } else { var level = GetEntityAccessLevel(sqlConnection, efContext, userId, 0, entityCode, entityId); var least = SqlQuerySyntaxHelper.Least(databaseType, "SEC.PERMISSION_LEVEL", level.ToString()); var secQuery = PermissionHelper.GetActionPermissionsAsQuery(efContext, userId); query = $@" SELECT ba.CODE, CAST(( CASE WHEN {least} >= PL.PERMISSION_LEVEL THEN 1 ELSE 0 END ) AS BIT) as visible FROM ACTION_TOOLBAR_BUTTON atb INNER JOIN BACKEND_ACTION ba on ba.ID = atb.ACTION_ID INNER JOIN ACTION_TYPE at on ba.TYPE_ID = at.ID INNER JOIN PERMISSION_LEVEL PL ON PL.PERMISSION_LEVEL_ID = AT.REQUIRED_PERMISSION_LEVEL_ID INNER JOIN ({secQuery}) SEC ON SEC.BACKEND_ACTION_ID = ba.ID WHERE atb.PARENT_ACTION_ID = {actionId} AND at.items_affected = 1 "; } return(Common.GetDataTableForQuery(sqlConnection, query)); }
public static IEnumerable <DataRow> GetRelations(DbConnection sqlConnection, DataTable articles) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); using (var cmd = DbCommandFactory.Create(GetRelationsQuery(dbType), sqlConnection)) { if (dbType == DatabaseType.SqlServer) { cmd.Parameters.Add(new SqlParameter("@values", SqlDbType.Structured) { TypeName = "Values", Value = articles }); } else { var xml = GetValuesDoc(articles).ToString(); cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@values", xml, dbType)); } var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); return(dt.AsEnumerable().ToArray()); } }
public static void InsertO2MFieldValues(DbConnection sqlConnection, string xmlParameter) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); string sql = dbType == DatabaseType.SqlServer ? "qp_update_o2mfieldvalues" : $@" WITH new AS ( select x.* from XMLTABLE('ITEMS/ITEM' passing @xmlParameter COLUMNS content_item_id int PATH '@id', linked_id int PATH '@linked_id', attribute_id int PATH '@field_id' ) x ) update content_data cd set data = new.linked_id from new where cd.content_item_id = new.CONTENT_ITEM_ID and cd.attribute_id = new.attribute_id; "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = dbType == DatabaseType.SqlServer ? CommandType.StoredProcedure : CommandType.Text; cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@xmlParameter", xmlParameter, dbType)); var resultDt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(resultDt); } }
public static string GetSecurityPathSql(DatabaseType dbType, List <RelationSecurityPathItem> items, int contentId) { if (items == null) { throw new ArgumentNullException(nameof(items)); } var sqlBuilder = new StringBuilder(); var lastItem = items.Last(); var selects = Enumerable.Repeat(lastItem, 1).Concat(lastItem.Secondary).Select(item => GetSelectExpression(dbType, item)); sqlBuilder.Append($"select c0.content_item_id as id, {string.Join(", ", selects)} from content_{contentId}_united c0 "); foreach (var item in items) { if (item != lastItem) { if (item.LinkId.HasValue) { throw new ApplicationException("Invalid security path settings: m2m"); } if (item.Secondary.Any()) { throw new ApplicationException("Invalid security path settings: secondary"); } } if (!item.LinkId.HasValue) { if (item.Extensions.Any()) { var first = item.Extensions.First(); var inner = string.Join("union all " + Environment.NewLine, item.Extensions.Select(n => n.LinkId.HasValue ? $@"select {n.AggAttributeName} as agg, il.linked_item_id as {first.AttributeName} From content_{n.ContentId}_united c inner join item_link il on c.content_item_id = il.item_id and il.link_id = {n.LinkId.Value} {Environment.NewLine} " : $"select {n.AggAttributeName} as agg, {n.AttributeName} as {first.AttributeName} From content_{n.ContentId}_united {Environment.NewLine}") ); sqlBuilder.AppendFormat("inner join ({3}{0}{3}) c{1} on c{1}.agg = c{2}.CONTENT_ITEM_ID{3}", inner, first.Order, item.JoinOrder, Environment.NewLine); sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}", first.RelContentId, item.Order, first.Order, first.AttributeName, Environment.NewLine); } else if (!item.IsClassifier) { sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}", item.RelContentId, item.Order, item.JoinOrder, item.AttributeName, Environment.NewLine); } } if (item == lastItem && item.Secondary != null) { foreach (var secItem in item.Secondary.Where(n => !n.LinkId.HasValue && !n.IsClassifier)) { sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}", secItem.RelContentId, secItem.Order, secItem.JoinOrder, secItem.AttributeName, Environment.NewLine); } } } sqlBuilder.Append($"where c0.content_item_id in (select id from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})"); return(sqlBuilder.ToString()); }
public static RelationSecurityInfo GetRelationSecurityInfo(DbConnection dbConnection, int contentId, int[] ids) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(dbConnection); var result = new RelationSecurityInfo(); var pathRows = GetRelationSecurityFields(dbConnection); var securityPathes = new List <List <RelationSecurityPathItem> >(); var finder = new RelationSecurityPathFinder(pathRows.ToList(), contentId); finder.Compute(); securityPathes.Add(finder.CurrentPath); foreach (var extra in finder.ExtraFinders) { extra.Compute(); securityPathes.Add(extra.CurrentPath); } foreach (var securityPath in securityPathes) { if (securityPath.Count <= 0) { var isEndNode = finder.PathRows.Any(n => (Converter.ToNullableInt32(n["rel_content_id"]) ?? 0) == contentId); if (!isEndNode) { result.MakeEmpty(); } else { result.AddContentInItemMapping(contentId, ids.ToDictionary(n => n, m => Enumerable.Repeat(m, 1).ToArray())); } return(result); } var lastItem = securityPath.Last(); var lastItemWithSecondary = Enumerable.Repeat(lastItem, 1).Concat(lastItem.Secondary).ToList(); var contentIds = lastItemWithSecondary.Where(n => !n.IsClassifier).Select(n => n.RelContentId).ToArray(); var attNames = lastItemWithSecondary.Where(n => n.IsClassifier).Select(n => n.AttributeName).ToArray(); foreach (var item in contentIds) { result.AddContentInItemMapping(item, new Dictionary <int, int[]>()); } var sql = GetSecurityPathSql(dbType, securityPath, contentId); using (var cmd = DbCommandFactory.Create(sql, dbConnection)) { cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType)); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { ProcessSecurityPathSqlReader(reader, contentIds, attNames, result); } } } AppendNotFound(ids, contentIds, result); } return(result); }
private static string GetSqlQuery(QPModelDataContext context, DbConnection connection, string entityTypeCode, int?parentEntityId, bool isFolder, bool isGroup, string groupItemCode, int entityId, int userId, bool isAdmin, string customerCode, bool enableContentGrouping, bool countOnly = false) { var entityTypes = EntityTypeCache.GetEntityTypes(context, customerCode, userId); var entityType = entityTypes.FirstOrDefault(x => x.Code.Equals(entityTypeCode, StringComparison.InvariantCultureIgnoreCase)); var parentGroupCode = entityType == null || !enableContentGrouping ? null : entityTypes.FirstOrDefault(x => x.Id == entityType.GroupParentId)?.Code; var realParentId = isGroup ? GetParentEntityId(context, connection, (decimal)parentEntityId, entityTypeCode, customerCode, userId) : parentEntityId; var currentIsGroup = false; string currentGroupItemCode = null; var newEntityTypeCode = entityTypeCode; var newIsFolder = isFolder; if (!string.IsNullOrWhiteSpace(parentGroupCode)) { if (isFolder) { currentGroupItemCode = entityTypeCode; newEntityTypeCode = parentGroupCode; currentIsGroup = true; } } else if (!string.IsNullOrWhiteSpace(groupItemCode)) { if (!isFolder) { newIsFolder = true; newEntityTypeCode = groupItemCode; } } var newEntityType = entityTypes.FirstOrDefault(x => x.Code.Equals(newEntityTypeCode, StringComparison.InvariantCultureIgnoreCase)); var realParentIdStr = realParentId.HasValue ? realParentId.ToString() : "NULL"; var iconField = newEntityType?.IconField ?? "NULL"; var iconModifierField = newEntityType?.IconModifierField ?? "NULL"; var parentIdField = newEntityType?.ParentIdField; string realParentIdField = null; if (isGroup) { realParentIdField = parentIdField; parentIdField = newEntityType?.GroupParentIdField; } var sqlSb = new StringBuilder(); var selectSb = new StringBuilder(); var whereSb = new StringBuilder(); var orderSb = new StringBuilder(); string sql; var databaseType = DatabaseTypeHelper.ResolveDatabaseType(context); var useSecurity = UseSecurity(isAdmin, databaseType); if (newIsFolder || !string.IsNullOrWhiteSpace(newEntityType?.RecurringIdField)) { if (newEntityType?.HasItemNodes ?? false) { var orderColumn = (string.IsNullOrWhiteSpace(newEntityType.OrderField) ? newEntityType.TitleField : newEntityType.OrderField).FixColumnName(databaseType); selectSb.AppendLine($@" {newEntityType.Source}.{newEntityType.IdField} AS id, {newEntityType.TitleField} AS title, {iconField} as icon, {iconModifierField} as icon_modifier, {orderColumn} as sortorder "); whereSb.AppendLine("1 = 1"); if (!string.IsNullOrWhiteSpace(parentIdField) && parentEntityId != 0) { whereSb.AppendLine($" AND {parentIdField} = {parentEntityId}"); } if (!string.IsNullOrWhiteSpace(newEntityType.RecurringIdField)) { whereSb.AppendLine($" AND {newEntityType.RecurringIdField} {(newIsFolder ? " is null" : $" = {parentEntityId}")}"); } if (entityId != 0) { whereSb.AppendLine($" AND {newEntityType.Source}.{newEntityType.IdField} = {entityId}"); } orderSb.AppendLine(orderColumn); } if (string.IsNullOrWhiteSpace(newEntityType.SourceSP)) { if (!string.IsNullOrWhiteSpace(selectSb.ToString()) && !string.IsNullOrWhiteSpace(newEntityType.Source) && !string.IsNullOrWhiteSpace(whereSb.ToString())) { sqlSb.AppendLine($"select {selectSb} from {newEntityType.Source} where {whereSb}"); } } else { decimal?siteId; switch (newEntityType.SourceSP) { case "qp_sites_list": sqlSb.AppendLine(GetSitesListSql(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), false, userId, useSecurity)); break; case "qp_real_content_list": siteId = !string.IsNullOrWhiteSpace(realParentIdField) ? (decimal?)realParentId.Value : parentEntityId; sqlSb.AppendLine(GetContentListSql(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), false, siteId, userId, useSecurity)); break; case "qp_virtual_content_list": siteId = realParentId.HasValue ? (decimal?)realParentId.Value : parentEntityId; sqlSb.AppendLine(GetContentListSql(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), true, siteId, userId, useSecurity)); break; case "qp_site_folder_list": siteId = realParentId.HasValue ? (decimal?)realParentId.Value : parentEntityId; var parentFolderId = newIsFolder ? 0 : parentEntityId.Value; sqlSb.AppendLine(GetSiteFolderList(context, selectSb.ToString(), whereSb.ToString(), orderSb.ToString(), siteId, parentFolderId, userId, useSecurity)); break; } } if (countOnly) { return(string.IsNullOrWhiteSpace(sqlSb.ToString()) ? null : $"SELECT COUNT(ID) FROM ({sqlSb}) as innerSql"); } if (string.IsNullOrWhiteSpace(sqlSb.ToString())) { return(null); } sql = " SELECT\n" + $"{realParentIdStr} as parent_id,\n" + $"{(isGroup ? $"{parentEntityId}" : "NULL")} as parent_group_id,\n" + $"'{newEntityTypeCode}' as code,\n" + $"{SqlQuerySyntaxHelper.ToBoolSql(databaseType, false)} as is_folder,\n" + $"{SqlQuerySyntaxHelper.ToBoolSql(databaseType, currentIsGroup)} as is_group,\n" + $"{(!string.IsNullOrWhiteSpace(currentGroupItemCode) ? $"'{currentGroupItemCode}'" : "NULL")} as group_item_code,\n" + "CASE WHEN i.ICON is not null THEN i.ICON\n" + $"WHEN i.ICON_MODIFIER is not null THEN {SqlQuerySyntaxHelper.ConcatStrValues(databaseType, $"'{newEntityTypeCode}'", SqlQuerySyntaxHelper.CastToString(databaseType, "i.ICON_MODIFIER"), "'.gif'")}\n" + $"ELSE {SqlQuerySyntaxHelper.ConcatStrValues(databaseType, $"'{newEntityTypeCode}'", "'.gif'")} END\n" + "AS icon,\n" + $"{SqlQuerySyntaxHelper.NullableDbValue(databaseType, newEntityType?.DefaultActionId)} AS default_action_id,\n" + $"{SqlQuerySyntaxHelper.NullableDbValue(databaseType, newEntityType?.ContextMenuId)} as context_menu_id,\n" + $"{SqlQuerySyntaxHelper.ToBoolSql(databaseType, !string.IsNullOrWhiteSpace(newEntityType?.RecurringIdField))} as is_recurring,\n" + "i.id,\n" + "i.title,\n" + "i.sortorder\n" + $"FROM ( {sqlSb} ) as i\n"; }
public static string GetActionPermissionsAsQuery(QPModelDataContext context, decimal userId) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(context); var actionSecQuery = GetPermittedItemsAsQuery(context, userId, startLevel: 0, endLevel: 100, entityTypeName: "BACKEND_ACTION" ); var entitySecQuery = GetEntityPermissionAsQuery(context, userId); var query = $@" select AP.BACKEND_ACTION_ID, COALESCE(AP.PERMISSION_LEVEL, EP.PERMISSION_LEVEL, 0) AS PERMISSION_LEVEL from (select L.PERMISSION_LEVEL AS PERMISSION_LEVEL, T.ID AS BACKEND_ACTION_ID, T.ENTITY_TYPE_ID FROM ({actionSecQuery}) P1 LEFT JOIN backend_action_access_permlevel P2 ON P1.BACKEND_ACTION_ID = P2.BACKEND_ACTION_ID and P1.permission_level = p2.permission_level and P2.{SqlQuerySyntaxHelper.EscapeEntityName(databaseType, "USER_ID")} = {userId} RIGHT JOIN BACKEND_ACTION T ON P1.BACKEND_ACTION_ID = T.ID LEFT join PERMISSION_LEVEL L ON P1.PERMISSION_LEVEL = L.PERMISSION_LEVEL ) AP JOIN ({entitySecQuery}) EP ON AP.ENTITY_TYPE_ID = EP.ENTITY_TYPE_ID "; return(query); }
internal static DbParameter GetIdsDatatableParam(string paramName, IEnumerable <int> ids, DatabaseType databaseType = DatabaseType.SqlServer) => SqlQuerySyntaxHelper.GetIdsDatatableParam(paramName, ids, databaseType);