Rails 4. Counter for the unique ID

Question

I need to generate a unique number for my model, which is called Shipment. The number must be in the following format:

• Two digits - current month
• Two digits - current year
• Six digits - just digits

For instance: 1114000001. 11 - current month. 14 - year. 0000001 - just six digits.

So. My idea is to use the counter for 6 digits. I want store it in database, then I can use the lock and ensure uniqueness. But then it is obvious that it is a potential bottleneck. Because each new shipment will wait in line to access to counter.

My question is: What better? Use counter or generate a random number, and query the database:

while Shipment.where(:id => supposedly_unique_number.).exists?

And one more question: Where do I store this Сounter? I need only one for whole application. Create standalone table for only one value?

UPDATE

I just realized that this check Shipment.where(:id => supposedly_unique_number.).exists? does not guarantee uniqueness. :)

So where better to store the counter? I will be glad to any of your suggestions.

UPDATE2

mu is too short, mentioned database sequence. And I can`t find understandable information how to use it. I would be grateful if someone explain to me how it works.

Rails 4, Ruby 2, PostgreSql.

Answer 1

There are a couple things you need to consider here:

1. Your ids will have leading zeros so they're not numbers, they're strings that contain digit characters.
2. You should let the database take care of the counter. Anything you try to do in Ruby will be a nasty mess of kludges and race conditions. Similarly, you should let the database take care of building the id.

Using a string for the id is a bit of work to keep ActiveRecord from thinking it owns you but not that bad. You need to create the table without the implicit id that create_table adds, add your own id, and set it as the primary key by hand:

create_table :shipments, :id => false do |t|
  # Don't bother with `t.string` when using PostgreSQL, it is pointless.
  t.text :id, :null => false
  ...
end
connection.execute('alter table shipments add primary key (id)')

Now we need to hook up a default value so that the database can generate our ids. Getting the date prefix is a simple of matter of using the to_char SQL function to format the current time (now()):

to_char(now(), 'MMYY')

To create the suffix, we can use a sequence. A database sequence is just database object that returns incrementing numbers in a safe way in the presence of multiple database connections. First, create the sequence:

connection.execute('create sequence shipments_id_seq owned by shipments.id')

To get the next value from a sequence we use the nextval SQL function:

nextval(regclass)
Advance sequence and return new value

and again use to_char to format it:

to_char(nextval('shipments_id_seq'), 'FM000000')

The linked document explains what the FM000000 format means. Combining those two parts of the id gives us a default value of:

to_char(now(), 'MMYY') || to_char(nextval('shipments_id_seq'), 'FM000000')

Note that || is string concatenation in SQL. Wrapping that in another connection.execute lets us attach the default to the id column:

connection.execute(%q{
  alter table shipments
  alter column id
  set default to_char(now(), 'MMYY') || to_char(nextval('shipments_id_seq'), 'FM000000')
})

If you want to reset the counters at the beginning of each month (or when you get close to the six digit limit), you can use the setval SQL function.

Finally, since you're using all kinds of things that ActiveRecord doesn't understand, you'll want to switch from schema.rb to structure.sql for managing your schema information. You can do this in your configs by setting:

config.active_record.schema_format = :sql

and you'll use rake db:structure:dump and db:structure:load instead of the schema tasks.

---

Putting all that together will give you a migration like this:

def up
  create_table :shipments, :id => false do |t|
    # Don't bother with `t.string` when using PostgreSQL, it is pointless.
    t.text :id, :null => false
    ...
  end
  connection.execute('alter table shipments add primary key (id)')
  connection.execute('create sequence shipments_id_seq owned by shipments.id')
  connection.execute(%q{
    alter table shipments
    alter column id
    set default to_char(now(), 'MMYY') || to_char(nextval('shipments_id_seq'), 'FM000000')
  })
end