CODEWITHSUNDEEP
ruby-on-railspostgresqlblob
highBandWidth
highBandWidth

Reputation: 17321

Modeling PostgreSQL's large objects in Rails

I need to save large objects in the db in my rails app. I think that I can have a table created in SQL as

CREATE TABLE files (
id serial NOT NULL,
name string NOT NULL,
blob_oid oid NUT NULL
)

And then store the data in Ruby as 

conn.exec("BEGIN")
lo = conn.lo_import(data)
conn.exec("COMMIT")
file = File.new
file.name = file_name; file.blob_oid = lo.id
file.save

First of all, is this correct? Secondly, how do I describe the model for File in Rails. What is the data type of Friend.blob_oid in Rails? Can I create the files table using a migration in Rails?

Upvotes: 2

Views: 2441

Answers (1)

Gary S. Weaver
Gary S. Weaver

Reputation: 8096

If using ActiveRecord that comes with Rails with one of its adapters, the only formal mapping of database type to Rails or Ruby type that happens is typically defined in the NATIVE_DATABASE_TYPES constant in the adapter which is returned via its native_database_types method. For PostgreSQL in Rails 3.2.x, that is in ActiveRecord::ConnectionAdapters::PostgreSQLAdapter which is here. So, for that adapter, the "binary" type in Rails maps to the "bytea" type in PG. For some types, you can override that database type that it maps to using a gem called activerecord-native_db_types_override. But, we want to use large objects, so...

Migrations

As Jim Deville noted in the comments, you can specify the custom typed column in the table like:

t.column :some_oid, 'blob_oid', :null => false

If you need to do even more that is non-standard, you can also use an execute("SQL GOES HERE;") to create the table using straight SQL. And, if you have an existing legacy schema or SQL changes that have been made outside of the migrations, consider using structure.sql (config.active_record.schema_format = :sql option in config/application.rb and then do: rake db:structure:dump).

Large Objects Read/Write/Check Length/Delete

Copied with some modifications to clarify, etc. from: https://github.com/diogob/carrierwave-postgresql/blob/v0.1.0/lib/carrierwave/storage/postgresql_lo.rb:

Updated: we can but don't need to put a begin before the lo_read/lo_write/lo_lseek and do lo_close in ensure block because per PG documentation "Any large object descriptors that remain open at the end of a transaction will be closed automatically." (thanks to Diogo for that info)

    require 'pg'

    ...

    def read
      (...).transaction do
        lo = connection.lo_open(identifier)
        content = connection.lo_read(lo, file_length)
        connection.lo_close(lo)
        content
      end
    end

    def write(file)
      (...).transaction do
        lo = connection.lo_open(identifier, ::PG::INV_WRITE)
        size = connection.lo_write(lo, file.read)
        connection.lo_close(lo)
        size
      end
    end

    def delete
      connection.lo_unlink(identifier)
    end

    def file_length
      (...).transaction do
        lo = connection.lo_open(identifier)
        size = connection.lo_lseek(lo, 0, 2)
        connection.lo_close(lo)
        size
      end
    end

Instead of connection, use the raw connection from the model or base, e.g. ActiveRecord::Base.connection.raw_connection (see this).

(...).transaction is calling transaction on model or base, e.g. ActiveRecord::Base.transaction (see this).

identifier is the oid that you either need to pass in/set or get from just doing a connection.lo_creat.

Other examples/info:

The latter and some answers here suggest that you might want to consider storage of large files separate from the DB, e.g. so that you can use cloud storage. But, if only store the paths/IDs to external files that are not managed by the DB, you lose ACID consistency (one or more DB records could point to one or more files that aren't there or one or more files could exist that don't have one or more associated records in the database). Another argument for storing files on the file system is that you can stream files, but PG large object stores files on the filesystem in a way managed by postgres to both ensure ACID consistency and allow streaming (which you can't do with a normal BLOB/Rails binary type). So, it just depends; some find storing in separate storage using path references a better option, and some prefer ACID consistency via Large Objects.

The Easy Way

Just use CarrierWave and carrierwave-postgresql.

Upvotes: 5

Related Questions