mysql gui database management

admin — Wed, 04 Aug 2010 00:31:59 +0000

I very rarely blog about an application, however sometimes there is an app or two that make my life and job a lot easier to manager. I use a lot of mysql databases, and managing them is something I like to, lets say…not do.
I simply don’t like navigating through databases and tables from the mysql command line editor, and I really hate the PHP gui tool that it comes with…its just so cumbersome. I’ve been using navicat of late, but I hate the idea that I have to pay for it. I mean lets face it, why the heck would you want to pay for a tool that manages an open-source application! Its irony at its greatest. So in my early morning googling, I stumbled across a diamond among the db management tools.

This little gem is called HeidiSQL. They are located here, although you can download the current version (at the time of writing this article) from here.
Its basically a full GUI management tool of your mysql database, with a whole bunch of features. I wont go into the features, but check it out yourself.

Props to HeidiSQL and MySQl.

Enjoy!

oracle reporting through ruby

admin — Tue, 03 Aug 2010 06:15:29 +0000

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>

mishmashmoo

mysql gui database management

oracle reporting through ruby