The internalstorage allows passing a piece of SQL to support more flexible query requirements

[Iceber]

What would you like to be added?

Many complex search queries are currently available, such as field selector and search by owner, but sometimes users have more customizable query needs .

To make the query more powerful, the plan wants to add an exclusive query feature to internalstorage that supports users passing a piece of SQL and then splicing the user's SQL after the WHERE.

Of course, in order to be compatible with the generic Search Label, which is still available, the SQL will be spliced after the sql according to the Search Label, but before the ORDER BY.

SELECT objects WHERE group='apps' AND version='v1' AND resource='deployments' AND <user sql> ORDER BY `id`

There are two current questions that need to be resolved

• How to prevent the injection of the passed SQL string
• How to be compatible with the Kubernetes API, after all, Label Selector's value checking is rather strict

Also the different syntax between postgres and mysql may need to be handled by the user depending on the storage component they choose .
The clusterpedia does not handle the compatibility of SQL pieces in different storage components.

However, the final generated SQL may be carried in the response header to allow the user to know the SQL statement

[Iceber]

How to be compatible with the Kubernetes API, after all, Label Selector's value checking is rather strict

Whether base64 SQL can be used as the value of the label ？

Since this feature is not likely to be used in kubectl, there is no need to think too much about readability and ease of use in kubectl

[wawa0210]

This idea is very good, and a more flexible query method is urgently needed in our actual product use

[calvin0327]

We also need to introduce our table structure to users, so they can build the correct SQL statements.

[Iceber]

@calvin0327 Yes, this is provided as an advanced feature, and it is provided by internalstorage; other storage layers do not provide this feature.
Users need to understand the table structure of internalstorage in detail when using this feature.

[Iceber]

How to be compatible with the Kubernetes API, after all, Label Selector's value checking is rather strict

Whether base64 SQL can be used as the value of the label ？

Since this feature is not likely to be used in kubectl, there is no need to think too much about readability and ease of use in kubectl

After some research, it turns out that base64 doesn't work well because the character length reaches the label selector limit.
I don't think clusterpedia needs to modify the validation of the ListSelector for a feature specific to the storage layer.

Then this function may only pass sql via query, and does not support search labels.

@calvin0327 Then, to make it easier to use, we may need to implement a client that supports setting url queries in client-go

[calvin0327]

@Iceber it should be implemented in client-go. but I still don't know how to implement URL query.

[Iceber]

Roughly similar to the dynamic client, we can additionally support adding a URL Params parameter to the List function.
example:

func (c *NewClient) List(ctx context.Context, opts metav1.ListOptions, params map[string]string, obj runtime.Object) error {
        request := c.client.client.Get().AbsPath(c.url).SpecificallyVersionedParams(&opts, dynamicParameterCodec, versionV1)
        for key, value := range params {
                request.Param(key, value)
        }
        return request.Do(ctx).Into(obj)
}