On 8.5.2012, at 0:18, Michael Bayer wrote:
>
> On May 7, 2012, at 6:09 PM, Stefan Urbanek wrote:
>
>>
>> p.s.: On the other hand, Table.is_view might be a good flag - to maintain
>> consistency with the fact that Table() can reflect a view. However, I am not
>> sure how does that fit into overall design of the library.
>
> the reason is that it's a partial, broken API:
>
> t = Table("myview", metadata,
> Column(...),
> Column(...),
> # ...
> is_view=True
> )
>
> metadata.create_all()
>
> ...to which we get, what exactly ? a view is not just a bunch of columns,
> you need the whole view definition.
>
> similarly:
>
> mytable = Table("someview", metadata, autoload=True)
>
> assert mytable.is_view
>
> mytable_2 = mytable.tometadata(othermetadata)
>
> othermetadata.create_all()
>
> -> same thing ! how do we do a CREATE ? is the whole view definition
> present ? do we have round trip tests for all that ? can I reflect a view
> from Oracle and recreate on SQLite ? the answer is: absolutely not. It
> would require that we can fully parse SQL, intelligently enough even to
> translate it into another platform, which is not just out of scope, it would
> be a vastly complicated process.
>
You are right, I agree with that. That would create more confusion and even
more inconsistencies.
> Hence the whole thing stays as a recipe, and also why you're finding it
> awkward that you'd like to emit DROP for views that you've reflected; the
> "view reflection" use case was at most intended as a read only use case.
>
Creating views (and therefore dropping them as well) should not belong to Table
and I see no problem with the way described in the recipe. The read-only flag
might be really helpful though - once the table/view is reflected, it might be
known what the source object type was (if it is possible to get it from DB
engine/backend).
Btw. this what I did finally and seems to work:
# we already have: view_name, metadata, schema, statement (selectable)
table = sqlalchemy.Table(view_name, metadata,
autoload=False, schema=schema)
full_name = "%s.%s" % (schema, view_name) if schema else view_name
if table.exists():
inspector = sqlalchemy.engine.reflection.Inspector.from_engine(engine)
view_names = inspector.get_view_names(schema=schema)
if view_name in view_names:
# Table reflects a view
drop_statement = "DROP VIEW %s" % full_name
engine.execute(drop_statement)
else:
# Table reflects a table
table.drop(checkfirst=False)
if materialize:
create_stat = "CREATE TABLE"
else:
create_stat = "CREATE OR REPLACE VIEW"
statement = "%s %s AS %s" % (create_stat, full_name, str(statement))
engine.execute(statement)
> I'm not 100% sure how "view reflection" even got into the library, to be
> honest, as it really doesn't belong in Table. a Table is not a view.
Table is not a view, right. On the other hand, views are quite useful and in
analytical domain they are being used quite a lot. The analytical data are
mostly read-only during their use, therefore I see no problem with treating
VIEWs as read-only TABLEs in this case. I would definitely not drop view
reflection from the library, as it is makes building another kinds of
abstractions/tools (besides ORM) much more easier.
Stefan
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
