<commit_before>﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using NPoco;
using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.Dtos;
using Umbraco.Core.Scoping;
using Umbraco.Web.Composing;
using static Umbraco.Core.Persistence.NPocoSqlExtensions.Statics;

namespace Umbraco.Web.PublishedCache.NuCache.DataSource
{
    // TODO: use SqlTemplate for these queries else it's going to be horribly slow!

    // provides efficient database access for NuCache
    internal class DatabaseDataSource : IDataSource
    {
        private const int PageSize = 500;
        private readonly IContentCacheDataSerializerFactory _contentCacheDataSerializerFactory;

        public DatabaseDataSource(IContentCacheDataSerializerFactory contentCacheDataSerializerFactory)
        {
            _contentCacheDataSerializerFactory = contentCacheDataSerializerFactory;
        }

        // we want arrays, we want them all loaded, not an enumerable

        private Sql<ISqlContext> ContentSourcesSelect(IScope scope, Func<Sql<ISqlContext>, Sql<ISqlContext>> joins = null)
        {
            var sqlTemplate = scope.SqlContext.Templates.Get(Constants.SqlTemplates.NuCacheDatabaseDataSource.ContentSourcesSelect1, tsql =>
                tsql.Select<NodeDto>(x => Alias(x.NodeId, "Id"), x => Alias(x.UniqueId, "Uid"),
                    x => Alias(x.Level, "Level"), x => Alias(x.Path, "Path"), x => Alias(x.SortOrder, "SortOrder"), x => Alias(x.ParentId, "ParentId"),
                    x => Alias(x.CreateDate, "CreateDate"), x => Alias(x.UserId, "CreatorId"))
                .AndSelect<ContentDto>(x => Alias(x.ContentTypeId, "ContentTypeId"))
                .AndSelect<DocumentDto>(x => Alias(x.Published, "Published"), x => Alias(x.Edited, "Edited"))

                .AndSelect<ContentVersionDto>(x => Alias(x.Id, "VersionId"), x => Alias(x.Text, "EditName"), x => Alias(x.VersionDate, "EditVersionDate"), x => Alias(x.UserId, "EditWriterId"))
                .AndSelect<DocumentVersionDto>(x => Alias(x.TemplateId, "EditTemplateId"))

                .AndSelect<ContentVersionDto>("pcver", x => Alias(x.Id, "PublishedVersionId"), x => Alias(x.Text, "PubName"), x => Alias(x.VersionDate, "PubVersionDate"), x => Alias(x.UserId, "PubWriterId"))
                .AndSelect<DocumentVersionDto>("pdver", x => Alias(x.TemplateId, "PubTemplateId"))

                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.Data, "EditData"))
                .AndSelect<ContentNuDto>("nuPub", x => Alias(x.Data, "PubData"))

                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.RawData, "EditDataRaw"))
                .AndSelect<ContentNuDto>("nuPub", x => Alias(x.RawData, "PubDataRaw"))

                .From<NodeDto>());

            var sql = sqlTemplate.Sql();

            // TODO: I'm unsure how we can format the below into SQL templates also because right.Current and right.Published end up being parameters

            if (joins != null)
                sql = joins(sql);

            sql = sql
                .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                .InnerJoin<DocumentDto>().On<NodeDto, DocumentDto>((left, right) => left.NodeId == right.NodeId)

                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
                .InnerJoin<DocumentVersionDto>().On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id)

                .LeftJoin<ContentVersionDto>(j =>
                    j.InnerJoin<DocumentVersionDto>("pdver").On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id && right.Published == true, "pcver", "pdver"), "pcver")
                .On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId, aliasRight: "pcver")

                .LeftJoin<ContentNuDto>("nuEdit").On<NodeDto, ContentNuDto>((left, right) => left.NodeId == right.NodeId && right.Published == false, aliasRight: "nuEdit")
                .LeftJoin<ContentNuDto>("nuPub").On<NodeDto, ContentNuDto>((left, right) => left.NodeId == right.NodeId && right.Published == true, aliasRight: "nuPub");

            return sql;
        }

        /// <summary>
        /// Returns a slightly more optimized query to use for the document counting when paging over the content sources
        /// </summary>
        /// <param name="scope"></param>
        /// <returns></returns>
        private Sql<ISqlContext> ContentSourcesCount(IScope scope)
        {
            var sqlTemplate = scope.SqlContext.Templates.Get(Constants.SqlTemplates.NuCacheDatabaseDataSource.ContentSourcesCount, tsql =>
                tsql.Select<NodeDto>(x => Alias(x.NodeId, "Id"))
                    .From<NodeDto>()
                    .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                    .InnerJoin<DocumentDto>().On<NodeDto, DocumentDto>((left, right) => left.NodeId == right.NodeId));

            var sql = sqlTemplate.Sql();

            // TODO: We can't use a template with this one because of the 'right.Current' and 'right.Published' ends up being a parameter so not sure how we can do that
            sql = sql
                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
                .InnerJoin<DocumentVersionDto>().On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id)
                .LeftJoin<ContentVersionDto>(j =>
                        j.InnerJoin<DocumentVersionDto>("pdver").On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id && right.Published, "pcver", "pdver"), "pcver")
                    .On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId, aliasRight: "pcver");

            return sql;
        }

        public ContentNodeKit GetContentSource(IScope scope, int id)
        {
            var sql = ContentSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && x.NodeId == id && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var dto = scope.Database.Fetch<ContentSourceDto>(sql).FirstOrDefault();

            if (dto == null) return ContentNodeKit.Empty;

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);
            return CreateContentNodeKit(dto, serializer);
        }

        public IEnumerable<ContentNodeKit> GetAllContentSources(IScope scope)
        {
            // Create a different query for the SQL vs the COUNT Sql since the auto-generated COUNT Sql will be inneficient
            var sql = ContentSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            // create a more efficient COUNT query without the join on the cmsContentNu table
            var sqlCountQuery = ContentSourcesCount(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed);
            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateContentNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetBranchContentSources(IScope scope, int id)
        {
            var syntax = scope.SqlContext.SqlSyntax;
            var sql = ContentSourcesSelect(scope,
                s => s.InnerJoin<NodeDto>("x").On<NodeDto, NodeDto>((left, right) => left.NodeId == right.NodeId || SqlText<bool>(left.Path, right.Path, (lp, rp) => $"({lp} LIKE {syntax.GetConcat(rp, "',%'")})"), aliasRight: "x"))
                    .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                    .Where<NodeDto>(x => x.NodeId == id, "x")
                    .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql))
            {
                yield return CreateContentNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetTypeContentSources(IScope scope, IEnumerable<int> ids)
        {
            if (!ids.Any()) yield break;

            var sql = ContentSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                .WhereIn<ContentDto>(x => x.ContentTypeId, ids)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql))
            {
                yield return CreateContentNodeKit(row, serializer);
            }   
        }

        private Sql<ISqlContext> MediaSourcesSelect(IScope scope, Func<Sql<ISqlContext>, Sql<ISqlContext>> joins = null)
        {
            var sql = scope.SqlContext.Sql()

                .Select<NodeDto>(x => Alias(x.NodeId, "Id"), x => Alias(x.UniqueId, "Uid"),
                    x => Alias(x.Level, "Level"), x => Alias(x.Path, "Path"), x => Alias(x.SortOrder, "SortOrder"), x => Alias(x.ParentId, "ParentId"),
                    x => Alias(x.CreateDate, "CreateDate"), x => Alias(x.UserId, "CreatorId"))
                .AndSelect<ContentDto>(x => Alias(x.ContentTypeId, "ContentTypeId"))
                .AndSelect<ContentVersionDto>(x => Alias(x.Id, "VersionId"), x => Alias(x.Text, "EditName"), x => Alias(x.VersionDate, "EditVersionDate"), x => Alias(x.UserId, "EditWriterId"))
                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.Data, "EditData"))
                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.RawData, "EditDataRaw"))
                .From<NodeDto>();

            if (joins != null)
                sql = joins(sql);

            sql = sql
                .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
                .LeftJoin<ContentNuDto>("nuEdit").On<NodeDto, ContentNuDto>((left, right) => left.NodeId == right.NodeId && !right.Published, aliasRight: "nuEdit");

            return sql;
        }

        public ContentNodeKit GetMediaSource(IScope scope, int id)
        {
            var sql = MediaSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && x.NodeId == id && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var dto = scope.Database.Fetch<ContentSourceDto>(sql).FirstOrDefault();

            if (dto == null) return ContentNodeKit.Empty;

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);
            return CreateMediaNodeKit(dto, serializer);
        }

        public IEnumerable<ContentNodeKit> GetAllMediaSources(IScope scope)
        {
            var sql = MediaSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql))
            {
                yield return CreateMediaNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetBranchMediaSources(IScope scope, int id)
        {
            var syntax = scope.SqlContext.SqlSyntax;
            var sql = MediaSourcesSelect(scope,
                s => s.InnerJoin<NodeDto>("x").On<NodeDto, NodeDto>((left, right) => left.NodeId == right.NodeId || SqlText<bool>(left.Path, right.Path, (lp, rp) => $"({lp} LIKE {syntax.GetConcat(rp, "',%'")})"), aliasRight: "x"))
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                .Where<NodeDto>(x => x.NodeId == id, "x")
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql))
            {
                yield return CreateMediaNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetTypeMediaSources(IScope scope, IEnumerable<int> ids)
        {
            if (!ids.Any()) yield break;

            var sql = MediaSourcesSelect(scope)
                    .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                    .WhereIn<ContentDto>(x => x.ContentTypeId, ids)
                    .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql))
            {
                yield return CreateMediaNodeKit(row, serializer);
            }   
        }

        private ContentNodeKit CreateContentNodeKit(ContentSourceDto dto, IContentCacheDataSerializer serializer)
        {
            ContentData d = null;
            ContentData p = null;

            if (dto.Edited)
            {
                if (dto.EditData == null && dto.EditDataRaw == null)
                {
                    if (Debugger.IsAttached)
                        throw new InvalidOperationException("Missing cmsContentNu edited content for node " + dto.Id + ", consider rebuilding.");
                    Current.Logger.Warn<DatabaseDataSource>("Missing cmsContentNu edited content for node {NodeId}, consider rebuilding.", dto.Id);
                }
                else
                {<commit_msg>Optimize content and media count queries when paging<commit_after>﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using NPoco;
using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.Dtos;
using Umbraco.Core.Scoping;
using Umbraco.Web.Composing;
using static Umbraco.Core.Persistence.NPocoSqlExtensions.Statics;

namespace Umbraco.Web.PublishedCache.NuCache.DataSource
{
    // TODO: use SqlTemplate for these queries else it's going to be horribly slow!

    // provides efficient database access for NuCache
    internal class DatabaseDataSource : IDataSource
    {
        private const int PageSize = 500;
        private readonly IContentCacheDataSerializerFactory _contentCacheDataSerializerFactory;

        public DatabaseDataSource(IContentCacheDataSerializerFactory contentCacheDataSerializerFactory)
        {
            _contentCacheDataSerializerFactory = contentCacheDataSerializerFactory;
        }

        // we want arrays, we want them all loaded, not an enumerable

        private Sql<ISqlContext> ContentSourcesSelect(IScope scope, Func<Sql<ISqlContext>, Sql<ISqlContext>> joins = null)
        {
            var sqlTemplate = scope.SqlContext.Templates.Get(Constants.SqlTemplates.NuCacheDatabaseDataSource.ContentSourcesSelect1, tsql =>
                tsql.Select<NodeDto>(x => Alias(x.NodeId, "Id"), x => Alias(x.UniqueId, "Uid"),
                    x => Alias(x.Level, "Level"), x => Alias(x.Path, "Path"), x => Alias(x.SortOrder, "SortOrder"), x => Alias(x.ParentId, "ParentId"),
                    x => Alias(x.CreateDate, "CreateDate"), x => Alias(x.UserId, "CreatorId"))
                .AndSelect<ContentDto>(x => Alias(x.ContentTypeId, "ContentTypeId"))
                .AndSelect<DocumentDto>(x => Alias(x.Published, "Published"), x => Alias(x.Edited, "Edited"))

                .AndSelect<ContentVersionDto>(x => Alias(x.Id, "VersionId"), x => Alias(x.Text, "EditName"), x => Alias(x.VersionDate, "EditVersionDate"), x => Alias(x.UserId, "EditWriterId"))
                .AndSelect<DocumentVersionDto>(x => Alias(x.TemplateId, "EditTemplateId"))

                .AndSelect<ContentVersionDto>("pcver", x => Alias(x.Id, "PublishedVersionId"), x => Alias(x.Text, "PubName"), x => Alias(x.VersionDate, "PubVersionDate"), x => Alias(x.UserId, "PubWriterId"))
                .AndSelect<DocumentVersionDto>("pdver", x => Alias(x.TemplateId, "PubTemplateId"))

                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.Data, "EditData"))
                .AndSelect<ContentNuDto>("nuPub", x => Alias(x.Data, "PubData"))

                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.RawData, "EditDataRaw"))
                .AndSelect<ContentNuDto>("nuPub", x => Alias(x.RawData, "PubDataRaw"))

                .From<NodeDto>());

            var sql = sqlTemplate.Sql();

            // TODO: I'm unsure how we can format the below into SQL templates also because right.Current and right.Published end up being parameters

            if (joins != null)
                sql = joins(sql);

            sql = sql
                .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                .InnerJoin<DocumentDto>().On<NodeDto, DocumentDto>((left, right) => left.NodeId == right.NodeId)

                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
                .InnerJoin<DocumentVersionDto>().On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id)

                .LeftJoin<ContentVersionDto>(j =>
                    j.InnerJoin<DocumentVersionDto>("pdver").On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id && right.Published == true, "pcver", "pdver"), "pcver")
                .On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId, aliasRight: "pcver")

                .LeftJoin<ContentNuDto>("nuEdit").On<NodeDto, ContentNuDto>((left, right) => left.NodeId == right.NodeId && right.Published == false, aliasRight: "nuEdit")
                .LeftJoin<ContentNuDto>("nuPub").On<NodeDto, ContentNuDto>((left, right) => left.NodeId == right.NodeId && right.Published == true, aliasRight: "nuPub");

            return sql;
        }

        /// <summary>
        /// Returns a slightly more optimized query to use for the document counting when paging over the content sources
        /// </summary>
        /// <param name="scope"></param>
        /// <returns></returns>
        private Sql<ISqlContext> ContentSourcesCount(IScope scope, Func<Sql<ISqlContext>, Sql<ISqlContext>> joins = null)
        {
            var sqlTemplate = scope.SqlContext.Templates.Get(Constants.SqlTemplates.NuCacheDatabaseDataSource.ContentSourcesCount, tsql =>
                tsql.Select<NodeDto>(x => Alias(x.NodeId, "Id"))
                    .From<NodeDto>()
                    .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                    .InnerJoin<DocumentDto>().On<NodeDto, DocumentDto>((left, right) => left.NodeId == right.NodeId));

            var sql = sqlTemplate.Sql();

            if (joins != null)
                sql = joins(sql);

            // TODO: We can't use a template with this one because of the 'right.Current' and 'right.Published' ends up being a parameter so not sure how we can do that
            sql = sql
                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
                .InnerJoin<DocumentVersionDto>().On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id)
                .LeftJoin<ContentVersionDto>(j =>
                        j.InnerJoin<DocumentVersionDto>("pdver").On<ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id && right.Published, "pcver", "pdver"), "pcver")
                    .On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId, aliasRight: "pcver");

            return sql;
        }

        public ContentNodeKit GetContentSource(IScope scope, int id)
        {
            var sql = ContentSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && x.NodeId == id && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var dto = scope.Database.Fetch<ContentSourceDto>(sql).FirstOrDefault();

            if (dto == null) return ContentNodeKit.Empty;

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);
            return CreateContentNodeKit(dto, serializer);
        }

        public IEnumerable<ContentNodeKit> GetAllContentSources(IScope scope)
        {
            // Create a different query for the SQL vs the COUNT Sql since the auto-generated COUNT Sql will be inneficient
            var sql = ContentSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            // create a more efficient COUNT query without the join on the cmsContentNu table
            var sqlCountQuery = ContentSourcesCount(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed);
            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateContentNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetBranchContentSources(IScope scope, int id)
        {
            var syntax = scope.SqlContext.SqlSyntax;
            var sql = ContentSourcesSelect(scope,
                s => s.InnerJoin<NodeDto>("x").On<NodeDto, NodeDto>((left, right) => left.NodeId == right.NodeId || SqlText<bool>(left.Path, right.Path, (lp, rp) => $"({lp} LIKE {syntax.GetConcat(rp, "',%'")})"), aliasRight: "x"))
                    .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                    .Where<NodeDto>(x => x.NodeId == id, "x")
                    .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            // create a more efficient COUNT query without the join on the cmsContentNu table
            var sqlCountQuery = ContentSourcesCount(scope,
                s => s.InnerJoin<NodeDto>("x").On<NodeDto, NodeDto>((left, right) => left.NodeId == right.NodeId || SqlText<bool>(left.Path, right.Path, (lp, rp) => $"({lp} LIKE {syntax.GetConcat(rp, "',%'")})"), aliasRight: "x"))
                    .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                    .Where<NodeDto>(x => x.NodeId == id, "x");
            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");


            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateContentNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetTypeContentSources(IScope scope, IEnumerable<int> ids)
        {
            if (!ids.Any()) yield break;

            var sql = ContentSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                .WhereIn<ContentDto>(x => x.ContentTypeId, ids)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var sqlCountQuery = ContentSourcesCount(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Document && !x.Trashed)
                .WhereIn<ContentDto>(x => x.ContentTypeId, ids);
            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Document);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateContentNodeKit(row, serializer);
            }   
        }

        private Sql<ISqlContext> MediaSourcesSelect(IScope scope, Func<Sql<ISqlContext>, Sql<ISqlContext>> joins = null)
        {
            var sql = scope.SqlContext.Sql()

                .Select<NodeDto>(x => Alias(x.NodeId, "Id"))
                .AndSelect<ContentDto>(x => Alias(x.ContentTypeId, "ContentTypeId"))
                .AndSelect<ContentVersionDto>(x => Alias(x.Id, "VersionId"), x => Alias(x.Text, "EditName"), x => Alias(x.VersionDate, "EditVersionDate"), x => Alias(x.UserId, "EditWriterId"))
                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.Data, "EditData"))
                .AndSelect<ContentNuDto>("nuEdit", x => Alias(x.RawData, "EditDataRaw"))
                .From<NodeDto>();

            if (joins != null)
                sql = joins(sql);

            sql = sql
                .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
                .LeftJoin<ContentNuDto>("nuEdit").On<NodeDto, ContentNuDto>((left, right) => left.NodeId == right.NodeId && !right.Published, aliasRight: "nuEdit");

            return sql;
        }
        private Sql<ISqlContext> MediaSourcesCount(IScope scope, Func<Sql<ISqlContext>, Sql<ISqlContext>> joins = null)
        {
            var sql = scope.SqlContext.Sql()

                .Select<NodeDto>(x => Alias(x.NodeId, "Id"))
                .From<NodeDto>();

            if (joins != null)
                sql = joins(sql);

            sql = sql
                .InnerJoin<ContentDto>().On<NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
                .InnerJoin<ContentVersionDto>().On<NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current);

            return sql;
        }

        public ContentNodeKit GetMediaSource(IScope scope, int id)
        {
            var sql = MediaSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && x.NodeId == id && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var dto = scope.Database.Fetch<ContentSourceDto>(sql).FirstOrDefault();

            if (dto == null) return ContentNodeKit.Empty;

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);
            return CreateMediaNodeKit(dto, serializer);
        }

        public IEnumerable<ContentNodeKit> GetAllMediaSources(IScope scope)
        {
            var sql = MediaSourcesSelect(scope)
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var sqlCountQuery = MediaSourcesCount(scope)
               .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed);

            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateMediaNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetBranchMediaSources(IScope scope, int id)
        {
            var syntax = scope.SqlContext.SqlSyntax;
            var sql = MediaSourcesSelect(scope,
                s => s.InnerJoin<NodeDto>("x").On<NodeDto, NodeDto>((left, right) => left.NodeId == right.NodeId || SqlText<bool>(left.Path, right.Path, (lp, rp) => $"({lp} LIKE {syntax.GetConcat(rp, "',%'")})"), aliasRight: "x"))
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                .Where<NodeDto>(x => x.NodeId == id, "x")
                .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var sqlCountQuery = MediaSourcesCount(scope,
                s => s.InnerJoin<NodeDto>("x").On<NodeDto, NodeDto>((left, right) => left.NodeId == right.NodeId || SqlText<bool>(left.Path, right.Path, (lp, rp) => $"({lp} LIKE {syntax.GetConcat(rp, "',%'")})"), aliasRight: "x"))
                .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                .Where<NodeDto>(x => x.NodeId == id, "x");

            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateMediaNodeKit(row, serializer);
            }   
        }

        public IEnumerable<ContentNodeKit> GetTypeMediaSources(IScope scope, IEnumerable<int> ids)
        {
            if (!ids.Any()) yield break;

            var sql = MediaSourcesSelect(scope)
                    .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                    .WhereIn<ContentDto>(x => x.ContentTypeId, ids)
                    .OrderBy<NodeDto>(x => x.Level, x => x.ParentId, x => x.SortOrder);

            var sqlCountQuery = MediaSourcesCount(scope)
                    .Where<NodeDto>(x => x.NodeObjectType == Constants.ObjectTypes.Media && !x.Trashed)
                    .WhereIn<ContentDto>(x => x.ContentTypeId, ids);

            var sqlCount = scope.SqlContext.Sql("SELECT COUNT(*) FROM (").Append(sqlCountQuery).Append(") npoco_tbl");

            var serializer = _contentCacheDataSerializerFactory.Create(ContentCacheDataSerializerEntityType.Media);

            // We need to page here. We don't want to iterate over every single row in one connection cuz this can cause an SQL Timeout.
            // We also want to read with a db reader and not load everything into memory, QueryPaged lets us do that.

            foreach (var row in scope.Database.QueryPaged<ContentSourceDto>(PageSize, sql, sqlCount))
            {
                yield return CreateMediaNodeKit(row, serializer);
            }   
        }

        private ContentNodeKit CreateContentNodeKit(ContentSourceDto dto, IContentCacheDataSerializer serializer)
        {
            ContentData d = null;
            ContentData p = null;

            if (dto.Edited)
            {
                if (dto.EditData == null && dto.EditDataRaw == null)
                {
                    if (Debugger.IsAttached)
                        throw new InvalidOperationException("Missing cmsContentNu edited content for node " + dto.Id + ", consider rebuilding.");
                    Current.Logger.Warn<DatabaseDataSource>("Missing cmsContentNu edited content for node {NodeId}, consider rebuilding.", dto.Id);
                }
                else
                {
{'fraction_non_alphanumeric': 0.12327658386826681, 'fraction_numerical': 0.0002232765838682668, 'mean_word_length': 2.5670482827277255, 'pattern_counts': {'":': 0, '<': 257, '<?xml version=': 0, '>': 478, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6745236', 'n_tokens_mistral': 10176, 'n_tokens_neox': 10039, 'n_words': 2410}