Подготовка серверной БД для перевода на Единое Окно - История изменений

Agk в 11:03, 3 марта 2017

2017-03-03T11:03:13Z

Agk: /* Обновляем структуру БД */

2017-03-03T05:59:22Z

‎Обновляем структуру БД

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

2017-03-03T05:58:57Z

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

Agk в 05:36, 3 марта 2017

2017-03-03T05:36:17Z

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

2017-01-20T09:32:01Z

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

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

2017-01-14T11:43:08Z

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

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

2017-01-14T11:36:49Z

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

Agk в 11:33, 14 января 2017

2017-01-14T11:33:53Z

Внесённые изменения

Agk в 09:43, 14 января 2017

2017-01-14T09:43:25Z

Agk: Новая страница: «==Обновляем структуру БД==

 /*******************************************************************/ /*НОВЫЕ ПОЛЯ*/  ALTER TABLE WA…»

2017-01-14T09:39:20Z

Новая страница: «==Обновляем структуру БД== <pre> /*******************************************************************/ ‎НОВЫЕ ПОЛЯ: ALTER TABLE WA…»

Новая страница

==Обновляем структуру БД==
<pre>
/*******************************************************************/
/*НОВЫЕ ПОЛЯ*/

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

</pre>

← Предыдущая		Версия 11:03, 3 марта 2017
Строка 867:		Строка 867:
	==Окончание==		==Окончание==
	Делаем бекап-ресторе;		Делаем бекап-ресторе;
		+
		+	==Запуск заданий==
		+	Подготавливаем задания в таблице G$TASKS_TMPL и запускаем по требуемым профилям
		+	<pre>
		+	insert into G$TASKS (PROFILE_ID,TASK_TYPE,CAPTION,DATA,CHECKPREVTASK) select 5,TASK_TYPE,CAPTION,DATA,CHECKPREVTASK from G$TASKS_TMPL order by id
		+	</pre>

@@ Строка 842: / Строка 842: @@
 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;
 </pre>
 ==Создаем индексы==

← Предыдущая		Версия 05:36, 3 марта 2017
Строка 1:		Строка 1:
		+	==Обновляем структуру G$TASKS==
		+	<pre>
		+	ALTER TABLE G$TASKS ADD CHECKPREVTASK DM_STATUS;
		+
		+	/******************************************************************************/
		+	/* Generated by IBExpert 03.03.2017 9:49:08 */
		+	/******************************************************************************/
		+
		+	/******************************************************************************/
		+	/* Following SET SQL DIALECT is just for the Database Comparer */
		+	/******************************************************************************/
		+	SET SQL DIALECT 3;
		+
		+
		+
		+	/******************************************************************************/
		+	/* Tables */
		+	/******************************************************************************/
		+
		+
		+
		+	CREATE TABLE G$TASKS_TMPL (
		+	ID DM_ID /* DM_ID = BIGINT NOT NULL */,
		+	PROFILE_ID BIGINT,
		+	TASK_TYPE INTEGER,
		+	CAPTION VARCHAR(250),
		+	DATA BLOB SUB_TYPE 1 SEGMENT SIZE 80,
		+	INSERTDT TIMESTAMP,
		+	SENDDT TIMESTAMP,
		+	ENDDT TIMESTAMP,
		+	ENDFLAG INTEGER,
		+	G$PROFILE_ID BIGINT,
		+	D$UUID CHAR(36),
		+	D$SRVUPDDT TIMESTAMP,
		+	CHECKPREVTASK INTEGER
		+	);
		+
		+
		+
		+
		+	/******************************************************************************/
		+	/* Primary keys */
		+	/******************************************************************************/
		+
		+	ALTER TABLE G$TASKS_TMPL ADD CONSTRAINT PK_G$TASKS_TMPL PRIMARY KEY (ID);
		+
		+
		+	/******************************************************************************/
		+	/* Privileges */
		+	/******************************************************************************/
		+
		+
		+
		+	</pre>
		+
		+
	==Обновляем структуру БД==		==Обновляем структуру БД==
	<pre>		<pre>

@@ Строка 676: / Строка 676: @@
      if (DOUPDATE<>0) then delete from wares where id=:id;  --триггер при удалении из wares должен быть неактивен
      s='delete from wares where id='''||:id||''';';
      suspend;
 end^

← Предыдущая		Версия 11:43, 14 января 2017
Строка 746:		Строка 746:

	GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARES TO SYSDBA;		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 ; ^
		+

	</pre>		</pre>

@@ Строка 61: / Строка 61: @@
 ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
-ALTER TABLE WARES ADD WHASH DM_ID;
+ALTER TABLE WARES ADD WHASH DM_ID_NULL;
 ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
 ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;

@@ Строка 268: / Строка 268: @@
 ==Добавляем объекты для процедуры сжатия==
 <pre>
 SET TERM ^ ;