Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вычислить арифметическое выражение неизвестное заранее  [new]
SergASh
Member

Откуда:
Сообщений: 167
Привет всем!

Есть таблица, в которой хранятся геометрические размеры объектов.
Также есть таблица, где описаны ограничения, которым эти размеры должны удовлетворять.
Ограничение определяется формулой, связывающей арифметическое выражение с числовой
константой через знак неравенства. Задача найти все объекты, которые не удовлетворяют
условиям, точнее все комбинации объектов и нарушенных ими ограничений.

Всё сводится к тому, что надо вычислить арифметическое выражение, которое на момент
написания кода неизвестно. Но в лоб составить и выполнить динамический запрос страшно,
потому что формулы приходят в систему извне и там что угодно может быть. Кроме того,
надо быть уверенным, что в выражении присутствуют только предопределенные переменные
(в примере ниже это только Length, Width и Height).

Что посоветуете?

+ Пример

CREATE TABLE tblBox
(
  BoxId INT IDENTITY PRIMARY KEY
 ,Length INT NOT NULL
 ,Width INT NOT NULL
 ,Height INT NOT NULL
)

CREATE TABLE tblConstraint
(
  ConstraintId INT IDENTITY PRIMARY KEY
 ,Formula VARCHAR( 500 ) NOT NULL
)

INSERT
  INTO tblBox
       ( Length
        ,Width
        ,Height
       )
  VALUES ( 5, 15, 20 )
        ,( 10, 25, 4 )
        ,( 100, 100, 100 )

INSERT
  INTO tblConstraint
       ( Formula
       )
  VALUES ( '( Length + Width ) * 2 <= 50' )
        ,( 'Width <= 75' )
        ,( 'Length * Width * Height <= 1200' )

Должно получиться:
BoxIdConstraintId
13
21
31
32
33

19 фев 16, 18:43    [18842795]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
SergASh,

ИМХО делать это на уровне sql извращение, а так либо разбирать формулу на атомы и проверять логику. Извращённо: можно выкинуть из формулы Replace-ом все свои поля, пробелы мат. знаки(хотя если в конце "+" напишут то уже ошибка) , и анализировать что осталось, кроме того числовые значения могут быть как 1.1 так и 1,1 кол-во и парность скобок... Вообщем изврат :)
19 фев 16, 18:52    [18842821]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 601
SergASh,

.Net CLR сборку с методом, куда передавать что вы хотите посчитать. Вычисляется вне ядра SQL, логика там может быть развесистая + можно использовать существующие библиотеки для парсинга.
19 фев 16, 19:26    [18842915]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
SergASh
Member

Откуда:
Сообщений: 167
Ferdipux,

Да вот как раз CLR я бы и не хотел испльзовать.

Пока склоняюсь к динамическому запросу.
Но пока не понимаю как его выполнить от имени пользователя, который не сможет навредить.
19 фев 16, 19:47    [18843001]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
SergASh,

Execute as?
19 фев 16, 22:17    [18843526]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
SergASh,

ну во первых наверное стоит предложить это сделать на клиенте
на мой взгляд там ловчее ошибки обрабатывают, хотя теоретически можно и на сервере...
далее по моему надо четко определить что совершенно произвольного выражения тут не должно быть
выражения должен составлять программист, а не юзер, это без вариантов
юзер даже в экселе какой нибедь бесконечный цикл закрутит и повесит все...
ну а если пишет программист то в общем будет обычный программный код
при добавлении/изменении цифр проверять их по всем формулам
при добавлении/изменении формулы проверять их по всем цифрам
ну а если все проверено то собирай как хочешь и исполняй exec-ом

я похожую вещь реализовал у себя, доступ к страницам приложения так разграничивал
довольно криво и сложно получилось, но работает до сих пор
20 фев 16, 00:35    [18843828]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
SergASh,

извиняюсь, попалась тема с которой сам долго работал и полез советовать
перечитал что в названии именно "неизвестное выражение"
а я совсем не про то...
20 фев 16, 00:42    [18843836]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
SergASh
Member

Откуда:
Сообщений: 167
leov,

Под спойлером я привел пример того, что хотелось бы получить.
Выражение - только 4 арифметических действия и скобки.
Выражения вводят хоть и не программисты, но ответственные операторы тех.поддержки.
20 фев 16, 14:46    [18846285]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21491
C одной стороны
SergASh
формулы приходят в систему извне и там что угодно может быть

С другой
SergASh
Выражения вводят хоть и не программисты, но ответственные операторы тех.поддержки.


Как я понимаю, теоретически несложно заставить их вводить выражения по строго заданной форме. В этом случае я бы предложил изменить формат выражения, доведя его до состояния "как в калькуляторе" - в такой форме значение выражения легко может быть посчитано достаточно несложной функцией.
20 фев 16, 15:37    [18846692]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
SergASh,

Дорабатывайте по вкусу
+
use tempdb;
go

create table dbo.tblBox
(
  BoxId int identity primary key,
  Length int not null,
  Width int not null,
  Height int not null
);

create table dbo.tblConstraint
(
  ConstraintId int identity primary key,
  Formula varchar(500) not null
);
go

create function dbo.fnEvaluateConstraints
(
 @Length int,
 @Width int,
 @Height int
)
returns table
as
return (
 select cast(null as int) as ConstraintId, cast(null as bit) as Successfully where 1 = 0
);
go

create trigger dbo.trBuildEvaluationConstraintsFunction
on dbo.tblConstraint
with execute as 'dbo'
for insert, update, delete
as
begin
 set nocount on;

 if not exists(select 1 from inserted) and not exists(select 1 from deleted)
  return;

 declare @s varchar(max);

 with t(x) as
 (
  select
   ' union all select ' + cast(ConstraintId as varchar(10)) + ' as ConstraintId, cast(case when ' + a.Predicates + ' then 1 else 0 end as bit) as Successfully'
  from
   dbo.tblConstraint with (tablock) cross apply
   (select replace(replace(replace(Formula, 'Length', '@Length'), 'Width', '@Width'), 'Height', '@Height')) a(Predicates)
  for xml path(''), type
 )
 select
  @s = stuff(x.value('.', 'varchar(max)'), 1, len(' union all '), '')
 from
  t;

 if @s is not null
  select @s = 'alter function dbo.fnEvaluateConstraints(@Length int, @Width int, @Height int) returns table as return (' + @s + ');'
 else
  select @s = 'alter function dbo.fnEvaluateConstraints(@Length int, @Width int, @Height int) returns table as return (select cast(null as int) as ConstraintId, cast(null as bit) as Successfully where 1 = 0);'

 exec(@s);
end;
go

insert into dbo.tblBox
 (Length, Width, Height)
values
 (5, 15, 20),
 (10, 25, 4 ),
 (100, 100, 100);

insert into dbo.tblConstraint
 (Formula)
values
 ('( Length + Width ) * 2 <= 50'),
 ('Width <= 75'),
 ('Length * Width * Height <= 1200');
go

select
 b.BoxId, c.ConstraintId
from
 dbo.tblBox b cross apply
 dbo.fnEvaluateConstraints(b.Length, b.Width, b.Height) c
where
 c.Successfully = 0;
go

drop function dbo.fnEvaluateConstraints;
drop table dbo.tblBox, dbo.tblConstraint;
go
20 фев 16, 15:47    [18846738]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить арифметическое выражение неизвестное заранее  [new]
ЦБ
Member [заблокирован]

Откуда:
Сообщений: 2773
use tempdb
go

create FUNCTION dbo.fnEval(@expression varchar(8000))
RETURNS 
	@result TABLE (Result sql_variant)
AS
BEGIN
	DECLARE @object int
	DECLARE @res sql_variant 
	DECLARE @hr int
	--
	EXEC @hr=sp_OACreate 'ScriptControl', @object OUT
	EXEC @hr=sp_OASetProperty @object, 'Language', 'vbscript'
	EXEC @hr=sp_OAMethod @object, 'Eval', @res OUT, @expression 
	EXEC @hr=sp_OADestroy @object
	--
	insert into @result	select @res
	--	
	RETURN 
END
GO

----------------------------
select	b.*, c.* 
	,replace(replace(replace(c.Formula,'Length',str(b.Length)),'Width',str(b.Width)),'Height',str(b.Height)) as expr
	,a.Result
from		tblBox			b
cross join	tblConstraint	c

cross apply dbo.fnEval(replace(replace(replace(Formula,'Length',str(Length)),'Width',str(Width)),'Height',str(Height))) a

where a.Result='False'

BoxIdLengthWidthHeightConstraintIdFormulaexprResult
1515203Length * Width * Height <= 1200 5 * 15 * 20 <= 1200False
2102541( Length + Width ) * 2 <= 50( 10 + 25 ) * 2 <= 50False
31001001001( Length + Width ) * 2 <= 50( 100 + 100 ) * 2 <= 50False
31001001002Width <= 75 100 <= 75False
31001001003Length * Width * Height <= 1200 100 * 100 * 100 <= 1200False
20 фев 16, 18:34    [18847310]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить