Documentation Home
MySQL Shell 8.0
Related Documentation Download this Manual
PDF (US Ltr) - 370.8Kb
PDF (A4) - 367.3Kb
HTML Download (TGZ) - 73.9Kb
HTML Download (Zip) - 85.7Kb


MySQL Shell 8.0  /  Reporting with MySQL Shell  /  Creating and Registering User-Defined Reports

6.1 Creating and Registering User-Defined Reports

You can create and register a user-defined report for MySQL Shell in either of the supported scripting languages, JavaScript and Python. The reporting facility handles built-in reports and user-defined reports using the same API frontend scheme.

MySQL Shell classes reports by the type of output that they return, as follows:

List type

Returns output as a list of lists, with the first list consisting of the names of columns, and the remainder being the content of rows. MySQL Shell displays the output in table format by default, or in vertical format if the --vertical or --E option was specified on the \show or \watch command.

Report type

Returns free-form output, which MySQL Shell displays using YAML.

Print type

Prints its own output to screen.

Reports can specify a list of report-specific options that they accept, and can also accept a specified number of additional arguments. Your report can support both, one, or neither of these inputs. When you request help for a report, MySQL Shell provides a listing of options and arguments, and any available descriptions of these that are provided when the report is registered.

Signature

The signature for the Python or JavaScript function to be registered as a MySQL Shell report must be as follows:

Dict report(Session session, List argv, Dict options);

Where:

  • session is a MySQL Shell session object that is to be used to execute the report.

  • argv is an optional list containing string values of additional arguments that are passed to the report.

  • options is an optional dictionary with key names and values that correspond to any report-specific options and their values.

Output

Your report can provide its output in any of the following ways:

  • Return a list of lists, with the first list consisting of the names of columns, and the remainder of the lists consisting of the content of rows. MySQL Shell can display this output as a table (the default) or in vertical format. The values for the rows are converted to string representations of the items. If a row has fewer elements than the number of column names, the missing elements are considered to be NULL. If a row has more elements than the number of column names, the extra elements are ignored. When you register this report, use the type list.

  • Return free-form output as a list containing a single item. MySQL Shell displays this output using YAML. When you register this report, use the type report.

  • Print the output directly to screen, and return an empty list to MySQL Shell to show that the output has already been displayed. When you register this report, use the type print.

To provide the output, the API function for the report must return a dictionary with the key report, and a list of JSON objects, one for each of the items in your returned list. For the List type, use one element for each list, for the Report type use a single element, and for the Print type use no elements.

Registering your report

To register your user-defined report with MySQL Shell, call the shell.registerReport method in JavaScript or shell.registerReport in Python. The syntax method is as follows:

shell.registerReport(name, type, report[, description])

Where:

  • name is a string giving the unique name of the report.

  • type is a string giving the report type which determines the output format, either list, report, or print.

  • report is the function to be called when the report is invoked.

  • description is a dictionary with options that you can use to specify the options that the report supports, additional arguments that the report accepts, and help information that is provided in the MySQL Shell help system.

The name, type, and report parameters are all required. The report name must meet the following requirements:

  • It must be unique in your MySQL Shell installation.

  • It must be a valid scripting identifier, so the first character must be a letter or underscore character, followed by any number of letters, numbers, or underscore characters.

  • It can be in mixed case, but it must still be unique in your MySQL Shell installation when converted to lower case.

The report name is case insensitive during the registration process and when running the report using the \show and \watch commands. The report name is case sensitive when calling the corresponding API function at the shell.reports object. There you must call the function using the exact name that was used to register the report, whether you are in Python or JavaScript mode.

The optional dictionary contains the following keys, which are all optional:

brief

A brief description of the report.

details

A detailed description of the report, provided as an array of strings. This is provided when you use the \help command or the --help option with the \show command.

options

Any report-specific options that the report can accept. Each dictionary in the array describes one option, and must contain the following keys:

  • name (string, required): The name of the option in the long form, which must be a valid scripting identifier.

  • brief (string, optional): A brief description of the option.

  • shortcut (string, optional): An alternate name for the option as a single alphanumeric character.

  • details (array of strings, optional): A detailed description of the option. This is provided when you use the \help command or the --help option with the \show command.

  • type (string, optional): The value type of the option. The permitted values are string, bool, integer, and float, with a default of string if type is not specified. If bool is specified, the option acts as a switch: it defaults to false if not specified, defaults to true (and accepts no value) when you run the report using the \show or \watch command, and must have a valid value when you run the report using the shell.reports object.

  • required (bool, optional): Whether the option is required. If required is not specified, it defaults to false. If the option type is bool then required cannot be true.

  • values (array of strings, optional): A list of allowed values for the option. Only options with type string can have this key. If values is not specified, the option accepts any values.

argc

A string specifying the number of additional arguments that the report expects, which can be one of the following:

  • An exact number of arguments, which is specified as a single number.

  • Zero or more arguments, which is specified as an asterisk.

  • A range of argument numbers, which is specified as two numbers separated by a dash (for example, 1-5).

  • A range of argument numbers with a minimum but no maximum, which is specified as a number and an asterisk separated by a dash (for example, 1-*).

Saving your report

Your report must be saved as a file in the init.d folder in the MySQL Shell user configuration path, so that MySQL Shell can identify and load it as a report. The default path for this folder is ~/.mysqlsh/init.d in Unix and %AppData%MySQL\mysqlsh\init.d in Windows. The user configuration path can be overridden on all platforms by defining the environment variable MYSQLSH_USER_CONFIG_HOME. The value of this variable replaces %AppData%\MySQL\mysqlsh\ on Windows or ~/.mysqlsh/ on Unix.

The file name for your report does not have to match the report name, but it is a best practice for it to do so. The file extension must be .js or .py to match the scripting language used for the report. The file extension is not case-sensitive.

Example

This example user-defined report sessions shows which sessions currently exist.

    def sessions(session, args, options):
        sys = session.get_schema('sys')
        session_view = sys.get_table('session')
        query = session_view.select(
            'thd_id', 'conn_id', 'user', 'db', 'current_statement',
            'statement_latency AS latency', 'current_memory AS memory')
        try:
            limit = int(options['limit'])
        except SystemError:
            limit = 0
        if limit > 0:
            query.limit(limit)

        result = query.execute()
        report = [result.get_column_names()]
        for row in result.fetch_all():
            report.append(list(row))

        return {'report': report}

    shell.register_report(
        'sessions',
        'list',
        sessions,
        {
            'brief': 'Shows which sessions exist.',
            'details': ['You need the SELECT privilege on sys.session view and the underlying tables and functions used by it.'],
            'options': [
                {
                    'name': 'limit',
                    'brief': 'The maximum number of rows to return.',
                    'shortcut': 'l',
                    'type': 'integer'
                }
            ],
            'argc': '0'
        }
    )