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); }
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); }
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 UpdateContentModification(DbConnection sqlConnection, List <int> liveIds, List <int> stageIds) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var sql = new List <DbParameter>(); var sb = new StringBuilder(); ; if (liveIds.Any()) { sb.AppendLine($@"update content_modification {WithRowLock(dbType)} set live_modified = {Now(dbType)} where content_id in (select id from {IdList(dbType, "@liveIds")});"); sql.Add(GetIdsDatatableParam("@liveIds", liveIds, dbType)); } if (stageIds.Any()) { sb.AppendLine($@"update content_modification {WithRowLock(dbType)} set stage_modified = {Now(dbType)} where content_id in (select id from {IdList(dbType, "@stageIds")});"); sql.Add(GetIdsDatatableParam("@stageIds", stageIds, dbType)); } if (sql.Any()) { using (var cmd = DbCommandFactory.Create(sb.ToString(), sqlConnection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(sql.ToArray()); cmd.ExecuteNonQuery(); } } }
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(); } }
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 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 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 Guid[] GetArticleGuidsByIds(DbConnection sqlConnection, int[] ids) { if (ids == null) { throw new ArgumentNullException(nameof(ids)); } var databaseType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var table = IdList(databaseType, "@ids", "ids"); var noLock = WithNoLock(databaseType); var query = $@"select ci.unique_id, ids.id from {table} left join content_item ci {noLock} on ci.content_item_id = ids.id"; using (var cmd = DbCommandFactory.Create(query, sqlConnection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, databaseType)); var result = new Dictionary <int, Guid>(); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { result.Add((int)dr.GetDecimal(1), dr.IsDBNull(0) ? Guid.Empty : dr.GetGuid(0)); } } return(ids.Select(n => result[n]).ToArray()); } }
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 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(); } }
public static void LockArticleForUpdate(DbConnection cnn, int id) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(cnn); var withlock = databaseType == DatabaseType.SqlServer ? "with(rowlock, updlock)" : ""; var forUpdate = databaseType != DatabaseType.SqlServer ? "for update" : ""; using (var cmd = DbCommandFactory.Create($@"select content_item_id from content_item {withlock} where content_item_id = @id {forUpdate}", cnn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@id", id); var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); } }
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 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 DeleteArticles(DbConnection connection, List <int> ids, bool withAggregated) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection); var idParam = withAggregated ? $@"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids"; var source = IdList(dbType, idParam); if (ids != null && ids.Any()) { var query = $"DELETE FROM content_item {WithRowLock(dbType)} where content_item_id in (select id from {source}) "; using (var cmd = DbCommandFactory.Create(query, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, dbType)); cmd.ExecuteNonQuery(); } } }
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 string GetConflictIds(DbConnection connection, int id, int contentId, string condition, List <FieldParameter> parameters) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection); using (var cmd = DbCommandFactory.Create($"SELECT CONTENT_ITEM_ID FROM CONTENT_{contentId}_UNITED WHERE {condition} AND CONTENT_ITEM_ID <> @id", connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@id", id); foreach (var parameter in parameters) { cmd.Parameters.Add(CreateDbParameter(databaseType, parameter)); } var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); return(IdCommaList(dt, FieldName.ContentItemId)); } }
public static void SetArchiveFlag(DbConnection connection, IEnumerable <int> articleIds, int userId, bool flag, bool withAggregated) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection); var idParam = withAggregated ? $@"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids"; var source = IdList(dbType, idParam); var value = flag ? 1 : 0; using (var cmd = DbCommandFactory.Create($@" update content_item {WithRowLock(dbType)} set archive = {value}, modified = {Now(dbType)}, last_modified_by = @userId where content_item_id in (select id from {source}); update content_item {WithRowLock(dbType)} set locked_by = null, locked = null where content_item_id in (select id from {source}); ", connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.Add(GetIdsDatatableParam("@ids", articleIds, dbType)); cmd.ExecuteNonQuery(); } }
public static string GetEntityPermissionAsQuery(QPModelDataContext context, decimal userId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(context); var isPostgres = dbType == DatabaseType.Postgres; var entitySecQuery = GetPermittedItemsAsQuery(context, userId, startLevel: 0, endLevel: 100, entityTypeName: "entity_type"); var permissionTable = "entity_type_access_permlevel"; if (isPostgres) { permissionTable = permissionTable.ToSnakeCase(); } return($@" select COALESCE(L.PERMISSION_LEVEL, 0) AS PERMISSION_LEVEL, T.ID AS ENTITY_TYPE_ID, HIDE FROM ({entitySecQuery}) P1 LEFT JOIN {permissionTable} P2 ON P1.entity_type_id = P2.entity_type_id and P1.permission_level = p2.permission_level and P2.USER_ID = {userId} RIGHT JOIN ENTITY_TYPE T ON P1.ENTITY_TYPE_ID = T.ID LEFT join PERMISSION_LEVEL L ON P1.PERMISSION_LEVEL = L.PERMISSION_LEVEL" ); }
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 DataTable GetFieldTypes(DbConnection cnn, int[] ids) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(cnn); var textType = dbType == DatabaseType.SqlServer ? "nvarchar(max)" : "text"; var text = $@"select attribute_id, BACK_RELATED_ATTRIBUTE_ID, attribute_type_id, link_id, is_classifier, cast(case when coalesce(cast(enum_values as {textType}), '') <> '' then 1 else 0 end as bit) as is_string_enum from content_attribute where attribute_id in (select id from {Common.IdList(dbType, "@ids")})"; using (var cmd = DbCommandFactory.Create(text, cnn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add(Common.GetIdsDatatableParam("@ids", ids, dbType)); var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); return(dt); } }
public static int CountChildArticles(DbConnection sqlConnection, int articleId, bool countArchived) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var sql = "qp_count_child_articles"; if (dbType == DatabaseType.Postgres) { sql = $@" select count(*)::int from content_data cd inner join content_attribute ca on ca.attribute_id = cd.attribute_id inner join content_item ci on ci.content_item_id = cd.content_item_id where o2m_data = @article_id and not ca.aggregated and ci.archive = 0 "; } using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { string outParam; cmd.Parameters.AddWithValue("@article_id", articleId); if (dbType == DatabaseType.SqlServer) { outParam = "@count"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@count_archived", countArchived); cmd.Parameters.Add(new SqlParameter(outParam, SqlDbType.Int) { Direction = ParameterDirection.Output }); } else { outParam = "count"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new NpgsqlParameter(outParam, NpgsqlDbType.Integer) { Direction = ParameterDirection.Output }); } cmd.ExecuteNonQuery(); return((int)cmd.Parameters[outParam].Value); } }
public static void AdjustManyToMany(DbConnection connection, int id, int newId) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection); var sql = dbType == DatabaseType.SqlServer ? $@" update item_to_item {WithRowLock(dbType)} set l_item_id = @newId where l_item_id = @id and r_item_id = @newId; delete from item_to_item {WithRowLock(dbType)} where r_item_id = @id and l_item_id = @newId; " : $@" insert into item_to_item(link_id, l_item_id, r_item_id) select link_id, @newId, @newId from item_to_item where l_item_id = @newId and r_item_id = @id; delete from item_to_item where l_item_id = @newId and r_item_id = @id; "; using (var cmd = DbCommandFactory.Create(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@newId", newId); cmd.ExecuteNonQuery(); } }
public static DataRow GetDefaultArticleRow(QPModelDataContext context, DbConnection connection, int contentId) { var databaseType = DatabaseTypeHelper.ResolveDatabaseType(connection); var sql = "qp_get_default_article"; if (databaseType == DatabaseType.Postgres) { var fields = context.FieldSet.Where(n => n.ContentId == contentId).OrderBy(n => n.Name).ToArray(); var fieldNames = fields.Select(n => n.Name.ToLower()).ToArray(); var fieldNameResults = String.Join(",", fieldNames.Select(n => $@"""{n}"" TEXT")); var fieldSelects = String.Join(",", fields.Select(n => $@"""{n.Name.ToLower()}""::{PgSelectType((int)n.TypeId)}")); sql = $@" SELECT {fieldSelects} FROM crosstab(' select 0::numeric as content_item_id, lower(ca.attribute_name), case when ca.attribute_type_id in (9, 10) then coalesce(ca.default_value, ca.default_blob_value) else qp_correct_data(ca.default_value::text, ca.attribute_type_id, ca.attribute_size, ca.default_value)::text end as value from content_attribute ca inner join content c on ca.content_id = c.content_id where c.content_id = {contentId} order by 1,2 ') AS final_result(content_item_id numeric, {fieldNameResults})" ; } using (var cmd = DbCommandFactory.Create(sql, connection)) { if (databaseType == DatabaseType.Postgres) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@content_id", contentId); } var ds = new DataSet(); DataAdapterFactory.Create(cmd).Fill(ds); return(0 == ds.Tables.Count || 0 == ds.Tables[0].Rows.Count ? null : ds.Tables[0].Rows[0]); } }
public static void GetContentModification(DbConnection sqlConnection, IEnumerable <int> articleIds, bool withAggregated, bool returnPublishedForLive, ref List <int> liveIds, ref List <int> stageIds) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection); var idsParam = withAggregated ? $"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids"; var source = IdList(dbType, idsParam); var aggFunc = returnPublishedForLive ? "max" : "min"; var ids = articleIds as int[] ?? articleIds.ToArray(); if (!ids.Any()) { return; } var sql = $@" select cast(a.content_id as int) as content_id, cast({aggFunc}(a.is_published) as bit) as is_published from ( select ci.content_item_id, ci.content_id, case when st.status_type_name = 'Published' and {IsFalse(dbType, "ci.splitted")} then 1 else 0 end as is_published from {source} inner join content_item ci {WithNoLock(dbType)} on i.id = ci.content_item_id inner join status_type st on ci.status_type_id = st.status_type_id ) a group by a.content_id "; using (var cmd = DbCommandFactory.Create(sql, sqlConnection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add(GetIdsDatatableParam("@ids", ids, dbType)); var dt = new DataTable(); DataAdapterFactory.Create(cmd).Fill(dt); var rows = dt.AsEnumerable().ToArray(); stageIds = rows.Select(n => n.Field <int>("content_id")).ToList(); bool Predicate1(DataRow n) => n.Field <bool>("is_published"); bool Predicate2(DataRow n) => !n.Field <bool>("is_published"); liveIds = rows.Where(returnPublishedForLive ? (Func <DataRow, bool>)Predicate1 : Predicate2).Select(n => n.Field <int>("content_id")).ToList(); } }
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 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 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(); } }
/// <summary> /// Массовая публикация (может использоваться для статей из разных контентов одного сайта) /// </summary> public static void Publish(DbConnection connection, IEnumerable <int> articleIds, int userId, bool withAggregated) { var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection); var idParam = withAggregated ? $@"{DbSchemaName(dbType)}.qp_aggregated_and_self(@ids)" : "@ids"; var source = IdList(dbType, idParam); string sql = $@"call qp_publish(@ids, @userId);"; if (dbType == DatabaseType.SqlServer) { sql = $@" declare @ids2 [Ids] insert into @ids2 select id from {source} declare @statusTypeId numeric select @statusTypeId = status_type_id from status_type where status_type_name = 'Published' and site_id in (select site_id from content c inner join content_item ci with(nolock) on c.content_id = ci.content_id inner join @ids2 i on i.id = ci.content_item_id ) update content_item with(rowlock) set status_type_id = @statusTypeId, modified = getdate(), last_modified_by = @userId where content_item_id in (select id from @ids2) and status_type_id <> @statusTypeId and splitted = 0; update content_item with(rowlock) set status_type_id = @statusTypeId, modified = getdate(), last_modified_by = @userId, schedule_new_version_publication = 1 where content_item_id in (select id from @ids2) and status_type_id <> @statusTypeId and splitted = 1; exec qp_merge_delays @ids2, @userId delete i from @ids2 i inner join content_item ci with(nolock) on ci.content_item_id = i.id where ci.splitted = 0 and ci.schedule_new_version_publication = 0 exec qp_merge_articles @ids2, @userId "; } using (var cmd = DbCommandFactory.Create(sql, connection)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.Add(GetIdsDatatableParam("@ids", articleIds, dbType)); cmd.ExecuteNonQuery(); } }