Shell Class Reference

Gives access to general purpose functions and properties. More...

Methods
void	disconnect ()
	Disconnects the global session.
bool	reconnect ()
	Reconnect the global session.
void	status ()
	Shows connection status info for the shell. More...
Dictionary	parseUri (String uri)
	Utility function to parse a URI string. More...
String	unparseUri (Dictionary options)
	Formats the given connection options to a URI string suitable for mysqlsh. More...
String	prompt (String message, Dictionary options)
	Utility function to prompt data from the user. More...
Session	connect (ConnectionData connectionData, String password)
	Establishes the shell global session. More...
Session	connectToPrimary (ConnectionData connectionData, String password)
	Establishes the shell global session, connecting to a primary of an InnoDB cluster or ReplicaSet. More...
Session	openSession (ConnectionData connectionData, String password)
	Establishes and returns session. More...
Session	getSession ()
	Returns the global session.
Undefined	setSession (Session session)
	Sets the global session. More...
Undefined	setCurrentSchema (String name)
	Sets the active schema on the global session. More...
Undefined	log (String level, String message)
	Logs an entry to the shell's log file. More...
List	listCredentialHelpers ()
	Returns a list of strings, where each string is a name of a helper available on the current platform. More...
Undefined	storeCredential (String url, String password)
	Stores given credential using the configured helper. More...
Undefined	deleteCredential (String url)
	Deletes credential for the given URL using the configured helper. More...
Undefined	deleteAllCredentials ()
	Deletes all credentials managed by the configured helper. More...
List	listCredentials ()
	Retrieves a list of all URLs stored by the configured helper. More...
List	listSshConnections ()
	Retrieves a list of all active SSH tunnels. More...
Undefined	enablePager ()
	Enables pager specified in shell.options.pager for the current scripting mode. More...
Undefined	disablePager ()
	Disables pager for the current scripting mode. More...
Undefined	registerReport (String name, String type, Function report, Dictionary description)
	Registers a new user-defined report. More...
UserObject	createExtensionObject ()
	Creates an extension object, it can be used to extend shell functionality. More...
Undefined	addExtensionObjectMember (Object object, String name, Value member, Dictionary definition)
	Adds a member to an extension object. More...
Undefined	registerGlobal (String name, Object object, Dictionary definition)
	Registers an extension object as a shell global object. More...
Integer	dumpRows (ShellBaseResult result, String format)
	Formats and dumps the given resultset object to the console. More...
Dictionary	autoCompleteSql (String statement, Dictionary options)
	Auto-completes the given SQL statement. More...
Undefined	registerSqlHandler (String name, String description, List prefixes, Function callback)
	Registers a custom SQL Handler to intercept and handle matching SQL statements. More...
List	listSqlHandlers ()
	Lists the name and description of any registered SQL handlers. More...
ShellResult	createResult (Dictionary data)
	Creates a ShellResult object from the given data. More...
ShellResult	createResult (List data)
	Creates a ShellResult object from the given data. More...

Properties
Options	options
	Gives access to the options that modify the shell behavior.
Reports	reports
	Gives access to built-in and user-defined reports.
String	version
	MySQL Shell version information.

Detailed Description

Gives access to general purpose functions and properties.

Member Function Documentation

status()

void status

(

)

Shows connection status info for the shell.

This shows the same information shown by the \status command.

parseUri()

Dictionary parseUri

(

String

uri

)

Utility function to parse a URI string.

Parameters

uri	a URI string.

Returns

A dictionary containing all the elements contained on the given URI string.

Parses a URI string and returns a dictionary containing an item for each found element.

A basic URI string has the following format:

[scheme://][user[:password]@]<host[:port]|socket>[/schema][?option=value&option=value...]

For more information about the URI format as well as the returned dictionary please look at Connection Data

unparseUri()

String unparseUri

(

Dictionary

options

)

Formats the given connection options to a URI string suitable for mysqlsh.

Parameters

options

a dictionary with the connection options.

Returns

A URI string

This function assembles a MySQL connection string which can be used in the shell or X DevAPI connectors.

prompt()

String prompt	(	String	message,
		Dictionary	options
	)

Utility function to prompt data from the user.

Parameters

message	a string with the message to be shown to the user.
options	Optional dictionary with options that change the function behavior.

Returns

A string value containing the result based on the user input.

This function allows creating scripts that require interaction with the user to gather data.

The options dictionary may contain the following options:

• title: a string to identify the prompt.
• description: a string list with a description of the prompt, each entry represents a paragraph.
• type: a string value to define the prompt type. Supported types include: text, password, confirm, select, fileOpen, fileSave, directory. Default value: text.
• defaultValue: defines the default value to be used in case the user accepts the prompt without providing custom information. The value of this option depends on the prompt type.
• yes: string value to overwrite the default '&Yes' option on 'confirm' prompts.
• no: string value to overwrite the default '&No' option on 'confirm' prompts.
• alt: string value to define an additional option for 'confirm' prompts.
• options: string list of options to be used in 'select' prompts.

General Behavior

The 'title' option is not used in the Shell but it might be useful for better integration with external plugins handling prompts.

When the 'description' option is provided, each entry will be printed as a separate paragraph.

Once the description is printed, the content of the message parameter will be printed and input from the user will be required.

The value of the 'defaultValue' option and the returned value will depend on the 'type' option, as explained on the following sections.

Open Prompts

These represent prompts without a pre-defined set of valid answers, the prompt types on this category include: text, password, fileOpen, fileSave and directory.

The 'defaultValue' on these prompts can be set to a string to be returned by the function if the user replies to the prompt with an empty string, if not defined then the prompt will accept and return the empty string.

The returned value on these prompts will be either the value set on 'defaultValue' option or the value entered by the user.

In the case of password type prompts, the data entered by the user will not be displayed on the screen.

Confirm Prompts

The default behaviour of these prompts if to allow the user answering Yes/No questions, however, the default '&Yes' and '&No' options can be overriden through the 'yes' and 'no' options.

An additional option can be added to the prompt by defining the 'alt' option.

The 'yes', 'no' and 'alt' options are used to define the valid answers for the prompt. The ampersand (&) symbol can be used to define a single character which acts as a shortcut for the user to select the indicated answer.

i.e. using an option like: '&Yes' causes the following to be valid answers (case insensitive): 'Yes', '&Yes', 'Y'.

All the valid answers must be unique within the prompt call.

If the 'defaultValue' option is defined, it must be set equal to any of the valid prompt answers, i.e. if the 'yes', 'no' and 'alt' options are not defined, then 'defaultValue' can be set to one of '&Yes', 'Yes', 'Y', '&No', 'No' or 'N', case insensitive, otherwise, it must be set to one of the valid answers based on the values defined in the 'yes', 'no' or 'alt' options.

This prompt will be shown repeatedly until the user explicitly replies with one of the valid answers unless a 'defaultValue' is provided, which will be used in case the user replies with an empty answer.

The returned value will be the label associated to the user provided answer.

i.e. if the prompt is using the default options ('&Yes' and '&No') and the user response is 'n' the prompt function will return '&No'.

Select Prompts

These prompts allow the user selecting an option from a pre-defined list of options.

To define the list of options to be used in the prompt the 'options' option should be used.

If the 'defaultValue' option is defined, it must be a number representing the 1 based index of the option to be selected by default.

This prompt will be shown repeatedly until the user explicitly replies with the 1 based index of the option to be selected unless a default option is pre-defined through the 'defaultValue' option.

The returned value will be the text of the selected option.

connect()

Session connect	(	ConnectionData	connectionData,
		String	password
	)

Establishes the shell global session.

Parameters

connectionData	the connection data to be used to establish the session.
password	Optional the password to be used when establishing the session.

This function will establish the global session with the received connection data.

The password may be included on the connectionData, the optional parameter should be used only if the connectionData does not contain it already. If both are specified the password parameter will override the password defined on the connectionData.

The connection data may be specified in the following formats:

• A URI string
• A dictionary with the connection options

A basic URI string has the following format:

[scheme://][user[:password]@]<host[:port]|socket>[/schema][?option=value&option=value...]

Connection Options

The following options are valid for use either in a URI or in a dictionary:

• ssl-mode: The SSL mode to be used in the connection.
• ssl-ca: The path to the X509 certificate authority file in PEM format.
• ssl-capath: The path to the directory that contains the X509 certificate authority files in PEM format.
• ssl-cert: The path to the SSL public key certificate file in PEM format.
• ssl-key: The path to the SSL private key file in PEM format.
• ssl-crl: The path to file that contains certificate revocation lists.
• ssl-crlpath: The path of directory that contains certificate revocation list files.
• ssl-cipher: The list of permissible encryption ciphers for connections that use TLS protocols up through TLSv1.2.
• tls-version: List of protocols permitted for secure connections.
• tls-ciphers: List of TLS v1.3 ciphers to use.
• auth-method: Authentication method.
• get-server-public-key: Request public key from the server required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• server-public-key-path: The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• connect-timeout: The connection timeout in milliseconds. If not provided a default timeout of 10 seconds will be used. Specifying a value of 0 disables the connection timeout.
• compression: Enable compression in client/server protocol.
• compression-algorithms: Use compression algorithm in server/client protocol.
• compression-level: Use this compression level in the client/server protocol.
• connection-attributes: List of connection attributes to be registered at the PERFORMANCE_SCHEMA connection attributes tables.
• local-infile: Enable/disable LOAD DATA LOCAL INFILE.
• net-buffer-length: The buffer size for TCP/IP and socket communication.
• plugin-authentication-kerberos-client-mode: (Windows) Allows defining the kerberos client mode (SSPI, GSSAPI) when using kerberos authentication.
• oci-config-file: Allows defining the OCI configuration file for OCI authentication.
• authentication-oci-client-config-profile: Allows defining the OCI profile used from the configuration for client side OCI authentication.
• authentication-openid-connect-client-id-token-file: Allows defining the file path to an OpenId Connect authorization token file when using OpenId Connect authentication.

When these options are defined in a URI, their values must be URL encoded.

The following options are also valid when a dictionary is used:

Base Connection Options

• uri: a URI string.
• scheme: the protocol to be used on the connection.
• user: the MySQL user name to be used on the connection.
• password: the password to be used on the connection.
• host: the hostname or IP address to be used on the connection.
• port: the port to be used in a TCP connection.
• socket: the socket file name to be used on a connection through unix sockets.
• schema: the schema to be selected once the connection is done.

SSH Tunnel Connection Options

• ssh: a SSHURI string used when SSH tunnel is required.
• ssh-password: the password the be used on the SSH connection.
• ssh-identity-file: the key file to be used on the SSH connection.
• ssh-identity-file-password: the SSH key file password.
• ssh-config-file: the SSH configuration file, default is the value of shell.options['ssh.configFile']

Attention

The connection options have precedence over options specified in the connection options uri

The connection options are case insensitive and can only be defined once.

If an option is defined more than once, an error will be generated.

Detailed description of the connection data format is available at Connection Data

connectToPrimary()

Session connectToPrimary	(	ConnectionData	connectionData,
		String	password
	)

Establishes the shell global session, connecting to a primary of an InnoDB cluster or ReplicaSet.

Parameters

connectionData	Optional The connection data to be used to establish the session.
password	Optional The password to be used when establishing the session.

Returns

The established session.

Ensures that the target server is a member of an InnoDB cluster or ReplicaSet and if it is not a PRIMARY, finds the PRIMARY and connects to it. Sets the global session object to the established session and returns that object.

If connectionData is not given, this function uses the global shell session, if there is none, an exception is raised.

The password may be included on the connectionData, the optional parameter should be used only if the connectionData does not contain it already. If both are specified the password parameter will override the password defined on the connectionData.

The connection data may be specified in the following formats:

• A URI string
• A dictionary with the connection options

A basic URI string has the following format:

[scheme://][user[:password]@]<host[:port]|socket>[/schema][?option=value&option=value...]

Connection Options

The following options are valid for use either in a URI or in a dictionary:

• ssl-mode: The SSL mode to be used in the connection.
• ssl-ca: The path to the X509 certificate authority file in PEM format.
• ssl-capath: The path to the directory that contains the X509 certificate authority files in PEM format.
• ssl-cert: The path to the SSL public key certificate file in PEM format.
• ssl-key: The path to the SSL private key file in PEM format.
• ssl-crl: The path to file that contains certificate revocation lists.
• ssl-crlpath: The path of directory that contains certificate revocation list files.
• ssl-cipher: The list of permissible encryption ciphers for connections that use TLS protocols up through TLSv1.2.
• tls-version: List of protocols permitted for secure connections.
• tls-ciphers: List of TLS v1.3 ciphers to use.
• auth-method: Authentication method.
• get-server-public-key: Request public key from the server required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• server-public-key-path: The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• connect-timeout: The connection timeout in milliseconds. If not provided a default timeout of 10 seconds will be used. Specifying a value of 0 disables the connection timeout.
• compression: Enable compression in client/server protocol.
• compression-algorithms: Use compression algorithm in server/client protocol.
• compression-level: Use this compression level in the client/server protocol.
• connection-attributes: List of connection attributes to be registered at the PERFORMANCE_SCHEMA connection attributes tables.
• local-infile: Enable/disable LOAD DATA LOCAL INFILE.
• net-buffer-length: The buffer size for TCP/IP and socket communication.
• plugin-authentication-kerberos-client-mode: (Windows) Allows defining the kerberos client mode (SSPI, GSSAPI) when using kerberos authentication.
• oci-config-file: Allows defining the OCI configuration file for OCI authentication.
• authentication-oci-client-config-profile: Allows defining the OCI profile used from the configuration for client side OCI authentication.
• authentication-openid-connect-client-id-token-file: Allows defining the file path to an OpenId Connect authorization token file when using OpenId Connect authentication.

When these options are defined in a URI, their values must be URL encoded.

The following options are also valid when a dictionary is used:

Base Connection Options

• uri: a URI string.
• scheme: the protocol to be used on the connection.
• user: the MySQL user name to be used on the connection.
• password: the password to be used on the connection.
• host: the hostname or IP address to be used on the connection.
• port: the port to be used in a TCP connection.
• socket: the socket file name to be used on a connection through unix sockets.
• schema: the schema to be selected once the connection is done.

SSH Tunnel Connection Options

• ssh: a SSHURI string used when SSH tunnel is required.
• ssh-password: the password the be used on the SSH connection.
• ssh-identity-file: the key file to be used on the SSH connection.
• ssh-identity-file-password: the SSH key file password.
• ssh-config-file: the SSH configuration file, default is the value of shell.options['ssh.configFile']

Attention

The connection options have precedence over options specified in the connection options uri

The connection options are case insensitive and can only be defined once.

If an option is defined more than once, an error will be generated.

For additional information about MySQL connection data, see Connection Data.

Exceptions

RuntimeError

in the following scenarios: If connectionData was not given and there is no global shell session. If there is no primary member of an InnoDB cluster or ReplicaSet. If the target server is not a member of an InnoDB cluster or ReplicaSet.

openSession()

Session openSession	(	ConnectionData	connectionData,
		String	password
	)

Establishes and returns session.

Parameters

connectionData	Optional the connection data to be used to establish the session. If none given, duplicates the shell's active session.
password	Optional the password to be used when establishing the session.

Returns

The session object.

This function will establish the session with the received connection data.

The password may be included on the connectionData, the optional parameter should be used only if the connectionData does not contain it already. If both are specified the password parameter will override the password defined on the connectionData.

The connection data may be specified in the following formats:

• A URI string
• A dictionary with the connection options

A basic URI string has the following format:

[scheme://][user[:password]@]<host[:port]|socket>[/schema][?option=value&option=value...]

Connection Options

The following options are valid for use either in a URI or in a dictionary:

• ssl-mode: The SSL mode to be used in the connection.
• ssl-ca: The path to the X509 certificate authority file in PEM format.
• ssl-capath: The path to the directory that contains the X509 certificate authority files in PEM format.
• ssl-cert: The path to the SSL public key certificate file in PEM format.
• ssl-key: The path to the SSL private key file in PEM format.
• ssl-crl: The path to file that contains certificate revocation lists.
• ssl-crlpath: The path of directory that contains certificate revocation list files.
• ssl-cipher: The list of permissible encryption ciphers for connections that use TLS protocols up through TLSv1.2.
• tls-version: List of protocols permitted for secure connections.
• tls-ciphers: List of TLS v1.3 ciphers to use.
• auth-method: Authentication method.
• get-server-public-key: Request public key from the server required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• server-public-key-path: The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic MySQL sessions with SSL mode DISABLED.
• connect-timeout: The connection timeout in milliseconds. If not provided a default timeout of 10 seconds will be used. Specifying a value of 0 disables the connection timeout.
• compression: Enable compression in client/server protocol.
• compression-algorithms: Use compression algorithm in server/client protocol.
• compression-level: Use this compression level in the client/server protocol.
• connection-attributes: List of connection attributes to be registered at the PERFORMANCE_SCHEMA connection attributes tables.
• local-infile: Enable/disable LOAD DATA LOCAL INFILE.
• net-buffer-length: The buffer size for TCP/IP and socket communication.
• plugin-authentication-kerberos-client-mode: (Windows) Allows defining the kerberos client mode (SSPI, GSSAPI) when using kerberos authentication.
• oci-config-file: Allows defining the OCI configuration file for OCI authentication.
• authentication-oci-client-config-profile: Allows defining the OCI profile used from the configuration for client side OCI authentication.
• authentication-openid-connect-client-id-token-file: Allows defining the file path to an OpenId Connect authorization token file when using OpenId Connect authentication.

When these options are defined in a URI, their values must be URL encoded.

The following options are also valid when a dictionary is used:

Base Connection Options

• uri: a URI string.
• scheme: the protocol to be used on the connection.
• user: the MySQL user name to be used on the connection.
• password: the password to be used on the connection.
• host: the hostname or IP address to be used on the connection.
• port: the port to be used in a TCP connection.
• socket: the socket file name to be used on a connection through unix sockets.
• schema: the schema to be selected once the connection is done.

SSH Tunnel Connection Options

• ssh: a SSHURI string used when SSH tunnel is required.
• ssh-password: the password the be used on the SSH connection.
• ssh-identity-file: the key file to be used on the SSH connection.
• ssh-identity-file-password: the SSH key file password.
• ssh-config-file: the SSH configuration file, default is the value of shell.options['ssh.configFile']

Attention

The connection options have precedence over options specified in the connection options uri

The connection options are case insensitive and can only be defined once.

If an option is defined more than once, an error will be generated.

For additional information about MySQL connection data, see Connection Data.

Detailed description of the connection data format is available at Connection Data

setSession()

Undefined setSession

(

Session

session

)

Sets the global session.

Parameters

session

The session object to be used as global session.

Sets the global session using a session from a variable.

setCurrentSchema()

Undefined setCurrentSchema

(

String

name

)

Sets the active schema on the global session.

Parameters

name	The name of the schema to be set as active.

Once the schema is set as active, it will be available through the db global object.

log()

Undefined log	(	String	level,
		String	message
	)

Logs an entry to the shell's log file.

Parameters

level	one of ERROR, WARNING, INFO, DEBUG, DEBUG2, DEBUG3 as a string
message	the text to be logged

Only messages that have a level value equal to or lower than the active one (set via --log-level) are logged.

listCredentialHelpers()

List listCredentialHelpers

(

)

Returns a list of strings, where each string is a name of a helper available on the current platform.

Returns

A list of string with names of available credential helpers.

The special values "default" and "<disabled>" are not on the list. Only values on this list (plus "default" and "<disabled>") can be used to set the "credentialStore.helper" option.

storeCredential()

Undefined storeCredential	(	String	url,
		String	password
	)

Stores given credential using the configured helper.

Parameters

url	URL of the server for the password to be stored.
password	Optional Password for the given URL.

Throws ArgumentError if URL has invalid form.

Throws RuntimeError in the following scenarios:

• if configured credential helper is invalid.
• if storing the credential fails.

If password is not provided, displays password prompt. If URL is already in storage, it's value is overwritten. URL needs to be in the following form: user@(host[:port]|socket).

deleteCredential()

Undefined deleteCredential

(

String

url

)

Deletes credential for the given URL using the configured helper.

Parameters

url	URL of the server to delete.

Throws ArgumentError if URL has invalid form.

Throws RuntimeError in the following scenarios:

• if configured credential helper is invalid.
• if deleting the credential fails.

URL needs to be in the following form: user@(host[:port]|socket).

deleteAllCredentials()

Undefined deleteAllCredentials

(

)

Deletes all credentials managed by the configured helper.

Throws RuntimeError in the following scenarios:

• if configured credential helper is invalid.
• if deleting the credentials fails.

listCredentials()

List listCredentials

(

)

Retrieves a list of all URLs stored by the configured helper.

Throws RuntimeError in the following scenarios:

• if configured credential helper is invalid.
• if listing the URLs fails.

Returns

A list of URLs stored by the configured credential helper.

listSshConnections()

List listSshConnections

(

)

Retrieves a list of all active SSH tunnels.

Returns

A list of active SSH tunnel connections.

enablePager()

Undefined enablePager

(

)

Enables pager specified in shell.options.pager for the current scripting mode.

All subsequent text output (except for prompts and user interaction) is going to be forwarded to the pager.

This behavior is in effect until disablePager() is called or current scripting mode is changed.

Changing the scripting mode has the same effect as calling disablePager().

If the value of shell.options.pager option is changed after this method has been called, the new pager will be automatically used.

If shell.options.pager option is set to an empty string when this method is called, pager will not be active until shell.options.pager is set to a non-empty string.

This method has no effect in non-interactive mode.

disablePager()

Undefined disablePager

(

)

Disables pager for the current scripting mode.

The current value of shell.options.pager option is not changed by calling this method.

This method has no effect in non-interactive mode.

registerReport()

Undefined registerReport	(	String	name,
		String	type,
		Function	report,
		Dictionary	description
	)

Registers a new user-defined report.

Parameters

name	Name of the registered report.
type	Type of the registered report, one of: 'list', 'report' or 'print'.
report	Function to be called when the report is requested.
description	Optional Dictionary describing the report being registered.

Throws ArgumentError in the following scenarios:

• if a report with the same name is already registered.
• if 'name' of a report is not a valid scripting identifier.
• if 'type' is not one of: 'list', 'report' or 'print'.
• if 'name' of a report option is not a valid scripting identifier.
• if 'name' of a report option is reused in multiple options.
• if 'shortcut' of a report option is not an alphanumeric character.
• if 'shortcut' of a report option is reused in multiple options.
• if 'type' of a report option holds an invalid value.
• if the 'argc' key of a 'description' dictionary holds an invalid value.

The name of the report must be unique and a valid scripting identifier. A case-insensitive comparison is used to validate the uniqueness.

The type of the report must be one of: 'list', 'report' or 'print'. This option specifies the expected result of calling this report as well as the output format if it is invoked using \show or \watch commands.

The report function must have the following signature: Dict report(Session session, List argv, Dict options), where:

• session - Session object used by the report to obtain the data.
• argv (optional) - Array of strings representing additional arguments.
• options (optional) - Dictionary with values for various report-specific options.

Each report returns a dictionary with the following keys:

• report (required) - List of JSON objects containing the report. The number and types of items in this list depend on type of the report.

The description dictionary may contain the following optional keys:

• brief - A string value providing a brief description of the report.
• details - A list of strings providing a detailed description of the report.
• options - A list of dictionaries describing the options accepted by the report. If this is not provided, the report does not accept any options.
• argc - A string representing the number of additional arguments accepted by the report. This string can be either: a number specifying exact number of arguments, * specifying zero or more arguments, two numbers separated by a '-' specifying a range of arguments or a number and * separated by a '-' specifying a range of arguments without an upper bound. If this is not provided, the report does not accept any additional arguments.
• examples - A list of dictionaries describing the example usage of the report.

The optional options list must hold dictionaries with the following keys:

• name (string, required) - Name of the option, must be a valid scripting identifier. This specifies an option name in the long form (--long) when invoking the report using \show or \watch commands or a key name of an option when calling this report as a function. Must be unique for a report.
• shortcut (string, optional) - alternate name of the option, must be an alphanumeric character. This specifies an option name in the short form (-s). The short form of an option can only be used when invoking the report using \show or \watch commands, it is not available when calling the report as a function. If this key is not specified, option will not have a short form. Must be unique for a report.
• brief (string, optional) - brief description of the option.
• details (array of strings, optional) - detailed description of the option.
• type (string, optional) - value type of the option. Allowed values are: 'string', 'bool', 'integer', 'float'. If this key is not specified it defaults to 'string'. If type is specified as 'bool', this option is a switch: if it is not specified when invoking the report it defaults to 'false'; if it's specified when invoking the report using \show or \watch commands it does not accept any value and defaults to 'true'; if it is specified when invoking the report using the function call it must have a valid value.
• required (Boolean, optional) - whether this option is required. If this key is not specified, defaults to false. If option is a 'bool' then 'required' cannot be 'true'.
• values (list of strings, optional) - list of allowed values. Only 'string' options may have this key. If this key is not specified, this option accepts any values.
• empty (Boolean, optional) - whether this option accepts empty strings. Only 'string' options may have this key. If this key is not specified, defaults to false.

The optional examples list must hold dictionaries with the following keys:

• description (string, required) - Description text of the example.
• args (list of strings, optional) - List of the arguments used in the example.
• options (dictionary of strings, optional) - Options used in the example.

The type of the report determines the expected result of a report invocation:

• The 'list' report returns a list of lists of values, with the first item containing the names of the columns and remaining ones containing the rows with values.
• The 'report' report returns a list with a single item.
• The 'print' report returns an empty list.

The type of the report also determines the output form when report is called using \show or \watch commands:

• The 'list' report will be displayed in tabular form (or vertical if --vertical option is used).
• The 'report' report will be displayed in YAML format.
• The 'print' report will not be formatted by Shell, the report itself will print out any output.

The registered report is can be called using \show or \watch commands in any of the scripting modes. The registered report is also going to be available as a method of the shell.reports object.

Users may create custom report files in the init.d folder located in the Shell configuration path (by default it is ~/.mysqlsh/init.d in Unix and %AppData%\MySQL\mysqlsh\init.d in Windows). Custom reports may be written in either JavaScript or Python. The standard file extension for each case should be used to get them properly loaded. All reports registered in those files using the registerReport() method will be available when Shell starts.

createExtensionObject()

ExtensionObject createExtensionObject

(

)

Creates an extension object, it can be used to extend shell functionality.

An extension object is defined by adding members in it (properties and functions).

An extension object can be either added as a property of another extension object or registered as a shell global object.

An extension object is usable only when it has been registered as a global object or when it has been added into another extension object that is member in an other registered object.

addExtensionObjectMember()

Undefined addExtensionObjectMember	(	Object	object,
		String	name,
		Value	member,
		Dictionary	definition
	)

Adds a member to an extension object.

Parameters

object	The object to which the member will be added.
name	The name of the member being added.
member	The member being added.
definition	Optional dictionary with help information about the member.

The name parameter must be a valid identifier, this is, it should follow the pattern [_|a..z|A..Z]([_|a..z|A..Z|0..9])*

The member parameter can be any of:

• An extension object
• A function
• Scalar values: boolean, integer, float, string
• Array
• Dictionary
• None/Null

When an extension object is added as a member, a read only property will be added into the target extension object.

When a function is added as member, it will be callable on the extension object but it will not be possible to overwrite it with a new value or function.

When any of the other types are added as a member, a read/write property will be added into the target extension object, it will be possible to update the value without any restriction.

IMPORTANT: every member added into an extensible object will be available only when the object is registered, this is, registered as a global shell object, or added as a member of another object that is already registered.

The definition parameter is an optional and can be used to define additional information for the member.

The member definition accepts the following attributes:

• brief: optional string containing a brief description of the member being added.
• details: optional array of strings containing detailed information about the member being added.

This information will be integrated into the shell help to be available through the built in help system (\?).

When adding a function, the following attribute is also allowed on the member definition:

• parameters: a list of parameter definitions for each parameter that the function accepts.

A parameter definition is a dictionary with the following attributes:

• name: required, the name of the parameter, must be a valid identifier.
• type: required, the data type of the parameter, allowed values include: string, integer, bool, float, array, dictionary, object.
• required: a boolean value indicating whether the parameter is mandatory or optional.
• brief: a string with a short description of the parameter.
• details: a string array with additional details about the parameter.

The information defined on the brief and details attributes will also be added to the function help on the built in help system (\?).

If a parameter's type is 'string' the following attribute is also allowed on the parameter definition dictionary:

• values: string array with the only values that are allowed for the parameter.

If a parameter's type is 'object' the following attributes are also allowed on the parameter definition dictionary:

• class: string defining the class of object allowed as parameter values.
• classes: string array defining multiple classes of objects allowed as parameter values.

The values for the class(es) properties must be a valid class exposed through the different APIs. For details use:

• \? mysql
• \? mysqlx
• \? adminapi
• \? shellapi

To get the class name for a global object or a registered extension call the print function passing as parameter the object, i.e. "Shell" is the class name for the built in shell global object:

mysql-js> print(shell)
<Shell>
mysql-js>

If a parameter's type is 'dictionary' the following attribute is also allowed on the parameter definition dictionary:

• options: list of option definition dictionaries defining the allowed options that can be passed on the dictionary parameter.

An option definition dictionary follows exactly the same rules as the parameter definition dictionary except for one: on a parameter definition dictionary, required parameters must be defined first, option definition dictionaries do not have this restriction.

registerGlobal()

UserObject registerGlobal	(	String	name,
		Object	object,
		Dictionary	definition
	)

Registers an extension object as a shell global object.

Parameters

name	The name to be given to the registered global object.
object	The extension object to be registered as a global object.
definition	optional dictionary containing help information about the object.

An extension object is created with the shell.createExtensionObject function. Once registered, the functionality it provides will be available for use.

The name parameter must comply with the following restrictions:

• It must be a valid scripting identifier.
• It can not be the name of a built in global object.
• It can not be the name of a previously registered object.

The name used to register an object will be exactly the name under which the object will be available for both Python and JavaScript modes, this is, no naming convention is enforced on global objects.

The definition parameter is a dictionary with help information about the object being registered, it allows the following properties:

• brief: a string with a brief description of the object.
• details: a list of strings with additional information about the object.

When the object is registered, the help data on the definition parameter as well as the object members is made available through the shell built in help system. (\?).

dumpRows()

Undefined dumpRows	(	ShellBaseResult	result,
		String	format
	)

Formats and dumps the given resultset object to the console.

Parameters

result	The resultset object to dump
format	One of table, tabbed, vertical, json, ndjson, json/raw, json/array, json/pretty. Default is table.

Returns

The number of printed rows

This function shows a resultset object returned by a DB Session query in the same formats supported by the shell.

Note that the resultset will be consumed by the function.

autoCompleteSql()

Dictionary autoCompleteSql	(	String	statement,
		Dictionary	options
	)

Auto-completes the given SQL statement.

Parameters

statement	A SQL statement to be auto-completed.
options	A dictionary with the auto-completion options.

Returns

A dictionary describing the auto-completion candidates.

The following options are supported:

• serverVersion: string (required) - Version of the server grammar, format "major.minor.patch".
• sqlMode: string (required) - SQL_MODE to use.
• statementOffset: unsigned int (default: the last offset) - zero-based offset position of the caret in statement.
• uppercaseKeywords: bool (default: true) - Whether keywords returned in the result should be upper case.
• filtered: bool (default: true) - Whether explicit candidate names returned in the result should be filtered using the prefix which is being auto-completed.

Return value

This function returns a dictionary describing candidates which can be used to auto-complete the given statement at the given caret position:

{
  "context": {
    "prefix": string,
    "qualifier": list of strings,
    "references": list of dictionaries,
    "labels": list of strings,
  },
  "keywords": list of strings,
  "functions": list of strings,
  "candidates": list of strings,
}

where:

• context - context of the auto-completion operation
• prefix - prefix (possibly empty) that is being auto-completed
• qualifier - optional, one or two unquoted identifiers which were typed so far
• references - optional, references detected in the statement
• labels - optional, labels in labelled blocks
• keywords - optional, candidate keywords
• functions - optional, candidate MySQL library (runtime) functions whose names are also keywords
• candidates - optional, candidate DB object types

The references list contains dictionaries with the following keys:

• schema - optional, name of the schema
• table - name of the table referenced in the statement
• alias - optional, alias of the table

The candidates list contains one or more of the following values:

• "schemas" - schemas
• "tables" - tables
• "views" - views
• "columns" - columns
• "internalColumns" - columns which refer to just the given table
• "procedures" - stored procedures
• "functions" - stored functions
• "triggers" - triggers
• "events" - events
• "engines" - MySQL storage engines
• "udfs" - user defined functions
• "runtimeFunctions" - MySQL library (runtime) functions
• "logfileGroups" - logfile groups
• "userVars" - user variables
• "systemVars" - system variables
• "tablespaces" - tablespaces
• "users" - user accounts
• "charsets" - character sets
• "collations" - collations
• "plugins" - plugins
• "labels" - labels in labelled blocks

registerSqlHandler()

Undefined registerSqlHandler	(	String	name,
		String	description,
		List	prefixes,
		Function	callback
	)

Registers a custom SQL Handler to intercept and handle matching SQL statements.

Parameters

name	A name that uniquely identifies the SQL handler.
description	A brief description of the SQL extensions provided by the SQL handler.
prefixes	List of prefixes to identify the SQL statements to be processed by this handler.
callback	The function to be executed when a statement implemented on this SQL handler is identified.

SQL statements are intercepted in user created sessions and when applicable, they are processed using the corresponding SQL Handler.

User created sessions include the MySQL Shell Global Session and the sessions created with the different API functions available.

The function provided on the callback parameter is expected to have the following signature:

function(session, sql): [Result]

The Session used to execute the SQL statement will first identify if a specific SQL handler should be used to process it. If that is the case, the associated callback will be executed.

If the function returns a Result object the SQL processing will be considered complete; if no data is returned, the Session will proceed to execute the SQL statement.

The selection of a custom SQL handler for execution will be done by matching the SQL with the prefixes defined on the handler by ignoring leading white spaces and coalescing multiple white spaces between non space characters.

The callback function may return a Result object, which can be created by calling shell.createResult.

listSqlHandlers()

Undefined listSqlHandlers

(

)

Lists the name and description of any registered SQL handlers.

Returns

A list with the information of the registered SQL Handlers.

Each element of the list is a dictionary with the following keys:

• name
• description

createResult() [1/2]

ShellResult createResult

(

Dictionary

data

)

Creates a ShellResult object from the given data.

Parameters

data	Optional Either a dictionary or a list of dictionaries with the data to be used in the result.

Returns

ShellResult object representing the given data.

A result can be created using a dictionary with the following elements (all of them optional):

• "affectedItemsCount" - The number of record affected by the SQL statement that produced the result.
• "info" - Additional information about the result.
• "executionTime" - The execution time of the SQL statement that produced this result.
• "autoIncrementValue" - the last integer auto generated id (for insert operations).
• "warnings" - a list of warnings associated to the Result.
• "data" - a list of dictionaries representing a record in the Result.

Creating a Result using an empty Dictionary is the same as creating an OK result with no data.

A warning is defined as a dictionary with the following elements:

• "level" - holds the warning type: error, warning, note.
• "code" - integer number identifying the warning code.
• "message" - describes the actual warning.

When defining warnings, both level and message are mandatory.

It is possible to create a multi-result Result object, to do so, instead of using a single data dictionary, provide a list of data dictionaries following the specification mentioned above.

createResult() [2/2]

ShellResult createResult

(

List

data

)

Creates a ShellResult object from the given data.

Parameters

data	Optional Either a dictionary or a list of dictionaries with the data to be used in the result.

Returns

ShellResult object representing the given data.

A result can be created using a dictionary with the following elements (all of them optional):

• "affectedItemsCount" - The number of record affected by the SQL statement that produced the result.
• "info" - Additional information about the result.
• "executionTime" - The execution time of the SQL statement that produced this result.
• "autoIncrementValue" - the last integer auto generated id (for insert operations).
• "warnings" - a list of warnings associated to the Result.
• "data" - a list of dictionaries representing a record in the Result.

Creating a Result using an empty Dictionary is the same as creating an OK result with no data.

A warning is defined as a dictionary with the following elements:

• "level" - holds the warning type: error, warning, note.
• "code" - integer number identifying the warning code.
• "message" - describes the actual warning.

When defining warnings, both level and message are mandatory.

It is possible to create a multi-result Result object, to do so, instead of using a single data dictionary, provide a list of data dictionaries following the specification mentioned above.