Problem with SQLite and Ruby

Ruby
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

On Wed, 3 May 2006, Peter H. wrote:

db.execute( “SELECT * FROM sources;” ) do |source|
id => sshd
description => SSH Daemon

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

 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

3

On Wed, 3 May 2006, Peter H. 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.

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

4

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.

5

Ah, that looks exactly like what I want.

Thanks