<commit_before>﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using Newtonsoft.Json;
using NPoco;
using Umbraco.Core.Cache;
using Umbraco.Core.Composing;
using Umbraco.Core.Events;
using Umbraco.Core.Logging;
using Umbraco.Core.Models;
using Umbraco.Core.Models.Editors;
using Umbraco.Core.Models.Entities;
using Umbraco.Core.Persistence.DatabaseModelDefinitions;
using Umbraco.Core.Persistence.Dtos;
using Umbraco.Core.Persistence.Factories;
using Umbraco.Core.Persistence.Querying;
using Umbraco.Core.PropertyEditors;
using Umbraco.Core.Scoping;
using Umbraco.Core.Services;
using Umbraco.Core.Services.Implement;
using static Umbraco.Core.Persistence.NPocoSqlExtensions.Statics;

namespace Umbraco.Core.Persistence.Repositories.Implement
{
    internal sealed class ContentRepositoryBase
    {
        /// <summary>
        /// This is used for unit tests ONLY
        /// </summary>
        public static bool ThrowOnWarning = false;
    }

    internal abstract class ContentRepositoryBase<TId, TEntity, TRepository> : NPocoRepositoryBase<TId, TEntity>, IContentRepository<TId, TEntity>
        where TEntity : class, IUmbracoEntity
        where TRepository : class, IRepository
    {
        protected ContentRepositoryBase(IScopeAccessor scopeAccessor, CacheHelper cache, ILanguageRepository languageRepository, ILogger logger)
            : base(scopeAccessor, cache, logger)
        {
            LanguageRepository = languageRepository;
        }

        protected abstract TRepository This { get; }

        protected ILanguageRepository LanguageRepository { get; }

        protected PropertyEditorCollection PropertyEditors => Current.PropertyEditors; // fixme inject

        #region Versions

        // gets a specific version
        public abstract TEntity GetVersion(int versionId);

        // gets all versions, current first
        public abstract IEnumerable<TEntity> GetAllVersions(int nodeId);

        // gets all version ids, current first
        public virtual IEnumerable<int> GetVersionIds(int nodeId, int maxRows)
        {
            var template = SqlContext.Templates.Get("Umbraco.Core.VersionableRepository.GetVersionIds", tsql =>
                tsql.Select<ContentVersionDto>(x => x.Id)
                    .From<ContentVersionDto>()
                    .Where<ContentVersionDto>(x => x.NodeId == SqlTemplate.Arg<int>("nodeId"))
                    .OrderByDescending<ContentVersionDto>(x => x.Current) // current '1' comes before others '0'
                    .AndByDescending<ContentVersionDto>(x => x.VersionDate) // most recent first
            );
            return Database.Fetch<int>(SqlSyntax.SelectTop(template.Sql(nodeId), maxRows));
        }

        // deletes a specific version
        public virtual void DeleteVersion(int versionId)
        {
            // fixme test object node type?

            // get the version we want to delete
            var template = SqlContext.Templates.Get("Umbraco.Core.VersionableRepository.GetVersion", tsql =>
                tsql.Select<ContentVersionDto>().From<ContentVersionDto>().Where<ContentVersionDto>(x => x.Id == SqlTemplate.Arg<int>("versionId"))
            );
            var versionDto = Database.Fetch<ContentVersionDto>(template.Sql(new { versionId })).FirstOrDefault();

            // nothing to delete
            if (versionDto == null)
                return;

            // don't delete the current version
            if (versionDto.Current)
                throw new InvalidOperationException("Cannot delete the current version.");

            PerformDeleteVersion(versionDto.NodeId, versionId);
        }

        //  deletes all versions of an entity, older than a date.
        public virtual void DeleteVersions(int nodeId, DateTime versionDate)
        {
            // fixme test object node type?

            // get the versions we want to delete, excluding the current one
            var template = SqlContext.Templates.Get("Umbraco.Core.VersionableRepository.GetVersions", tsql =>
                tsql.Select<ContentVersionDto>().From<ContentVersionDto>().Where<ContentVersionDto>(x => x.NodeId == SqlTemplate.Arg<int>("nodeId") && !x.Current && x.VersionDate < SqlTemplate.Arg<DateTime>("versionDate"))
            );
            var versionDtos = Database.Fetch<ContentVersionDto>(template.Sql(new { nodeId, versionDate }));
            foreach (var versionDto in versionDtos)
                PerformDeleteVersion(versionDto.NodeId, versionDto.Id);
        }

        // actually deletes a version
        protected abstract void PerformDeleteVersion(int id, int versionId);

        #endregion

        #region Count

        /// <summary>
        /// Count descendants of an item.
        /// </summary>
        public int CountDescendants(int parentId, string contentTypeAlias = null)
        {
            var pathMatch = parentId == -1
                ? "-1,"
                : "," + parentId + ",";

            var sql = SqlContext.Sql()
                .SelectCount()
                .From<NodeDto>();

            if (contentTypeAlias.IsNullOrWhiteSpace())
            {
                sql
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.Path.Contains(pathMatch));
            }
            else
            {
                sql
                    .InnerJoin<ContentDto>()
                    .On<NodeDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<ContentTypeDto>()
                    .On<ContentTypeDto, ContentDto>(left => left.NodeId, right => right.ContentTypeId)
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.Path.Contains(pathMatch))
                    .Where<ContentTypeDto>(x => x.Alias == contentTypeAlias);
            }

            return Database.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// Count children of an item.
        /// </summary>
        public int CountChildren(int parentId, string contentTypeAlias = null)
        {
            var sql = SqlContext.Sql()
                .SelectCount()
                .From<NodeDto>();

            if (contentTypeAlias.IsNullOrWhiteSpace())
            {
                sql
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.ParentId == parentId);
            }
            else
            {
                sql
                    .InnerJoin<ContentDto>()
                    .On<NodeDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<ContentTypeDto>()
                    .On<ContentTypeDto, ContentDto>(left => left.NodeId, right => right.ContentTypeId)
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.ParentId == parentId)
                    .Where<ContentTypeDto>(x => x.Alias == contentTypeAlias);
            }

            return Database.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// Count items.
        /// </summary>
        public int Count(string contentTypeAlias = null)
        {
            var sql = SqlContext.Sql()
                .SelectCount()
                .From<NodeDto>();

            if (contentTypeAlias.IsNullOrWhiteSpace())
            {
                sql
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId);
            }
            else
            {
                sql
                    .InnerJoin<ContentDto>()
                    .On<NodeDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<ContentTypeDto>()
                    .On<ContentTypeDto, ContentDto>(left => left.NodeId, right => right.ContentTypeId)
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<ContentTypeDto>(x => x.Alias == contentTypeAlias);
            }

            return Database.ExecuteScalar<int>(sql);
        }

        #endregion

        #region Tags

        /// <summary>
        /// Updates tags for an item.
        /// </summary>
        protected void SetEntityTags(IContentBase entity, ITagRepository tagRepo)
        {
            foreach (var property in entity.Properties)
            {
                var tagConfiguration = property.GetTagConfiguration();
                if (tagConfiguration == null) continue;
                tagRepo.Assign(entity.Id, property.PropertyTypeId, property.GetTagsValue().Select(x => new Tag { Group = tagConfiguration.Group, Text = x }), true);
            }
        }

        // FIXME should we do it when un-publishing? or?
        /// <summary>
        /// Clears tags for an item.
        /// </summary>
        protected void ClearEntityTags(IContentBase entity, ITagRepository tagRepo)
        {
            tagRepo.RemoveAll(entity.Id);
        }

        #endregion

        private Sql<ISqlContext> PreparePageSql(Sql<ISqlContext> sql, Sql<ISqlContext> filterSql, Ordering ordering)
        {
            // non-filtering, non-ordering = nothing to do
            if (filterSql == null && ordering.IsEmpty) return sql;

            // preserve original
            var psql = new Sql<ISqlContext>(sql.SqlContext, sql.SQL, sql.Arguments);

            // apply filter
            if (filterSql != null)
                psql.Append(filterSql);

            // non-sorting, we're done
            if (ordering.IsEmpty)
                return psql;

            // else apply ordering
            ApplyOrdering(ref psql, ordering);

            // no matter what we always MUST order the result also by umbracoNode.id to ensure that all records being ordered by are unique.
            // if we do not do this then we end up with issues where we are ordering by a field that has duplicate values (i.e. the 'text' column
            // is empty for many nodes) - see: http://issues.umbraco.org/issue/U4-8831

            var dbfield = GetQuotedFieldName("umbracoNode", "id");
            (dbfield, _) = SqlContext.Visit<NodeDto>(x => x.NodeId); // fixme?!
            if (ordering.IsCustomField || !ordering.OrderBy.InvariantEquals("id"))
            {
                psql.OrderBy(GetAliasedField(dbfield, sql)); // fixme why aliased?
            }

            // create prepared sql
            // ensure it's single-line as NPoco PagingHelper has issues with multi-lines
            psql = Sql(psql.SQL.ToSingleLine(), psql.Arguments);
            return psql;
        }

        private void ApplyOrdering(ref Sql<ISqlContext> sql, Ordering ordering)
        {
            if (sql == null) throw new ArgumentNullException(nameof(sql));
            if (ordering == null) throw new ArgumentNullException(nameof(ordering));

            var orderBy = ordering.IsCustomField
                ? ApplyCustomOrdering(ref sql, ordering)
                : ApplySystemOrdering(ref sql, ordering);

            // beware! NPoco paging code parses the query to isolate the ORDER BY fragment,
            // using a regex that wants "([\w\.\[\]\(\)\s""`,]+)" - meaning that anything
            // else in orderBy is going to break NPoco / not be detected

            // beware! NPoco paging code (in PagingHelper) collapses everything [foo].[bar]
            // to [bar] only, so we MUST use aliases, cannot use [table].[field]

            // beware! pre-2012 SqlServer is using a convoluted syntax for paging, which
            // includes "SELECT ROW_NUMBER() OVER (ORDER BY ...) poco_rn FROM SELECT (...",
            // so anything added here MUST also be part of the inner SELECT statement, ie
            // the original statement, AND must be using the proper alias, as the inner SELECT
            // will hide the original table.field names entirely

            if (ordering.Direction == Direction.Ascending)
                sql.OrderBy(orderBy);
            else
                sql.OrderByDescending(orderBy);
        }

        protected virtual string ApplySystemOrdering(ref Sql<ISqlContext> sql, Ordering ordering)
        {
            // id is invariant
            if (ordering.OrderBy.InvariantEquals("id"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.NodeId), sql);

            // sort order is invariant
            if (ordering.OrderBy.InvariantEquals("sortOrder"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.SortOrder), sql);

            // path is invariant
            if (ordering.OrderBy.InvariantEquals("path"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.Path), sql);

            // note: 'owner' is the user who created the item as a whole,
            //       we don't have an 'owner' per culture (should we?)
            if (ordering.OrderBy.InvariantEquals("owner"))
            {
                var joins = Sql()
                    .InnerJoin<UserDto>("ownerUser").On<NodeDto, UserDto>((node, user) => node.UserId == user.Id, aliasRight: "ownerUser");

                // see notes in ApplyOrdering: the field MUST be selected + aliased
                sql = Sql(InsertBefore(sql, "FROM", ", " + SqlSyntax.GetFieldName<UserDto>(x => x.UserName, "ownerUser") + " AS ordering "), sql.Arguments);

                sql = InsertJoins(sql, joins);

                return "ordering";
            }

            // note: each version culture variation has a date too,
            //       maybe we would want to use it instead?
            if (ordering.OrderBy.InvariantEquals("versionDate") || ordering.OrderBy.InvariantEquals("updateDate"))
                return GetAliasedField(SqlSyntax.GetFieldName<ContentVersionDto>(x => x.VersionDate), sql);

            // create date is invariant (we don't keep each culture's creation date)
            if (ordering.OrderBy.InvariantEquals("createDate"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.CreateDate), sql);

            // name is variant
            if (ordering.OrderBy.InvariantEquals("name"))
            {
                // no culture = can only work on the invariant name
                // see notes in ApplyOrdering: the field MUST be aliased
                if (ordering.Culture.IsNullOrWhiteSpace())
                    return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.Text), sql);

                // culture = must work on variant name ?? invariant name
                // insert proper join and return coalesced ordering field

                var joins = Sql()
                    .LeftJoin<ContentVersionCultureVariationDto>(nested =>
                        nested.InnerJoin<LanguageDto>("lang").On<ContentVersionCultureVariationDto, LanguageDto>((ccv, lang) => ccv.LanguageId == lang.Id && lang.IsoCode == ordering.Culture, "ccv", "lang"), "ccv")
                    .On<ContentVersionDto, ContentVersionCultureVariationDto>((version, ccv) => version.Id == ccv.VersionId, aliasRight: "ccv");

                // see notes in ApplyOrdering: the field MUST be selected + aliased
                sql = Sql(InsertBefore(sql, "FROM", ", " + SqlContext.Visit<ContentVersionCultureVariationDto, NodeDto>((ccv, node) => ccv.Name ?? node.Text, "ccv").Sql + " AS ordering "), sql.Arguments);

                sql = InsertJoins(sql, joins);

                return "ordering";
            }

            // previously, we'd accept anything and just sanitize it - not anymore
            throw new NotSupportedException($"Ordering by {ordering.OrderBy} not supported.");
        }

        private string ApplyCustomOrdering(ref Sql<ISqlContext> sql, Ordering ordering)
        {
            // sorting by a custom field, so set-up sub-query for ORDER BY clause to pull through value
            // from 'current' content version for the given order by field
            var sortedInt = string.Format(SqlContext.SqlSyntax.ConvertIntegerToOrderableString, "intValue");
            var sortedDecimal = string.Format(SqlContext.SqlSyntax.ConvertDecimalToOrderableString, "decimalValue");
            var sortedDate = string.Format(SqlContext.SqlSyntax.ConvertDateToOrderableString, "dateValue");
            var sortedString = "COALESCE(varcharValue,'')"; // assuming COALESCE is ok for all syntaxes

            // needs to be an outer join since there's no guarantee that any of the nodes have values for this property
            var innerSql = Sql().Select($@"CASE
                            WHEN intValue IS NOT NULL THEN {sortedInt}
                            WHEN decimalValue IS NOT NULL THEN {sortedDecimal}
                            WHEN dateValue IS NOT NULL THEN {sortedDate}
                            ELSE {sortedString}
                        END AS customPropVal,
                        cver.nodeId AS customPropNodeId")
                .From<ContentVersionDto>("cver")
                .InnerJoin<PropertyDataDto>("opdata")
                    .On<ContentVersionDto, PropertyDataDto>((version, pdata) => version.Id == pdata.VersionId, "cver", "opdata")
                .InnerJoin<PropertyTypeDto>("optype").On<PropertyDataDto, PropertyTypeDto>((pdata, ptype) => pdata.PropertyTypeId == ptype.Id, "opdata", "optype")
                .LeftJoin<LanguageDto>().On<PropertyDataDto, LanguageDto>((pdata, lang) => pdata.LanguageId == lang.Id, "opdata")
                .Where<ContentVersionDto>(x => x.Current, "cver") // always query on current (edit) values
                .Where<PropertyTypeDto>(x => x.Alias == ordering.OrderBy, "optype")
                .Where<PropertyDataDto, LanguageDto>((opdata, lang) => opdata.LanguageId == null || lang.IsoCode == ordering.Culture, "opdata");

            // merge arguments
            var argsList = sql.Arguments.ToList();
            var innerSqlString = ParameterHelper.ProcessParams(innerSql.SQL, innerSql.Arguments, argsList);

            // create the outer join complete sql fragment
            var outerJoinTempTable = $@"LEFT OUTER JOIN ({innerSqlString}) AS customPropData
                ON customPropData.customPropNodeId = {Constants.DatabaseSchema.Tables.Node}.id "; // trailing space is important!

            // insert this just above the first WHERE
            var newSql = InsertBefore(sql.SQL, "WHERE", outerJoinTempTable);

            // see notes in ApplyOrdering: the field MUST be selected + aliased
            newSql = InsertBefore(newSql, "FROM", ", customPropData.customPropVal AS ordering "); // trailing space is important!

            // create the new sql
            sql = Sql(newSql, argsList.ToArray());

            // and order by the custom field
            // this original code means that an ascending sort would first expose all NULL values, ie items without a value
            return "ordering";

            // note: adding an extra sorting criteria on
            // "(CASE WHEN customPropData.customPropVal IS NULL THEN 1 ELSE 0 END")
            // would ensure that items without a value always come last, both in ASC and DESC-ending sorts
        }

        public abstract IEnumerable<TEntity> GetPage(IQuery<TEntity> query,
            long pageIndex, int pageSize, out long totalRecords,
            IQuery<TEntity> filter,
            Ordering ordering);

        // here, filter can be null and ordering cannot
        protected IEnumerable<TEntity> GetPage<TDto>(IQuery<TEntity> query,
            long pageIndex, int pageSize, out long totalRecords,
            Func<List<TDto>, IEnumerable<TEntity>> mapDtos,
            Sql<ISqlContext> filter,
            Ordering ordering)
        {
            if (ordering == null) throw new ArgumentNullException(nameof(ordering));

            // start with base query, and apply the supplied IQuery
            if (query == null) query = Query<TEntity>();
            var sql = new SqlTranslator<TEntity>(GetBaseQuery(QueryType.Many), query).Translate();

            // sort and filter
            sql = PreparePageSql(sql, filter, ordering);

            // get a page of DTOs and the total count
            var pagedResult = Database.Page<TDto>(pageIndex + 1, pageSize, sql);
            totalRecords = Convert.ToInt32(pagedResult.TotalItems);

            // map the DTOs and return
            return mapDtos(pagedResult.Items);<commit_msg>Moving the sql join and coalesce query in the DocumentRepository.GetBaseQuery(), renaming "ordering" to "variantName" to match the field created in GetBaseQuery.<commit_after>﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using Newtonsoft.Json;
using NPoco;
using Umbraco.Core.Cache;
using Umbraco.Core.Composing;
using Umbraco.Core.Events;
using Umbraco.Core.Logging;
using Umbraco.Core.Models;
using Umbraco.Core.Models.Editors;
using Umbraco.Core.Models.Entities;
using Umbraco.Core.Persistence.DatabaseModelDefinitions;
using Umbraco.Core.Persistence.Dtos;
using Umbraco.Core.Persistence.Factories;
using Umbraco.Core.Persistence.Querying;
using Umbraco.Core.PropertyEditors;
using Umbraco.Core.Scoping;
using Umbraco.Core.Services;
using Umbraco.Core.Services.Implement;
using static Umbraco.Core.Persistence.NPocoSqlExtensions.Statics;
using Content = System.Web.UI.WebControls.Content;

namespace Umbraco.Core.Persistence.Repositories.Implement
{
    internal sealed class ContentRepositoryBase
    {
        /// <summary>
        /// This is used for unit tests ONLY
        /// </summary>
        public static bool ThrowOnWarning = false;
    }

    internal abstract class ContentRepositoryBase<TId, TEntity, TRepository> : NPocoRepositoryBase<TId, TEntity>, IContentRepository<TId, TEntity>
        where TEntity : class, IUmbracoEntity
        where TRepository : class, IRepository
    {
        protected ContentRepositoryBase(IScopeAccessor scopeAccessor, CacheHelper cache, ILanguageRepository languageRepository, ILogger logger)
            : base(scopeAccessor, cache, logger)
        {
            LanguageRepository = languageRepository;
        }

        protected abstract TRepository This { get; }

        protected ILanguageRepository LanguageRepository { get; }

        protected PropertyEditorCollection PropertyEditors => Current.PropertyEditors; // fixme inject

        #region Versions

        // gets a specific version
        public abstract TEntity GetVersion(int versionId);

        // gets all versions, current first
        public abstract IEnumerable<TEntity> GetAllVersions(int nodeId);

        // gets all version ids, current first
        public virtual IEnumerable<int> GetVersionIds(int nodeId, int maxRows)
        {
            var template = SqlContext.Templates.Get("Umbraco.Core.VersionableRepository.GetVersionIds", tsql =>
                tsql.Select<ContentVersionDto>(x => x.Id)
                    .From<ContentVersionDto>()
                    .Where<ContentVersionDto>(x => x.NodeId == SqlTemplate.Arg<int>("nodeId"))
                    .OrderByDescending<ContentVersionDto>(x => x.Current) // current '1' comes before others '0'
                    .AndByDescending<ContentVersionDto>(x => x.VersionDate) // most recent first
            );
            return Database.Fetch<int>(SqlSyntax.SelectTop(template.Sql(nodeId), maxRows));
        }

        // deletes a specific version
        public virtual void DeleteVersion(int versionId)
        {
            // fixme test object node type?

            // get the version we want to delete
            var template = SqlContext.Templates.Get("Umbraco.Core.VersionableRepository.GetVersion", tsql =>
                tsql.Select<ContentVersionDto>().From<ContentVersionDto>().Where<ContentVersionDto>(x => x.Id == SqlTemplate.Arg<int>("versionId"))
            );
            var versionDto = Database.Fetch<ContentVersionDto>(template.Sql(new { versionId })).FirstOrDefault();

            // nothing to delete
            if (versionDto == null)
                return;

            // don't delete the current version
            if (versionDto.Current)
                throw new InvalidOperationException("Cannot delete the current version.");

            PerformDeleteVersion(versionDto.NodeId, versionId);
        }

        //  deletes all versions of an entity, older than a date.
        public virtual void DeleteVersions(int nodeId, DateTime versionDate)
        {
            // fixme test object node type?

            // get the versions we want to delete, excluding the current one
            var template = SqlContext.Templates.Get("Umbraco.Core.VersionableRepository.GetVersions", tsql =>
                tsql.Select<ContentVersionDto>().From<ContentVersionDto>().Where<ContentVersionDto>(x => x.NodeId == SqlTemplate.Arg<int>("nodeId") && !x.Current && x.VersionDate < SqlTemplate.Arg<DateTime>("versionDate"))
            );
            var versionDtos = Database.Fetch<ContentVersionDto>(template.Sql(new { nodeId, versionDate }));
            foreach (var versionDto in versionDtos)
                PerformDeleteVersion(versionDto.NodeId, versionDto.Id);
        }

        // actually deletes a version
        protected abstract void PerformDeleteVersion(int id, int versionId);

        #endregion

        #region Count

        /// <summary>
        /// Count descendants of an item.
        /// </summary>
        public int CountDescendants(int parentId, string contentTypeAlias = null)
        {
            var pathMatch = parentId == -1
                ? "-1,"
                : "," + parentId + ",";

            var sql = SqlContext.Sql()
                .SelectCount()
                .From<NodeDto>();

            if (contentTypeAlias.IsNullOrWhiteSpace())
            {
                sql
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.Path.Contains(pathMatch));
            }
            else
            {
                sql
                    .InnerJoin<ContentDto>()
                    .On<NodeDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<ContentTypeDto>()
                    .On<ContentTypeDto, ContentDto>(left => left.NodeId, right => right.ContentTypeId)
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.Path.Contains(pathMatch))
                    .Where<ContentTypeDto>(x => x.Alias == contentTypeAlias);
            }

            return Database.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// Count children of an item.
        /// </summary>
        public int CountChildren(int parentId, string contentTypeAlias = null)
        {
            var sql = SqlContext.Sql()
                .SelectCount()
                .From<NodeDto>();

            if (contentTypeAlias.IsNullOrWhiteSpace())
            {
                sql
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.ParentId == parentId);
            }
            else
            {
                sql
                    .InnerJoin<ContentDto>()
                    .On<NodeDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<ContentTypeDto>()
                    .On<ContentTypeDto, ContentDto>(left => left.NodeId, right => right.ContentTypeId)
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<NodeDto>(x => x.ParentId == parentId)
                    .Where<ContentTypeDto>(x => x.Alias == contentTypeAlias);
            }

            return Database.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// Count items.
        /// </summary>
        public int Count(string contentTypeAlias = null)
        {
            var sql = SqlContext.Sql()
                .SelectCount()
                .From<NodeDto>();

            if (contentTypeAlias.IsNullOrWhiteSpace())
            {
                sql
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId);
            }
            else
            {
                sql
                    .InnerJoin<ContentDto>()
                    .On<NodeDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<ContentTypeDto>()
                    .On<ContentTypeDto, ContentDto>(left => left.NodeId, right => right.ContentTypeId)
                    .Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId)
                    .Where<ContentTypeDto>(x => x.Alias == contentTypeAlias);
            }

            return Database.ExecuteScalar<int>(sql);
        }

        #endregion

        #region Tags

        /// <summary>
        /// Updates tags for an item.
        /// </summary>
        protected void SetEntityTags(IContentBase entity, ITagRepository tagRepo)
        {
            foreach (var property in entity.Properties)
            {
                var tagConfiguration = property.GetTagConfiguration();
                if (tagConfiguration == null) continue;
                tagRepo.Assign(entity.Id, property.PropertyTypeId, property.GetTagsValue().Select(x => new Tag { Group = tagConfiguration.Group, Text = x }), true);
            }
        }

        // FIXME should we do it when un-publishing? or?
        /// <summary>
        /// Clears tags for an item.
        /// </summary>
        protected void ClearEntityTags(IContentBase entity, ITagRepository tagRepo)
        {
            tagRepo.RemoveAll(entity.Id);
        }

        #endregion

        private Sql<ISqlContext> PreparePageSql(Sql<ISqlContext> sql, Sql<ISqlContext> filterSql, Ordering ordering)
        {
            // non-filtering, non-ordering = nothing to do
            if (filterSql == null && ordering.IsEmpty) return sql;

            // preserve original
            var psql = new Sql<ISqlContext>(sql.SqlContext, sql.SQL, sql.Arguments);

            // apply filter
            if (filterSql != null)
                psql.Append(filterSql);

            // non-sorting, we're done
            if (ordering.IsEmpty)
                return psql;

            // else apply ordering
            ApplyOrdering(ref psql, ordering);

            // no matter what we always MUST order the result also by umbracoNode.id to ensure that all records being ordered by are unique.
            // if we do not do this then we end up with issues where we are ordering by a field that has duplicate values (i.e. the 'text' column
            // is empty for many nodes) - see: http://issues.umbraco.org/issue/U4-8831

            var dbfield = GetQuotedFieldName("umbracoNode", "id");
            (dbfield, _) = SqlContext.Visit<NodeDto>(x => x.NodeId); // fixme?!
            if (ordering.IsCustomField || !ordering.OrderBy.InvariantEquals("id"))
            {
                psql.OrderBy(GetAliasedField(dbfield, sql)); // fixme why aliased?
            }

            // create prepared sql
            // ensure it's single-line as NPoco PagingHelper has issues with multi-lines
            psql = Sql(psql.SQL.ToSingleLine(), psql.Arguments);

            
            // replace the magic culture parameter (see DocumentRepository.GetBaseQuery())
            if (!ordering.Culture.IsNullOrWhiteSpace())
            {
                for (var i = 0; i < psql.Arguments.Length; i++)
                {
                    if (psql.Arguments[i] is string s && s == "[[[ISOCODE]]]")
                    {
                        psql.Arguments[i] = ordering.Culture;
                        break;
                    }
                }
            }
            return psql;
        }

        private void ApplyOrdering(ref Sql<ISqlContext> sql, Ordering ordering)
        {
            if (sql == null) throw new ArgumentNullException(nameof(sql));
            if (ordering == null) throw new ArgumentNullException(nameof(ordering));

            var orderBy = ordering.IsCustomField
                ? ApplyCustomOrdering(ref sql, ordering)
                : ApplySystemOrdering(ref sql, ordering);

            // beware! NPoco paging code parses the query to isolate the ORDER BY fragment,
            // using a regex that wants "([\w\.\[\]\(\)\s""`,]+)" - meaning that anything
            // else in orderBy is going to break NPoco / not be detected

            // beware! NPoco paging code (in PagingHelper) collapses everything [foo].[bar]
            // to [bar] only, so we MUST use aliases, cannot use [table].[field]

            // beware! pre-2012 SqlServer is using a convoluted syntax for paging, which
            // includes "SELECT ROW_NUMBER() OVER (ORDER BY ...) poco_rn FROM SELECT (...",
            // so anything added here MUST also be part of the inner SELECT statement, ie
            // the original statement, AND must be using the proper alias, as the inner SELECT
            // will hide the original table.field names entirely

            if (ordering.Direction == Direction.Ascending)
                sql.OrderBy(orderBy);
            else
                sql.OrderByDescending(orderBy);
        }

        protected virtual string ApplySystemOrdering(ref Sql<ISqlContext> sql, Ordering ordering)
        {
            // id is invariant
            if (ordering.OrderBy.InvariantEquals("id"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.NodeId), sql);

            // sort order is invariant
            if (ordering.OrderBy.InvariantEquals("sortOrder"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.SortOrder), sql);

            // path is invariant
            if (ordering.OrderBy.InvariantEquals("path"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.Path), sql);

            // note: 'owner' is the user who created the item as a whole,
            //       we don't have an 'owner' per culture (should we?)
            if (ordering.OrderBy.InvariantEquals("owner"))
            {
                var joins = Sql()
                    .InnerJoin<UserDto>("ownerUser").On<NodeDto, UserDto>((node, user) => node.UserId == user.Id, aliasRight: "ownerUser");

                // see notes in ApplyOrdering: the field MUST be selected + aliased
                sql = Sql(InsertBefore(sql, "FROM", ", " + SqlSyntax.GetFieldName<UserDto>(x => x.UserName, "ownerUser") + " AS ordering "), sql.Arguments);

                sql = InsertJoins(sql, joins);

                return "ordering";
            }

            // note: each version culture variation has a date too,
            //       maybe we would want to use it instead?
            if (ordering.OrderBy.InvariantEquals("versionDate") || ordering.OrderBy.InvariantEquals("updateDate"))
                return GetAliasedField(SqlSyntax.GetFieldName<ContentVersionDto>(x => x.VersionDate), sql);

            // create date is invariant (we don't keep each culture's creation date)
            if (ordering.OrderBy.InvariantEquals("createDate"))
                return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.CreateDate), sql);

            // name is variant
            if (ordering.OrderBy.InvariantEquals("name"))
            {
                // no culture = can only work on the invariant name
                // see notes in ApplyOrdering: the field MUST be aliased
                if (ordering.Culture.IsNullOrWhiteSpace())
                    return GetAliasedField(SqlSyntax.GetFieldName<NodeDto>(x => x.Text), sql);

                return "variantName"; 
            }

            // previously, we'd accept anything and just sanitize it - not anymore
            throw new NotSupportedException($"Ordering by {ordering.OrderBy} not supported.");
        }

        private string ApplyCustomOrdering(ref Sql<ISqlContext> sql, Ordering ordering)
        {
            // sorting by a custom field, so set-up sub-query for ORDER BY clause to pull through value
            // from 'current' content version for the given order by field
            var sortedInt = string.Format(SqlContext.SqlSyntax.ConvertIntegerToOrderableString, "intValue");
            var sortedDecimal = string.Format(SqlContext.SqlSyntax.ConvertDecimalToOrderableString, "decimalValue");
            var sortedDate = string.Format(SqlContext.SqlSyntax.ConvertDateToOrderableString, "dateValue");
            var sortedString = "COALESCE(varcharValue,'')"; // assuming COALESCE is ok for all syntaxes

            // needs to be an outer join since there's no guarantee that any of the nodes have values for this property
            var innerSql = Sql().Select($@"CASE
                            WHEN intValue IS NOT NULL THEN {sortedInt}
                            WHEN decimalValue IS NOT NULL THEN {sortedDecimal}
                            WHEN dateValue IS NOT NULL THEN {sortedDate}
                            ELSE {sortedString}
                        END AS customPropVal,
                        cver.nodeId AS customPropNodeId")
                .From<ContentVersionDto>("cver")
                .InnerJoin<PropertyDataDto>("opdata")
                    .On<ContentVersionDto, PropertyDataDto>((version, pdata) => version.Id == pdata.VersionId, "cver", "opdata")
                .InnerJoin<PropertyTypeDto>("optype").On<PropertyDataDto, PropertyTypeDto>((pdata, ptype) => pdata.PropertyTypeId == ptype.Id, "opdata", "optype")
                .LeftJoin<LanguageDto>().On<PropertyDataDto, LanguageDto>((pdata, lang) => pdata.LanguageId == lang.Id, "opdata")
                .Where<ContentVersionDto>(x => x.Current, "cver") // always query on current (edit) values
                .Where<PropertyTypeDto>(x => x.Alias == ordering.OrderBy, "optype")
                .Where<PropertyDataDto, LanguageDto>((opdata, lang) => opdata.LanguageId == null || lang.IsoCode == ordering.Culture, "opdata");

            // merge arguments
            var argsList = sql.Arguments.ToList();
            var innerSqlString = ParameterHelper.ProcessParams(innerSql.SQL, innerSql.Arguments, argsList);

            // create the outer join complete sql fragment
            var outerJoinTempTable = $@"LEFT OUTER JOIN ({innerSqlString}) AS customPropData
                ON customPropData.customPropNodeId = {Constants.DatabaseSchema.Tables.Node}.id "; // trailing space is important!

            // insert this just above the first WHERE
            var newSql = InsertBefore(sql.SQL, "WHERE", outerJoinTempTable);

            // see notes in ApplyOrdering: the field MUST be selected + aliased
            newSql = InsertBefore(newSql, "FROM", ", customPropData.customPropVal AS ordering "); // trailing space is important!

            // create the new sql
            sql = Sql(newSql, argsList.ToArray());

            // and order by the custom field
            // this original code means that an ascending sort would first expose all NULL values, ie items without a value
            return "ordering";

            // note: adding an extra sorting criteria on
            // "(CASE WHEN customPropData.customPropVal IS NULL THEN 1 ELSE 0 END")
            // would ensure that items without a value always come last, both in ASC and DESC-ending sorts
        }

        public abstract IEnumerable<TEntity> GetPage(IQuery<TEntity> query,
            long pageIndex, int pageSize, out long totalRecords,
            IQuery<TEntity> filter,
            Ordering ordering);

        // here, filter can be null and ordering cannot
        protected IEnumerable<TEntity> GetPage<TDto>(IQuery<TEntity> query,
            long pageIndex, int pageSize, out long totalRecords,
            Func<List<TDto>, IEnumerable<TEntity>> mapDtos,
            Sql<ISqlContext> filter,
            Ordering ordering)
        {
            if (ordering == null) throw new ArgumentNullException(nameof(ordering));

            // start with base query, and apply the supplied IQuery
            if (query == null) query = Query<TEntity>();
            var sql = new SqlTranslator<TEntity>(GetBaseQuery(QueryType.Many), query).Translate();

            // sort and filter
            sql = PreparePageSql(sql, filter, ordering);


            // get a page of DTOs and the total count
            var pagedResult = Database.Page<TDto>(pageIndex + 1, pageSize, sql);
            totalRecords = Convert.ToInt32(pagedResult.TotalItems);

            // map the DTOs and return
            return mapDtos(pagedResult.Items);
{'fraction_non_alphanumeric': 0.10011298324900525, 'fraction_numerical': 0.000908778307216191, 'mean_word_length': 2.024888558692422, 'pattern_counts': {'":': 0, '<': 223, '<?xml version=': 0, '>': 326, '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': '4826300', 'n_tokens_mistral': 10629, 'n_tokens_neox': 10341, 'n_words': 2816}