private PropertyCollection GetPropertyCollection(int id, Guid versionId, IMediaType contentType, DateTime createDate, DateTime updateDate)
{
var sql = new Sql();
sql.Select("*")
.From <PropertyDataDto>()
.InnerJoin <PropertyTypeDto>()
.On <PropertyDataDto, PropertyTypeDto>(left => left.PropertyTypeId, right => right.Id)
.Where <PropertyDataDto>(x => x.NodeId == id)
.Where <PropertyDataDto>(x => x.VersionId == versionId);
var propertyDataDtos = Database.Fetch <PropertyDataDto, PropertyTypeDto>(sql);
var propertyFactory = new PropertyFactory(contentType, versionId, id, createDate, updateDate);
var properties = propertyFactory.BuildEntity(propertyDataDtos);
var newProperties = properties.Where(x => x.HasIdentity == false);
foreach (var property in newProperties)
{
var propertyDataDto = new PropertyDataDto {
NodeId = id, PropertyTypeId = property.PropertyTypeId, VersionId = versionId
};
int primaryKey = Convert.ToInt32(Database.Insert(propertyDataDto));
property.Version = versionId;
property.Id = primaryKey;
}
return(new PropertyCollection(properties));
}
/// <summary>
/// Gets the property collection for a query
/// </summary>
/// <param name="pagingSqlQuery"></param>
/// <param name="documentDefs"></param>
/// <returns></returns>
protected IDictionary <Guid, PropertyCollection> GetPropertyCollection(
PagingSqlQuery pagingSqlQuery,
IReadOnlyCollection <DocumentDefinition> documentDefs)
{
if (documentDefs.Count == 0)
{
return(new Dictionary <Guid, PropertyCollection>());
}
//initialize to the query passed in
var docSql = pagingSqlQuery.PrePagedSql;
//we need to parse the original SQL statement and reduce the columns to just cmsContent.nodeId, cmsContentVersion.VersionId so that we can use
// the statement to go get the property data for all of the items by using an inner join
var parsedOriginalSql = "SELECT {0} " + docSql.SQL.Substring(docSql.SQL.IndexOf("FROM", StringComparison.Ordinal));
if (pagingSqlQuery.HasPaging)
{
//if this is a paged query, build the paged query with the custom column substitution, then re-assign
docSql = pagingSqlQuery.BuildPagedQuery("{0}");
parsedOriginalSql = docSql.SQL;
}
else if (parsedOriginalSql.InvariantContains("ORDER BY "))
{
//now remove everything from an Orderby clause and beyond if this is unpaged data
parsedOriginalSql = parsedOriginalSql.Substring(0, parsedOriginalSql.LastIndexOf("ORDER BY ", StringComparison.Ordinal));
}
//This retrieves all pre-values for all data types that are referenced for all property types
// that exist in the data set.
//Benchmarks show that eagerly loading these so that we can lazily read the property data
// below (with the use of Query intead of Fetch) go about 30% faster, so we'll eagerly load
// this now since we cannot execute another reader inside of reading the property data.
var preValsSql = new Sql(@"SELECT a.id, a.value, a.sortorder, a.alias, a.datatypeNodeId
FROM cmsDataTypePreValues a
WHERE EXISTS(
SELECT DISTINCT b.id as preValIdInner
FROM cmsDataTypePreValues b
INNER JOIN cmsPropertyType
ON b.datatypeNodeId = cmsPropertyType.dataTypeId
INNER JOIN
(" + string.Format(parsedOriginalSql, "cmsContent.contentType") + @") as docData
ON cmsPropertyType.contentTypeId = docData.contentType
WHERE a.id = b.id)", docSql.Arguments);
var allPreValues = Database.Fetch <DataTypePreValueDto>(preValsSql);
//It's Important with the sort order here! We require this to be sorted by node id,
// this is required because this data set can be huge depending on the page size. Due
// to it's size we need to be smart about iterating over the property values to build
// the document. Before we used to use Linq to get the property data for a given content node
// and perform a Distinct() call. This kills performance because that would mean if we had 7000 nodes
// and on each iteration we will perform a lookup on potentially 100,000 property rows against the node
// id which turns out to be a crazy amount of iterations. Instead we know it's sorted by this value we'll
// keep an index stored of the rows being read so we never have to re-iterate the entire data set
// on each document iteration.
var propSql = new Sql(@"SELECT cmsPropertyData.*
FROM cmsPropertyData
INNER JOIN cmsPropertyType
ON cmsPropertyData.propertytypeid = cmsPropertyType.id
INNER JOIN
(" + string.Format(parsedOriginalSql, "cmsContent.nodeId, cmsContentVersion.VersionId") + @") as docData
ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId
ORDER BY contentNodeId, versionId, propertytypeid
", docSql.Arguments);
//This does NOT fetch all data into memory in a list, this will read
// over the records as a data reader, this is much better for performance and memory,
// but it means that during the reading of this data set, nothing else can be read
// from SQL server otherwise we'll get an exception.
var allPropertyData = Database.Query <PropertyDataDto>(propSql);
var result = new Dictionary <Guid, PropertyCollection>();
var propertiesWithTagSupport = new Dictionary <string, SupportTagsAttribute>();
//used to track the resolved composition property types per content type so we don't have to re-resolve (ToArray) the list every time
var resolvedCompositionProperties = new Dictionary <int, PropertyType[]>();
//keep track of the current property data item being enumerated
var propertyDataSetEnumerator = allPropertyData.GetEnumerator();
var hasCurrent = false; // initially there is no enumerator.Current
var comparer = new DocumentDefinitionComparer(SqlSyntax);
try
{
//This must be sorted by node id because this is how we are sorting the query to lookup property types above,
// which allows us to more efficiently iterate over the large data set of property values
foreach (var def in documentDefs.OrderBy(x => x.Id).ThenBy(x => x.Version, comparer))
{
// get the resolved properties from our local cache, or resolve them and put them in cache
PropertyType[] compositionProperties;
if (resolvedCompositionProperties.ContainsKey(def.Composition.Id))
{
compositionProperties = resolvedCompositionProperties[def.Composition.Id];
}
else
{
compositionProperties = def.Composition.CompositionPropertyTypes.ToArray();
resolvedCompositionProperties[def.Composition.Id] = compositionProperties;
}
// assemble the dtos for this def
// use the available enumerator.Current if any else move to next
var propertyDataDtos = new List <PropertyDataDto>();
while (hasCurrent || propertyDataSetEnumerator.MoveNext())
{
//Not checking null on VersionId because it can never be null - no idea why it's set to nullable
// ReSharper disable once PossibleInvalidOperationException
if (propertyDataSetEnumerator.Current.VersionId.Value == def.Version)
{
hasCurrent = false; // enumerator.Current is not available
propertyDataDtos.Add(propertyDataSetEnumerator.Current);
}
else
{
hasCurrent = true; // enumerator.Current is available for another def
break; // no more propertyDataDto for this def
}
}
var properties = PropertyFactory.BuildEntity(propertyDataDtos, compositionProperties, def.CreateDate, def.VersionDate).ToArray();
foreach (var property in properties)
{
//NOTE: The benchmarks run with and without the following code show very little change so this is not a perf bottleneck
var editor = PropertyEditorResolver.Current.GetByAlias(property.PropertyType.PropertyEditorAlias);
var tagSupport = propertiesWithTagSupport.ContainsKey(property.PropertyType.PropertyEditorAlias)
? propertiesWithTagSupport[property.PropertyType.PropertyEditorAlias]
: TagExtractor.GetAttribute(editor);
if (tagSupport != null)
{
//add to local cache so we don't need to reflect next time for this property editor alias
propertiesWithTagSupport[property.PropertyType.PropertyEditorAlias] = tagSupport;
//this property has tags, so we need to extract them and for that we need the prevals which we've already looked up
var preValData = allPreValues.Where(x => x.DataTypeNodeId == property.PropertyType.DataTypeDefinitionId)
.Distinct()
.ToArray();
var asDictionary = preValData.ToDictionary(x => x.Alias, x => new PreValue(x.Id, x.Value, x.SortOrder));
var preVals = new PreValueCollection(asDictionary);
var contentPropData = new ContentPropertyData(property.Value, preVals);
TagExtractor.SetPropertyTags(property, contentPropData, property.Value, tagSupport);
}
}
if (result.ContainsKey(def.Version))
{
var msg = string.Format("The query returned multiple property sets for document definition {0}, {1}, {2}", def.Id, def.Version, def.Composition.Name);
if (ThrowOnWarning)
{
throw new InvalidOperationException(msg);
}
else
{
Logger.Warn <VersionableRepositoryBase <TId, TEntity> >(msg);
}
}
result[def.Version] = new PropertyCollection(properties);
}
}
finally
{
propertyDataSetEnumerator.Dispose();
}
return(result);
}
protected IDictionary <int, PropertyCollection> GetPropertyCollection(
Sql docSql,
IEnumerable <DocumentDefinition> documentDefs)
{
if (documentDefs.Any() == false)
{
return(new Dictionary <int, PropertyCollection>());
}
//we need to parse the original SQL statement and reduce the columns to just cmsContent.nodeId, cmsContentVersion.VersionId so that we can use
// the statement to go get the property data for all of the items by using an inner join
var parsedOriginalSql = "SELECT {0} " + docSql.SQL.Substring(docSql.SQL.IndexOf("FROM", StringComparison.Ordinal));
//now remove everything from an Orderby clause and beyond
if (parsedOriginalSql.InvariantContains("ORDER BY "))
{
parsedOriginalSql = parsedOriginalSql.Substring(0, parsedOriginalSql.LastIndexOf("ORDER BY ", StringComparison.Ordinal));
}
var propSql = new Sql(@"SELECT cmsPropertyData.*
FROM cmsPropertyData
INNER JOIN cmsPropertyType
ON cmsPropertyData.propertytypeid = cmsPropertyType.id
INNER JOIN
(" + string.Format(parsedOriginalSql, "cmsContent.nodeId, cmsContentVersion.VersionId") + @") as docData
ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId
LEFT OUTER JOIN cmsDataTypePreValues
ON cmsPropertyType.dataTypeId = cmsDataTypePreValues.datatypeNodeId", docSql.Arguments);
var allPropertyData = Database.Fetch <PropertyDataDto>(propSql);
//This is a lazy access call to get all prevalue data for the data types that make up all of these properties which we use
// below if any property requires tag support
var allPreValues = new Lazy <IEnumerable <DataTypePreValueDto> >(() =>
{
var preValsSql = new Sql(@"SELECT a.id, a.value, a.sortorder, a.alias, a.datatypeNodeId
FROM cmsDataTypePreValues a
WHERE EXISTS(
SELECT DISTINCT b.id as preValIdInner
FROM cmsDataTypePreValues b
INNER JOIN cmsPropertyType
ON b.datatypeNodeId = cmsPropertyType.dataTypeId
INNER JOIN
(" + string.Format(parsedOriginalSql, "DISTINCT cmsContent.contentType") + @") as docData
ON cmsPropertyType.contentTypeId = docData.contentType
WHERE a.id = b.id)", docSql.Arguments);
return(Database.Fetch <DataTypePreValueDto>(preValsSql));
});
var result = new Dictionary <int, PropertyCollection>();
var propertiesWithTagSupport = new Dictionary <string, SupportTagsAttribute>();
//iterate each definition grouped by it's content type - this will mean less property type iterations while building
// up the property collections
foreach (var compositionGroup in documentDefs.GroupBy(x => x.Composition))
{
var compositionProperties = compositionGroup.Key.CompositionPropertyTypes.ToArray();
foreach (var def in compositionGroup)
{
var propertyDataDtos = allPropertyData.Where(x => x.NodeId == def.Id).Distinct();
var propertyFactory = new PropertyFactory(compositionProperties, def.Version, def.Id, def.CreateDate, def.VersionDate);
var properties = propertyFactory.BuildEntity(propertyDataDtos.ToArray()).ToArray();
var newProperties = properties.Where(x => x.HasIdentity == false && x.PropertyType.HasIdentity);
foreach (var property in newProperties)
{
var propertyDataDto = new PropertyDataDto {
NodeId = def.Id, PropertyTypeId = property.PropertyTypeId, VersionId = def.Version
};
int primaryKey = Convert.ToInt32(Database.Insert(propertyDataDto));
property.Version = def.Version;
property.Id = primaryKey;
}
foreach (var property in properties)
{
//NOTE: The benchmarks run with and without the following code show very little change so this is not a perf bottleneck
var editor = PropertyEditorResolver.Current.GetByAlias(property.PropertyType.PropertyEditorAlias);
var tagSupport = propertiesWithTagSupport.ContainsKey(property.PropertyType.PropertyEditorAlias)
? propertiesWithTagSupport[property.PropertyType.PropertyEditorAlias]
: TagExtractor.GetAttribute(editor);
if (tagSupport != null)
{
//add to local cache so we don't need to reflect next time for this property editor alias
propertiesWithTagSupport[property.PropertyType.PropertyEditorAlias] = tagSupport;
//this property has tags, so we need to extract them and for that we need the prevals which we've already looked up
var preValData = allPreValues.Value.Where(x => x.DataTypeNodeId == property.PropertyType.DataTypeDefinitionId)
.Distinct()
.ToArray();
var asDictionary = preValData.ToDictionary(x => x.Alias, x => new PreValue(x.Id, x.Value, x.SortOrder));
var preVals = new PreValueCollection(asDictionary);
var contentPropData = new ContentPropertyData(property.Value,
preVals,
new Dictionary <string, object>());
TagExtractor.SetPropertyTags(property, contentPropData, property.Value, tagSupport);
}
}
if (result.ContainsKey(def.Id))
{
Logger.Warn <VersionableRepositoryBase <TId, TEntity> >("The query returned multiple property sets for document definition " + def.Id + ", " + def.Composition.Name);
}
result[def.Id] = new PropertyCollection(properties);
}
}
return(result);
}
