在Rails中为PostgreSQL的大型对象建模 [英] Modeling PostgreSQL's large objects in Rails

问题描述

我需要在Rails应用程序的数据库中保存大对象。我认为我可以在SQL中创建一个表，

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
)

然后将数据存储在Ruby中

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

首先，这是正确的吗？其次，我如何描述Rails中的File模型。 Rails中 Friend.blob_oid 的数据类型是什么？我可以在Rails中使用迁移来创建文件表吗？

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?

推荐答案

如果使用Rails附带的ActiveRecord及其适配器之一，通常，在适配器中通过 native_database_types返回的常量 NATIVE_DATABASE_TYPES 常量定义发生的唯一的数据库类型到Rails或Ruby类型的正式映射。 / code>方法。对于Rails 3.2.x中的PostgreSQL，位于 ActiveRecord :: ConnectionAdapters :: PostgreSQLAdapter 中，这是此处。因此，对于该适配器，Rails中的二进制类型映射到PG中的 bytea类型。对于某些类型，您可以使用名为 activerecord-native_db_types_override 。但是，我们要使用大对象，所以...

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...

迁移

正如吉姆·德维尔（Jim Deville）在评论中指出的那样，您可以在表格中指定自定义类型的列，例如：

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

如果您需要做更多的非标准操作，也可以使用 execute（ SQL GOES HERE;）使用直接SQL创建表。并且，如果您在迁移之外进行了现有的旧模式或SQL更改，请考虑使用structure.sql（ config.active_record.schema_format =：sql 选项 config / application.rb ，然后执行以下操作： rake db：structure：dump ）。

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).

大对象读取/写入/检查长度/删除

复制了一些修改以澄清等： https://github.com /diogob/carrierwave-postgresql/blob/v0.1.0/lib/carrierwave/storage/postgresql_lo.rb ：

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

已更新 ：我们可以但不必在lo_read / lo_write / lo_lseek之前放置一个开始，并在确保块中进行lo_close，因为根据 PG文档交易结束时仍保持开放状态将自动关闭。 （感谢Diogo提供该信息）

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

而不是 connectio n ，使用模型或基础中的原始连接，例如 ActiveRecord :: Base.connection.raw_connection （请参见此）。

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

（...）。事务正在调用模型上的交易或基地，例如 ActiveRecord :: Base.transaction （请参见此）。

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

标识符是您要么需要传入/只需执行 connection.lo_creat 即可设置或获取。

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

其他示例/信息：

• http：// rubydoc.info/github/nedforce/devcms-core/DbFile


• https://github.com/nedforce/devcms-core


• http://my.safaribooksonline.com/book/web-development/ruby/9780596510329/database/largebinary_objects

• http://rubydoc.info/github/nedforce/devcms-core/DbFile
• https://github.com/nedforce/devcms-core
• http://my.safaribooksonline.com/book/web-development/ruby/9780596510329/database/largebinary_objects

后者和一些答案此处建议您可能要考虑将大文件与数据库分开存储，例如以便您可以使用云存储。但是，如果仅将路径/ ID存储到数据库未管理的外部文件中，则会失去ACID一致性（一个或多个数据库记录可能指向一个或多个不存在的文件，或者可能存在一个或多个在数据库中没有一个或多个相关记录的文件）。在文件系统上存储文件的另一个论据是，您可以流式传输文件，但是PG大对象通过postgres管理的方式将文件存储在文件系统上，以确保ACID一致性并允许流式传输（这是普通BLOB不能做到的） / Rails二进制类型）。因此，这取决于有些人发现使用路径引用存储在单独的存储中是一个更好的选择，而有些人则希望通过大对象来实现ACID一致性。

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.

简便方法

只需使用 CarrierWave 和 carrierwave-postgresql 。

这篇关于在Rails中为PostgreSQL的大型对象建模的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！