如何访问使用ActiveRecord的一个Postgres列缺省值？ [英] How can I access a Postgres column default value using ActiveRecord?

问题描述

我试图访​​问一个列的默认值在我的Postgres 9.2数据库。通过使用原始的SQL，我可以验证列默认为users_next_id（）：

I'm trying to access a column's default value in my Postgres 9.2 database. By using raw SQL, I can verify that the column default is "users_next_id()":

> db = ActiveRecord::Base.connection
> db.execute("SELECT table_name,column_name,column_default 
FROM information_schema.columns 
WHERE table_name = 'users' and column_name ='id'").first

=> {"table_name"=>"users",
 "column_name"=>"id",
 "column_default"=>"users_next_id()"}

但是当我使用AR的'列'的方法，默认值似乎是零：

But when I use AR's 'columns' method, the default value appears to be nil:

[26] pry(main)> db.columns('users')[0]=> #<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x007feb397ba6e8
 @coder=nil,
 @default=nil,
 @limit=8,
 @name="id",
 @null=false,
 @precision=nil,
 @primary=nil,
 @scale=nil,
 @sql_type="bigint",
 @type=:integer>

这是不会造成任何问题（比困惑我除外），但是这是预期的行为？我是否做出不正确的假设，关于列的方法？

This isn't causing any problems (other than confusing me), but is this expected behavior? Am I making an incorrect assumption about the 'columns' method?

推荐答案

在ActiveRecord的需要知道它类似于您 INFORMATION_SCHEMA 查询，但AR将一个查询表经过<一个href="https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L1285"相对=nofollow> PostgreSQL特有的系统表来代替：

When ActiveRecord needs to know about a table it does a query similar to your information_schema query but AR will go through the PostgreSQL-specific system tables instead:

  SELECT a.attname, format_type(a.atttypid, a.atttypmod),
         pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
    FROM pg_attribute a LEFT JOIN pg_attrdef d
      ON a.attrelid = d.adrelid AND a.attnum = d.adnum
   WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass
     AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

搜索<一href="https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb"相对=nofollow> PostgreSQL的适配器源以regclass的，你会看到，AR将使用找出表的结构的一些其他问题。

Search the PostgreSQL adapter source for "regclass" and you'll see some other queries that AR will use to figure out the table's structure.

的<一个href="http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE"相对=nofollow> pg_get_expr 在上面的查询调用就是列的缺省值来自

The pg_get_expr call in the above query is where the column's default value comes from.

这查询的结果去，或多或少，<一个href="https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L853"相对=nofollow>直接进入 PostgreSQLColumn.new ：

The results of that query go, more or less, straight into PostgreSQLColumn.new:

def columns(table_name, name = nil)
  # Limit, precision, and scale are all handled by the superclass.
  column_definitions(table_name).collect do |column_name, type, default, notnull|
    PostgreSQLColumn.new(column_name, default, type, notnull == 'f')
  end
end

的<一个href="https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L36"相对=nofollow> PostgreSQLColumn 构造将使用<一个href="https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L134"相对=nofollow> extract_value_from_default 以红宝石IFY的默认值;在<一个href="https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L181"相对=nofollow>的开关 在 extract_value_from_default 结束有趣的是，在这里：

The PostgreSQLColumn constructor will use extract_value_from_default to Ruby-ify the default; the end of the switch in extract_value_from_default is interesting here:

else
  # Anything else is blank, some user type, or some function
  # and we can't know the value of that, so return nil.
  nil

因此​​，如果默认值绑定到一个序列（其中 ID PostgreSQL中列会），那么默认会在数据库作为一个函数调用与此类似：

So if the default value is bound to a sequence (which an id column in PostgreSQL will be), then the default will come out of the database as a function call similar to this:

nextval('models_id_seq'::regclass)

这将结束在上述其他分支 column.default.nil？将是真实的。

That will end up in the above else branch and column.default.nil? will be true.

对于 ID 列，这是不是一个问题，AR预计该数据库提供的值 ID 列，以便它不关心的默认值是什么。

For an id column this isn't a problem, AR expects the database to supply the values for id columns so it doesn't care what the default value is.

这是一个很大的问题，如果列的默认的东西，AR不理解，说一个函数调用，如 MD5（随机（）::文） 。问题是，AR将初始化所有属性为默认值 - 如 Model.columns 看见他们，而不是作为数据库看到他们 - 当你说 Model.new 。例如，在控制台中，你会看到这样的事情：

This is a big problem if the column's default is something that AR doesn't understand, say a function call such as md5(random()::text). The problem is that AR will initialize all the attributes to their default values – as Model.columns sees them, not as the database sees them – when you say Model.new. For example, in the console you'll see things like this:

 > Model.new
=> #<Model id: nil, def_is_function: nil, def_is_zero: 0>

因此​​，如果 def_is_function 实际使用函数调用作为它的默认值，AR会忽略这一点，尝试插入NULL作为该列的值。这NULL将prevent的默认值被使用，你会结束了一个混乱的烂摊子。默认值是AR可以理解（如字符串和数字）工作得很好，但。

So if def_is_function actually uses a function call as its default value, AR will ignore that and try to insert a NULL as that column's value. That NULL will prevent the default value from being used and you'll end up with a confusing mess. Defaults that AR can understand (such as strings and numbers) work just fine though.

其结果是，你不能真正通过ActiveRecord的回调的一个用不平凡的缺省列值与ActiveRecord的，如果你想要一个不平凡的价值，那么你必须做的红宝石（如 before_create ）。

The result is that you can't really use non-trivial default column values with ActiveRecord, if you want a non-trivial value then you have to do in Ruby through one of the ActiveRecord callbacks (such as before_create).

国际海事组织它会好得多，如果AR将默认值到数据库，如果它不理解他们，让他们出的INSERT或使用默认的价值观会产生更好的结果; AR会，当然，需要重新加载，以获得所有适当的默认值从数据库中新创建的对象，但你只需要重新加载，如果有违约的AR不明白。如果其他在 extract_value_from_default 用一个特殊的我不知道这是什么意思的标志，而不是零然后是我需要后第一次重新加载此对象保存状态将是微不足道的检测和你只会重新加载必要的时候。

IMO it would be much better if AR left the default values up to the database if it didn't understand them: leaving them out of the INSERT or using DEFAULT in the VALUES would produce much better results; AR would, of course, have to reload newly created objects from the database in order to get all the proper defaults but you'd only need the reload if there were defaults that AR didn't understand. If the else in extract_value_from_default used a special "I don't know what this means" flag instead of nil then the "I need to reload this object after the first save" condition would be trivial to detect and you'd only reload when necessary.

以上是PostgreSQL特有的，但这个过程应该是类似的其他数据库;不过，我不作任何保证。

The above is PostgreSQL-specific but the process should be similar for other databases; however, I make no guarantees.

这篇关于如何访问使用ActiveRecord的一个Postgres列缺省值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！