Support for SQL Server

[Vikram]

Hi,

Thank you for a great project.

I am using Octopus with three shards - two on MySQL and the other on
SQL Server. Access to the MySQL shards works great. But when I try to
access the shard on SQL Server, I get the error below.

Has Octopus been tested with SQL Server? If not I can help with that.
I am relatively new to ruby and rails. Any pointers?

Thanks
Vikram

undefined method `symbolize_keys!' for
#<ActiveSupport::HashWithIndifferentAccess:0x3681f18>
Rails.root: D:/Work/rails_projects/UserSetup

activerecord-sqlserver-adapter (3.0.9) lib/active_record/
connection_adapters/sqlserver_adapter.rb:20:in `sqlserver_connection'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:229:in `new_connection'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:237:in `checkout_new_connection'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:191:in `block (2 levels) in checkout'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:187:in `loop'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:187:in `block in checkout'
C:/Ruby192/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:186:in `checkout'
activerecord (3.0.4) lib/active_record/connection_adapters/abstract/
connection_pool.rb:94:in `connection'
ar-octopus (0.3.4) lib/octopus/proxy.rb:86:in `select_connection'
ar-octopus (0.3.4) lib/octopus/proxy.rb:149:in `method_missing'
activerecord (3.0.4) lib/active_record/base.rb:596:in
`quoted_table_name'
activerecord (3.0.4) lib/active_record/relation/query_methods.rb:
276:in `build_select'
activerecord (3.0.4) lib/active_record/relation/query_methods.rb:
190:in `build_arel'
activerecord (3.0.4) lib/active_record/relation/query_methods.rb:
150:in `arel'
activerecord (3.0.4) lib/active_record/relation.rb:64:in `to_a'
activerecord (3.0.4) lib/active_record/relation/finder_methods.rb:
143:in `all'
activerecord (3.0.4) lib/active_record/relation/finder_methods.rb:
105:in `find'
activerecord (3.0.4) lib/active_record/relation/finder_methods.rb:
101:in `find'
C:in `find'
ar-octopus (0.3.4) lib/octopus/scope_proxy.rb:29:in `block in
method_missing'
ar-octopus (0.3.4) lib/octopus/proxy.rb:104:in `run_queries_on_shard'
ar-octopus (0.3.4) lib/octopus/scope_proxy.rb:28:in `method_missing'
app/controllers/application_controller.rb:51:in `setInitialDepartment'

[Thiago Pradi]

Hi Vikram,

Can you try Octopus from git and see what happens?

Thanks.

--
Att,

Thiago Pradi
Blog: http://blog.thiagopradi.net/
http://twitter.com/thiagopradi

[Vikram]

Hi Thiago,

I am using the latest from git..

Regards
Vikram

[G. Sobrinho]

Could you show a piece of code behind trace?

Em 27/04/2011 01:10, "Vikram" <vraya...@gmail.com>escreveu:

Hi Thiago,

I am using the latest from git..

Regards
Vikram

On Apr 27, 8:02 am, Thiago Pradi <thiago.pr...@gmail.com> wrote:
> Hi Vikram,
>

> Can you try Octop...

> On Tue, Apr 26, 2011 at 10:12 PM, Vikram <vrayabh...@gmail.com> wrote:
> > Hi,
>

> > Thank you for...

[Vikram]

The scenario is this: The same active record models connect to
different databases(all with the same schema) based on the users
login. The error is seen in the Department.find call of the
setInitialDepartment method.
The same works perfectly when I dont use octopus(with the sql server
configuration in database.yml) or with the three shards on mysql.

application_controller.rb
=====
class ApplicationController < ActionController::Base
protect_from_forgery
around_filter :select_shard
before_filter:setInitialDepartment

def select_shard(&block)
get_db
Octopus.using(session[:db], &block)
end

def get_db
if (session[:db].blank?)
# lets manually connect to the proper db
if (xyz)
# db = :ars_mysql
db = :ars
elsif (abc)
db = :jh_mysql
else
db = :uemf_mysql
end
session[:db] = db
end
end

def setInitialDepartment
if(session[:presentDeptID].blank?)
allDepts = Department.find(:all,:order => "DeptName")
session[:presentDeptID] = allDepts[0].DeptID
end
end
end
===========
shards.yml
===========
octopus:
environments:
- development
development:
shards:
ars_mysql:
adapter: mysql
database: ars
username: vikram
password: vikram123
host: localhost
jh_mysql:
adapter: mysql
database: jh
username: vikram
password: vikram123
host: localhost
uemf_mysql:
adapter: mysql
database: uemf
username: vikram
password: vikram123
host: localhost
ars:
adapter: sqlserver
dsn: mydsn
mode: odbc
database: ARS
username: vikram
password: vikram123
======

On Apr 27, 3:35 pm, "G. Sobrinho" <gabriel.sobri...@gmail.com> wrote:
> Could you show a piece of code behind trace?
>

[Vikram]

I was able to fix the issue with this monkey patch in lib\active_record
\connection_adapters\sqlserver_adapter.rb
(around line 21 in 3.0.9)

# Octopus sends in a HashWithIndifferentAccess which is not
liked around here.
# create a simple hash from it and pass it along
if (config.kind_of? HashWithIndifferentAccess )
config = Hash[config.keys.map{|k| [k,config[k]]}]
end
config = config.dup.symbolize_keys!

[Brett]

I am also having troubles using sqlserver + octopus + tiny_tds.

uname -a
Linux brett-laptop 2.6.38-9-generic #43-Ubuntu SMP Thu Apr 28 15:23:06
UTC 2011 x86_64 x86_64 x86_64 GNU/Linux

gem list:
activerecord (3.0.7, 3.0.5, 3.0.1, 2.3.8, 2.3.5)
activerecord-oracle_enhanced-adapter (1.3.2, 1.3.0)
activerecord-sqlserver-adapter (3.0.15)
activeresource (3.0.7, 3.0.5, 3.0.3, 3.0.1, 2.3.5)
activesupport (3.0.7, 3.0.5, 3.0.3, 3.0.1, 2.3.8, 2.3.5)
ar-octopus (0.3.4)
...
ruby-oci8 (2.0.4)
sqlite3 (1.3.3)
sqlite3-ruby (1.3.3, 1.3.2)



cat config/shards.yml

octopus:
environments:
- development
development:

shard_sqlite:
adapter: sqlite3
database: db/db_one.sqlite3
pool: 5
timeout: 5000

shard_ms:
adapter: sqlserver
host: 192.168.0.58
database: test
username: sa
password:
changedforthispostbutthepasswordisworkingsodontworryaboutit



I have an SQL Server Express (:shard_ms) and an sqlite (:shard_sqlite)
DB with *almost* the same schema. The SQL Server has an extra table,
but it isn't used (no model). Both DBs have a table called posts
(model: Post) for my testing. The SQL Server table has about 5
records. The sqlite table doesn't have any. My goal is to copy a
record from the SQL Server to the sqlite server like:

p = Post.using(:shard_ms).first
Post.using(:shard_sqlite).create( p.attributes )

Here are the two items I've noticed so far:

1) Oddly, the first time I run a command to pull a record from the
sqlite db (which has no records) it finds some. I'm pretty sure its
getting them from the :shard_ms server. The second time I run the
exact same request it gets it from the correct DB.

Loading development environment (Rails 3.0.7)
irb(main):001:0> Post.using(:shard_sqlite).count
=> 4
irb(main):002:0> Post.using(:shard_sqlite).count
=> 0



2) The second bug is the sqlite queries fail (besides the count
query). It looks like it is trying to run the SQL Server query against
the sqlite DB. (BTW: in the dump below you can also see the first bug
I mentioned, where it gets the record from the wrong DB)

Loading development environment (Rails 3.0.7)
irb(main):001:0> Post.using(:shard_sqlite).first
=> #<Post id: 1, name: "Brett", title: "my title", content: "blah blah
blah blah changed", created_at: "2011-05-19 18:19:24", updated_at:
"2011-05-19 18:19:24">
irb(main):002:0> Post.using(:shard_sqlite).first
ActiveRecord::StatementInvalid: SQLite3::SQLException: near ".":
syntax error: SELECT TOP (1) "posts".* FROM [posts]
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract_adapter.rb:207:in `log'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/sqlite_adapter.rb:284:in `select'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract/query_cache.rb:56:in
`select_all'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:142:in `send'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:142:in `method_missing'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/base.rb:468:in `find_by_sql'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/relation.rb:64:in `to_a'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/relation/finder_methods.rb:341:in `find_first'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/relation/finder_methods.rb:122:in `first'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/base.rb:439:in `__send__'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/base.rb:439:in `first'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
scope_proxy.rb:29:in `send'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
scope_proxy.rb:29:in `method_missing'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:97:in `run_queries_on_shard'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
scope_proxy.rb:28:in `method_missing'


And again with a create:

irb(main):003:0> p = Post.using(:shard_ms).first
=> #<Post id: 1, name: "Brett", title: "my title", content: "blah blah
blah blah changed", created_at: "2011-05-19 18:19:24", updated_at:
"2011-05-19 18:19:24">
...
irb(main):006:0> Post.using(:shard_sqlite).create( p.attributes )
ActiveRecord::StatementInvalid: SQLite3::SQLException: near "'blah
blah blah blah changed'": syntax error: INSERT INTO [posts]
([content], [updated_at], [name], [created_at], [title]) VALUES
(N'blah blah blah blah changed', '2011-05-19 18:19:24.043', N'Brett',
'2011-05-19 18:19:24.043', N'my title')
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract_adapter.rb:207:in `log'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract/database_statements.rb:
282:in `insert_sql'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/sqlite_adapter.rb:149:in
`insert_sql'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract/database_statements.rb:
44:in `insert'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract/query_cache.rb:16:in
`insert'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:142:in `send'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:142:in `method_missing'
from /var/lib/gems/1.8/gems/arel-2.0.9/lib/arel/
select_manager.rb:217:in `insert'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/relation.rb:14:in `__send__'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/relation.rb:14:in `insert'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/persistence.rb:270:in `create'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/timestamp.rb:47:in `create'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/callbacks.rb:277:in `create'
from /var/lib/gems/1.8/gems/activesupport-3.0.7/lib/
active_support/callbacks.rb:414:in `_run_create_callbacks'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/callbacks.rb:277:in `create'
... 6 levels...
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/attribute_methods/dirty.rb:21:in `save'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:240:in `save'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:292:in
`with_transaction_returning_status'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/connection_adapters/abstract/database_statements.rb:
139:in `transaction'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:129:in `transaction'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:207:in `transaction'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:290:in
`with_transaction_returning_status'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:240:in `save'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:251:in `rollback_active_record_state!'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/transactions.rb:239:in `save'
from /var/lib/gems/1.8/gems/activerecord-3.0.7/lib/
active_record/base.rb:499:in `create'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
scope_proxy.rb:29:in `send'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
scope_proxy.rb:29:in `method_missing'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
proxy.rb:97:in `run_queries_on_shard'
from /var/lib/gems/1.8/gems/ar-octopus-0.3.4/lib/octopus/
scope_proxy.rb:28:in `method_missing'
from (irb):6irb(main):007:0>


Thoughts?