按字母顺序创建递增

实际上,在Postgres中有一种非常酷的方法可以做到这一点,即对列应用计算出的默认值。

首先我们要定义一个Postgres函数,它给你一个给定整数(1=a,26=Z)的字母,这样我们就可以得到一个给定ID的字母。

class CreateIdToLetterFunction < ActiveRecord::Migration[5.2]
  def up
    execute <<-SQL
    CREATE OR REPLACE FUNCTION id_to_letter(integer) RETURNS varchar
      AS 'select chr(64 + $1)' 
      LANGUAGE SQL
      IMMUTABLE
      RETURNS NULL ON NULL INPUT;
    SQL
  end

  def down
    execute <<-SQL
      DROP FUNCTION id_to_letter(integer);
    SQL
  end
end

chr(int) 返回具有给定ASCII码的字符。在ASCII中,A是65,所以我们需要用64填充值。

class AddExposureCountFunction < ActiveRecord::Migration[5.2]
  def up
    execute <<-SQL
      CREATE OR REPLACE FUNCTION count_exposures() RETURNS bigint
        AS 'select count(*) from exposures'
        LANGUAGE SQL
        VOLATILE;
    SQL
  end

  def down
    execute <<-SQL
      DROP FUNCTION count_exposures();
    SQL
  end
end

然后我们要更改exposures.setup\u code列以添加默认值。

class AddDefaultSetupCodeToExposures < ActiveRecord::Migration[5.2]
  def change
    change_column_default(
      :exposures,
      :setup_code,
      from: nil,
      to: -> {"id_to_letter(CAST(count_exposures() AS integer) + 1)"}
    )
  end
end

我们将新的默认值包装在lambda中( ->{} )因为这告诉ActiveRecord该值不是文本值,应该作为SQL添加到语句中。

由于这是在DB级别处理的,因此不需要额外的模型代码。请注意,有关DB设置的默认值的警告适用—在从DB重新加载实例之前,该值将为零。

补充

但是,如果您希望在功能上与您的注释中的糟糕代码相同,您只需要:

class Exposure < ApplicationRecord
  SETUP_CODES = %w{ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z A2 B2 C2 D2 E2 F2 G2 H2 }
  def self.next_setup_code(current_code)
    if !SETUP_CODES.index(current_code) || current_code == SETUP_CODES.last
      "error"
    else
      SETUP_CODES[ SETUP_CODES.index(current_code) + 1 ]
    end 
  end
end

首先创建自动递增列:

class AddSetupCodeToExposures < ActiveRecord::Migration[5.2]
  def change
    add_column :exposures, :setup_code, :serial
  end
end

您可以使用ID列,因为它是自动递增的。

在Ruby中,将整数映射到字母表的字母非常简单:

irb(main):008:0> ALPHABET = ('A'..'Z').to_a.unshift(nil)
=> [nil, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]
irb(main):009:0> ALPHABET[1]
=> "A"
irb(main):010:0> ALPHABET[26]
=> "Z"

在模型中,我们可以通过使用自定义setter和getter来处理值的转换:

class Exposure < ApplicationRecord
  # This creates a array of the 24 ASCII letters A..Z
  # adding null to the beginning lets us treat it as a 1 indexed array
  ALPHABET = ('A'..'Z').to_a.unshift(nil)

  def setup_code
    # the default value here handles out of range values
    self.class.integer_to_letter(super || 0) || "default_value"
  end

  def setup_code=(value)
    super self.class.integer_to_letter(value)
  end

  def self.integer_to_letter(integer)
    ALPHABET[integer]
  end

  def self.letter_to_integer(letter)
    ALPHABET.index(letter)
  end
end

自动递增列和数据库默认值的一个问题是,插入记录时不填充列:

irb(main):005:0> e = Exposure.create
   (0.2ms)  BEGIN
  Exposure Create (0.7ms)  INSERT INTO "exposures" ("created_at", "updated_at") VALUES ($1, $2) RETURNING "id"  [["created_at", "2018-09-27 12:26:24.672016"], ["updated_at", "2018-09-27 12:26:24.672016"]]
   (0.6ms)  COMMIT
=> #<Exposure id: 3, created_at: "2018-09-27 12:26:24", updated_at: "2018-09-27 12:26:24", setup_code: nil>
irb(main):006:0> e.setup_code
=> "default_value"
irb(main):007:0> e.reload
  Exposure Load (0.7ms)  SELECT  "exposures".* FROM "exposures" WHERE "exposures"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
=> #<Exposure id: 3, created_at: "2018-09-27 12:26:24", updated_at: "2018-09-27 12:26:24", setup_code: 3>
irb(main):008:0> e.setup_code
=> "C"

因为ActiveRecord只返回 id 插入时的列。

将现有列更改为序列

这并不会真正改变列—而是使用旧的重命名技巧创建新列并将旧值移动到新列。

# rename the old column
class RenameExposuresSequenceCode < ActiveRecord::Migration[5.2]
  def change
    rename_column :exposures, :setup_code, :old_setup_code
  end
end

# add the new column
class AddSequencedSetupCodeToExposures < ActiveRecord::Migration[5.2]
  def change
    add_column :exposures, :setup_code, :serial
  end
end

# convert the existing values 
class ConvertOldSetupCodes < ActiveRecord::Migration[5.2]
  def up
    Exposure.find_each do |e|
      converted_code = Exposure.letter_to_integer(e.old_setup_code)
      e.update_attribute(setup_code: converted_code) if converted_code
    end
  end

  def down
    Exposure.find_each do |e|
      converted_code = Exposure.integer_to_letter(e.setup_code)
      e.update_attribute(old_setup_code: converted_code) if converted_code
    end
  end
end

# remove the old column
class RemoveOldSetupCodeFromExposures < ActiveRecord::Migration[5.2]
  def change
    remove_column :exposures, :old_setup_code, :string
  end
end

class Exposure < ApplicationRecord
  NO_FORMAT = "A"
  before_create :create_setup_code

  def next_setup_code
    (Exposure.count > 0 ? Exposure.last.setup_code : Exposure::NO_FORMAT).succ
  end

  def create_setup_code
    self.setup_code = next_setup_code
  end
end