Skip to content
February 6, 2011 / Alex Nedoboi

Interactive SVG example on iPad (iPhone) via Oracle PL/SQL Server Pages


A couple of weeks ago we talked about Flash on Android via Oracle APEX. Today let’s check the competitor’s offer.

Unfortunately Apple products don’t support Flash. The good news however is that they do support SVG, as do most web browsers (IE7/8 is pluginable and IE9 is going to support it properly).

I first came across SVG in 2003 at some conference in Melbourne, it was presented by a geekish W3C guy. Don’t remember much detail now except that I absolutely loved the concept. It was so simple and at the same time so powerful, I couldn’t see any reason not to use it whenever there’s a need of constructing anything resembling a graph.

SVG was great, and it hasn’t changed much since then which only tells you that it was great from the beginning.

HTML5 will even support direct embedding of SVG (though I don’t really see the point).

Motivational Speech

One might say – well, there are plenty of packages/libraries/kits that can generate graphs, why would you want to mess with this SVG stuff yourself.

The explanation is simple. The simplest way of doing the job is the best way to do it. Simple.

Or, in other words, less code = less bugs (lots of people attribute those words to Tom Kyte, though I must say, Kernighan and Ritchie were big fans of this paradigm a couple of decades earlier).

Mocking up

So let’s start.

First we would need an HTML container page and an SVG dummy.

## html_wrapper.psp
<html>
<head>
<title>Follow the transparent rabbit</title>
</head>
<body style="background-color:black;">
Here it is
<br>
<embed src="svg_wrappee" width="900" height="500" type="image/svg+xml" />
<br>
</body>
</html>
## svg_wrappee.psp
<%!

   cw simple_integer := 900; /* canvas width and height */
   ch simple_integer := 500;

   gw simple_integer := 750; /* graph width and height */
   gh simple_integer := 400;

   mx simple_integer := 80; /* axis margins */
   my simple_integer := 50;

   xs simple_integer := 10; /* number of bands */
   ys simple_integer := 4;

   max_qty simple_integer := 80000; /* events */

   j simple_integer := 0;

%><%
   ## SVG mime
   owa_util.mime_header('image/svg+xml', false);
   owa_util.http_header_close;

%><?xml version="1.0" standalone="no" ?>
<!DOCTYPE svg PUBLIC 
   "-//W3C//DTD SVG 1.1//EN" 
   "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">
<svg
   width="<%= cw %>"
   height="<%= ch %>"
   version="1.1"
   xmlns="http://www.w3.org/2000/svg"
   style="background-color:white;">

<style>
.axis { stroke:black; stroke-width:2; }
.bands { stroke:silver; stroke-width:0.5; }
.labels { font-family:Arial; font-size:11; }
</style>

## draw x and y axis
<path d="M<%= mx*0.8 %>,<%= ch-my %> l<%= gw + mx*0.2 %>,0" class="axis" />
<path d="M<%= mx %>,<%= ch-my*0.8 %> l0,<%= -ch+my %>" class="axis" />

<%
## draw bands and print labels
   for i in 1..ys loop
      j := (ch-my) - (gh/ys)*i;
%>
<path d="M<%= mx %>,<%= j %> l<%= gw %>,0" class="bands" />
<text 
   x="<%= mx*0.2 %>" 
   y="<%= j %>" 
   class="labels"><%= to_char( (max_qty/ys)*i, '999,999,990' ) %></text>
<%
   end loop;
%>

</svg>

Compile the two files with loadpsp, and on your iPod/iPhone open Safari and navigate to your Oracle HTTP Server.

As you can see, we have an empty canvas with a couple of lines (the <path> tag) and labels (the <text> tag).

Most of the code above is just one big header, the real job done by the highlighted tags, four lines.

Data Source

It’s always hard to make something up. Well, maybe not that hard, but it’s much better to find something practical, something that could be used in the day to day life, not just as an example.

For the purpose of this article, we will graph up one of the most important Oracle database events – class slave wait. So important, it was at some point included in the AWR and statspack reporting.

The query is trivial, ignoring under 64ms waits –

select inst_id, wait_time_milli, wait_count
from gv$event_histogram t
where t.wait_time_milli between power(2,6) and power(2,15)
order by 1,2
;

Let’s just create a [global (if you wish)] temporary table, so the data is consistent every time we query it (which is not the case with v$/gv$ views).

create table temp_slave_wait
as
select inst_id as node, wait_time_milli as ms, wait_count as qty
...

So we have three series of data, one for each node -

SQL> select * from temp_slave_wait order by 1,2;

      NODE         MS        QTY
---------- ---------- ----------
         1         64      76577
         1        128      84508
         1        256       4256
         1        512      23850
         1       1024      23524
         1       2048      10003
         1       4096      12772
         1       8192      23876
         1      16384      15152
         1      32768      18185
         2         64      71716
         2        128      65321
         2        256      13780
         2        512      17504
         2       1024      12680
         2       2048      15129
         2       4096      18406
         2       8192       9109
         2      16384       8582
         2      32768      18323
         3         64     107862
         3        128      93494
         3        256      23420
         3        512      28968
         3       1024      10991
         3       2048      15183
         3       4096      22315
         3       8192      14199
         3      16384      25702
         3      32768      31565

30 rows selected.

Dots

The pace the screen resolution has been increasing at over the past few years is quite fast. Retina’s (fourth generation) resolution is 960 by 640, or something around those numbers.

So if we want the dots to be visible, we should make them circles or rectangles. Rounded rectangle shape would, to my personal taste, suit iPhone/iPad the most, as they are of the same shape themselves.

To draw a rectangle in SVG, you would use the <rect> tag.

Add the following line to the <style> section -

.dots { fill-opacity:0.2; stroke-width:1.2; }

Then add the following loop just before the closing </svg> tag -


   for n in (select distinct node from temp_slave_wait order by 1) 
   loop
      clr := (case n.node when 1 then 'magenta' 
                          when 2 then 'lime' 
                          when 3 then 'aqua' 
                          else 'black' end);
      k := 1;  /* or use rownum if you consider yourself a purist */ 

      for q in (select qty from temp_slave_wait where node = n.node order by ms) 
      loop
%>
<rect
   x="<%= mx + (gw/xs)*(k-1/2) - 4 %>" /* position */
   y="<%= (ch-my) - gh*(q.qty/max_qty) - 4 %>"
   rx="3" /* rounding */
   ry="3"
   width="8"
   height="8"
   style="fill:<%= clr %>; stroke:<%= clr %>;"
   class="dots"
   />
<%
         k := k + 1;
      end loop;
   end loop;

Click refresh in Safari. What do you see.

Zoom in a bit.

Connect them

Connecting the dots is just as simple.

One more style -

.lines { opacity:0.9; }

Slightly change the “for q” loop -

for q in
(
   select qty,
   (
      select qty from temp_slave_wait t_in
      where t_in.node = t_out.node
      and t_in.ms = t_out.ms / 2
   ) as prev_qty
   from temp_slave_wait t_out
   where t_out.node = n.node order by ms
)
loop

Then add the following tag inside the loop -

if q.prev_qty is not null then

%>
<line
   x1="<%= mx + (gw/xs)*(k-3/2) %>"
   y1="<%= (ch-my) - gh*(q.prev_qty/max_qty) %>"
   x2="<%= mx + (gw/xs)*(k-1/2) %>"
   y2="<%= (ch-my) - gh*(q.qty/max_qty) %>"
   stroke="<%= clr %>"
   stroke-width="1"
   class="lines"
/>
<%

end if;

Beautifil?

Zoom in -

It is.

Legend

As you can see, there’s a lot of white space in the top right corner of the graph. Of course there is no guarantee this will always be the case, but just for the sake of this example, let’s be opportunistic and take advantage of it.

Let’s add a legend to our graph (and use the white space for it).

First, add a couple of temp variables to the declaration section –

tx simple_integer := 0;
ty simple_integer := 0;

Then, just after we assign the colour to “clr”, add the following code –

tx := cw * 2/3;
ty := ch/10 + (ch/20) * n.node;

%>
<rect
   x="<%= tx - 4 %>"
   y="<%= ty - 4 %>"
   rx="3"
   ry="3"
   width="8"
   height="8"
   style="fill:<%= clr %>; stroke:<%= clr %>;"
   class="dots"
/>
<line
   x1="<%= tx - 10 %>"
   y1="<%= ty %>"
   x2="<%= tx + 10 %>"
   y2="<%= ty %>"
   stroke="<%= clr %>"
   stroke-width="1"
   class="lines"
/>
<text x="<%= tx + 20 %>" y="<%= ty + 5 %>" class="labels">node <%= n.node %></text>
<%

The white space has now been put to use.

Zooming in.

Now make it interactive

Since iPad/iPhones are tappable, and are being tapped all the time, let’s see if our graph can benefit from tapping.

We will use the on-mouse-over and on-mouse-out events for this, even thought there is no mouse on iPads/iPhones. Tapping something will invoke on-mouse-over, and tapping somewhere else will invoke on-mouse-out.

To do a quick test, add the following attributes to the text tag (the one that prints “node x”) -

<text
   onmouseover="evt.target.setAttribute('font-weight','bold');"
   onmouseout="evt.target.setAttribute('font-weight','normal');"
   x="<%= tx + 20 %>" 
   y="<%= ty + 5 %>" 
   class="labels">node <%= n.node %></text>

Now tap “node x”, and tap somewhere else, you can see it become bold and then change back to normal.

But that’s not all. Let’s also make the corresponding part of the graph (the lines that connect node x’s dots) “bolder”.

The way we are going to do this, is not the neatest. However, the reason we are doing it this way, is to illustrate that using javascript in SVG is as easy as using it in HTML.

First, add the following attribite to the line tag (the one that connects the dot, not in the legend) -

...
id="line_<%= n.node %>_<%= k %>"
...

Then add the following section, right after the opening <svg> tag -

<script type="text/javascript">
<![CDATA[

function restroke(node, sw)
{
   var e;

   <% for k in 2..xs loop %>

   e = document.getElementById('line_' + node + '_<%= k %>');
   e.setAttribute('stroke-width', sw);

   <% end loop; %>
}

// ]]>
</script>

And call the function from the on-mouse-* events -

...
onmouseover=" ... restroke(<%= n.node %>,5);"
onmouseout=" ... restroke(<%= n.node %>,1)"
...

Now let’s see what happens when we tap the “node 1″ text in the legend.

The text itself becomes bold (as we’ve seen before), and the line becomes wider.

Tap “node 2″ or “node 3″ and see the lines switch.

What now

The example we created can be easily extended, you could create a procedure out of it, so one would pass a reference to the data, some parameters, and the procedure would draw up an SVG graph.

Another point, maybe not so important, is the size of the generated SVG. Since you’re looking at it on your iPad (iPhone) via 3G connection, data traffic plays a big part. Sometimes it’s slow, sometimes it’s expensive.

The entire SVG file we created in this example is only about 10K, which would load to your device instantly and cost you next to nothing.

Conclusion

As you can see, putting an SVG graph together is very simple. It is very similar to HTML, and if you know HTML well, you’ll catch up with SVG tags quite quickly. As was pointed out at the beginning of this article – in HTML5, SVG will be an integral part of it.

About these ads

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.

%d bloggers like this: