Rails - Apr 2006 - Integrating with Legacy Databases

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 :)

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

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 :) (one gotcha is the typecasting in the write_attribute
call)

My 2 (euro)cents,

   -- Jean-Fran?ois.

--
Railsfrance - http://www.railsfrance.org

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 = ''jimhalberg@gmail.com'') 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 <jf.web3@gmail.com>
wrote:
> 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 :) (one gotcha is the typecasting in the write_attribute
> call)
>
> My 2 (euro)cents,
>
>    -- Jean-Fran?ois.
>
> --
> Railsfrance - http://www.railsfrance.org
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

--
http://jimhalberg.com

Jim :
> 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''])
[...]

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.


--
? la renverse.

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 <jf.web3@gmail.com>
wrote:
> Jim :
> > 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''])
> [...]
>
> 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.
>
>
> --
> ? la renverse.
>

--
http://jimhalberg.com

Jim Halberg wrote:
> 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 :)
> 
> 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
I did exactly just that for my presentation at Canada on Rails..

http://rubyurl.com/DRv

-Robby