get_table_names should not return views

[Former user]

firebird dialect returns views when using get_table_names

[Mike Bayer]

I can't reproduce this issue. Can you please provide a test case and full information about DBAPI in use and Firebird version?

[Mike Bayer]

changeset a51c41e6305c3fcd64bdb10f8b (not pushed yet) adds a test with this DEBUG output:

CREATE TABLE users (
    user_id INTEGER NOT NULL, 
    test1 CHAR(5) NOT NULL, 
    test2 FLOAT NOT NULL, 
    parent_user_id INTEGER, 
    PRIMARY KEY (user_id), 
    FOREIGN KEY(parent_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE email_addresses (
    address_id INTEGER, 
    remote_user_id INTEGER, 
    email_address VARCHAR(20), 
    CONSTRAINT email_ad_pk PRIMARY KEY (address_id), 
    FOREIGN KEY(remote_user_id) REFERENCES users (user_id)
)


CREATE TABLE dingalings (
    dingaling_id INTEGER NOT NULL, 
    address_id INTEGER, 
    data VARCHAR(30), 
    PRIMARY KEY (dingaling_id), 
    FOREIGN KEY(address_id) REFERENCES email_addresses (address_id)
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:CREATE VIEW users_v AS SELECT * FROM users
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:CREATE VIEW email_addresses_v AS SELECT * FROM email_addresses
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
test.dialect.test_suite.ComponentReflectionTest.test_get_tables_and_views ... INFO:sqlalchemy.engine.base.Engine:
        SELECT DISTINCT rdb$relation_name
        FROM rdb$relation_fields
        WHERE rdb$system_flag=0 AND rdb$view_context IS NULL

INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('RDB$RELATION_NAME',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('DINGALINGS                     ',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('EMAIL_ADDRESSES                ',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('USERS                          ',)
INFO:sqlalchemy.engine.base.Engine:
        SELECT distinct rdb$view_name
        FROM rdb$view_relations

INFO:sqlalchemy.engine.base.Engine:()
DEBUG:sqlalchemy.engine.base.Engine:Col ('RDB$VIEW_NAME',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('EMAIL_ADDRESSES_V              ',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('USERS_V                        ',)
ok

as you can see, "WHERE rdb$system_flag=0 AND rdb$view_context IS NULL" delivers us only table names. Can you please provide detail as to the conditions/rationale for the patch? thanks.

[Mike Bayer]

the existing query seems to be looking at the fields of the relation and extrapolating the view name from that. The new query does look more appropriate, however need a test case - how to produce a column that is part of a view but has "view context" as NULL?

[Mike Bayer]

link here: http://www.alberton.info/firebird_sql_meta_info.html#.Ur3vXfZGni8 includes both queries as interchangeable: "To exclude the VIEWs from the resultset, you can write one of these queries"

[Mike Bayer]

so my suspicion here is that both queries work fine. But since the Firebird FAQ refers to "view_blr" (http://www.firebirdfaq.org/faq174/), might as well change it.

[Mike Bayer]

f50c670e47bb562ee23b52f in 0.9

[Mike Bayer]

Removing milestone: 0.9.0 (automated comment)