Работа с представлениями SQL

Categories: SQL Server

Работа с представлениями SQL

В проектах часто можно встретить использование хранимых процедур для вытяжки табличных данных. Однако, у хранимых процедур есть такие серьезные недостатки как отсутствие автоматической типизации выходных данных, отсутствие гибкости и вариантов абстрагирования.

В данной статье будут рассмотрены возможности представлений для устранения данных недостатков.

В первую очередь рассмотрим типизацию и гибкость выходных данных. В конце будет продемонстрирована возможность создания абстрактного движка для сортировки элементов через as.crud.

Строитель модели EntittyFramework способен автоматически сгенерировать сущности БД в виде прикладных объектов. Это правило справедливо как для таблиц, так и для их представлений. Эти объекты представляются как DbSet на прикладном уровне, которые поддерживают IQueryable, а значит, на прикладном уровне можно уточнить запрос к этим сущностям – фильтр, проекция полей, пагинация.

Рассмотрим произвольную Get-хранимку (детали опущены):

ALTER PROCEDURE [dbo].[GetCountMakeAndModel]
@modelPropertyId int,
@publishedStatusId int,
@parentCategoryId int
AS
BEGIN
WITH groupedMakeModel
AS
(
    SELECT SUM(COUNT(*)) OVER (PARTITION BY ads.categoryID) AS MakeCount,
        COUNT(*) as ModelCount,
        ads.categoryID AS CategoryId,
        category.name AS CategoryName,
        (CASE WHEN avModel.value IS NULL THEN pvModel.value ELSE avModel.value END) AS Model,
        image.path AS ImagePath
    FROM cm_advertisements ads
    INNER JOIN cm_categories category on category.id = ads.categoryID
    LEFT JOIN cm_advertisementValues avModel ON avModel.advertisementID = ads.id AND avModel.propertyID = @modelPropertyId
    LEFT JOIN cm_propertyValuesString pvModel ON pvModel.configurationID = ads.configurationID AND pvModel.propertyID = @modelPropertyId
    LEFT JOIN cm_images image on category.imageID = image.id
    WHERE ads.isReady = 1
        AND statusID = @publishedStatusId
        AND ads.isPart = 0
        AND category.parentID = @parentCategoryId
    GROUP BY ads.categoryID, category.name, CASE WHEN avModel.value IS NULL THEN pvModel.value ELSE avModel.value END, image.path
),
c
AS
(
    SELECT *, DENSE_RANK() OVER(ORDER BY MakeCount DESC) AS CategoryNumber
    FROM groupedMakeModel
)
SELECT MakeCount, CategoryId, CategoryName, ModelCount, Model, ImagePath
FROM c
WHERE CategoryNumber <= 15
ORDER BY MakeCount DESC, CategoryName, ModelCount DESC, Model;
END

Обычная выборка с кучей JOIN и фильтром, структура которого задается жестко, лишь параметры гибкие.

Как эта хранимая процедура типизируется на прикладном уровне? Вручную пишется ViewModel, структура которой должна четко соответствовать структуре результата хранимой процедуры.

 db.GetSQLDataTyped<MakeModelItem>("GetCountMakeAndModel", p, CommandType.StoredProcedure)

 private class MakeModelItem
        {
            public int MakeCount { get; set; }
            public int ModelCount { get; set; }
            public int CategoryId { get; set; }
            public string CategoryName { get; set; }
            public string Model { get; set; }
            public string ImagePath { get; set; }
        }

Вернемся к SQL коду и порассуждаем чисто в терминах БД. Почему для единичного SELECT-запроса делается хранимая процедура? Зачем ограничивать структуру фильтрации? Специально для подобного случая существуют представления SQL:

Представление выполняет функцию фильтра базовых таблиц, на которые оно ссылается. (c) msdn

Далее будет продемонстрировано аналогичное решение, но с помощью представлений.

IF OBJECT_ID('dbo.vw_psl_parcelServices', 'V') IS NOT NULL
    DROP VIEW dbo.vw_psl_parcelServices;
GO

CREATE VIEW dbo.vw_psl_parcelServices
AS
  SELECT 
    p.id      as 'ID' 
  ,  p.name      as 'ParcelName'
  ,  1       as 'IsIncoming'
  ,  ost.code     as 'OrderStatusCode'
  ,  ost.name     as 'OrderStatusName'
  -- HACK
  ,  p.outgoingParcelID   as 'OutgoingParcelID' 
  ,  pt.code      as 'ParcelType'
  ,  p.parentID     as 'ParentID'  
  ,  o.comment     as 'Comment'
  ,  ''       as 'GiftComment'
  ,  vp.MainSrc     as 'MainSrc'
  ,  s.*
  FROM psl_incomingParcels p
  LEFT JOIN vw_psl_incomingParcels vp
   ON vp.ID = p.id   
  LEFT JOIN psl_service_incomingParcels ps
   ON ps.incomingParcelID  = p.id      
  LEFT JOIN vw_psl_services s
   ON ps.serviceID    = s.ServiceID
  LEFT JOIN psl_parcelTypes pt 
   ON pt.id      = p.parcelTypeID
  LEFT JOIN psl_expectedParcelOrders epo
   ON epo.id       = p.orderID
  LEFT JOIN psl_orders o
   ON epo.orderID       = o.id
  LEFT JOIN psl_orderStatuses ost   
   ON ost.id       = o.orderStatusID

  
 UNION ALL
  SELECT 
    p.id      as 'ID' 
  ,  ''       as 'ParcelName'
  ,  0       as 'IsIncoming'
  ,  ost.code     as 'OrderStatusCode'
  ,  ost.name     as 'OrderStatusName'
  -- HACK
  ,  NULL      as 'OutgoingParcelID' 
  ,  'OutgoingParcel'   as 'ParcelType'
  ,  NULL      as 'ParentID'
  ,  o.comment
           as 'Comment'
  ,  p.giftDesc     as 'GiftComment'
  ,  ''       as 'MainSrc'
  ,  s.*
  FROM psl_outgoingParcels p
  LEFT JOIN psl_outgoingParcels vp
   ON vp.ID = p.id
  LEFT JOIN psl_service_outgoingParcels ps
   ON ps.outgoingParcelID   = p.id      
  LEFT JOIN vw_psl_services s
   ON ps.serviceID    = s.ServiceID
  LEFT JOIN psl_orders o
   ON o.id   = p.orderID
  LEFT JOIN psl_orderStatuses ost   
   ON ost.id      = o.orderStatusID
GO

-- test
select * 
from vw_psl_parcelServices
order by ID DESC;

В данном представлении, аналогично, лежит куча JOIN, плюс есть даже немного логики для определения отношения записи к определенному множеству – согласно бизнес-правилам. Более того, заданная структура автоматически импортируется в прикладной уровень в таком виде:

 public partial class vw_psl_parcelServices
    {
        public int ID { get; set; }
        public string ParcelName { get; set; }
        public int IsIncoming { get; set; }
        public string OrderStatusCode { get; set; }
        public string OrderStatusName { get; set; }
        public Nullable<int> OutgoingParcelID { get; set; }
        public string ParcelType { get; set; }
        public Nullable<int> ParentID { get; set; }
        public string Comment { get; set; }
        public string GiftComment { get; set; }
        public string MainSrc { get; set; }
        public Nullable<int> ServiceID { get; set; }
        public string ServiceName { get; set; }
        public string ServiceDesctiption { get; set; }
        public Nullable<decimal> ServiceMinPrice { get; set; }
        public Nullable<decimal> ServiceMaxPrice { get; set; }
        public Nullable<int> ServiceHavePrecisePrice { get; set; }
        public Nullable<decimal> ServicePrice { get; set; }
        public Nullable<int> ServiceTypeID { get; set; }
        public string ServiceTypeCode { get; set; }
        public string ServiceTypeName { get; set; }
        public Nullable<int> ParentServiceTypeID { get; set; }
        public string ParentServiceTypeCode { get; set; }
        public string ParentServiceTypeName { get; set; }
        public Nullable<int> ServiceCategoryID { get; set; }
        public string ServiceCategoryName { get; set; }
        public string ServiceCategoryCode { get; set; }
        public Nullable<int> ParentServiceCategoryID { get; set; }
        public string ParentServiceCategoryName { get; set; }
        public string ParentServiceCategoryCode { get; set; }
        public string ServiceSrc { get; set; }
        public string ServiceAlt { get; set; }
        public string ServiceTitle { get; set; }
    }

И использование представления выглядит так:

// общая логика
 public List<vw_psl_parcelServices> GetParcelServices(ParcelServiceFilter filter)
 {
 var res = repository.db.vw_psl_parcelServices.AsNoTracking().AsQueryable()
 .Where(x => x.OrderStatusCode != "canceled")
 .Where(x => x.OrderStatusCode != "cancelled")
 .Where(x => x.OrderStatusCode != "deleted");

 if (filter.Precise.IsFilled)
 {
 return res.Where(x => x.IsIncoming == (filter.Precise.IsIncoming.Value ? 1 : 0))
 .Where(x => x.ID == filter.Precise.Id)
 .ToList();
 }
 
 if (filter.OutgoingParcelID.HasValue)
 {
 res = res.Where(x => x.OutgoingParcelID == filter.OutgoingParcelID && x.IsIncoming == 1 && x.ParcelType == "product") ;
 }

 if (filter.IdList != null && filter.IdList.Length != 0)
 {
 res = res.Where(x => filter.IdList.Contains(x.ID));
 }

 return res.ToList();
 }

// Использование
 public List<vw_psl_parcelServices> GetParcelServices(ParcelServiceFilter filter)
 {
 var res = repository.db.vw_psl_parcelServices.AsNoTracking().AsQueryable()
 .Where(x => x.OrderStatusCode != "canceled")
 .Where(x => x.OrderStatusCode != "cancelled")
 .Where(x => x.OrderStatusCode != "deleted");

 if (filter.Precise.IsFilled)
 {
 return res.Where(x => x.IsIncoming == (filter.Precise.IsIncoming.Value ? 1 : 0))
 .Where(x => x.ID == filter.Precise.Id)
 .ToList();
 }
 
 if (filter.OutgoingParcelID.HasValue)
 {
 res = res.Where(x => x.OutgoingParcelID == filter.OutgoingParcelID && x.IsIncoming == 1 && x.ParcelType == "product") ;
 }

 if (filter.IdList != null && filter.IdList.Length != 0)
 {
 res = res.Where(x => filter.IdList.Contains(x.ID));
 }

 return res.ToList();
 }

 public List<vw_psl_parcelServices> GetIncomingParcelServices(params int[] id)
 {
 var extra = new ParcelServiceFilter();
 extra.IdList = id;
 return GetParcelServices(extra);
 }

 public List<vw_psl_parcelServices> GetIncomingParcelServices(int id, ParcelServiceFilter extra = null)
 {
 extra = extra ?? new ParcelServiceFilter();
 extra.Precise.Id = id;
 extra.Precise.IsIncoming = true;

 return GetParcelServices(extra);
 }

Данный код наглядно демонстрирует гибкость представлений – на основе одной SQL-структуры можно породить сколько угодно вариаций для каждого частного случая, и в то же время задать общие ограничения, которые справедливы для всех случаев или их подмножеств.

Абстрагирование

Помимо гибкости фильтрации, представления позволяют выполнить сортировку над любым из доступных полей. Ниже будет показан боевой пример. Реализация неуклюжая, однако была написана за три минуты с помощью блокнота и регулярных выражений по полям представлений:

switch (paging.Sort)
{
// powered by regexps =)
        case "ID": orderedQuery = query.OrderBy(x => x.ID, paging.Direction); break;
 case "Cost": orderedQuery = query.OrderBy(x => x.Cost, paging.Direction); break;
 case "Size": orderedQuery = query.OrderBy(x => x.Size, paging.Direction); break;
 case "Name": orderedQuery = query.OrderBy(x => x.Name, paging.Direction); break;
// и еще N кейсов
}

В качестве входных параметров – поле для сортировки и направление. Это берется напрямую из аякс-запроса который присылает as.crud. Изящное решение напрашивается само собой – с помощью отражения определить по какому полю требуется сортировка и выполнить эту сортировку. Однако, это уже тема для движка представлений.

В следующих статьях будут рассмотрены примененные здесь практики написания представлений и проработка решения для абстрактного движка выборки представлений

Периодически мы проводим обучение и даем возможность стажировки на базе нашей платформы с управлением на SQL. Если вам это интересно, то пожалуйста посмотрите информацию об обучении/стажировке по SQL.

Руслан Раянов