drop database MessageReceiver
go
/*==============================================================*/
/* Database: MessageReceiver */
/*==============================================================*/
create database MessageReceiver
go
use MessageReceiver
go
/*==============================================================*/
/* Table: ReceiveMessage */
/*==============================================================*/
create table ReceiveMessage (
ID int identity,
MessageType nvarchar(200) not null,
Operation smallint not null,
Content nvarchar(max) not null,
IsCompleted bit not null,
TraceID uniqueidentifier not null default newid(),
constraint PK_RECEIVEMESSAGE primary key (ID)
)
go
/*==============================================================*/
/* Table: ReceiveMessageLog */
/*==============================================================*/
create table ReceiveMessageLog (
ID int identity,
ReceiveMessageID int not null,
LogTime datetime not null default getdate(),
Remark nvarchar(100) null,
constraint PK_RECEIVEMESSAGELOG primary key (ID)
)
go
/*==============================================================*/
/* Index: ix_ReceiveMessageLog_MsgID */
/*==============================================================*/
create index ix_ReceiveMessageLog_MsgID on ReceiveMessageLog (
ReceiveMessageID ASC
)
go
/*==============================================================*/
/* Table: SendMessage */
/*==============================================================*/
create table SendMessage (
ID int identity,
MessageType nvarchar(200) not null,
Operation smallint not null,
Content nvarchar(max) not null,
IsArrived bit not null,
TraceID uniqueidentifier not null default newid(),
constraint PK_SENDMESSAGE primary key (ID)
)
go
/*==============================================================*/
/* Table: SendMessageLog */
/*==============================================================*/
create table SendMessageLog (
ID int identity,
SendMessageID int not null,
LogTime datetime not null default getdate(),
Remark nvarchar(100) null,
constraint PK_SENDMESSAGELOG primary key (ID)
)
go
/*==============================================================*/
/* Index: ix_SendMessageLog_MsgID */
/*==============================================================*/
create index ix_SendMessageLog_MsgID on SendMessageLog (
SendMessageID ASC
)
go
alter table ReceiveMessageLog
add constraint fk_ReceiveMessage_ReceiveMessageID foreign key (ReceiveMessageID)
references ReceiveMessage (ID)
go
alter table SendMessageLog
add constraint fk_SendMessageLog_SendMessageID foreign key (SendMessageID)
references SendMessage (ID)
go
create procedure up_SendMessageToRemoteServer
as
declare @SendMessageID int,@MessageType nvarchar(200),@Operation smallint,@Content nvarchar(max),@TraceID uniqueidentifier
while(1=1)
begin
set @SendMessageID=null
select top(1) @SendMessageID=ID,
@MessageType=MessageType,
@Operation=Operation,
@Content=Content,
@TraceID=TraceID
from SendMessage a
where a.IsArrived = 0
order by a.ID
if (@SendMessageID is null) break
exec Server001.MessageReceiver.dbo.up_cReceiveMessageForRemoteServer
@MessageType =@MessageType,
@Operation = @Operation,
@Content = @Content,
@TraceID=@TraceID
if (@@error <> 0) break
exec up_cSendMessageLog
@SendMessageID = @SendMessageID,
@Remark = N'发送',
@IsArrived = 1
end
go
create procedure up_cReceiveMessage
(
@MessageType nvarchar(200),
@Operation smallint,
@Content nvarchar(max)
)
as
begin try
begin transaction
declare @ReceiveMessageID int
insert into ReceiveMessage ( MessageType, Operation, Content,IsCompleted)
values(@MessageType,@Operation,@Content,0)
set @ReceiveMessageID=scope_identity()
insert into ReceiveMessageLog ( ReceiveMessageID, Remark )
values(@ReceiveMessageID,N'接收.')
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
create procedure up_cReceiveMessageForRemoteServer
(
@MessageType nvarchar(200),
@Operation smallint,
@Content nvarchar(max),
@TraceID uniqueidentifier
)
as
begin try
begin transaction
declare @ReceiveMessageID int
insert into ReceiveMessage ( MessageType, Operation, Content,IsCompleted,TraceID)
values(@MessageType,@Operation,@Content,0,@TraceID)
set @ReceiveMessageID=scope_identity()
insert into ReceiveMessageLog ( ReceiveMessageID, Remark )
values(@ReceiveMessageID,N'接收.')
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
create procedure up_cReceiveMessageLog
(
@ReceiveMessageID int,
@Remark nvarchar(100),
@IsCompleted bit
)
as
begin try
begin transaction
insert into ReceiveMessageLog ( ReceiveMessageID, Remark )
values(@ReceiveMessageID,@Remark)
update ReceiveMessage set IsCompleted=@IsCompleted where ID=@ReceiveMessageID
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
create procedure up_cSendMessage
(
@MessageType nvarchar(200),
@Operation smallint,
@Content nvarchar(max)
)
as
begin try
begin transaction
declare @SendMessageID int
insert into SendMessage ( MessageType, Operation, Content,IsArrived)
values(@MessageType,@Operation,@Content,0)
set @SendMessageID=scope_identity()
insert into SendMessageLog ( SendMessageID, Remark )
values(@SendMessageID,N'接收.')
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
create procedure up_cSendMessageLog
(
@SendMessageID int,
@Remark nvarchar(100),
@IsArrived bit
)
as
begin try
begin transaction
insert into SendMessageLog ( SendMessageID, Remark )
values(@SendMessageID,@Remark)
update SendMessage set IsArrived=@IsArrived where ID=@SendMessageID
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
create procedure up_dReceiveMessageWithCompleted
as
set nocount on
begin try
begin transaction
declare @tb_del table(ID int)
insert into @tb_del(ID) select ID from ReceiveMessage where IsCompleted=1
delete a from ReceiveMessageLog a where exists(select 1 from @tb_del x where x.ID=a.ReceiveMessageID)
delete a from ReceiveMessage a where exists(select 1 from @tb_del x where x.ID=a.ID)
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
create procedure up_dSendMessageWithArrived
as
begin try
begin transaction
declare @tb_del table(ID int)
insert into @tb_del(ID) select ID from SendMessage where IsArrived=1
delete a from SendMessageLog a where exists(select 1 from @tb_del x where x.ID=a.SendMessageID)
delete a from SendMessage a where exists(select 1 from @tb_del x where x.ID=a.ID)
commit transaction
end try
begin catch
declare @error nvarchar(2048)=error_message()
;throw 50001 ,@error,1
if (@@trancount >0) rollback transaction
end catch
go
