夏凌云，龔文濤

基于存儲(chǔ)過(guò)程和HTTP協(xié)議的消息主動(dòng)推送系統(tǒng)實(shí)現(xiàn)

夏凌云，龔文濤

數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程和觸發(fā)器多用于數(shù)據(jù)完整性檢測(cè)和復(fù)雜規(guī)則約束，其提供了一個(gè)當(dāng)數(shù)據(jù)表數(shù)據(jù)發(fā)生變更后的主動(dòng)操作機(jī)制，Oracle數(shù)據(jù)庫(kù)還提供了UTL_HTTP包來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)對(duì)外部WEB服務(wù)器的主動(dòng)訪問(wèn)。利用這兩個(gè)特性，可以在數(shù)據(jù)發(fā)生變化的時(shí)候主動(dòng)地將變化內(nèi)容及時(shí)傳輸給外部業(yè)務(wù)服務(wù)器，再通過(guò)業(yè)務(wù)服務(wù)器發(fā)送給相應(yīng)的業(yè)務(wù)人員，以此避免外部業(yè)務(wù)系統(tǒng)主動(dòng)和頻繁讀取數(shù)據(jù)庫(kù)服務(wù)器，提高系統(tǒng)消息發(fā)送效率，降低系統(tǒng)查詢負(fù)擔(dān)。

存儲(chǔ)過(guò)程；觸發(fā)器；消息推送；Oracle；UTL_HTTP

0 引言

隨著高校信息化建設(shè)發(fā)展和個(gè)人智能終端越來(lái)越普及，移動(dòng)化學(xué)習(xí)和辦公逐漸成為趨勢(shì)，需要向師生用戶的移動(dòng)終端主動(dòng)推送消息或提醒的應(yīng)用和場(chǎng)景也越來(lái)越多。在以往的使用過(guò)程中，主動(dòng)推送的消息一般采用應(yīng)用內(nèi)推送的方式，在各個(gè)業(yè)務(wù)系統(tǒng)的APP端獨(dú)立推送，以保證推送的及時(shí)性。但是隨著高校信息化建設(shè)情況的發(fā)展，這種模式也顯現(xiàn)出了一些弊端，主要是需要推送消息的 APP會(huì)越來(lái)越多，用戶安裝起來(lái)比較復(fù)雜和混亂，也不符合統(tǒng)一門(mén)戶和入口的高校信息化移動(dòng)建設(shè)要求。

現(xiàn)在很多高校比較常用的是將一些應(yīng)用和消息推送功能統(tǒng)一集成到微信公眾平臺(tái)或者QQ公眾平臺(tái)上，通過(guò)這些公共平臺(tái)進(jìn)行校園信息化功能的消息推送和用戶交互。使用這樣的第三方獨(dú)立平臺(tái)來(lái)進(jìn)行消息推送在解決了老問(wèn)題的時(shí)候又帶來(lái)了新問(wèn)題，由于校內(nèi)的信息化系統(tǒng)消息接口并不統(tǒng)一，致使第三方公共平臺(tái)的后臺(tái)服務(wù)器很多情況下不得不對(duì)各個(gè)系統(tǒng)消息接口進(jìn)行定時(shí)輪詢，以確定是否有新消息和發(fā)送目標(biāo)。而每個(gè)系統(tǒng)需要推送的消息、目標(biāo)用戶往往非常多，消息也往往不是內(nèi)容完全相同的群發(fā)消息。在這種情況下對(duì)每個(gè)業(yè)務(wù)系統(tǒng)的輪詢將消耗后臺(tái)服務(wù)器和業(yè)務(wù)系統(tǒng)服務(wù)器太多的資源，給整個(gè)校園信息化系統(tǒng)帶來(lái)很大的負(fù)擔(dān)。經(jīng)過(guò)不斷探索，我們發(fā)現(xiàn)采用數(shù)據(jù)庫(kù)自帶的存儲(chǔ)過(guò)程和高級(jí)接口，并結(jié)合通用HTTP協(xié)議來(lái)實(shí)現(xiàn)消息的主動(dòng)推送，在用來(lái)替代輪詢過(guò)程和減輕系統(tǒng)負(fù)擔(dān)上有著很好的效果。

1 存儲(chǔ)過(guò)程和觸發(fā)器

存儲(chǔ)過(guò)程是一種重要的數(shù)據(jù)庫(kù)對(duì)象，一般由SQL語(yǔ)句組合而成，具有在存儲(chǔ)過(guò)程中聲明和設(shè)置的SQL變量、實(shí)現(xiàn)流程控制、處理異常，能夠?qū)?shù)據(jù)進(jìn)行更新、讀取和刪除操作并返回結(jié)果集的功能[1]。一般來(lái)說(shuō)，SQL語(yǔ)句每次提交執(zhí)行一次就需要編譯一次，而存儲(chǔ)過(guò)程在創(chuàng)造時(shí)經(jīng)過(guò)一次編譯后，可以將編譯結(jié)果保存在數(shù)據(jù)庫(kù)中以便反復(fù)調(diào)用，因此具有較高的執(zhí)行效率。同時(shí)，存儲(chǔ)過(guò)程還可以降低整個(gè)應(yīng)用程序的復(fù)雜性，具有可移植性、重用性、安全性和伸縮性等特性。因此，使用存儲(chǔ)過(guò)程操作數(shù)據(jù)庫(kù)可以有效地提高數(shù)據(jù)

庫(kù)應(yīng)用的實(shí)現(xiàn)效率[2]。

觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，顧名思義，它是由某個(gè)數(shù)據(jù)庫(kù)事件的發(fā)生而被自動(dòng)隱式調(diào)用的一種存儲(chǔ)過(guò)程。除了在一些數(shù)據(jù)庫(kù)系統(tǒng)事件里觸發(fā)的系統(tǒng)觸發(fā)器，數(shù)據(jù)庫(kù)系統(tǒng)中最常用的是DML（Data Manipulation Language 數(shù)據(jù)操控語(yǔ)言）觸發(fā)器，在我們的消息主動(dòng)推送系統(tǒng)實(shí)現(xiàn)中采用的也是DML觸發(fā)器。觸發(fā)器主要由觸發(fā)對(duì)象、觸發(fā)事件、觸發(fā)事件和觸發(fā)頻率等等元素組成，細(xì)節(jié)如圖1所示：

圖1 觸發(fā)器組成

我們可以這樣簡(jiǎn)要地理解觸發(fā)器工作過(guò)程：在數(shù)據(jù)庫(kù)中編譯成功并保存的觸發(fā)器，會(huì)一直監(jiān)測(cè)著其設(shè)置的觸發(fā)對(duì)象（某個(gè)表、視圖或其它數(shù)據(jù)庫(kù)對(duì)象），當(dāng)在該對(duì)象上發(fā)生指定的觸發(fā)事件（數(shù)據(jù)插入、 更新、刪除或其它數(shù)據(jù)庫(kù)事務(wù)）時(shí)，將在該觸發(fā)事件被執(zhí)行前或執(zhí)行后（由觸發(fā)時(shí)機(jī)指定），以指定的觸發(fā)頻率（語(yǔ)句級(jí)：每句SQL指令調(diào)用一次；行級(jí)：每影響一行數(shù)據(jù)調(diào)用一次）來(lái)執(zhí)行觸發(fā)器中所設(shè)置的一系列指令操作。

2 系統(tǒng)總體設(shè)計(jì)

總體設(shè)計(jì)如圖2所示：

圖2 系統(tǒng)總體設(shè)計(jì)

從上所述我們可以看出，觸發(fā)器可以由一個(gè)數(shù)據(jù)庫(kù)事件啟動(dòng)，在某個(gè)具體的數(shù)據(jù)庫(kù)事件發(fā)生時(shí)自動(dòng)后臺(tái)運(yùn)行。結(jié)合我們的需求，當(dāng)我們需要主動(dòng)提醒的數(shù)據(jù)每次插入到數(shù)據(jù)表中的同時(shí)，我們都可以利用觸發(fā)器在數(shù)據(jù)庫(kù)中觸發(fā)一系列指定操作的運(yùn)行，現(xiàn)在的問(wèn)題就是如何使這一系列觸發(fā)操作能傳遞到數(shù)據(jù)庫(kù)外部的其它服務(wù)器中。以O(shè)racle數(shù)據(jù)庫(kù)軟件為例，其提供了 UTL_HTTP包[3]，用以實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)對(duì)外部WEB服務(wù)器的HTTP訪問(wèn)與互聯(lián)。

編寫(xiě)一個(gè)行觸發(fā)器監(jiān)視通知提醒數(shù)據(jù)表，當(dāng)每有一行新通知插入到該表的時(shí)候，觸發(fā)器被觸發(fā)并調(diào)用預(yù)先編譯好的存儲(chǔ)過(guò)程，使用UTL_HTTP包將具體消息發(fā)送到外部WEB服務(wù)器，外部服務(wù)器接收到消息后再對(duì)用戶進(jìn)行相應(yīng)的消息具體推送?？梢钥闯?，在如此設(shè)計(jì)思路充分利用了數(shù)據(jù)庫(kù)的觸發(fā)器機(jī)制，外部服務(wù)器再不需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行定時(shí)輪詢，在大大降低了雙邊系統(tǒng)的負(fù)載的同時(shí)，還消除了輪詢時(shí)間間隔，使消息推送更加的及時(shí)。

3 系統(tǒng)實(shí)現(xiàn)

在這里我們以實(shí)現(xiàn)將校園門(mén)戶中的待辦事項(xiàng)提醒推送到微信企業(yè)號(hào)的通知消息中為例，介紹整個(gè)流程的具體實(shí)現(xiàn)方法。假設(shè)所有用戶的新增待辦事項(xiàng)都被業(yè)務(wù)系統(tǒng)插入到數(shù)字校園公共數(shù)據(jù)庫(kù)用戶CORE_iUPC的TODO_INFO表中，我們需要通過(guò)行觸發(fā)器調(diào)用一段存儲(chǔ)過(guò)程，將該插入數(shù)據(jù)的具體內(nèi)容（代辦人員和待辦事項(xiàng)）通過(guò)HTTP協(xié)議主動(dòng)推送到微信企業(yè)號(hào)后臺(tái)服務(wù)器。本系統(tǒng)示例中的數(shù)據(jù)庫(kù)軟件為Oracle 11g RAC，存儲(chǔ)過(guò)程和觸發(fā)器[4]采用PL/SQL編寫(xiě)[5]，HTTP端代碼采用C#編寫(xiě)，使用IIS7.0發(fā)布WEB服務(wù)。

3.1 觸發(fā)器設(shè)計(jì)觸發(fā)器的PL/SQL的代碼如下，具體含義見(jiàn)注釋?zhuān)?/p>

3.2 存儲(chǔ)過(guò)程設(shè)計(jì)

存儲(chǔ)過(guò)程SendNotice的代碼如下，該存儲(chǔ)過(guò)程的重點(diǎn)是在調(diào)用oracle所提供的utl_http包來(lái)訪問(wèn)HTTP服務(wù)，賦予該存儲(chǔ)過(guò)程HTTP Client的角色和功能，具體含義見(jiàn)注釋?zhuān)?/p>

在這里為了簡(jiǎn)要演示UTL_HTTP的調(diào)用方法，我們使用其來(lái)調(diào)用HTTP_GET方法來(lái)向外部服務(wù)器發(fā)送信息，將相關(guān)變量組合成GET頁(yè)面的URL參數(shù)部分進(jìn)行發(fā)送，這樣的實(shí)現(xiàn)方法比較簡(jiǎn)單易懂。不過(guò)在參數(shù)較多或者參數(shù)比較復(fù)雜的情況下，利用URL傳遞參數(shù)的方法可能不太適用，我們還是推薦使用HTTP_POST方法來(lái)發(fā)送數(shù)據(jù)，將所有參數(shù)打包成一個(gè)XML或者JSON格式的數(shù)據(jù)，通過(guò)POST方法把數(shù)據(jù)發(fā)送到對(duì)方服務(wù)器。POST方法對(duì)所傳參數(shù)的長(zhǎng)度和格式一般沒(méi)有限制，也基本不會(huì)有特殊字符沖突處理問(wèn)題。

3.3 HTTP接收消息和對(duì)外推送

為了便于接收從數(shù)據(jù)庫(kù)推送來(lái)的消息并同時(shí)往微信企業(yè)號(hào)推送，我們直接在微信企業(yè)號(hào)的后臺(tái)創(chuàng)建了上述UTL_HTTP包所指定的接受推送頁(yè)面sendnotice.aspx，在接收到參數(shù)后調(diào)用微信企業(yè)號(hào)的后臺(tái)消息發(fā)送功能，往指定人員的微信推送相應(yīng)通知信息。后臺(tái)Web服務(wù)器由IIS7.0搭建，代碼用C#編寫(xiě)，在Page_Load時(shí)運(yùn)行的有關(guān)代碼片段如下：

其中往微信企業(yè)號(hào)內(nèi)用戶發(fā)送通知消息的iUPC_Notice類(lèi)具體實(shí)現(xiàn)的是與微信企業(yè)號(hào)公眾平臺(tái)所對(duì)接的相關(guān)標(biāo)準(zhǔn)[6]，具體內(nèi)容參見(jiàn)騰訊公司發(fā)布的企業(yè)號(hào)使用文檔，該文檔描述了后臺(tái)服務(wù)器與微信企業(yè)號(hào)之間的接口規(guī)范和調(diào)用流程等等細(xì)節(jié)，在此不再累述。

4 Oracle 11 g相關(guān)權(quán)限和安全設(shè)置

從數(shù)據(jù)安全角度來(lái)說(shuō)，在數(shù)據(jù)庫(kù)軟件中主動(dòng)調(diào)用HTTP協(xié)議訪問(wèn)外部站點(diǎn)是一個(gè)高危動(dòng)作，因此Oracle對(duì)內(nèi)部用戶是否能發(fā)起對(duì)外HTTP連接做了嚴(yán)格的限制。在以前的版本是控制某用戶對(duì) UTL相關(guān)網(wǎng)絡(luò)訪問(wèn)包（UTL_HTTP、UTL_MAIL、UTL_TCP等等）的調(diào)用權(quán)限，而在Oracle 11g版本中進(jìn)行了更高細(xì)粒度的ACL(Access Control List，訪問(wèn)控制列表)限制。在PL/SQL下配置用戶“test”訪問(wèn)某個(gè)IP地址80端口的ACL的具體方法如下：

DBMS_NETWORK_ACL_ADMIN包用于創(chuàng)建、管理、修改和刪除ACL，其中的create_acl方法用于創(chuàng)建一條ACL，如果這條 ACL還需要指定給其他用戶使用，還可以利用add_privilege 方法進(jìn)行添加用戶，assign_acl方法用于對(duì)某條ACL及其用戶分配網(wǎng)絡(luò)訪問(wèn)權(quán)限，指定其可以訪問(wèn)的主機(jī)和端口，其中host變量可以是IP地址或者域名，也可以使用通配符匹配一段網(wǎng)段。同時(shí)也可以多次調(diào)用 assign_acl為同一個(gè)ACL和其所含用戶授權(quán)訪問(wèn)多個(gè)host主機(jī)和端口號(hào)。

5 總結(jié)

在本文中我們利用存儲(chǔ)過(guò)程和觸發(fā)器，在數(shù)據(jù)表發(fā)生變化時(shí)主動(dòng)調(diào)用UTL_HTTP包訪問(wèn)數(shù)據(jù)庫(kù)服務(wù)器外部的網(wǎng)站，并通過(guò)HTTP協(xié)議傳遞出相關(guān)內(nèi)容，使消息可以第一時(shí)間發(fā)送到微信客戶端，避免了輪詢過(guò)程對(duì)雙方系統(tǒng)的額外開(kāi)銷(xiāo)。該方法還是個(gè)非常適用于通過(guò) POST方法將格式數(shù)據(jù)發(fā)送到通信運(yùn)營(yíng)商所提供的短信發(fā)送接口，實(shí)現(xiàn)全自動(dòng)的短信提醒功能。除了 UTL_HTTP包外，Oracle 11g還提供了UTL_TCP、UTL_MAIL來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)對(duì)外的socket連接功能和對(duì)外發(fā)送郵件功能，同樣可以實(shí)現(xiàn)本文類(lèi)似的主動(dòng)推送功能。另外Microsoft SQL Server和其它主流數(shù)據(jù)庫(kù)軟件均具有類(lèi)似的存儲(chǔ)過(guò)程、觸發(fā)器和HTTP訪問(wèn)機(jī)制，有待大家參考和利用。

[1] 馬洪江，周相兵.基于存儲(chǔ)過(guò)程的科研績(jī)效統(tǒng)計(jì)分析系統(tǒng)的實(shí)現(xiàn)[J].計(jì)算機(jī)技術(shù)與發(fā)展, 2011,21(8)181-184.

[2] 郭紹忠，甄濤，賈琦.基于存儲(chǔ)過(guò)程的海量郵件數(shù)據(jù)挖掘[J].計(jì)算機(jī)工程, 2010，36(1)：40-42.

[3] Database PL/SQL Packages and Types Reference. Chapter 225 UTL_HTTP[EB/OL].[2016-03-02]，http：//docs.oracle. com/cd/E11882_01/appdev.112/e40758/u_http.htm#ARPL S070.

[4] 林樹(shù)澤, 盧芬, 柳冬青. Oracle DBA高可用、備份恢復(fù)與性能優(yōu)化[M].北京：清華大學(xué)出版社,2015.

[5] Michael McLaughli．Oracle Database 11g PL／SQL程序設(shè)計(jì) [M]．張?jiān)?，等譯．北京：清華大學(xué)出版社，2009.

[6] 微信公眾平臺(tái)企業(yè)號(hào)接口文檔[EB/OL].[2016-03-02] http：//qydev. weixin.qq.com/wiki/.

Implementation of Message Push System Based on the Stored Procedure and HTTP Protocol

Xia Lingyun, Gong Wentao
(Internet and Education Technology Center, China University of Petroleum (East China), Qingdao 266580, China)

Stored procedures and triggers in database are formerly used for data integrity checking and complex rule restriction, they also provide an actively operating mechanism when the data change in the database table. Oracle Database also provides UTL_HTTP package to achieve the access from database to external WEB server. Using these two characteristics, it can send the changing data to an external service server initiatively and promptly, and then sends the message to the appropriate user through the service server. Thereby avoiding external service systems avoid frequent reading the database server initiatively, it improves messaging system efficiency and reduces the burden on system queries

Stored procedure； Trigger； Message push； Oracle； UTL_HTTP

TP393

A

1007-757X(2016)11-0066-03

2016.07.06）

夏凌云（1980-）男，瀘州人，中國(guó)石油大學(xué)（華東），網(wǎng)絡(luò)及教育技術(shù)中心，碩士研究生，工程師，研究方向：互聯(lián)網(wǎng)技術(shù)、計(jì)算機(jī)軟

硬件技術(shù)和物聯(lián)網(wǎng)技術(shù)，青島 266580

龔文濤（1984-）男，潛江人，中國(guó)石油大學(xué)（華東），網(wǎng)絡(luò)及教育技術(shù)中心，碩士，工程師，研究方向：網(wǎng)絡(luò)信息安全，青島 266580