Using Sqlite for processing

ruby-talk
1

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/

\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

2

db = SQLite::Database.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

···

On Tue, Oct 05, 2004 at 02:22:20AM +0900, Simon Conrad-Armes wrote:

3

Simon Conrad-Armes (Curne) wrote:

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

···

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

4

yn Tue, 5 Oct 2004, Brian Candler wrote:

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

===============================================================================

5

yn Tue, 5 Oct 2004, Brian Candler wrote:

>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)

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.

···

On Tue, Oct 05, 2004 at 05:14:52AM +0900, Ara.T.Howard@noaa.gov wrote: