Lesson 5: Configuring the Notifier

This lesson assumes you have completed all preceding lessons. See Lesson 1: Setting up the consolidated database.

In this lesson, you configure three Notifier events to define how the Notifier creates push requests, transmits the requests to MobiLink Listeners, and deletes expired requests.

The Notifier detects changes in the consolidated database and creates push requests using the begin_poll event. In this case, the begin_poll script populates the PushRequest table if changes occur in the Dealer table and when a remote database is not up-to-date.

The request_cursor script fetches push requests. Each push request determines what information is sent in the message, and which remote databases receive the information.

The request_delete Notifier event specifies cleanup operations. Using this script, the Notifier can automatically remove implicitly dropped and expired requests.

Create and configure a new Notifier

In the left pane of Sybase Central under MobiLink 12, expand sis_cons_project, Consolidated Databases and then sis_cons.
Right-click Notification, and then click New » Notifier.
In the What Do You Want To Name The New notifier field, type CarDealerNotifier.
Click Finish.

Enter the begin_poll event script.

In the right pane, select CarDealerNotifier, and then click File » Properties.
Click the Events tab.
Choose begin_poll from the Events list.

Type the following SQL statements in the provided text field:

--
-- Insert the last consolidated database 
-- modification date into @last_modified 
--
DECLARE @last_modified timestamp;
SELECT MAX(last_modified) INTO @last_modified FROM Dealer;

--
-- Delete processed requests if the mluser is up-to-date
--
DELETE FROM PushRequest
    FROM PushRequest AS p, ml_user AS u, ml_subscription AS s
    WHERE p.status = 'processed'
        AND u.name = p.mluser
        AND u.user_id = s.user_id
        AND @last_modified <= GREATER(s.last_upload_time, s.last_download_time);

--
-- Insert new requests when a device is not up-to-date
--
INSERT INTO PushRequest(mluser, subject, content) 
SELECT u.name, 'sync', 'ignored'
    FROM ml_user as u, ml_subscription as s
    WHERE u.name IN (SELECT name FROM ml_listening WHERE listening = 'y')
        AND u.user_id = s.user_id
        AND @last_modified > greater(s.last_upload_time, s.last_download_time)
        AND u.name NOT LIKE '%-dblsn'
        AND NOT EXISTS(SELECT * FROM PushRequest 
            WHERE PushRequest.mluser = u.name
                AND PushRequest.subject = 'sync')

In the first major section of the begin_poll script, processed requests from the PushRequest table are eliminated if a device is up to date:

@last_modified <= GREATER(s.last_upload_time, s.last_download_time)

@last_modified is the maximum modification date in the consolidated database Dealer table. The expression greater( s.last_upload_time, s.last_download_time) represents the last synchronization time for a remote database.

You can also delete push requests directly using the request_delete event. However, the begin_poll event, in this case, ensures that expired or implicitly dropped requests are not eliminated before a remote database synchronizes.

The next section of code checks for changes in the last_modified column of the Dealer table and issues push requests for all active MobiLink Listeners (listed in the ml_listening table) that are not up to date:

@last_modified > GREATER(s.last_upload_time, s.last_download_time)

When populating the PushRequest table, the begin_poll script sets the subject to 'sync'.

Enter the request_cursor script.

Click request_cursor from the Events list.

Type the following SQL statement in the provided text field:

SELECT
    p.req_id,
    'Default-DeviceTracker',
    p.subject,
    p.content,
    p.mluser,
    p.resend_interval,
    p.time_to_live
    FROM PushRequest AS p

The PushRequest table supplies rows to the request_cursor script.

The order and values in the request_cursor result set is significant. The second parameter, for example, defines the default gateway Default-DeviceTracker. A device tracking gateway keeps track of how to reach users and automatically selects UDP or SMTP to connect to remote devices.

Enter the request_delete script.

Click request_delete from the Events list.

Type the following SQL statement in the provided text field:

UPDATE PushRequest SET status='processed' WHERE req_id = ?

Instead of deleting the row, this request_delete script updates the status of a row in the PushRequest table to 'processed'.

Click OK to save the Notifier events.
Proceed to Lesson 6: Configuring gateways and carriers.

Lesson 5: Configuring the Notifier

Create and configure a new Notifier

See also