NAME

AnyEvent::MySQL::Client - A low-level MySQL client API

SYNOPSIS

use AnyEvent::MySQL::Client;
$client = AnyEvent::MySQL::Client->new;
$client->connect
    (hostname => $hostname, port => $port,
     username => $username, password => $password,
     database => $dbname)->then (sub {
  return $client->query ('insert into hoge (id) values (12)');
})->then (sub {
  die $_[0] if $_[0]->is_failure;
  return $client->query ('insert into fuga (id) values (56)');
})->catch (sub {
  warn $_[0];
})->then (sub {
  $client->disconnect;
});

DESCRIPTION

The AnyEvent::MySQL::Client module provides a low-level client API for the MySQL server/client protocol, built on top of AnyEvent and Promise.

METHODS

Following methods are available:

$client = AnyEvent::MySQL::Client->new

Create a new instance of the MySQL client class.

$promise = $client->connect (OPTIONS)

Connect to the specified MySQL server. It returns a promise, which is resolved with a result object with the handshake packet received from the server, when the client object is ready to accept subsequential commands. The promise is rejected with a result object containing the error description if it fails to connect to the server.

Following key/value pairs can be specified as options:

hostname => $string (REQUIRED)

The ASCII hostname (or IP address) of the server for TCP/IP, or unix/ for Unix domain socket.

port => $string (REQUIRED)

The port number of the server for TCP/IP, or the file name for Unix domain socket.

tls => {OPTIONS}

Whether TLS (SSL) is used to connect to the server or not. If specified, the value must be a hash reference, which is used to create AnyEvent::TLS context object. Semantics of these options are same as AnyEvent::TLS constructor options, except that verify option is enabled (true) by default. If not specified, TLS is not used. An example:

$client->connect (..., tls => {
  verify => 1,
  ca_file => "path/to/ca-cert.pem",
  key_file => "path/to/client-key.pem",
  cert_file => "path/to/client-cert.pem",
});

character_set => $charset

The MySQL character set used for the connection. See <http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html> for more information on the character set of the connection.

If this option is not specified, the binary character set is used. Otherwise, if the value latin1, utf8, or binary is specified, that character set is used. Otherwise, if an integer is specified, it is used as the character set ID (see <http://dev.mysql.com/doc/internals/en/character-set.html>). Otherwise, if the value default is specified, the server default character set is used.

username => $string

The user name for the authorization, if necessary. If specified, the value must be a byte string encoded in the character encoding explicitly or implicily specified by character_set.

password => $string

The password for the authorization, if necessary. If specified, the value must be a byte string encoded in the character encoding explicitly or implicily specified by character_set.

database => $string

The database name to connect. If specified, the value must be a byte string encoded in the character encoding explicitly or implicily specified by character_set.

Please note that, once connected, the disconnect method of the client object must be invoked before the client object is discarded such that the connection to the server is grecefully closed.

$promise = $client->disconnect

Disconect the connection to the MySQL server, if any. It returns a promise, which is resolved with a success result object when the connection is closed. If there is no connection, the promise is immediately resolved. That is, this method might be invoked even when there is no connection.

$promise = $client->quit

Send a COM_QUIT command, which will let the server close the connection. It returns a promise, which is resolved with a success result object when the connection is closed. If there is no connection, the promise is immediately resolved. That is, this method might be invoked even when there is no connection.

$promise = $client->ping

Send a COM_PING command. It returns a promise, which is resolved with a true value when the server responds to the command. If the server does not return a response, or if there is no connection, the promise is resolved with a false value.

Note that the promise returned by this method is resolved with a boolean value unlike other methods of this class.

$promise = $client->query ($query[, $row_callback])

Send a COM_QUERY command with the specified query (an SQL statement). It returns a promise, which is resolved with the result object containing the evaluation result of the specified query.

The first argument must be an SQL statement. It cannot use the placeholder syntax (?). It must be a byte string encoded in the character encoding of the connection.

The second argument, if specified, must be a code reference for the row callback. The code is invoked whenever a resultset is returned from the server as part of the response to the query. It is invoked with a result object, whose packet contains a field data which is an array reference of values returned by the server (i.e. the column values in the row) and whose column_packets contains an array reference of packets representing the columns in the row. Note that the values are encoded in the character set of the connection. The row callback can return a promise need to be resolved before continue, or can throw an exception. In other words, it can be used in the same way as the fulfill callback of a promise. Column name/value pairs of the rows returned by a query can be enumerated by the following code:

$client->query ('SELECT * FROM table', sub {
  my $row = shift;
  my @col = map { $_->{name} } @{$row->column_packets};
  my $data = $row->packet->{data};
  for (0..$#col) {
    printf "Column `%s` = %s\n", $col[$_], $data->[$_] // '(NULL)';
  }
});

The promise returned by the method is resolved with the result object for the entire query. It can be both success or failure. For example, if the query results in a "duplicate entry" error, the promise's fulfill callback is invoked with a failure result object with that error packet. The promise is resolved only after any possible invocations of the row callback have been done. If the promise is resolved with a failure result object, the row callback has never been invoked. Note that additional information on query such as affected row count might be available in the packet of a success result object.

SQL begin, commit, and rollback statements can be sent to the server by this method. Please be aware that any statement between begin statement and subsequent commit or rollback command are considered as part of the transaction. For example, in a bad code fragment:

## BAD EXAMPLE
Promise->all ([
  Promise->resolve->then (sub { $client->query ('INSERT ...') }, ## #1
  $client->query ('BEGIN')->then (sub {
    return $client->query ('INSERT ...'); ## #2
  })->then (sub {
    return $client->query ('COMMIT');
  }),
  $client->query ('INSERT ...'), ## #3
])

... the insert statements #1 and #3 might be considered as part of the transaction, depending on unforeseeable factors.

$promise = $client->statement_prepare ($query)

Send a COM_STMT_PREPARE command with the specified query (an SQL statement). It returns a promise, which is resolved with the result object containing the statement ID of the prepared statement.

The first argument must be an SQL statement, possibly containing placeholders (?). It must be a byte string encoded in the character encoding of the connection.

If the returned promise is resolved with a success return object, the statement ID of the statement prepared can be accessed by $result->packet->{statement_id}.

$promise = $client->statement_execute ($statement_id, [$value1, $value2, ...][, $on_row])

Send a COM_STMT_EXECUTE command with the specified parameters (i.e. the arguments to the prepared statement's placeholders). It returns a promise, which is resolved with the result object containing the evaluation result of the prepared statement with the parameters.

The first argument must be the statement ID of the prepared statement to execute.

The second argument, if specified to non-undef value, must be an array reference containing typed values (see "TYPED VALUES") representing parameters in order, possibly empty.

The third argument, if specified, must be a code reference, which is the row callback similar to query method's argument, except that the items of @{$_[0]->packet->{data}} are typed values representing columns in the row.

The promise returned by the method is resolved with the result object for the entire query, similar to the returned promise of the query method.

$promise = $client->statement_close ($statement_id)

Send a COM_STMT_CLOSE command with the specified statement ID. It returns a promise, which is resolved with the result object containing whether the command has succeeded or not.

$promise = $client->statement_reset ($statement_id)

Send a COM_STMT_RESET command with the specified statement ID. It returns a promise, which is resolved with the result object containing whether the command has succeeded or not.

$number = $client->handshake_packet_timeout

$client->handshake_packet_timeout ($number)

Get or set timeout for connection management related packets (e.g. initial handshake packet and ping response packet) in seconds.

$number = $client->query_packet_timeout

$client->query_packet_timeout ($number)

Get or set timeout for query-related packets (e.g. response packets for query command or prepared statement commands) in seconds.

Many methods return promises, which is compatible with (but not an instance of) Promise available from <https://github.com/wakaba/perl-promise>, which implements similar API to JavaScript Promise objects.

RESULT OBJECT

Most methods of this class returns a promise, which is resolved or rejected with a result object. The result object contains the result of the operation, which can be accessed using the following methods of the result object:

$boolean = $result->is_success

$boolean = $result->is_failure

$boolean = $result->is_exception

Return whether the result is success, failure, or exception, respectively. A result object is either success, failure, or exception.

Please note that failures and exceptions are different for the purpose of the result object. In general, failures are non-fatal error of an operation (e.g. SQL syntax error or "duplicate entry" error) while exceptions are fatal error of the connection (e.g. error during connection establishment, authorization error, or unparsable packet sequence).

$packet = $result->packet

The received packet which lets the client return the result object. If the result is a success, additional information might be retrieved from the packet, which is usually a OK_Packet. If the result is an error, descriptions of the error by the server might be retrieved from the packet, which is usually a ERR_Packet. If there is no relevant packet (e.g. a TCP error), undef is returned instead.

$packet = $result->handshake_packet

The initial handshake packet object received from the server, if the result object is a success result of the connect method, or undef.

[$packet1, $packet2, ...] = $result->column_packets

The array reference of the packet objects containing data of the columns available, if the result object is a row callback argument or a success result of a query or prepared statement method, or undef.

[$packet1, $packet2, ...] = $result->param_packets

The array reference of the packet objects containing data of the parameters available, if the result object is a success result of a prepared statement method, or undef.

$string = $result->message

A short description of the result, if available, or undef.

Note that this string can contain binary data (if some input used to construct the message or an error message from the platform contains binary or non-ASCII bytes) or utf8-flagged characters (if some input used to construct the message contains them).

$string = '' . $result

A true value which might be useful for debugging. If $result->message is a true value, it is returned.

RECEIVED PACKET OBJECT

A received packet object contains parsed packet data as hash-like fields. Keys are field names shown in MySQL protocol documentation.

For example, an initial handshake packet has protocol_version and server_version fields:

$client->connect (...)->then (sub {
  my $initial_packet = shift->packet;
  is $initial_packet->{protocol_version}, 0x0A;
  warn $initial_packet->{server_version};
});

TYPED VALUES

A typed value represents a value in MySQL binary protocol. It is a hash reference which can contain type, unsigned, and value key/value pairs.

The value of type identifies the type of the value. It must be one of the following values: DECIMAL, TINY, SHORT, LONG, FLOAT, DOUBLE, TIMESTAMP, LONGLONG, DATE, TIME, DATETIME, VARCHAR, BIT, NEWDECIMAL, ENUM, SET, TINY_BLOB, MEDIUM_BLOB, LONG_BLOB, BLOB, VAR_STRING, STRING, or GEOMETRY. If unsigned is true, the "unsigned" variant of type is used as the type.

If value is undef, the typed value represents a NULL value. Otherwise, the typed value represents a non-NULL value and value is interpreted in the context of type and unsigned. It must be within the range of the type. The value cannot be a utf8-flagged string. Strings are interpreted using the character set of the connection.

If type is DATETIME, TIMESTAMP, or DATE, value must be a string matching to the regular expression \A([0-9]{4})-([0-9]{2})-([0-9]{2})(?:\x20([0-9]{2}):([0-9]{2}):([0-9]{2}(?:\.[0-9]{1,6})?))?\z where captured values represent year, month, day, hour, minute, and second, respectively, if specified, or zero.

If type is TIME, value must be a string matching to the regular expression \A(-?[0-9]{2,}):([0-9]{2}):([0-9]{2}(?:\.[0-9]{1,6})?)\z where captured values represent hours, minutes, and seconds, respectively.

HOOKS FOR DEBUGGING

A few callback hooks are provided to implement a development mode, where any SQL executions are logged so that application developers can inspect operations which have been performed through this module.

The AnyEvent::MySQL::Client::ShowLog module is such an implementation that prints connect and SQL execution logs to the standard error output.

There are three global variables: $AnyEvent::MySQL::Client::OnActionInit, $AnyEvent::MySQL::Client::OnActionStart, and $AnyEvent::MySQL::Client::OnActionEnd. Their values must be code references.

When an "action" initiates, the OnActionInit callback is invoked with parameters as key/value pair arguments. The action_type value identifies the type of the "action", which is corresponding to the method name at the moment. For other parameters, see AnyEvent::MySQL::Client::ShowLog's source code. The callback can return a "state" value.

Then, just before the "action" is actually started, the OnActionStart callback is invoked with state key/value pair argument, whose value is the "state" value returned by the OnActionInit callback.

Finally, when the "action" is completed, the OnActionEnd callback is invoked with state and result key/value pair arguments, where state's value is the "state" value returned by the OnActionInit callback and result's value is the result object of the action (which the relevant promise of the method that initiated the action is to be resolved with).

These callbacks are not expected to throw any exception. They should not use blocking I/O and they should return as soon as possible.

DEPENDENCY

The module requires Perl 5.12 or later and AnyEvent. It also requires some core modules.

For MySQL 8 servers, Crypt::OpenSSL::RSA is also required.

SPECIFICATION

MySQL Internals Manual :: 14 MySQL Client/Server Protocol <http://dev.mysql.com/doc/internals/en/client-server-protocol.html>.

SEE ALSO

AnyEvent.

Promise <https://github.com/wakaba/perl-promise>.

AUTHOR

Wakaba <wakaba@suikawiki.org>.

ACKNOWLEDGEMENTS

This module is inspired by various earlier works on this area, including DBI, DBD::mysql, AnyEvent::DBI, AnyEvent::DBI::MySQL, and AnyEvent::MySQL.

Thanks to suzak and hatz48.

LICENSE

Copyright 2014-2024 Wakaba <wakaba@suikawiki.org>.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.