private IEnumerable <RevisionData> GetAllRevisionData(HiveId entityUri) { Mandate.ParameterNotEmpty(entityUri, "hiveId"); //var entityStatusLog = Helper.NhSession.QueryOver<NodeVersionStatusHistory>() // .OrderBy(x => x.Date).Desc // .JoinQueryOver(x => x.NodeVersion).Where(x => x.Node.Id == (Guid)entityUri.Value) // .List() // .DistinctBy(x => x.Id); NodeVersionStatusHistory aliasHistory = null; NodeVersion aliasVersion = null; Node aliasNode = null; NodeVersionStatusType aliasType = null; var entityStatusLog = Helper.NhSession.QueryOver <NodeVersionStatusHistory>(() => aliasHistory) .OrderBy(x => x.Date).Desc .JoinQueryOver(x => x.NodeVersionStatusType, () => aliasType) .JoinQueryOver(x => aliasHistory.NodeVersion, () => aliasVersion) .JoinQueryOver(x => x.Node, () => aliasNode) .Where(x => x.Id == (Guid)entityUri.Value) .Fetch(x => aliasHistory.NodeVersionStatusType).Eager .Select(x => x.Date, x => x.Id, x => aliasNode.DateCreated, x => aliasType.Id, x => aliasType.IsSystem, x => aliasType.Alias, x => aliasType.Name, x => aliasVersion.Id) .List <object[]>() .Select(col => new { Date = (DateTimeOffset)col[0], Id = (Guid)col[1], DateCreated = (DateTimeOffset)col[2], TypeId = (Guid)col[3], TypeIsSystem = (bool)col[4], TypeAlias = (string)col[5], TypeName = (string)col[6], VersionId = (Guid)col[7] }); var otherRevisionData = new HashSet <RevisionData>(); var changeset = new Changeset(new Branch("default")); // Ignored for the moment in the persistence layer for this provider foreach (var statusQueryRow in entityStatusLog) { var nodeVersionStatusType = new NodeVersionStatusType { Alias = statusQueryRow.TypeAlias, Name = statusQueryRow.TypeName, Id = statusQueryRow.TypeId, IsSystem = statusQueryRow.TypeIsSystem }; var revisionStatusType = FrameworkContext.TypeMappers.Map <RevisionStatusType>(nodeVersionStatusType); var revisionData = new RevisionData(changeset, (HiveId)statusQueryRow.VersionId, revisionStatusType) { UtcCreated = statusQueryRow.DateCreated, UtcModified = statusQueryRow.Date, UtcStatusChanged = statusQueryRow.Date }; otherRevisionData.Add(revisionData); } return(otherRevisionData); }
public IQueryOver <NodeVersion, NodeVersionStatusHistory> GenerateVersionedQuery(out NodeVersion outerVersionSelectAlias, Guid[] nodeIds = null, RevisionStatusType revisionStatus = null, bool limitToLatestRevision = true) { // We want the NodeVersion table, joined to the NodeVersionStatusHistory table, // ordered by the status history date descending, but we only want the latest one Node node = null; NodeVersion subSelectVersion = null; NodeVersionStatusHistory subSelectTopStatus = null; NodeVersion outerVersionSelect = null; NodeVersionStatusType subSelectStatusType = null; // First define the subselection of the top 1 version items when joined and sorted by version status history in date-descending order // We also add a clause to say "where the outer selected version's node id equals the subselected node id" since it's selecting the top 1 // so we want it to be the 1 latest-date item relevant for each row of the outer select var subSelectTopStatusByDate = QueryOver.Of(() => subSelectTopStatus) //.Where(() => subSelectTopStatus.NodeVersion.Id == outerVersionSelect.Id); .JoinQueryOver(() => subSelectTopStatus.NodeVersion, () => subSelectVersion) .JoinQueryOver(() => subSelectVersion.Node, () => node) .Where(() => subSelectTopStatus.NodeVersion.Id == subSelectVersion.Id) .And(() => outerVersionSelect.Node.Id == node.Id); int takeCount = limitToLatestRevision ? 1 : 999; // Now we need to add a filter for the revision status type, if one was supplied QueryOver <NodeVersionStatusHistory> subSelectTopStatusByDateWithFilter = null; QueryOver <NodeVersionStatusHistory> excludeNegatingStatusses = null; if (revisionStatus != null) { var statusAlias = revisionStatus.Alias; if (revisionStatus.NegatedByTypes.Any()) { NodeVersionStatusHistory negateHistory = null; NodeVersionStatusType negateType = null; NodeVersion negateVersion = null; var negatingAliases = revisionStatus.NegatedByTypes.Select(x => x.Alias).ToArray(); //var first = negatingAliases.First(); excludeNegatingStatusses = QueryOver.Of(() => negateHistory) .JoinAlias(() => negateHistory.NodeVersionStatusType, () => negateType) .JoinAlias(() => negateHistory.NodeVersion, () => negateVersion) .Where(() => negateType.Alias.IsIn(negatingAliases)) .And(() => outerVersionSelect.Node == negateVersion.Node) .Select(Projections.SqlFunction("coalesce", NHibernateUtil.DateTime, Projections.Max <NodeVersionStatusHistory>(x => x.Date), new ConstantProjection(new DateTime(1981, 8, 1)))); //excludeNegatingStatusses = QueryOver.Of(() => negateHistory) // .JoinAlias(() => negateHistory.NodeVersionStatusType, () => negateType) // .JoinAlias(() => negateHistory.NodeVersion, () => negateVersion) // .JoinAlias(() => negateVersion.Node, () => node) // .Where(() => negateType.Alias.IsIn(negatingAliases)) // .And(() => negateHistory.Date > subSelectTopStatus.Date) // //.And(() => subSelectTopStatus.NodeVersion.Id == negateVersion.Id) // .And(() => outerVersionSelect.Node.Id == node.Id) // .Select(x => x.Id) // .Take(1); //excludeNegatingStatusses = QueryOver.Of(() => negateHistory) // .JoinAlias(() => negateHistory.NodeVersionStatusType, () => negateType) // .JoinAlias(() => negateHistory.NodeVersion, () => negateVersion) // .JoinAlias(() => negateVersion.Node, () => node) // .Where(() => negateType.Alias.IsIn(negatingAliases)) // .And(() => outerVersionSelect.Node.Id == node.Id) // .And(() => negateHistory.Date > subSelectTopStatus.Date) // .OrderBy(x => x.Date).Desc // .Select(x => x.Id) // .Take(1); } var subSelectBuilder = subSelectTopStatusByDate .And(() => subSelectStatusType.Alias == statusAlias) .JoinQueryOver(x => subSelectTopStatus.NodeVersionStatusType, () => subSelectStatusType) .OrderBy(() => subSelectTopStatus.Date).Desc; if (excludeNegatingStatusses != null) { // Yeah, I know, horrible to check Sql dialect when generating query, but Nh's dialect support doesn't allow a provider-based // way of telling whether the db engine supports / requires "all" to be prefix before a subquery when doing an operation. // e.g. SqlCe requires "blah > all (select max(blah) from blah)", SqlServer doesn't mind, SQLite doesn't support it if (RequiresAllForGtSubquery()) { subSelectBuilder = subSelectBuilder.WithSubquery.WhereProperty(() => subSelectTopStatus.Date).GtAll(excludeNegatingStatusses); } else { subSelectBuilder = subSelectBuilder.WithSubquery.WhereProperty(() => subSelectTopStatus.Date).Gt(excludeNegatingStatusses); } //subSelectBuilder = subSelectBuilder.WithSubquery.WhereNotExists(excludeNegatingStatusses); //subSelectBuilder = subSelectBuilder.WithSubquery.WhereProperty(() => subSelectTopStatus.Id).NotIn(excludeNegatingStatusses); } subSelectTopStatusByDateWithFilter = subSelectBuilder.Select(x => subSelectTopStatus.NodeVersion.Id).Take(takeCount); // We have to include a Take here for compatibility with SqlServerCe } else { subSelectTopStatusByDateWithFilter = subSelectTopStatusByDate .OrderBy(() => subSelectTopStatus.Date).Desc .Select(x => subSelectTopStatus.NodeVersion.Id).Take(takeCount); // We have to include a Take here for compatibility with SqlServerCe } NodeVersionStatusHistory outerHistoryForSort = null; IQueryOver <NodeVersion, NodeVersionStatusHistory> outerQuery = NhSession.QueryOver <NodeVersion>( () => outerVersionSelect) //.Fetch(x => x.AttributeSchemaDefinition).Eager .Fetch(x => x.Attributes).Eager // We load these eagerly rather than in a Future to avoid a separate query .Fetch(x => x.Node).Eager // There's a 1-m mapping between Node-NodeVersion so safe to load this with a join too rather than with a future .Inner.JoinQueryOver(x => outerHistoryForSort.NodeVersionStatusType, () => subSelectStatusType) .Inner.JoinQueryOver(x => outerVersionSelect.NodeVersionStatuses, () => outerHistoryForSort); NodeVersion innerVersion = null; NodeVersionStatusHistory innerHistory = null; NodeVersionStatusType innerType = null; var buildInnerHistorySubQuery = QueryOver.Of <NodeVersionStatusHistory>(() => innerHistory) .JoinQueryOver(() => innerHistory.NodeVersion, () => innerVersion) .JoinQueryOver(() => innerHistory.NodeVersionStatusType, () => innerType) .Where(() => innerVersion.Node == outerVersionSelect.Node); if (revisionStatus != null) { var statusAlias = revisionStatus.Alias; buildInnerHistorySubQuery = buildInnerHistorySubQuery .And(() => innerType.Alias == statusAlias); NodeVersionStatusType reselectType = null; outerQuery = outerQuery .And(() => subSelectStatusType.Alias == statusAlias); // Yeah, I know, horrible to check Sql dialect when generating query, but Nh's dialect support doesn't allow a provider-based // way of telling whether the db engine supports / requires "all" to be prefix before a subquery when doing an operation. // e.g. SqlCe requires "blah > all (select max(blah) from blah)", SqlServer doesn't mind, SQLite doesn't support it if (excludeNegatingStatusses != null) { if (RequiresAllForGtSubquery()) { outerQuery = outerQuery.WithSubquery.WhereProperty(() => outerHistoryForSort.Date).GtAll(excludeNegatingStatusses); } else { outerQuery = outerQuery.WithSubquery.WhereProperty(() => outerHistoryForSort.Date).Gt(excludeNegatingStatusses); } } } var subQueryInnerHistory = buildInnerHistorySubQuery.Select(x => x.Id) .OrderBy(() => innerHistory.Date).Desc .Take(takeCount); outerQuery = outerQuery .OrderBy(() => outerHistoryForSort.Date).Asc .WithSubquery.WhereProperty(() => outerHistoryForSort.Id).In(subQueryInnerHistory); //// Based on perf testing of SqlCe queries, it shaves a lot off execution time if we specify the status type in the //// outer query in addition to the subquery //if (revisionStatus != null) //{ // var statusAlias = revisionStatus.Alias; // outerQuery = outerQuery // .And(x => subSelectStatusType.Alias == statusAlias); //} //outerQuery = outerQuery // .OrderBy(() => outerHistoryForSort.Date).Asc // .WithSubquery.WhereProperty(() => outerVersionSelect.Id).In(subSelectTopStatusByDateWithFilter.Clone()); if (nodeIds != null && nodeIds.Any()) { outerQuery = outerQuery.And(() => outerVersionSelect.Node.Id.IsIn(nodeIds)); } outerVersionSelectAlias = outerVersionSelect; return(outerQuery); }