Integrating with Legacy Databases


#1

This seems like it must have been asked before - I really did try to
find it in the archives, so my apologies if it’s already out there.

Utilizing ActiveRecord, I would like to specify a prefix for the
column names in my table. For example, in ‘Recipe 16 Integrating with
Legacy Databases’ (Rails Recipes, from PragProg, by Fowler) they deal
with integration with a WordPress db where all field names are
preceded by “comment_” – i.e. comment_author_email.

The recipe explains how to deal with the primary key (comment_id) via:
ActiveRecord::Base.primary_key_prefix_type = :table_name_with_underscore

which is great: the primary key would obviously be the most important
piece to get working :slight_smile:

However, it stops short of going into detail on dealing with this on
the other column names.

I guess what I’m looking for is a nice clean way to take their extent
their example - allowing me to reference the non-key fields as
“author_email” instead of needing to always prefix and say things like
“comment_author_email” all over my whole application. and since this
will be in a more complete environment: I also don’t want to remove
“comment_” from the front of every field name in the whole db! It
will need to be specific to the Comment class itself.

Any help is most appreciated. Thanks.

  • jim

#2

Hello Jim,

However, it stops short of going into detail on dealing with this on
the other column names.

I guess what I’m looking for is a nice clean way to take their extent
their example - allowing me to reference the non-key fields as
“author_email” instead of needing to always prefix and say things like
“comment_author_email” all over my whole application. and since this
will be in a more complete environment: I also don’t want to remove
“comment_” from the front of every field name in the whole db! It
will need to be specific to the Comment class itself.

If I understand your problem correctly, let’s say we’ve got a Author
model
with a ‘comment_author_email’ field in the associated table, and
you want to do things like :

a = Author.new
a.author_email = ‘…’

So you can use facade column ; in your model

class Author < AR::B
def author_email
read_attribute(“comment_author_email”)
end

def author_email=(email)
write_attribute(“comment_author_email”, email.to_s)
end
end

and do the same for the other fields. Maybe a sort of model scaffolding
will ease your job :slight_smile: (one gotcha is the typecasting in the
write_attribute
call)

My 2 (euro)cents,

РJean-Fran̤ois.


#3

Jean-François - Thanks for the help. Your example is just what I am
trying to do. However, I am now wondering if maybe I don’t have my
hands around exactly what is causing my issue…

For my exact application:
my field name is “user_email”, which I would like to refer to as
“email”. My table name is “common_users” and I have already taken
care of it so that I can name my class “user”.

I have the code (as you have detailed below) in my user.rb:
def email
read_attribute “user_email”
end
def email=(value)
write_attribute “user_email”, value.to_s
end

however I run into the following method and see an error when
executing save (console output below the code)

def signup
return if generate_blank
@params[‘user’].delete(‘form’)
@user = User.new(@params[‘user’])
begin
User.transaction(@user) do
@user.new_password = true
logger.warn “4444444444444444444444444444”
if @user.save
logger.warn “55555555555555555555555555555555”
#snip
end
logger.warn “66666666666666666666666666”
end
logger.warn “777777777777777777”
rescue
logger.warn “888888888888888888888”
flash.now[‘message’] = l(:user_confirmation_email_error)
end
end

###############
console output::
User Columns (0.000580) SHOW FIELDS FROM common_users
SQL (0.000113) BEGIN
4444444444444444444444444444
User Load (0.000000) Mysql::Error: Unknown column
‘common_users.email’ in ‘where clause’: SELECT * FROM common_users
WHERE (common_users.email = ‘removed_email_address@domain.invalid’) LIMIT 1
SQL (0.000109) ROLLBACK
888888888888888888888

The table name looks fine, but the field is still being referred to as
‘email’ here. I guess I’m a bit confused as to why these two
statements are running at all?

Again, thanks for the help in advance.

On 4/20/06, Jean-François removed_email_address@domain.invalid wrote:

“comment_” from the front of every field name in the whole db! It

and do the same for the other fields. Maybe a sort of model scaffolding


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


http://jimhalberg.com


#4

I found the culprit… I had missed this guy hanging out in a cluster
of validation lines:
validates_uniqueness_of :email, :on => :create

… causing the execution of that select with the field name “email”.

Thanks a ton for the help on this Jean-François.

  • Jim

On 4/20/06, Jean-François removed_email_address@domain.invalid wrote:

I have the code (as you have detailed below) in my user.rb:
def signup
You can check that by unit-testing your model. Test a


user


À la renverse.


http://jimhalberg.com


#5

Jim H. wrote:

ActiveRecord::Base.primary_key_prefix_type = :table_name_with_underscore
“comment_author_email” all over my whole application. and since this
will be in a more complete environment: I also don’t want to remove
“comment_” from the front of every field name in the whole db! It
will need to be specific to the Comment class itself.

Any help is most appreciated. Thanks.

  • jim

I did exactly just that for my presentation at Canada on Rails…

http://rubyurl.com/DRv

-Robby


#6

Jim :

def email
return if generate_blank
@params[‘user’].delete(‘form’)
@user = User.new(@params[‘user’])
[…]

In fact, I think there may be a problem with User#new
when passing a hash, since the constructor creates the User
instance with the help of the column definitions. As we’ve
got a facade column (let’s say faked attribute), there must be
trouble. The User#create must work correctly I think.
You can check that by unit-testing your model. Test a
User.new with a hash having a ‘email’ key.

If I’m right, it should fail. Instead,

user = User.new
user.new_password = ‘…’
user.email = ‘…’
user.save

must work (another test)

So test also User#create.

For your code, maybe a way will be :

h = params[:user]
h.remove(‘form’)
email = h[:email]
h.remove(:email)
@user = User.new(h)
@user.email = email

And for convenience, make it a method

class User < AR::B

maybe not a good idea to override AR::B#initialize

def self.my_special_new(hash)
email = hash[:email]
hash.remove[:email]
user = new(hash)
user.email = email
user
end

end

sth like that… (to be unit-tested)

Hope this helps,

РJean-Fran̤ois.