When I read about the approach that the oracle adapter takes to auto incrementing primary keys, my first thought was that it was a bad idea. Why wouldn't you use the 'standard' approach of creating a sequence for the primary key, and writing a trigger to set the primary to the next value of the sequence upon creation of a record? This ensures that the same policy is used, whether a stored procedure or an ad-hoc insert is used. (I am talking about the fact that the oracle adapter creates its own primary key using #next_sequence_value in oracle_enhanced_adapter.rb.)
It's probably just me; I am coming from the viewpoint of someone who has spent the last 3 years working with oracle databases using plsql stored procedures to program much of the logic. I already have the sequences (which ActiveRecord migrations create automatically). I also have triggers, which interfere with the adapters behaviour.
To someone who just wants to move their rails application onto an oracle database, the approach taken by the adapter makes more sense. Also no oracle-specific code (outside of the adapter itself) has to be used.
I can imagine an adapter for data definition commands (eg table creation), encapsulating this code, but as far as I know, rails migrations are fairly basic when it comes to setting up a database. They require extra setup for foreign keys, for example.
I tried searching for a solution in the blogosphere, this post mentions this problem, but is actually about an issue with LOBs, which is solved in a later version.
Raimonds Simanovskis, the maintainer of the adapter, blogged about custom activerecord methods for legacy oracle databases.
It didn't help, because my knowledge of how activerecord adapters work was pretty limited at the time. (It is only slightly less limited now).
I posted to the "oracle-enhanced" google group and Raimonds was kind enough to improvise an 'ugly' solution. I tried it out and it seemed to work.
I added the code to the end of the apps config/environment.rb:
class ActiveRecord::Base
set_create_method do
quoted_attributes = attributes_with_quotes
conn = connection.raw_connection
cursor = conn.parse <<-EOS
BEGIN
INSERT INTO #{self.class.quoted_table_name} (#{quoted_column_names.join(', ')})
VALUES(#{quoted_attributes.values.join(', ')})
RETURNING #{self.class.primary_key} INTO :id;
END;
EOS
cursor.bind_param(':id', nil, Integer)
cursor.exec
id = cursor[':id']
cursor.close
id
end
end
What does this code do? It replaces the default active record create method with a method that sends a small pl/sql
script (the bit between BEGIN and END) which gets the new primary key. Note this works even if you change the name of
the primary key in your model using set_primary_key.
This also requires the plsql gem to be installed, which seems to be required for the adapter anyway.
There is one major shortcoming; this patch causes inserts to only work with tables that have an auto-increment sequence and trigger combination set up.
Ideally, this code could be incorporated into the adapter, and a setting could be used to switch it on or off. I don't know if there would be much demand for it, though.
Hi,
ReplyDeleteThanks for this, it's exactly what I was looking for.
I added a use_trigger method so you can apply this only to specific models instead of all :
class MyModel < ActiveRecord::Base
use_trigger
end
And your code :
class ActiveRecord::Base
def self.use_trigger
set_create_method do
quoted_attributes = attributes_with_quotes
conn = connection.raw_connection
cursor = conn.parse <<-EOS
BEGIN
INSERT INTO #{self.class.quoted_table_name} (#{quoted_column_names.join(', ')})
VALUES(#{quoted_attributes.values.join(', ')})
RETURNING #{self.class.primary_key} INTO :id;
END;
EOS
cursor.bind_param(':id', nil, Integer)
cursor.exec
id = cursor[':id']
cursor.close
id
end
end
end
Cheers!