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