There are a couple things you need to consider here:
- Your
ids will have leading zeros so they're not numbers, they're strings that contain digit characters.
- 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
