chrisonoracle

A blog about Application Express and Oracle related topics.

Showing diagrams in Apex, with Graphviz and OHS

This Apex forum thread reminded me that I mentioned how to integrate Graphviz in Apex to somebody at last year’s OOW. Since the next OOW is right around the corner, it’s high time to finally publish how this can be done.

Setting up an Output Filter in Oracle HTTP Server

The Apache-based OHS allows to specify output filters, to post-process data. Such filters can be written in shell script or Perl. My example uses the latter.

In dads.conf, I entered

LoadModule perl_module “${ORACLE_HOME}/ohs/modules/mod_perl.so”
PerlSwitches -I/home/cneumuel/perl
PerlModule CN::DotToSvgFilter

at the top level to load mod_perl, add my local Perl directory to Perl’s path and register my filter module. Then I added

PerlOutputFilterHandler CN::DotToSvgFilter

within the DAD’s <location> tags. This line tells OHS that all output from the DAD has to be post processed by my module.

In /home/cneumuel/perl/CN, I created the file DotToSvgFilter.pm:

package CN::DotToSvgFilter;
use strict;
use warnings;
 
use IPC::Open2;
 
use base qw(Apache2::Filter);
use Apache2::RequestRec ();
use APR::Table ();
use Apache2::Const -compile => qw(OK);
 
use constant BUFF_LEN => 4096;
 
sub handler : FilterRequestHandler {
    my $f = shift;
    my $r = $f->r;
 
    if ($r->content_type() =~ /^text\/dot;/) {
        #
        # convert dot to svg
        #
 
        my $pid = open2(*Reader, *Writer, “/home/cneumuel/usr/bin/dot -Tsvg”) or die “can not run dot”;
 
        while ($f->read(my $buffer, BUFF_LEN)) {
            print Writer $buffer;
        }
        close Writer;
        my $got = do { local $/; <Reader> };
        close Reader;
        waitpid $pid, 0;
        $r->content_type(‘image/svg+xml’);
        $r->headers_out->set(‘Content-Length’, length($got));
        $f->print($got);
    } else {
        #
        # pass through
        #
        while ($f->read(my $buffer, BUFF_LEN)) {
            $f->print($buffer);
        }
    }
 
    return Apache2::Const::OK;
}
1;
This script checks if the content type is “text/dot”. That’s not an official mime type, we just use that internally to tell the filter when content has to be post-processed. If we get “text/dot”, it filters the input with /home/cneumuel/usr/bin/dot, which is called with a parameter that specifies svg as the output format. Otherwise, the script just passes the input data through.

After a restart of OHS, the filter is active.

Creating a sample Apex application that uses the filter

I created a new application with a blank page. On that page, I defined a HTML region “Org Chart” with the following region source:

< object
    data=”f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:APPLICATION_PROCESS%3dorg_chart_graph”
    type=”image/svg+xml” >
< /object >

I also created an Ajax callback on the page, called “org_chart_graph”:

– set mime type – converted by apache
sys.owa_util.mime_header (
    ccontent_type => ‘text/dot’,
    bclose_header => true );
– open graph
sys.htp.p(‘digraph orgchart {‘);
– empoyee nodes
for i in ( select empno,
                  ename
             from emp )
loop
    sys.htp.p(‘e’||i.empno||
              ‘ [label="'||
              replace(i.ename, '"', '\"')||
              '"];’);
end loop;
– edges, via mgr
for i in ( select empno,
                  mgr
             from emp
            where mgr is not null )
loop
    sys.htp.p(‘e’||i.empno||’ -> e’||i.mgr||’;’);
end loop;
– close graph
sys.htp.p(‘}’);
Finished! Here is the result:
Image
Note that this is just a simple graph, Graphviz has many advanced options (clickable nodes and edges, custom shapes, etc.) and different layout algorithms that are just a few lines of code away. The gallery has some quite impressive examples.
About these ads

2 responses to “Showing diagrams in Apex, with Graphviz and OHS

  1. Jason September 26, 2012 at 19:46

    Google charts maybe simpler and includes directed graphs.

    Here is an example region source value for generating a directed graph for PL/SQL package dependencies using Google charts.

    declare
    v_src varchar2(32767) := ‘ 0 or instr(referenced_name,upper(:p59_search)) > 0)
    and type = ‘PACKAGE BODY’;
    begin
    for v_relation in get_relations loop
    v_digraph := v_digraph||v_relation.child||’->’||v_relation.parent||’;’;
    end loop;
    v_digraph := v_digraph||’}’;
    v_src := v_src||v_digraph||’” alt=”either too many dependencies or none found”>’;
    htp.p(v_src);
    exception
    when others then
    :p59_search := null;
    htp.p(sqlerrm);
    end;

  2. chrisonoracle September 26, 2012 at 19:56

    Indeed, Google has (experimental and limited) Graphviz support: https://developers.google.com/chart/image/docs/gallery/graphviz

    Didn’t know that, thanks for the information!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.