Подготовка серверной БД для перевода на Единое Окно
Материал из wiki.standart-n.ru
Версия от 14:43, 14 января 2017; Agk (обсуждение | вклад)
Обновляем структуру БД
/*******************************************************************/
/*НОВЫЕ ПОЛЯ*/
ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
ALTER TABLE WARES ADD WHASH DM_ID;
ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SIZG DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SCOUNTRY DM_TEXT;
ALTER TABLE PARTS ADD ORIG_BCODE_IZG DM_TEXT;
/*******************************************************************/
/*ТРИГГЕРЫ*/
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable cur_id type of DM_UUID;
begin
if (new.barcode is null) then
new.barcode='';
if (new.orig_code is null) then
new.orig_code='';
select d$uuid from wares where
NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and orig_code=new.orig_code and d$uuid<>new.d$uuid into :cur_id;
if (cur_id is null) then exit;
new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_BI_CHECK FOR WARES
ACTIVE BEFORE INSERT POSITION 0
AS
declare variable ID type of DM_UUID;
begin
if (exists (select id from wares where NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and
ORIG_NAME_ID=new.ORIG_NAME_ID and ORIG_IZG_ID=new.ORIG_IZG_ID and ORIG_COUNTRY_ID=new.ORIG_COUNTRY_ID)) then
new.orig_code='>WARES_BI_CHECK;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_BI_SNAME FOR WARES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (new.d$srvupddt is null) then
exception ex_wrong_db;
if (new.d$uuid is null) then
exception ex_wrong_db;
new.sname = (select svalue from vals where id = new.name_id);
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable cur_id type of DM_UUID;
begin
if (new.barcode is null) then
new.barcode='';
if (new.orig_code is null) then
new.orig_code='';
select d$uuid from wares where
NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and orig_code=new.orig_code and d$uuid<>new.d$uuid into :cur_id;
if (cur_id is null) then exit;
new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_BU_LOG FOR WARES
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (new.d$srvupddt=old.d$srvupddt) then
begin
insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,
PACKET,L_ID)
values (old.ID,old.NAME_ID,old.IZG_ID,old.COUNTRY_ID,old.ORIG_CODE,old.ORIG_NAME_ID,old.ORIG_IZG_ID,old.ORIG_COUNTRY_ID,old.BARCODE,old.Z_ID,old.SKLAD_ID,old.INSERTDT,
old.PACKET,old.L_ID);
end
end
^
SET TERM ; ^
ALTER TRIGGER WARES_BD0 INACTIVE;
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_AD_DISTR FOR WARES
ACTIVE AFTER DELETE POSITION 0
AS
begin
--20161209 А.К. иначе синхронизация ругается на то, что не найдена запись в таблице wares
delete from g$distribute where tablename='WARES' and uuid=old.d$uuid;
/*
if (not exists(select id from g$distribute where tablename='WARES' and uuid=old.d$uuid and soper=2)) then
update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WARES',old.d$uuid,2,null)
matching (TABLENAME,UUID);
*/
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER VALS_BI0 FOR VALS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (new.d$srvupddt is null) then
exception ex_wrong_db;
if (new.d$uuid is null) then
exception ex_wrong_db;
if (new.svalue starting with '~') then
update g$distribute set serverpacket=-1 where uuid=new.d$uuid;
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER VALS_BI_COWA_SRV FOR VALS
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable id type of dm_uuid;
begin
select id from vals where VTYPE=new.vtype and SVALUE=new.svalue and ALTTYPE=new.alttype and id<>new.id into :id;
if (id is null) then exit;
new.uppervalue=new.svalue;
new.svalue=trim(substring(new.svalue from 1 for 245))||' >'||coalesce((select from_profile_id from g$distribute where uuid=new.d$uuid),0);
end
^
SET TERM ; ^
ALTER TRIGGER VALS_BI_TILDA INACTIVE;
ALTER TRIGGER VALS_BU_TILDA INACTIVE;
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER VALS_BI_COWA_SRV FOR VALS
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable id type of dm_uuid;
begin
select id from vals where VTYPE=new.vtype and SVALUE=new.svalue and ALTTYPE=new.alttype and id<>new.id into :id;
if (id is null) then exit;
new.uppervalue=new.svalue;
new.svalue=trim(substring(new.svalue from 1 for 245))||' >'||coalesce((select from_profile_id from g$distribute where uuid=new.d$uuid),0);
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER VALS_BD0 FOR VALS
ACTIVE BEFORE DELETE POSITION 0
AS
begin
exception ex_wrong_db;
end
^
SET TERM ; ^
Подготавливаем товары к сжатию
CREATE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE,ORIG_CODE); ALTER TRIGGER WARES_BIU_COWA INACTIVE; ALTER TRIGGER WARES_BU_LOG INACTIVE; update wares set orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID()); ALTER TRIGGER WARES_BIU_COWA ACTIVE; ALTER TRIGGER WARES_BU_LOG ACTIVE;
Создаем индексы
CREATE INDEX WARES_LOG_IDX_AWID ON WARES_LOG (ACTUAL_WARE_ID); DROP INDEX WARES_IDX_U; CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE,ORIG_CODE); CREATE DESCENDING INDEX WARES_IDX_ID_DESC ON WARES (ID); CREATE INDEX WARES_IDX_NID ON WARES (NAME_ID); CREATE INDEX WARES_IDX_IID ON WARES (IZG_ID); CREATE INDEX WARES_IDX_CID ON WARES (COUNTRY_ID); CREATE UNIQUE INDEX WARES_IDX100 ON WARES (WHASH);
Окончание
Делаем бекап-ресторе;
