/// <summary> /// Returns permissions for a given user for any number of nodes /// </summary> /// <param name="userId"></param> /// <param name="entityIds"></param> /// <returns></returns> public IEnumerable <EntityPermission> GetUserPermissionsForEntities(int userId, params int[] entityIds) { var entityIdKey = string.Join(",", entityIds.Select(x => x.ToString(CultureInfo.InvariantCulture))); return(_runtimeCache.GetCacheItem <IEnumerable <EntityPermission> >( string.Format("{0}{1}{2}", CacheKeys.UserPermissionsCacheKey, userId, entityIdKey), () => { var whereBuilder = new StringBuilder(); //where userId = @userId AND whereBuilder.Append(_sqlSyntax.GetQuotedColumnName("userId")); whereBuilder.Append("="); whereBuilder.Append(userId); if (entityIds.Any()) { whereBuilder.Append(" AND "); //where nodeId = @nodeId1 OR nodeId = @nodeId2, etc... whereBuilder.Append("("); for (var index = 0; index < entityIds.Length; index++) { var entityId = entityIds[index]; whereBuilder.Append(_sqlSyntax.GetQuotedColumnName("nodeId")); whereBuilder.Append("="); whereBuilder.Append(entityId); if (index < entityIds.Length - 1) { whereBuilder.Append(" OR "); } } whereBuilder.Append(")"); } var sql = new Sql(); sql.Select("*") .From <User2NodePermissionDto>() .Where(whereBuilder.ToString()); //ToArray() to ensure it's all fetched from the db once. var result = _unitOfWork.Database.Fetch <User2NodePermissionDto>(sql).ToArray(); return ConvertToPermissionList(result); }, //Since this cache can be quite large (http://issues.umbraco.org/issue/U4-2161) we will only have this exist in cache for 20 minutes, // then it will refresh from the database. new TimeSpan(0, 20, 0), //Since this cache can be quite large (http://issues.umbraco.org/issue/U4-2161) we will make this priority below average priority: CacheItemPriority.BelowNormal)); }
public static Sql GroupBy <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlProvider) { var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo; var columnName = column.GetColumnName(); return(sql.GroupBy(sqlProvider.GetQuotedColumnName(columnName))); }
/// <summary> /// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a very particular syntax that /// needs to be used to work for all servers: MySql, SQLCE and MSSQL /// </summary> /// <returns></returns> /// <remarks> /// See: http://issues.umbraco.org/issue/U4-3876 /// </remarks> public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery) { return(new Sql(string.Format(@"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)", sqlProvider.GetQuotedTableName(tableName), sqlProvider.GetQuotedColumnName(columnName), subQuery.SQL), subQuery.Arguments)); }
private static string GetFieldName <T>(Expression <Func <T, object> > fieldSelector, ISqlSyntaxProvider sqlSyntax) { var field = ExpressionHelper.FindProperty(fieldSelector) as PropertyInfo; var fieldName = field.GetColumnName(); var type = typeof(T); var tableName = type.GetTableName(); return(sqlSyntax.GetQuotedTableName(tableName) + "." + sqlSyntax.GetQuotedColumnName(fieldName)); }
/// <summary> /// Gets a quoted table and field name. /// </summary> /// <typeparam name="TDto">The type of the DTO.</typeparam> /// <param name="sqlSyntax">An <see cref="ISqlSyntaxProvider"/>.</param> /// <param name="fieldSelector">An expression specifying the field.</param> /// <param name="tableAlias">An optional table alias.</param> /// <returns></returns> public static string GetFieldName <TDto>(this ISqlSyntaxProvider sqlSyntax, Expression <Func <TDto, object> > fieldSelector, string tableAlias = null) { var field = ExpressionHelper.FindProperty(fieldSelector).Item1 as PropertyInfo; var fieldName = field.GetColumnName(); var type = typeof(TDto); var tableName = tableAlias ?? type.GetTableName(); return(sqlSyntax.GetQuotedTableName(tableName) + "." + sqlSyntax.GetQuotedColumnName(fieldName)); }
/// <summary> /// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a /// very particular syntax that /// needs to be used to work for all servers /// </summary> /// <returns></returns> /// <remarks> /// See: http://issues.umbraco.org/issue/U4-3876 /// </remarks> public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery, WhereInType whereInType = WhereInType.In) => // TODO: This is no longer necessary since this used to be a specific requirement for MySql! // Now we can do a Delete<T> + sub query, see RelationRepository.DeleteByParent for example new Sql( string.Format( whereInType == WhereInType.In ? @"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)" : @"DELETE FROM {0} WHERE {1} NOT IN (SELECT {1} FROM ({2}) x)", sqlProvider.GetQuotedTableName(tableName), sqlProvider.GetQuotedColumnName(columnName), subQuery.SQL), subQuery.Arguments);
public static Sql On <TLeft, TRight>(this Sql.SqlJoinClause sql, ISqlSyntaxProvider sqlSyntax, Expression <Func <TLeft, object> > leftMember, Expression <Func <TRight, object> > rightMember, params object[] args) { var leftType = typeof(TLeft); var rightType = typeof(TRight); var leftTableName = leftType.FirstAttribute <TableNameAttribute>().Value; var rightTableName = rightType.FirstAttribute <TableNameAttribute>().Value; var left = ExpressionHelper.FindProperty(leftMember) as PropertyInfo; var right = ExpressionHelper.FindProperty(rightMember) as PropertyInfo; var leftColumnName = left.FirstAttribute <ColumnAttribute>().Name; var rightColumnName = right.FirstAttribute <ColumnAttribute>().Name; string onClause = string.Format("{0}.{1} = {2}.{3}", sqlSyntax.GetQuotedTableName(leftTableName), sqlSyntax.GetQuotedColumnName(leftColumnName), sqlSyntax.GetQuotedTableName(rightTableName), sqlSyntax.GetQuotedColumnName(rightColumnName)); return(sql.On(onClause)); }
internal virtual string GetColumnName(ISqlSyntaxProvider sqlSyntax, Type dtoType, PropertyInfo dtoProperty) { var tableNameAttribute = dtoType.FirstAttribute <TableNameAttribute>(); var tableName = tableNameAttribute.Value; var columnAttribute = dtoProperty.FirstAttribute <ColumnAttribute>(); var columnName = columnAttribute.Name; var columnMap = sqlSyntax.GetQuotedTableName(tableName) + "." + sqlSyntax.GetQuotedColumnName(columnName); return(columnMap); }
internal virtual string GetColumnName(Type dtoType, PropertyInfo dtoProperty) { var tableNameAttribute = dtoType.FirstAttribute <TableNameAttribute>(); string tableName = tableNameAttribute.Value; var columnAttribute = dtoProperty.FirstAttribute <ColumnAttribute>(); string columnName = columnAttribute.Name; string columnMap = string.Format("{0}.{1}", _sqlSyntax.GetQuotedTableName(tableName), _sqlSyntax.GetQuotedColumnName(columnName)); return(columnMap); }
public static Sql OrderByDescending <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlSyntax) { var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo; var columnName = column.FirstAttribute <ColumnAttribute>().Name; var type = typeof(TColumn); var tableNameAttribute = type.FirstAttribute <TableNameAttribute>(); string tableName = tableNameAttribute == null ? string.Empty : tableNameAttribute.Value; var syntax = string.Format("{0}.{1} DESC", sqlSyntax.GetQuotedTableName(tableName), sqlSyntax.GetQuotedColumnName(columnName)); return(sql.OrderBy(syntax)); }
public static Sql OrderByDescending <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlSyntax) { var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo; var columnName = column.GetColumnName(); var type = typeof(TColumn); var tableName = type.GetTableName(); //need to ensure the order by is in brackets, see: https://github.com/toptensoftware/PetaPoco/issues/177 var syntax = string.Format("({0}.{1}) DESC", sqlSyntax.GetQuotedTableName(tableName), sqlSyntax.GetQuotedColumnName(columnName)); return(sql.OrderBy(syntax)); }
public static Sql OrderBy <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlSyntax) { var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo; var columnName = column.FirstAttribute <ColumnAttribute>().Name; var type = typeof(TColumn); var tableNameAttribute = type.FirstAttribute <TableNameAttribute>(); string tableName = tableNameAttribute == null ? string.Empty : tableNameAttribute.Value; //need to ensure the order by is in brackets, see: https://github.com/toptensoftware/PetaPoco/issues/177 var syntax = string.Format("({0}.{1})", sqlSyntax.GetQuotedTableName(tableName), sqlSyntax.GetQuotedColumnName(columnName)); return(sql.OrderBy(syntax)); }
/// <summary> /// Gets the basic tag SQL used to retrieve tags for a given articulate root node /// </summary> /// <param name="selectCols"></param> /// <param name="masterModel"></param> /// <param name="sqlSyntax"></param> /// <returns></returns> /// <remarks> /// TODO: We won't need this when this is fixed http://issues.umbraco.org/issue/U4-9290 /// </remarks> private static Sql GetTagQuery(string selectCols, IMasterModel masterModel, ISqlSyntaxProvider sqlSyntax) { var sql = new Sql() .Select(selectCols) .From(Constants.DatabaseSchema.Tables.Tag) .InnerJoin(Constants.DatabaseSchema.Tables.TagRelationship) .On($"{Constants.DatabaseSchema.Tables.TagRelationship}.tagId = {Constants.DatabaseSchema.Tables.Tag}.id") .InnerJoin(Constants.DatabaseSchema.Tables.Content) .On($"{Constants.DatabaseSchema.Tables.Content}.nodeId = {Constants.DatabaseSchema.Tables.TagRelationship}.nodeId") .InnerJoin(Constants.DatabaseSchema.Tables.Node) .On($"{Constants.DatabaseSchema.Tables.Node}.id = {Constants.DatabaseSchema.Tables.Content}.nodeId") .Where($"{Constants.DatabaseSchema.Tables.Node}.nodeObjectType = @nodeObjectType", new { nodeObjectType = Constants.ObjectTypes.Document }) //only get nodes underneath the current articulate root .Where($"{Constants.DatabaseSchema.Tables.Node}." + sqlSyntax.GetQuotedColumnName("path") + " LIKE @path", new { path = masterModel.RootBlogNode.Path + ",%" }); return(sql); }
/// <summary> /// Gets the basic tag SQL used to retrieve tags for a given articulate root node /// </summary> /// <param name="selectCols"></param> /// <param name="masterModel"></param> /// <param name="sqlSyntax"></param> /// <returns></returns> /// <remarks> /// TODO: We won't need this when this is fixed http://issues.umbraco.org/issue/U4-9290 /// </remarks> private static Sql GetTagQuery(string selectCols, IMasterModel masterModel, ISqlSyntaxProvider sqlSyntax) { var sql = new Sql() .Select(selectCols) .From("cmsTags") .InnerJoin("cmsTagRelationship") .On("cmsTagRelationship.tagId = cmsTags.id") .InnerJoin("cmsContent") .On("cmsContent.nodeId = cmsTagRelationship.nodeId") .InnerJoin("umbracoNode") .On("umbracoNode.id = cmsContent.nodeId") .Where("umbracoNode.nodeObjectType = @nodeObjectType", new { nodeObjectType = Constants.ObjectTypes.Document }) //only get nodes underneath the current articulate root .Where("umbracoNode." + sqlSyntax.GetQuotedColumnName("path") + " LIKE @path", new { path = masterModel.RootBlogNode.Path + ",%" }); return(sql); }
/// <summary> /// Gets the tag SQL used to retrieve paged posts for particular tags for a given articulate root node /// </summary> /// <param name="selectCols"></param> /// <param name="masterModel"></param> /// <param name="sqlSyntax"></param> /// <param name="publishedDatePropertyTypeId"> /// This is needed to perform the sorting on published date, this is the PK of the property type for publishedDate on the ArticulatePost content type /// </param> /// <returns></returns> /// <remarks> /// TODO: We won't need this when this is fixed http://issues.umbraco.org/issue/U4-9290 /// </remarks> private static Sql GetContentByTagQueryForPaging(string selectCols, IMasterModel masterModel, ISqlSyntaxProvider sqlSyntax, int publishedDatePropertyTypeId) { var sql = new Sql() .Select(selectCols) .From("umbracoNode") .InnerJoin("cmsDocument") .On("cmsDocument.nodeId = umbracoNode.id") .InnerJoin("cmsPropertyData") .On("cmsPropertyData.versionId = cmsDocument.versionId") .Where("umbracoNode.nodeObjectType = @nodeObjectType", new { nodeObjectType = Constants.ObjectTypes.Document }) //Must be published, this will ensure there's only one version selected .Where("cmsDocument.published = 1") //must only return rows with the publishedDate property data so we only get one row and so we can sort on `cmsPropertyData.dataDate` which will be the publishedDate .Where("cmsPropertyData.propertytypeid = @propTypeId", new { propTypeId = publishedDatePropertyTypeId }) //only get nodes underneath the current articulate root .Where("umbracoNode." + sqlSyntax.GetQuotedColumnName("path") + " LIKE @path", new { path = masterModel.RootBlogNode.Path + ",%" }); return(sql); }
// fixme: TSource is used for nothing protected void DefineMap <TSource, TTarget>(string sourceName, string targetName) { if (_sqlSyntax == null) { throw new InvalidOperationException("Do not define maps outside of DefineMaps."); } var targetType = typeof(TTarget); // TODO ensure that sourceName is a valid sourceType property (but, slow?) var tableNameAttribute = targetType.FirstAttribute <TableNameAttribute>(); if (tableNameAttribute == null) { throw new InvalidOperationException($"Type {targetType.FullName} is not marked with a TableName attribute."); } var tableName = tableNameAttribute.Value; // TODO maybe get all properties once and then index them var targetProperty = targetType.GetProperty(targetName); if (targetProperty == null) { throw new InvalidOperationException($"Type {targetType.FullName} does not have a property named {targetName}."); } var columnAttribute = targetProperty.FirstAttribute <ColumnAttribute>(); if (columnAttribute == null) { throw new InvalidOperationException($"Property {targetType.FullName}.{targetName} is not marked with a Column attribute."); } var columnName = columnAttribute.Name; var columnMap = _sqlSyntax.GetQuotedTableName(tableName) + "." + _sqlSyntax.GetQuotedColumnName(columnName); var mapperMaps = _maps.GetOrAdd(GetType(), type => new ConcurrentDictionary <string, string>()); mapperMaps[sourceName] = columnMap; }
/// <summary> /// Gets a dictionary of key/values directly from the database, no scope, nothing. /// </summary> /// <remarks>Used by <see cref="CoreRuntimeBootstrapper"/> to determine the runtime state.</remarks> public static IReadOnlyDictionary <string, string?>?GetFromKeyValueTable(this IUmbracoDatabase database, string keyPrefix) { if (database is null) { return(null); } // create the wildcard where clause ISqlSyntaxProvider sqlSyntax = database.SqlContext.SqlSyntax; var whereParam = sqlSyntax.GetStringColumnWildcardComparison( sqlSyntax.GetQuotedColumnName("key"), 0, TextColumnType.NVarchar); var sql = database.SqlContext.Sql() .Select <KeyValueDto>() .From <KeyValueDto>() .Where(whereParam, keyPrefix + sqlSyntax.GetWildcardPlaceholder()); return(database.Fetch <KeyValueDto>(sql) .ToDictionary(x => x.Key !, x => x.Value)); }
/// <summary> /// Returns the quotes tableName.columnName combo /// </summary> /// <param name="sql"></param> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <returns></returns> public static string GetQuotedColumn(this ISqlSyntaxProvider sql, string tableName, string columnName) { return(sql.GetQuotedTableName(tableName) + "." + sql.GetQuotedColumnName(columnName)); }
/// <summary> /// Checks the cmsContentXml table to see if the number of entries for media matches the number of media entities /// </summary> /// <returns></returns> /// <remarks> /// Further to just counting the data integrity, this also checks if the XML stored in the cmsContentXml table contains the correct /// GUID identifier. In older versions of Umbraco, the GUID is not persisted to the content cache, that will cause problems in newer /// versions of Umbraco, so the xml table would need a rebuild. /// </remarks> private HealthCheckStatus CheckMedia() { var total = _services.MediaService.CountNotTrashed(); var mediaObjectType = Guid.Parse(Constants.ObjectTypes.Media); //count entries var countTotalSubQuery = new Sql() .Select("Count(*)") .From <ContentXmlDto>(_sqlSyntax) .InnerJoin <NodeDto>(_sqlSyntax) .On <ContentXmlDto, NodeDto>(_sqlSyntax, left => left.NodeId, right => right.NodeId) .Where <NodeDto>(dto => dto.NodeObjectType == mediaObjectType); var totalXml = _database.ExecuteScalar <int>(countTotalSubQuery); //count invalid entries var countNonGuidQuery = new Sql() .Select("Count(*)") .From <ContentXmlDto>(_sqlSyntax) .InnerJoin <NodeDto>(_sqlSyntax) .On <ContentXmlDto, NodeDto>(_sqlSyntax, left => left.NodeId, right => right.NodeId) .Where <NodeDto>(dto => dto.NodeObjectType == mediaObjectType) .Where(string.Format("{0}.{1} NOT LIKE '% key=\"%'", _sqlSyntax.GetQuotedTableName("cmsContentXml"), _sqlSyntax.GetQuotedColumnName("xml"))); var totalNonGuidXml = _database.ExecuteScalar <int>(countNonGuidQuery); var hasError = false; var actions = new List <HealthCheckAction>(); if (totalXml != total || totalNonGuidXml > 0) { //if the counts don't match actions.Add(new HealthCheckAction(CheckMediaXmlTableAction, Id)); hasError = true; } return(new HealthCheckStatus(_textService.Localize("healthcheck/xmlDataIntegrityCheckMedia", new[] { totalXml.ToString(), total.ToString(), totalNonGuidXml.ToString() })) { ResultType = hasError == false ? StatusResultType.Success : StatusResultType.Error, Actions = actions }); }
/// <summary> /// Gets the basic tag SQL used to retrieve tags for a given articulate root node /// </summary> /// <param name="selectCols"></param> /// <param name="masterModel"></param> /// <param name="sqlSyntax"></param> /// <returns></returns> /// <remarks> /// TODO: We won't need this when this is fixed http://issues.umbraco.org/issue/U4-9290 /// </remarks> private static Sql GetTagQuery(string selectCols, IMasterModel masterModel, ISqlSyntaxProvider sqlSyntax) { var sql = new Sql() .Select(selectCols) .From("cmsTags") .InnerJoin("cmsTagRelationship") .On("cmsTagRelationship.tagId = cmsTags.id") .InnerJoin("cmsContent") .On("cmsContent.nodeId = cmsTagRelationship.nodeId") .InnerJoin("umbracoNode") .On("umbracoNode.id = cmsContent.nodeId") .Where("umbracoNode.nodeObjectType = @nodeObjectType", new {nodeObjectType = Constants.ObjectTypes.Document}) //only get nodes underneath the current articulate root .Where("umbracoNode." + sqlSyntax.GetQuotedColumnName("path") + " LIKE @path", new {path = masterModel.RootBlogNode.Path + ",%"}); return sql; }