Rails迁移：PostgreSQL的md5随机字符串为默认值 [英] rails migration: postgresql for md5 of random string as default

问题描述

Rails 3 + postgresql

Rails 3 + postgresql

我想为一列默认值设置一个随机字符串的阴影。

I want to have a sha of a random string for a default value of a column.

所以，在我的迁移中，我有：

So, in my migration I have:

t.string：uniqueid，默认值：md5（random（）:: text）

但是我无法真正产生任何东西，我使用了反引号，引号等从我看到的示例中，似乎pg函数仅在 SELECT 语句中起作用。

However i can not get this to actually produce anything, I've used backticks, quotes,etc. From examples that I've seen it seems like that pg function only works in a SELECT statement.

准确？关于如何实现此目标的任何想法？

Is that accurate? Any ideas on how I could achieve this?

谢谢

推荐答案

Rails将尝试解释这一点：

Rails will try to interpret this:

t.string :uniqueid, default: md5(random()::text)

为Ruby代码和：default => md5（...）在Ruby中没有任何意义。如果您引用它，那么Rails会认为它是一个字符串，并将 uniqueid 的默认值设置为字符串'md5（random（）:: text ）'，这无济于事。

as Ruby code and :default => md5(...) doesn't mean anything in Ruby. If you quote it, then Rails will think it is a string and make the default value for uniqueid the string 'md5(random()::text)' and that's not going to help.

您的问题是Rails无法理解复杂的事情，例如使用函数作为列默认值，Rails具有一种奇怪且被误导的观念，即您应该在Rails中而不是在数据库中执行所有操作。如果要在默认列中使用函数调用，则可以执行 更改表 ：

Your problem is that Rails doesn't understand complicated things like using a function as a column default, Rails has the bizarre and misguided notion that you should do everything in Rails rather than inside the database. If you want to use a function call in a column default, you can do the alter table by hand:

connection.execute(%q{
    alter table your_table alter column uniqueid set default md5(random()::text)
})

这将为您提供数据库中所需的默认值，但您可能会注意到 schema.rb 。如果您想要一个可用的架构，那么您就必须将Rails推开，并使用 SQL架构，而不是将这在您的 application.rb ：

That will get you the default you want in the database but you might notice that there's no mention of the new default in your schema.rb. If you want a usable schema then you'll have to, of course, push Rails out of the way and use an SQL schema instead by putting this in your application.rb:

config.active_record.schema_format = :sql

请注意，SQL模式转储在3.2之前已被破坏，并且存在模式加载问题在各种Rails版本中（但是您始终可以 psql< structure.sql 来解决）。然后删除您的 schema.rb 并使用 structure.sql 。从好的方面来看，SQL模式转储将跟踪真正的外键，检查约束，触发器等花哨的事物……

Note that SQL schema dumps were broken until 3.2 and there are schema loading problems in various Rails versions (but you can always psql < structure.sql your way around that). Then delete your schema.rb and use structure.sql instead. On the upside, SQL schema dumps will keep track of fancy things real foreign keys, check constraints, triggers, ...

BTW，如果您真的想要SHA，那么您可以想要查看 摘要来自 pgcrypto 的函数。

BTW, if you really want SHA then you'll want to look at the digest function from pgcrypto.

当然，这都不起作用，因为ActiveRecord太笨了，无法将它无法理解的事情搁置一旁。 ActiveRecord解析从数据库获取的表定义时会看到默认值，但它无法理解，因此会忽略它。然后，在INSERT期间，ActiveRecord似乎坚持为所有列（甚至是您未分配值的列）提供值。结果是，即使您已在数据库中附加了一个明智的默认值，您在 uniqueid 中仍将为NULL。

Of course none of this will work because ActiveRecord is too dumb to leave things alone that it doesn't understand. ActiveRecord will see the default value when it parses the table definition it gets from the database but it won't understand it so it will ignore it. Then, during the INSERT, ActiveRecord seems to insist on supplying values for all the columns, even columns that you didn't assign values to. The result is that you'll end up with a NULL in uniqueid even though you have attached a sensible default value in the database.

那么，如何解决呢？与ActiveRecord一样，您假装数据库是一个愚蠢的电子表格，并且可能使用 before_create 钩子在Ruby中进行所有操作。或者，您可以找到比ActiveRecord更少的数据库恶意ORM；我不确定其他ORM能否很好地处理非恒定列默认值。

So, how do you get around that? As usual with ActiveRecord, you pretend that the database is a stupid spreadsheet and do everything in Ruby using, probably, a before_create hook. Or you could find a less database-hostile ORM than ActiveRecord; I'm not sure how well the other ORMs will handle non-constant column defaults though.

或者，您可以尝试编写一个BEFORE INSERT触发器来分配默认值如果 NEW.uniqueid IS NULL 则返回列。

Alternatively, you could try writing a BEFORE INSERT trigger that would assign the default value to the column if NEW.uniqueid IS NULL.

这篇关于Rails迁移：PostgreSQL的md5随机字符串为默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！