support autoload with extend_existing

[Former user]

Creating a Table with autoload=True (and useexisting=True) that already exists does not start a reflection process.

Example:
 >>> from sqlalchemy import MetaData, Table, create_engine                       
 >>> from sqlalchemy.sql import text                                             
 >>> engine = create_engine('sqlite:////home/christoph/cjklib.db', echo=False)
 >>> metadata = MetaData(bind=engine, reflect=True)
 >>> view = Table('testview', metadata)
 >>> engine.execute(text("CREATE VIEW %s AS SELECT * FROM %s JOIN %s ON %s.rowid= %s.rowid;" % ('testview', 'HanDeDict_Normal', 'HanDeDict_Text', 'HanDeDict_Normal', 'HanDeDict_Text')))
 <sqlalchemy.engine.base.ResultProxy object at 0xa3c074c>
 >>> v = Table('testview', metadata, autoload=True, useexisting=True)
 >>> v.get_children()
 []
 >>>

The second construction of the Table will yield an empty column set. Looking at the source shows, that while {{{init}}} initiates the reflection process, the code for recreating an already existing Table does not. A workaround would be calling {{{engine.reflecttable(view)}}} before doing the second instantiation.

As the example shows, autoloading does make sense on recreation, and that would be the expected behaviour in this case.

[Mike Bayer]

it might be nice if this worked, but the whole field of "changing metadata" use cases is something that historically has been out of scope for core SQLAlchemy. Since the full feature here is that the Table can be reloaded many times, i.e. you'd of course be able to autoload=True/manually configure the table as many times as you like, intricate use cases occur very quickly. The Migrate folks have taken on the task of having a Table be slightly mutable - you can add and remove columns and constraints - but they've accomplished that only in the context of a very limited use case, within a migrate script where the Table objects are not expected to be fully functioning units outside of that context.

There is only one very specific usage we support which mixes user-defined Table constructs with autoload=True, and its the "overriding columns" feature. That alone only works to a very limited degree yet still is an intricate feature, and there were also some issues which I can't recall at the moment that force the order of user-defined columns/reflected columns. With that feature, there's still simple things that right now don't work as expected, such as if you specify a Table with a Column that is not a primary key, set autoload=True, and the column reflects as a primary key, the underlying primary key constraint still remains.

Tables changing their contents repeatedly is also much more difficult to implement when we consider preserving existing SQL or ORM constructs derived from those tables. If i make a construct table.c.foo == 3, the table re-reflects, we can either choose to leave the actual "foo" Column object intact, and change its internal contents to match what we've received (this logic would need to be added), or we can replace "foo" with a new "foo" column (this is how it would work now) which means our SQL construct is broken. But then, if the table re-reflects, and its former primary key is now gone, what happens to a mapper() that was generated from that table ? We'd have to find it in a registry, raise an error, etc.

I'm hoping its apparent here that the "fixed" nature of the MetaData/ Table structure has some advantages and keeps things simple. The reason the second "autoload=True" doesn't initiate a load is because the table is already "loaded". the "auto" implies "automatically do this if needed".

[Mike Bayer]

test case is now:

from sqlalchemy import MetaData, Table, create_engine
from sqlalchemy.sql import text
engine = create_engine('sqlite:///')
engine.execute("create table foo (id integer)")
metadata = MetaData(bind=engine, reflect=True)
view = Table('bar', metadata)
engine.execute("create view bar as select * from foo")
v = Table('bar', metadata, autoload=True, extend_existing=True)
print v.get_children()

patch is attached, linking this to extend_existing.

[Mike Bayer]

81945d7a0c60cc898541189d52564df2010871c1

[Mike Bayer]

extend_existing with autoload is almost useless if autoload blows away existing columns - the usual use case is that whatever is declared in Python, even if previously present, takes precedence over what comes from the DB.

But there's a natural logic here, such that what you say in Table(), that's what takes effect; there's no "let's take the explicit columns but then autoload has some exceptions". Overall the way this works is a bit troubling. But I think for now there needs to be another flag, autoload_replace=False. See #2356.

[Mike Bayer]

Removing milestone: 0.7.4 (automated comment)