Using websockets in APEX for automatic refresh

I visited the OGH APEX day this monday in Zeist. Not only gave Patrick Wolf some insight in the possible upcoming new features of 4.2, but there were also a bunch of inspiring speakers that gave me some new ideas. One of the speakers Roel Hartman gave a presentation of some HTML5 features. He used a notification-service to sent messages to the mobile devices of some of the members of the audience.

That gave me the following.

What would it take to let the database push a refresh on the screen.

Normally you have to implement a timer mechanism that uses resources to poll the database in some frequency, say 5 seconds, to keep the report up to data.
But what if we could use websockets to let the database push a refresh after an insert.

And it works! In its simplest form it’s so easy it almost made me cry.

What I did was the following:

1. Go to Beaconpush and sign up.

After registration you will get an API-key and a Secret Key

2. Create a page with an interactive report that you want to be refreshed.
In the page footer add the following:

    Beacon.connect('*****', ['mychannel']);
    Beacon.listen(function (data) {
      if (data.message=='REFRESH') {
        gReport.pull();
      }
      else {
        alert(data.message);
      }
    });

with ***** your API-key.
Here I use the default channel. Now when we post a JSON-object

{ "message": "REFRESH" }

the message-attribute of the data-object will refresh the report. This will make your page listening to its channel in the beaconpush-server.

3. Create a simple package

create or replace package body beacon
is
/*Run these ACL-Setting as SYS when you are in 11G
begin
  dbms_network_acl_admin.create_acl
    ( acl         => 'beaconConnectList.xml'
    , description => 'Beaconpush ACL'
    , principal   => 'APEX_040100'
    , is_grant    => true
    , privilege   => 'connect'
    , start_date  => null
    , end_date    => null
    );
  commit;
end;

begin
  dbms_network_acl_admin.assign_acl
    ( acl        => 'beaconConnectList.xml'
    , host       => 'api.beaconpush.com'
    , lower_port => null
   , upper_port  => null
   );
  commit;
end;
*/

procedure push
  ( p_text in varchar2
  )
is
  l_clob clob;
begin
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST
  ( p_url => 'http://api.beaconpush.com/1.0.0/*****/channels/mychannel'
  , p_http_method => 'POST'
  , p_body =>'{ "message": "'||p_text||'" }'
  );

end;

end;

We are almost there. Because in its simplest form I don’t want to use the secret key I disabled it in the account-settings on beaconpush.com:

Disable secret key usage

That’s it. You can test it by executing a simple

begin
  beacon.push('REFRESH');
end ;

from your favorite IDE like SQL*Plus to test that the page has automagically refreshed.
Then put this code in some after insert trigger and you are done.
In a production system there are of course still a few things to think of
– using the secret key
– realise how many messages you will push and read the conditions of beaconpush very carefully.

Maybe you don’t want to use beaconpush. Maybe you want to write your own Comet server.

Compress Components with gzip in Apex

In my former post I addressed the YSlow analysis that I had to enable Expires Headers to my Apex application. In the same analysis YSlow reported a F because I hadn’t enabled compression in the web server.

I created a moddeflate.conf file which I included in my configuration. This file contains the following:

<IfModule mod_deflate.c>
SetOutputFilter DEFLATE
AddOutputFilterByType DEFLATE text/plain
AddOutputFilterByType DEFLATE text/xml
AddOutputFilterByType DEFLATE application/xhtml+xml
AddOutputFilterByType DEFLATE text/css
AddOutputFilterByType DEFLATE text/javascript
AddOutputFilterByType DEFLATE text/x-js
AddOutputFilterByType DEFLATE application/xml
AddOutputFilterByType DEFLATE image/svg+xml
AddOutputFilterByType DEFLATE application/rss+xml
AddOutputFilterByType DEFLATE application/atom+xml
AddOutputFilterByType DEFLATE application/x-javascript
AddOutputFilterByType DEFLATE text/html
SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.(?:exe|t?gz|zip|bz2|sit|rar)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.(?:pdf|doc?x|ppt?x|xls?x)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.avi$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.mov$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.mp3$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \.mp4$ no-gzip dont-vary
</IfModule>

Notice de conditional configuration.

You have to make sure that the proper module is loaded.
In my OHS-configuration that’s:
LoadModule deflate_module “${ORACLE_HOME}/ohs/modules/mod_deflate.so”

Add Expires Headers to your APEX Application

Probably the best book I have ever read about apex is Expert Oracle Application Express by several knowledgeable authors. You should buy it if you don’t already did.

The first chapter from John Edward Scott is about some of the well known architectures of an Apex implementation and its configuration. This chapter made me use the YSLOW Add-on for Firefox/firebug on our website and it came up with this following picture:
yslow-before picture

Not a pretty picture indeed.

First I wanted to get ride of the expires headers error.

I created a modexpires.conf Apache configuration file with the following content and included it in our HTTPD-configuration.

ExpiresActive On
ExpiresByType image/gif “access plus 15 days”
ExpiresByType image/jpeg “access plus 15 days”
ExpiresByType image/jpg “access plus 15 days”
ExpiresByType image/png “access plus 15 days”
ExpiresByType image/x-icon “access plus 15 days”
ExpiresByType application/x-javascript “access plus 7 days”
ExpiresByType text/javascript “access plus 7 days”
ExpiresByType text/css “access plus 7 days”
FileETag none

The morning after picture on Expires Headers is much prettier:
Yslow expires headers after picture

Next step will be compression. But that’s another blog.

Like I said. Read the book!

Update:

Some older OHS/Apache versions don’t seem to support the alternate syntax used above. Instead you should use the <code>seconds notation.

ExpiresActive On
ExpiresByType image/gif A1209600
ExpiresByType image/jpeg A1209600
ExpiresByType image/jpg A1209600
ExpiresByType image/png A1209600
ExpiresByType image/x-icon A1209600
ExpiresByType application/x-javascript A604800
ExpiresByType text/javascript A604800
ExpiresByType text/css A604800
FileETag none

Using a refcursor in Apex

My client had a running portal, build in Delphi. Requests for data were implemented as calls to stored packaged functions returning refcursors to a soapserver. For obvious reasons my client wanted to renew the front end of the portal with Apex. The backend however the refcursors- had to be untouched.

I came up with the idea of using some of the XDB features of the database.

This simple example using EMP should give you some idea of what we did.

First, we create a package with a stored function returning a refcursor

create or replace package demo_ref
is

function give_employees
  return sys_refcursor;

end;
/

create or replace package body demo_ref
is

function give_employees
  return sys_refcursor
is
  l_return sys_refcursor;
begin
  open l_return
  for
    select empno, ename
    from   emp
  ;
  return l_return;
end;

end;/

In an apex-page we create an SQL-region using the following-like SQL:

select x.empno
,      x.ename 
from  xmltable ( '/ROWSET/ROW' 
                 passing xmltype(demo_ref.give_employees) 
                 columns empno  number(10) path 'EMPNO'
                 ,       ename  varchar2(100) path 'ENAME'  
               ) x

Of course you could also just test the SQL in your favorite IDE.