=head1 NAME

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

=head1 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;
  });

=head1 DESCRIPTION

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

=head1 METHODS

Following methods are available:

=over 4

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

Create a new instance of the MySQL client class.

=item $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:

=over 4

=item hostname => $string (REQUIRED)

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

=item port => $string (REQUIRED)

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

=item 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
L<AnyEvent::TLS> context object.  Semantics of these options are same
as L<AnyEvent::TLS> constructor options, except that C<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",
  });

=item 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 C<binary> character set is used.
Otherwise, if the value C<latin1>, C<utf8>, or C<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 C<default> is specified, the server default
character set is used.

=item 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 C<character_set>.

=item 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 C<character_set>.

=item 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 C<character_set>.

=back

Please note that, once connected, the C<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.

=item $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.

=item $promise = $client->quit

Send a C<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.

=item $promise = $client->ping

Send a C<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.

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

Send a C<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 (C<?>).  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 C<packet> contains a field C<data> which
is an array reference of values returned by the server (i.e. the
column values in the row) and whose C<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 C<packet> of a success
result object.

SQL C<begin>, C<commit>, and C<rollback> statements can be sent to the
server by this method.  Please be aware that any statement between
C<begin> statement and subsequent C<commit> or C<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 C<insert> statements #1 and #3 might be considered as part of
the transaction, depending on unforeseeable factors.

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

Send a C<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 (C<?>).  It must be a byte string encoded in the
character encoding of the connection.

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

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

Send a C<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-C<undef> value, must be an
array reference containing typed values (see L</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 C<query> method's argument, except
that the items of C<< @{$_[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 C<query>
method.

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

Send a C<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.

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

Send a C<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.

=item $number = $client->handshake_packet_timeout

=item $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.

=item $number = $client->query_packet_timeout

=item $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.

=back

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

=head1 RESULT OBJECT

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

=over 4

=item $boolean = $result->is_success

=item $boolean = $result->is_failure

=item $boolean = $result->is_exception

Return whether the result is I<success>, I<failure>, or I<exception>,
respectively.  A result object is either I<success>, I<failure>, or
I<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).

=item $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 C<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 C<ERR_Packet>.  If there is no relevant
packet (e.g. a TCP error), C<undef> is returned instead.

=item $packet = $result->handshake_packet

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

=item [$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 C<undef>.

=item [$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 C<undef>.

=item $string = $result->message

A short description of the result, if available, or C<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).

=item $string = '' . $result

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

=back

=head1 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 C<protocol_version> and
C<server_version> fields:

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

=head1 TYPED VALUES

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

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

If C<value> is C<undef>, the typed value represents a C<NULL> value.
Otherwise, the typed value represents a non-C<NULL> value and C<value>
is interpreted in the context of C<type> and C<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 C<type> is C<DATETIME>, C<TIMESTAMP>, or C<DATE>, C<value> must be
a string matching to the regular expression
C<\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 C<type> is C<TIME>, C<value> must be a string matching to the
regular expression
C<\A(-?[0-9]{2,}):([0-9]{2}):([0-9]{2}(?:\.[0-9]{1,6})?)\z> where
captured values represent hours, minutes, and seconds, respectively.

=head1 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 L<AnyEvent::MySQL::Client::ShowLog> module is such an
implementation that prints C<connect> and SQL execution logs to the
standard error output.

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

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

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

Finally, when the "action" is completed, the C<OnActionEnd> callback
is invoked with C<state> and C<result> key/value pair arguments, where
C<state>'s value is the "state" value returned by the C<OnActionInit>
callback and C<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.

=head1 DEPENDENCY

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

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

=head1 SPECIFICATION

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

=head1 SEE ALSO

L<AnyEvent>.

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

=head1 AUTHOR

Wakaba <wakaba@suikawiki.org>.

=head1 ACKNOWLEDGEMENTS

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

Thanks to suzak and hatz48.

=head1 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.

=cut