Using Sqlite for processing

[(Curne) Simon Conrad-Armes]

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:atabase.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

 

[Brian Candler]

db = SQLite:atabase.new('temp') ...
ensure
db.close if defined? db

Just a minor point, but db is *always* defined at that point in the code,
and so this test is useless. It's defined even if an exception is raised
*before* the statement which assigns to db (see example below).

You want:

db.close if db
or
db.close unless db.nil?

(depending on stylistic preference). Or with a database backend, maybe
something like

db.close if db and db.connected?

(I don't know if sqlite has a method like this though)

Regards,

Brian.
-------- 8< -------------------
begin
raise "wibble"
ert = 3
rescue Exception
p defined?(ert) # => "local-variable"
p ert # => nil
end

 

[Ara.T.Howard]

Just a minor point, but db is *always* defined at that point in the code,
and so this test is useless. It's defined even if an exception is raised
*before* the statement which assigns to db (see example below).

it may be defined, but it may not respond to 'close' if Database::new blew up.
eg:


harp:~ > cat a.rb
begin
db = raise
ensure
db.close if defined? db
end

harp:~ > ruby a.rb
a.rb:5: undefined method `close' for nil:NilClass (NoMethodError)

this is a misleading error unless you are a developer with the source code
handy.

i often use

begin
db = raise
ensure
db.close rescue nil
end

or even

begin
db = raise
ensure
db.close if db and db.respond_to 'close'
end

the second is better since if the db is a valid db object AND the close fails i
actually would want to know about it. the problem is this can bury your
original error. leading to code like

begin
db = raise
ensure
begin
db.close
rescue => e
error{ e }
end
end

assuming you are using some sort of logging - which of course would also catch
and log the original error... sticky doing things like this in ensure blocks
no?

i have used MetaError classes for cases like this - error class that has
instance Exception variable...


kind regards.

-a
--
===============================================================================
| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE :: 303.497.6469
| A flower falls, even though we love it;
| and a weed grows, even though we do not love it.
| --Dogen
===============================================================================

 

[Jamis Buck]

I thought this was cool enough to share.

[snip]

job = chomp.split(/\t/)
job[1,1] = job[1].split '/'
db.execute 'insert into job values (?,?,?,?,?,?,?,?)', *job

Clever use of the bind parameters, Simon. I'd never have thought to use
them that way!

- Jamis

 

[Brian Candler]

it may be defined, but it may not respond to 'close' if Database::new blew
up.
eg:


harp:~ > cat a.rb
begin
db = raise
ensure
db.close if defined? db
end

harp:~ > ruby a.rb
a.rb:5: undefined method `close' for nil:NilClass (NoMethodError)

That's exactly what I was saying. The clause "if defined? db" should not be
there, because it always evaluates to a true value.

db.close if db and db.respond_to 'close'

That assumes you don't know whether db was created as a database object or
something completely different! Which might be the case in some programs,
but I think in most cases you'd know that db was either nil or something
which responded to 'close'.

However, calling 'close' might raise an exception anyway (e.g. because the
database is already closed)

Regards,

Brian.