[SIP-132] Proposal for SQL Lab addon plugins

[justinpark]

[SIP-132] Proposal for SQL Lab addon plugins

Motivation

We have a couple of exciting SQL Lab integration ideas, and one of them is the text-to-SQL feature. However, we have encountered a limitation with the prototype due to the lack of layout space. Currently, users can only input queries in one line without the ability to trace their chat or query history.
Additionally, while the left bar table selector is helpful, it may not always be necessary. If we are able to utilize the layout space more effectively, we can address these limitations and provide enhanced functionality.

The SQL Lab addon plugins bring a host of benefits to Apache Superset users, similar to how VSCode extensions enhance the coding experience in VSCode. By leveraging these plugins, user can extend sql editing capabilities, improve productivity, and unlock new possibilities for data exploration and analysis.

Proposed Change

While we will still keep the existing table schema explore in the left bar layout, we are also introducing an extra tab that provides a different user interface.

sqllab-addon-intro.mov

Think of it as similar to the VSCode extensions concept. Users will have the flexibility to explore and choose from a variety of addons based on their needs. These addons can be accessed from the "addon store" tab, which will be conveniently located as the last tab. From there, users can easily add or remove available extensions(like chart plugins) as per their preference.

We also encourage community collaboration by allowing open-source contributions to the available extensions.
This will further enrich the options and functionalities for our users.
Additionally, for company-specific requirements, there is the possibility of incorporating custom addons through overrides.

New or Changed Public Interfaces

• Folder hierarchy

Here is the proposed basic folder structure for each extension:

- src/SqlLab/addons
|-------- /ExampleAddon
|--------------- index.ts
|--------------- ExampleAddon.tsx (the main component that will be rendered in the left panel)
|--------------- Icon.tsx 

• index.ts metadata format

export default {
  metadata: {
    name: 'Example',
    description: 'Example description',
    iconName: 'filter', // name in Icons
    // icon: for Custom icon component
  },
  loadComponent: () => import('./ExampleAddon'),
};

• Addon component props

We will provide an interface to access the current query editor context.
E.g. You can access the current executed query result by extracting the record from queriesSelector using the latestQueryId from useQueryEditor

type AddonProps = {
  queryEditorId: string;
  active: boolean;
  useQueryEditor: typeof useQueryEditor;
  useQueryHistoryQuery: typeof useEditorQueriesQuery;
  useDatabaseListQuery: typeof useDatabases;
  useSchemaListQuery: typeof useSchemas;
  useTableListQuery: typeof useTables;
  queriesSelector: typeof queriesSelector;
  runQuery: () => promise<void>; 
  setQuery: (sql: string) => void;
};

// how-to use

function MyAddon({ queryEditorId }) {
  const queryEditor = useQueryEditor(queryEditorId, ['dbId', 'sql', 'schema', 'queryLimit', 'cursorPosition']);
  const [data, isLoading, error] = useQueryHistoryQuery({ pageIndex: 0 });
  const [data, isLoading, error] = useSchemaListQuery({ dbId: queryEditor.dbId });
}

• Adds SqlLabAddonRegistry

class SqlLabAddonRegistry {
  items: FunctionalRegistryState<
    ComponentRegistry<SqlLabAddonMetadata, AddonProps>
  > = {
    registry: {},
    registryKeys: [],
  };

  constructor(addons: typeof defaultAddons) {
    addons.forEach(({ key, item }) => {
      this.register(key, item);
    });
  }

  register = registrySetComponent(this.items);

  get = registryGet(this.items);

  clear = registryDelete(this.items);

  getAll = registryGetAll(this.items);
}

const getInstance = makeSingleton(SqlLabAddonRegistry, defaultAddons);

The SqlLabAddonRegistry will import all public extensions under src/SqlLab/addons.
Additional custom extensions can be added through the overrides setupSqlLabAddons configuration.

• setupSqlLabAddons

// import getRegistry from 'src/SqlLab/addons/SqlLabAddonRegistrySingleton';

export default function setupSqlLabAddons() {
  // Add custom addons here. Example:
  // getRegistry().register('custom', customAddons);
}

New Dependencies

We will store the currently activated addon list(e.g. sqllab_current_addons: ['addon_key1', 'addon_key2']) for the user in localStorage first.

...
lastTab: #Home
redux: {...}
common__resizable_sidebar_...
...

And then, once it's ready, we can migrate this data to the user preference database.

Migration Plan and Compatibility

I do not for-see any migration required.

Rejected Alternatives

None

[geido]

Thanks @justinpark, a few questions:

• Can this addon system be applied to other parts of Superset? Should the design be more generic to benefit beyond SQL Lab?
• Are addons tab-specific or user-specific? Clarifying this is essential to avoid user confusion, as it seems they are user-specific but displayed under individual tabs.
• Should administrators have control over which addons are available via a permission system?
• Also, I believe addons should be configurable at startup so that one can have control of what addons are available in the Superset instance
• As for what is available for the addons to consume, I believe we need to consider whether wider access to the state and actions might be helpful

[michael-s-molina]

Thank you @justinpark. It's just the beginning of the discussion, given the complexity of the topic, but I would like to add something about the following:

We will provide an interface to access the current query editor context

As for what is available for the addons to consume, I believe we need to consider whether wider access to the state and actions might be helpful

It's really important that what we expose to the plugins should be defined under a versioned API instead of internal structures. The Redux state and actions are internal structures that can be changed at any time and are not versioned. Having a clear defined API is important to avoid the situation where plugins break because of internal changes and to provide clarity for plugin authors about the resources that they have access to in a particular version.

[rusackas]

I echo all of @geido's questions - particularly about the applicability to other areas of Superset. Our viz plugins, dashboard component extensions, etc. There are about a million places we'll want to add plugins eventually (formatters, advanced types, "export as" options, etc), so it would be great to arrive at an architecture that's repeatable/reusable for all of this an clean up house.

In a perfect world, I'd love to see these plugins managed through the UI (like an IDE does) where one can simply add npm URLs, and each "plugin" has its own webpack/tsconfig, so you simply point Superset at the bundle and load it dynamically. This would kick the doors open for a highly extensible Superset experience.

I'm guessing from the video that there's already some code that would be good to upstream. I love that advantage, but at the same time, I want to make sure we nail this... because if we do, it'll be an absolute game-changer for Superset.

[rusackas]

Hey @justinpark - just wondering if there's more to tackle in the discussion here of if this is ready for a VOTE.