I''m looking for direction here on finishing a patch that let''s
RoR work with
Oracle installs where the sequences are not available to the database user.
This patch also cleans up some regex''s that some adapters use to find
the table
name that is being INSERTed into and saves one SELECT call for Oracle.
Hopefully, some of points may be taken into account in Michael''s
forthcoming
bind variables patch.
We have the following:
- All tables use triggers to select the next primary key.
- Some tables have a primary key of VARCHAR2(32) and use the sys_op_guid()
function to get the next primary key. So the SQL "select from
sys_op_guid().nextval" doesn''t work. Any user can call
sys_op_guid().
- The remaining tables use sequences and the database user does not have any
rights on the sequence, so the Oracle adapter cannot do a "select
#{seq}.nextval" on it.
To work around these, I''m using the following code: insert() now takes
the AR
instance, instead of the primary key, id and the sequence name as arguments.
The new code requires that the AR instance be passed down, so I can get the
primary key column and its Ruby type for the bind.
BTW, this allows cleaner code in some of the other database adapters, since some
use regex''s to pull out the table name or other info from the SQL,
where they
can now get it from the AR instance. See insert() and get_table_name() in
http://dev.rubyonrails.org/svn/rails/trunk/activerecord/lib/active_record/connection_adapters/sybase_adapter.rb
Finally, its also faster, since it does one less SELECT.
Here''s my current code:
def insert(sql, ar_instance, name = nil) #:nodoc:
# Pre-assigned id.
if ar_instance.id
log(sql, name) { @connection.exec sql }
return ar_instance.id
end
# Some Oracle DBAs do not allow the database user access to
# the primary key''s sequence or the primary key is generated
# from sys_op_guid(), in which case you cannot do a SELECT
# sys_op_guid().nextval. So use bind variables and a
# RETURNING clause to get back the primary key that the
# trigger choose. This also saves one additional round trip
# to the database.
ar_class = ar_instance.class
pk = "#{ar_class.table_name}.#{ar_class.primary_key}"
sql = <<-END_OF_SQL
DECLARE
id #{pk}%type;
BEGIN
#{sql} RETURNING #{pk} into :id;
END;
END_OF_SQL
cursor = nil
log(sql, name) { cursor = @connection.parse(sql) }
id_column = ar_class.columns_hash[ar_class.primary_key]
cursor.bind_param('':id'', id_column.default,
id_column.klass)
cursor.exec
cursor['':id'']
end
This works great for us, but breaks the AR test suite, since it assumes that
each table has a read/writable sequence.
So I''m thinking to support Oracle shops that use public sequences and
shops that
don''t, for Oracle''s case, if the AR class doesn''t use
set_sequence_name, for the
Oracle binding to assume that it uses the new code, otherwise if
set_sequence_name is set, it can do the "select from
#{sequence_name}.nextval".
This would require changes to AR itself.
Would this be acceptable? It seems a little messy, but I don''t see
another easy
way.
Regards,
Blair