iBase.ru Forum

Привет Всем!

Пусть есть таблицы:

CREATE TABLE Projects (
    Project_ID BIGINT NOT NULL,
    Parent_ID BIGINT,
    Level SMALLINT,
    CONSTRAINT PK_Projects PRIMARY KEY (Project_ID)
);

CREATE TABLE Project_Users (
    Project_PF BIGINT NOT NULL,
    User_PF BIGINT NOT NULL,
    Level SMALLINT,
    Role SMALLINT,
    CONSTRAINT PK_Project_Users PRIMARY KEY (Project_PF, User_PF)
);

Пусть есть хранимая процедура, которая обходит дерево сверху вниз:

Код: Выделить всё

CREATE PROCEDURE TREE_PROJECTS_GET_NODES_ID (
    ANID BIGINT,
    SELF SMALLINT)
RETURNS (
    ID BIGINT)
AS
BEGIN

  IF ( (:SELF > 0) AND EXISTS(SELECT PROJECT_ID FROM PROJECTS WHERE PROJECT_ID = :ANID) ) THEN
  BEGIN
    ID = :ANID;
    SUSPEND;
  END

  FOR SELECT PROJECT_ID FROM PROJECTS
  WHERE PARENT_ID = :ANID INTO :ID
    DO FOR SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(:ID, 1) INTO :ID
      DO SUSPEND;

END^

Пусть есть два пользователя с id=1, id=2.
Пусть есть дерево:

Код: Выделить всё

 1
 |-2-
    |-3
    |-4

Необходимо дать права 1-му пользователю на поддерево с ID=1, 2-му с ID=3.

На мой взгляд, есть два способа дать права пользователю на определенное поддерево. В нашем случае, таблица Project_Users будет выглядеть:
1 способ.
project_pf=1, user_pf=1, level=0, role=100
project_pf=3, user_pf=2, level=2, role=10

Т.е. давать права на поддерево.

2 способ.
project_pf=1, user_pf=1, level=0, role=100
project_pf=2, user_pf=1, level=0, role=100
project_pf=3, user_pf=1, level=0, role=100
project_pf=4, user_pf=1, level=0, role=100
project_pf=3, user_pf=2, level=2, role=10
project_pf=4, user_pf=2, level=2, role=10

Т.е. давать права на каждый объект.

Здесь Level - это уровень в дереве ( корень - 0 уровень, узлы поддерева корня - 1 и т.д.) на котором пользователь получил права. Т.е. первый пользователь получил права на 0 уровне, а второй - на втором уровне.

Теперь по селектам.
В первом случае, что бы вытащить права придется писать

Код: Выделить всё

LEFT JOIN
    PROJECT_USERS PS ON PS.PROJECT_PF IN (
        SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(8, 1)
    ) ...

=> Плохой план, при больших объемах - долгий селект.
Можно, конечно, предварительно вычислить SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(8, 1). Т.е. привести к виду

Код: Выделить всё

LEFT JOIN
    PROJECT_USERS PS ON PS.PROJECT_PF IN (
       1,2,3,4,5,6
    ) ...

Но это не есть гуд. План запроса при этом лучше, но ограничение на 1500 элементов под IN.

Во втором случае.

Код: Выделить всё

LEFT JOIN
    PROJECT_USERS PS ON PS.PROJECT_PF = PROJECT_FK
    ) ...

Выборки делаются быстро, хороший план запросов.
Но появляется избыточность в таблице PROJECT_USERS, надо писать дополнительные триггеры, например, если добавился проект (таблица PROJECTS):

Код: Выделить всё

  INSERT INTO PROJECT_USERS (PROJECT_PF, USER_PF, LEVEL, ROLE)
   SELECT NEW.PROJECT_ID, USER_PF, ROLE, LEVEL FROM PROJECT_USERS WHERE PROJECT_PF = NEW.PARENT_ID;

Это самый простой триггер. Триггер на перенос поддерева в дереве проектов => сменился PROJECTS.PARENT_ID => поменялся PROJECTS.LEVEL. И так далее...

Еще если мне надо дополнительное поле в таблице PROJECT_USERS то оно так и будет дублироваться на все поддеревья. В этом и избыточность.

Как правильно дать права на элементы дерева?

Вот пример реального селекта по второму случаю:

Код: Выделить всё

SELECT
    P.PROJECT_ID, MN.MODULE_NEW_ID, MN.USER_FK,
    MN.CREATE_DATE, MN.TITLE, MN."MESSAGE",
    U.LAST_NAME, U.FIRST_NAME, U.MIDDLE_NAME
FROM
    MODULE_NEWS MN

JOIN PROJECTS P ON P.PROJECT_ID = MN.PROJECT_FK
    AND P.IFACTIVE = 1 /* ТОЛЬКО АКТИВНЫЕ ПРОЕКТЫ */

JOIN USERS U ON U.USER_ID = MN.USER_FK
    AND U.IFACTIVE = 1 /* ОБЪЕДИНИМ С ПОЛЬЗОВАТЕЛЯМИ => ЕСТЬ ФИО */

LEFT JOIN PROJECT_USERS PU ON PU.PROJECT_PF = MN.PROJECT_FK
    AND PU.USER_PF = ##Здесь ID пользователя который АВТОРИЗОВАЛСЯ## /* ОБЪЕДИНИМ И => НАЙДЕМ ПРАВА */

WHERE MN.PROJECT_FK = P.PROJECT_ID
    AND MN.IFACTIVE = 1
    AND MN.LANGUAGE = 'ru'

    /* SQL ADD */
    AND ( (MN.STATUS = 1) OR (MN.STATUS = 0 AND PU.USER_PF IS NOT NULL) )
    /* END SQL ADD */

ORDER BY MN.CREATE_DATE DESC

По первому:

Код: Выделить всё

SELECT
    P.PROJECT_ID, MN.MODULE_NEW_ID, MN.USER_FK, MN.STATUS,
    MN.CREATE_DATE, MN.TITLE, MN.\"MESSAGE\",
    U.LAST_NAME, U.FIRST_NAME, U.MIDDLE_NAME
FROM
    PROJECTS P
JOIN MODULE_NEWS MN ON MN.PROJECT_FK = P.PROJECT_ID
    AND MN.IFACTIVE = 1
    AND MN.LANGUAGE = 'ru'
    /* SQL ADD */
     OR (MN.STATUS = 0 AND MN.PROJECT_FK IN ($PROJECTS))
    /* END SQL ADD */
JOIN USERS U ON U.USER_ID = MN.USER_FK
	AND U.IFACTIVE = 1
WHERE
    P.IFACTIVE = 1
ORDER BY MN.CREATE_DATE DESC

Где $PROJECTS вычисляется так:

Код: Выделить всё

$pids = $db->GetCol("
  SELECT PU.PROJECT_PF FROM PROJECT_USERS PU, PROJECTS P
  WHERE PU.PROJECT_PF = P.PROJECT_ID
  AND P.IFACTIVE = 1
  AND PU.IFACTIVE = 1
  AND PU.USER_PF = " . ##ID ТЕКУЩЕГО ПОЛЬЗОВАТЕЛЯ В СИСТЕМЕ##);
reset($pids);
foreach ($pids as $name => $value) {
  $pid_nodes = $db->GetCol("
    SELECT P.PROJECT_ID FROM PROJECTS P
    JOIN TREE_PROJECTS_GET_NODES_ID($value, 1) TP ON TP.ID = P.PROJECT_ID
      AND P.IFACTIVE = 1
      AND P.LANGUAGE = 'ru'");
  $projects = array_merge($projects, $pid_nodes);
}

На выходе - массив. Например (1,2,3,4,5,6,7,8)

Добрый день!

To Avenger

Хотя мое мнение Вам уже известно, повторюсь:

Либо Вы храните права пользователей в БД явно (что требует чуть больше места на диске), либо
определяете их при выборке (что требует больше вычислительных ресурсов).

Поскольку на мой непросвещенный взгляд вычислительные ресурсы всегда
в большем дефиците, то я бы предпочел избыточность данных + триггеры,
чем мучать сервер кошмарного вида запросами.
Да и избыточность скорее всего, Вы просто не почувствуете - ну посчитайте
сами: при таком размере записи Project_Users 100 тысяч дополнительных
записей займут пару-тройку мегабайт - стоит ли так экономить?
В конце концов, второе решение более красиво, а значит и более правильно

Что касается "реальному селекта по второму случаю":
а зачем Вам дополнительное условие MN.PROJECT_FK = P.PROJECT_ID в WHERE,
если по нему и выполняется связывание?
И для чего /* SQL ADD */?

Из чистого любопытства можно посмотреть на план запроса?
Просто в старых версиях IB последовательность
t1 inner join t2 on (t1,t2) left join t3 on (t1,t3)
порождала гораздо более неоптимальный план, чем
t1 left join t3 on (t1,t3) inner join t2 on (t1,t2)
Firebird я еще толком не щупал

Кузнецов Евгений писал(а):Что касается "реальному селекта по второму случаю":
а зачем Вам дополнительное условие MN.PROJECT_FK = P.PROJECT_ID в WHERE,
если по нему и выполняется связывание?

Моя опечатка. Спасибо, убрал.

Кузнецов Евгений писал(а):И для чего /* SQL ADD */?

Чтоб потом не забыть, что откуда берется. У меня в коде комментарий есть.

Кузнецов Евгений писал(а):Из чистого любопытства можно посмотреть на план запроса?

Мой, неотредактированный селект. Т.е. t1 inner join t2 on (t1,t2) left join t3 on (t1,t3)

Код: Выделить всё

План
PLAN SORT (JOIN (JOIN (U NATURAL,MN INDEX (USERS_MODULE_NEWS),P INDEX (PK_PROJECTS)),PU INDEX (PK_PROJECT_USERS)))

Адаптированный план
PLAN SORT (JOIN (JOIN (U NATURAL,MN INDEX (USERS_MODULE_NEWS),P INDEX (PK_PROJECTS)),PU INDEX (PK_PROJECT_USERS)))

------ Performance info ------
Prepare time = 15ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 1 172 960
Max memory = 1 250 036
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 1 997

A t1 left join t3 on (t1,t3) inner join t2 on (t1,t2)

Код: Выделить всё

План
PLAN SORT (JOIN (JOIN (MN NATURAL,PU INDEX (PK_PROJECT_USERS)),JOIN (P INDEX (PK_PROJECTS),U INDEX (PK_USERS))))

Адаптированный план
PLAN SORT (JOIN (JOIN (MN NATURAL,PU INDEX (PK_PROJECT_USERS)),JOIN (P INDEX (PK_PROJECTS),U INDEX (PK_USERS))))

------ Performance info ------
Prepare time = 0ms
Execute time = 46ms
Avg fetch time = 2,56 ms
Current memory = 1 327 748
Max memory = 1 377 304
Memory buffers = 2 048
Reads from disk to cache = 1
Writes from cache to disk = 0
Fetches from cache = 2 613

План во втором случае хуже!

А тогда такой триггер это нормально?

Код: Выделить всё

CREATE TRIGGER PROJECT_USERS_AIU0 FOR PROJECT_USERS
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
BEGIN
  IF ( EXISTS(
    SELECT PU.PROJECT_PF FROM PROJECT_USERS PU
    JOIN PROJECTS P ON P.PROJECT_ID = PU.PROJECT_PF
      AND P.PARENT_ID = NEW.PROJECT_PF
    WHERE PU.USER_PF = NEW.USER_PF) ) THEN
  BEGIN
    DELETE FROM PROJECT_USERS WHERE USER_PF = NEW.USER_PF AND PROJECT_PF IN (
      SELECT PROJECT_ID FROM PROJECTS WHERE PARENT_ID = NEW.PROJECT_PF
    );
  END

  INSERT INTO PROJECT_USERS (PROJECT_PF, USER_PF, "ROLE", "LEVEL")
    SELECT PROJECT_ID, NEW.USER_PF, NEW."ROLE", NEW."LEVEL"
    FROM PROJECTS WHERE PARENT_ID = NEW.PROJECT_PF;
END

Рекурсивный триггер получается. Так как вставка в эту таблицу инициирует новую такую же вставку, но УЗЕЛ уже ниже по дереву.

избыточность данных

Избыточность данных не только в этом. Сейчас у меня в таблице PROJECT_USERS есть четыре поля данных. А если я еще 4 поля добавлю, они же будут дублироваться на все поддерево. Хотя мне было бы достаточно знать эти значения только один раз.
Например:
1 способ.
project_pf=1, user_pf=1, level=0, role=100, поле5=знач5, поле6=знач6
project_pf=3, user_pf=2, level=2, role=10, поле5=знач5, поле6=знач6
2 способ.
project_pf=1, user_pf=1, level=0, role=100, поле5=знач5, поле6=знач6
project_pf=2, user_pf=1, level=0, role=100, поле5=знач5, поле6=знач6
project_pf=3, user_pf=1, level=0, role=100, поле5=знач5, поле6=знач6
project_pf=4, user_pf=1, level=0, role=100, поле5=знач5, поле6=знач6
project_pf=3, user_pf=2, level=2, role=10, поле5=знач5, поле6=знач6
project_pf=4, user_pf=2, level=2, role=10, поле5=знач5, поле6=знач6

Или может лучше так?
2 способ.
project_pf=1, user_pf=1, level=0, role=100, поле5=знач5, поле6=знач6
project_pf=2, user_pf=1, level=0, role=100, поле5=NULL, поле6=NULL
project_pf=3, user_pf=1, level=0, role=100, поле5=NULL, поле6=NULL
project_pf=4, user_pf=1, level=0, role=100, поле5=NULL, поле6=NULL
project_pf=3, user_pf=2, level=2, role=10, поле5=знач5, поле6=знач6
project_pf=4, user_pf=2, level=2, role=10, поле5=NULL, поле6=NULL
То есть добавлять эти поля только когда я делаю INSERT, а триггер вставляет NULL?

Доброго времени суток!

avenger писал(а):План во втором случае хуже!

Ну, значит те глюки оптимизатора IB 4.0 в последующих
версиях исправили.

Что касается триггера - я бы убрал IF (EXISTS ...):
все равно то же самое условие есть в WHERE
DELETE - т.е. в итоге проверка выполняется дважды.

О рекурсивности - не знаю, какая у Вас глубина
дерева, но я к пределу в 700 (в FB 1.5 вроде 750)
рекурсивных вызовов никогда и близко даже не подходил.

Об избыточности данных
Не зная Вашей задачи, ничего не могу сказать, нужны
эти поля именно в Project_users.
Но если Вы заполняете их NULL'ами для дочерних записей,
то очевидно для получения значения все равно придется
обращаться к родительской. Все равно, выигрыш в дисковом
пространстве вряд ли получится существенный, а логику
решения испортите.

Ну ладно, завтра сюда заглянут более опытные разработчики,
может еще что-нибудь посоветуют.

Кузнецов Евгений писал(а):я бы убрал IF (EXISTS ...):

По моим тестам триггер при задании прав на элементы дерева вложенность 3000 уровней с IF (EXISTS ...) проходит в четыре раза быстрей, чем без этой проверки. Могу предположить, что оператор EXIST работает быстрее оператора DELETE. Или это из-за того, что когда идет удаление там есть IN SELECT, а когда EXISTS - объединение.

Кузнецов Евгений писал(а):а логику
решения испортите.

Логику не испорчу, так как эти элементы нужны только при ДОБАВЛЕНИИ\РЕДАКТИРОВАНИИ прав. Для вычисления прав на объекты в селекте достаточно PROJECT_PF, USER_PF, "ROLE", "LEVEL" - их необходимо дублировать на все уровни вложенности.
Если дополнительные поля заполнять NULL-ами, тогда мне не надо модифицировать все ТРИГГЕРЫ. А иначе придется добавлять везде в ТРИГГЕРЫ эти дополнительные поля.

avenger писал(а):По моим тестам триггер при задании прав на элементы дерева вложенность 3000 уровней с IF (EXISTS ...) проходит в четыре раза быстрей, чем без этой проверки.

Тогда так

Код: Выделить всё

CREATE TRIGGER PROJECT_USERS_AIU0 FOR PROJECT_USERS 
ACTIVE AFTER INSERT OR UPDATE POSITION 0 
AS 

DECLARE VARIABLE ID1 BIGINT;
DECLARE VARIABLE ID2 BIGINT;

BEGIN 
  FOR SELECT PU.PROJECT_PF, PU.USER_PF
      FROM PROJECT_USERS PU JOIN PROJECTS P 
           ON P.PROJECT_ID = PU.PROJECT_PF AND P.PARENT_ID = NEW.PROJECT_PF  
      WHERE PU.USER_PF = NEW.USER_PF  
      INTO :ID1, :ID2
  BEGIN 
    DELETE FROM PROJECT_USERS 
    WHERE PROJECT_PF = :ID1 and USER_PF = :ID2;
  END 
  INSERT INTO PROJECT_USERS (PROJECT_PF, USER_PF, "ROLE", "LEVEL") 
    SELECT PROJECT_ID, NEW.USER_PF, NEW."ROLE", NEW."LEVEL" 
    FROM PROJECTS WHERE PARENT_ID = NEW.PROJECT_PF; 
END

Как рациональнее построить триггер на удаление?

У меня сейчас:

Код: Выделить всё

CREATE TRIGGER PROJECT_USERS_AD0 FOR PROJECT_USERS
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
  IF ( EXISTS(
    SELECT PU.PROJECT_PF FROM PROJECT_USERS PU
    JOIN PROJECTS P ON P.PROJECT_ID = PU.PROJECT_PF
      AND P.PARENT_ID = OLD.PROJECT_PF
    WHERE PU.USER_PF = OLD.USER_PF) ) THEN
  BEGIN
    DELETE FROM PROJECT_USERS WHERE USER_PF = OLD.USER_PF AND PROJECT_PF IN (
      SELECT PROJECT_ID FROM PROJECTS WHERE PARENT_ID = OLD.PROJECT_PF
    );
  END
END

Но это не рационально. Самый рациональный вариант невозможен. Он такой:

Код: Выделить всё

DELETE FROM PROJECT_USERS PU
JOIN TREE_PROJECTS_GET_NODES_ID(OLD.PROJECT_PF, 1) TP ON TP.ID = PU.PROJECT_PF
WHERE PU.USER_PF = OLD.USER_PF

Кузнецов Евгений писал(а): Тогда так
Код: Выделить всё
CREATE TRIGGER PROJECT_USERS_AIU0 FOR PROJECT_USERS 
ACTIVE AFTER INSERT OR UPDATE POSITION 0 
AS 
...

Мне не подойдет, есть AFTER триггер на удаление.

avenger писал(а):Самый рациональный вариант невозможен.

Код: Выделить всё

EXECUTE PROCEDURE TREE_GET_NODES_ID_AND_KILL(OLD.PROJECT_PF, 1);

WildSery писал(а):
Код: Выделить всё
EXECUTE PROCEDURE TREE_GET_NODES_ID_AND_KILL(OLD.PROJECT_PF, 1);

А без процедуры никак нельзя?

И в процедуре наверняка будет цикл, в котором происходит удаление. А каждое удаление будет вызывать этот же триггер на удаление. => Этот вариант схож с моим первоначальным. Надо за один DELETE удалить все нужные записи.

avenger писал(а):Надо за один DELETE удалить все нужные записи.

Ты думаешь, что если запрос выполнится один раз для группы записей, то этот триггер вызываться больше не будет?
Так не пробовал

Код: Выделить всё

DELETE FROM PROJECT_USERS PU
  WHERE PU.USER_PF = OLD.USER_PF AND
    EXISTS (SELECT 1 FROM PROJECTS P WHERE P.PROJECT_ID = PU.PROJECT_PF AND P.PARENT_ID = OLD.PROJECT_PF);

WildSery писал(а):Ты думаешь, что если запрос выполнится один раз для группы записей, то этот триггер вызываться больше не будет?

Нет, я так не думаю. Поэтому у меня стояла проверка на exists. Ваш вариант работает быстрее моего, но все равно долго.

Код: Выделить всё

select * FROM PROJECT_USERS PU
JOIN TREE_PROJECTS_GET_NODES_ID(1, 1) TP ON TP.ID = PU.PROJECT_PF
WHERE PU.USER_PF = 1

показывает все мне нужные для удаления записи, и выполнятся на порядок быстрее. Плохо, что нельзя зделать так:

Код: Выделить всё

DELETE FROM PROJECT_USERS PU
JOIN TREE_PROJECTS_GET_NODES_ID(OLD.PROJECT_PF, 1) TP ON TP.ID = PU.PROJECT_PF
WHERE PU.USER_PF = OLD.USER_PF

Добрый день!

avenger писал(а):Мне не подойдет, есть AFTER триггер на удаление.

Ничего не понимаю. Какая триггеру AFTER DELETE разница, как удалена запись - одним запросом или в цикле? Все равно он будет вызван для каждой удаленной.

iBase.ru Forum

Права на ветки дерева

Права на ветки дерева