July 10, 2008
In some Ruby on Rails projects I am putting ActiveRecord interface on top of existing legacy databases. It is quite easy to specify legacy table names and primary keys in ActiveRecord models so that ActiveRecord would generate correct SQL SELECT statements to read data from legacy databases.
But it is more difficult to insert, update and delete in legacy databases using ActiveRecord. When using Oracle legacy databases then quite often they have exposed PL/SQL APIs for writing to tables and you are typically not allowed to directly modify tables with INSERT, UPDATE and DELETE statements.
Therefore I created support for custom create, update and delete methods in the version 1.1.3 of ActiveRecord Oracle enhanced adapter which uses also my ruby-plsql gem.
Let’s look at the following example. Assume that we have the following table:
CREATE TABLE test_employees ( employee_id NUMBER(6,0), first_name VARCHAR2(20), last_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), version NUMBER(15,0), create_time DATE, update_time DATE )
And we have the following PL/SQL API package that should be used to write to this table:
CREATE OR REPLACE PACKAGE test_employees_pkg IS PROCEDURE create_employee( p_first_name VARCHAR2, p_last_name VARCHAR2, p_hire_date DATE, p_salary NUMBER, p_employee_id OUT NUMBER); PROCEDURE update_employee( p_employee_id NUMBER, p_first_name VARCHAR2, p_last_name VARCHAR2, p_hire_date DATE, p_salary NUMBER); PROCEDURE delete_employee( p_employee_id NUMBER); END;
Then we define ActiveRecord model in the following way:
class TestEmployee < ActiveRecord::Base set_primary_key :employee_id # should return ID of new record set_create_method do plsql.test_employees_pkg.create_employee( :p_first_name => first_name, :p_last_name => last_name, :p_hire_date => hire_date, :p_salary => salary, :p_employee_id => nil )[:p_employee_id] end # return value is ignored set_update_method do plsql.test_employees_pkg.update_employee( :p_employee_id => id, :p_first_name => first_name, :p_last_name => last_name, :p_hire_date => hire_date, :p_salary => salary ) end # return value is ignored set_delete_method do plsql.test_employees_pkg.delete_employee( :p_employee_id => id ) end end
And as a result we can use this model in the same way as other ActiveRecord models:
@employee = TestEmployee.create( :first_name => "First", :last_name => "Last", :hire_date => Date.today ) @employee.reload @employee.first_name = "Second" @employee.save! @employee.destroy
And all writing to the database will be done using defined API procedures.
Currently this functionality is embedded into Oracle enhanced adapter but if somebody needs it also for other databases this functionality could easily be extraced from the adapter.
To get the new release of Oracle enhanced adapter just do:
sudo gem install activerecord-oracle_enhanced-adapter