From: Simon Conrad-Armes <curne@...> (Curne)
Date: 2004-10-05T02:22:20+09:00
Subject: Using Sqlite for processing

I thought this was cool enough to share.

At work, once a month, I have to send in a time-sheet in a specific  
format. Today I was looking for a clever way of converting the raw data  
exported from my time-logger (JobLog) into that format. After messing  
around with regular expressions for a while my Ruby program was longer  
than fifty lines and I thought to myself, "there must be a better way."

What I really wanted was the power of Ruby syntax but to run queries  
against my data like a relational database. So I thought of  
sqlite-ruby, which I often use for application development. The  
combination of the tabular data (making a simple split/insert real  
easy) and the inline queries in the HERE document makes it real easy to  
create and maintain.

This is the code I wrote in 20 minutes:
--CODE START--
#!/usr/bin/ruby
#JobLog:<id>\t<client/project/ 
job>\t<date>\t<time>\t<minutes>\t<filename>
require 'rubygems';require_gem 'sqlite-ruby'
require 'ftools'

begin

db = SQLite::Database.new('temp')
db.execute 'create table job (id int, client text, project text, job  
text, date text, time text, minutes int, filename text)'
while gets "\r"
      job = chomp.split(/\t/)
      job[1,1] = job[1].split '/'
      db.execute 'insert into job values (?,?,?,?,?,?,?,?)', *job
end

puts <<EOS
#{db.get_first_value 'select filename from job'}, #{ENV['USER']}
#{db.execute('select client, project, round(sum(minutes)/60.0, 2), job  
from job group by client, project, job').collect {|row| (row.join ', ')  
}.join "\n" }
----------------------------------------
#{db.execute('select client, round(sum(minutes)/60.0, 2) from job group  
by client').collect {|row| (row.join ', ') }.join "\n" }
========================================
#{db.get_first_value 'select round(sum(minutes)/60.0, 2) from job'}
EOS
ensure
     db.close if defined? db
     File.rm_f 'temp'
end
--CODE END--

--SAMPLE OUTPUT START--
client1, project1, 6.82, job1
client1, project1, 1.52, job2
----------------------------------------
client1, 8.33
========================================
8.33
--SAMPLE OUTPUT END--


Simon Conrad-Armes