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 Honorato" <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,

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 Honorato" <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,

Please quote when replying.

"Gustavo de Sá Carvalho Honorato" <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.

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?

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#966984:

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,

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#966984:

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?

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?

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#966998:

`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,

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#966998:

`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.

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

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#966998:

`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.

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

I've just found this plugin https://github.com/eladmeidar/PlainViews.
Do you know if it's good??

"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#967009: