I’d been struggling with this one for a while: I needed a class which was keyed on something non-sequential, non-guessable and non-numeric. In my case, uuids. ActiveRecord doesn’t give you a great deal of help to do this, it generally assumes that you will use a column called ‘id’ that is some sort of integer and that the database will always give you a value for it.
If you’re not doing it this way, you need to work around a few things:
- In your model, define a ‘before_create’ hook and set up your id. in my case, it looks like this:
def before_create
self.id = uuid
end
- For test cases using this approach, do the following to avoid having activerecord try and set the non-existent sequence back to 1 (or define a useless sequence). I suspect this is only absolutely necessary for postgres:
class Fixtures
def self.reset_sequences(connection, table_names)
end
end
Hi TufTy,
Well I did try this but I am a newbe and must have got something wrong. Here is my Model:
class Job < ActiveRecord::Base
def before_create
self.id = job_id
end
def self.reset_sequences(connection, table_names)
end
end
But when I try to do a save ActiveRecord keeps refering to a missing ID field. I really just want to use PK’s that don’t use autoincrement. Better yet that don’t use autoincrement and don’t have to be integer. I want to assign the value for the PK myself when I create a new record.
Here is the table:
CREATE TABLE [dbo].[jobs] (
[job_id] [int] NOT NULL ,
[job_desc] [varchar] (50) NULL ,
[min_lvl] [tinyint] NULL ,
[max_lvl] [tinyint] NULL
) ON [PRIMARY]
GO
—————
Tuffy and others:
I’ve been struggling
on userping the Rails autoincrement and hide primary key phillosopy as in real world applications it just isn’t an across the board “one way for all ID’s”. So, here’s how far I’ve gotten which does give you the ability to self-assign in any manner you like the ID for new records.
Here’s what I did (running against the Oracle demo schema for Scott/Tiger and Emp, Dept tables):
models/emp.rb (you won’t need the _before_type_cast if you’re using character ID’s – here I want to display the numeric ID and Rails wants to hide it):
def empno_before_type_cast
return read_attribute(:empno) if read_attribute(:empno)
return Integer(self.empno_next)
end
def empno_next
return ActiveRecord::Base.connection.select_value(‘select max(empno)+1 from emp’)
end
views\emp\new.rhtml (not much different here):
New emp
<= start_form_tag :action => ‘create’ %>
<= render :partial => ‘form’ >
<= submit_tag “Create” >
<= end_form_tag %><%= link_to ‘Back’, :action => ‘list’ %>
and in my views\emp\edit.rhtml:
Editing emp
<= start_form_tag :action => ‘update’, :id => @emp.empno %>
<= render :partial => ‘form’ >
<= submit_tag ‘Update’ >
<= end_form_tag %><= link_to ‘Show’, :action => ‘show’, :id => @emp %> |
<= link_to ‘Back’, :action => ‘list’ %>
The thing that makes this work is the conditional in the empno_before_type_cast. You could just as easily overload the attribute itself in the model (for me the emp.rb) like
def empno
return read_attribute(:empno) if read_attribute(:empno)
return Integer(self.empno_next)
end
Can’t be right, but it worked
AKA: Horribal hack of doom
I had a lot of problems with this and postgres, especially because just remapping the primary key to some other column value wasn’t quite enough to avoid getting errors like:
ActiveRecord::StatementInvalid (PGError: ERROR: \ relation "usr_general_rights_login_seq" does not exist
I ended up setting the hooks, as mentioned above, and then defining a different column name for the primary key otherwise. It was an existing column, but essentially not in use. Primarily, I don’t think that anything cares about the values of that column so it was a good candidate to play with. I was finding if I set it to the correct value that I wanted to treat as a primary key that it would start inserting NULL’s where I wanted the my uid’s to be, as it was data I was defining as part of the passed attributes. Perhaps I just found some strange loop hole. Certainly, if anyone has a better solution that works without needing to make one of your columns into a NULL storage unit. I’d love to hear about it <leah@heinous.org>.
Here’s the example from the model. ‘login’ is the closest thing I have to a unique ID in this case, and the ‘changed_from’ column is just a random varchar field for an updating client to ID itself with.
set_primary_key "changed_from"
def before_create
self.id = login
end
def before_update
self.id = login
end
—————
Now, while this worked sometimes, sometimes, for whatever reason, it did not, with postgres still compaining about sequence relations that don’t exist. So here’s an even worse hack:
Create lib/pghack.rb
module ActiveRecord
class Base
class << self
def sequence_name
"#{table_name}_#{primary_key}_seq"
end
end
end
module ConnectionAdapters
class PostgreSQLAdapter
private
def last_insert_id( table, column = id )
klass = Object.const_get( Inflector.classify( table ) )
sequence_name = klass.sequence_name
0
end
end
end
end
Then add require 'pghack' to config/environment.rb.
Then the model looks gets this code:
class UsrGeneralRight < ActiveRecord::Base
set_primary_key "login"
def self.sequence_name
"login"
end
Again, if anyone has a better way that deals with non-numeric, non-incrementing, postgres primary keys with non-standard names, that would be great for a link here.
—————
Note to the self-described “newbie” – It sounds like you’re trying to do something slightly different than what this page describes. You’re trying to remap the “id” field to another column. That is accomplished with the “set_primary_key” method, which will instruct ruby to use an alternate column for the object identifier. In your case, try this:
class Job < ActiveRecord::Base
set_primary_key "job_id"
... snip ...
end
end
Note to Tufty. This is exactly what I’m looking for. I’ll try it in MySQL tonight, and let you know how it works. Thanks!
I’d been struggling with this one for a while: I needed a class which was keyed on something non-sequential, non-guessable and non-numeric. In my case, uuids. ActiveRecord doesn’t give you a great deal of help to do this, it generally assumes that you will use a column called ‘id’ that is some sort of integer and that the database will always give you a value for it.
If you’re not doing it this way, you need to work around a few things:
- In your model, define a ‘before_create’ hook and set up your id. in my case, it looks like this:
def before_create
self.id = uuid
end
- For test cases using this approach, do the following to avoid having activerecord try and set the non-existent sequence back to 1 (or define a useless sequence). I suspect this is only absolutely necessary for postgres:
class Fixtures
def self.reset_sequences(connection, table_names)
end
end
Hi TufTy,
Well I did try this but I am a newbe and must have got something wrong. Here is my Model:
class Job < ActiveRecord::Base
def before_create
self.id = job_id
end
def self.reset_sequences(connection, table_names)
end
end
But when I try to do a save ActiveRecord keeps refering to a missing ID field. I really just want to use PK’s that don’t use autoincrement. Better yet that don’t use autoincrement and don’t have to be integer. I want to assign the value for the PK myself when I create a new record.
Here is the table:
CREATE TABLE [dbo].[jobs] (
[job_id] [int] NOT NULL ,
[job_desc] [varchar] (50) NULL ,
[min_lvl] [tinyint] NULL ,
[max_lvl] [tinyint] NULL
) ON [PRIMARY]
GO
—————
Tuffy and others:
I’ve been struggling
on userping the Rails autoincrement and hide primary key phillosopy as in real world applications it just isn’t an across the board “one way for all ID’s”. So, here’s how far I’ve gotten which does give you the ability to self-assign in any manner you like the ID for new records.
Here’s what I did (running against the Oracle demo schema for Scott/Tiger and Emp, Dept tables):
models/emp.rb (you won’t need the _before_type_cast if you’re using character ID’s – here I want to display the numeric ID and Rails wants to hide it):
def empno_before_type_cast
return read_attribute(:empno) if read_attribute(:empno)
return Integer(self.empno_next)
end
def empno_next
return ActiveRecord::Base.connection.select_value(‘select max(empno)+1 from emp’)
end
views\emp\new.rhtml (not much different here):
New emp
<= start_form_tag :action => ‘create’ %>
<= render :partial => ‘form’ >
<= submit_tag “Create” >
<= end_form_tag %><%= link_to ‘Back’, :action => ‘list’ %>
and in my views\emp\edit.rhtml:
Editing emp
<= start_form_tag :action => ‘update’, :id => @emp.empno %>
<= render :partial => ‘form’ >
<= submit_tag ‘Update’ >
<= end_form_tag %><= link_to ‘Show’, :action => ‘show’, :id => @emp %> |
<= link_to ‘Back’, :action => ‘list’ %>
The thing that makes this work is the conditional in the empno_before_type_cast. You could just as easily overload the attribute itself in the model (for me the emp.rb) like
def empno
return read_attribute(:empno) if read_attribute(:empno)
return Integer(self.empno_next)
end
Can’t be right, but it worked
AKA: Horribal hack of doom
I had a lot of problems with this and postgres, especially because just remapping the primary key to some other column value wasn’t quite enough to avoid getting errors like:
ActiveRecord::StatementInvalid (PGError: ERROR: \ relation "usr_general_rights_login_seq" does not exist
I ended up setting the hooks, as mentioned above, and then defining a different column name for the primary key otherwise. It was an existing column, but essentially not in use. Primarily, I don’t think that anything cares about the values of that column so it was a good candidate to play with. I was finding if I set it to the correct value that I wanted to treat as a primary key that it would start inserting NULL’s where I wanted the my uid’s to be, as it was data I was defining as part of the passed attributes. Perhaps I just found some strange loop hole. Certainly, if anyone has a better solution that works without needing to make one of your columns into a NULL storage unit. I’d love to hear about it <leah@heinous.org>.
Here’s the example from the model. ‘login’ is the closest thing I have to a unique ID in this case, and the ‘changed_from’ column is just a random varchar field for an updating client to ID itself with.
set_primary_key "changed_from"
def before_create
self.id = login
end
def before_update
self.id = login
end
—————
Now, while this worked sometimes, sometimes, for whatever reason, it did not, with postgres still compaining about sequence relations that don’t exist. So here’s an even worse hack:
Create lib/pghack.rb
module ActiveRecord
class Base
class << self
def sequence_name
"#{table_name}_#{primary_key}_seq"
end
end
end
module ConnectionAdapters
class PostgreSQLAdapter
private
def last_insert_id( table, column = id )
klass = Object.const_get( Inflector.classify( table ) )
sequence_name = klass.sequence_name
0
end
end
end
end
Then add require 'pghack' to config/environment.rb.
Then the model looks gets this code:
class UsrGeneralRight < ActiveRecord::Base
set_primary_key "login"
def self.sequence_name
"login"
end
Again, if anyone has a better way that deals with non-numeric, non-incrementing, postgres primary keys with non-standard names, that would be great for a link here.
—————
Note to the self-described “newbie” – It sounds like you’re trying to do something slightly different than what this page describes. You’re trying to remap the “id” field to another column. That is accomplished with the “set_primary_key” method, which will instruct ruby to use an alternate column for the object identifier. In your case, try this:
class Job < ActiveRecord::Base
set_primary_key "job_id"
... snip ...
end
end
Note to Tufty. This is exactly what I’m looking for. I’ll try it in MySQL tonight, and let you know how it works. Thanks!