Topic: setting sequence start

We're porting an older app to rails and want to bring as much of the old data with us as possible. As I've examined the problem I see that the easiest thing to do would be to bring the old data over as directly as possible, keeping the old ID values since converting them all could be very time consuming to code and debug. I'd like to run my migration, import the old data and then have any new objects created use sequence values ABOVE those used in the imported data. The issue I have is that I can't figure out how to set the starting value of the sequences in rails. In SQL I'd do something like:

CREATE SEQUENCE my_seq START WITH 50000 INCREMENT BY 1;

and ideally it would work with the migrations. Is this possible? If not, any ideas how to bake this in? I really like the idea of migrations and would like to stick with them.

--fgk

Re: setting sequence start

See the "options" parameter of create_table. I believe you can supply an option to do this. What the option should be depends largely on your database engine.

Railscasts - Free Ruby on Rails Screencasts

Re: setting sequence start

Yeah, that's the first place I looked, but the option only appends to the end of the ''CREATE TABLE (...) '' SQL statement. I couldn't find anything in the MySQL (or postgres) documentation that allows setting the start value of a sequence. I suppose I can try using execute in a migration, not as clean as I'd like but it can't hurt to give it a try.

--fgk

Re: setting sequence start

I think MySQL supports it in the OPTIONS section. Try this: "AUTO_INCREMENT=101". Of course replace 101 with whatever you want the first row to be.

Railscasts - Free Ruby on Rails Screencasts

Re: setting sequence start

Great, that does the trick! I wish I had something a bit more database agnostic, but this will solve the problem. Thanks!

--fgk