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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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')"

Answer 5

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.

Answer 6

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