Подготовка серверной базы и скриптов для Единого Окна
Материал из wiki.standart-n.ru
Версия от 15:01, 6 сентября 2016; Aleksnick (обсуждение | вклад)
- Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert
- Создаем и вставляем данные 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
- Выпоняем 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;
- Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)
- Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log.
- Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем.
- Экспрорт как 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" - убираем.
- Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта.
