Ruby-on-rails 在 rails 中生成一个自增字段

Generate an auto increment field in rails

提问by Wahaj Ali

I have a model Token, which has a field token_number that I need to auto increment (starting from 1001), if and only if the user does not provide it. The problem is that, since the user has the option to provide this field, I can't exactly query the database and ask for the largest token_number. I found one answer on this forum, but I'm quite certain there has to be a better way to do it than to execute an SQL statement? Auto increment a non-primary key field in Ruby on Rails

我有一个模型令牌，它有一个字段 token_number 我需要自动递增（从 1001 开始），当且仅当用户不提供它时。问题在于，由于用户可以选择提供此字段，因此我无法准确查询数据库并要求提供最大的 token_number。我在这个论坛上找到了一个答案，但我很确定必须有比执行 SQL 语句更好的方法来做到这一点？在 Ruby on Rails 中自动增加非主键字段

回答by Syed Aslam

Interesting question for me. Unfortunately, rails doesn't provide a way to auto-increment columns, so we must resort to SQL with little automation. I tried this in Rails 3.0.7 using PostgreSQL as my database and it works and hope this will be useful:

对我来说有趣的问题。不幸的是，rails 没有提供自动增加列的方法，所以我们必须求助于几乎没有自动化的 SQL。我在 Rails 3.0.7 中使用 PostgreSQL 作为我的数据库尝试了这个，它可以工作，希望这会有用：

Creating sequence for token_number PGSql Documentation

为 token_number 创建序列PGSql 文档

class CreateTokens < ActiveRecord::Migration

  def self.up
    create_table :tokens do |t|
      t.string :name
      t.integer :token_number

      t.timestamps
    end

    execute "CREATE SEQUENCE tokens_token_number_seq START 1001"
  end

  def self.down
    drop_table :tokens

    execute "DROP SEQUENCE tokens_token_number_seq"
  end
end

Now, since there is a possibility of token_number being set by the user manually, we'll need to generate the token_number only if it is not being set. Read about Callbacks here. With that we have,

现在，由于用户可能会手动设置 token_number，因此只有在未设置的情况下，我们才需要生成 token_number。在此处阅读回调。有了我们，

class Token < ActiveRecord::Base
  # Generate the sequence no if not already provided.
  before_validation(:on => :create) do
    self.application_no = next_seq unless attribute_present?("application_no")
  end

  private
    def next_seq(column = 'application_no')
      # This returns a PGresult object [http://rubydoc.info/github/ged/ruby-pg/master/PGresult]
      result = Token.connection.execute("SELECT nextval('tokens_token_number_seq')")

      result[0]['nextval']
    end 
end

A sample run. Please note that for the first token I am not setting token_number and it generates the token_number sequence and for the second I am assigning.

一个示例运行。请注意，对于第一个令牌，我没有设置 token_number，它会生成 token_number 序列，而对于我正在分配的第二个令牌。

token = Token.new
# => #<Token id: nil, name: nil, token_number: nil, created_at: nil, updated_at: nil> 

token.save
  SQL (0.8ms)  BEGIN
  SQL (1.7ms)  SELECT nextval('tokens_token_number_seq')
  SQL (6.6ms)   SELECT tablename
 FROM pg_tables
 WHERE schemaname = ANY (current_schemas(false))

  SQL (33.7ms)  INSERT INTO "tokens" ("name", "token_number", "created_at", "updated_at") VALUES (NULL, 1001, '2012-03-02 12:04:00.848863', '2012-03-02 12:04:00.848863') RETURNING "id"
  SQL (15.9ms)  COMMIT
# => true 

token = Token.new
# => #<Token id: nil, name: nil, token_number: nil, created_at: nil, updated_at: nil> 

token.token_number = 3000
# => 3000 

token.save
  SQL (0.8ms)  BEGIN
  SQL (1.5ms)  INSERT INTO "tokens" ("name", "token_number", "created_at", "updated_at") VALUES (NULL, 3000, '2012-03-02 12:04:22.924834', '2012-03-02 12:04:22.924834') RETURNING "id"
  SQL (19.2ms)  COMMIT
# => true 

回答by buncis

another alternatives is set nextval as the default value in your postgresql

另一个选择是将 nextval 设置为 postgresql 中的默认值

this make us not need to write callbacks

这使我们不需要编写回调

class CreateTokens < ActiveRecord::Migration

  def self.up
    create_table :tokens do |t|
      t.string :name
      t.integer :token_number

      t.timestamps
    end

    execute "CREATE SEQUENCE tokens_token_number_seq START 1001"
    execute "ALTER TABLE tokens ALTER COLUMN token_number SET DEFAULT NEXTVAL('tokens_token_number_seq')"
  end

  def self.down
    drop_table :tokens

    execute "DROP SEQUENCE tokens_token_number_seq"
  end
end