How do I use a Rails ActiveRecord migration to insert a primary key into a MySQL database?

I need to create an AR migration for a table of image files. The images are being checked into the source tree, and should act like attachment_fu files. That being the case, I'm creating a hierarchy for them under /public/system.

Because of the way attachment_fu generates links, I need to use the directory naming convention to insert primary key values. How do I override the auto-increment in MySQL as well as any Rails magic so that I can do something like this:

image = Image.create(:id => 42, :filename => "foo.jpg")
image.id #=> 42

From stackoverflow

• Unlike, say Sybase, in MySQL if you specify the id column in the insert statement's column list, you can insert any valid, non-duplicate value in the id. No need to do something special.

  I suspect the rails magic is just to not let rails know the id is auto-increment. If this is the only way you'll be inserting into this table, then don't make the id auto_increment. Just make in an int not null primary key.

  Though frankly, this is using a key as data, and so it makes me uneasy. If attachment_fu is just looking for a column named "id", make a column named id that's really data, and make a column named "actual_id" the actual, synthetic, auto_incremented key.

  Terry Lorber : Unfortunately, I don't have the time to replace attachment_fu. I'd agree that using the primary key field to construct a filepath is a little iffy.

  Terry Lorber : I think I'm running up against Rails magic, not MySQL magic.

• Here's my kluge:

  class AddImages < ActiveRecord::Migration
    def self.up
      Image.destroy_all
  
      execute("ALTER TABLE images AUTO_INCREMENT = 1")
  
      image = Image.create(:filename => "foo.jpg")
      image.id #=> 1
    end
  
    def self.down
    end
  end
  

  Don Werve : This looks to do exactly what the default primary key does... what's the difference in functionality?

  Terry Lorber : The create() method doesn't use the value of ":id => ##" if it's given. While I used the kluge above, I think the right answer is to let the AR class do it work, as @Sarah Mei suggests.

• Yikes, not a pleasant problem to have. The least-kludgy way I can think of to do it is to have some code in your migration that actually "uploads" all the files through attachment-fu, and therefore lets the plugin create the IDs and place the files.

  Something like this:

  Dir.glob("/images/to/import/*.{jpg,png,gif}").each do |path|
  
    # simulate uploading the image
    tempfile = Tempfile.new(path)
    tempfile.set_encoding(Encoding::BINARY) if tempfile.respond_to?(:set_encoding)
    tempfile.binmode
    FileUtils.copy_file(path, tempfile.path)
  
    # create as you do in the controller - may need other metadata here
    image = Image.create({:uploaded_data => tempfile})
    unless image.save
      logger.info "Failed to save image #{path} in migration: #{image.errors.full_messages}"
    end
  
    tempfile.close!
  end
  

  A look at attachment-fu's tests might be useful.

  Terry Lorber : I didn't do this, but I think this is the right way to do it... let the code do it's thing and don't try to subvert it.

• I'm not entirely sure I understand why you need to do this, but if you only need to do this a single time, for a migration, just use execute in the migration to set the ID (assuming it's not already taken, which I can't imagine it would be):

  execute "INSERT INTO images (id, filename) VALUES (42, 'foo.jpg')"

• I agree with AdminMyServer although I believe you can still perform this task on the object directly:

  image = Image.new :filename => "foo.jpg"
image.id = 42
image.save


  You'll also need to ensure your id auto-increment is updated at the end of the process to avoid clashes in the future.

  newValue = Images.find(:first, :order => 'id DESC').id + 1
execute("ALTER TABLE images AUTO_INCREMENT = #{newValue}")

  Hope this helps.

• image = Image.create(:filename => "foo.jpg") { |r| r.id = 42 }