Support for char primary keys?

[Daniel Berger]

Hi,

Me again. Dealing with the same legacy Oracle database that uses char
types for primary keys.

I've run into an issue where I cannot create a record if the table has
a char primary key. If a sequence exists, it ends up inserting a
sequence value instead of a string. If I remove the sequence, AR fails
because it can't find a sequence.

Does the enhanced oracle adapter provide a way around this by any
chance? If not, is there any chance support could be added for it?
Something like this:

class MyTable < ActiveRecord::Base
self.primary_key = false
end

Essentially, override the current semantics so that nil and false mean
two different things. A nil value would simply mean use the default,
while an explicitly false value indicates not to use a sequence at
all.

What do you think?

Regards,

Dan

[Raimonds Simanovskis]

I created the following simple example how you can do insertion:

# CREATE TABLE test_countries (
# code CHAR(2) PRIMARY KEY,
# name CHAR(20)
# );

class TestCountry < ActiveRecord::Base
set_primary_key :code
end

country = TestCountry.new do |c|
c.code = "LV"
c.name = "Latvia"
end.save!

This was successful for me.

You cannot do
TestCountry.create(:code => "LV", :name => "Latvia")
as ActiveRecord will in this case remove primary key from hash
parameters. But if you assign primary key value separately then AR
will not try to fetch sequence value.

But in any case this example does not fit very well ActiveRecord
conventions. If you have legacy schema which does not follow
ActiveRecord conventions then probably you should evaluate DataMapper
as ORM for your application (see
http://blog.rayapps.com/2009/07/21/initial-version-of-datamapper-oracle-adapter/)

Raimonds

[Daniel Berger]

On Sep 29, 11:59 am, Raimonds Simanovskis

<raimonds.simanovs...@gmail.com> wrote:
> I created the following simple example how you can do insertion:
>
> # CREATE TABLE test_countries (
> #   code CHAR(2) PRIMARY KEY,
> #   name CHAR(20)
> # );
>
> class TestCountry < ActiveRecord::Base
>   set_primary_key :code
> end
>
> country = TestCountry.new do |c|
>   c.code = "LV"
>   c.name = "Latvia"
> end.save!
>
> This was successful for me.

This was _not_ successful for me.

/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
connection_adapters/abstract_adapter.rb:219:in `log': OCIError:
ORA-02289: sequence does not exist: SELECT test_countries_seq.NEXTVAL
id FROM dual (ActiveRecord::StatementInvalid)

This is Ruby 1.8.7, Rails 2.3.4, btw.

Any ideas?

Regards,

Dan

[Raimonds Simanovskis]

Strange... Here is my full test script, that was successful for me:

############################
require "rubygems"
gem "activerecord", "=2.3.4"
require "activerecord"
gem "activerecord-oracle_enhanced-adapter", "=1.2.2"

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.colorize_logging = false
ActiveRecord::Base.logger.level = Logger::DEBUG

ActiveRecord::Base.establish_connection :adapter => "oracle_enhanced",
:username => "hr", :password => "hr", :database => "orcl"

ActiveRecord::Base.connection.execute "DROP TABLE test_countries"
rescue nil
ActiveRecord::Base.connection.execute <<-SQL
CREATE TABLE test_countries (
code CHAR(2) PRIMARY KEY,
name CHAR(20)
)
SQL

class TestCountry < ActiveRecord::Base
set_primary_key :code
end

country = TestCountry.new do |c|
c.code = "LV"
c.name = "Latvia"
end.save!

puts TestCountry.all.inspect
############################

If ActiveRecord object has primary_key attribute populated then it
should not select next sequence value. Here is fragment from
activerecord gem base.rb file:

# Creates a record with values matching those of the instance
attributes
# and returns its id.
def create
if self.id.nil? && connection.prefetch_primary_key?
(self.class.table_name)
self.id = connection.next_sequence_value
(self.class.sequence_name)
end
...

Can you please check one more time with my full test script? :)
(change database connection as needed in your case)

Raimonds

[Daniel Berger]

On Sep 30, 12:22 am, Raimonds Simanovskis

Ok, this worked. Many thanks!

Regards,

Dan

[Daniel Berger]

On Sep 30, 12:22 am, Raimonds Simanovskis

Upon further review it only works if you use TestCountry.new. When I
tried TestCountry.create it failed:

country = TestCountry.create(:code => 'LV', :name => 'Latvia')
country.save!

WARNING: Can't mass-assign these protected attributes: code
SQL (0.0ms) OCIError: ORA-02289: sequence does not exist: SELECT

test_countries_seq.NEXTVAL id FROM dual
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
connection_adapters/abstract_adapter.rb:219:in `log': OCIError:
ORA-02289: sequence does not exist: SELECT test_countries_seq.NEXTVAL
id FROM dual (ActiveRecord::StatementInvalid)

from /usr/local/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-
adapter-1.2.2/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:1304:in `log'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-
adapter-1.2.2/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:1228:in `select'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all_without_query_cache'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/connection_adapters/abstract/query_cache.rb:62:in
`select_all'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/connection_adapters/abstract/database_statements.rb:
13:in `select_one'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-
adapter-1.2.2/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:582:in `next_sequence_value'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/base.rb:2888:in `create_without_timestamps'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/timestamp.rb:53:in `create_without_callbacks'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/callbacks.rb:266:in `create'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/base.rb:2867:in `create_or_update_without_callbacks'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/callbacks.rb:250:in `create_or_update'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/base.rb:2538:in `save_without_validation'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/validations.rb:1078:in `save_without_dirty'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/dirty.rb:79:in `save_without_transactions'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:229:in `send'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:229:in
`with_transaction_returning_status'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/connection_adapters/abstract/database_statements.rb:
136:in `transaction'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:182:in `transaction'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:228:in
`with_transaction_returning_status'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:196:in `save'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:208:in `rollback_active_record_state!'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/transactions.rb:196:in `save'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/
active_record/base.rb:723:in `create'
from test.rb:35

Since .create is apparently how fixtures are loaded (makes sense, YAML
returns a hash), this is problematic.

Any suggestions?

Regards,

Dan

[Raimonds Simanovskis]

When you use .create method then it saves it to database directly. But
you cannot pass protected attributes in hash parameters (like primary
key value).

.create is not used for loading fixtures - it uses different methods
(see fixtures.rb in activerecord gem).
I think it should be possible to load data with character primary key
from fixture file (as you can load also numeric primary keys).
But have not tested yet :)

Raimonds

[Daniel Berger]

On Sep 30, 12:22 am, Raimonds Simanovskis

What's curious is that it works using a block as you've done above.
But if you try to pass a hash, it fails:

TestCountry.new(:code => 'LV', :name => 'Latvia').save # fails

Any idea why?

Regards,

Dan

[Raimonds Simanovskis]

Because :code is primary key and therefore protected attribute.
You cannot pass protected attributes (that you define with
attr_protected as well as primary keys) as hash values to #new method
or to #update_attributes method.
But you can assign protected attributes explicitly with attribute
setter method.

Raimonds

[Daniel Berger]

On Oct 26, 2:04 pm, Raimonds Simanovskis

<raimonds.simanovs...@gmail.com> wrote:
> Because :code is primary key and therefore protected attribute.
> You cannot pass protected attributes (that you define with
> attr_protected as well as primary keys) as hash values to #new method
> or to #update_attributes method.
> But you can assign protected attributes explicitly with attribute
> setter method.

Thanks, I get it now.

It does seem like an odd interface decision. How much protection am I
really getting if I can simply use the assignment operator anyway?
Maybe it just fit their overall design better. But, whatever, I got it
working. :)

Regards,

Dan

[Raimonds Simanovskis]

This is not protection against developer but from bad users :)

Mostly it is useful in controllers where e.g. in UserController new
action you would do
@user = User.new(params[:user])
and if you have "admin" attribute as protected then when some hacker
will pass user[admin]=1 in POST request to the controller then params
['user']['admin'] will be ignored by User.new when creating new user.

Raimonds