Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
Исходные данные:
началоконец
1.1.201131.1.2011
1.2.201128.2.2011
1.3.201131.3.2011

Необходимо, что бы при попытке задать значение, уже входящее в диапазон, ошибку. Например: 2.2.2011 / 04.02.2011 - должно вызвать ошибку.
А не входящие в период должны успешно добавляться в таблицу, например: 1.4.2011 / 4.4.2011.

Или не правильно формулирую вопрос, или google молчит по этому поводу. Подскажите?
8 фев 11, 02:08    [10199809]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
А что сложного?
Пересекаются, если один из концов (с, по) лежит внутри какого-либо диапазона (:FROM between begin and end OR :TO between...)
Ну и определись, как поступать если входит только один конец, например 2.2.2011 / 4.4.2011
8 фев 11, 02:24    [10199814]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
Вячеслав Любомудров
А что сложного?
Пересекаются, если один из концов (с, по) лежит внутри какого-либо диапазона (:FROM between begin and end OR :TO between...)
Ну и определись, как поступать если входит только один конец, например 2.2.2011 / 4.4.2011

Все верно, но как это сделать ключом проверки на условие (constraint ckeck)? Если возложить это действие на триггер - тот будет "ругаться" на мутацию таблицу. Оно и логично будет.
8 фев 11, 02:31    [10199816]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Декларативным ограничением тут не получится
Только через триггера с обходом мутации (строчным триггером перед вставкой каждой строки заполняем PL/SQL табличку, после вставки всех строк, операторным триггером, проверяем, не нарушают ли вставленные строки нашего ограничения)
8 фев 11, 02:57    [10199821]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
Вячеслав Любомудров,

Спасибо Вам за информацию и идею.
8 фев 11, 11:26    [10200885]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Вячеслав Любомудров
А что сложного?
Пересекаются, если один из концов (с, по) лежит внутри какого-либо диапазона (:FROM between begin and end OR :TO between...)


А если не лежит? Например в ту таблицу, что приведена вставим 31.12.2010 - 1.4.2011.

Кстати, проверять можно одним post insert,update триггером всю целостность. Например непустотой self-join для попадания концов в интервал (за искл. самого себя). Ну и генерить exception.
8 фев 11, 11:41    [10201002]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
Вячеслав Любомудров,

Ага, нашел потенциальный баг: как быть с многопользовательским режимом?
Если два пользователя одновременно начнут вставку правильных, но одинаковых диапазонов (или пересекающихся)?
8 фев 11, 11:44    [10201029]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Elic
Member

Откуда:
Сообщений: 29980
Сергей Арсеньев
Кстати, проверять можно одним post insert,update триггером всю целостность. Например непустотой self-join для попадания концов в интервал (за искл. самого себя). Ну и генерить exception.
О конкурентной работе нужно помнить, чтобы не выдумывать решений, о неработоспособности которых жизнь заставит вспомнить потом.
8 фев 11, 11:48    [10201046]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
Сергей Арсеньев
А если не лежит? Например в ту таблицу, что приведена вставим 31.12.2010 - 1.4.2011.

Точно, верно заметили. Включил в проверку дополнительное условие. Спасибо.
8 фев 11, 11:54    [10201077]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

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

Согласен, нагрузку это будет генерить, но обеспечивает консистентность - ибо 1 выборка.
8 фев 11, 11:57    [10201091]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
-2-
Member

Откуда:
Сообщений: 15330
Сергей Арсеньев
но обеспечивает консистентность - ибо 1 выборка.
не глупи, речь про конкуренцию.
8 фев 11, 12:00    [10201111]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ioannov
Вячеслав Любомудров
А что сложного?
Пересекаются, если один из концов (с, по) лежит внутри какого-либо диапазона (:FROM between begin and end OR :TO between...)
Ну и определись, как поступать если входит только один конец, например 2.2.2011 / 4.4.2011

Все верно, но как это сделать ключом проверки на условие (constraint ckeck)? Если возложить это действие на триггер - тот будет "ругаться" на мутацию таблицу. Оно и логично будет.


stff примерно так
8 фев 11, 12:01    [10201116]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
Я реализовал так:
1. строчным триггером перед вставкой - во временную таблицу
2. операторным триггером получаю к-во записей нарушающие условие, если есть - генерирую исключение.

Полагаю 2 триггера не так уж и ужасно.
Вот только беспокоюсь, может-ли получиться ситуация, при которой схема из триггеров пропустит таки что-то?
8 фев 11, 12:01    [10201123]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
ioannov
Member

Откуда: Украина, Кривой Рог
Сообщений: 122
orawish, ознакамливаюсь
8 фев 11, 12:03    [10201135]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
-2-
Member

Откуда:
Сообщений: 15330
ioannov
Полагаю 2 триггера не так уж и ужасно
11g compound trigger
8 фев 11, 12:06    [10201163]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
-2-,

Я же сказал, что согласен с тем что если это массовая операция то будет тормозить как...

Но вопрос в том, что если кто-то селал merge и в нем понавставлял, поудалял, поизменял а параллельно, кто-то что-то закомитил?

Ну и вопорс, как его решить без конкуренции?
8 фев 11, 12:07    [10201171]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Elic
Member

Откуда:
Сообщений: 29980
Сергей Арсеньев
Согласен, нагрузку это будет генерить, но обеспечивает консистентность - ибо 1 выборка.
RTFM Concurrency (FAQ)
8 фев 11, 12:14    [10201252]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

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

Понял я туп, глуп и не образован.


P.S. а вьюшку с instead of триггером хотя бы можно?
8 фев 11, 12:22    [10201307]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Elic
Member

Откуда:
Сообщений: 29980
Сергей Арсеньев
P.S. а вьюшку с instead of триггером хотя бы можно?
А конкуренции какая от этого разница?
8 фев 11, 12:27    [10201337]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

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

Да никакой. Все равно перед проверкой талицу лочить, а после тут же комитить.
Хотя вариант, вести цепочку и лочить только соседей предпочтительней.
8 фев 11, 12:37    [10201407]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Elic
Member

Откуда:
Сообщений: 29980
Сергей Арсеньев
Хотя вариант, вести цепочку и лочить только соседей предпочтительней.
Параллельные вставки соседей не имеют, но друг другу противоречат. Что блокировать?
8 фев 11, 12:43    [10201458]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

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

Только в случае первого инсерта не закомиченного - вся таблица пуста. Но это можно разрулить специальным инсталляционной записью которая во view игнорируется.

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

Проблемы - как раз не в конкуренции а в множественной операции у себя -
одной операцией изменить старый интервал и влезть на его место может не получиться.
8 фев 11, 12:54    [10201526]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Как??!
Guest
Сергей Арсеньев
Elic,
Проверяем, что соседи ссылаются друг на друга - (т.е мы не накрыли кого-либо).
Проверяем, что не налезаем на сосоедей.
Встраиваемся между ними (меняем их ссылки если это первый специальный просто заменяем его).

Проблемы - как раз не в конкуренции


примерчик в sqlplus для 3-х конкурентных сессий покажи
8 фев 11, 12:59    [10201556]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Вячеслав Любомудров
Только через триггера с обходом мутации
Если интервалы могут быть с дискретностью день, то о мутации можно сильно не беспокоится:
SQL> alter session set nls_date_format = 'dd.mm.yyyy'
SQL> create table test_check as
  2  select to_date('1.1.2011') x1, to_date('31.1.2011') x2 from dual
  3  union all select to_date('1.2.2011') x1, to_date('28.2.2011') x2 from dual
  4  union all select to_date('1.3.2011') x1, to_date('31.3.2011') x2 from dual;
 
Table created
SQL> create table test_check_ext as
  2  SELECT x1+column_value-1 x
  3  FROM test_check,
  4  (SELECT LEVEL column_value FROM dual CONNECT BY LEVEL <= 100)
  5  where x2 - x1 + 1 >= column_value;
 
Table created
SQL> alter table TEST_CHECK_EXT add constraint TEST_CKECK_EXT_PK primary key (X);
 
Table altered
SQL> CREATE OR REPLACE TRIGGER tr_test_check
  2  	BEFORE INSERT ON test_check
  3  	FOR EACH ROW
  4  BEGIN
  5  	INSERT INTO test_check_ext
  6      SELECT trunc(:NEW.x1) + LEVEL - 1 FROM dual CONNECT BY LEVEL <= trunc(:NEW.x2) - trunc(:NEW.x1) + 1;
  7  END;
  8  /
 
Trigger created
SQL> insert into test_check values(to_date('2.2.2011'),to_date('4.2.2011'));
 
insert into test_check values(to_date('2.2.2011'),to_date('4.2.2011'))
 
ORA-00001: unique constraint (TEST.TEST_CKECK_EXT_PK) violated
ORA-06512: at "TEST.TR_TEST_CHECK", line 2
ORA-04088: error during execution of trigger 'TEST.TR_TEST_CHECK'
SQL> insert into test_check values(to_date('1.4.2011'),to_date('4.4.2011'));
 
1 row inserted
SQL> select * from test_check;
 
X1          X2
----------- -----------
01.01.2011  31.01.2011
01.02.2011  28.02.2011
01.03.2011  31.03.2011
01.04.2011  04.04.2011
Если же использовать Extensible Indexing, то и триггер не нужен.
Не говоря уже о том, что ТС может просто поддерживать табличку с набором дней интервалов, а исходные данные получать ч-з start_of_group.
Но, опять же, это все актуально, если верно предположение про дискретность интервалов.
8 фев 11, 13:42    [10201880]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение уникальности на период (две даты: с, по)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Как??!,

Предположим:
Мы не будем вставлять интервалы из BC - принудительно считаем это ошибкой.
+ Есть внутрянняя таблица inerTable и вьюха outerView...
create table innerTable (
 beginDate date,
 endDate date,
 leftNeighbor rowid,
 rightNeighbor rowid
);

insert into  innerTable (beginDate,endDate)
 values(to_date('-0001/01/01','syyyy/mm/dd'),to_date('-0001/01/01','syyyy/mm/dd'));
 
create or replace view outerView as
 select beginDate,endDate from innerTable
  where beginDate>to_date('-0001/01/01','syyyy/mm/dd')
     or endDate>to_date('-0001/01/01','syyyy/mm/dd');


Поэтому инсталляционная запись в полях начало и конец интервала имеет дату из BC
(по ней она отфильтровывается в пользовательской view) и ссылки rightNeighbor и leftNeighbor - null;

Действия любой транзакции по вставке нового интервала (newBeginDate,newEndDate):

1. Проверка на непустоту переданных параметров, их собственную непротеворичивость и что они не BC

2.
+ лочим соседей
select * from innerTable
 where rowID in (
 select RID from (select rowID RID from innerTable
  where endDate<to_date(:newBeginDate,'dd.mm.yyyy')
   order by endDate desc)
 where rownum=1   
 union all
 select RID from (select rowID RID from innerTable
  where beginDate>to_date(:newEndDate,'dd.mm.yyyy')
   order by beginDate asc)
 where rownum=1   
) 
 for update;


3a. Проверяем, что левый сосед ссылается справа на null - себя ссылаем на него же (ставим себе его rowid слева, а с права на null), вставляем себя меняем ссылку у левого соседа на то что наш rowID справа. Все.

3б. Если справа у левого соседа не null. Проверяем обоих соседей на то, что они ссылались друг на друга (если нет - облом - пересечение).

4б. Ставим себе метки соседей, вставляем себя, меняем ссылки у соседей.

В общих словах так, построчный update и delete домыслить самим.
8 фев 11, 14:17    [10202156]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить