ТНВЭД — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎У тех клиентов где код тнвэд в таблице wares)
 
(не показано 16 промежуточных версии этого же участника)
Строка 1: Строка 1:
−
* В глобальной базе в таблицу wares добавляем поле TNVEDCODE.
+
==Новая вспомогательная таблица TNVED==
 
  <nowiki>
 
  <nowiki>
−
ALTER TABLE WARES ADD TNVEDCODE DM_TEXT;
+
/******************************************************************************/
−
</nowiki>
+
/***              Generated by IBExpert 16.08.2018 12:21:47                ***/
−
* Для всех сводных баз и локальных по g$tasks так же отправляем создание новой колонки
+
/******************************************************************************/
−
* В vw_doc_detail добавить колонку TNVEDCODE. Заполнять значением из wares. Распространить изменение по g$tasks на все локальные базы.
+
 
−
* Предзаполнение
+
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 
SET SQL DIALECT 3;
 
SET SQL DIALECT 3;
−
CREATE TABLE TNVED ( TNVEDCODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
+
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Tables                                ***/
 +
/******************************************************************************/
 +
 
 +
 
 +
 
 +
CREATE TABLE TNVED (
 +
    TNVEDCODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
 
     BARCODE    DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
 
     BARCODE    DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
−
     SNAME      DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */
+
     SNAME      DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */
 
);
 
);
−
CREATE INDEX TNVED_IDX1 ON TNVED (BARCODE);
 
  
−
* Выполнить файл tnved.sql
+
 
−
* Заполняем коды  
+
 
−
update wares w set tnvedcode=(select first 1 t.tnvedcode from tnved t where t.barcode=w.barcode) where exists (select first 1  t.tnvedcode from tnved t where t.barcode=w.barcode)
+
 
−
update wares w set tnvedcode=(select first 1 t.tnvedcode from tnved t where t.sname=w.sname) where  exists (select first 1  t.tnvedcode from tnved t where t.sname=w.sname) and coalesce(trim(w.tnvedcode),'')=''
+
/******************************************************************************/
−
update wares w set tnvedcode=(select first 1 ww.tnvedcode from wares ww where ww.name_id=w.name_id and ww.tnvedcode is not null ) where exists  (select first 1 ww.tnvedcode from wares ww  where ww.name_id=w.name_id and ww.tnvedcode is not null ) and w.tnvedcode is null
+
/***                              Primary keys                              ***/
−
* Обновить менеджер до версии 273,64
+
/******************************************************************************/
 +
 
 +
ALTER TABLE TNVED ADD CONSTRAINT PK_TNVED PRIMARY KEY (TNVEDCODE, BARCODE, SNAME);
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Indices                                ***/
 +
/******************************************************************************/
 +
 
 +
CREATE INDEX TNVED_IDX1 ON TNVED (TNVEDCODE);
 +
CREATE INDEX TNVED_IDX2 ON TNVED (BARCODE);
 +
CREATE INDEX TNVED_IDX3 ON TNVED (SNAME);
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
 
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER TNVED_BI0 FOR TNVED
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  new.tnvedcode=trim(new.tnvedcode);
 +
end
 +
^
 +
 
 +
SET TERM ; ^
 +
 
 +
</nowiki>
 +
 
 +
==Выполнить файл tnved.sql==
 +
[[Файл:Tnved.rar]]
 +
 
 +
==Заполняем коды ==
 +
долгая операция может уйти до получаса
 +
  <nowiki>
 +
EXECUTE BLOCK
 +
AS
 +
declare variable b dm_text;
 +
declare variable t dm_text;
 +
declare variable ware_id dm_uuid;
 +
BEGIN
 +
  for select t.barcode,t.tnvedcode from tnved t into :b,:t do
 +
  begin
 +
    for select id from wares where barcode containing :b into :ware_id do
 +
      update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values ('TNVEDCODE',:ware_id,:t) matching (ATTR_SID,ATTR_LINKID);
 +
  end
 +
END;
 +
</nowiki>
 +
 
 +
==Обновить менеджер до версии не ниже 273.75==
 +
==У тех клиентов где код тнвэд в таблице wares нужно перенести на атрибуты==
 +
<pre>
 +
EXECUTE BLOCK
 +
AS
 +
  declare variable t dm_text;
 +
  declare variable w dm_text;
 +
BEGIN
 +
  for select edizm,ware_id from vw_parts where coalesce(trim(edizm),'') <>'' into :t,:w do
 +
  update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values
 +
  ('UNITNOMENCLATURE',:w,:t) matching (ATTR_SID,ATTR_LINKID);
 +
 
 +
END;
 +
 
 +
 
 +
EXECUTE BLOCK
 +
AS
 +
  declare variable t dm_text;
 +
declare variable w dm_text;
 +
BEGIN
 +
for select tnvedcode,id from wares where coalesce(trim(tnvedcode),'') <>'' into :t,:w do
 +
  update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values
 +
  ('TNVEDCODE',:w,:t) matching (ATTR_SID,ATTR_LINKID);
 +
 
 +
END;
 +
</pre>
 +
 
 +
==Скрипт на перевод атрибутов на двуст.синхронизацию. Сервер==
 +
* Не забыть проверить настройки в G$DISTRIBUTE_VECTORS, G$DISTRIBUTE_X_TABLES
 +
<pre>
 +
/******************************************************************************/
 +
/***              Generated by IBExpert 14.08.2018 12:37:19                ***/
 +
/******************************************************************************/
 +
 
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Tables                                ***/
 +
/******************************************************************************/
 +
 
 +
 
 +
 
 +
CREATE TABLE ATTRIBUTES (
 +
    ATTR_SID            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    ATTR_LINKFIELDNAME DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    ATTR_TYPE          DM_STATUS /* DM_STATUS = INTEGER */,
 +
    STATUS              DM_STATUS /* DM_STATUS = INTEGER */,
 +
    INSERTDT            DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
 +
    D$SRVUPDDT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
 +
    D$UUID              DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */
 +
);
 +
 
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                          Unique constraints                          ***/
 +
/******************************************************************************/
 +
 
 +
ALTER TABLE ATTRIBUTES ADD CONSTRAINT UNQ1_ATTRIBUTES UNIQUE (D$UUID);
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Indices                                ***/
 +
/******************************************************************************/
 +
 
 +
CREATE INDEX ATTRIBUTES_IDX1 ON ATTRIBUTES (ATTR_SID);
 +
 
 +
/******************************************************************************/
 +
/***                          Fields descriptions                          ***/
 +
/******************************************************************************/
 +
 
 +
COMMENT ON COLUMN ATTRIBUTES.ATTR_TYPE IS
 +
'0 - string
 +
1 - int
 +
2 - float
 +
3 - date
 +
4 - datetime';
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***              Generated by IBExpert 14.08.2018 12:38:29                ***/
 +
/******************************************************************************/
 +
 
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Tables                                ***/
 +
/******************************************************************************/
 +
 
 +
 
 +
CREATE GENERATOR GEN_ATTRIBUTE_DETAIL_ID;
 +
 
 +
CREATE TABLE ATTRIBUTE_DETAIL (
 +
    ID          DM_ID NOT NULL /* DM_ID = BIGINT */,
 +
    ATTR_SID    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    ATTR_LINKID DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
 +
    ATTR_SVALUE  DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
 +
    INSERTDT    DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
 +
    D$SRVUPDDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
 +
    D$UUID      DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */
 +
);
 +
 
 +
 
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                          Unique constraints                          ***/
 +
/******************************************************************************/
 +
 
 +
ALTER TABLE ATTRIBUTE_DETAIL ADD CONSTRAINT UNQ1_ATTRIBUTE_DETAIL UNIQUE (D$UUID);
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                              Primary keys                              ***/
 +
/******************************************************************************/
 +
 
 +
ALTER TABLE ATTRIBUTE_DETAIL ADD CONSTRAINT PK_ATTRIBUTE_DETAIL PRIMARY KEY (ID);
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                                Indices                                ***/
 +
/******************************************************************************/
 +
 
 +
CREATE UNIQUE INDEX ATTRIBUTE_DETAIL_IDX1 ON ATTRIBUTE_DETAIL (ATTR_SID, ATTR_LINKID);
 +
CREATE INDEX ATTRIBUTE_DETAIL_IDX2 ON ATTRIBUTE_DETAIL (ATTR_SID, ATTR_LINKID, ATTR_SVALUE);
 +
 
 +
 
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
 
 +
 
 +
INSERT INTO G$DISTRIBUTE_VECTORS ( FROM_PROFILE_ID, TO_PROFILE_ID, TABLENAME, PERMISSION) VALUES ( 0, 0, 'ATTRIBUTE_DETAIL', 0);
 +
INSERT INTO G$DISTRIBUTE_VECTORS ( FROM_PROFILE_ID, TO_PROFILE_ID, TABLENAME, PERMISSION) VALUES ( 0, 0, 'ATTRIBUTES', 0);
 +
</pre>
 +
==Скрипт на перевод атрибутов на двуст.синхронизацию. Клиент==
 +
<pre>
 +
ALTER TABLE ATTRIBUTE_DETAIL
 +
ADD D$SRVUPDDT DM_DATETIME;
 +
 
 +
ALTER TABLE ATTRIBUTE_DETAIL
 +
ADD D$UUID DM_UUID
 +
NOT NULL ;
 +
 
 +
ALTER TABLE ATTRIBUTE_DETAIL
 +
ADD CONSTRAINT UNQ1_ATTRIBUTE_DETAIL
 +
UNIQUE (D$UUID);
 +
 
 +
SET TERM ^ ;
 +
 
 +
CREATE trigger attribute_detail_bi_distr for attribute_detail
 +
active before insert position 0
 +
AS
 +
begin
 +
  if (new.d$uuid is null) then
 +
  begin
 +
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
  end
 +
  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 ('ATTRIBUTE_DETAIL',new.d$uuid,0,null) matching (TABLENAME,UUID);
 +
  end
 +
end^
 +
 
 +
SET TERM ; ^
 +
 
 +
SET TERM ^ ;
 +
 
 +
CREATE trigger attribute_detail_bu_distr for attribute_detail
 +
active before update position 0
 +
AS
 +
begin
 +
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
 +
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',new.d$uuid,1,null)
 +
    matching (TABLENAME,UUID);
 +
end^
 +
 
 +
SET TERM ; ^
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER ATTRIBUTE_DETAIL_AD_DISTR FOR ATTRIBUTE_DETAIL
 +
ACTIVE AFTER DELETE POSITION 0
 +
AS
 +
begin
 +
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',old.d$uuid,2,null)
 +
  matching (TABLENAME,UUID);
 +
end
 +
^
 +
 
 +
SET TERM ; ^
 +
 
 +
update ATTRIBUTE_DETAIL set d$srvupddt='2000-01-01',d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
update ATTRIBUTE_DETAIL set id=id;
 +
 
 +
 
 +
 
 +
 
 +
ALTER TABLE ATTRIBUTES
 +
ADD D$SRVUPDDT DM_DATETIME;
 +
 
 +
ALTER TABLE ATTRIBUTES
 +
ADD D$UUID DM_UUID
 +
NOT NULL ;
 +
 
 +
ALTER TABLE ATTRIBUTES
 +
ADD CONSTRAINT UNQ1_ATTRIBUTES
 +
UNIQUE (D$UUID);
 +
 
 +
SET TERM ^ ;
 +
 
 +
CREATE trigger ATTRIBUTES_bi_distr for ATTRIBUTES
 +
active before insert position 0
 +
AS
 +
begin
 +
  if (new.d$uuid is null) then
 +
  begin
 +
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
  end
 +
  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 ('ATTRIBUTES',new.d$uuid,0,null) matching (TABLENAME,UUID);
 +
  end
 +
end^
 +
 
 +
SET TERM ; ^
 +
 
 +
SET TERM ^ ;
 +
 
 +
CREATE trigger ATTRIBUTES_bu_distr for ATTRIBUTES
 +
active before update position 0
 +
AS
 +
begin
 +
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
 +
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',new.d$uuid,1,null)
 +
    matching (TABLENAME,UUID);
 +
end^
 +
 
 +
SET TERM ; ^
 +
 
 +
SET TERM ^ ;
 +
 
 +
 
 +
 
 +
CREATE OR ALTER TRIGGER ATTRIBUTES_AD_DISTR FOR ATTRIBUTES
 +
ACTIVE AFTER DELETE POSITION 0
 +
AS
 +
begin
 +
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',old.d$uuid,2,null)
 +
  matching (TABLENAME,UUID);
 +
end
 +
^
 +
 
 +
SET TERM ; ^
 +
 
 +
update ATTRIBUTES set d$srvupddt='2000-01-01',d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
update ATTRIBUTES set STATUS=STATUS;
 +
</pre>

Текущая версия на 16:19, 20 августа 2018

Новая вспомогательная таблица TNVED

/******************************************************************************/
/***               Generated by IBExpert 16.08.2018 12:21:47                ***/
/******************************************************************************/

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



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



CREATE TABLE TNVED (
    TNVEDCODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    BARCODE    DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    SNAME      DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE TNVED ADD CONSTRAINT PK_TNVED PRIMARY KEY (TNVEDCODE, BARCODE, SNAME);


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

CREATE INDEX TNVED_IDX1 ON TNVED (TNVEDCODE);
CREATE INDEX TNVED_IDX2 ON TNVED (BARCODE);
CREATE INDEX TNVED_IDX3 ON TNVED (SNAME);


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

SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER TNVED_BI0 FOR TNVED
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.tnvedcode=trim(new.tnvedcode);
end
^

SET TERM ; ^

Выполнить файл tnved.sql

Файл:Tnved.rar

Заполняем коды

долгая операция может уйти до получаса 

EXECUTE BLOCK
AS
declare variable b dm_text;
declare variable t dm_text;
declare variable ware_id dm_uuid;
BEGIN
  for select t.barcode,t.tnvedcode from tnved t into :b,:t do
  begin
    for select id from wares where barcode containing :b into :ware_id do
      update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values ('TNVEDCODE',:ware_id,:t) matching (ATTR_SID,ATTR_LINKID);
  end
END;

Обновить менеджер до версии не ниже 273.75

У тех клиентов где код тнвэд в таблице wares нужно перенести на атрибуты

EXECUTE BLOCK
AS
 declare variable t dm_text;
 declare variable w dm_text;
BEGIN
 for select edizm,ware_id from vw_parts where coalesce(trim(edizm),'') <>'' into :t,:w do
   update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values
   ('UNITNOMENCLATURE',:w,:t) matching (ATTR_SID,ATTR_LINKID);

END;


EXECUTE BLOCK
AS
 declare variable t dm_text;
 declare variable w dm_text;
BEGIN
 for select tnvedcode,id from wares where coalesce(trim(tnvedcode),'') <>'' into :t,:w do
   update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values
   ('TNVEDCODE',:w,:t) matching (ATTR_SID,ATTR_LINKID);

END;

Скрипт на перевод атрибутов на двуст.синхронизацию. Сервер

  • Не забыть проверить настройки в G$DISTRIBUTE_VECTORS, G$DISTRIBUTE_X_TABLES 
/******************************************************************************/
/***               Generated by IBExpert 14.08.2018 12:37:19                ***/
/******************************************************************************/

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



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



CREATE TABLE ATTRIBUTES (
    ATTR_SID            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ATTR_LINKFIELDNAME  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ATTR_TYPE           DM_STATUS /* DM_STATUS = INTEGER */,
    STATUS              DM_STATUS /* DM_STATUS = INTEGER */,
    INSERTDT            DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$SRVUPDDT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$UUID              DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */
);




/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/

ALTER TABLE ATTRIBUTES ADD CONSTRAINT UNQ1_ATTRIBUTES UNIQUE (D$UUID);


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

CREATE INDEX ATTRIBUTES_IDX1 ON ATTRIBUTES (ATTR_SID);

/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN ATTRIBUTES.ATTR_TYPE IS 
'0 - string
1 - int
2 - float
3 - date
4 - datetime';



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



/******************************************************************************/
/***               Generated by IBExpert 14.08.2018 12:38:29                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_ATTRIBUTE_DETAIL_ID;

CREATE TABLE ATTRIBUTE_DETAIL (
    ID           DM_ID NOT NULL /* DM_ID = BIGINT */,
    ATTR_SID     DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ATTR_LINKID  DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    ATTR_SVALUE  DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    INSERTDT     DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$SRVUPDDT   DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    D$UUID       DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */
);




/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/

ALTER TABLE ATTRIBUTE_DETAIL ADD CONSTRAINT UNQ1_ATTRIBUTE_DETAIL UNIQUE (D$UUID);


/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE ATTRIBUTE_DETAIL ADD CONSTRAINT PK_ATTRIBUTE_DETAIL PRIMARY KEY (ID);


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

CREATE UNIQUE INDEX ATTRIBUTE_DETAIL_IDX1 ON ATTRIBUTE_DETAIL (ATTR_SID, ATTR_LINKID);
CREATE INDEX ATTRIBUTE_DETAIL_IDX2 ON ATTRIBUTE_DETAIL (ATTR_SID, ATTR_LINKID, ATTR_SVALUE);


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


INSERT INTO G$DISTRIBUTE_VECTORS ( FROM_PROFILE_ID, TO_PROFILE_ID, TABLENAME, PERMISSION) VALUES ( 0, 0, 'ATTRIBUTE_DETAIL', 0);
INSERT INTO G$DISTRIBUTE_VECTORS ( FROM_PROFILE_ID, TO_PROFILE_ID, TABLENAME, PERMISSION) VALUES ( 0, 0, 'ATTRIBUTES', 0);

Скрипт на перевод атрибутов на двуст.синхронизацию. Клиент

ALTER TABLE ATTRIBUTE_DETAIL
ADD D$SRVUPDDT DM_DATETIME;

ALTER TABLE ATTRIBUTE_DETAIL
ADD D$UUID DM_UUID
NOT NULL ;

ALTER TABLE ATTRIBUTE_DETAIL
ADD CONSTRAINT UNQ1_ATTRIBUTE_DETAIL
UNIQUE (D$UUID);

SET TERM ^ ;

CREATE trigger attribute_detail_bi_distr for attribute_detail
active before insert position 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
  end
  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 ('ATTRIBUTE_DETAIL',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
end^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger attribute_detail_bu_distr for attribute_detail
active before update position 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
end^

SET TERM ; ^

SET TERM ^ ;



CREATE OR ALTER TRIGGER ATTRIBUTE_DETAIL_AD_DISTR FOR ATTRIBUTE_DETAIL
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTE_DETAIL',old.d$uuid,2,null)
  matching (TABLENAME,UUID);
end
^

SET TERM ; ^

update ATTRIBUTE_DETAIL set d$srvupddt='2000-01-01',d$uuid=UUID_TO_CHAR(GEN_UUID());
update ATTRIBUTE_DETAIL set id=id;




ALTER TABLE ATTRIBUTES
ADD D$SRVUPDDT DM_DATETIME;

ALTER TABLE ATTRIBUTES
ADD D$UUID DM_UUID
NOT NULL ;

ALTER TABLE ATTRIBUTES
ADD CONSTRAINT UNQ1_ATTRIBUTES
UNIQUE (D$UUID);

SET TERM ^ ;

CREATE trigger ATTRIBUTES_bi_distr for ATTRIBUTES
active before insert position 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
  end
  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 ('ATTRIBUTES',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
end^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ATTRIBUTES_bu_distr for ATTRIBUTES
active before update position 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
end^

SET TERM ; ^

SET TERM ^ ;



CREATE OR ALTER TRIGGER ATTRIBUTES_AD_DISTR FOR ATTRIBUTES
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('ATTRIBUTES',old.d$uuid,2,null)
  matching (TABLENAME,UUID);
end
^

SET TERM ; ^

update ATTRIBUTES set d$srvupddt='2000-01-01',d$uuid=UUID_TO_CHAR(GEN_UUID());
update ATTRIBUTES set STATUS=STATUS;
Источник — «http://wiki.standart-n.ru/index.php?title=ТНВЭД&oldid=6752»