Change starting id number

I have an 'Account' model in Rails with its corresponding 'accounts' table in the database. If I wipe the database and start over, the 'account_id' field will always start at 1 and count up from there. I would like to change the starting number, so that, when the开发者_运维技巧 very first account is created in a fresh database, the 'account_id' is, say, 1000. Is there a way to do that in Rails, or do I need specialized database-dependent SQL code?

For the sake of illustration, here is a simplified version of my 'accounts' table:

create_table "accounts", :force => true do |t|
  t.string   "email", :null => false
  t.string   "crypted_password", :null => false
  t.string   "name", :null => false
  t.boolean  "email_verified", :default => false
end

for PostgreSQL:

execute("ALTER SEQUENCE accounts_id_seq START with 1000 RESTART;")

see https://www.postgresql.org/docs/current/static/sql-altersequence.html

You'll need to do some specialized database-dependent SQL to get this functionality.

If you're using MySQL, you can add the following code to your migration after the create_table code:

execute("ALTER TABLE tbl AUTO_INCREMENT = 1000")

For sqlite

sequences are stored in the table sqlite_sequence (name,seq)

• Check first if the sequence already exists?

  select name,seq from sqlite_sequence where name = 'accounts'

if sequence.empty?

insert into sqlite_sequence(name,seq) values('accounts', 1000);

else

update sqlite_sequence set seq = 1000 where name = 'accounts';

A pure Ruby, database-independent approach could be:

class MyModel
  before_create do
    self.id = [1000, (self.class.maximum(:id) || 0) + 1].max if self.id.nil?
  end
end

When you're creating lots of records at once, this may not perform so well though.

Another possible concept might be to simply use a start_at variable in your model file?

Such as define a base number such as start_at = 53131 and then... Make an accessor method (could call it "key") which adds your start_at number to your database's real ID before returning it.

And you could make a attr writer method that subtracts the start_at before saving the key, that may not even be necessary depending on your implementation.

Example in pseudo-code so bear with me.

class FakeModel
  attr_accessible :name
  start_at = 53121

  def self.find_by_key(key)
    find_by_id(key-start_at))
  end

  def key
    (self.id+start_at)
  end
end

Not sure how practical this is or if it would even work 100% but at least you wouldn't have to modify the database to handle it.

in SQL Server:

execute('DBCC CHECKIDENT (accounts, reseed, 1000)')

In my case, the development environment and the production environment are using different type of database.

This code block will run the relevant execution accordin to DB type - just put it in the relevant migration:

puts 'Migration trys to set initial account ID to adapter:' + ActiveRecord::Base.connection.adapter_name
case ActiveRecord::Base.connection.adapter_name
  when 'MySQL'
    execute('ALTER TABLE accounts AUTO_INCREMENT = 1000')
  when 'SQLServer'
    execute('DBCC CHECKIDENT (accounts, reseed, 1000)')
  when 'SQLite'
    begin
      execute('insert into sqlite_sequence(name,seq) values(\'accounts\', 1000);')
    rescue
      puts 'insert error... updating'
    end
    execute('update sqlite_sequence set seq = 1000 where name = \'accounts\';')
  else
    puts "cant recognize the database"
end