 |
Forums |
Admin  Start New Thread
By: Alex Pitigoi
 RE: Support for multiple schemas? [ reply ]
2008-02-22 14:18
|
Nathan, I am glad to hear you found a work-around that helps at least temporary in your environment, and I also wanted to thank you for sharing your thoughts and work.
There are 2 issues that preclude us from taking advantage of your patch:
1. First and the most important, we do not have legal clearance to take in external contributions before we put in place a Contributors License Agreement (CLA)
2. Any solution has to be validated against all supported platforms, and for this specific case, schema has a slightly different usage/meaning for the DB2 on z/OS and Informix IDS data servers.
That being said, we are looking into a cross-platform solution, and initial considerations start from the current support for legacy data stores (eg. existing set_table_name, set_primary_key API support).
That means, one should be able to set_schema_name on a Rails model, and that could potentially be passed implicitly throughout table interaction to qualify the table name (schema_name.table_name). Remains for us to devise the implementation and assess the performance impact.
Your feedback is very much appreciated.
Thanks,
Alex P
|
By: Nathan Wiebe
RE: Support for multiple schemas? [ reply ]
2008-02-20 19:11
|
We've made some changes to ibm_db adapter to support multiple schemas. The changes are really simple, but seem to work for us. All they do is detect when a table name has been specified with a schema name in front (ex OTHER_SCHEMA.SOME_TABLE) and then pass the specified schema instead of @schema.
These changes allow us to work with tables outside the schema specified in database.yml. We can use ActiveRecord accessors like .find_by_<some_column>(), .save,() .update() and .delete() the data.
In order to make it into an official release more robust support might be required. In particular, if there are cases where the schema name (@schema) are required besides the two we changed below this will likely break.
With the changes in the diff below, we can create a model for a table outside the specified schema. For example:
class SomeTable < ActiveRecord::Base
set_table_name "OTHER_SCHEMA.SOME_TABLE"
end
The above allows us to work with SomeTable using ActiveRecord just like you'd expect.
Nathan
Here's the diff, against ibm_db-0.9.2-mswin32\lib\active_record\connection_adapters.
--- ibm_db_adapter.rb_orig Tue Feb 12 12:10:27 2008
+++ ibm_db_adapter.rb Wed Feb 20 10:54:43 2008
@@ -789,9 +789,13 @@
# "NON_UNIQUE: #{index_stats[3]}"
# "INDEX_NAME: #{index_stats[5]}"
# "COLUMN_NAME: #{index_stats[8]}"
- if stmt = IBM_DB::statistics( @connection, nil,
- @servertype.set_case(@schema),
- @servertype.set_case(table_name), 1 )
+ schema, table_without_schema = get_schema_from_table(table_name, @schema)
+ if stmt = IBM_DB::statistics( @connection, nil,
+ @servertype.set_case(schema),
+ @servertype.set_case(table_without_schema), 1 )
+# if stmt = IBM_DB::statistics( @connection, nil,
+# @servertype.set_case(@schema),
+# @servertype.set_case(table_name), 1 )
begin
while ( index_stats = IBM_DB::fetch_array(stmt) )
if index_stats[5] # INDEX_NAME
@@ -830,9 +834,13 @@
# +columns+ will contain the resulting array
columns = []
# Statement required to access all the columns information
- if stmt = IBM_DB::columns( @connection, nil,
- @servertype.set_case(@schema),
- @servertype.set_case(table_name) )
+ schema, table_without_schema = get_schema_from_table(table_name, @schema)
+ if stmt = IBM_DB::columns( @connection, nil,
+ @servertype.set_case(schema),
+ @servertype.set_case(table_without_schema) )
+# if stmt = IBM_DB::columns( @connection, nil,
+# @servertype.set_case(@schema),
+# @servertype.set_case(table_name) )
begin
# Fetches all the columns and assigns them to col.
# +col+ is an hash with keys/value pairs for a column
@@ -896,7 +904,16 @@
return columns
end
- # Renames a table.
+ def get_schema_from_table(table_name, schema)
+ tableparts = table_name.split('.')
+ if tableparts.size >= 2 then
+ [tableparts[0], tableparts[1..tableparts.size].join('.')]
+ else
+ [schema, table_name]
+ end
+ end
+
+ # Renames a table.
# ==== Example
# rename_table('octopuses', 'octopi')
# Overriden to satisfy IBM data servers syntax
|
By: Alex Pitigoi
RE: Support for multiple schemas? [ reply ]
2008-02-20 17:12
|
Yes, you are right. I didn't quite say it's going to be easy, but only that it can be done with what was provided already in the adapter :-)
To make it easy we need some sort of schema support in the ActiveRecord, and at this time I am not quite certain we can support it the way you described, solely by IBM_DB adapter code. We'll get back on this as soon as we have some results on our investigation.
Thanks for your feedback,
Alex P
|
By: Nathan Wiebe
RE: Support for multiple schemas? [ reply ]
2008-02-19 18:39
|
I see that the adapter supports setting the schema, but to me it looks like a lot of work to use it.
In order to use the existing ibm_db schema support, every time I call some method in the ActiveRecord class (find, save, etc), I'll need to add code to set @schema first, since the previous caller to an ActiveRecord method may have set a different schema. That's a lot of schema setting.
Also, if I forget to set the schema before just one ActiveRecord call anywhere in my app, it might work 90% of the time, and mysteriously fail 10% of the time due to an incorrect schema setting, based on the order of client requests, and hence paths through my code.
If the IBM_DB_ADAPTER allowed for table names to have explicit schemas identified as in set_table_name "SCHEMA.TABLE" and used that schema in the places where the schema is used:
if stmt = IBM_DB::statistics( @connection, nil,
@servertype.set_case(@schema),
@servertype.set_case(table_name), 1 ) (line 792 in 0.9.2 mswin32)
if stmt = IBM_DB::columns( @connection, nil,
@servertype.set_case(@schema),
@servertype.set_case(table_name) ) (line 833)
These places could pick up the schema from the table name when present and thus we wouldn't have to always remember to change the schema whenever we went to use a table.
Any thoughts?
Nathan
|
By: Alex Pitigoi
RE: Support for multiple schemas? [ reply ]
2008-02-14 15:35
|
|
You don't need to modify the IBM_DB adapter, it already supports this schema attribute, as the test case proves. You just need to use the attribute as the test case does to set the schema whenever needed.
|
By: Alex Pitigoi
RE: Support for multiple schemas? [ reply ]
2008-02-14 14:42
|
Hi Nathan,
For multiple schema support you may want to take a look at the IBM_DB specific test in the IBM_DB_Adapter/ibm_db/test/adapter_test.rb or more precisely: http://rubyibm.rubyforge.org/svn/trunk/IBM_DB_Adapter/ibm_db/test/adapter_test.rb
You should be able to spot the test_a_connection_attributes test case which makes use of the 'schema' connection attribute:
if @connection.respond_to?(:schema)
previous_schema = ActiveRecord::Base.connection.schema
ActiveRecord::Base.connection.schema = 'SYSCAT'
assert_equal 'SYSCAT', ActiveRecord::Base.connection.schema
ActiveRecord::Base.connection.schema = previous_schema
else
warn "#{@connection.class} does not support client connection attribute schema_name"
end
I hope this helps, but if otherwise please let us know. Thanks,
Alex
|
By: Nathan Wiebe
Support for multiple schemas? [ reply ]
2008-02-13 20:38
|
Hi, is anyone else out there using ibm_db with multiple schemas? I'm running ibm_db v0.9.2 with rails v1.2.6, and I can't see a nice way to do it.
In database.yml I specify:
development:
adapter: ibm_db
database: MAIN
username: user
password: pass
schema: schema1
host: dbserver
port: 50002
Now I can create models for tables inside schema1 like so:
class Table1 < ActiveRecord::Base
set_table_name "table1"
end
Now I can use Table1 to run queries, update the data and .save() the results just fine.
But if I have a different table, in schema2, it doesn't work properly. I can create the model like so:
class Table2 < ActiveRecord::Base
set_table_name "schema2.table2"
end
Table2 now I can run Table2.find_by_sql(...) and work with the results, but I can't run Table2.find_by_COLUMN_NAME(...). I also can't update then save() on the results of Table2.find_by_sql().
I traced this back to ibm_db failing to load the column definitions for Table2. This is a result of having a single @schema variable and assuming that all tables live in that schema. Since table2 is not in the schema defined in database.yml the column meta data can't be loaded.
Is there any support for modeling tables outside of the schema defined in database.yml?
A possible fix would be for the driver to notice when a table name includes a schema name in front and alter its behaviour accordingly.
Right now we define a subclass of ActiveRecord::Base for each schema in our database, and subclass from those.
Nathan
|
|
|
