You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by be...@apache.org on 2024/05/06 23:05:44 UTC
(superset) 01/01: feat(SIP-95): catalogs in SQL Lab
This is an automated email from the ASF dual-hosted git repository.
beto pushed a commit to branch catalog-sqllab
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 7ca8b2abf5759b04171c85242811485883ebddd4
Author: Beto Dealmeida <ro...@dealmeida.net>
AuthorDate: Mon May 6 19:03:22 2024 -0400
feat(SIP-95): catalogs in SQL Lab
---
superset-frontend/src/SqlLab/actions/sqlLab.js | 18 +++-
.../SqlLab/components/SqlEditorLeftBar/index.tsx | 34 +++++--
.../src/SqlLab/components/TableElement/index.tsx | 4 +-
superset-frontend/src/SqlLab/reducers/sqlLab.js | 12 +++
superset-frontend/src/SqlLab/types.ts | 2 +
.../src/components/DatabaseSelector/index.tsx | 101 +++++++++++++++++++--
.../src/components/TableSelector/index.tsx | 29 +++++-
.../databases/DatabaseModal/ExtraOptions.tsx | 23 ++++-
superset-frontend/src/features/databases/types.ts | 3 +
.../hooks/apiResources/{schemas.ts => catalogs.ts} | 43 +++++----
superset-frontend/src/hooks/apiResources/index.ts | 1 +
.../src/hooks/apiResources/queryApi.ts | 1 +
.../src/hooks/apiResources/schemas.ts | 31 +++++--
superset-frontend/src/hooks/apiResources/tables.ts | 48 ++++++----
superset/databases/schemas.py | 16 +++-
superset/db_engine_specs/base.py | 5 +-
superset/sqllab/schemas.py | 1 +
superset/sqllab/sqllab_execution_context.py | 4 +
18 files changed, 307 insertions(+), 69 deletions(-)
diff --git a/superset-frontend/src/SqlLab/actions/sqlLab.js b/superset-frontend/src/SqlLab/actions/sqlLab.js
index 6befa17ac1..de7fe75c8e 100644
--- a/superset-frontend/src/SqlLab/actions/sqlLab.js
+++ b/superset-frontend/src/SqlLab/actions/sqlLab.js
@@ -55,6 +55,7 @@ export const REMOVE_QUERY = 'REMOVE_QUERY';
export const EXPAND_TABLE = 'EXPAND_TABLE';
export const COLLAPSE_TABLE = 'COLLAPSE_TABLE';
export const QUERY_EDITOR_SETDB = 'QUERY_EDITOR_SETDB';
+export const QUERY_EDITOR_SET_CATALOG = 'QUERY_EDITOR_SET_CATALOG';
export const QUERY_EDITOR_SET_SCHEMA = 'QUERY_EDITOR_SET_SCHEMA';
export const QUERY_EDITOR_SET_TITLE = 'QUERY_EDITOR_SET_TITLE';
export const QUERY_EDITOR_SET_AUTORUN = 'QUERY_EDITOR_SET_AUTORUN';
@@ -326,6 +327,7 @@ export function runQuery(query) {
database_id: query.dbId,
json: true,
runAsync: query.runAsync,
+ catalog: query.catalog,
schema: query.schema,
sql: query.sql,
sql_editor_id: query.sqlEditorId,
@@ -797,6 +799,14 @@ export function queryEditorSetDb(queryEditor, dbId) {
return { type: QUERY_EDITOR_SETDB, queryEditor, dbId };
}
+export function queryEditorSetCatalog(queryEditor, catalog) {
+ return {
+ type: QUERY_EDITOR_SET_CATALOG,
+ queryEditor: queryEditor || {},
+ catalog,
+ };
+}
+
export function queryEditorSetSchema(queryEditor, schema) {
return {
type: QUERY_EDITOR_SET_SCHEMA,
@@ -954,12 +964,13 @@ export function mergeTable(table, query, prepend) {
return { type: MERGE_TABLE, table, query, prepend };
}
-export function addTable(queryEditor, tableName, schemaName) {
+export function addTable(queryEditor, tableName, catalogName, schemaName) {
return function (dispatch, getState) {
const query = getUpToDateQuery(getState(), queryEditor, queryEditor.id);
const table = {
dbId: query.dbId,
queryEditorId: query.id,
+ catalog: catalogName,
schema: schemaName,
name: tableName,
};
@@ -983,12 +994,14 @@ export function runTablePreviewQuery(newTable) {
sqlLab: { databases },
} = getState();
const database = databases[newTable.dbId];
- const { dbId } = newTable;
+ const { dbId, catalog, schema } = newTable;
if (database && !database.disable_data_preview) {
const dataPreviewQuery = {
id: shortid.generate(),
dbId,
+ catalog,
+ schema,
sql: newTable.selectStar,
tableName: newTable.name,
sqlEditorId: null,
@@ -1003,6 +1016,7 @@ export function runTablePreviewQuery(newTable) {
{
id: newTable.id,
dbId: newTable.dbId,
+ catalog: newTable.catalog,
schema: newTable.schema,
name: newTable.name,
queryEditorId: newTable.queryEditorId,
diff --git a/superset-frontend/src/SqlLab/components/SqlEditorLeftBar/index.tsx b/superset-frontend/src/SqlLab/components/SqlEditorLeftBar/index.tsx
index 15a1735626..1eee80f485 100644
--- a/superset-frontend/src/SqlLab/components/SqlEditorLeftBar/index.tsx
+++ b/superset-frontend/src/SqlLab/components/SqlEditorLeftBar/index.tsx
@@ -34,6 +34,7 @@ import {
removeTables,
collapseTable,
expandTable,
+ queryEditorSetCatalog,
queryEditorSetSchema,
setDatabases,
addDangerToast,
@@ -115,13 +116,17 @@ const SqlEditorLeftBar = ({
shallowEqual,
);
const dispatch = useDispatch();
- const queryEditor = useQueryEditor(queryEditorId, ['dbId', 'schema']);
+ const queryEditor = useQueryEditor(queryEditorId, [
+ 'dbId',
+ 'catalog',
+ 'schema',
+ ]);
const [emptyResultsWithSearch, setEmptyResultsWithSearch] = useState(false);
const [userSelectedDb, setUserSelected] = useState<DatabaseObject | null>(
null,
);
- const { schema } = queryEditor;
+ const { catalog, schema } = queryEditor;
useEffect(() => {
const bool = querystring.parse(window.location.search).db;
@@ -138,9 +143,9 @@ const SqlEditorLeftBar = ({
}
}, [database]);
- const onEmptyResults = (searchText?: string) => {
+ const onEmptyResults = useCallback((searchText?: string) => {
setEmptyResultsWithSearch(!!searchText);
- };
+ }, []);
const onDbChange = ({ id: dbId }: { id: number }) => {
setEmptyState?.(false);
@@ -152,7 +157,11 @@ const SqlEditorLeftBar = ({
[tables],
);
- const onTablesChange = (tableNames: string[], schemaName: string) => {
+ const onTablesChange = (
+ tableNames: string[],
+ catalogName: string | null,
+ schemaName: string,
+ ) => {
if (!schemaName) {
return;
}
@@ -169,7 +178,7 @@ const SqlEditorLeftBar = ({
});
tablesToAdd.forEach(tableName => {
- dispatch(addTable(queryEditor, tableName, schemaName));
+ dispatch(addTable(queryEditor, tableName, catalogName, schemaName));
});
dispatch(removeTables(currentTables));
@@ -210,6 +219,15 @@ const SqlEditorLeftBar = ({
const shouldShowReset = window.location.search === '?reset=1';
const tableMetaDataHeight = height - 130; // 130 is the height of the selects above
+ const handleCatalogChange = useCallback(
+ (catalog: string | null) => {
+ if (queryEditor) {
+ dispatch(queryEditorSetCatalog(queryEditor, catalog));
+ }
+ },
+ [dispatch, queryEditor],
+ );
+
const handleSchemaChange = useCallback(
(schema: string) => {
if (queryEditor) {
@@ -246,9 +264,11 @@ const SqlEditorLeftBar = ({
getDbList={handleDbList}
handleError={handleError}
onDbChange={onDbChange}
+ onCatalogChange={handleCatalogChange}
+ catalog={catalog}
onSchemaChange={handleSchemaChange}
- onTableSelectChange={onTablesChange}
schema={schema}
+ onTableSelectChange={onTablesChange}
tableValue={selectedTableNames}
sqlLabMode
/>
diff --git a/superset-frontend/src/SqlLab/components/TableElement/index.tsx b/superset-frontend/src/SqlLab/components/TableElement/index.tsx
index e29a654c22..2f93491e88 100644
--- a/superset-frontend/src/SqlLab/components/TableElement/index.tsx
+++ b/superset-frontend/src/SqlLab/components/TableElement/index.tsx
@@ -101,7 +101,7 @@ const StyledCollapsePanel = styled(Collapse.Panel)`
`;
const TableElement = ({ table, ...props }: TableElementProps) => {
- const { dbId, schema, name, expanded } = table;
+ const { dbId, catalog, schema, name, expanded } = table;
const theme = useTheme();
const dispatch = useDispatch();
const {
@@ -112,6 +112,7 @@ const TableElement = ({ table, ...props }: TableElementProps) => {
} = useTableMetadataQuery(
{
dbId,
+ catalog,
schema,
table: name,
},
@@ -125,6 +126,7 @@ const TableElement = ({ table, ...props }: TableElementProps) => {
} = useTableExtendedMetadataQuery(
{
dbId,
+ catalog,
schema,
table: name,
},
diff --git a/superset-frontend/src/SqlLab/reducers/sqlLab.js b/superset-frontend/src/SqlLab/reducers/sqlLab.js
index 9ffcb4dfcb..83a07ba9a9 100644
--- a/superset-frontend/src/SqlLab/reducers/sqlLab.js
+++ b/superset-frontend/src/SqlLab/reducers/sqlLab.js
@@ -503,6 +503,18 @@ export default function sqlLabReducer(state = {}, action) {
),
};
},
+ [actions.QUERY_EDITOR_SET_CATALOG]() {
+ return {
+ ...state,
+ ...alterUnsavedQueryEditorState(
+ state,
+ {
+ catalog: action.catalog,
+ },
+ action.queryEditor.id,
+ ),
+ };
+ },
[actions.QUERY_EDITOR_SET_SCHEMA]() {
return {
...state,
diff --git a/superset-frontend/src/SqlLab/types.ts b/superset-frontend/src/SqlLab/types.ts
index cac9ceb5d9..b1dea6f2e3 100644
--- a/superset-frontend/src/SqlLab/types.ts
+++ b/superset-frontend/src/SqlLab/types.ts
@@ -50,6 +50,7 @@ export interface QueryEditor {
dbId?: number;
name: string;
title?: string; // keep it optional for backward compatibility
+ catalog?: string | null;
schema?: string;
autorun: boolean;
sql: string;
@@ -81,6 +82,7 @@ export type UnsavedQueryEditor = Partial<QueryEditor>;
export interface Table {
id: string;
dbId: number;
+ catalog: string | null;
schema: string;
name: string;
queryEditorId: QueryEditor['id'];
diff --git a/superset-frontend/src/components/DatabaseSelector/index.tsx b/superset-frontend/src/components/DatabaseSelector/index.tsx
index 0c0268db5c..1b8eef5787 100644
--- a/superset-frontend/src/components/DatabaseSelector/index.tsx
+++ b/superset-frontend/src/components/DatabaseSelector/index.tsx
@@ -24,7 +24,12 @@ import Label from 'src/components/Label';
import { FormLabel } from 'src/components/Form';
import RefreshLabel from 'src/components/RefreshLabel';
import { useToasts } from 'src/components/MessageToasts/withToasts';
-import { useSchemas, SchemaOption } from 'src/hooks/apiResources';
+import {
+ useCatalogs,
+ CatalogOption,
+ useSchemas,
+ SchemaOption,
+} from 'src/hooks/apiResources';
const DatabaseSelectorWrapper = styled.div`
${({ theme }) => `
@@ -92,9 +97,11 @@ export interface DatabaseSelectorProps {
isDatabaseSelectEnabled?: boolean;
onDbChange?: (db: DatabaseObject) => void;
onEmptyResults?: (searchText?: string) => void;
+ onCatalogChange?: (catalog?: string) => void;
+ catalog?: string | null;
onSchemaChange?: (schema?: string) => void;
- readOnly?: boolean;
schema?: string;
+ readOnly?: boolean;
sqlLabMode?: boolean;
}
@@ -113,6 +120,7 @@ const SelectLabel = ({
</LabelStyle>
);
+const EMPTY_CATALOG_OPTIONS: CatalogOption[] = [];
const EMPTY_SCHEMA_OPTIONS: SchemaOption[] = [];
export default function DatabaseSelector({
@@ -124,12 +132,19 @@ export default function DatabaseSelector({
isDatabaseSelectEnabled = true,
onDbChange,
onEmptyResults,
+ onCatalogChange,
+ catalog,
onSchemaChange,
- readOnly = false,
schema,
+ readOnly = false,
sqlLabMode = false,
}: DatabaseSelectorProps) {
const [currentDb, setCurrentDb] = useState<DatabaseValue | undefined>();
+ const [currentCatalog, setCurrentCatalog] = useState<
+ CatalogOption | undefined
+ >(catalog ? { label: catalog, value: catalog, title: catalog } : undefined);
+ const catalogRef = useRef(catalog);
+ catalogRef.current = catalog;
const [currentSchema, setCurrentSchema] = useState<SchemaOption | undefined>(
schema ? { label: schema, value: schema, title: schema } : undefined,
);
@@ -193,7 +208,7 @@ export default function DatabaseSelector({
};
});
},
- [formMode, getDbList, sqlLabMode],
+ [formMode, getDbList, sqlLabMode, onEmptyResults],
);
useEffect(() => {
@@ -215,6 +230,32 @@ export default function DatabaseSelector({
);
}, [db]);
+ const {
+ data: catalogData,
+ isFetching: loadingCatalogs,
+ refetch: refetchCatalogs,
+ } = useCatalogs({
+ dbId: currentDb?.value,
+ onSuccess: (catalogs, isFetched) => {
+ if (catalogs.length === 1) {
+ changeCatalog(catalogs[0]);
+ } else if (
+ !catalogs.find(
+ catalogOption => catalogRef.current === catalogOption.value,
+ )
+ ) {
+ changeCatalog(undefined);
+ }
+
+ if (isFetched) {
+ addSuccessToast('List refreshed');
+ }
+ },
+ onError: () => handleError(t('There was an error loading the catalogs')),
+ });
+
+ const catalogOptions = catalogData || EMPTY_CATALOG_OPTIONS;
+
function changeSchema(schema: SchemaOption | undefined) {
setCurrentSchema(schema);
if (onSchemaChange && schema?.value !== schemaRef.current) {
@@ -223,11 +264,12 @@ export default function DatabaseSelector({
}
const {
- data,
+ data: schemaData,
isFetching: loadingSchemas,
- refetch,
+ refetch: refetchSchemas,
} = useSchemas({
dbId: currentDb?.value,
+ catalog: currentCatalog?.value,
onSuccess: (schemas, isFetched) => {
if (schemas.length === 1) {
changeSchema(schemas[0]);
@@ -244,17 +286,29 @@ export default function DatabaseSelector({
onError: () => handleError(t('There was an error loading the schemas')),
});
- const schemaOptions = data || EMPTY_SCHEMA_OPTIONS;
+ const schemaOptions = schemaData || EMPTY_SCHEMA_OPTIONS;
+
+ function changeCatalog(catalog: CatalogOption | undefined) {
+ setCurrentCatalog(catalog);
+ setCurrentSchema(undefined);
+ if (onCatalogChange && catalog?.value !== catalogRef.current) {
+ onCatalogChange(catalog?.value);
+ }
+ }
- function changeDataBase(
+ function changeDatabase(
value: { label: string; value: number },
database: DatabaseValue,
) {
setCurrentDb(database);
+ setCurrentCatalog(undefined);
setCurrentSchema(undefined);
if (onDbChange) {
onDbChange(database);
}
+ if (onCatalogChange) {
+ onCatalogChange(undefined);
+ }
if (onSchemaChange) {
onSchemaChange(undefined);
}
@@ -278,7 +332,7 @@ export default function DatabaseSelector({
header={<FormLabel>{t('Database')}</FormLabel>}
lazyLoading={false}
notFoundContent={emptyState}
- onChange={changeDataBase}
+ onChange={changeDatabase}
value={currentDb}
placeholder={t('Select database or type to search databases')}
disabled={!isDatabaseSelectEnabled || readOnly}
@@ -288,10 +342,36 @@ export default function DatabaseSelector({
);
}
+ function renderCatalogSelect() {
+ const refreshIcon = !readOnly && (
+ <RefreshLabel
+ onClick={() => refetchCatalogs()}
+ tooltipContent={t('Force refresh catalog list')}
+ />
+ );
+ return renderSelectRow(
+ <Select
+ ariaLabel={t('Select catalog or type to search catalogs')}
+ disabled={!currentDb || readOnly}
+ header={<FormLabel>{t('Catalog')}</FormLabel>}
+ labelInValue
+ loading={loadingCatalogs}
+ name="select-catalog"
+ notFoundContent={t('No compatible catalog found')}
+ placeholder={t('Select catalog or type to search catalogs')}
+ onChange={item => changeCatalog(item as CatalogOption)}
+ options={catalogOptions}
+ showSearch
+ value={currentCatalog}
+ />,
+ refreshIcon,
+ );
+ }
+
function renderSchemaSelect() {
const refreshIcon = !readOnly && (
<RefreshLabel
- onClick={() => refetch()}
+ onClick={() => refetchSchemas()}
tooltipContent={t('Force refresh schema list')}
/>
);
@@ -317,6 +397,7 @@ export default function DatabaseSelector({
return (
<DatabaseSelectorWrapper data-test="DatabaseSelector">
{renderDatabaseSelect()}
+ {renderCatalogSelect()}
{renderSchemaSelect()}
</DatabaseSelectorWrapper>
);
diff --git a/superset-frontend/src/components/TableSelector/index.tsx b/superset-frontend/src/components/TableSelector/index.tsx
index b7c92a978a..e31c71321a 100644
--- a/superset-frontend/src/components/TableSelector/index.tsx
+++ b/superset-frontend/src/components/TableSelector/index.tsx
@@ -97,13 +97,19 @@ interface TableSelectorProps {
handleError: (msg: string) => void;
isDatabaseSelectEnabled?: boolean;
onDbChange?: (db: DatabaseObject) => void;
+ onCatalogChange?: (catalog?: string | null) => void;
onSchemaChange?: (schema?: string) => void;
readOnly?: boolean;
+ catalog?: string | null;
schema?: string;
onEmptyResults?: (searchText?: string) => void;
sqlLabMode?: boolean;
tableValue?: string | string[];
- onTableSelectChange?: (value?: string | string[], schema?: string) => void;
+ onTableSelectChange?: (
+ value?: string | string[],
+ catalog?: string | null,
+ schema?: string,
+ ) => void;
tableSelectMode?: 'single' | 'multiple';
customTableOptionLabelRenderer?: (table: Table) => JSX.Element;
}
@@ -159,9 +165,11 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
handleError,
isDatabaseSelectEnabled = true,
onDbChange,
+ onCatalogChange,
onSchemaChange,
readOnly = false,
onEmptyResults,
+ catalog,
schema,
sqlLabMode = true,
tableSelectMode = 'single',
@@ -170,6 +178,9 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
customTableOptionLabelRenderer,
}) => {
const { addSuccessToast } = useToasts();
+ const [currentCatalog, setCurrentCatalog] = useState<
+ string | null | undefined
+ >(catalog);
const [currentSchema, setCurrentSchema] = useState<string | undefined>(
schema,
);
@@ -182,6 +193,7 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
refetch,
} = useTables({
dbId: database?.id,
+ catalog: currentCatalog,
schema: currentSchema,
onSuccess: (data, isFetched) => {
if (isFetched) {
@@ -218,6 +230,7 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
useEffect(() => {
// reset selections
if (database === undefined) {
+ setCurrentCatalog(undefined);
setCurrentSchema(undefined);
setTableSelectValue(undefined);
}
@@ -245,6 +258,7 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
Array.isArray(selectedOptions)
? selectedOptions.map(option => option?.value)
: selectedOptions?.value,
+ currentCatalog,
currentSchema,
);
} else {
@@ -258,6 +272,17 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
}
};
+ const internalCatalogChange = (catalog?: string | null) => {
+ setCurrentCatalog(catalog);
+ if (onCatalogChange) {
+ onCatalogChange(catalog);
+ }
+
+ setCurrentSchema(undefined);
+ const value = tableSelectMode === 'single' ? undefined : [];
+ internalTableChange(value);
+ };
+
const internalSchemaChange = (schema?: string) => {
setCurrentSchema(schema);
if (onSchemaChange) {
@@ -328,6 +353,8 @@ const TableSelector: FunctionComponent<TableSelectorProps> = ({
handleError={handleError}
onDbChange={readOnly ? undefined : internalDbChange}
onEmptyResults={onEmptyResults}
+ onCatalogChange={readOnly ? undefined : internalCatalogChange}
+ catalog={currentCatalog}
onSchemaChange={readOnly ? undefined : internalSchemaChange}
schema={currentSchema}
sqlLabMode={sqlLabMode}
diff --git a/superset-frontend/src/features/databases/DatabaseModal/ExtraOptions.tsx b/superset-frontend/src/features/databases/DatabaseModal/ExtraOptions.tsx
index 28e02a02f0..5700b59c85 100644
--- a/superset-frontend/src/features/databases/DatabaseModal/ExtraOptions.tsx
+++ b/superset-frontend/src/features/databases/DatabaseModal/ExtraOptions.tsx
@@ -56,6 +56,8 @@ const ExtraOptions = ({
const createAsOpen = !!(db?.allow_ctas || db?.allow_cvas);
const isFileUploadSupportedByEngine =
db?.engine_information?.supports_file_upload;
+ const supportsDynamicCatalog =
+ db?.engine_information?.supports_dynamic_catalog;
// JSON.parse will deep parse engine_params
// if it's an object, and we want to keep it a string
@@ -191,7 +193,8 @@ const ExtraOptions = ({
<IndeterminateCheckbox
id="allows_virtual_table_explore"
indeterminate={false}
- checked={!!extraJson?.allows_virtual_table_explore}
+ // when `allows_virtual_table_explore` is not present in `extra` it defaults to true
+ checked={extraJson?.allows_virtual_table_explore !== false}
onChange={onExtraInputChange}
labelText={t('Allow this database to be explored')}
/>
@@ -587,6 +590,24 @@ const ExtraOptions = ({
/>
</div>
</StyledInputContainer>
+ {supportsDynamicCatalog && (
+ <StyledInputContainer css={no_margin_bottom}>
+ <div className="input-container">
+ <IndeterminateCheckbox
+ id="allow_multi_catalog"
+ indeterminate={false}
+ checked={!!extraJson?.allow_multi_catalog}
+ onChange={onExtraInputChange}
+ labelText={t('Allow changing catalogs')}
+ />
+ <InfoTooltip
+ tooltip={t(
+ 'Give access to multiple catalogs in a single database connection.',
+ )}
+ />
+ </div>
+ </StyledInputContainer>
+ )}
</Collapse.Panel>
</Collapse>
);
diff --git a/superset-frontend/src/features/databases/types.ts b/superset-frontend/src/features/databases/types.ts
index 06799ebafa..a09ad174a0 100644
--- a/superset-frontend/src/features/databases/types.ts
+++ b/superset-frontend/src/features/databases/types.ts
@@ -109,6 +109,7 @@ export type DatabaseObject = {
engine_information?: {
supports_file_upload?: boolean;
disable_ssh_tunneling?: boolean;
+ supports_dynamic_catalog?: boolean;
};
// SSH Tunnel information
@@ -202,6 +203,7 @@ export type DatabaseForm = {
engine_information: {
supports_file_upload: boolean;
disable_ssh_tunneling: boolean;
+ supports_dynamic_catalog: boolean;
};
};
@@ -223,6 +225,7 @@ export interface ExtraJson {
cost_estimate_enabled?: boolean; // in SQL Lab
disable_data_preview?: boolean; // in SQL Lab
disable_drill_to_detail?: boolean;
+ allow_multi_catalog?: boolean;
engine_params?: {
catalog?: Record<string, string>;
connect_args?: {
diff --git a/superset-frontend/src/hooks/apiResources/schemas.ts b/superset-frontend/src/hooks/apiResources/catalogs.ts
similarity index 72%
copy from superset-frontend/src/hooks/apiResources/schemas.ts
copy to superset-frontend/src/hooks/apiResources/catalogs.ts
index bbce48d5ad..1e6a97b344 100644
--- a/superset-frontend/src/hooks/apiResources/schemas.ts
+++ b/superset-frontend/src/hooks/apiResources/catalogs.ts
@@ -20,33 +20,32 @@ import { useCallback, useEffect, useRef } from 'react';
import useEffectEvent from 'src/hooks/useEffectEvent';
import { api, JsonResponse } from './queryApi';
-export type SchemaOption = {
+export type CatalogOption = {
value: string;
label: string;
title: string;
};
-export type FetchSchemasQueryParams = {
+export type FetchCatalogsQueryParams = {
dbId?: string | number;
forceRefresh: boolean;
- onSuccess?: (data: SchemaOption[], isRefetched: boolean) => void;
+ onSuccess?: (data: CatalogOption[], isRefetched: boolean) => void;
onError?: () => void;
};
-type Params = Omit<FetchSchemasQueryParams, 'forceRefresh'>;
+type Params = Omit<FetchCatalogsQueryParams, 'forceRefresh'>;
-const schemaApi = api.injectEndpoints({
+const catalogApi = api.injectEndpoints({
endpoints: builder => ({
- schemas: builder.query<SchemaOption[], FetchSchemasQueryParams>({
- providesTags: [{ type: 'Schemas', id: 'LIST' }],
+ catalogs: builder.query<CatalogOption[], FetchCatalogsQueryParams>({
+ providesTags: [{ type: 'Catalogs', id: 'LIST' }],
query: ({ dbId, forceRefresh }) => ({
- endpoint: `/api/v1/database/${dbId}/schemas/`,
- // TODO: Would be nice to add pagination in a follow-up. Needs endpoint changes.
+ endpoint: `/api/v1/database/${dbId}/catalogs/`,
urlParams: {
force: forceRefresh,
},
transformResponse: ({ json }: JsonResponse) =>
- json.result.map((value: string) => ({
+ json.result.sort().map((value: string) => ({
value,
label: value,
title: value,
@@ -60,19 +59,19 @@ const schemaApi = api.injectEndpoints({
});
export const {
- useLazySchemasQuery,
- useSchemasQuery,
- endpoints: schemaEndpoints,
- util: schemaApiUtil,
-} = schemaApi;
+ useLazyCatalogsQuery,
+ useCatalogsQuery,
+ endpoints: catalogEndpoints,
+ util: catalogApiUtil,
+} = catalogApi;
-export const EMPTY_SCHEMAS = [] as SchemaOption[];
+export const EMPTY_CATALOGS = [] as CatalogOption[];
-export function useSchemas(options: Params) {
+export function useCatalogs(options: Params) {
const isMountedRef = useRef(false);
const { dbId, onSuccess, onError } = options || {};
- const [trigger] = useLazySchemasQuery();
- const result = useSchemasQuery(
+ const [trigger] = useLazyCatalogsQuery();
+ const result = useCatalogsQuery(
{ dbId, forceRefresh: false },
{
skip: !dbId,
@@ -80,7 +79,7 @@ export function useSchemas(options: Params) {
);
const handleOnSuccess = useEffectEvent(
- (data: SchemaOption[], isRefetched: boolean) => {
+ (data: CatalogOption[], isRefetched: boolean) => {
onSuccess?.(data, isRefetched);
},
);
@@ -94,7 +93,7 @@ export function useSchemas(options: Params) {
trigger({ dbId, forceRefresh: true }).then(
({ isSuccess, isError, data }) => {
if (isSuccess) {
- handleOnSuccess(data || EMPTY_SCHEMAS, true);
+ handleOnSuccess(data || EMPTY_CATALOGS, true);
}
if (isError) {
handleOnError();
@@ -110,7 +109,7 @@ export function useSchemas(options: Params) {
result;
if (!originalArgs?.forceRefresh && requestId && !isFetching) {
if (isSuccess) {
- handleOnSuccess(data || EMPTY_SCHEMAS, false);
+ handleOnSuccess(data || EMPTY_CATALOGS, false);
}
if (isError) {
handleOnError();
diff --git a/superset-frontend/src/hooks/apiResources/index.ts b/superset-frontend/src/hooks/apiResources/index.ts
index faf4e5736b..53aa7aa113 100644
--- a/superset-frontend/src/hooks/apiResources/index.ts
+++ b/superset-frontend/src/hooks/apiResources/index.ts
@@ -26,6 +26,7 @@ export {
// A central catalog of API Resource hooks.
// Add new API hooks here, organized under
// different files for different resource types.
+export * from './catalogs';
export * from './charts';
export * from './dashboards';
export * from './tables';
diff --git a/superset-frontend/src/hooks/apiResources/queryApi.ts b/superset-frontend/src/hooks/apiResources/queryApi.ts
index 439418529f..b0c0bf5781 100644
--- a/superset-frontend/src/hooks/apiResources/queryApi.ts
+++ b/superset-frontend/src/hooks/apiResources/queryApi.ts
@@ -72,6 +72,7 @@ export const supersetClientQuery: BaseQueryFn<
export const api = createApi({
reducerPath: 'queryApi',
tagTypes: [
+ 'Catalogs',
'Schemas',
'Tables',
'DatabaseFunctions',
diff --git a/superset-frontend/src/hooks/apiResources/schemas.ts b/superset-frontend/src/hooks/apiResources/schemas.ts
index bbce48d5ad..eba1cee9a3 100644
--- a/superset-frontend/src/hooks/apiResources/schemas.ts
+++ b/superset-frontend/src/hooks/apiResources/schemas.ts
@@ -28,6 +28,7 @@ export type SchemaOption = {
export type FetchSchemasQueryParams = {
dbId?: string | number;
+ catalog?: string;
forceRefresh: boolean;
onSuccess?: (data: SchemaOption[], isRefetched: boolean) => void;
onError?: () => void;
@@ -39,14 +40,15 @@ const schemaApi = api.injectEndpoints({
endpoints: builder => ({
schemas: builder.query<SchemaOption[], FetchSchemasQueryParams>({
providesTags: [{ type: 'Schemas', id: 'LIST' }],
- query: ({ dbId, forceRefresh }) => ({
+ query: ({ dbId, catalog, forceRefresh }) => ({
endpoint: `/api/v1/database/${dbId}/schemas/`,
// TODO: Would be nice to add pagination in a follow-up. Needs endpoint changes.
urlParams: {
force: forceRefresh,
+ ...(catalog !== undefined && { catalog }),
},
transformResponse: ({ json }: JsonResponse) =>
- json.result.map((value: string) => ({
+ json.result.sort().map((value: string) => ({
value,
label: value,
title: value,
@@ -70,10 +72,10 @@ export const EMPTY_SCHEMAS = [] as SchemaOption[];
export function useSchemas(options: Params) {
const isMountedRef = useRef(false);
- const { dbId, onSuccess, onError } = options || {};
+ const { dbId, catalog, onSuccess, onError } = options || {};
const [trigger] = useLazySchemasQuery();
const result = useSchemasQuery(
- { dbId, forceRefresh: false },
+ { dbId, catalog, forceRefresh: false },
{
skip: !dbId,
},
@@ -89,9 +91,24 @@ export function useSchemas(options: Params) {
onError?.();
});
+ useEffect(() => {
+ if (dbId) {
+ trigger({ dbId, catalog, forceRefresh: false }).then(
+ ({ isSuccess, isError, data }) => {
+ if (isSuccess) {
+ handleOnSuccess(data || EMPTY_SCHEMAS, true);
+ }
+ if (isError) {
+ handleOnError();
+ }
+ },
+ );
+ }
+ }, [dbId, catalog, handleOnError, handleOnSuccess, trigger]);
+
const refetch = useCallback(() => {
if (dbId) {
- trigger({ dbId, forceRefresh: true }).then(
+ trigger({ dbId, catalog, forceRefresh: true }).then(
({ isSuccess, isError, data }) => {
if (isSuccess) {
handleOnSuccess(data || EMPTY_SCHEMAS, true);
@@ -102,7 +119,7 @@ export function useSchemas(options: Params) {
},
);
}
- }, [dbId, handleOnError, handleOnSuccess, trigger]);
+ }, [dbId, catalog, handleOnError, handleOnSuccess, trigger]);
useEffect(() => {
if (isMountedRef.current) {
@@ -119,7 +136,7 @@ export function useSchemas(options: Params) {
} else {
isMountedRef.current = true;
}
- }, [result, handleOnSuccess, handleOnError]);
+ }, [catalog, result, handleOnSuccess, handleOnError]);
return {
...result,
diff --git a/superset-frontend/src/hooks/apiResources/tables.ts b/superset-frontend/src/hooks/apiResources/tables.ts
index 41be4c167c..8b1d63df23 100644
--- a/superset-frontend/src/hooks/apiResources/tables.ts
+++ b/superset-frontend/src/hooks/apiResources/tables.ts
@@ -50,6 +50,7 @@ export type Data = {
export type FetchTablesQueryParams = {
dbId?: string | number;
+ catalog?: string | null;
schema?: string;
forceRefresh?: boolean;
onSuccess?: (data: Data, isRefetched: boolean) => void;
@@ -58,6 +59,7 @@ export type FetchTablesQueryParams = {
export type FetchTableMetadataQueryParams = {
dbId: string | number;
+ catalog: string | null;
schema: string;
table: string;
};
@@ -91,16 +93,30 @@ export type TableExtendedMetadata = Record<string, string>;
type Params = Omit<FetchTablesQueryParams, 'forceRefresh'>;
+function toQueryString(params: Record<string, any>): string {
+ const queryParts: string[] = [];
+ Object.keys(params).forEach(key => {
+ const value = params[key];
+ if (value) {
+ queryParts.push(
+ `${encodeURIComponent(key)}=${encodeURIComponent(value)}`,
+ );
+ }
+ });
+ return queryParts.length > 0 ? `?${queryParts.join('&')}` : '';
+}
+
const tableApi = api.injectEndpoints({
endpoints: builder => ({
tables: builder.query<Data, FetchTablesQueryParams>({
providesTags: ['Tables'],
- query: ({ dbId, schema, forceRefresh }) => ({
+ query: ({ dbId, catalog, schema, forceRefresh }) => ({
endpoint: `/api/v1/database/${dbId ?? 'undefined'}/tables/`,
// TODO: Would be nice to add pagination in a follow-up. Needs endpoint changes.
urlParams: {
force: forceRefresh,
schema_name: schema ? encodeURIComponent(schema) : '',
+ ...(catalog && { catalog_name: catalog }),
},
transformResponse: ({ json }: QueryResponse) => ({
options: json.result,
@@ -113,10 +129,12 @@ const tableApi = api.injectEndpoints({
}),
}),
tableMetadata: builder.query<TableMetaData, FetchTableMetadataQueryParams>({
- query: ({ dbId, schema, table }) => ({
- endpoint: schema
- ? `/api/v1/database/${dbId}/table_metadata/?name=${table}&schema=${schema}`
- : `/api/v1/database/${dbId}/table_metadata/?name=${table}`,
+ query: ({ dbId, catalog, schema, table }) => ({
+ endpoint: `/api/v1/database/${dbId}/table_metadata/${toQueryString({
+ name: table,
+ catalog,
+ schema,
+ })}`,
transformResponse: ({ json }: TableMetadataReponse) => json,
}),
}),
@@ -124,10 +142,10 @@ const tableApi = api.injectEndpoints({
TableExtendedMetadata,
FetchTableMetadataQueryParams
>({
- query: ({ dbId, schema, table }) => ({
- endpoint: schema
- ? `/api/v1/database/${dbId}/table_metadata/extra/?name=${table}&schema=${schema}`
- : `/api/v1/database/${dbId}/table_metadata/extra/?name=${table}`,
+ query: ({ dbId, catalog, schema, table }) => ({
+ endpoint: `/api/v1/database/${dbId}/table_metadata/extra/${toQueryString(
+ { name: table, catalog, schema },
+ )}`,
transformResponse: ({ json }: JsonResponse) => json,
}),
}),
@@ -144,22 +162,20 @@ export const {
} = tableApi;
export function useTables(options: Params) {
+ const { dbId, catalog, schema, onSuccess, onError } = options || {};
const isMountedRef = useRef(false);
- const { data: schemaOptions, isFetching } = useSchemas({
- dbId: options.dbId,
- });
+ const { data: schemaOptions, isFetching } = useSchemas({ dbId });
const schemaOptionsMap = useMemo(
() => new Set(schemaOptions?.map(({ value }) => value)),
[schemaOptions],
);
- const { dbId, schema, onSuccess, onError } = options || {};
const enabled = Boolean(
dbId && schema && !isFetching && schemaOptionsMap.has(schema),
);
const result = useTablesQuery(
- { dbId, schema, forceRefresh: false },
+ { dbId, catalog, schema, forceRefresh: false },
{
skip: !enabled,
},
@@ -176,7 +192,7 @@ export function useTables(options: Params) {
const refetch = useCallback(() => {
if (enabled) {
- trigger({ dbId, schema, forceRefresh: true }).then(
+ trigger({ dbId, catalog, schema, forceRefresh: true }).then(
({ isSuccess, isError, data, error }) => {
if (isSuccess && data) {
handleOnSuccess(data, true);
@@ -187,7 +203,7 @@ export function useTables(options: Params) {
},
);
}
- }, [dbId, schema, enabled, handleOnSuccess, handleOnError, trigger]);
+ }, [dbId, catalog, schema, enabled, handleOnSuccess, handleOnError, trigger]);
useEffect(() => {
if (isMountedRef.current) {
diff --git a/superset/databases/schemas.py b/superset/databases/schemas.py
index 4318a1b48c..bffb261fe6 100644
--- a/superset/databases/schemas.py
+++ b/superset/databases/schemas.py
@@ -968,6 +968,20 @@ class DatabaseSchemaAccessForFileUploadResponse(Schema):
)
+class EngineInformationSchema(Schema):
+ supports_file_upload = fields.Boolean(
+ metadata={"description": "Users can upload files to the database"}
+ )
+ disable_ssh_tunneling = fields.Boolean(
+ metadata={"description": "SSH tunnel is not available to the database"}
+ )
+ supports_dynamic_catalog = fields.Boolean(
+ metadata={
+ "description": "The database supports multiple catalogs in a single connection"
+ }
+ )
+
+
class DatabaseConnectionSchema(Schema):
"""
Schema with database connection information.
@@ -1001,7 +1015,7 @@ class DatabaseConnectionSchema(Schema):
driver = fields.String(
allow_none=True, metadata={"description": "SQLAlchemy driver to use"}
)
- engine_information = fields.Dict(keys=fields.String(), values=fields.Raw())
+ engine_information = fields.Nested(EngineInformationSchema)
expose_in_sqllab = fields.Boolean(
metadata={"description": expose_in_sqllab_description}
)
diff --git a/superset/db_engine_specs/base.py b/superset/db_engine_specs/base.py
index 4ee4e4dc03..3ecd64273b 100644
--- a/superset/db_engine_specs/base.py
+++ b/superset/db_engine_specs/base.py
@@ -131,7 +131,9 @@ builtin_time_grains: dict[str | None, str] = {
}
-class TimestampExpression(ColumnClause): # pylint: disable=abstract-method, too-many-ancestors
+class TimestampExpression(
+ ColumnClause
+): # pylint: disable=abstract-method, too-many-ancestors
def __init__(self, expr: str, col: ColumnClause, **kwargs: Any) -> None:
"""Sqlalchemy class that can be used to render native column elements respecting
engine-specific quoting rules as part of a string-based expression.
@@ -2185,6 +2187,7 @@ class BaseEngineSpec: # pylint: disable=too-many-public-methods
return {
"supports_file_upload": cls.supports_file_upload,
"disable_ssh_tunneling": cls.disable_ssh_tunneling,
+ "supports_dynamic_catalog": cls.supports_dynamic_catalog,
}
@classmethod
diff --git a/superset/sqllab/schemas.py b/superset/sqllab/schemas.py
index 0864420c90..5e22a97e2a 100644
--- a/superset/sqllab/schemas.py
+++ b/superset/sqllab/schemas.py
@@ -53,6 +53,7 @@ class ExecutePayloadSchema(Schema):
client_id = fields.String(allow_none=True)
queryLimit = fields.Integer(allow_none=True)
sql_editor_id = fields.String(allow_none=True)
+ catalog = fields.String(allow_none=True)
schema = fields.String(allow_none=True)
tab = fields.String(allow_none=True)
ctas_method = fields.String(allow_none=True)
diff --git a/superset/sqllab/sqllab_execution_context.py b/superset/sqllab/sqllab_execution_context.py
index 7a732cf642..7ab4459be3 100644
--- a/superset/sqllab/sqllab_execution_context.py
+++ b/superset/sqllab/sqllab_execution_context.py
@@ -44,6 +44,7 @@ SqlResults = dict[str, Any]
@dataclass
class SqlJsonExecutionContext: # pylint: disable=too-many-instance-attributes
database_id: int
+ catalog: str | None
schema: str
sql: str
template_params: dict[str, Any]
@@ -73,6 +74,7 @@ class SqlJsonExecutionContext: # pylint: disable=too-many-instance-attributes
def _init_from_query_params(self, query_params: dict[str, Any]) -> None:
self.database_id = cast(int, query_params.get("database_id"))
+ self.catalog = cast(str, query_params.get("catalog"))
self.schema = cast(str, query_params.get("schema"))
self.sql = cast(str, query_params.get("sql"))
self.template_params = self._get_template_params(query_params)
@@ -147,6 +149,7 @@ class SqlJsonExecutionContext: # pylint: disable=too-many-instance-attributes
return Query(
database_id=self.database_id,
sql=self.sql,
+ catalog=self.catalog,
schema=self.schema,
select_as_cta=True,
ctas_method=self.create_table_as_select.ctas_method, # type: ignore
@@ -163,6 +166,7 @@ class SqlJsonExecutionContext: # pylint: disable=too-many-instance-attributes
return Query(
database_id=self.database_id,
sql=self.sql,
+ catalog=self.catalog,
schema=self.schema,
select_as_cta=False,
start_time=start_time,