July 10, 2008
Custom ActiveRecord create, add and delete methods for legacy databases
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
Source code of Oracle enhanced adapter is located at GitHub and you can submit bug reports and enhancement requests in Lighthouse.
