Problem with SQLite and Ruby

ruby-talk
1

I'm having a little problem here. I have a version 2 SQLite database and the following code:

#!/usr/bin/ruby

require 'sqlite'

db = SQLite::Database.new( "db/ars.db", 0644 )
db.results_as_hash = true

db.execute( "SELECT * FROM sources;" ) do |source|
        source.each do |k,v|
                puts "#{k} => #{v}"
        end
        puts
end

And I get:

0 => sshd
1 => SSH Daemon
id => sshd
description => SSH Daemon

0 => ipfw
1 => Firewall
id => ipfw
description => Firewall

0 => snort
1 => SNORT IDS
id => snort
description => SNORT IDS

0 => apache_error
1 => Apache Error Log
id => apache_error
description => Apache Error Log

But what I was expecting / wanted is:

id => sshd
description => SSH Daemon

id => ipfw
description => Firewall

id => snort
description => SNORT IDS

id => apache_error
description => Apache Error Log

What am I doing wrong?

2

sqlite, by default, gives each tuple as a hash containing both the field names
and field id as keys for each value. a more convenient, imho, way to use
sqlite is in conjunction with arrayfields:

   jib:~ > sqlite db '.tables'
   attributes jobs

   jib:~ > cat a.rb
   require 'sqlite'
   require 'arrayfields'

   db = SQLite::Database::new 'db', 42
   db.use_array = true

   tuples = db.execute('select * from jobs limit 4')

   tuples.each do |tuple|
     fields = tuple.fields
     puts "---"
     puts " class : #{ tuple.class }"
     puts " tuple : #{ tuple.join ', ' }"
     puts " fields : #{ tuple.fields.join ', ' }"
     puts " tuple[0] : #{ tuple[0] }"
     puts " tuple['jid'] : #{ tuple['jid'] }"
   end

   jib:~ > ruby a.rb

···

On Wed, 3 May 2006, Peter Hickman wrote:

I'm having a little problem here. I have a version 2 SQLite database and the following code:

#!/usr/bin/ruby

require 'sqlite'

db = SQLite::Database.new( "db/ars.db", 0644 )
db.results_as_hash = true

db.execute( "SELECT * FROM sources;" ) do |source|
      source.each do |k,v|
              puts "#{k} => #{v}"
      end
      puts
end

And I get:

0 => sshd
1 => SSH Daemon
id => sshd
description => SSH Daemon

   ---
     class : Array
     tuple : 1, 0, finished, 2006-04-20 10:40:34.751516, 2006-04-20 10:40:49.817180, 2006-04-20 11:38:42.422901, 3472.605721, jib.ngdc.noaa.gov, halibut.ngdc.noaa.gov, , stdout/1, stderr/1, 16036, 0, F152006_lc4_line_screened, true, cfjob --src=ftp://cfd2/cfd2\-2/data/vfl\_4avg/F152006\.d/lc4/ --dst=ftp://cfd2/cfd2\-3/data/lc\_composites/ -- wavg_dn /dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list F15_20060314_20060412.cloud2.light1.line_screened.tile10 -c /dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile10.conf
     fields : jid, priority, state, submitted, started, finished, elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status, tag, restartable, command
     tuple[0] : 1
     tuple['jid'] : 1
   ---
     class : Array
     tuple : 2, 0, finished, 2006-04-20 10:40:34.751516, 2006-04-20 10:40:50.655100, 2006-04-20 11:40:46.114144, 3595.459044, jib.ngdc.noaa.gov, halibut.ngdc.noaa.gov, , stdout/2, stderr/2, 16090, 0, F152006_lc4_line_screened, true, cfjob --src=ftp://cfd2/cfd2\-2/data/vfl\_4avg/F152006\.d/lc4/ --dst=ftp://cfd2/cfd2\-3/data/lc\_composites/ -- wavg_dn /dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list F15_20060314_20060412.cloud2.light1.line_screened.tile11 -c /dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile11.conf
     fields : jid, priority, state, submitted, started, finished, elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status, tag, restartable, command
     tuple[0] : 2
     tuple['jid'] : 2
   ---
     class : Array
     tuple : 3, 0, finished, 2006-04-20 10:40:34.751516, 2006-04-20 10:40:51.674523, 2006-04-20 11:42:12.135216, 3680.460693, jib.ngdc.noaa.gov, trout.ngdc.noaa.gov, , stdout/3, stderr/3, 16003, 0, F152006_lc4_line_screened, true, cfjob --src=ftp://cfd2/cfd2\-2/data/vfl\_4avg/F152006\.d/lc4/ --dst=ftp://cfd2/cfd2\-3/data/lc\_composites/ -- wavg_dn /dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list F15_20060314_20060412.cloud2.light1.line_screened.tile12 -c /dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile12.conf
     fields : jid, priority, state, submitted, started, finished, elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status, tag, restartable, command
     tuple[0] : 3
     tuple['jid'] : 3
   ---
     class : Array
     tuple : 4, 0, finished, 2006-04-20 10:40:34.751516, 2006-04-20 10:40:51.858822, 2006-04-20 11:44:41.847442, 3829.98862, jib.ngdc.noaa.gov, trout.ngdc.noaa.gov, , stdout/4, stderr/4, 16057, 0, F152006_lc4_line_screened, true, cfjob --src=ftp://cfd2/cfd2\-2/data/vfl\_4avg/F152006\.d/lc4/ --dst=ftp://cfd2/cfd2\-3/data/lc\_composites/ -- wavg_dn /dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list F15_20060314_20060412.cloud2.light1.line_screened.tile13 -c /dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile13.conf
     fields : jid, priority, state, submitted, started, finished, elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status, tag, restartable, command
     tuple[0] : 4
     tuple['jid'] : 4

when you use arrayfields each tuple is an Array, but the array is dynamically
altered such that keyword access is also supported. you have 'normal'
hash-like things like ' tuple.values_at %w( jid priority ) ' etc.

regards.

-a
--
be kind whenever possible... it is always possible.
- h.h. the 14th dali lama

3

The problem for me is that the code I am writing needs to take an array of results and convert the results hash for each record into a chunk of XML for any set of results it is passed in. Thus it does not know, or even need to know, what the keys for the table are. It just wants to iterate over them and create some output. It does not take much for me to only process the string based keys but it looked to me like I was overlooking something obvious.

No big deal, just didn't work quite the way I was expecting.

4

indeed. you can do

   fields = tuple.fields

   fields.each do |field|
     append_xml field, tuple[field]
   end

and preserve order. or just the String keys and use random order.

regards.

-a

···

On Wed, 3 May 2006, Peter Hickman wrote:

The problem for me is that the code I am writing needs to take an array of
results and convert the results hash for each record into a chunk of XML for
any set of results it is passed in. Thus it does not know, or even need to
know, what the keys for the table are. It just wants to iterate over them
and create some output. It does not take much for me to only process the
string based keys but it looked to me like I was overlooking something
obvious.

No big deal, just didn't work quite the way I was expecting.

--
be kind whenever possible... it is always possible.
- h.h. the 14th dali lama

5

Ah, that looks exactly like what I want.

Thanks