Raw SQL in Migration

Hi,

I’m using MySQL for development and Oracle for testing and production. I
need to create a database view with migrations and this feature is not
available through migration DSL, so, to create the view, I’ll need to
put
raw SQL code in migration.

What is the best way to do this, keeping the migration working for
Oracle
and MySQL?

Thanks,
Gustavo Honorato

“Gustavo de Sá Carvalho H.” <gustavohonorato wrote in post
#966922:

Hi,

I’m using MySQL for development and Oracle for testing and production. I
need to create a database view with migrations and this feature is not
available through migration DSL, so, to create the view, I’ll need to
put
raw SQL code in migration.

No you won’t. Just use the rails_sql_views plugin.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Thanks Marnen,

I didn’t know this plugin. I’ve read the documentation, but in his
example I
can’t see how to change the columns names of the view. Is it possible?

Thanks again,
Gustavo

Please quote when replying.

“Gustavo de Sá Carvalho H.” <gustavohonorato wrote in post
#966932:

Thanks Marnen,

I didn’t know this plugin. I’ve read the documentation, but in his
example I
can’t see how to change the columns names of the view. Is it possible?

I don’t know. I’ve never used it. But the example sort of implies that
you could use AS in the select statement.

Thanks again,
Gustavo

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

“Gustavo de Sá Carvalho H.” <gustavohonorato wrote in post
#966984:

On Tue, Dec 7, 2010 at 4:29 PM, Marnen Laibow-Koser
[email protected] wrote:

I don’t know. I’ve never used it. But the example sort of implies that
you could use AS in the select statement.

It seems that rails_sql_view doesn’t work with JRuby (I’m using JRuby
and Rails 2.3). When I installed the gem, I’m getting the following
error:

undefined method `base_tables’ for
#ActiveRecord::ConnectionAdapters::JdbcAdapter:0x40eca3

/home/gustavo/.rvm/gems/jruby-1.5.2/gems/rails_sql_views-0.8.0/lib/rails_sql_views/schema_dumper.rb:98:in

`tables_with_views_excluded’

/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/schema_dumper.rb:25:in

`dump’

/home/gustavo/.rvm/gems/jruby-1.5.2/gems/rails_sql_views-0.8.0/lib/rails_sql_views/schema_dumper.rb:27:in

`dump_with_views’

/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/schema_dumper.rb:19:in

`dump’

Do you have any idea?

Probably not a JRuby issue (though from the stack trace, it looks like
it might be a JdbcAdapter issue). AFAIK that gem is pure Ruby, so it
should work. Try it with MRI?

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

On Tue, Dec 7, 2010 at 6:25 PM, Marnen Laibow-Koser
[email protected] wrote:

`dump_with_views’

/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/schema_dumper.rb:19:in

`dump’

Do you have any idea?

Probably not a JRuby issue (though from the stack trace, it looks like
it might be a JdbcAdapter issue). AFAIK that gem is pure Ruby, so it
should work. Try it with MRI?

I can’t use MRI. The project I’m working uses some Java Libs and JRuby
make more easy this integration. I went back and tried to create the
views with raw SQL, but the views are not dumped to schema.rb. I’m
stuck without any ideas and my deadline is comming :(.

I thought to reimplement this plugin, but I have no experience doing
this. Do you have any suggestions?

On Tue, Dec 7, 2010 at 4:29 PM, Marnen Laibow-Koser
[email protected] wrote:

I don’t know. I’ve never used it. But the example sort of implies that
you could use AS in the select statement.

It seems that rails_sql_view doesn’t work with JRuby (I’m using JRuby
and Rails 2.3). When I installed the gem, I’m getting the following
error:

undefined method base_tables' for #<ActiveRecord::ConnectionAdapters::JdbcAdapter:0x40eca3> /home/gustavo/.rvm/gems/jruby-1.5.2/gems/rails_sql_views-0.8.0/lib/rails_sql_views/schema_dumper.rb:98:in tables_with_views_excluded’
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/schema_dumper.rb:25:in
dump' /home/gustavo/.rvm/gems/jruby-1.5.2/gems/rails_sql_views-0.8.0/lib/rails_sql_views/schema_dumper.rb:27:in dump_with_views’
/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/schema_dumper.rb:19:in
`dump’

Do you have any idea?

On Tue, Dec 7, 2010 at 6:49 PM, Marnen Laibow-Koser
[email protected] wrote:

`dump’
can’t use it for this project.
You could set the schema_dumper to use SQL, but don’t do that. :slight_smile:
Server. It was really pretty easy.
Thanks Marnen, I’ll try to do this. I’ll come back when have news.

“Gustavo de Sá Carvalho H.” <gustavohonorato wrote in post
#966998:

On Tue, Dec 7, 2010 at 6:25 PM, Marnen Laibow-Koser
[email protected] wrote:

`dump_with_views’

/home/gustavo/.rvm/gems/jruby-1.5.2/gems/activerecord-2.3.8/lib/active_record/schema_dumper.rb:19:in

`dump’

Do you have any idea?

Probably not a JRuby issue (though from the stack trace, it looks like
it might be a JdbcAdapter issue). AFAIK that gem is pure Ruby, so it
should work. Try it with MRI?

I can’t use MRI.

You should at least be able to test the plugin with it, even if you
can’t use it for this project.

The project I’m working uses some Java Libs and JRuby
make more easy this integration.

MRI + RJB is feasible too. Depends on how you need the Java stuff.

I went back and tried to create the
views with raw SQL, but the views are not dumped to schema.rb. I’m
stuck without any ideas and my deadline is comming :(.

You could set the schema_dumper to use SQL, but don’t do that. :slight_smile:

I thought to reimplement this plugin, but I have no experience doing
this. Do you have any suggestions?

It probably wouldn’t be hard to patch the plugin to work with JDBC, if
that’s where your problem is. It’s generating pretty simple SQL. Just
look at how it generates the SQL for the adapters that it does support.

I had to do something similar when I patched Foreigner to support MS SQL
Server. It was really pretty easy.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

2010/12/7 Gustavo de S Carvalho H. [email protected]:

stuck without any ideas and my deadline is comming :(.

I had to do something similar when I patched Foreigner to support MS SQL
Server. It was really pretty easy.

Thanks Marnen, I’ll try to do this. I’ll come back when have news.

I’ve just found this plugin GitHub - eladmeidar/PlainViews: an ActiveRecord migration DSL for creating database views.
Do you know if it’s good??

“Gustavo de Sá Carvalho H.” <gustavohonorato wrote in post
#967009:

2010/12/7 Gustavo de S Carvalho H. [email protected]:

stuck without any ideas and my deadline is comming :(.

I had to do something similar when I patched Foreigner to support MS SQL
Server. It was really pretty easy.

Thanks Marnen, I’ll try to do this. I’ll come back when have news.

I’ve just found this plugin GitHub - eladmeidar/PlainViews: an ActiveRecord migration DSL for creating database views.
Do you know if it’s good??

Never heard of it. Try it, if you like!

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]