Mojo-mysql

 view release on metacpan or  search on metacpan

Changes  view on Meta::CPAN

 - Allow constructor to take a single hashref #37

1.06 2018-02-27T19:32:40+0100
 - Changed from_string() to also accept Mojo::URL objects #36
   Contributor: Karl Rune Nilsen

1.05 2017-11-11T10:04:40+0800
 - Add delete_p(), insert_p(), query_p(), select_p() and update_p()

1.04 2017-08-14T19:22:33+0200
 - Documented "mysql_enable_utf8" v.s. "mysql_enable_utf8mb4" #32
 - Can pass on attributes to new()

1.03 2017-05-21T23:19:29+0200
 - Add ability to set types of query parameters #31
   Contributor: Dan Book

1.02 2017-05-15T20:34:01+0200
 - Fix utf8 handling in DBD::mysql 4.042
 - Prevent warnings when creating the mojo_migrations table #26
 - Add proper quoting of table and column names #30

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

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};

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

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');

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

JSON text with L<Mojo::JSON/"to_json">. To accomplish the reverse, you can use
the method L<Mojo::mysql::Results/"expand">, which automatically decodes data back
to Perl data structures.

  $db->query('insert into foo values (x) values (?)', {json => {bar => 'baz'}});
  $db->query('select * from foo')->expand->hash->{x}{bar}; # baz

Hash reference arguments containing values named C<type> and C<value> can be
used to bind specific L<DBI> data types (see L<DBI/"DBI Constants">) to
placeholders. This is needed to pass binary data in parameters; see
L<DBD::mysql/"mysql_enable_utf8"> for more information.

  # Insert binary data
  use DBI ':sql_types';
  $db->query('insert into bar values (?)', {type => SQL_BLOB, value => $bytes});

=head2 query_p

  my $promise = $db->query_p('select * from foo');

Same as L</"query">, but performs all operations non-blocking and returns a

t/connection.t  view on Meta::CPAN

use Mojo::Base -strict;
use Test::More;
use Mojo::mysql;
use Mojo::Util 'url_escape';

note 'Defaults';
my $mysql = Mojo::mysql->new;
is $mysql->dsn,      'dbi:mysql:dbname=test', 'right data source';
is $mysql->username, '',                      'no username';
is $mysql->password, '',                      'no password';
my $options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 1};
is_deeply $mysql->options, $options, 'right options';

note 'Without database name';
$mysql = Mojo::mysql->new('mysql://root@');
is $mysql->dsn, 'dbi:mysql', 'right data source';

note 'Minimal connection string with database';
$mysql = Mojo::mysql->new('mysql:///test1');
is $mysql->dsn,      'dbi:mysql:dbname=test1', 'right data source';
is $mysql->username, '',                       'no username';
is $mysql->password, '',                       'no password';
$options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 1};
is_deeply $mysql->options, $options, 'right options';

note 'Connection string with host and port';
$mysql = Mojo::mysql->new('mysql://127.0.0.1:8080/test2');
is $mysql->dsn,      'dbi:mysql:dbname=test2;host=127.0.0.1;port=8080', 'right data source';
is $mysql->username, '',                                                'no username';
is $mysql->password, '',                                                'no password';
$options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 1};
is_deeply $mysql->options, $options, 'right options';

note 'Connection string username but without host';
$mysql = Mojo::mysql->new('mysql://mysql@/test3');
is $mysql->dsn,      'dbi:mysql:dbname=test3', 'right data source';
is $mysql->username, 'mysql',                  'right username';
is $mysql->password, '',                       'no password';
$options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 1};
is_deeply $mysql->options, $options, 'right options';

note 'Connection string with unix domain socket and options';
my $dummy_socket = File::Spec->rel2abs(__FILE__);
$mysql = Mojo::mysql->new("mysql://x1:y2\@@{[url_escape $dummy_socket]}/test4?PrintError=1&RaiseError=0");
is $mysql->dsn,      "dbi:mysql:dbname=test4;mysql_socket=$dummy_socket", 'right data source';
is $mysql->username, 'x1',                                                'right username';
is $mysql->password, 'y2',                                                'right password';
$options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 1, RaiseError => 0};
is_deeply $mysql->options, $options, 'right options';

note 'Mojo::URL object with credentials';
my $url_obj = Mojo::URL->new('mysql://x2:y3@/test5?PrintError=1');
$mysql = Mojo::mysql->new($url_obj);
is $mysql->dsn,      'dbi:mysql:dbname=test5', 'right data source with Mojo::URL object';
is $mysql->username, 'x2',                     'right username';
is $mysql->password, 'y3',                     'right password';
$options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 1, RaiseError => 1};
is_deeply $mysql->options, $options, 'right options';

note 'Connection string with lots of zeros';
$mysql = Mojo::mysql->new('mysql://0:0@/0?RaiseError=0');
is $mysql->dsn,      'dbi:mysql:dbname=0', 'right data source';
is $mysql->username, '0',                  'right username';
is $mysql->password, '0',                  'right password';
$options = {mysql_enable_utf8 => 1, AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 0};
is_deeply $mysql->options, $options, 'right options';

note 'Invalid connection string';
eval { Mojo::mysql->new('http://localhost:3000/test') };
like $@, qr{Invalid MySQL/MariaDB connection string}, 'right error';

note 'Quote fieldnames correctly';
like $mysql->abstract->select("foo", ['binary']),     qr{`binary},       'quoted correct binary';
like $mysql->abstract->select("foo", ['foo.binary']), qr{`foo`.`binary}, 'quoted correct foo.binary';