Подготовка серверной БД для перевода на Единое Окно
Материал из wiki.standart-n.ru
Версия от 16: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 DOMAIN DM_2K AS
VARCHAR(2048) CHARACTER SET WIN1251
COLLATE WIN1251;
SET TERM ^ ;
create or alter procedure UTPR_VALUE4SQL (
source type of DM_TEXT)
returns (
S varchar(255))
as
begin
if (source is null) then
s='NULL';
else
s=''''||replace(trim(source),'''','''''')||'''';
suspend;
end^
SET TERM ; ^
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO PROCEDURE AA_COWA_COMPRESS_WARES_SINGLE;
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO SYSDBA;
SET TERM ^ ;
create or alter procedure AA_COWA_COMPRESS_WARE (
ID type of DM_UUID,
ACTUAL_WARE_ID type of DM_UUID,
DOUPDATE integer)
returns (
S type of DM_BLOBTEXT)
as
begin
if (DOUPDATE is null) then DOUPDATE=0;
s=null;
select
'update or insert into wares (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,D$UUID,D$SRVUPDDT,SNAME,MGN_NAME,MGN_ID,MGN_SOURCE,WHASH) values '||
'('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','''||INSERTDT||''','||PACKET||','''||L_ID||''','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(SNAME))||','||(select s from UTPR_VALUE4SQL(MGN_NAME))||','||(select s from UTPR_VALUE4SQL(MGN_ID))||','||(select s from UTPR_VALUE4SQL(MGN_SOURCE))||','||(select s from UTPR_VALUE4SQL(WHASH))||');'
from wares where id=:actual_ware_id into s;
suspend;
if (DOUPDATE<>0) then update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where id=:actual_ware_id and actual_ware_id is not null;
s='update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where actual_ware_id is not null and id='''||:actual_ware_id||''';';
suspend;
if (DOUPDATE<>0) then update wares_log set actual_ware_id=:actual_ware_id, d$srvupddt=current_timestamp where actual_ware_id=:id;
s='update wares_log set actual_ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where actual_ware_id='''||:id||''';';
suspend;
s=null;
if (not exists(select id from wares_log where id=:id and actual_ware_id=:actual_ware_id)) then
begin -- логично, что нет записи об удалении вареса - создаем ее, без g$distribute - будем отправлять по g$tasks, централизованно всем сразу, одним пакетом
if (DOUPDATE<>0) then
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, D$SRVUPDDT, LOG_INSERTDT)
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_ware_id, current_timestamp, current_timestamp from wares where id=:id;
else s='нет записи об удалении вареса (WARE_ID='||id||', actual_ware_id='||actual_ware_id||')';
end
if (not exists(select id from wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id)) then
exception ex_wrong_db 'эээ';
select first 1
'update or 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,LOG_ID,LOG_INSERTDT,L_ID,D$UUID,D$SRVUPDDT,ACTUAL_WARE_ID) '||
'values ('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','||(select s from UTPR_VALUE4SQL(INSERTDT))||','||PACKET||','''||LOG_ID||''','||(select s from UTPR_VALUE4SQL(LOG_INSERTDT))||','||L_ID||','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(ACTUAL_WARE_ID))||');'
from wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id order by log_insertdt into s;
suspend;
if (DOUPDATE<>0) then update parts set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update parts set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
-- if (DOUPDATE<>0) then update doc_detail_active set ware_id=:actual_ware_id where ware_id=:id;
s='update doc_detail_active set ware_id='''||:actual_ware_id||''' where ware_id='''||:id||''';';
suspend;
-- if (DOUPDATE<>0) then update warebase_distr set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
-- s='update warebase_distr set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
-- suspend;
if (DOUPDATE<>0) then update doc_detail_active_treb set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update doc_detail_active_treb set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
if (DOUPDATE<>0) then update DOC_DETAIL_DELETED set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update DOC_DETAIL_DELETED set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
if (DOUPDATE<>0) then update DOC_DETAIL_VIRTUAL set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update DOC_DETAIL_VIRTUAL set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
if (DOUPDATE<>0) then delete from wares where id=:id; --триггер при удалении из wares должен быть неактивен
s='delete from wares where id='''||:id||''';';
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,DELETE ON WARES TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,INSERT,UPDATE ON WARES_LOG TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON PARTS TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_TREB TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_DELETED TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_VIRTUAL TO PROCEDURE AA_COWA_COMPRESS_WARE;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO SYSDBA;
SET TERM ^ ;
create or alter procedure UTPR_VALUE4SQL (
SOURCE type of DM_TEXT)
returns (
S varchar(255))
as
begin
if (source is null) then
s='NULL';
else
s=''''||replace(trim(source),'''','''''')||'''';
suspend;
end^
SET TERM ; ^
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO SYSDBA;
/******************************************************************************/
/*** Generated by IBExpert 14.01.2017 15:40:24 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_AA_TMP_ID;
CREATE TABLE AA_TMP (
ID DM_ID NOT NULL /* DM_ID = BIGINT NOT NULL */,
S DM_2K /* DM_2K = VARCHAR(2048) */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE AA_TMP ADD CONSTRAINT PK_AA_TMP PRIMARY KEY (ID);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: AA_TMP_BI */
CREATE OR ALTER TRIGGER AA_TMP_BI FOR AA_TMP
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_aa_tmp_id,1);
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
SET TERM ^ ;
create or alter procedure AA_COWA_COMPRESS_WARES_SINGLE (
IN_TMP_TABLE integer)
returns (
S type of DM_BLOBTEXT)
as
declare variable ID type of DM_UUID;
declare variable ACTUAL_WARE_ID 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 WLIST type of DM_BLOBTEXT;
declare variable MIN_ACTUAL_WARE_ID type of DM_UUID;
declare variable TS type of DM_BLOBTEXT;
declare variable I integer;
declare variable SHIFT integer;
declare variable TASK_ID type of DM_ID;
begin
if (IN_TMP_TABLE is null) then IN_TMP_TABLE=0;
s='SET TERM ^ ;
CREATE OR ALTER procedure AA_COWA_COMPRESS_WARE_PROFILE (
ID type of DM_UUID,
ACTUAL_WARE_ID type of DM_UUID)
as
begin
update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where actual_ware_id is not null and id=:actual_ware_id;
update wares_log set actual_ware_id=:actual_ware_id, d$srvupddt=current_timestamp where actual_ware_id=:id;
update parts set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
update doc_detail_active set ware_id=:actual_ware_id where ware_id=:id;
-- update warebase_distr set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
update doc_detail_active_treb set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
update DOC_DETAIL_DELETED set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
update DOC_DETAIL_VIRTUAL set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
delete from wares where id=:id;
end^
SET TERM ; ^
';
if (IN_TMP_TABLE=0) then suspend; else insert into aa_tmp(s)values(:s);
for select distinct actual_ware_id, id from wares_log where actual_ware_id is not null into actual_ware_id, id do
begin
select
'update or insert into wares (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,D$UUID,D$SRVUPDDT,SNAME,MGN_NAME,MGN_ID,WHASH) values '||
'('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','''||INSERTDT||''','||PACKET||','''||L_ID||''','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(SNAME))||','||(select s from UTPR_VALUE4SQL(MGN_NAME))||','||(select s from UTPR_VALUE4SQL(MGN_ID))||','||(select s from UTPR_VALUE4SQL(WHASH))||');'
from wares where id=:actual_ware_id into s;
if (IN_TMP_TABLE=0) then suspend; else insert into aa_tmp(s)values(:s);
select first 1
'update or 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,LOG_ID,LOG_INSERTDT,L_ID,D$UUID,D$SRVUPDDT,ACTUAL_WARE_ID) '||
'values ('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','||(select s from UTPR_VALUE4SQL(INSERTDT))||','||PACKET||','''||LOG_ID||''','||(select s from UTPR_VALUE4SQL(LOG_INSERTDT))||','||L_ID||','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(ACTUAL_WARE_ID))||');'
from wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id order by log_insertdt into s;
if (IN_TMP_TABLE=0) then suspend; else insert into aa_tmp(s)values(:s);
s='execute procedure AA_COWA_COMPRESS_WARE_PROFILE('''||id||''','''||actual_ware_id||''');';
if (IN_TMP_TABLE=0) then suspend; else insert into aa_tmp(s)values(:s);
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT INSERT ON AA_TMP TO PROCEDURE AA_COWA_COMPRESS_WARES_SINGLE;
GRANT SELECT ON WARES_LOG TO PROCEDURE AA_COWA_COMPRESS_WARES_SINGLE;
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO PROCEDURE AA_COWA_COMPRESS_WARES_SINGLE;
GRANT SELECT ON WARES TO PROCEDURE AA_COWA_COMPRESS_WARES_SINGLE;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARES_SINGLE TO SYSDBA;
/******************************************************************************/
/*** Generated by IBExpert 14.01.2017 15:41:13 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_COWA_4REP_ID;
CREATE TABLE COWA_4REP (
ID DM_ID NOT NULL /* DM_ID = BIGINT NOT NULL */,
WARE_ID DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
ACTUAL_WARE_ID DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
INSERTDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE COWA_4REP ADD CONSTRAINT PK_COWA_4REP PRIMARY KEY (ID);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: COWA_4REP_BI */
CREATE OR ALTER TRIGGER COWA_4REP_BI FOR COWA_4REP
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_cowa_4rep_id,1);
new.insertdt=current_timestamp;
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
SET TERM ^ ;
create or alter procedure AA_COWA_COMPRESS_WARE (
ID type of DM_UUID,
ACTUAL_WARE_ID type of DM_UUID,
DOUPDATE integer)
returns (
S type of DM_BLOBTEXT)
as
begin
if (DOUPDATE is null) then DOUPDATE=0;
s=null;
select
'update or insert into wares (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,D$UUID,D$SRVUPDDT,SNAME,MGN_NAME,MGN_ID,MGN_SOURCE,WHASH) values '||
'('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','''||INSERTDT||''','||PACKET||','''||L_ID||''','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(SNAME))||','||(select s from UTPR_VALUE4SQL(MGN_NAME))||','||(select s from UTPR_VALUE4SQL(MGN_ID))||','||(select s from UTPR_VALUE4SQL(MGN_SOURCE))||','||(select s from UTPR_VALUE4SQL(WHASH))||');'
from wares where id=:actual_ware_id into s;
suspend;
if (DOUPDATE<>0) then update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where id=:actual_ware_id and actual_ware_id is not null;
s='update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where actual_ware_id is not null and id='''||:actual_ware_id||''';';
suspend;
if (DOUPDATE<>0) then update wares_log set actual_ware_id=:actual_ware_id, d$srvupddt=current_timestamp where actual_ware_id=:id;
s='update wares_log set actual_ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where actual_ware_id='''||:id||''';';
suspend;
s=null;
if (not exists(select id from wares_log where id=:id and actual_ware_id=:actual_ware_id)) then
begin -- логично, что нет записи об удалении вареса - создаем ее, без g$distribute - будем отправлять по g$tasks, централизованно всем сразу, одним пакетом
if (DOUPDATE<>0) then
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, D$SRVUPDDT, LOG_INSERTDT)
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_ware_id, current_timestamp, current_timestamp from wares where id=:id;
else s='нет записи об удалении вареса (WARE_ID='||id||', actual_ware_id='||actual_ware_id||')';
end
if (not exists(select id from wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id)) then
exception ex_wrong_db 'эээ';
select first 1
'update or 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,LOG_ID,LOG_INSERTDT,L_ID,D$UUID,D$SRVUPDDT,ACTUAL_WARE_ID) '||
'values ('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','||(select s from UTPR_VALUE4SQL(INSERTDT))||','||PACKET||','''||LOG_ID||''','||(select s from UTPR_VALUE4SQL(LOG_INSERTDT))||','||L_ID||','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(ACTUAL_WARE_ID))||');'
from wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id order by log_insertdt into s;
suspend;
if (DOUPDATE<>0) then update parts set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update parts set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
-- if (DOUPDATE<>0) then update doc_detail_active set ware_id=:actual_ware_id where ware_id=:id;
s='update doc_detail_active set ware_id='''||:actual_ware_id||''' where ware_id='''||:id||''';';
suspend;
-- if (DOUPDATE<>0) then update warebase_distr set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
-- s='update warebase_distr set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
-- suspend;
if (DOUPDATE<>0) then update doc_detail_active_treb set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update doc_detail_active_treb set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
if (DOUPDATE<>0) then update DOC_DETAIL_DELETED set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update DOC_DETAIL_DELETED set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
if (DOUPDATE<>0) then update DOC_DETAIL_VIRTUAL set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
s='update DOC_DETAIL_VIRTUAL set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
suspend;
if (DOUPDATE<>0) then delete from wares where id=:id; --триггер при удалении из wares должен быть неактивен
s='delete from wares where id='''||:id||''';';
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,DELETE ON WARES TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,INSERT,UPDATE ON WARES_LOG TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON PARTS TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_TREB TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_DELETED TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_VIRTUAL TO PROCEDURE AA_COWA_COMPRESS_WARE;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO SYSDBA;
SET TERM ^ ;
create or alter procedure AA_COWA_COMPRESS_WARES (
DOUPDATE integer)
returns (
S type of DM_BLOBTEXT)
as
declare variable ID type of DM_UUID;
declare variable ACTUAL_WARE_ID 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 WLIST type of DM_BLOBTEXT;
declare variable MIN_ACTUAL_WARE_ID type of DM_UUID;
declare variable TS type of DM_BLOBTEXT;
declare variable I integer;
declare variable SHIFT integer;
declare variable TASK_ID type of DM_ID;
begin
for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,BARCODE, list(distinct ''''||id||'''') from wares group by NAME_ID,IZG_ID,COUNTRY_ID,BARCODE having count(1)>1 into min_actual_ware_id, NAME_ID,IZG_ID,COUNTRY_ID,BARCODE, WLIST do
begin
actual_ware_id=null;
execute statement 'select first 1 actual_ware_id from wares_log where actual_ware_id in ('||wlist||') order by log_insertdt desc' into actual_ware_id;
if (actual_ware_id is null) then
actual_ware_id=min_actual_ware_id;
if (not exists(select id from wares where id=:actual_ware_id)) then
exception ex_wrong_db 'не найден актуальный товар в wares: actual_ware_id='||actual_ware_id;
for select id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:COUNTRY_ID and BARCODE=:BARCODE and id<>:actual_ware_id into :id do
begin
insert into COWA_4REP (ware_id, actual_ware_id) values (:id,:actual_ware_id);
for select s from AA_COWA_COMPRESS_WARE(:id,:actual_ware_id,:DOUPDATE) into :s do
begin
if (s is null) then exception ex_wrong_db 'id='||id||'; actual_ware_id='||actual_ware_id;
suspend;
end
end
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON WARES TO PROCEDURE AA_COWA_COMPRESS_WARES;
GRANT INSERT ON COWA_4REP TO PROCEDURE AA_COWA_COMPRESS_WARES;
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO PROCEDURE AA_COWA_COMPRESS_WARES;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARES TO SYSDBA;
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_LOG_BI_DISTR 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';
update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WARES_LOG',new.d$uuid,0,null) matching (TABLENAME,UUID);
end
if (new.l_id is null) then
new.l_id=GEN_ID(GEN_WARES_LOG_ID,1);
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);
Окончание
Делаем бекап-ресторе;
