On Thu, 23 Oct 2008 16:16:50 -0400
Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> On Oct 23, 2008, at 3:40 PM, [EMAIL PROTECTED] wrote:
>
> >
> > hi
> > i've no much idea about sql views, so i want to ask something.
> > As i understand, views are sort-of virtual tables consisting of
> > whatever a query structure may contain. So:
> > 1) is it possible sql views to be created via SA in a more or less
> > server-independent way? if issuing raw sql is the only way, any
> > pointer how it is done in, say, sqlite, postgres, mssql?
> > 2) once there, what can be done with them? are they a
> > readonly "table"? can they have "foreign keys" i.e. relations to and
> > from?
> > 3) what is the advantage of a view vs issuing the ~same query over
> > and over? i mean, if it's just faster, roughly how much - 2x,
> > 10x, ..?
>
>
> theres two flavors of view (at least)...materialized and non
> materialized.
> [...]
>
> As far as syntax, I'm not deeply familiar with the differences, I've
> only done them on Oracle. I don't recall them being able to have
> their own foreign keys and such. I do know that "writeable" views
> exist as well but I'm not sure of the details of that.
> [...]
I would expect that, beyond read-only non-materialized views, all the
DBMSs are going to vary wildly in semantics, let alone in syntax.
I've only used them in PostgreSQL which, for example, implements views
with its production rule system. This is sort of like Apache
mod_rewrite, on steroids, for SQL.[1]
The CREATE VIEW syntax for creating read-only, non-materialized views is
actually syntactic sugar for creating an empty table, and attaching a
rule to rewrite SELECT queries against it. The view can then be made
writable by adding rules describing how to rewrite INSERTs, UPDATEs, and
DELETEs, or materialized by adding triggers and SELECT INTO.[2]
The upside of this is that it's extremely expressive, and the
"rewriting" happens before the planner, so it plans based on the reality
of how the data is actually stored, rather than the way things appear
from your SQL statement.
The downside is that if you want anything other than read-only
non-materialized views, you have to get your hands dirty with rules.
The docs[3], however, are very helpful.
-Kyle
[1] Well, it looks like that from a galloping horse, anyway. In reality
it's somewhat more elegant becaues it rewrites queries
abstractly/symbolically instead of with string-munging.
[2] http://jonathangardner.net/tech/w/PostgreSQL/Materialized_Views#Intr
oduction
[3] http://www.postgresql.org/docs/8.3/static/rules.html
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
