Изменения представления
Материал из wiki.standart-n.ru
Версия от 13:13, 13 июля 2016; BeTePoK (обсуждение | вклад)
Изменение 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;
скрипт для G$TASK
SET TERM ^ ;'||ascii_char(13)||ascii_char(10)||' create or alter procedure PR_ALTER_VIEW ('||ascii_char(13)||ascii_char(10)||' VW_NAME DM_TEXT,'||ascii_char(13)||ascii_char(10)||' NEW_VW_FIELD DM_TEXT,'||ascii_char(13)||ascii_char(10)||' NEW_VW_VALUE DM_TEXT,'||ascii_char(13)||ascii_char(10)||' NEW_VW_JOIN DM_TEXT = null,'||ascii_char(13)||ascii_char(10)||' NEW_EVENT DM_TEXT = null)'||ascii_char(13)||ascii_char(10)||' as'||ascii_char(13)||ascii_char(10)||' declare variable I DM_ID;'||ascii_char(13)||ascii_char(10)||' declare variable VW_BODY DM_TEXT_BIG;'||ascii_char(13)||ascii_char(10)||' declare variable VW_FIELDS DM_TEXT_BIG;'||ascii_char(13)||ascii_char(10)||' declare variable J DM_ID;'||ascii_char(13)||ascii_char(10)||' declare variable STR DM_TEXT_BIG;'||ascii_char(13)||ascii_char(10)||' declare variable L integer;'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' if ((:NEW_VW_JOIN is null) or (:NEW_VW_JOIN = '''') or (upper(:NEW_VW_JOIN) = ''VIEW'')) then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' select list(trim(RDB$FIELD_NAME))'||ascii_char(13)||ascii_char(10)||' from (select RC.RDB$FIELD_NAME'||ascii_char(13)||ascii_char(10)||' from RDB$RELATION_FIELDS RC'||ascii_char(13)||ascii_char(10)||' where RC.RDB$RELATION_NAME = upper(:VW_NAME)'||ascii_char(13)||ascii_char(10)||' order by RC.RDB$FIELD_POSITION)'||ascii_char(13)||ascii_char(10)||' into :VW_FIELDS;'||ascii_char(13)||ascii_char(10)||' select R.RDB$VIEW_SOURCE'||ascii_char(13)||ascii_char(10)||' from RDB$RELATIONS R'||ascii_char(13)||ascii_char(10)||' where R.RDB$RELATION_NAME = upper(:VW_NAME)'||ascii_char(13)||ascii_char(10)||' into :VW_BODY;'||ascii_char(13)||ascii_char(10)||' I = 0;'||ascii_char(13)||ascii_char(10)||' J = 0;'||ascii_char(13)||ascii_char(10)||' if ((select count(*)'||ascii_char(13)||ascii_char(10)||' from RDB$RELATION_FIELDS RF'||ascii_char(13)||ascii_char(10)||' where RF.RDB$RELATION_NAME = upper(:VW_NAME) and'||ascii_char(13)||ascii_char(10)||' RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' while (position(''FROM'', UPPER(:VW_BODY), :J + 1) <> 0) do'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' J = position(''FROM'', UPPER(:VW_BODY), :J + 1);'||ascii_char(13)||ascii_char(10)||' I = :I + 1;'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (:I = 1) then --один основной from, т.е. скрипт без подзапросов'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' 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));'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' else'||ascii_char(13)||ascii_char(10)||' begin --есть подзапросы - нужно распарсить и найти основной from'||ascii_char(13)||ascii_char(10)||' J = 0'||ascii_char(13)||ascii_char(10)||'; while (position(''FROM'', UPPER(:VW_BODY), :J + 1) <> 0) do --находим сколько всего fromов в скрипте'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' I = 0;'||ascii_char(13)||ascii_char(10)||' J = position(''FROM'', UPPER(:VW_BODY), :J + 1);'||ascii_char(13)||ascii_char(10)||' L = 0;'||ascii_char(13)||ascii_char(10)||' STR = substring(:VW_BODY from L + 1 for :J - 1);'||ascii_char(13)||ascii_char(10)||' while (position(''('', STR, :L + 1) <> 0) do --считаем количество открывающих скобок'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' L = position(''('', STR, :L + 1);'||ascii_char(13)||ascii_char(10)||' I = :I + 1;'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' L = 0;'||ascii_char(13)||ascii_char(10)||' while (position('')'', STR, :L + 1) <> 0) do --считаем количество закрывающих скобок'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' L = position('')'', STR, :L + 1);'||ascii_char(13)||ascii_char(10)||' I = :I - 1;'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (:I = 0) then --если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок'||ascii_char(13)||ascii_char(10)||' 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));'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (upper(:NEW_VW_JOIN) = ''TABLE'') then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' 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'||ascii_char(13)||ascii_char(10)||' execute statement ''ALTER ''||:NEW_VW_JOIN||'' ''||:VW_NAME||'' ADD ''||:NEW_VW_FIELD||'' ''||:NEW_VW_VALUE;'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if ((upper(:NEW_VW_JOIN) = ''INDEX'') or (upper(:NEW_VW_JOIN) = ''DESCENDING INDEX'') or (upper(:NEW_VW_JOIN) = ''ASCENDING INDEX'')) then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME=:NEW_VW_VALUE)) then'||ascii_char(13)||ascii_char(10)||' execute statement ''CREATE ''||:NEW_VW_JOIN||'' ''||:NEW_VW_VALUE||'' ON ''||:VW_NAME||'' (''||:NEW_VW_FIELD||'');'';'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (upper(:NEW_VW_JOIN) = ''GENERATOR'') then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = :NEW_VW_VALUE)) then'||ascii_char(13)||ascii_char(10)||' execute statement ''CREATE GENERATOR ''||:NEW_VW_VALUE;'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (upper(:NEW_VW_JOIN) = ''PRIMARY KEY'') then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' 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'||ascii_char(13)||ascii_char(10)||' execute statement ''ALTER TABLE ''||:VW_NAME||'' ADD CONSTRAINT PK_''||:VW_NAME||'' PRIMARY KEY (''||:NEW_VW_FIELD||'');'';'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (upper(:NEW_VW_JOIN) = ''DOMAIN'') then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' 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'||ascii_char(13)||ascii_char(10)||' execute statement ''CREATE DOMAIN ''||:VW_NAME||'' AS ''||:NEW_VW_VALUE;'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' if (upper(:NEW_VW_JOIN) = ''TRIGGER'') then'||ascii_char(13)||ascii_char(10)||' begin'||ascii_char(13)||ascii_char(10)||' if ((select r.rdb$trigger_source from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD) not containing :NEW_VW_VALUE) then'||ascii_char(13)||ascii_char(10)||' execute statement '''||ascii_char(13)||ascii_char(10)||' CREATE OR ALTER TRIGGER ''||:NEW_VW_FIELD||'' FOR ''||:VW_NAME||'''||ascii_char(13)||ascii_char(10)||' ACTIVE ''||:NEW_EVENT||'' POSITION 0 ''||'||ascii_char(13)||ascii_char(10)||' (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||'''||ascii_char(13)||ascii_char(10)||' end'';'||ascii_char(13)||ascii_char(10)||' end'||ascii_char(13)||ascii_char(10)||' end;'||ascii_char(13)||ascii_char(10)||' ^ '||ascii_char(13)||ascii_char(10)||' SET TERM ; ^