Подготовка серверной БД для перевода на Единое Окно — различия между версиями

Версия 14:43, 14 января 2017

Обновляем структуру БД

/*******************************************************************/
/*НОВЫЕ ПОЛЯ*/

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);

Окончание

Делаем бекап-ресторе;