One of the items which has come out of the ActiveWarehouse plugin
development is the Rails SQL Views plugin. It adds support for
create_view and drop_view statements in migrations and also supports
extracting those elements to the schema.rb dump. Currently it is
implemented for MySQL and PostgreSQL. Is there any interest in
eventually moving this code into ActiveRecord or should it just remain
as an extension gem/plugin?
If there is interest to bring it into the AR core then I can create
the patches for it.
I think possibly the biggest issue with bringing views into AR's
schema notation is that most (useful) views don't just contain columns
from other tables; they often perform functions (SUM, AVG). From
looking at the current plugin, it seems like you'd end up having to
write that as a fairly explicit SQL statement, and I'm not sure in
such cases how great the value in using Ruby to declare the columns.
In a nutshell, is
create_view :v_stuff, %{
SELECT thing_id, other_thing_id, SUM(stuff) as blah
FROM things, other things
WHERE thing_attribute LIKE "wibble"
GROUP BY other_things.cost
} do |v|
v.column :thing_id
v.column :other_thing_id
v.column :blah
end
really that much better than
SomeModel.connection.execute %{
CREATE VIEW v_stuff AS (
SELECT thing_id, other_thing_id, SUM(stuff) as blah
FROM things, other things
WHERE thing_attribute LIKE "wibble"
GROUP BY other_things.cost )
}
... I'm not sure. Convince me, because we're using views with
legacy-style databases at the moment, and if anything can be done make
this simpler, I'm definitely interested.
I think the primary benefit would be that views would be included in
schema.rb and thus would be usable in the test environment with no
additional work required. The primary thing is that Rails would
understand views whereas right now views just get silently ignored.
I think possibly the biggest issue with bringing views into AR's
schema notation is that most (useful) views don't just contain columns
from other tables; they often perform functions (SUM, AVG). From
looking at the current plugin, it seems like you'd end up having to
write that as a fairly explicit SQL statement, and I'm not sure in
such cases how great the value in using Ruby to declare the columns.
In a nutshell, is
create_view :v_stuff, %{
SELECT thing_id, other_thing_id, SUM(stuff) as blah
FROM things, other things
WHERE thing_attribute LIKE "wibble"
GROUP BY other_things.cost
} do |v|
v.column :thing_id
v.column :other_thing_id
v.column :blah
end
really that much better than
SomeModel.connection.execute %{
CREATE VIEW v_stuff AS (
SELECT thing_id, other_thing_id, SUM(stuff) as blah
FROM things, other things
WHERE thing_attribute LIKE "wibble"
GROUP BY other_things.cost )
}
This is my first post to this list.
We now working on integration of our Valentina database into RoR.
During this, we have note, that RoR people are used to specify field alias
without single quotes, as above for example.
SELECT thing_id, other_thing_id, SUM(stuff) as blah
^^^^^^^^^
I wonder, may be it can be more accurate if ROR will produce SQL queries
using single quotes, according to SQL92 standard?
Also, please, let me know if this list is correct place for talk/questions
about Valentina database integration into ROR?
Maybe, one has to think about a different syntax, e.g., something like:
create_view :my_view do |v|
v.column :thing :from => table1.thing
v.column :thing2 :from => table2.thing
v.conditions :where=>some sql, :group => again_sql
end
Well, personally, I am convinced that extending AR with capabilities of
modeling also sql views is essential advantage.
