Thread: Arrays with Rails?

Has anyone here used a postgres array with Rails?  If so, how?

Rick Schumeyer wrote:
> Has anyone here used a postgres array with Rails?  If so, how?
>

split()?

> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake schrieb:
> Rick Schumeyer wrote:
>> Has anyone here used a postgres array with Rails?  If so, how?
>>
>
> split()?

Err... there is no type mapping?

On Fri, 13 Apr 2007 10:30:29 +0200, Tino Wildenhain <tino@wildenhain.de>
wrote:

> Joshua D. Drake schrieb:
>> Rick Schumeyer wrote:
>>> Has anyone here used a postgres array with Rails?  If so, how?
>>>
>>  split()?
>
> Err... there is no type mapping?


    You know, some languages spoil us developers, so that we for granted that
Doing It The Right Way is indeed the way it is, and then we feel the
burning pain of having to deal with the reality...
    For instance, python and psycopg massively spoil you :

cursor.execute( "select '{1,2}'::INTEGER[] AS myarray, 1 AS myint, 'abc'
AS mytext, %s as myarg1, %s as myarg2, %s as myarg3", (
    u"this is an unicode string €éàù",
    [1, 2, 3],
    ["this","is","an","array","of","text"]
))

for row in cursor:
    for name, item in row.items():
        print name, type( item ), item

    Results :
    As you can see, arguments and results are auto-converted from python
types to postgres types (you can write converters for your own types).
This includes unicode, arrays, etc.
    The interface is clean and elegant : you provide SQL with %s in it and a
list of arguments. You can use named or positional arguments too.

mytext <type 'str'> abc
myarray <type 'list'> [1, 2]
myint <type 'int'> 1
myarg1 <type 'str'> this is an unicode string €éàù
myarg2 <type 'list'> [1, 2, 3]
myarg3 <type 'list'> ['this', 'is', 'an', 'array', 'of', 'text']

    Some (like me) would argue that this is NORMAL and that anything inferior
to this is murder. I believe the job of libraries and languages is to help
the programmer, not torture him.

    Then, other languages will make you feel the pain of having to quote all
your arguments YOURSELF and provide all results as string.
    The most famous offender is PHP (this causes countless security holes).
    Ruby is no better :

require "postgres"
conn = PGconn.connect("localhost", 5432, "", "", "test")
res = conn.exec("select '{1,2}'::INTEGER[] AS myarray, 1 AS myint, 'abc'
AS mytext;")
res.result.each {|row|
    row.each { |item|
        puts item
        puts item.class
        puts "\n"
    }
}

{1,2}
String

1
String

abc
String

    So the answer to your question is :

    - either suffer
    - or code an additional layer above the ruby postgres lib which does the
same as the python lib above.
    I partially did this for PHP. It's a lifesaver. No more addslashes() !
Yay !

Listmail schrieb:
>     Then, other languages will make you feel the pain of having to
> quote all your arguments YOURSELF and provide all results as string.
>     The most famous offender is PHP (this causes countless security
> holes).
>     I partially did this for PHP. It's a lifesaver. No more
> addslashes() ! Yay !

What about PEAR MDB2?
http://pear.php.net/manual/en/package.database.mdb2.php

Is it any good?

Cheers, Alex

On Fri, 13 Apr 2007 12:15:30 +0200, Alexander Presber
<aljoscha@weisshuhn.de> wrote:

> Listmail schrieb:
>>     Then, other languages will make you feel the pain of having to
>> quote all your arguments YOURSELF and provide all results as string.
>>     The most famous offender is PHP (this causes countless security
>> holes).
>>     I partially did this for PHP. It's a lifesaver. No more
>> addslashes() ! Yay !
>
> What about PEAR MDB2?
> http://pear.php.net/manual/en/package.database.mdb2.php
>
> Is it any good?
>
> Cheers, Alex

    Well, the problem with a lot of PHP libraries is that they are written by
people who don't think.

    Python's interface for doing a query in your code is close to the ideal,
which should be something like that :
    query( "SELECT * FROM duhhh WHERE id=%s AND date < %s", id, date )
    (python's API has an extra set of () and it also takes named parameters )

    If id is an python integer and date a python datetime object, format
conversion is automatic.
    If they are not, first they should be, but whatever error the programmer
makes DOES NOT make a SQL injection. At most psql will complain that you
try to compare a date with something that is not a date, but you don't get
hacked, since in order to put an un-quoted argument into the SQL you have
to do it really on purpose.

    Now, I use many database queries in my web applications (so does
everyone), therefore I consider a system that needs me to type a lot of
crap in order to work is DUMB.

    PEAR::DB2 says :

$query = 'INSERT INTO tablename (id, itemname, saved_time) VALUES ('
     . $mdb2->quote($id,   'integer')   .', '
     . $mdb2->quote($name, 'text')      .', '
     . $mdb2->quote($time, 'timestamp') .')';
$res =& $mdb2->query($query);

    As you see,
    - it's a lot less compact and readable
    - it's a pain to use, so I will copypaste code, which is the GREAT EVIL
and a good source of bugs
    - you have to repeat the variable types (who cares ?)
    - if I am not well awake I may forget to type that crap because I'm sure
the variable is an integer, why bother (but am I really sure ? => get
hacked)

    Since PHP has no type for date, a much better way of doing this would be :

    query( "INSERT INTO ... VALUES %s,%s,%s", $id, $name,
DB::datetime( $timestamp ) )
    with all the basic types being quoted as they come (ie like a string
since postgres doesn't care between 1 and '1'), and a few adapters for
other types (like date).

    Also the ORM part of PEAR::DB2 is braindead since the field specifiers
are not objects that you can custmize and derive...