ruby-dbi-devel

[Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

Hi!

Currently, DBD::Pg does not support PostgreSQL's native Array types as
described in
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=arrays.html

Attached are two patches that add support for
- reading of Arrays
- quote()ing of Arrays

I have submitted the reading-part before, but did not receive any response
at that time. Seems like nobody is using PostgreSQL's arrays, except me :-)
I'd really appreciate some kind of comment from the DBD::Pg maintainer.

Now let me explain the patches:

pg_array_quote.diff:

PostgreSQL-Arrays are surronded by single quotes, the array delimiters
 are{ and }, the elements separated with comma. The problem is, that inside
the array, different quotingrules apply than usual. E.g. a Strings and Dates
are quoted with double-quotes instead of the usual single-quotes. Additionally,
all ' and \ must be extra escaped because the array as a whole is handled as
String in SQL.

To cope with this, an extra private function quote_array_elements() is added
to quote array-elements. This method is called from quote(), when the object
to be quoted is an Array.

The patch is based on Pg.rb 1.27 . I just discovered that in 1.28, support for
PGconn.quote was added. But the quoting of Arrays of PGconn.quote is WRONG.
So slight modifications would be additionally needed to bypass PGconn.quote if
the value is an array and use my quoting mechanism instead. 

pg_array.read.diff:

The goal is to get an array of the respectable type returned by DBD::Pg instead
of Strings like "{1,2,3}" when selecting a column with an array-type.
For this I've improved load_type_map() to register array-types and the type of
their elements. Entries with an _ in front of them are actually arrays, according
to the PostgreSQL documentation.
If an retrieved column is an array, it is passed to the new function
convert_array(), which is a nasty beast that parses the string that comes
out of PostgreSQL in to (possibly nested) Ruby-Arrays.

--
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF

Re: [Ruby-DBI-devel] Array support for DBD::Pg

[Michael N. <mne...@nt...>]

On Wed, May 14, 2003 at 05:57:09PM +0200, Oliver M. Bolzer wrote:
> Hi!
> 
> Currently, DBD::Pg does not support PostgreSQL's native Array types as
> described in
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=arrays.html
> 
> Attached are two patches that add support for
> - reading of Arrays
> - quote()ing of Arrays
> 
> I have submitted the reading-part before, but did not receive any response
> at that time. Seems like nobody is using PostgreSQL's arrays, except me :-)
> I'd really appreciate some kind of comment from the DBD::Pg maintainer.

Sorry, I had not much time in the last months. 
There is currently no active DBD::Pg maintainer (is there?). 

I'd be really happy if someone would take over this part, so I could
concentrate (i.e. spend my time) more with overall DBI issues.

What I really had in mind is, to completely separate the DBDs from the DBI
(= different projects). Doing this, updates on single DBDs
would come out much quicker than today it's the case.

> Now let me explain the patches:
> 
> pg_array_quote.diff:
> 
> PostgreSQL-Arrays are surronded by single quotes, the array delimiters
>  are{ and }, the elements separated with comma. The problem is, that inside
> the array, different quotingrules apply than usual. E.g. a Strings and Dates
> are quoted with double-quotes instead of the usual single-quotes. Additionally,
> all ' and \ must be extra escaped because the array as a whole is handled as
> String in SQL.
> 
> To cope with this, an extra private function quote_array_elements() is added
> to quote array-elements. This method is called from quote(), when the object
> to be quoted is an Array.
> 
> The patch is based on Pg.rb 1.27 . I just discovered that in 1.28, support for
> PGconn.quote was added. But the quoting of Arrays of PGconn.quote is WRONG.
> So slight modifications would be additionally needed to bypass PGconn.quote if
> the value is an array and use my quoting mechanism instead. 

Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
there are any modifications (i.e. send me the updated patch).

> pg_array.read.diff:
> 
> The goal is to get an array of the respectable type returned by DBD::Pg instead
> of Strings like "{1,2,3}" when selecting a column with an array-type.
> For this I've improved load_type_map() to register array-types and the type of
> their elements. Entries with an _ in front of them are actually arrays, according
> to the PostgreSQL documentation.

I thought types with prefixed _ are basic Postgres types.
Removing the _ types from the type_map do not break programs?

Regards,

  Michael

Re: [Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

On Wed, May 14, 2003 at 08:52:44PM +0200, Michael Neumann <mne...@nt...> wrote...
 
> What I really had in mind is, to completely separate the DBDs from the DBI
> (= different projects). Doing this, updates on single DBDs
> would come out much quicker than today it's the case.

A first step might be to designate somebody as each DBD's
maintainer and if the DBD is much more frequently updated
than main DBI, then it could still be separated out. 
 
> Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
> there are any modifications (i.e. send me the updated patch).

Sure. Will do it tomorrow first thing.
 
> I thought types with prefixed _ are basic Postgres types.
> Removing the _ types from the type_map do not break programs?

According to pg_type's definition at 

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=catalog-pg-type.html

PostgreSQL-Arrays have a non-zero typelem field containing the type of the
elements. Looking into the actual values in my pg_type, it's the _ variants
that are arrays and the types without _ are non-array types.
 
-- 
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF

Re: [Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

On Wed, May 14, 2003 at 08:52:44PM +0200, Michael Neumann <mne...@nt...> wrote...
 
> Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
> there are any modifications (i.e. send me the updated patch).

Here you go.

-- 
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF

Re: [Ruby-DBI-devel] Array support for DBD::Pg

[Michael N. <mne...@nt...>]

On Thu, May 15, 2003 at 11:44:47AM +0200, Oliver M. Bolzer wrote:
> On Wed, May 14, 2003 at 08:52:44PM +0200, Michael Neumann <mne...@nt...> wrote...
>  
> > Can you try the patch with the newest version (1.31) of Pg.rb and tell me if
> > there are any modifications (i.e. send me the updated patch).
> 
> Here you go.

Thanks.  I've applied the patches.


Regards,

  Michael