Bug in SQLite3 Ruby wrapper when results_as_hash=true?

Ruby
1

Hello,

I am in my first days of Ruby, so I may have done something stupid, but
my
search did not uncover it.

It seems to me that the SQLite3 wrapper doesn’t return correct results
when
results_as_hash is true. I get spurious entries in the hash.

Here is a redux of my problem:

require 'rubygems'
require 'sqlite3'

#File.delete "test.db"
db = SQLite3::Database.new("test.db");
db.results_as_hash=true
db.execute("create table repertoire (Name TEXT,Firstname

TEXT,Telephone
TEXT)")
db.execute(“insert into repertoire (Name, Firstname, Telephone)
values
(‘Doe’, ‘John’, ‘555-123-4567’)”)
p db.execute(‘select * from repertoire;’)

The results is indeed a hash, but with spurious entries:

[{“Name”=>“Doe”, 0=>“Doe”, 1=>“John”, 2=>“555-123-4567”,
“Telephone”=>“555-123-4567”, “Firstname”=>“John”}]

I am running Ruby:
ruby 1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0]

Under MacOS X Leopard 10.5.6
With SQLite3 3.4.0

Executing the query with the sqlite3 command line tool returns the
expected
result:

$ sqlite3 test.db
SQLite version 3.4.0
Enter “.help” for instructions
sqlite> select * from repertoire;
Doe|John|555-123-4567
sqlite>

Any confirmation/workaround/fix?

Many thanks.

Jean-Denis

2

On Feb 5, 2009, at 08:06 , Jean-Denis M. wrote:

The results is indeed a hash, but with spurious entries:

[{“Name”=>“Doe”, 0=>“Doe”, 1=>“John”, 2=>“555-123-4567”,
“Telephone”=>“555-123-4567”, “Firstname”=>“John”}]

looks like that is intentional. You can access a value by column name
or index (like an array).

3

Quoting Ryan D. [email protected]:

or index (like an array).

It seems indeed intentional. The Database class uses a [SQL] Statement
class,
which uses a ResultSet class.

The ResultSet.next method has the following comment:

# For hashes, the column names are the keys of the hash, and the

column
# types are accessible via the +types+ property.

Yet, the code looks like this:

    [...]
    if @db.results_as_hash
      new_row = HashWithTypes[ *( @stmt.columns.zip( row

).to_a.flatten ) ]
row.each_with_index { |value,idx| new_row[idx] = value }
row = new_row
else […]

The line beginning with “row.each_with_index” is the one adding the
spurious
entries in the Hash. So this seems intentional, even though the comment
seems to
say otherwise.

My suggestion is to get rid of this line. I commented it out from my
version,
and it now works as I expected.

However, my change will get reversed next time I “gem update”, and the
architecture of sqlite3-ruby makes it rather difficult to subclass.

Yet again, I am a Ruby noob, so I welcome suggestions.

Jean-Denis

4

Quoting Ryan D. [email protected]:

or index (like an array).

This may be, but it is not documented as such, and it breaks any attempt
to
iterate over the returned hash (without special treatment).

Since I need to iterate over all values for all rows, I am now facing a
choice:

1- Either I iterate using [an array of] hashes, testing each key as I go
2- Or I iterate in the default mode of [an array of] arrays, with no
test, as
each value is present once and only once.

Without the test, option 1 was more attractive as the iterative
treatment
consists of outputting all records one line per column name, followed by
one
line per corresponding value.

With the test, Option 2 becomes equally attractive, and (I suspect),
more
efficient.

Comments welcome.

Jean-Denis