I adapted the file upload example from Agile Web Development with Rails (page 362)- the one where you store the image as a blob. This works great in MySQL, but it doesn''t work when I switched over to Postgres. Postgres uses the OID type as opposed to a blob. Does Rails know what to do with this? Is there some way I can intercept the SQL statement and rewrite it? Or should I just give up on this one? - Fred
Fred McCann wrote:> I adapted the file upload example from Agile Web Development with Rails > (page 362)- the one where you store the image as a blob. This works > great in MySQL, but it doesn''t work when I switched over to Postgres. > Postgres uses the OID type as opposed to a blob. Does Rails know what > to do with this? Is there some way I can intercept the SQL statement > and rewrite it? Or should I just give up on this one? > > - FredThere is also BYTEA datatype in PostgreSQL to store BLOBs. But there can be problems when you try to store large files in BYTEA column - Ruby can eat up to 25 times more memory than file size (due to some glitches in PostgreSQLAdapter#escape_bytea() methods) and another possible issue is that ActiveRecord by default will read all fields from database record (including BYTEA), so you can run out of memory again. So I had to add lo_* methods to my copy of PostgreSQLAdapter. :)
* Lugovoi Nikolai (meadow.nnick-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org) [051012 07:20]:> There is also BYTEA datatype in PostgreSQL to store BLOBs. > But there can be problems when you try to store large files in BYTEA > column - Ruby can eat up to 25 times more memory than file size (due to > some glitches in PostgreSQLAdapter#escape_bytea() methods) and another > possible issue is that ActiveRecord by default will read all fields from > database record (including BYTEA), so you can run out of memory again. > > So I had to add lo_* methods to my copy of PostgreSQLAdapter. :)Hey -- did you submit that as a patch somewhere? This is something I think quite a few of us would like to see folded in upstream (plus, hey, you get more CHANGELOG love). Best, Rick -- http://www.rickbradley.com MUPRN: 328 | >Peter Chernin >Thank random email haiku | you very much, Fred, and thanks | for inviting me.
Rick Bradley wrote:> > Hey -- did you submit that as a patch somewhere? This is something I > think quite a few of us would like to see folded in upstream (plus, hey, > you get more CHANGELOG love). >I don''t think it would be a worthy patch, as it was just a quick hack to make things working :) , and I''m too lazy to write thorough tests, so it won''t be send to dev.rubyonrails I provide code below and one can fairly say : this code sucks! (Note: calls to lo_* methods always have to be inside transaction) ---------------------------------------------------------- Index: postgresql_adapter.rb ==================================================================--- postgresql_adapter.rb (revision 2537) +++ postgresql_adapter.rb (working copy) @@ -236,9 +254,55 @@ end execute "DROP INDEX #{index_name}" - end + end + INV_WRITE = 0x00020000 + INV_READ = 0x00040000 + + def lo_open(oid, mode = INV_READ) + @connection.lo_open(oid, mode) + end + + def lo_create(mode = INV_WRITE) + @connection.lo_create(mode) + end + + def lo_write_data(data) + lo = @connection.lo_create(INV_WRITE) + lo_id = lo.oid + lo.open(INV_WRITE) + lo.write(data) + lo.close + lo_id + end + + def lo_get_data(oid) + lo = @connection.lo_open(oid, INV_READ) + lo_data = lo.read + lo.close + lo_data + end + + def lo_data_size(oid) + lo = @connection.lo_open(oid, INV_READ) + data_size = lo.size + lo.close + data_size + end + + def create_savepoint(name) + @connection.execute("SAVEPOINT #{name}") + end + + def release_savepoint(name) + @connection.execute("RELEASE SAVEPOINT #{name}") + end + + def rollback_savepoint(name) + @connection.execute("ROLLBACK TO SAVEPOINT #{name}") + end + private BYTEA_COLUMN_TYPE_OID = 17 @@ -357,6 +421,7 @@ # geometric types (the line type is currently not implemented in postgresql) when /^(?:point|lseg|box|"?path"?|polygon|circle)/i then ''string'' when /^bytea/i then ''binary'' + when /^oid$/i then ''integer'' else field_type # Pass through standard types. end end