betodealmeida commented on PR #29860:
URL: https://github.com/apache/superset/pull/29860#issuecomment-2271251378
> I'll also need to fix this part which generates a query for each dataset,
which in Airbnb's case is more than 25,000 queries.
>
> ```
> # update `schema_perm` and `catalog_perm` for tables and charts
> for table in session.query(SqlaTable).filter_by(
> database_id=database.id,
> catalog=None,
> ):
> schema_perm = security_manager.get_schema_perm(
> database.database_name,
> default_catalog,
> table.schema,
> )
>
> table.catalog = default_catalog
> table.catalog_perm = catalog_perm
> table.schema_perm = schema_perm
>
> for chart in session.query(Slice).filter_by(
> datasource_id=table.id,
> datasource_type="table",
> ):
> chart.catalog_perm = catalog_perm
> chart.schema_perm = schema_perm
> ```
You might be able to rewrite this as a single query for some dialects:
```sql
UPDATE tables
SET
catalog=${default_catalog},
catalog_perm=${catalog_perm},
schema_perm=REGEXP_REPLACE(schema_perm, '\[([^\]]+)\]\.\[([^\]]+)\]',
'[\1].[${default_catalog}].[\2]', 'g')
WHERE
database_id=${database_id} AND
catalog IS NULL
RETURNING id;
```
For charts you can then use the returned IDs from the query above to build a
similar `UPDATE` query.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
