Bug in SQLite3 Ruby wrapper when results_as_hash=true?

ruby-talk
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

select * from repertoire;

Doe>John>555-123-4567

Any confirmation/workaround/fix?

Many thanks.

Jean-Denis

2

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

···

On Feb 5, 2009, at 08:06 , Jean-Denis Muys 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"}]

3

Quoting Ryan Davis <ryand-ruby@zenspider.com>:

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

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

···

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

4

Quoting Ryan Davis <ryand-ruby@zenspider.com>:

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

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

···

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