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

В прошлой статье «Игра со списками условий» я показал, как строить запросы. В качестве примера я взял несколько условий, написанных на 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))