Support Regex-Based User-Defined Commands (UDF) / Command Aliases

[zxyqq]

Is your feature request related to a problem? Please describe.

MyCLI currently supports Favorite Queries via the \fs and \f commands. While useful, this feature has several limitations:

• Aliases must be invoked explicitly with \f <alias> — they cannot be triggered automatically by typing a shorthand command directly.
• There is no support for regex-based pattern matching to transform input dynamically.
• Each alias must be a fixed query with positional parameters ($1, $2), which lacks flexibility for more complex use cases.

For example, I frequently find myself typing commands like:

show create table some_table;
select count(*) from some_table;
select * from some_table limit 10;

With the current Favorite Queries system, I would need to define separate aliases and remember to type \f c some_table, \f n some_table, and \f s some_table. This is not as intuitive or efficient as being able to type c some_table, n some_table, or s some_table directly.

Describe the solution you'd like

I would like MyCLI to support User-Defined Commands (UDC) or regex-based command aliases, similar to the feature available in the hatarist/clickhouse-cli project.

In clickhouse-cli, users can define a dictionary of regex patterns in the configuration file (~/.clickhouse-cli.rc) under a udf section:

[main]
udf = {
    r'^c (.*)': r'show create table \1',
    r'^n (.*)': r'select count(*) from \1',
    r'^s (.*)': r'select * from \1',
    r'^p$': r"select DATE_FORMAT(create_time, '%%Y-%%m-%%d') as date, count(*) as count from dsc_audit_partition_log_local group by date order by date",
    r'^t$': r"SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS total FROM system.parts GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC LIMIT 10",
}

When a user types a command that matches one of these regex patterns, the client performs a find-and-replace operation on the query before sending it to the server. For example:

• Typing c users would be transformed into show create table users.
• Typing n orders would become select count(*) from orders.
• Typing s customers would become select * from customers.
• Typing p would execute a predefined date-grouped audit query.
• Typing t would execute a predefined table size report.

This approach offers:

• Automatic, seamless expansion — no need to remember a separate \f prefix.
• Flexible pattern matching using regex capture groups.
• Complex, multi-line queries can be bound to simple shortcuts (e.g., t for a top-10 table size report).
• Configuration persistence in ~/.myclirc, making shortcuts available across sessions.
• Backward compatibility — existing Favorite Queries (\fs/\f) would remain unchanged.

Describe alternatives you've considered

1. Shell aliases — e.g., alias lt_cloud="mycli -e 'show tables like \"%cloud%\"'". This works but is external to MyCLI, cannot interact with an active session, does not support dynamic parameters as elegantly, and requires spawning a new connection for each invocation.

2. Favorite Queries (\fs / \f) — As described above, this requires the \f prefix and does not support regex-based matching.

3. Manual typing — Always an option, but defeats the purpose of a productivity tool.

Additional context

This feature is inspired by the hatarist/clickhouse-cli project, which has successfully implemented regex-based UDFs and received positive feedback from its users. The implementation in clickhouse-cli is described as a "dirty hack" that performs a find-and-replace on the query string before execution — a pragmatic approach that could be adapted for MyCLI.

I believe this feature would greatly enhance MyCLI's usability for database administrators and developers who frequently run repetitive or complex queries.

Potential implementation considerations:

• The regex definitions could be stored in ~/.myclirc under a new section, e.g., [udf] or [aliases].
• The matching should occur before the input is sent to the MySQL server, similar to how \f works.
• The feature should be opt-in — users who don't define any regex aliases would see no behavior change.
• It would be ideal to support both $1, $2 style positional parameters (for compatibility with existing Favorite Queries) and regex capture groups like \1, \2.

Thank you for considering this feature request!

[rolandwalker]

• There is no support for regex-based pattern matching to transform input dynamically.
• Each alias must be a fixed query with positional parameters ($1, $2), which lacks flexibility for more complex use cases.

We recently added Jinja templates for favorite queries, which goes far beyond what can be done with regex: #2085 .

It also introduces named parameters, like

/f alias --key=Fred

and the parameters can be optional, depending on how the Jinja template is constructed.