2015年10月20日 星期二
Android + Sql server + service broker
今天花了一點時間把android推播系統的後端(SQLCLR + stored procedure)整合到service broker ,
以後就可以使用sql server的非同步服務,縮短程式執行時間,
若一次進來大量訊息也可以快點做完
/********************STEPUP**********************************/
USE workdb
ALTER DATABASE workdb SET ENABLE_BROKER
GO
ALTER DATABASE workdb SET TRUSTWORTHY ON
GO
USE workdb
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '!abcd1234'
GO
CREATE MESSAGE TYPE [//arcadyan/sender]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [//arcadyan/receiver]
VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT
[//arcadyan/contract]
( [//arcadyan/sender]
SENT BY INITIATOR,
[//arcadyan/receiver]
SENT BY TARGET
)
GO
/*
CREATE QUEUE sb_queue_in
WITH STATUS=ON
GO
*/
drop service [//arcadyan/receiver_service]
drop sERVICE [//arcadyan/sender_service]
drop queue sb_queue_out
/****** Object: ServiceQueue [misadmTargetQueue_multi] Script Date: 2015/10/20 下午 01:53:27 ******/
create QUEUE [dbo].[sb_queue_out] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[zp_sb_process_internal] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )
--
CREATE QUEUE sb_queue_out
WITH STATUS=ON
GO
CREATE SERVICE [//arcadyan/sender_service]
ON QUEUE dbo.sb_queue_in
([//arcadyan/contract])
go
CREATE SERVICE [//arcadyan/receiver_service]
ON QUEUE dbo.sb_queue_out
([//arcadyan/contract])
GO
/********************STEPUP**********************************/
/*@@@@@zp_sb_request@@@@*/
use workdb
go
alter proc dbo.zp_sb_request @proc nvarchar(max),@input nvarchar(max)
as
--exec workdb.dbo.zp_sb_request 'workdb.dbo.zp_sb_test123','12345'
set nocount on
declare @sb_input nvarchar(max)
declare @site_name_from varchar(30) ,@site_name_to varchar(30)
declare @sb_thread varchar(30),@sp_receive varchar(max)
declare @input_nvarchar nvarchar(max),@retry_count int
declare @mail_to varchar(max),@action_code varchar(50)
declare @XML_INPUT xml
select @input_nvarchar=@input,@retry_count=0
select @sp_receive=@proc
--select @sp_receive='workdb.dbo.zp_sb_test'
set @input_nvarchar=replace(@input_nvarchar,'<','<')
select @sb_input= N''+
N''+isnull(@site_name_from,'')+N' '+
N''+isnull(@site_name_to,'')+N' '+
N''+isnull(@sb_thread,'')+N' '+
N''+isnull(@sp_receive,'')+N' '+
N''+isnull(@input_nvarchar,'')+N''+
N''+
N''+rtrim(ltrim(convert(int,@retry_count)))+' '+
N''+isnull(@mail_to,'')+' '+
N'None '+
N''+isnull(@action_code,'')+' '+
N' '
DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml;
BEGIN DIALOG CONVERSATION @Conv_Handler
FROM SERVICE [//arcadyan/sender_service]
TO SERVICE '//arcadyan/receiver_service'
ON CONTRACT [//arcadyan/contract];
declare @tmp2 int
set @tmp2=1
SELECT @XML_INPUT =cast(@sb_input as xml)
set @XML_INPUT.modify('replace value of (/XML_CONTENT/conversation_id[sql:variable("@tmp2")]/text())[1] with sql:variable("@Conv_Handler")') ;
set @sb_input=cast(@XML_INPUT as nvarchar(max));
INSERT INTO workdb.dbo.sb_message_gateway(
site_name_from,site_name_to,action_code,
message_body,
message_return,dt_create,create_by,stat_update,conversation_id)
values(@site_name_from,@site_name_to,@action_code,
@sb_input,null,getdate(),'system',1,@Conv_Handler)
insert into workdb.dbo.sb_message_out(
conversation_id,message_body,message_type_name,dt_create)
values(@Conv_Handler,@sb_input,N'//arcadyan/Rsender_service',getdate());
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//arcadyan/sender]
(@sb_input);
set nocount off
return
/*@@@@@zp_sb_request@@@@*/
/*
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @message_type_name nvarchar(256);
RECEIVE TOP(1) @Conv_Handler = conversation_handle,
@OrderMsg = message_body,
@message_type_name = message_type_name
FROM dbo.sb_queue_out
-- 兩端 (起始端與目標) 皆必須結束交談
select @OrderMsg,@message_type_name
IF
@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @Conv_Handler;
END
*/
/*
/*
drop table workdb.dbo.sb_message_in
create table workdb.dbo.sb_message_in (id int identity(1,1),
conversation_id nvarchar(max) null, message_return nvarchar(max) null,
message_type_name varchar(max) null, dt_create datetime null,
message_body nvarchar(max) null)
drop table workdb.dbo.sb_message_out
create table workdb.dbo.sb_message_out (id int identity(1,1),
conversation_id nvarchar(max) null, message_return nvarchar(max) null,
message_type_name varchar(max) null, dt_create datetime null,
message_body nvarchar(max) null)
drop table workdb.dbo.sb_message_gateway
create table workdb.dbo.sb_message_gateway(
id int identity(1,1),
conversation_id nvarchar(max) null,
message_return nvarchar(max) null,
stat_update int null,
dt_update datetime null,
update_by varchar(10) null,
site_name_from varchar(50) null,
site_name_to varchar(50) null,
action_code varchar(50) null,
message_body nvarchar(max) null,
dt_create datetime null,
create_by varchaR(30) null)
*/
select cast(message_body as xml),* from sb_queue_out
--END CONVERSATION '3624B2C2-F976-E511-BFB3-000C29F46414'
exec workdb.dbo.[zp_broker_resume] '3624B2C2-F976-E511-BFB3-000C29F46414'
select * from workdb.dbo.sb_message_out
select * from workdb.dbo.sb_message_in
select * from workdb.dbo.sb_message_gateway
truncate table workdb.dbo.sb_message_gateway
truncate table workdb.dbo.sb_message_in
truncate table workdb.dbo.sb_message_out
*/
/***********************[zp_sb_process_internal]****************************/
use workdb
go
/****** Object: StoredProcedure [dbo].[zp_sb_process_internal] Script Date: 01/21/2013 13:17:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*
alter procedure [dbo].[zp_sb_process_internal]
AS
set nocount on
--*/
--EXEC workdb.dbo.zp_sb_process_internal
set nocount on
SET XACT_ABORT ON
declare @proc_name varchar(1024)--, @error_msg nvarchar(max)
declare @conversation uniqueidentifier
declare @msg varbinary(max), @msg_type varchar(1024)
declare @original_msg varbinary(max)
declare @xml XML,@ack_result NVARCHAR(MAX),@OUTPUT_XML xml
DECLARE @XML_INPUT XML ,@XML_OUTPUT XML
DECLARE @ERROR_MSG VARCHAR(2000),@XML_MSG XML ,@PROCNAME VARCHAR(100)
DECLARE @ERROR_PROC VARCHAR(1000) ,@PROCLEVEL INT ,@ERROR_SEVERITY INT,@RTN_STATUS INT
DECLARE @ROWSET NVARCHAR(MAX) ,@RTNPARAMETER NVARCHAR(MAX)
SELECT @PROCLEVEL = @@NESTLEVEL
SELECT @PROCNAME = isnull( OBJECT_NAME(@@PROCID) ,'' )
SELECT @ERROR_PROC = ''
SELECT @ERROR_MSG = ''
SELECT @ERROR_SEVERITY = 0
SELECT @RTN_STATUS = 0
SELECT @XML_OUTPUT = ''
SELECT @ROWSET = ''
SELECT @XML_MSG = ''
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
DECLARE @RecvReplyMsg NVARCHAR(max);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
DECLARE @ReplyMsg NVARCHAR(max),@sp_name nvarchaR(50) ,@input nvarchar(max),@output nvarchar(max)
declare @conversation_id nvarchar(max)
declare @return_value int
declare @return_message varchar(max)
declare @err_flag int
select @err_flag=0
Select @proc_name = 'zp_sb_process_internal'
begin try
WHILE (1=1)
BEGIN
waitfor(
RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body,
@RecvReqMsgName = message_type_name
FROM sb_queue_out),timeout 1000
if @@rowcount>0
begin
IF @RecvReqMsgName=N'//arcadyan/sender'
begin
set @sp_name='workdb.dbo.zp_sb_rec'
set @input=@RecvReplyMsg
--select @sp_name ,@input
begin try
begin try
select @sp_name, @input ,@output
declare @output nvarchar(max)
exec workdb.dbo.zp_sb_rec 'workdb.dbo.zp_send_gcm_message <root><user_id>A110018</user_id><message>測試來自CNC</message><id_msg_gcm_global>1</id_msg_gcm_global></root>0 E6DE4C0E-0177-E511-BFB3-000C29F46414 workdb.dbo.zp_send_gcm_message_pack <root><user_id>A110018</user_id><message>測試來自CNC2</message><id_msg_gcm_global>2</id_msg_gcm_global></root>0 C58CDCAA-FC76-E511-BFB3-000C29F46414 '
SELECT @ROWSET = ''
SELECT @XML_MSG = ''
SET XACT_ABORT ON
BEGIN TRY
declare @rec_proc_name varchar(50),@rec_input nvarchaR(max),@site_name_to varchar(50),@site_name_from varchar(50)
declare @req_id nvarchar(max),@action_code varchar(100),@mail_to varchar(max)
declare @retry_count int,@proc_retry_count int
declare @flag_error int
begin try
select @tmp2=1,@retry_count=0,@proc_retry_count=0
set @flag_error=0
SET @XML_INPUT=CAST(@input AS XML)
select @rec_proc_name=@XML_INPUT.value('(/XML_CONTENT/sp_receive)[1]','NVARCHAR(MAX)')
select @rec_input=@XML_INPUT.value('(/XML_CONTENT/input)[1]','NVARCHAR(MAX)')
select @req_id=@XML_INPUT.value('(/XML_CONTENT/conversation_id)[1]','NVARCHAR(MAX)')
select @action_code=@XML_INPUT.value('(/XML_CONTENT/action_code)[1]','NVARCHAR(MAX)')
select @mail_to=@XML_INPUT.value('(/XML_CONTENT/mail_to)[1]','NVARCHAR(MAX)')
select @site_name_to=@XML_INPUT.value('(/XML_CONTENT/site_name_to)[1]','NVARCHAR(MAX)')
select @site_name_from=@XML_INPUT.value('(/XML_CONTENT/site_name_from)[1]','NVARCHAR(MAX)')
select @retry_count=convert(int,@XML_INPUT.value('(/XML_CONTENT/retry_count)[1]','NVARCHAR(MAX)') )
select @input=cast(@rec_input as nvarchar(max))
while (@proc_retry_count <= @retry_count)
begin
begin try
BEGIN TRANSACTION
--select @rec_proc_name,@rec_input,@output
exec @rec_proc_name @input, @output output
COMMIT TRANSACTION
break;
end try
begin catch
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END
waitfor delay '00:00:05'
begin
set @proc_retry_count=@proc_retry_count+1
--set @error_msg_output=@error_msg_output+'('+convert(varchar(10),@proc_retry_count)+')'
-- exec GlobalSYNC.dbo.zp_send_broker_mail
--'REC',
--@site_name_to,
--@site_name_from,
--@mail_to,
--@action_code,
--'Fail',
--@error_msg_output,
--@req_id
if @proc_retry_count>@retry_count
begin
set @error_msg_output=''
set @error_msg_output=@error_msg_output+ISNULL(ERROR_MESSAGE(),'')+'Retry Count :'+Convert(varchar(10),@proc_retry_count-1 )
set @flag_error=1
break;
end
end
end catch
end
if @flag_error=1
begin
RAISERROR(@error_msg_output,16,101)
end
set @sp_output=replace(@output,'<','<')
set @sb_result=N'SB_OK'
set @output=isnull(@output,'')
set @XML_INPUT.modify('replace value of (/XML_CONTENT/output[sql:variable("@tmp2")]/text())[1] with sql:variable("@sp_output")')
select @output=cast(@XML_INPUT as nvarchar(max))
goto ProcEnd
end try
begin catch
SET @error_msg_output=''
set @error_msg_output=@error_msg_output+ISNULL(ERROR_MESSAGE(),'')
/*
exec GlobalSYNC.dbo.zp_send_broker_mail
'REC',
@site_name_to,
@site_name_from,
@mail_to,
@action_code,
'Fail',
@error_msg_output,
@req_id
*/
RAISERROR(@error_msg_output,16,101)
end catch
return
END TRY
BEGIN CATCH
SELECT @ERROR_SEVERITY = ERROR_SEVERITY()
SELECT @ERROR_PROC = ERROR_PROCEDURE()
SELECT @ERROR_MSG = '[PROC]:['+LTRIM(STR(@PROCLEVEL))+']'+@PROCNAME + '[LINE]:'+ CONVERT(VARCHAR(MAX),ERROR_LINE()) + ', ' + ERROR_MESSAGE()
Goto Exception
END CATCH
ProcEnd:
RETURN --@RTN_STATUS
Exception:
SELECT @DOC_OUTPUT=@ERROR_MSG
RAISERROR ('PROC:[%d]%s CODE:%d%s', @ERROR_SEVERITY,@RTN_STATUS, @PROCLEVEL ,@PROCNAME , @RTN_STATUS ,@ERROR_MSG )
GO
go
grant execute on workdb.dbo.[zp_sb_rec] to public
go
/*********************************[zp_sb_rec]******************************************/
/************************[zp_broker_resume]**********************************/
create proc [dbo].[zp_broker_resume] @conversation_handle nvarchar(max)
as
declare @mt varchar(max),@body nvarchar(max)--,@conversation_handle UNIQUEIDENTIFIER
declare @conversation_handle nvarchar(max)
select @conversation_handle='67D87BFE-F276-E511-BFB3-000C29F46414'
--select @conversation_handle='BE9793AA-7A38-E211-B009-00188B318D66'
set @mt='//arcadyan/receiver';
--set @mt='//GlobalSYNC/ReplyMessage';
select @body=message_body
--select *
from sb_queue_out
where [conversation_handle]=@conversation_handle;
SEND ON CONVERSATION (@conversation_handle)
MESSAGE TYPE @mt
(@body);
return
GO
/************************[zp_broker_resume]**********************************/
/***********************/
USE [workdb]
GO
/****** Object: StoredProcedure [dbo].[zp_send_gcm_message_job] Script Date: 2015/10/20 下午 03:00:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[zp_send_gcm_message_job]
as
set nocount on
if object_id('tempdb.dbo.#msg_list') is not null drop table #msg_list
select id=identity(int,1,1),user_id,message,id_msg_gcm_global=converT(int,id_msg_gcm_global)
into #msg_list
from workdb.dbo.msg_gcm_global
where stat_update=0
declare @row int,@row_count int
declare @user_id nvarchar(30),@message nvarchar(max),@id_msg_gcm_global int
declare @xml_input nvarchar(max)
set @row=1
select @row_count =count(*)
from #msg_list
while @row<>@row_count+1
begin
select @user_id=user_id,@message=message,@id_msg_gcm_global=id_msg_gcm_global
from #msg_list
where id=@row
--select @xml_input='test'
select @xml_input=''+isnull(@user_id,'')+' '+
''+isnull(@message,'')+' '+
''+isnull(ltrim(rtrim(str(@id_msg_gcm_global))),'0')+' 2015年10月4日 星期日
android 資源
android layout example1
https://github.com/Trinea/android-open-project
android layout example2
http://www.23code.com/dong-hua-xiao-guo-animate/
2015年10月1日 星期四
JQUERY資源
http://pjchender.blogspot.tw/2015/03/jquery_8.html
jquery教學
http://pclevin.blogspot.tw/search/label/jQuery
jquery邊做邊學
https://www.youtube.com/playlist?list=PLpRG_zGZAavj3aztURYe_Sq286-Sl8PtR
jquery 圖表套件
http://www.highcharts.com/demo/bar-stacked
訂閱：
意見 (Atom)