使用treemaps图形化表示数据库
union all selectts.name, f.ktfbfeblks * ts.blocksize, 0, f.ktfbfeblks * ts.blocksize fromsys.ts$ ts, sys.x$ktfbfe f wherets.ts# = f.ktfbfetsn andts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0; grant select on treemap_data_view to public; 剩下的工作就是用PHP 文件来产生图表。该文件接受宽度和长度参数来调整被约束的区域的大小。我为图像分配了一个颜色表,颜色0是背景色,颜色1是文本和边界颜色,颜色2-128是绿色和黄色之间的颜色,颜色129-256是黄色和红色之间的颜色。 函数gendate将数据从数据库装入到一个本地表中。函数ImageCenterString在矩形的中心画一些文本(表空间的名字)。 函数TreemapDrawGroup画treemap矩形,使用相应的颜色填充矩形,并在矩形上写名字。 TreemapGroup函数递归地将数据分成交互的垂直和水平分割,划分的依据是每一边的大小,即相对于表空间的大小的百分比。数据首先填入一个数组然后使用 PHP 函数“array_sum()”将两边相加得出总的大小。下面是 PHP 代码: <?php // send header Header("Content-Type: image/png"); // look $font = 4; $fontwidth = ImageFontWidth($font); $fontheight = ImageFontHeight($font); // the query $username = 'scott'; $passWord = 'tiger'; $sql = <<<SQL selecttablespace_name, sum(free_bytes), sum(used_bytes), sum(total_bytes) fromtreemap_data_view group by tablespace_name SQL; // where tablespace_name in ('SYSTEM','EJALA','TOOLS') // generate treemap data functiongendata() { global $username,$password,$sql; // arrange this way to take advantage of "array_sum" $tmdata = array ( 'name'=>array(), 'freebytes'=>array(), 'usedbytes'=>array(), 'totalbytes'=>array() ); $conn = ora_logon($username,$password); ora_commitoff($conn); $cursor = ora_open($conn); ora_parse($cursor,$sql); ora_exec($cursor); $i = 0; while (ora_fetch($cursor)) { $tmdata['name'][$i] = trim(ora_getcolumn($cursor,0)); $tmdata['freebytes'][$i] = ora_getcolumn($cursor,1);
$tmdata['usedbytes'][$i] = ora_getcolumn($cursor,2); $tmdata['totalbytes'][$i] = ora_getcolumn($cursor,3); $i++; } ora_close($cursor); ora_logoff($conn); return $tmdata; } // draw text centered in a rectangle functionImageCenterString(&$image,$x,$y,$wd,$ht,$string) { global $font,$fontwidth,$fontheight; $tw = $fontwidth * strlen($string); $cx = ($tw > $wd) ?0 : ($wd - $tw) >> 1; $cy = ($fontheight > $ht) ?0 : ($ht - $fontheight) >> 1; ImageString($image,$font,$x+$cx,$y+$cy,$string,1); } functionTreemapDrawGroup(&$image,$x,$y,$wd,$ht,&$tmdata,$i) { $pctfree = $tmdata['freebytes'][$i]/$tmdata['totalbytes'][$i]; $color = round($pctfree * 254) + 2.0; ImageFilledRectangle($image,$x+1,$y+1,$x+$wd-1,$y+$ht-1,$color); ImageCenterString($image,$x,$y,$wd,$ht,$tmdata['name'][$i]); } functionTreemapGroup($lvl,&$image,$x,$y,$wd,$ht,&$tmdata) { $cnt = count($tmdata['name']); switch ($cnt) { case 1: // use entire area for rectangle TreemapDrawGroup($image,$x,$y,$wd,$ht,$tmdata,0); break; case 2: // draw split region $pct = $tmdata['totalbytes'][0] / ($tmdata['totalbytes'][0] + $tmdata['totalbytes'][1]); if ($lvl % 2) { // odd = vertical split $ht1 = $ht * $pct; TreemapDrawGroup($image,$x,$y,$wd,$ht1,$tmdata,0); ImageLine($image,$x,$y+$ht1,$x+$wd,$y+$ht1,1); TreemapDrawGroup($image,$x,$y+$ht1,$wd,$ht-$ht1,$tmdata,1); } else {
/// even = horizontal split $wd1 = $wd * $pct; TreemapDrawGroup($image,$x,$y,$wd1,$ht,$tmdata,0); ImageLine($image,$x+$wd1,$y,$x+$wd1,$y+$ht,1); TreemapDrawGroup($image,$x+$wd1,$y,$wd-$wd1,$ht,$tmdata,1); } break; default: // recursively pivot slices $s = $cnt >> 1; // determine split point $tm1 = array ( 'name' => array_slice($tmdata['name'],0,$s), 'freebytes' => array_slice($tmdata['freebytes'],0,$s), 'usedbytes' => array_slice($tmdata['usedbytes'],0,$s), 'totalbytes' => array_slice($tmdata['totalbytes'],0,$s) ); $tm2 = array ( 'name' => array_slice($tmdata['name'],$s), 'freebytes' => array_slice($tmdata['freebytes'],$s), 'usedbytes' => array_slice($tmdata['usedbytes'],$s), 'totalbytes' => array_slice($tmdata['totalbytes'],$s) ); $tm1sum = (float)array_sum($tm1['totalbytes']); $tm2sum = (float)array_sum($tm2['totalbytes']); $pct = $tm1sum / ($tm1sum + $tm2sum); if ($lvl % 2) { // odd = vertical split $ht1 = $ht * $pct;
TreemapGroup($lvl+1,$image,$x,$y,$wd,$ht1,$tm1); ImageLine($image,$x,$y+$ht1,$x+$wd,$y+$ht1,1); TreemapGroup($lvl+1,$image,$x,$y+$ht1,$wd,$ht-$ht1,$tm2); } else { // even = horizontal split $wd1 = $wd * $pct; TreemapGroup($lvl+1,$image,$x,$y,$wd1,$ht,$tm1); ImageLine($image,$x+$wd1,$y,$x+$wd1,$y+$ht,1); TreemapGroup($lvl+1,$image,$x+$wd1,$y,$wd-$wd1,$ht,$tm2); } break; } } // create image and colors $wd = ($_REQUEST["width"]) ? $_REQUEST["width"] : 640; $ht = ($_REQUEST["height"]) ? $_REQUEST["height"] : 480; $im = ImageCreate($wd,$ht); // allocate colors ImageColorAllocate($im,255,255,255); // background = white ImageColorAllocate($im,0,0,0); // text = black // allocates 254 shades from green -> yello -> red for ($i=0;$i<126;$i++) { ImageColorAllocate($im,$i*2,255,0); } for ($i=0;$i<126;$i++) { ImageColorAllocate($im,255,255-($i*2),0); } $tmdata = gendata(); TreemapGroup(0,$im,0,0,$wd,$ht,$tmdata); // ImageCenterString($im,0,0,$wd,$ht,"count = ".count($tmdata['name'])); // draw border at edges of image ImageLine($im,0,0,0,$ht-1,1); ImageLine($im,0,$ht-1,$wd-1,$ht-1,1); ImageLine($im,$wd-1,$ht-1,$wd-1,0,1); ImageLine($im,$wd-1,0,0,0,1); // generate image to output ImagePNG($im); ImageDestroy($im); ?> 本文作者:Scott Stephens已经在Oracle工作了13年有余,他曾经在技术支持、电子商务、市场和软件开发等部门工作。