Asp Forum - ruby-DBI and SQLite3?

Jos Backus

1/17/2007 1:53:00 AM

Is anybody interested in working with me on updating ruby-DBI to support
SQLite3? I need it in order to ward off the evil P-monster (both!) at work.

--
Jos Backus
jos at catnook.com

3 Answers

Mike Kasick

1/17/2007 3:39:00 AM

On Wed, Jan 17, 2007 at 10:53:29AM +0900, Jos Backus wrote:

> Is anybody interested in working with me on updating ruby-DBI to support
> SQLite3? I need it in order to ward off the evil P-monster (both!) at work.

There already exists two implementations of a SQLite3 dbd that I'm aware
of. Neither are actively maintained as far as I know, they were posted
to the ruby-dbi mailing list sometime (I found them on Google, was never
a subscriber myself).

Anyways, the more featureful of the two has been packaged in Debian for
some time now. Over the summer I made a few improvements to it,
including adding transaction support. I procrastinated way too long in
getting the patch in the Debian package, but I sent it in a little over
a week ago, and it has since been committed.

If you're running debian, install package libdbd-sqlite3-ruby1.8.
Otherwise download:

http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1.o...
http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1...

The patch contains the SQLite3 dbd with transaction support.

Also, be aware that the SQLite3 dbd requires the sqlite3-ruby library.
I also recently sent some patches to Debian that fix a few issues when
using the SQLite3 dbd, namely when binding variables to a prepared
statement.

The sqlite3-ruby changes are available in the libsqlite3-ruby1.8
package, or from:

http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0.o...
http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0...

It also appears that these changes have made it back into sqlite3-ruby
upstream in the 1.2.0 release that came out just a few days ago.
However, I've not had a chance to verify that the dbd works properly
with that version (and I probably won't be able to for some time).

In fact, if you try sqlite3-ruby 1.2.0, I'd appreciate knowing
if everything seems to work OK with it.

Good luck!

Jos Backus

1/17/2007 5:10:00 AM

Hey Mike,

On Wed, Jan 17, 2007 at 12:38:48PM +0900, Mike Kasick wrote:
> On Wed, Jan 17, 2007 at 10:53:29AM +0900, Jos Backus wrote:
>
> > Is anybody interested in working with me on updating ruby-DBI to support
> > SQLite3? I need it in order to ward off the evil P-monster (both!) at work.
>
> There already exists two implementations of a SQLite3 dbd that I'm aware
> of. Neither are actively maintained as far as I know, they were posted
> to the ruby-dbi mailing list sometime (I found them on Google, was never
> a subscriber myself).

I did see the Debian package but it didn't occur to me to look for the
tarballs.

> Anyways, the more featureful of the two has been packaged in Debian for
> some time now. Over the summer I made a few improvements to it,
> including adding transaction support. I procrastinated way too long in
> getting the patch in the Debian package, but I sent it in a little over
> a week ago, and it has since been committed.
>
> If you're running debian, install package libdbd-sqlite3-ruby1.8.

Sadly, I'm on CentOS.

> Otherwise download:
>
> http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1.o...
> http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1...
>
> The patch contains the SQLite3 dbd with transaction support.
>
> Also, be aware that the SQLite3 dbd requires the sqlite3-ruby library.
> I also recently sent some patches to Debian that fix a few issues when
> using the SQLite3 dbd, namely when binding variables to a prepared
> statement.
>
> The sqlite3-ruby changes are available in the libsqlite3-ruby1.8
> package, or from:
>
> http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0.o...
> http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0...

Excellent!

> It also appears that these changes have made it back into sqlite3-ruby
> upstream in the 1.2.0 release that came out just a few days ago.
> However, I've not had a chance to verify that the dbd works properly
> with that version (and I probably won't be able to for some time).
>
> In fact, if you try sqlite3-ruby 1.2.0, I'd appreciate knowing
> if everything seems to work OK with it.

I just installed the sqlite3-ruby 1.2.0 gem and aside from spewing a bunch of
warnings like

sqlite3_api_wrap.c:1171: warning: dereferencing type-punned pointer will break strict-aliasing rules

it appears to install okay. Running the demo from the README mostly works,
too:

# cat x
require 'dbi'

DBI.connect('DBI:SQLite3:test', 'testuser', 'testpwd') do | dbh |

sql = "insert into songs (SongName, SongLength) VALUES (?, ?)"

dbh.prepare(sql) do | sth |
1.upto(13) { |i| sth.execute("Song #{i}", "#{i*10}") }
end

dbh.select_all('select * from songs') do | row |
p row
end

dbh.do('delete from songs where internal_id > 10')

end
# ruby x
["Song 1", 10]
["Song 2", 20]
["Song 3", 30]
["Song 4", 40]
["Song 5", 50]
["Song 6", 60]
["Song 7", 70]
["Song 8", 80]
["Song 9", 90]
["Song 10", 100]
["Song 11", 110]
["Song 12", 120]
["Song 13", 130]
["Song 1", 10]
["Song 2", 20]
["Song 3", 30]
["Song 4", 40]
["Song 5", 50]
["Song 6", 60]
["Song 7", 70]
["Song 8", 80]
["Song 9", 90]
["Song 10", 100]
["Song 11", 110]
["Song 12", 120]
["Song 13", 130]
["Song 1", 10]
["Song 2", 20]
["Song 3", 30]
["Song 4", 40]
["Song 5", 50]
["Song 6", 60]
["Song 7", 70]
["Song 8", 80]
["Song 9", 90]
["Song 10", 100]
["Song 11", 110]
["Song 12", 120]
["Song 13", 130]
/usr/lib/ruby/site_ruby/1.8/DBD/SQLite3/SQLite3.rb:212:in `initialize': no such column: internal_id (DBI::ProgrammingError)
from /usr/lib/ruby/site_ruby/1.8/DBD/SQLite3/SQLite3.rb:99:in `new'
from /usr/lib/ruby/site_ruby/1.8/DBD/SQLite3/SQLite3.rb:99:in `prepare'
from /usr/lib/ruby/site_ruby/1.8/dbi.rb:891:in `execute'
from /usr/lib/ruby/site_ruby/1.8/dbi.rb:898:in `do'
from /usr/lib/ruby/site_ruby/1.8/dbi.rb:496:in `do'
from x:15
from /usr/lib/ruby/site_ruby/1.8/dbi.rb:430:in `connect'
from /usr/lib/ruby/site_ruby/1.8/dbi.rb:215:in `connect'
from x:3
#

I'm going to do a more realistic app with it soon. I'll let you know how it
goes.

> Good luck!

Thanks for the great info! Looks like I'm on my way.

--
Jos Backus
jos at catnook.com

Jos Backus

4/4/2007 6:14:00 PM

On Wed, Jan 17, 2007 at 12:38:48PM +0900, Mike Kasick wrote:
> On Wed, Jan 17, 2007 at 10:53:29AM +0900, Jos Backus wrote:
>
> > Is anybody interested in working with me on updating ruby-DBI to support
> > SQLite3? I need it in order to ward off the evil P-monster (both!) at work.
>
> There already exists two implementations of a SQLite3 dbd that I'm aware
> of. Neither are actively maintained as far as I know, they were posted
> to the ruby-dbi mailing list sometime (I found them on Google, was never
> a subscriber myself).
>
> Anyways, the more featureful of the two has been packaged in Debian for
> some time now. Over the summer I made a few improvements to it,
> including adding transaction support. I procrastinated way too long in
> getting the patch in the Debian package, but I sent it in a little over
> a week ago, and it has since been committed.
>
> If you're running debian, install package libdbd-sqlite3-ruby1.8.
> Otherwise download:
>
> http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1.o...
> http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1...
>
> The patch contains the SQLite3 dbd with transaction support.
>
> Also, be aware that the SQLite3 dbd requires the sqlite3-ruby library.
> I also recently sent some patches to Debian that fix a few issues when
> using the SQLite3 dbd, namely when binding variables to a prepared
> statement.
>
> The sqlite3-ruby changes are available in the libsqlite3-ruby1.8
> package, or from:
>
> http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0.o...
> http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0...
>
> It also appears that these changes have made it back into sqlite3-ruby
> upstream in the 1.2.0 release that came out just a few days ago.
> However, I've not had a chance to verify that the dbd works properly
> with that version (and I probably won't be able to for some time).
>
> In fact, if you try sqlite3-ruby 1.2.0, I'd appreciate knowing
> if everything seems to work OK with it.

I have been using sqlite3-ruby for a while now and it seems to work fine.

Thanks again, Mike.

--
Jos Backus
jos at catnook.com

comp.lang.ruby

ruby-DBI and SQLite3?

Jos Backus

Mike Kasick

Jos Backus

Jos Backus

x Login to ForumsZone