How to assign a non-auto ID?

ssk · December 11, 2007, 2:38am

Hi,

I am converting data from an old PHP application to a new Rails
application.
Instead of directly throwing “insert…” statements to MySQL, I’m using
ActiveRecord models.
As you know, id field is automatically set.
How can I keep the old id when I convert data through ActiveRecord?

I want to keep the same id for relations so that I don’t have to keep
track of new id for foreign keys.

If you think I’m doing it wrong, can you suggest a good way?

Thanks.
Sam

ssk · December 11, 2007, 2:46am

Hi Sam, if you can simply reuse your old MySQL database that have
existing
id fields, you shouldn’t have to convert anything.

Good luck,

-Conrad

ssk · December 11, 2007, 2:50am

Hi,

Conrad T. wrote:

Hi Sam, if you can simply reuse your old MySQL database that have
existing
id fields, you shouldn’t have to convert anything.

Good luck,

-Conrad

I wish it was the case.
Unfortunately, I need to modify some structures and column names.

Thanks.
Sam

ssk · December 11, 2007, 3:31am

There is an add_column thing you can do with migrations.

You do not need to rewrite your whole database.

On Dec 11, 2007 12:20 PM, Sam K. [email protected]
wrote:

-Conrad

–
Ryan B.

ssk · December 11, 2007, 4:37am

So you’re going and doing Model.new(object) for each object?

What’s wrong with leaving the records the way they are? ActiveRecord
does
not change the ID of the objects already in database tables.

On Dec 11, 2007 12:08 PM, Sam K. [email protected]
wrote:

I want to keep the same id for relations so that I don’t have to keep

–
Ryan B.

ssk · December 11, 2007, 5:02am

Hi,

Ryan B. wrote:

So you’re going and doing Model.new(object) for each object?

What’s wrong with leaving the records the way they are? ActiveRecord
does
not change the ID of the objects already in database tables.

On Dec 11, 2007 12:08 PM, Sam K. [email protected]
wrote:

I want to keep the same id for relations so that I don’t have to keep

–
Ryan B.
http://www.frozenplague.net

Hmmm, I think everyone has an opinion against my idea.
Normally, I would just dump data.
But in this specific case, the conversion rule is very complex.
Using ActiveRecord helps a lot as it has validations and associations.

Thanks.

Sam

ssk · December 11, 2007, 6:43am

Ok…

You can save the record and then afterwards specify its ID and save it
again.

On Dec 11, 2007 2:32 PM, Sam K. [email protected]
wrote:

On Dec 11, 2007 12:08 PM, Sam K. [email protected]
But in this specific case, the conversion rule is very complex.
Using ActiveRecord helps a lot as it has validations and associations.

Thanks.

Sam

Posted via http://www.ruby-forum.com/.

–
Ryan B.

ssk · December 11, 2007, 7:01am

Ryan B. wrote:

Ok…

You can save the record and then afterwards specify its ID and save it
again.

Do you mean something like this?

user = User.find(1)
user.id = 2
user.save

Actually, I tried this but the id is not changed.

Did you mean directly modifying the id in SQL?
It will work but I want to know if there’s a way in ActiveRecord way.

Sam

ssk · December 11, 2007, 7:19am

Switch the save and the id method calls around and you have it.

On Dec 11, 2007 4:33 PM, Ryan B. [email protected] wrote:

again.
It will work but I want to know if there’s a way in ActiveRecord way.

Ryan B.
http://www.frozenplague.net

–
Ryan B.

ssk · December 11, 2007, 7:18am

On Dec 11, 2007 4:31 PM, Sam K. [email protected]
wrote:

user.id = 2

–
Ryan B.

ssk · December 11, 2007, 5:41pm

Hi,

Greg W. wrote:

On Dec 10, 2007, at 5:38 PM, Sam K. wrote:

If you think I’m doing it wrong, can you suggest a good way?

You can use the existing key field and key value. I’m doing this in
apps being rewritten, as well as in databases that are being shared
by Rails & other platforms. I have found no need so far to insist on
the autoinc field.

In your model, if the field name is not “id,” you can declare the
keyfield like this:

self.primary_key = “rcrd_id” # or whatever the name is

When you declare associations, you can use the :foreign_key => ‘xxx’
option in :has_one,:has_many etc to define a field name that does not
follow Rails’ convention.

The only tricky part is when *reading the key value, you can specify
the field as my_model.rcrd_id (as expected), but when *writing the
value, you still have to use the Rails default of my_model.id
(presumably because .id is a manually written accessor method), so
the name doesn’t change even when the field name does.

Next, add a method to each ActiveRecord model similar to this one to
create the key value for when new records are created:

def before_create
self.id = 20.random_id
end

That .random_id is custom extension I use, so do whatever it is you
normally do to generate your keyfield values.

Is that the kind of info you’re looking for?

–
def gw
acts_as_n00b
writes_at(www.railsdev.ws)
end

Thank you for the lengthy explanation.
I know how to use non-conventional ID.

My problem is like this.

products (in old DB)

1:A
3:B
4:C

When I import the products table to the new DB via Activa, it will be
like this:

products (in old DB)

1:A
2:B
3:C

The IDs are sequentially generated and I lose the old IDs.
But other tables have product_id for old IDs.
I have to record the pairs of {old_id => new_id} for other tables.

To avoid this situation, I just want to have the old IDs as new IDs.

Thanks.

Sam

ssk · December 11, 2007, 2:00pm

On Dec 10, 2007, at 5:38 PM, Sam K. wrote:

If you think I’m doing it wrong, can you suggest a good way?

You can use the existing key field and key value. I’m doing this in
apps being rewritten, as well as in databases that are being shared
by Rails & other platforms. I have found no need so far to insist on
the autoinc field.

In your model, if the field name is not “id,” you can declare the
keyfield like this:

self.primary_key = “rcrd_id” # or whatever the name is

When you declare associations, you can use the :foreign_key => ‘xxx’
option in :has_one,:has_many etc to define a field name that does not
follow Rails’ convention.

The only tricky part is when *reading the key value, you can specify
the field as my_model.rcrd_id (as expected), but when *writing the
value, you still have to use the Rails default of my_model.id
(presumably because .id is a manually written accessor method), so
the name doesn’t change even when the field name does.

Next, add a method to each ActiveRecord model similar to this one to
create the key value for when new records are created:

def before_create
self.id = 20.random_id
end

That .random_id is custom extension I use, so do whatever it is you
normally do to generate your keyfield values.

Is that the kind of info you’re looking for?

–
def gw
acts_as_n00b
writes_at(www.railsdev.ws)
end

ssk · December 12, 2007, 4:02am

Hi Sam,

ActiveRecord always protect it’s PK from modifying … One way to
achieve your goal is to create a temp model use some other column as
PK:

create_table products, :primary_key => :temp_id do |t|
t.column :temp_id, :integer
#add other stuff …
end

Then you can set&save id attribute to any value you want in Product.
After you migration, you can remove the fake id column and modify your
create_table back to normal.

Another approach is … modifying the Rails source yourself, have a
look on attributes_from_column_definition method in
ActiveRecord::Base. We’ll love opensource

I don’t know whether there’s more clever ways …
Thanks,
Jan

On Dec 12, 12:41 am, Sam K. [email protected]

ssk · December 12, 2007, 12:12pm

Hi Jan,

Jan wrote:

Hi Sam,

ActiveRecord always protect it’s PK from modifying … One way to
achieve your goal is to create a temp model use some other column as
PK:

create_table products, :primary_key => :temp_id do |t|
t.column :temp_id, :integer
#add other stuff …
end

Then you can set&save id attribute to any value you want in Product.
After you migration, you can remove the fake id column and modify your
create_table back to normal.

Another approach is … modifying the Rails source yourself, have a
look on attributes_from_column_definition method in
ActiveRecord::Base. We’ll love opensource

I don’t know whether there’s more clever ways …
Thanks,
Jan

On Dec 12, 12:41 am, Sam K. [email protected]

Thank you for the suggestions.
I’m also thinking of using fixtures.
The new fixture seems very clever about this problem.
Or maybe I can add old_id column.

Thanks all for different ideas!

Sam

ssk · December 11, 2007, 7:07pm

On Dec 11, 2007, at 8:41 AM, Sam K. wrote:

To avoid this situation, I just want to have the old IDs as new IDs.
When you create your DBs, simply do not make the ID field autoinc
(make it integer, etc like it was going to be an autoinc though).
Then after the importing is done, turn autoinc on, and MySQL will
automatically pick up with the next highest number for new records.

–
def gw
acts_as_n00b
writes_at(www.railsdev.ws)
end

ssk · December 13, 2007, 6:18pm

I’m still confused about your situation. If you have an existing
database that you want to copy & modify, do just that. Use MYSQL to
copy the database, use rails to modify things after that. The only
issue would be if you were renaming tables.
http://dev.mysql.com/doc/refman/6.0/en/mysqldump.html

I suspect that the output is hackable in the table name space.

ssk · December 13, 2007, 7:12pm

On Dec 13, 2007, at 11:42 AM, Sam K. wrote:

I suspect that the output is hackable in the table name space.

I know I can copy the database.
But the structures are so different that I can’t just copy and paste.
Also, I need to apply complex rules.
That’s why I need to use ActiveRecord.

Maybe it’s a matter of word choice? It sounds like you are doing a
conversion, not a simple migration.

Phillip

ssk · December 13, 2007, 6:42pm

Hi,

Student wrote:

I’m still confused about your situation. If you have an existing
database that you want to copy & modify, do just that. Use MYSQL to
copy the database, use rails to modify things after that. The only
issue would be if you were renaming tables.
http://dev.mysql.com/doc/refman/6.0/en/mysqldump.html

I suspect that the output is hackable in the table name space.

I know I can copy the database.
But the structures are so different that I can’t just copy and paste.
Also, I need to apply complex rules.
That’s why I need to use ActiveRecord.

I hope that I explained my intention well.

Sam

ssk · December 13, 2007, 7:19pm

Hi Rob,

Rob B. wrote:

On Dec 13, 2007, at 12:42 PM, Sam K. wrote:

I know I can copy the database.
But the structures are so different that I can’t just copy and paste.
Also, I need to apply complex rules.
That’s why I need to use ActiveRecord.

I hope that I explained my intention well.

Sam

If you assign the id ‘manually’, it will be honored, if possible.

mycopy = Model.new(hash_of_old_attributes) { |m| m.id = oldid }

… apply complex rules …

mycopy.save

Note that you can’t have :id in the hash_of_old_attributes (well, it
will be ignored if present).

-Rob

(of course, you’re on the forum and may never see this message…)

Rob B. http://agileconsultingllc.com
[email protected]

Yeah~
I finally got the answer.
Thank you so much.

Sam

ssk · December 13, 2007, 7:23pm

Sam K. wrote:

Hi Rob,

Rob B. wrote:

On Dec 13, 2007, at 12:42 PM, Sam K. wrote:

I know I can copy the database.
But the structures are so different that I can’t just copy and paste.
Also, I need to apply complex rules.
That’s why I need to use ActiveRecord.

I hope that I explained my intention well.

Sam

If you assign the id ‘manually’, it will be honored, if possible.

mycopy = Model.new(hash_of_old_attributes) { |m| m.id = oldid }

… apply complex rules …

mycopy.save

Note that you can’t have :id in the hash_of_old_attributes (well, it
will be ignored if present).

-Rob

(of course, you’re on the forum and may never see this message…)

Rob B. http://agileconsultingllc.com
[email protected]

Yeah~
I finally got the answer.
Thank you so much.

Sam

I was so dumb.
When I first did it, I used Model.create instead of Model.new.
Once you create a record using Model.create, you can’t change the id.

Thank you for enlightening me.

Sam