oracle reporting through ruby

As the cobwebs fill up on my blog just in time for spring, I thought it may best to clean it up and put some fresh stuff…So reporting is a kind of a big deal at these big corporations. Seems like there is always someone higher in the food chain who wants a spreadsheet to tell them what you’re actually doing, and if you’re hitting your targets. As annoying and cumbersome as it may be, it’s very important that you produce and provide statistics that are accurate, and up to date, so I’ve got a quick solution that will make any boss somewhat happy…

A wise man once told me, if you do something more than once, it’s worth automating it. So I took that idea and ran a marathon with it. I automate everything. I hate repetition so I’d rather invest 3 times the effort and write a script than do it 10 times over. Simple maths really.
Oracle db’s are everywhere, I blog about them, I work with them and I fight with too. If you want the basics on getting ruby and oracle to work, read my other blog post which is here.

Once you have that established, install wamp, which is an apache webserver, mysql and php rolled into one package.

Now what we’re going to do here is use the script from my other post, and enhance it somewhat. In my scenario I have a spreadsheet with NFR’s. They state the Group name, Transaction Name & Response Time. I want my report to show these values (as the source) and then display the values that are in the database so my boss can compare the results and see how the system is performing.

Let’s assume your spreadsheet looks likes this:

Group Transaction Response Time
01.Payment Submit Payment 12 seconds

Now in my master database, my system keeps a log of when a payment changed status from entered, to submitted. Like most systems, it’s a timestamp and it will show the changed status of that particular payment x amount of times, based on how many status’s it goes through. I’ve seen this in many systems, and it’s quite kosher, or halal (depending on where you come from!).

Luckily I have an sql query that will do this for me, but it just presents all the payments in the date range I need, but I need the 95th Percentile of this….luckily sql can do this for you and here is something I whipped up…with some help from my colleagues and maybe a DBA or 2..

SELECT trunc(percentile_cont(0.95) WITHIN GROUP (ORDER BY ((bt2.logtime - bt1.logtime) * 24 * 3600 )),1),COUNT(bt1.trans_num)
        FROM AUDIT_LOG bt1, AUDIT_LOG bt2, 
        WHERE bt1.TRANS_NUM = bt2.TRANS_NUM
        AND bt1.STATUS IN ('SUBMITTED')
        AND bt2.STATUS IN ('APPROVED')
        AND at.LOG_TIMESTAMP > TO_DATE('"+datetimefrom +"','YYYY-MM-DD HH24:MI:SS')
        AND at.LOG_TIMESTAMP < TO_DATE('"+datetimeto +"','YYYY-MM-DD HH24:MI:SS')

Pretty straight forward, You will obviously need to change it for your own application but it a start.
So this returns 1 value, the 95th percentile, truncated to 1 decimal place, and the number of samples (count) that it evaluated the figure on.

So with those 3 components, we have enough to build a ruby script that will read the spreadsheet, and create a report with the results based on that query.

Ruby script…

Ok so now we need to make a functions file. lets say, that there are 100 NFR’s. Each nfr, is based on a different transaction, each one of those transactions needs a separate sql query. So we will need a functions file…with those queries that our script can call.

Name the following file sql.rb

require 'OCI8'
require 'rubygems'
 
def function(name,datetimefrom,datetimeto)
  case name
       when "submit_payment"
              $nfr_name = "Submit Payment"
              $query="select trunc(percentile_cont(0.95) WITHIN GROUP (ORDER BY ((bt2.logtime - bt1.logtime) * 24 * 3600 )),1),count(bt1.trans_num)
        FROM AUDIT_LOG bt1, AUDIT_LOG bt2, 
        WHERE bt1.TRANS_NUM = bt2.TRANS_NUM
        AND bt1.STATUS in ('SUBMITTED')
        AND bt2.STATUS in ('APPROVED')
        AND at.LOG_TIMESTAMP > TO_DATE('"+datetimefrom +"','YYYY-MM-DD HH24:MI:SS')
        AND at.LOG_TIMESTAMP < TO_DATE('"+datetimeto +"','YYYY-MM-DD HH24:MI:SS')"
   end
end

Now the script, which requires the above file.

require 'OCI8'
require 'rubygems'
require 'date.rb'
require 'sql.rb'
require 'time'
require 'functions.rb'
 
#get the current time
t= Time.now
 
datetimefrom = ARGV[0]
datetimeto = ARGV[1]
filename = ARGV[2]
full_filename = "c:\wamp\www\reports\"" +filename+ ".csv"
 
#put the headers of the file in first
 
File.open(full_filename,"a") do |the_file|
  the_file.puts "Group,Transaction,Date/Time From, Date/Time To,RT,95th Percentile,Sample Size"
  the_file.close
end  
 
#add the function names into the array
class Nfrlist <
 
 Struct.new(:group, :transaction, :rt, :function)
 
end
# define new array to hold the records
nfr = Array.new
 
# open the csv file
f = File.open("nfrs.csv", "r")
 
# loop through each record in the csv file, adding
# each record to our array.
f.each_line { |line|
 
  # each line has fields separated by commas, so split those fields
  fields = line.split(',')
 
  # create a new Person
  p = Nfrlist.new
 
  # do a little work here to get rid of double-quotes and blanks
    p.group = fields[0].tr_s('"', '').strip
    p.transaction = fields[1].tr_s('"', '').strip
    p.rt = fields[2].tr_s('"', '').strip
    p.function = fields[3].tr_s('"', '').strip
    nfr.push(p)   
 
 
    #array declreation inside loop to clear the array on each iteration in the loop.
    $lines= []
    $nfr=[]
    $blah=[]
    $percentile=[]
    $query=""
 
    #call the function from the other .rb file
    function(p.function,datetimefrom,datetimeto)  
 
      #if the name of the function starts with CPR connect to the cpr database,   otherwise go to BTR
      if( /^cpr+.w+$/).match(p.function)  then
         conn = OCI8.new('login', 'password', 'database1.world') 
      else
          conn = OCI8.new('login', 'password', 'database2.world')
      end    
    #execute the sql query
    #if the query is blank(for items that are not required for the db, but need to have a line item in the rrport) then skip this bit...
 
   if $query != "" then
    cursor = conn.exec($query)
      while r = cursor.fetch()
       #add each row to the lines array, separate by tab.
       $lines <<p.group + "," + p.transaction + "," + datetimefrom + "," + datetimeto + "," + p.rt + "," + r.join(",")
      end
    cursor.close
  end 
 
    $percentile << $lines
 
 
#now write it to a file. 
File.open(full_filename,"a") do |the_file|
  the_file.puts $percentile
end  
}

Now change your csv, to have a colum which has the name of the case statement name in your sql.rb file for that particular function.

Group Transaction Response Time function
01.Payment Submit Payment 12 seconds submit_payment

Once that’s done, you will be able to run that script. It will read the nfr.csv file, and read each column and save it in the struct. Then you can call it via the struct name, and evaluate on it.

As you can see there are ARGV’s for some info. Those are so this ruby file can be run remotely….say from a web interface….

Build your php…
My php contains some calendar scripts, there are hundreds on the net…just have a poke around to find one that suits you.

<head>
<script language="javascript" src="calendar.js"></script>
</head>
<html>
<body>
<form action="somewhere.php" method="post">
<?php
set_time_limit(0);
//get class into the page
require('tc_calendar.php');
 
//instantiate class and set properties
$dateFrom = new tc_calendar("date1", true);
$dateFrom->setIcon("images/iconCalendar.gif");
$dateFrom->setDate(date('d'), date('m'), date('Y'));
 
echo "Time From (HH24:MI:SS): <input type="text" name="timefrom" /><br />";
//output the calendar
$dateFrom->writeScript();	 
 
echo "<P>";
$dateTo = new tc_calendar("date2", true);
$dateTo->setIcon("images/iconCalendar.gif");
$dateTo->setDate(date('d'), date('m'), date('Y'));
echo "Time To (HH24:MI:SS): <input type="text" name="timeto" /><br />";
 
//output the calendar
$dateTo->writeScript();	  
 
?>
<p>
<input type="checkbox" name="throughput" value="Yes"> Throughput Report <br>
<input type="checkbox" name="responsetime" value="Yes"> Response Time Report <br>
 
<input type="submit" value="Send it!"></p>
 
</form>
</body>
</html>

Now the php page that the form will submit to…called somewhere.php

<?php
set_time_limit(0);
date_default_timezone_set("Australia/Sydney");
$filenameDate=date("dmy_His");
$datefrom = isset($_REQUEST["date1"]) ? $_REQUEST["date1"] :"";
$timefrom=($_REQUEST["timefrom"]);
 
$dateto = isset($_REQUEST["date2"]) ? $_REQUEST["date2"] : "";
$timeto=($_REQUEST["timeto"]);
 
if (isset($_POST['responsetime'])) 
	{
    exec("responsetime.rb "$datefrom $timefrom" "$dateto $timeto" "ResponseTime_$filenameDate");
    $file= "ResponseTime_$filenameDate.csv";
    echo "<a href='http://localhost/reports/ResponseTime_" .$filenameDate.".csv'>Get Response Time Report</a>";
  }
if (isset($_POST['throughput'])) 
	{
    exec("throughput.rb "$datefrom $timefrom" "$dateto $timeto" "Throughput_$filenameDate");
    $file2= "Throughput_$filenameDate.csv";
    echo "<br><a href='http://localhost/reports/Throughput_" .$filenameDate.".csv'>Get Throughput Report</a>";
 
	}
?>

Now give the URL of your wamp server page to your boss, and he can specify his own date/time range for the report, and it will present itself to him. It will show him something like this

Group Transaction Response Time 95th Percentile (from db)
01.Payment Submit Payment 12 seconds 37.7

Now you need worry bout reporting, as its in the hands of his trusty browser, and your script.

You can keep adding to this, by adding more case statement’s in the sql.rb file, and making sure you mention the name of the case statement in a row of your nfr.csv.
Then you will never have to touch the script.

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>