ruby-DBI and SQLite3?

[Jos Backus]

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.

 

[Mike Kasick]

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.orig.tar.gz
http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1-4.diff.gz

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.orig.tar.gz
http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0-4.diff.gz

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]

Hey Mike,

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.orig.tar.gz
http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1-4.diff.gz

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.orig.tar.gz
http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0-4.diff.gz
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:rogrammingError)
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]

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.orig.tar.gz
http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1-4.diff.gz

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.orig.tar.gz
http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0-4.diff.gz

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.