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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
Строка 263: Строка 263:
 
SET TERM ; ^
 
SET TERM ; ^
  
 +
 +
</pre>
 +
 +
==Добавляем объекты для процедуры сжатия==
 +
<pre>
 +
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;
  
 
</pre>
 
</pre>

Версия 16:33, 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 ; ^

Добавляем объекты для процедуры сжатия

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;

Подготавливаем товары к сжатию

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

Окончание

Делаем бекап-ресторе;
Источник — «http://wiki.standart-n.ru/index.php?title=Подготовка_серверной_БД_для_перевода_на_Единое_Окно&oldid=5306»