Изменения представления — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Установка)
 
(не показаны 22 промежуточные версии 2 участников)
Строка 1: Строка 1:
 
==Изменение VIEW==
 
==Изменение VIEW==
  
−
*изменяет view - добавляет поля по 1 штуке<br>
+
*изменяет view,таблицы<br>
−
пока что без добавления join'ов - там всё не так однозначно<br><br>
+
пока что без добавления join'ов во view - там всё не так однозначно<br><br>
−
Пример: execute procedure pr_alter_view('VW_WAREBASE','source','wb.mmbsh')
+
  
 +
==Пример==
 +
<PRE>
 +
/* создание генератора */
 +
execute procedure PR_ALTER_VIEW('','','gen1111','generator');
 +
/* создание или добавление в конец триггера */
 +
execute procedure pr_alter_view('SELECTMODES','SELECTMODES_BI','--test','TRIGGER','BEFORE INSERT')
 +
/* создание индекса */
 +
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100500','index');
 +
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100501','DESCENDING INDEX');
 +
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100502','ASCENDING INDEX');
 +
/* первичный ключ */
 +
execute procedure PR_ALTER_VIEW('TEMP_MNN','ID','','PRIMARY KEY');
 +
/* изменение таблицы */
 +
execute procedure PR_ALTER_VIEW('DOC_DETAIL','MGN_ID1','DM_DATETIME','TABLE');
 +
/* изменение вьюхи */
 +
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','VIEW');
 +
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');
 +
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','');
 +
/* создание домена */
 +
execute procedure PR_ALTER_VIEW('DM_BIGINT1','','BIGINT','DOMAIN');
 +
 +
</PRE>
 +
 +
==Установка==
 
<PRE>
 
<PRE>
 +
 
SET TERM ^ ;
 
SET TERM ^ ;
  
Строка 12: Строка 36:
 
     NEW_VW_FIELD DM_TEXT,
 
     NEW_VW_FIELD DM_TEXT,
 
     NEW_VW_VALUE DM_TEXT,
 
     NEW_VW_VALUE DM_TEXT,
−
     NEW_VW_JOIN DM_TEXT = null)
+
     NEW_VW_JOIN DM_TEXT = null,
 +
    NEW_EVENT DM_TEXT = null)
 
as
 
as
 
declare variable I DM_ID;
 
declare variable I DM_ID;
Строка 21: Строка 46:
 
declare variable L integer;
 
declare variable L integer;
 
begin
 
begin
−
   if (:NEW_VW_JOIN is null) then
+
   if ((:NEW_VW_JOIN is null) or (:NEW_VW_JOIN = '') or (upper(:NEW_VW_JOIN) = 'VIEW'))  then
−
    NEW_VW_JOIN = '';
+
   begin
−
   select list(trim(RDB$FIELD_NAME))
+
    select list(trim(RDB$FIELD_NAME))
−
  from (select RC.RDB$FIELD_NAME
+
    from (select RC.RDB$FIELD_NAME
−
        from RDB$RELATION_FIELDS RC
+
            from RDB$RELATION_FIELDS RC
−
        where RC.RDB$RELATION_NAME = upper(:VW_NAME)
+
            where RC.RDB$RELATION_NAME = upper(:VW_NAME)
−
        order by RC.RDB$FIELD_POSITION)
+
            order by RC.RDB$FIELD_POSITION)
−
  into :VW_FIELDS;
+
    into :VW_FIELDS;
−
  select R.RDB$VIEW_SOURCE
+
    select R.RDB$VIEW_SOURCE
−
  from RDB$RELATIONS R
+
    from RDB$RELATIONS R
−
  where R.RDB$RELATION_NAME = upper(:VW_NAME)
+
    where R.RDB$RELATION_NAME = upper(:VW_NAME)
−
  into :VW_BODY;
+
    into :VW_BODY;
  
−
  I = 0;
+
    I = 0;
−
  J = 0;
+
    J = 0;
−
  if ((select count(*)
+
    if ((select count(*)
−
      from RDB$RELATION_FIELDS RF
+
            from RDB$RELATION_FIELDS RF
−
      where RF.RDB$RELATION_NAME = upper(:VW_NAME) and
+
            where RF.RDB$RELATION_NAME = upper(:VW_NAME) and
 
             RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then
 
             RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then
−
  begin
 
−
    while (position('from', :VW_BODY, :J + 1) <> 0) do
 
 
     begin
 
     begin
−
      J = position('from', :VW_BODY, :J + 1);
+
        while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do
−
      I = :I + 1;
+
−
    end
+
−
 
+
−
    if (:I = 1) then --один основной from, т.е. скрипт без подзапросов
+
−
    begin
+
−
      str = 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
+
−
    end
+
−
    else
+
−
    begin --есть подзапросы - нужно распарсить и найти основной from
+
−
      J = 0;
+
−
      while (position('from', :VW_BODY, :J + 1) <> 0) do    --находим сколько всего from'ов в скрипте
+
−
      begin
+
−
        I = 0;
+
−
        J = position('from', :VW_BODY, :J + 1);
+
−
        L = 0;
+
−
        STR = substring(:VW_BODY from L + 1 for :J - 1);
+
−
        while (position('(', STR, :L + 1) <> 0) do       --считаем количество открывающих скобок
+
 
         begin
 
         begin
−
          L = position('(', STR, :L + 1);
+
            J = position('FROM', UPPER(:VW_BODY), :J + 1);
−
          I = :I + 1;
+
            I = :I + 1;
 
         end
 
         end
  
−
         L = 0;
+
         if (:I = 1) then /* один основной from, т.е. скрипт без подзапросов */
−
        while (position(')', STR, :L + 1) <> 0) do      --считаем количество закрывающих скобок
+
 
         begin
 
         begin
−
          L = position(')', STR, :L + 1);
+
            execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
−
          I = :I - 1;
+
 
         end
 
         end
 +
        else
 +
        begin /* есть подзапросы - нужно распарсить и найти основной from */
 +
            J = 0;
 +
            while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do /* находим сколько всего from'ов в скрипте */
 +
            begin
 +
                I = 0;
 +
                J = position('FROM', UPPER(:VW_BODY), :J + 1);
 +
                L = 0;
 +
                STR = substring(:VW_BODY from L + 1 for :J - 1);
 +
                while (position('(', STR, :L + 1) <> 0) do /* считаем количество открывающих скобок */
 +
                begin
 +
                    L = position('(', STR, :L + 1);
 +
                    I = :I + 1;
 +
                end
  
−
        if (:I = 0) then --если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок
+
                L = 0;
−
              str = 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
+
                while (position(')', STR, :L + 1) <> 0) do /* считаем количество закрывающих скобок */
−
      end
+
                begin
 +
                    L = position(')', STR, :L + 1);
 +
                    I = :I - 1;
 +
                end
 +
 
 +
                if (:I = 0) then /* если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок */
 +
                    execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
 +
            end
 +
        end
 
     end
 
     end
−
  --if (position('where', substring(:VW_BODY from j+1))<>0) then
+
  end
−
    -- str2 = substring(str from 1 for :j)||substring(str from j for position('where', substring(str from :j))-1)||' '||:new_vw_join;--||' '||substring(str from position('where', substring(str from :j)));
+
  if (upper(:NEW_VW_JOIN) = 'TABLE') then
−
    execute statement str; end
+
  begin
 +
    if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = :VW_NAME and rf.RDB$FIELD_NAME = :NEW_VW_FIELD)) then
 +
        execute statement 'ALTER '||:NEW_VW_JOIN||' '||:VW_NAME||' ADD '||:NEW_VW_FIELD||' '||:NEW_VW_VALUE||';';
 +
  end
 +
  if ((upper(:NEW_VW_JOIN) = 'INDEX') or (upper(:NEW_VW_JOIN) = 'DESCENDING INDEX') or (upper(:NEW_VW_JOIN) = 'ASCENDING INDEX'))  then
 +
  begin
 +
    if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME=:NEW_VW_VALUE)) then
 +
        execute statement 'CREATE '||:NEW_VW_JOIN||' '||:NEW_VW_VALUE||' ON '||:VW_NAME||' ('||:NEW_VW_FIELD||');';
 +
  end
 +
  if (upper(:NEW_VW_JOIN) = 'GENERATOR') then
 +
  begin
 +
    if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = :NEW_VW_VALUE)) then
 +
        execute statement 'CREATE GENERATOR '||:NEW_VW_VALUE||';';
 +
  end
 +
  if (upper(:NEW_VW_JOIN) = 'PRIMARY KEY') then
 +
  begin
 +
    if (not exists(SELECT s.rdb$field_name FROM rdb$index_segments AS s LEFT JOIN rdb$relation_constraints AS rc ON (rc.rdb$index_name = s.rdb$index_name) WHERE rc.rdb$constraint_name = 'PK_'||:VW_NAME AND rc.rdb$constraint_type = 'PRIMARY KEY')) then
 +
        execute statement 'ALTER TABLE '||:VW_NAME||' ADD CONSTRAINT PK_'||:VW_NAME||' PRIMARY KEY ('||:NEW_VW_FIELD||');';
 +
  end
 +
  if (upper(:NEW_VW_JOIN) = 'DOMAIN') then
 +
  begin
 +
    if (not exists(select f.rdb$field_name, t.rdb$type_name from     rdb$fields f, rdb$types t where f.rdb$field_name=:VW_NAME and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then
 +
        execute statement 'CREATE DOMAIN '||:VW_NAME||' AS '||:NEW_VW_VALUE||';';
 +
  end
 +
 
 +
  if (upper(:NEW_VW_JOIN) = 'TRIGGER') then
 +
  begin
 +
    if ((select r.rdb$trigger_source from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD) not containing :NEW_VW_VALUE) then
 +
    execute statement '
 +
        CREATE OR ALTER TRIGGER '||:NEW_VW_FIELD||' FOR '||:VW_NAME||'
 +
        ACTIVE '||:NEW_EVENT||' POSITION 0
 +
        '||
 +
        (select substring(r.rdb$trigger_source from 1 for CHAR_LENGTH(r.rdb$trigger_source)-3) from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD)||:NEW_VW_VALUE
 +
        ||'
 +
        end;';
 +
  end
 
end^
 
end^
  
Строка 87: Строка 148:
  
 
GRANT EXECUTE ON PROCEDURE PR_ALTER_VIEW TO SYSDBA;
 
GRANT EXECUTE ON PROCEDURE PR_ALTER_VIEW TO SYSDBA;
 +
 
</PRE>
 
</PRE>

Текущая версия на 15:54, 14 июля 2016

Изменение VIEW

  • изменяет view,таблицы

пока что без добавления join'ов во view - там всё не так однозначно

Пример

 
/* создание генератора */
execute procedure PR_ALTER_VIEW('','','gen1111','generator');
/* создание или добавление в конец триггера */
execute procedure pr_alter_view('SELECTMODES','SELECTMODES_BI','--test','TRIGGER','BEFORE INSERT')
/* создание индекса */
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100500','index');
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100501','DESCENDING INDEX');
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100502','ASCENDING INDEX');
/* первичный ключ */
execute procedure PR_ALTER_VIEW('TEMP_MNN','ID','','PRIMARY KEY');
/* изменение таблицы */
execute procedure PR_ALTER_VIEW('DOC_DETAIL','MGN_ID1','DM_DATETIME','TABLE');
/* изменение вьюхи */
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','VIEW');
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','');
/* создание домена */
execute procedure PR_ALTER_VIEW('DM_BIGINT1','','BIGINT','DOMAIN');

Установка


SET TERM ^ ;

create or alter procedure PR_ALTER_VIEW (
    VW_NAME DM_TEXT,
    NEW_VW_FIELD DM_TEXT,
    NEW_VW_VALUE DM_TEXT,
    NEW_VW_JOIN DM_TEXT = null,
    NEW_EVENT DM_TEXT = null)
as
declare variable I DM_ID;
declare variable VW_BODY DM_TEXT_BIG;
declare variable VW_FIELDS DM_TEXT_BIG;
declare variable J DM_ID;
declare variable STR DM_TEXT_BIG;
declare variable L integer;
begin
  if ((:NEW_VW_JOIN is null) or (:NEW_VW_JOIN = '') or (upper(:NEW_VW_JOIN) = 'VIEW'))  then
  begin
    select list(trim(RDB$FIELD_NAME))
    from (select RC.RDB$FIELD_NAME
            from RDB$RELATION_FIELDS RC
            where RC.RDB$RELATION_NAME = upper(:VW_NAME)
            order by RC.RDB$FIELD_POSITION)
    into :VW_FIELDS;
    select R.RDB$VIEW_SOURCE
    from RDB$RELATIONS R
    where R.RDB$RELATION_NAME = upper(:VW_NAME)
    into :VW_BODY;

    I = 0;
    J = 0;
    if ((select count(*)
            from RDB$RELATION_FIELDS RF
            where RF.RDB$RELATION_NAME = upper(:VW_NAME) and
             RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then
    begin
        while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do
        begin
            J = position('FROM', UPPER(:VW_BODY), :J + 1);
            I = :I + 1;
        end

        if (:I = 1) then /* один основной from, т.е. скрипт без подзапросов */
        begin
            execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
        end
        else
        begin /* есть подзапросы - нужно распарсить и найти основной from */
            J = 0;
            while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do /* находим сколько всего from'ов в скрипте */
            begin
                I = 0;
                J = position('FROM', UPPER(:VW_BODY), :J + 1);
                L = 0;
                STR = substring(:VW_BODY from L + 1 for :J - 1);
                while (position('(', STR, :L + 1) <> 0) do /* считаем количество открывающих скобок */
                begin
                    L = position('(', STR, :L + 1);
                    I = :I + 1;
                end

                L = 0;
                while (position(')', STR, :L + 1) <> 0) do /* считаем количество закрывающих скобок */
                begin
                    L = position(')', STR, :L + 1);
                    I = :I - 1;
                end

                if (:I = 0) then /* если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок */
                    execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
            end
        end
    end
  end
  if (upper(:NEW_VW_JOIN) = 'TABLE') then
  begin
    if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = :VW_NAME and rf.RDB$FIELD_NAME = :NEW_VW_FIELD)) then
        execute statement 'ALTER '||:NEW_VW_JOIN||' '||:VW_NAME||' ADD '||:NEW_VW_FIELD||' '||:NEW_VW_VALUE||';';
  end
  if ((upper(:NEW_VW_JOIN) = 'INDEX') or (upper(:NEW_VW_JOIN) = 'DESCENDING INDEX') or (upper(:NEW_VW_JOIN) = 'ASCENDING INDEX'))  then
  begin
    if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME=:NEW_VW_VALUE)) then
        execute statement 'CREATE '||:NEW_VW_JOIN||' '||:NEW_VW_VALUE||' ON '||:VW_NAME||' ('||:NEW_VW_FIELD||');';
  end
  if (upper(:NEW_VW_JOIN) = 'GENERATOR') then
  begin
    if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = :NEW_VW_VALUE)) then
        execute statement 'CREATE GENERATOR '||:NEW_VW_VALUE||';';
  end
  if (upper(:NEW_VW_JOIN) = 'PRIMARY KEY') then
  begin
    if (not exists(SELECT s.rdb$field_name FROM rdb$index_segments AS s LEFT JOIN rdb$relation_constraints AS rc ON (rc.rdb$index_name = s.rdb$index_name) WHERE rc.rdb$constraint_name = 'PK_'||:VW_NAME AND rc.rdb$constraint_type = 'PRIMARY KEY')) then
        execute statement 'ALTER TABLE '||:VW_NAME||' ADD CONSTRAINT PK_'||:VW_NAME||' PRIMARY KEY ('||:NEW_VW_FIELD||');';
  end
  if (upper(:NEW_VW_JOIN) = 'DOMAIN') then
  begin
    if (not exists(select f.rdb$field_name, t.rdb$type_name from     rdb$fields f, rdb$types t where f.rdb$field_name=:VW_NAME and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then
        execute statement 'CREATE DOMAIN '||:VW_NAME||' AS '||:NEW_VW_VALUE||';';
  end

  if (upper(:NEW_VW_JOIN) = 'TRIGGER') then
  begin
    if ((select r.rdb$trigger_source from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD) not containing :NEW_VW_VALUE) then
    execute statement '
        CREATE OR ALTER TRIGGER '||:NEW_VW_FIELD||' FOR '||:VW_NAME||'
        ACTIVE '||:NEW_EVENT||' POSITION 0
        '||
        (select substring(r.rdb$trigger_source from 1 for CHAR_LENGTH(r.rdb$trigger_source)-3) from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD)||:NEW_VW_VALUE
        ||'
         end;';
  end
end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_ALTER_VIEW TO SYSDBA;
Источник — «http://wiki.standart-n.ru/index.php?title=Изменения_представления&oldid=4315»