view release on metacpan or  search on metacpan

lib/Mojo/mysql.pm  view on Meta::CPAN

package Mojo::mysql;
use Mojo::Base 'Mojo::EventEmitter';

use Carp qw(croak);
use DBI;
use File::Spec::Functions qw(file_name_is_absolute);
use Mojo::mysql::Database;
use Mojo::mysql::Migrations;
use Mojo::URL;
use Scalar::Util qw(blessed weaken);
use SQL::Abstract::mysql;

use constant MARIADB => !!eval { require DBD::MariaDB; DBD::MariaDB->VERSION(1.21) };
use constant URI     => !!eval { require URI::db;      URI::db->VERSION(0.10) };

our $VERSION = '1.28';

has abstract        => sub { SQL::Abstract::mysql->new(quote_char => chr(96), name_sep => '.') };
has auto_migrate    => 0;
has database_class  => 'Mojo::mysql::Database';
has dsn             => 'dbi:mysql:dbname=test';
has max_connections => 5;

has migrations => sub {
  my $migrations = Mojo::mysql::Migrations->new(mysql => shift);
  weaken $migrations->{mysql};
  return $migrations;
};

has options => sub {
  my $self    = shift;
  my $options = {AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 1};
  $options->{mysql_enable_utf8} = 1 if $self->dsn =~ m!^dbi:mysql:!;
  return $options;
};

has [qw(password username)] => '';
has pubsub                  => sub {
  require Mojo::mysql::PubSub;
  my $pubsub = Mojo::mysql::PubSub->new(mysql => shift);
  warn "Use of Mojo::mysql::PubSub is highly EXPERIMENTAL and should be considered an experiment"
    unless $ENV{MOJO_PUBSUB_EXPERIMENTAL};
  weaken $pubsub->{mysql};
  return $pubsub;
};

sub close_idle_connections {
  my ($self, $keep) = (shift, $_[0] || 0);
  my $queue = $self->{queue} || [];

  # The database handle needs to be destroyed before the file handle
  shift(@$queue)->[0] = undef while @$queue > $keep;
  return $self;
}

sub db {
  my $self = shift;

  # Fork safety
  delete @$self{qw(pid queue)} unless ($self->{pid} //= $$) eq $$;

  my ($dbh, $handle) = @{$self->_dequeue};
  return $self->database_class->new(dbh => $dbh, handle => $handle, mysql => $self);
}

sub from_string {
  my ($self, $str) = @_;

  # Protocol
  return $self unless $str;
  my $url = blessed $str ? $str : Mojo::URL->new($str);

  my $protocol = $url->can('engine') ? $url->engine : $url->protocol;
  croak qq{Invalid MySQL/MariaDB connection string "$str"} unless $protocol =~ m!^(mariadb|mysql)$!;
  my $dsn = $protocol eq 'mariadb' ? 'dbi:MariaDB' : 'dbi:mysql';

  # Database
  my $dbname = $url->can('dbname') ? $url->dbname : $url->path->parts->[0];
  $dsn .= ":dbname=$dbname" if length $dbname;

  # Host and port
  if (my $host = $url->host) { $dsn .= file_name_is_absolute($host) ? ";mysql_socket=$host" : ";host=$host" }
  if (my $port = $url->port) { $dsn .= ";port=$port" }

  # Need to set the dsn before reading options
  $self->dsn($dsn);

  # Username and password
  if ($url->userinfo) {
    my @info = split /:/, $url->userinfo, 2;
    $self->username($info[0]);
    $self->password($info[1] // '') if defined $info[1];
  }

lib/Mojo/mysql.pm  view on Meta::CPAN

  my ($stmt, @bind) = $mysql->abstract->select('names');

=head2 auto_migrate

  my $bool = $mysql->auto_migrate;
  $mysql   = $mysql->auto_migrate($bool);

Automatically migrate to the latest database schema with L</"migrations">, as
soon as the first database connection has been established.

Defaults to false.

=head2 database_class

  $class = $mysql->database_class;
  $mysql = $mysql->database_class("MyApp::Database");

Class to be used by L</"db">, defaults to L<Mojo::mysql::Database>. Note that this
class needs to have already been loaded before L</"db"> is called.

=head2 dsn

  my $dsn = $mysql->dsn;
  $mysql  = $mysql->dsn('dbi:mysql:dbname=foo');

Data Source Name, defaults to C<dbi:mysql:dbname=test>.

=head2 max_connections

  my $max = $mysql->max_connections;
  $mysql  = $mysql->max_connections(3);

Maximum number of idle database handles to cache for future use, defaults to
C<5>.

=head2 migrations

  my $migrations = $mysql->migrations;
  $mysql         = $mysql->migrations(Mojo::mysql::Migrations->new);

L<Mojo::mysql::Migrations> object you can use to change your database schema more
easily.

  # Load migrations from file and migrate to latest version
  $mysql->migrations->from_file('/Users/sri/migrations.sql')->migrate;

MySQL and MariaDB does not support nested transactions and DDL transactions.
DDL statements cause implicit C<COMMIT>. C<ROLLBACK> will be called if any step
of migration script fails, but only DML statements after the last implicit or
explicit C<COMMIT> can be reverted. Not all storage engines (like C<MYISAM>)
support transactions.

This means database will most likely be left in unknown state if migration script fails.
Use this feature with caution and remember to always backup your database.

=head2 options

  my $options = $mysql->options;
  $mysql      = $mysql->options({mysql_use_result => 1});

Options for database handles, defaults to activating C<mysql_enable_utf8> (only
for L<DBD::mysql>), C<AutoCommit>, C<AutoInactiveDestroy> as well as
C<RaiseError> and deactivating C<PrintError>. C<AutoCommit> and C<RaiseError>
are considered mandatory, so deactivating them would be very dangerous.

C<mysql_auto_reconnect> is never enabled, L<Mojo::mysql> takes care of dead connections.

C<AutoCommit> cannot not be disabled, use $db->L<begin|Mojo::mysql::Database/"begin"> to manage transactions.

C<RaiseError> is enabled for blocking and disabled in event loop for non-blocking queries.

About C<mysql_enable_utf8>:

  The mysql_enable_utf8 sets the utf8 charset which only supports up to 3-byte
  UTF-8 encodings. mysql_enable_utf8mb4 (as of DBD::mysql 4.032) properly
  supports encoding unicode characters to up to 4 bytes, such as 𠜎. It means the
  connection charset will be utf8mb4 (supported back to at least mysql 5.5) and
  these unicode characters will be supported, but no other changes.

See also L<https://github.com/jhthorsen/mojo-mysql/pull/32>

=head2 password

  my $password = $mysql->password;
  $mysql       = $mysql->password('s3cret');

Database password, defaults to an empty string.

=head2 pubsub

  my $pubsub = $mysql->pubsub;
  $mysql     = $mysql->pubsub(Mojo::mysql::PubSub->new);

L<Mojo::mysql::PubSub> should be considered an EXPERIMENT! See
L<Mojo::mysql::PubSub/DESCRIPTION> for more information.

=head2 username

  my $username = $mysql->username;
  $mysql       = $mysql->username('batman');

Database username, defaults to an empty string.

=head1 METHODS

L<Mojo::mysql> inherits all methods from L<Mojo::EventEmitter> and implements the
following new ones.

=head2 close_idle_connections

  $mysql = $mysql->close_idle_connections($keep);

Close all connections that are not currently active, or limit the
number of idle connections to C<$keep>.

=head2 db

  my $db = $mysql->db;

Get L<Mojo::mysql::Database> object for a cached or newly created database
handle. The database handle will be automatically cached again when that
object is destroyed, so you can handle connection timeouts gracefully by
holding on to it only for short amounts of time.

=head2 from_string

  $mysql = $mysql->from_string('mysql://user@/test');
  $mysql = $mysql->from_string(Mojo::URL->new);
  $mysql = $mysql->from_string(URI::db->new);

Parse configuration from connection string or a connection string object.

  # Just a database
  $mysql->from_string('mysql:///db1');