Oracle APEX / Node Websocket Notification Bundle
Table of Contents
Purpose of this software bundle was to enable all APEX developers to use modern and state of the art web features like Node, Websockets and nice looking notifications in their applications.
This bundle includes all these features and simultaneously is designed to use all of them out of the box. This means:
Ready to go Node.js websocket server especially for notifications using socket.io
A native PL/SQL package to send all kinds of different messages/notifications using APEX_WEB_SERVICE
APEX plugins for all kind of events that are needed by the notification system:
Developers don´t need to be experts in Javascript or JQuery and stuff like that (But as always, it´s not a bad skill!;) ). APEX & PL/SQL Know-How and a good knowledge of using Dynamic Actions should be enough to implement this notification bundle in your applications...
A demo application is available under https://apex.danielh.de/ords/f?p=WSNOTIFY
And of course you find a APEX export (demo_app.sql) of it in ../apex/ folder. To use it just import the app and then go through the installation steps below. Under Shared Components --> Edit Application Definition --> Substitutions Strings, set
The demo includes all plugins and shows the most common preferences and possibilities.
It is required to have a up and running Node.js installation on your server. Either install it using a package manager, or download the latest version from Nodejs homepage...for example:
apt-get install nodejs
apt-get install npm
brew install nodejs
npm is the package manager for Node applications. npm is used to install all required packages by the Node Websocket Notification Server...
cd /path/to/node-notify-server
npm install
npm start
This should be everything to have the Notification Server up und running. To check that, you can point your web browser to http://[host-ip-of-server]:8080
There you should get a overview of all supported services by the Notification Server.
This helper pages are supported by the server:
You can change the default behavior of the server by editing the JSON config file ../node/node-notify-server/prefs.json
{
"server": {
"ip": "0.0.0.0", // listener ip address 0.0.0.0 for all interfaces
"port": "8080", // listener port
"authUser": "", // User for HTTP basic auth, empty means no user auth (only REST-Interface)
"authPwd": "", // Password for HTTP basic auth, empty means no user auth (only REST-Interface)
"sslKeyPath": "", // FOR SSL: path to ssl key file (./certs/key.pem), empty means no SSL/HTTPS
"sslCertPath": "", // FOR SSL: path to ssl certificate file (./certs/cert.pem), empty means no SSL/HTTPS
"logging": true // logging to console on or off, for prod disable logging
},
"socket": {
"private": true, // activate private websocket room/namespace of server
"public": true, // activate public websocket room/namespace of server
"authToken":"please-change-me" // authentication token, client should have the same to connect with websocket, please change it to some random string
}
}
After changing one of these settings, please restart the Node Notification Server.
SSL Support:
All notifications are sent through web service requests. Therefore a ACL is needed, so you are allowed to connect to this host. Here is a example script, configure it to reflect your environment...
DECLARE
--
l_filename VARCHAR2(30) := 'ws-notify-host.xml';
l_host_or_ip VARCHAR2(50) := '<YOUR-NODE-WEBSOCKET-HOST>'
l_port NUMBER := 8080;
l_schema VARCHAR2(20) := '<SCHEMA>';
--
BEGIN
--
BEGIN
dbms_network_acl_admin.drop_acl(acl => l_filename);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
dbms_network_acl_admin.create_acl(acl => l_filename,
description => 'All requests to Node Websocket Server',
principal => l_schema,
is_grant => TRUE,
privilege => 'connect');
--
dbms_network_acl_admin.add_privilege(acl => l_filename,
principal => l_schema,
is_grant => TRUE,
privilege => 'resolve');
--
dbms_network_acl_admin.assign_acl(acl => l_filename,
host => l_host_or_ip,
lower_port => l_port);
END;
/
If you configured the Node Notification Server with SSL/HTTPS support, a Oracle SSL Wallet is needed by the database to communicate with the REST-Interface for sending notifications.
To manually create a wallet, either use Oracle Wallet Manager or create the wallet with openssl utils like:
openssl pkcs12 -export -in cert.pem -out ewallet.p12 -nokeys
The APEX part contains 3 plugins, you can find it in ../apex/plugins folder. Just import these 3 files to your application and you are ready to go.
For a detailed description of the plugins, read further under "Usage Section" or import the demo app sql file to your workspace.
As mentioned in the installation steps, the node notification server component consists of 3 areas:
Sending messages and notifications to users which are connected to the websocket interface.
Connecting and authenticating users against the node server and still more to receive live messages on client browser from server part. There exists 2 rooms/namespaces which users can subscribe to:
private - For single user messages to all instances of one user (e.g. one user is logged in with 3 browsers)
public - For single user messages to all instances of one user AND broadcasting messages to all connected clients
Helper pages
Helper pages to get informations about services, status of server and a test client page to test some websocket interactions.
General settings of the node server like IP, port, authentication, SSL support and active websocket rooms can be configured with ../node/node-notify-server/prefs.json file as mentioned above.
The REST-Service is designed to send messages to connected websocket users. Base-URL scheme looks like this:
Type: GET
http://[host-ip-of-server]:[port]/notifyuser
URL-Parameter
HTTP Header-Variables
A demo call using curl looks like this:
curl -H "notify-title: Test Title Text" -H "notify-message: Test Message Text" "http://[host-ip-of-server]:[port]/notifyuser?userid=daniel&room=private&type=info&optparam=myoptionalinfo123"
The PL/SQL API consists of one package ws_notify_api and includes many procedures to send any kind of possible notifications over the REST-Interface. It can be used to send notifications to users via PL/SQL or inside of APEX. All web service requests are based on APEX package APEX_WEB_SERVICE.
Procedure: do_rest_notify_user
Purpose: Send Websocket Notifications over REST to connected users (General sending procedure with all parameters)
Parameter:
Procedure: do_notify_user_private_info
Purpose: Send Websocket Notification to User / Room: Private / Type: Info
Parameter:
Procedure: do_notify_user_private_success
Purpose: Send Websocket Notification to User / Room: Private / Type: Success
Parameter:
Procedure: do_notify_user_private_warn
Purpose: Send Websocket Notification to User / Room: Private / Type: Warn
Parameter:
Procedure: do_notify_user_private_error
Purpose: Send Websocket Notification to User / Room: Private / Type: Error
Parameter:
Procedure: do_notify_user_public_info
Purpose: Send Websocket Notification to User / Room: Public / Type: Info
Parameter:
Procedure: do_notify_user_public_success
Purpose: Send Websocket Notification to User / Room: Public / Type: Success
Parameter:
Procedure: do_notify_user_public_warn
Purpose: Send Websocket Notification to User / Room: Public / Type: Warn
Parameter:
Procedure: do_notify_user_public_error
Purpose: Send Websocket Notification to User / Room: Public / Type: Error
Parameter:
Procedure: do_notify_all_public_info
Purpose: Send Websocket Notification to all Users / Room: Public / Type: Info
Parameter:
Procedure: do_notify_all_public_success
Purpose: Send Websocket Notification to all Users / Room: Public / Type: Success
Parameter:
Procedure: do_notify_all_public_warn
Purpose: Send Websocket Notification to all Users / Room: Public / Type: Warn
Parameter:
Procedure: do_notify_all_public_error
Purpose: Send Websocket Notification to all Users / Room: Public / Type: Error
Parameter:
A demo call could look like this:
BEGIN
ws_notify_api.do_rest_notify_user(i_userid => 'daniel',
i_room => 'private',
i_type => 'info',
i_title => 'My test title',
i_message => 'My test message content...',
i_optparam => 'myoptionalinfo123');
END;
As already mentioned above, the APEX part contains 3 plugins to cover all functionalities from initialization of a websocket connection, sending notifications to other connected users to show incoming notifications. All 3 plugin files are located under ../apex/plugins folder.
Plugin File: dynamic_action_plugin_de_danielh_initwsnotifyconnection.sql
Purpose: Initialize a connection to the websocket server, for general usage over all pages of your APEX application this plugin should be located on Global Page 0 (Zero)
Plugin Attributes:
Plugin Events:
Plugin File: dynamic_action_plugin_de_danielh_sendwsnotify.sql
Purpose: Send websocket notifications to other connected users or to all connected users
Plugin Attributes:
Plugin Events:
Example SQL Source Query:
SELECT 'MYUSER' AS user_id,
'private' AS room, -- private, public
'info' AS notify_type, -- info, success, warn, error
'Test Title' AS title,
'My Test Message Content...' AS message,
'123:abc' AS optional_parameter
FROM dual
Plugin File: dynamic_action_plugin_de_danielh_showwsnotify.sql
Purpose: Show notifications for incoming websocket message events. Notifications UI based on AlertifyJS
Plugin Attributes:
All other parameters of an notification object (title, message, type (info, success, warn, error), etc.) are automatically fetched from the websocket message event.
This software is under MIT License.