siteserver 6.1.15 pgsql content表大小写和restAPI不兼容的问题

[xcdiv]

通过测试发现默认处理restAPI的时候QueryRequest类拼接query查询条件的时候是按照原始大小写传递的，但是pgsql环境中创建表的时候是按照小写创建的，这样通过restAPI V1接口查询的时候会引发BUG public class QueryRequest { public int SiteId { get; set; } public int? ChannelId { get; set; } public bool? Checked { get; set; } }

siteserver 在pgsq中创建表或者列的时候都是小写，如果是mssql或者mysql添加表"content_Model"则表默认按照content_Model或这content_model都可以搜索，但是在pgsql下如果通过封装方法请求都是按照"content_model“去请求的，这个直接会引发npgsql查询不到表或者列的bug，建议统一或者出补丁

另外oracle的部分版本可能也会出现类似的问题

[xcdiv]

我通过研究发现一个简单的修复方法，你们看看要不要合并进去，不过我没时间测试那么多种数据库

cms/SiteServer.Web/Controllers/V1/ContentsController.Utils.cs

using Datory; using SiteServer.CMS.DataCache; using SiteServer.CMS.Model; using SiteServer.Utils; using SiteServer.Utils.Enumerations; using SqlKata;

namespace SiteServer.API.Controllers.V1 { public partial class ContentsController { private Query GetQuery(int siteId, int? channelId, QueryRequest request) { var query = Q.Where(nameof(ContentInfo.SiteId).ToLower(), siteId).Where(nameof(ContentInfo.ChannelId).ToLower(), ">", 0);

        if (channelId.HasValue)
        {
            //query.Where(nameof(Abstractions.Content.ChannelId), channelId.Value);
            var channel = ChannelManager.GetChannelInfo(siteId, channelId.Value);
            var channelIds = ChannelManager.GetChannelIdList(channel, EScopeType.All);

            query.WhereIn(nameof(ContentInfo.ChannelId).ToLower(), channelIds);
        }

        if (request.Checked.HasValue)
        {
            query.Where(nameof(ContentInfo.IsChecked).ToLower(), request.Checked.Value.ToString());
        }
        if (request.Top.HasValue)
        {
            query.Where(nameof(ContentInfo.IsTop).ToLower(), request.Top.Value.ToString());
        }
        if (request.Recommend.HasValue)
        {
            query.Where(nameof(ContentInfo.IsRecommend).ToLower(), request.Recommend.Value.ToString());
        }
        if (request.Color.HasValue)
        {
            query.Where(nameof(ContentInfo.IsColor).ToLower(), request.Color.Value.ToString());
        }
        if (request.Hot.HasValue)
        {
            query.Where(nameof(ContentInfo.IsHot).ToLower(), request.Hot.Value.ToString());
        }

        if (request.GroupNames != null)
        {
            query.Where(q =>
            {
                foreach (var groupName in request.GroupNames)
                {
                    if (!string.IsNullOrEmpty(groupName))
                    {
                        q
                            .OrWhere(nameof(ContentInfo.GroupNameCollection).ToLower(), groupName)
                            .OrWhereLike(nameof(ContentInfo.GroupNameCollection).ToLower(), $"{groupName},%")
                            .OrWhereLike(nameof(ContentInfo.GroupNameCollection).ToLower(), $"%,{groupName},%")
                            .OrWhereLike(nameof(ContentInfo.GroupNameCollection).ToLower(), $"%,{groupName}");
                    }
                }
                return q;
            });
        }

        if (request.TagNames != null)
        {
            query.Where(q =>
            {
                foreach (var tagName in request.TagNames)
                {
                    if (!string.IsNullOrEmpty(tagName))
                    {
                        q
                            .OrWhere(nameof(ContentInfo.Tags).ToLower(), tagName)
                            .OrWhereLike(nameof(ContentInfo.Tags).ToLower(), $"{tagName},%")
                            .OrWhereLike(nameof(ContentInfo.Tags).ToLower(), $"%,{tagName},%")
                            .OrWhereLike(nameof(ContentInfo.Tags).ToLower(), $"%,{tagName}");
                    }
                }
                return q;
            });
        }

        if (request.Wheres != null)
        {
            foreach (var where in request.Wheres)
            {
                if (string.IsNullOrEmpty(where.Operator)) where.Operator = OpEquals;
                if (StringUtils.EqualsIgnoreCase(where.Operator, OpIn))
                {
                    query.WhereIn(where.Column.ToLower(), TranslateUtils.StringCollectionToStringList(where.Value));
                }
                else if (StringUtils.EqualsIgnoreCase(where.Operator, OpNotIn))
                {
                    query.WhereNotIn(where.Column.ToLower(), TranslateUtils.StringCollectionToStringList(where.Value));
                }
                else if (StringUtils.EqualsIgnoreCase(where.Operator, OpLike))
                {
                    query.WhereLike(where.Column.ToLower(), $"%{where.Value}%");
                }
                else if (StringUtils.EqualsIgnoreCase(where.Operator, OpNotLike))
                {
                    query.WhereNotLike(where.Column.ToLower(), $"%{where.Value}%");
                }
                else
                {
                    query.Where(where.Column.ToLower(), where.Operator, where.Value);
                }
            }
        }

        if (request.Orders != null)
        {
            foreach (var order in request.Orders)
            {
                if (order.Desc)
                {
                    query.OrderByDesc(order.Column.ToLower());
                }
                else
                {
                    query.OrderBy(order.Column.ToLower());
                }
            }
        }
        else
        {
            query.OrderByDesc(nameof(ContentInfo.IsTop).ToLower(),
                nameof(ContentInfo.ChannelId).ToLower(),
                nameof(ContentInfo.Taxis).ToLower(),
                nameof(ContentInfo.Id).ToLower());
        }

        var page = request.Page > 0 ? request.Page : 1;
        var perPage = request.PerPage > 0 ? request.PerPage : 20;

        query.ForPage(page, perPage);

        return query;
    }
}

}