SQLite/Ruby update pending

ruby-talk
1

I'm getting ready to release a "release-candidate" of SQLite/Ruby 1.3.0. The only thing different about this one is that "arrayfields" is no longer a dependency.

I liked Sean's suggestion about leaving the "arrayfields" dependency up the programmer, so now you can have result set rows returned either as Hashes (the original behavior) or as arrays. If you are using arrays, then the result-set rows will have a new property, "fields" added to them (unless Array already responds to that property, as will be the case with arrayfields).

Hashes are used for rows, by default. To use arrays, just set the "use_array" property of your database instance to 'true'.

I like this much better than the way it was done in 1.2. Does this set better with those of you that didn't particularly like the way 1.2 did it? How about those of you that *did* like the way 1.2 did it?

Also, since I'm working on an update: any other "wish list" items you'd like to see in SQLite/Ruby?

···

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

ruby -ropenssl -e'k="01234567";p((c,c.padding,c.iv,c.key=OpenSSL::Cipher::BF.new,0,k,k*2)[0].decrypt.update("1A81803C452C324619D319F980D5B84DBB45FC0FE2BAA045".scan(/../).map{|n|n.to_i(16).chr}.join))'

2

I'm getting ready to release a "release-candidate" of SQLite/Ruby 1.3.0.
The only thing different about this one is that "arrayfields" is no longer a
dependency.

I liked Sean's suggestion about leaving the "arrayfields" dependency up the
programmer, so now you can have result set rows returned either as Hashes
(the original behavior) or as arrays. If you are using arrays, then the
result-set rows will have a new property, "fields" added to them (unless
Array already responds to that property, as will be the case with
arrayfields).

Hashes are used for rows, by default. To use arrays, just set the
"use_array" property of your database instance to 'true'.

sounds good. hopefully you know that it goes without saying that you can take
arrayfields.rb and distrbute it with sqlite if you ever want too...

I like this much better than the way it was done in 1.2. Does this set
better with those of you that didn't particularly like the way 1.2 did it?
How about those of you that *did* like the way 1.2 did it?

i think this is by far the best way because you give the option of having the
fastest most memory un-intensive method (Array's), something that uses a lot
of memory but is fast for fielded lookups (Hashes), and something that's
inbetween (ArrayFields).

Also, since I'm working on an update: any other "wish list" items you'd like
to see in SQLite/Ruby?

a dedicated error for SQLITE_BUSY! it would be great to be able to

begin
   db.execute sql
rescue SQLite::Busy
   retry unles too_many_retries or timeout
end

instead of the current

begin
   db.execute sql
rescue SQLite::DatabaseError => e
   if e.message =~ /locked/o
     retry unles too_many_retries or timeout
   else
     raise
   end
end

which is highly error prone.

cheers.

-a

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

ruby -ropenssl
-e'k="01234567";p((c,c.padding,c.iv,c.key=OpenSSL::Cipher::BF.new,0,k,k*2)[0].decrypt.update("1A81803C452C324619D319F980D5B84DBB45FC0FE2BAA045".scan(/../).map{|n|n.to_i(16).chr}.join))'

-a

···

On Sun, 4 Jul 2004, Jamis Buck wrote:
--

EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
PHONE :: 303.497.6469
A flower falls, even though we love it;
and a weed grows, even though we do not love it. --Dogen

===============================================================================

3

a quote() or escape() method built in would be useful imo

···

il Sun, 4 Jul 2004 04:04:22 +0900, Jamis Buck <jgb3@email.byu.edu> ha scritto::

Also, since I'm working on an update: any other "wish list" items you'd
like to see in SQLite/Ruby?

4

Ara.T.Howard wrote:

sounds good. hopefully you know that it goes without saying that you can take
arrayfields.rb and distrbute it with sqlite if you ever want too...

Thanks, Ara. I appreciate that. However, I always worry when doing that, since if I package it with one version of arrayfields, and somewhere down the road I forget to update arrayfields when you come out with a new version, it could wind up that a user will have multiple (potentially incompatible) versions of arrayfields installed...which gets ugly.

a dedicated error for SQLITE_BUSY! it would be great to be able to

begin
  db.execute sql
rescue SQLite::Busy
  retry unles too_many_retries or timeout
end

Done! The exception is SQLite::BusyException. :slight_smile: I'd been meaning to do that anyway--thanks for reminding me!

Other exceptions are also defined, such as SQLite::LockedException, SQLite::SQLException, and so forth. (Basically, each valid return code defined by the sqlite API is now encapsulated in an exception class.)

Anything else? :slight_smile:

···

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

ruby -ropenssl -e'k="01234567";p((c,c.padding,c.iv,c.key=OpenSSL::Cipher::BF.new,0,k,k*2)[0].decrypt.update("1A81803C452C324619D319F980D5B84DBB45FC0FE2BAA045".scan(/../).map{|n|n.to_i(16).chr}.join))'

5

gabriele renzi wrote:

a quote() or escape() method built in would be useful imo

SQLite::Database.quote(...) will do just that. It is new in 1.2.9.1.

- Jamis

···

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

ruby -ropenssl -e'k="01234567";p((c,c.padding,c.iv,c.key=OpenSSL::Cipher::BF.new,0,k,k*2)[0].decrypt.update("1A81803C452C324619D319F980D5B84DBB45FC0FE2BAA045".scan(/../).map{|n|n.to_i(16).chr}.join))'

6

just keep up the good work!

i ran about 200,000 jobs through an nfs mounted priority queue i implemented
using your sqlite module in about 200 lines of code in the last week without
error. 30 clients accessing the queue with no central server - i'm blow away
by the possiblities opened up by developing apps using ruby on top of sqlite -
it's an awesome combo so i think your package is poised to see some heavy use.

cheers.

-a

···

On Sun, 4 Jul 2004, Jamis Buck wrote:

Ara.T.Howard wrote:

sounds good. hopefully you know that it goes without saying that you
can take
arrayfields.rb and distrbute it with sqlite if you ever want too...

Thanks, Ara. I appreciate that. However, I always worry when doing that,
since if I package it with one version of arrayfields, and somewhere
down the road I forget to update arrayfields when you come out with a
new version, it could wind up that a user will have multiple
(potentially incompatible) versions of arrayfields installed...which
gets ugly.

a dedicated error for SQLITE_BUSY! it would be great to be able to

begin
  db.execute sql
rescue SQLite::Busy
  retry unles too_many_retries or timeout
end

Done! The exception is SQLite::BusyException. :slight_smile: I'd been meaning to do
that anyway--thanks for reminding me!

Other exceptions are also defined, such as SQLite::LockedException,
SQLite::SQLException, and so forth. (Basically, each valid return code
defined by the sqlite API is now encapsulated in an exception class.)

Anything else? :slight_smile:

--

EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
PHONE :: 303.497.6469
A flower falls, even though we love it;
and a weed grows, even though we do not love it. --Dogen

===============================================================================

7

Does that take care of NULs?

Gavin

···

On Sunday, July 4, 2004, 11:01:02 PM, Jamis wrote:

gabriele renzi wrote:

a quote() or escape() method built in would be useful imo

SQLite::Database.quote(...) will do just that. It is new in 1.2.9.1.

8

Gavin Sinclair wrote:

···

On Sunday, July 4, 2004, 11:01:02 PM, Jamis wrote:

gabriele renzi wrote:

a quote() or escape() method built in would be useful imo

SQLite::Database.quote(...) will do just that. It is new in 1.2.9.1.

Does that take care of NULs?

Gavin

What do you mean? Can you give me an example?

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

ruby -ropenssl -e'k="01234567";p((c,c.padding,c.iv,c.key=OpenSSL::Cipher::BF.new,0,k,k*2)[0].decrypt.update("1A81803C452C324619D319F980D5B84DBB45FC0FE2BAA045".scan(/../).map{|n|n.to_i(16).chr}.join))'

9

SQLite, I believe, chokes on strings that have NUL (0) characters in
them, because C uses \0 as the string terminator and, well, everyone
else should work like that too. Or something. Since SQLite stores
everything as a string (I believe), you have no chance of storing
binary data in SQLite directly -- that's too likely to contain \0 in
it.

I tend to base64-encode any binary data. That's unlikely to please
people who need performance, though. I don't know what the "best
practice" is, but it would be nice to identify it and provide it in
your package.

Cheers,
Gavin

···

On Sunday, July 4, 2004, 11:59:43 PM, Jamis wrote:

SQLite::Database.quote(...) will do just that. It is new in 1.2.9.1.

Does that take care of NULs?

Gavin

What do you mean? Can you give me an example?

10

Gavin Sinclair wrote:

I tend to base64-encode any binary data. That's unlikely to please
people who need performance, though. I don't know what the "best
practice" is, but it would be nice to identify it and provide it in
your package.

I see what you mean. I've been using base64 to encode/decode as well. (In fact, the latest version of sqlite-ruby provides two new convenience methods of the Database class, 'encode' and 'decode', that use Base64 and Marshal to serialize and unserialize objects.)

Until SQLite provides support for binary data (is that in sqlite3?), there doesn't seem to be a whole lot else you *can* do, at least nothing as convenient. Does anyone else have any suggestions? What have the rest of you been doing for storing binary data in a sqlite database?

···

--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis

ruby -ropenssl -e'k="01234567";p((c,c.padding,c.iv,c.key=OpenSSL::Cipher::BF.new,0,k,k*2)[0].decrypt.update("1A81803C452C324619D319F980D5B84DBB45FC0FE2BAA045".scan(/../).map{|n|n.to_i(16).chr}.join))'

11

Hello Gavin,

···

On Sunday, July 4, 2004, 11:59:43 PM, Jamis wrote:

SQLite::Database.quote(...) will do just that. It is new in 1.2.9.1.

Does that take care of NULs?

Gavin

What do you mean? Can you give me an example?

SQLite, I believe, chokes on strings that have NUL (0) characters in
them, because C uses \0 as the string terminator and, well, everyone
else should work like that too. Or something. Since SQLite stores
everything as a string (I believe), you have no chance of storing
binary data in SQLite directly -- that's too likely to contain \0 in
it.

SQLite 3.0 has support for BLOB's.

--
Best regards, emailto: scholz at scriptolutions dot com
Lothar Scholz http://www.ruby-ide.com
CTO Scriptolutions Ruby, PHP, Python IDE 's

12

considering the nature of the binary data i am usually working with (satelite
data) it makes life much easier to simply store the path to the blob in the
database and use the file system. it's a pain to first extract a 1gb blob and
write it to file before i can open it up in envi or something like that...

-a

···

On Mon, 5 Jul 2004, Jamis Buck wrote:

Until SQLite provides support for binary data (is that in sqlite3?), there
doesn't seem to be a whole lot else you *can* do, at least nothing as
convenient. Does anyone else have any suggestions? What have the rest of you
been doing for storing binary data in a sqlite database?

--

EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
PHONE :: 303.497.6469
A flower falls, even though we love it;
and a weed grows, even though we do not love it. --Dogen

===============================================================================