Инструмент для построения условий

    В прошлой статье «Игра со списками условий» я показал, как строить запросы. В качестве примера я взял несколько условий, написанных на SQL, и использовал их в запросе.

    Но пользователь не умеет писать на SQL, пользователя нельзя подпускать к написанию запросов. Но как же быть, если очень надо? Я предлагаю дать пользователю простой инструмент, результаты которого уже трансформировать в SQL.

    Накидаем список вопросов:

    1. Что нужно пользователю?
    2. Какими понятиями может оперировать пользователь?
    3. Как нам преобразовать эти понятия в запрос?
    4. Как нам проверить, что условие составленное пользователем охватывает все множество записей?
    5. Как нам сделать не одноразовый инструмент?

    В своей практике я прошел через несколько этапов: от простых перечислений в запросах, до построения списков в таблицах, но иногда возникали задачи, где простых списком мало. И тогда я придумал следующий механизм:

    Создаются группы из разнотипных элементов (если требуется). Все элементы в такой группе объединяются по ИЛИ.
    В одно условие входит одна или несколько групп объединяющихся по И и, возможно, одна или несколько групп отрицающих. Примерно так:
    (a1 or a2 or a3) and (b1 or b2) and not(c1 or c2) and not (d1)

    Теперь об элементах:

    1. Один элемент — имя поля и его значение. (Учтите, что ко всему описанному ниже нужно будет прикрутить пользовательский интерфейс, а там нужно использовать понятные обозначения: не «pa.city», а «Город доставки»,…
    2. Можно сделать элементы — константы ( просто прописывается программистом SQL-выражение). В качестве констант очень полезна положительное условие (1=1). С ее помощью можно выбрать все строки, построить множество дополнений, блокировать условия.
    3. Элемент — ссылка на другое условие. Это очень важный и сильный элемент.
    Пример: Усл1 = (a1 or a2 or a3 ), тогда Усл2 = (1=1) and not (Усл1) является дополнением множества Усл1.
    Оба эти условия перекрывают все множество записей, и при изменении Усл1 автоматически меняется Усл2

    В прошлой своей статье я приводил примеры некоторых условий:
    if OBJECT_ID('tempdb..#Conditions') is not null drop table #Conditions
    create table #Conditions (
      ConditionID 	int identity(1,1) primary key,
      Name   		varchar(100),
      [Text] 		varchar(200),
      [Value] 		varchar(200)
    )
    
    insert #Conditions(Name, [Text], [Value]) select 'Доставка в Берлин и Бонн байков', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name in (''Byke'')' , 'sd.OrderQty * pp.Weight'
    insert #Conditions(Name, [Text], [Value]) select 'Доставка в Берлин и Бонн других товаров', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name not in (''Byke'')', 'sd.OrderQty * pp.Weight'
    insert #Conditions(Name, [Text], [Value]) select 'Доставка в другие города', 'pa.city not in (''Berlin'', ''Bonn'')', 'sd.OrderQty * pp.Weight'
    
    


    В свете этой статьи предлагаю построить следующую таблицу (для полномасштабного проекта больше таблиц):

    if OBJECT_ID('tempdb..#ConditionAtoms') is not null drop table #ConditionAtoms
    create table #ConditionAtoms (
      AtomID int identity(1,1) primary key,
      ConditionID int,
      GroupNumber int, -- номер группы, Номер > 0 группа положительных условий, Номер < 0 - группа отрицания
      Field varchar(50), -- Немного упрощаю
      Value varchar(1000) -- Здесь могут хранится значения разных типов
    )
    
    

    Если переписать текст условий в лоб, то должно получиться где-то так
    
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Berlin'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Bonn'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 2, 'ppc.Name', 'Byke'
    
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2,  1, 'pa.city', 'Berlin'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2,  1, 'pa.city', 'Bonn'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, -1, 'ppc.Name', 'Byke'
    
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3,  1, '<SQL>'    , '1=1'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, 'pa.city', 'Berlin'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, 'pa.city', 'Bonn'
    
    


    — а можно и по-другому

    -- Объединяем Берлин и Бонн в первое условие
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Berlin'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Bonn'
    
    -- Ограничиваем первое условие только байками
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 1, '<CONDITION>', '1'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 2, 'ppc.Name', 'Byke'
    
    -- Исключаем из первого условия, то что попало во второе (байки)
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3,  1, '<CONDITION>', '1'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, '<CONDITION>', '2'
    
    -- А все остальное и есть наше последнее условие
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 4,  1, '<SQL>', '1=1'
    insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 4, -1, '<CONDITION>', '1'
    
    


    Чтоб не перегружать текст SQL-кодом остановлюсь не нескольких моментах:

    1. Как видно из последнего кода, для построения Условия № 3, нужно построить все предыдущие, а для этого нужно найти сначала Условия, которые не содержат вложенных условий, на следующем шаге нужно строить только те условия, для которых известны все вложенные условия и т.д.
    2. Защититесь от зацикливания
    3. Защититесь от удаленных условий
    4. Для оптимизации для некоторых типов полей значения можно объединять в «IN».
    5. Для построения запросов очень удобно пользоваться инструкцией WITH и оконными функциями.

    Вот и сама функция для построения условия (обратите внимание, что я вместо временной таблицы, использовал постоянную).

    create function test.ConditionSQL( @ConditionID int , @Lvl int = 0 )
    returns varchar(max)
    begin
      declare @Ret varchar(max)
      if @Lvl < 100
    	with  Data as (
    	select * 
    	,[SQL] = '( ' + case
    	  when Field = '<SQL>' then value
    	  when Field = '<CONDITION>' then test.ConditionSQL( try_convert(int, value), @Lvl+1  )
    	  else Field + ' = ''' + Replace( Value, '''', '''''') + ''''
    	end + ')'
    	,[Row] = ROW_NUMBER() over(partition by GroupNumber  order by Field ) 
    	from ConditionAtoms ca where ConditionID = @ConditionID
    	),
    	Build as (
    	  select GroupNumber, [Row], [Sql] = convert(varchar(max), [SQL]), MaxRow = (select  max([ROW]) from data d where d.GroupNumber = data.GroupNumber )  from Data where [Row] = 1
    	  union all
    	  select Build.GroupNumber, Data.Row, Build.SQL + ' or ' + data.SQL , Build.MaxRow
    	    from Build
    	  join Data on Build.GroupNumber = data.GroupNumber and Build.Row + 1 = Data.Row
    	)
    	select @Ret = iif( @Ret is null, '', @Ret + ' and ' )
    	   + iif( GroupNumber < 0 , ' not ', '' )
    	   + '  (' + [SQL] + ')  '
    	   from Build where [Row] = [MaxRow]
    
      return IsNull( @Ret, 'Error ConditionID = ' + format(@ConditionID, '0') )
    
    end 
    
    

    Сама функция немного упрощена. С помощью еще одной рекурсии можно OR по одному полю объединить в секцию IN. Можно добавить обработку не только списков, но и диапазонов, сравнения с числами и т.д. (на сколько хватит фантазии)

    Пример работы функции:

    print dbo.ConditionSQL(1,0)
    print dbo.ConditionSQL(2,0)
    print dbo.ConditionSQL(3,0)
    print dbo.ConditionSQL(4,0)
    
    ---------------------------------------
    
    
      (( pa.city = 'Berlin') or ( pa.city = 'Bonn'))  
    
      ((   (( pa.city = 'Berlin') or ( pa.city = 'Bonn'))  ))   and   (( ppc.Name = 'Byke'))  
    
     not   ((   ((   (( pa.city = 'Berlin') or ( pa.city = 'Bonn'))  ))   and   (( ppc.Name = 'Byke'))  ))   and   ((   (( pa.city = 'Berlin') or ( pa.city = 'Bonn'))  ))  
    
     not   ((   (( pa.city = 'Berlin') or ( pa.city = 'Bonn'))  ))   and   (( 1=1))  
    
    
    
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 1

      –2
      Немного удивлен реакцией.
      Придумав эту вещь (построение условий + игра с условиями), я ее использую практически во всех областях: от группировки данных при построении отчетов, фильтрации данных, расчета тарифов,…. Единственное что меня ограничивает для более широкого использования — ограничения моей базовой системы и, в какой-то степени, то, что я еще не раскрыл для себя все стороны ее применения.

      Only users with full accounts can post comments. Log in, please.