/// <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;
}
