Incubator-superset: Include schema on sqlatable's unique constraint

[aspedrosa]

On a project that I'm working on we have different schemas on the same database (postgres) and all schemas have the same ddl.
Since the SqlaTable model has a unique constrain on the fields "database_id" and "table_name" we can't import the tables.

The solution would be to add the "schema" field to the existing unique constraint of the SqlaTable model.
To prevent having several tables with the same name and database on the list endpoint of the tablemodelview I would also add the schema to the list_columns

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #enhancement to this issue, with a confidence of 0.87. Please mark this comment with :thumbsup: or :thumbsdown: to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[avielb]

Hey, we are facing the same issue as well and would like to see that enhancement going into superset.

[Ron-Merkin-Nielsen]

We've found a workaround for that issue. Basically even though it's two different schemas it won't allow to create a new table because of the unique name (the table's name).
What we did to solve this issue for now was:

1. go to the sql lab --> sql editor.
2. created a query relevant to our needs (by the relevant schema).
3. after the results are displayed you press the explore button on top of the results table.

then what happens is that it created kind of a unique datasource.
this way we managed to display tables from different schemas with the same table name.

[nytai]

@aspedrosa There was actually a migration to introduce the change you're proposing.

I've opened a PR to reflect that change in the model.

If you're running SQLite that migration fails with

NotImplementedError: No support for ALTER of constraints in SQLite dialect

since SQLite does not support ALTER statements. This could be fixed my running a migration to recreate the entire table, but changing the uniqueness constraint, but that seems a little excessive. I'd suggest running another database, that supports altering a constraint, as your main/metadata db (I tested with postgres and was able to import multiple tables with the same name but different schemas).

Regarding:

To prevent having several tables with the same name and database on the list endpoint of the tablemodelview I would also add the schema to the list_columns

If you include a schema when importing a table, the table name will appear as [schema].[table] in the list view.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.