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

Версия 15:01, 6 сентября 2016

1. Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert
2. Создаем и вставляем данные vals_link в нашу БД (скрипт создания и импорта из dbf: cowa_vals_links.sql)

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;

/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_VALS_LINKS_ID;

CREATE TABLE VALS_LINKS (
    ID            DM_ID NOT NULL,
    RIGHT_VAL_ID  DM_UUID,
    WRONG_VAL_ID  DM_UUID,
    INSERTDT      DM_DATETIME,
    VTYPE         DM_STATUS NOT NULL,
    SRIGHT_VAL    DM_TEXT,
    SWRONG_VAL    DM_TEXT,
    DELETEDT      DM_DATETIME
);




/******************************************************************************/
/***                           Unique Constraints                           ***/
/******************************************************************************/

ALTER TABLE VALS_LINKS ADD CONSTRAINT UNQ1_VALS_LINKS UNIQUE (RIGHT_VAL_ID, WRONG_VAL_ID);


/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE VALS_LINKS ADD CONSTRAINT PK_VALS_LINKS PRIMARY KEY (ID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX VALS_LINKS_IDX1 ON VALS_LINKS (RIGHT_VAL_ID);
CREATE INDEX VALS_LINKS_IDX2 ON VALS_LINKS (WRONG_VAL_ID);
CREATE INDEX VALS_LINKS_IDX3 ON VALS_LINKS (SRIGHT_VAL, SWRONG_VAL);
CREATE INDEX VALS_LINKS_IDX4 ON VALS_LINKS (SRIGHT_VAL);
CREATE INDEX VALS_LINKS_IDX5 ON VALS_LINKS (SWRONG_VAL);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: VALS_LINKS_BI */
CREATE OR ALTER TRIGGER VALS_LINKS_BI FOR VALS_LINKS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_vals_links_id,1);
  new.insertdt=current_timestamp;
end
^


SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/



execute ibeblock
as
begin
  cbb = 'execute ibeblock (RecCount variant)
         as
         begin
           if (ibec_Mod(RecCount, 100) = 0) then
             ibec_Progress(RecCount || '' records imported.'');
         end;';
  
  DB = ibec_CreateConnection(__ctFirebird, 'DBName="standart-n:D:\STANDART-N\base_g\test\test_ztrade_g.fdb";
                                            ClientLib=gds32.dll;
                                            User=SYSDBA; Password=masterkey; Names=WIN1251; SqlDialect=3;');
  try
    ibec_UseConnection(DB);
    Res = ibec_ImportData(DB, 'VALS_LINKS', __impDBase, 'D:\STANDART-N\base_g\test\SQLs\vals_links.dbf', '', 
                          'RowFirst=1; RowLast=2147483647;
                           DateOrder=DMY; DateSeparator="."; TimeSeparator=":";', '', cbb);
  
    if (Res is not null) then
      ibec_ShowMessage(Res || ' records were imported successfully.');
  finally
    ibec_CloseConnection(DB);
  end;

end

1. Выпоняем cowa_all.sql (не оптимизирован для ЙО)

--копируем VALS_LINKS как объект
ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_AD_DISTR INACTIVE;
ALTER TRIGGER WARES_AD_DISTR INACTIVE;

ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_AIU0 INACTIVE;
ALTER TRIGGER VALS_AIU0 INACTIVE;

ALTER TRIGGER PARTS_AU0 INACTIVE;

ALTER TRIGGER VALS_AD_DISTR INACTIVE;
--ALTER TRIGGER VALS_AD_DODOUBLES INACTIVE;
--ALTER TRIGGER WARES_BIU_VALCHECK INACTIVE;

--ALTER TRIGGER WARES_AD_DISTR INACTIVE;
--ALTER TRIGGER WARES_AD0 INACTIVE;
ALTER TRIGGER WARES_BD0 INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
--ALTER TRIGGER WARES_BU0_MGN_NAME_ASNA INACTIVE;

--DROP INDEX WARES_IDX15;
--CREATE INDEX WARES_IDX15 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID);

ALTER TABLE WARES_LOG
ADD ACTUAL_WARE_ID DM_UUID_NULL;
CREATE INDEX WARES_LOG_ACTUAL_WARE_ID ON WARES_LOG (ACTUAL_WARE_ID);

SET TERM ^ ;

CREATE OR ALTER trigger wares_log_bi_tmp for wares_log
active before insert position 0
AS
begin
  if (new.log_id is null) then
    new.log_id=UUID_TO_CHAR(GEN_UUID());
  new.d$uuid=new.log_id;
  if (new.d$srvupddt is null) then
  begin
    new.d$srvupddt='2000-01-01';
  end
  if (new.l_id is null) then
    new.l_id=GEN_ID(GEN_WARES_LOG_ID,1);
  new.log_insertdt=current_timestamp;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
begin
  delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id));
  for
    select min(d$uuid), name_id, izg_id, country_id, barcode from wares group by name_id, izg_id, country_id, barcode having count(1)>1
       into actual_uuid, name_id, izg_id, country_id, barcode
  do
  begin
    for select d$uuid from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode and d$uuid<>:actual_uuid into uuid
    do begin
--      insert into tmp_todelete (TABLENAME,UUID,REASON,ACTUAL_UUID) values ('WARES',:uuid,'удаление',:actual_uuid);
      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,ACTUAL_WARE_ID)
          select 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,:actual_uuid
        from wares where d$uuid=:uuid;
      update parts set ware_id=:actual_uuid where ware_id=:uuid;
      delete from wares where d$uuid=:uuid;
    end
  end
--delete from wares w where not exists (select id from parts p where p.ware_id=w.id) = всего 11414 , после сжатия - игнорим пока
delete from vals v where (vtype=0) and (not exists (select id from wares w where w.name_id=v.id)) and (not exists (select id from wares_log wl where wl.name_id=v.id));
delete from vals v where (vtype=3) and (not exists (select id from wares w where w.izg_id=v.id)) and (not exists (select id from wares_log wl where wl.izg_id=v.id));
delete from vals v where (vtype=2) and (not exists (select id from wares w where w.country_id=v.id)) and (not exists (select id from wares_log wl where wl.country_id=v.id));

delete from vals v where (vtype=1) and (not exists (select id from wares w where w.orig_name_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_name_id=v.id));
delete from vals v where (vtype=6) and (not exists (select id from wares w where w.orig_izg_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_izg_id=v.id));
delete from vals v where (vtype=5) and (not exists (select id from wares w where w.orig_country_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_country_id=v.id));
end^

SET TERM ; ^

update wares set barcode='' where barcode is null;

execute procedure PR_TMP_CHANGEWARES_CONSTR;
commit work;

CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID, IZG_ID, COUNTRY_ID, BARCODE);

--DROP TRIGGER WARES_LOG_BI_TMP;

CREATE TABLE TMP_UPDATED_WARES (
    UUID  DM_UUID /* DM_UUID = CHAR(36) NOT NULL */
);
CREATE INDEX TMP_UPDATED_WARES_IDX1 ON TMP_UPDATED_WARES (UUID);


/*
CREATE GENERATOR GEN_VALS_CARANTINE_ID;
CREATE TABLE VALS_CARANTINE (
    ID               DM_ID NOT NULL,
    CUR_ID           DM_UUID,
    NEW_ID           DM_UUID,
    INSERTDT         DM_DATETIME,
    FROM_PROFILE_ID  DM_ID_NULL
);
SET TERM ^ ;

CREATE OR ALTER TRIGGER VALS_CARANTINE_BI FOR VALS_CARANTINE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_vals_carantine_id,1);
  new.insertdt=current_timestamp;
  if (new.from_profile_id is null) then
    select from_profile_id from g$distribute where uuid=new.new_id into new.from_profile_id;
end
^

SET TERM ; ^
*/
SET TERM ^ ;

CREATE OR ALTER trigger vals_biu_dodoubles for vals
inactive 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 d$uuid<>new.d$uuid into :id;
  if (id is null) then exit;
/*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
  if (new.vtype=0) then
    update wares set name_id=new.id where name_id=:id;
  else if (new.vtype=3) then
    update wares set izg_id=new.id where izg_id=:id;
  else if (new.vtype=2) then
    update wares set country_id=new.id where country_id=:id;
  else if (new.vtype=1) then
    update wares set orig_name_id=new.id where orig_name_id=:id;
  else if (new.vtype=6) then
    update wares set orig_izg_id=new.id where orig_izg_id=:id;
  else if (new.vtype=5) then
    update wares set orig_country_id=new.id where orig_country_id=:id;
  else exit;
  insert into vals_carantine (cur_id, new_id) values (:id, new.id);
  delete from vals where id=:id;
end^

SET TERM ; ^

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
  cur_id=null;
  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 d$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  if (not exists(select id from wares_log where id=:cur_id and actual_ware_id=new.id)) then
  begin
    update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id;
    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,ACTUAL_WARE_ID)
      select 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,new.d$uuid from wares where d$uuid=:cur_id;
--      values (new.ID,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,new.BARCODE,new.Z_ID,new.SKLAD_ID,new.INSERTDT,new.PACKET,new.L_ID,new.d$uuid);
  end
  update parts set ware_id=new.d$uuid where ware_id=:cur_id;
  delete from wares where d$uuid=:cur_id;
end^

SET TERM ; ^
SET TERM ^ ;

CREATE OR ALTER trigger parts_biu_cowa for parts
active before insert or update position 0
AS
declare variable ware_id type of DM_UUID;
begin
  if (exists(select id from wares where id=new.ware_id)) then exit;
  select max(actual_ware_id) from wares_log where id=new.ware_id and (exists(select id from wares w where w.d$uuid=actual_ware_id)) into :ware_id;
  if (ware_id is null) then exit;
--  if (exists(select id from wares where d$uuid=:ware_id)) then
    new.ware_id=:ware_id;
end^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure UTPR_REMOVE_TILDA (
    SVALUE type of DM_TEXT)
returns (
    S type of DM_TEXT)
as
declare variable TILDA type of DM_TEXT;
declare variable C char(10);
begin
  s=:svalue;
  if (svalue not starting with '~') then
  begin
    suspend;
    exit;
  end
  tilda='';
  if (svalue starting with '~100') then
  begin
    s=substring(svalue from 5 for 245);
    tilda='100';
  end
  else
  begin
    c=substring(svalue from 3 for 1);
    if (c in ('0','1','2','3','4','5','6','7','8','9')) then
    begin
      s=substring(svalue from 4 for 245);
      tilda=substring(svalue from 2 for 2);
    end
    else
    begin
      s=substring(svalue from 3 for 245);
      tilda=substring(svalue from 2 for 1);
    end
    c=substring(s from 1 for 1);
    if (c='@') then
    begin
      s=substring(s from 2);
    end
  end
  suspend;
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure UTPR_BUBBLE_SORT (
    SOURCE DM_TEXT)
returns (
    S DM_TEXT)
as
declare variable C1 char(1);
declare variable L integer;
declare variable I integer;
declare variable SWAPPED integer;
declare variable J integer;
declare variable C2 char(1);
declare variable B char(1);
begin
  l=char_length(source);
  i=0;
  s=source;
  while (i<=l) do
  begin
    i=i+1; j=0;
    swapped=0;
    while (j<=l-i-1) do
    begin
      j=j+1;
      c1=substring(s from j for 1);
      c2=substring(s from j+1 for 1);
      if (c1>c2) then
      begin
        b=c1;
        s=substring(s from 1 for j - 1) || c2 || c1 || substring(s from j + 2);
        swapped=1;
      end 
    end 
    if (swapped=0) then break;
  end 
  suspend;
end^

SET TERM ; ^



SET TERM ^ ;

CREATE OR ALTER procedure UTPR_CHARSONLY (
    SOURCE type of DM_TEXT,
    SORTED integer = 0)
returns (
    S DM_TEXT)
as
declare variable C char(1);
declare variable L integer;
declare variable I integer;
declare variable V integer;
begin
  l=char_length(source);
  i=1;
  s='';
  while (i<=l) do
  begin
    c=substring(source from i for 1);
    v=ascii_val(c);
    if (((v>47) and (v<58)) or
        ((v>64) and (v<91)) or
        ((v>96) and (v<123)) or
        (v>191))   then
      s=s||c;
    i=i+1;
  end 
  if (sorted=1) then
    select s from UTPR_BUBBLE_SORT(:s) into :s;
  suspend;
end^

SET TERM ; ^





SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_COMPRESS_VALS
as
declare variable UVALUE type of DM_TEXT;
declare variable VTYPE type of DM_STATUS;
declare variable ID type of DM_UUID;
declare variable W_CNT type of DM_STATUS;
declare variable T_CNT type of DM_STATUS;
declare variable T_ID type of DM_UUID;
begin
--  update vals set uppervalue=upper(trim((select s from UTPR_REMOVE_TILDA(svalue)))), isactual=0;
  update vals set uppervalue=(select s from UTPR_CHARSONLY(upper(trim((select s from UTPR_REMOVE_TILDA(svalue)))),1)), isactual=0;
--  for select uppervalue, vtype from vals where vtype in (0,3,2) and svalue<>'' group by vtype, uppervalue having count(1)>1 into uvalue, vtype do
  for select uppervalue, vtype from vals where vtype in (0,3,2) group by vtype, uppervalue having count(1)>1 into uvalue, vtype do
  begin
    T_CNT=0; t_id=null;
    for select id from vals where vtype=:vtype and uppervalue=:uvalue into id do
    begin
      if (vtype=0) then
        select count(1) from wares where name_id=:id into :w_cnt;
      else if (vtype=3) then
        select count(1) from wares where izg_id=:id into :w_cnt;
      else if (vtype=2) then
        select count(1) from wares where country_id=:id into :w_cnt;
     if (t_cnt<w_cnt) then
     begin
       t_cnt=w_cnt;
       t_id=:id;
     end 
     update vals set isactual=:w_cnt where id=:id;
    end
    if (t_id is not null) then
    begin
        for select id from vals where vtype=:vtype and uppervalue=:uvalue and id<>:t_id into id do
        begin
          if (vtype=0) then
          begin
            insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where name_id=:id;
            update wares set name_id=:t_id where name_id=:id;
          end
          else if (vtype=3) then
            begin
              insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where izg_id=:id;
              update wares set izg_id=:t_id where izg_id=:id;
            end
          else if (vtype=2) then
            begin
              insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where country_id=:id;
              update wares set country_id=:t_id where country_id=:id;
            end
        end
    end
  end 
end^

SET TERM ; ^


CREATE INDEX VALS_IDX_COWA_TMP ON VALS (VTYPE, UPPERVALUE);

execute procedure PR_TMP_COMPRESS_VALS;
commit work;

SET TERM ^ ;

CREATE OR ALTER procedure PR_TMP_APPLY_VALS_LINKS
as
declare variable RIGHT_ID type of DM_UUID;
declare variable WRONG_ID type of DM_UUID;
begin
  for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=0 into right_id, wrong_id do
  begin
    insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where name_id=:wrong_id;
    update wares set name_id=:right_id where name_id=:wrong_id;
  end
  for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=3 into right_id, wrong_id do
  begin
    insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where izg_id=:wrong_id;
    update wares set izg_id=:right_id where izg_id=:wrong_id;
  end
  for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=2 into right_id, wrong_id do
  begin
    insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where country_id=:wrong_id;
    update wares set country_id=:right_id where country_id=:wrong_id;
  end
end^

SET TERM ; ^

execute procedure PR_TMP_APPLY_VALS_LINKS;
commit work;

DROP TRIGGER WARES_LOG_BI_TMP;


ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_AIU0 INACTIVE;
ALTER TRIGGER VALS_AIU0 INACTIVE;

ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE;
ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_LOG_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;

ALTER TRIGGER PARTS_AU0 ACTIVE;

1. Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)
2. Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log.
3. Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем.
4. Экспрорт как update or insert скрипт в файл запроса "select distinct w.* from TMP_UPDATED_WARES t left join wares w on t.uuid=w.d$uuid where w.d$uuid is not null", все строки "commit work" - убираем.
5. Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта.