File Coverage

blib/lib/Mojo/mysql.pm
Criterion Covered Total %
statement 47 80 58.7
branch 22 42 52.3
condition 6 18 33.3
subroutine 11 17 64.7
pod 5 5 100.0
total 91 162 56.1


line stmt bran cond sub pod time code
1             package Mojo::mysql;
2 18     18   5220546 use Mojo::Base 'Mojo::EventEmitter';
  18         241  
  18         115  
3              
4 18     18   23852 use Carp 'croak';
  18         37  
  18         967  
5 18     18   28728 use DBI;
  18         309674  
  18         1484  
6 18     18   7698 use File::Spec::Functions 'file_name_is_absolute';
  18         10973  
  18         1230  
7 18     18   9677 use Mojo::mysql::Database;
  18         257  
  18         222  
8 18     18   10501 use Mojo::mysql::Migrations;
  18         56  
  18         132  
9 18     18   10758 use Mojo::URL;
  18         153588  
  18         169  
10 18     18   937 use Scalar::Util 'weaken';
  18         40  
  18         1074  
11 18     18   10223 use SQL::Abstract::mysql;
  18         66  
  18         29280  
12              
13             our $VERSION = '1.25';
14              
15             has abstract => sub { SQL::Abstract::mysql->new(quote_char => chr(96), name_sep => '.') };
16             has auto_migrate => 0;
17             has database_class => 'Mojo::mysql::Database';
18             has dsn => 'dbi:mysql:dbname=test';
19             has max_connections => 5;
20              
21             has migrations => sub {
22             my $migrations = Mojo::mysql::Migrations->new(mysql => shift);
23             weaken $migrations->{mysql};
24             return $migrations;
25             };
26              
27             has options => sub {
28             my $self = shift;
29             my $options = {AutoCommit => 1, AutoInactiveDestroy => 1, PrintError => 0, RaiseError => 1};
30             $options->{mysql_enable_utf8} = 1 if $self->dsn =~ m!^dbi:mysql:!;
31             return $options;
32             };
33              
34             has [qw(password username)] => '';
35             has pubsub => sub {
36             require Mojo::mysql::PubSub;
37             my $pubsub = Mojo::mysql::PubSub->new(mysql => shift);
38             warn "Use of Mojo::mysql::PubSub is highly EXPERIMENTAL and should be considered an experiment"
39             unless $ENV{MOJO_PUBSUB_EXPERIMENTAL};
40             weaken $pubsub->{mysql};
41             return $pubsub;
42             };
43              
44             sub close_idle_connections {
45 0   0 0 1 0 my ($self, $keep) = (shift, $_[0] || 0);
46 0   0     0 my $queue = $self->{queue} || [];
47              
48             # The database handle needs to be destroyed before the file handle
49 0         0 shift(@$queue)->[0] = undef while @$queue > $keep;
50 0         0 return $self;
51             }
52              
53             sub db {
54 0     0 1 0 my $self = shift;
55              
56             # Fork safety
57 0 0 0     0 delete @$self{qw(pid queue)} unless ($self->{pid} //= $$) eq $$;
58              
59 0         0 my ($dbh, $handle) = @{$self->_dequeue};
  0         0  
60 0         0 return $self->database_class->new(dbh => $dbh, handle => $handle, mysql => $self);
61             }
62              
63             sub from_string {
64 10     10 1 70 my ($self, $str) = @_;
65              
66             # Protocol
67 10 100       28 return $self unless $str;
68 8 100       50 my $url = UNIVERSAL::isa($str, 'Mojo::URL') ? $str : Mojo::URL->new($str);
69 8 100       920 croak qq{Invalid MySQL/MariaDB connection string "$str"} unless $url->protocol =~ m!^(mariadb|mysql)$!;
70 7 50       66 my $dsn = $url->protocol eq 'mariadb' ? 'dbi:MariaDB' : 'dbi:mysql';
71              
72             # https://github.com/jhthorsen/mojo-mysql/pull/47
73 7 50 33     51 die "DBD::MariaDB 1.21 is required for Mojo::mysql to work properly"
74             if $dsn eq 'dbi:MariaDB' and !eval 'use DBD::MariaDB 1.21;1';
75              
76             # Database
77 7 100       13 $dsn .= ':dbname=' . $url->path->parts->[0] if defined $url->path->parts->[0];
78              
79             # Host and port
80 7 100       373 if (my $host = $url->host) { $dsn .= file_name_is_absolute($host) ? ";mysql_socket=$host" : ";host=$host" }
  2 100       13  
81 7 100       44 if (my $port = $url->port) { $dsn .= ";port=$port" }
  1         5  
82              
83             # Need to set the dsn before reading options
84 7         31 $self->dsn($dsn);
85              
86             # Username and password
87 7 100 100     37 if (($url->userinfo // '') =~ /^([^:]+)(?::([^:]+))?$/) {
88 5         40 $self->username($1);
89 5 100       33 $self->password($2) if defined $2;
90             }
91              
92             # Options
93 7         34 my $hash = $url->query->to_hash;
94 7         428 @{$self->options}{keys %$hash} = values %$hash;
  7         29  
95              
96 7         46 return $self;
97             }
98              
99             sub new {
100 12 100 100 12 1 15828 @_ > 2 || ref $_[-1] eq 'HASH' ? shift->SUPER::new(@_) : shift->SUPER::new->from_string(@_);
101             }
102              
103             sub strict_mode {
104 0 0   0 1   my $self = ref $_[0] ? shift : shift->new(@_);
105 0 0         $self->{strict_mode} = $_[0] ? 1 : @_ ? 0 : 1;
    0          
106 0 0         warn "[Mojo::mysql] strict_mode($self->{strict_mode})\n" if $ENV{DBI_TRACE};
107 0           $self->close_idle_connections;
108 0           return $self;
109             }
110              
111             sub _dequeue {
112 0     0     my $self = shift;
113 0           my $dbh;
114              
115 0 0         while (my $c = shift @{$self->{queue}}) { return $c if $c->[0]->ping }
  0            
  0            
116 0           $dbh = DBI->connect(map { $self->$_ } qw(dsn username password options));
  0            
117              
118             # batman's probably going to have more "fun" than you have ...
119             # especially once he discovers that DBD::mysql randomly reconnects under
120             # you, silently, but only if certain env vars are set
121             # hint: force-set mysql_auto_reconnect or whatever it's called to 0
122 0           Mojo::mysql::Database->_dbh_attr($dbh, mysql_auto_reconnect => 0);
123              
124             # Maintain Commits with Mojo::mysql::Transaction
125 0           $dbh->{AutoCommit} = 1;
126              
127 0 0         $self->_set_strict_mode($dbh) if $self->{strict_mode};
128 0 0 0       $self->migrations->migrate if $self->auto_migrate and !$self->{migrated}++;
129 0           $self->emit(connection => $dbh);
130 0           [$dbh];
131             }
132              
133             sub _enqueue {
134 0     0     my ($self, $dbh, $handle) = @_;
135 0 0         push @{$self->{queue}}, [$dbh, $handle] if $dbh->{Active};
  0            
136 0           $self->close_idle_connections($self->max_connections);
137             }
138              
139             sub _set_strict_mode {
140 0     0     $_[1]->do(q[SET SQL_MODE = CONCAT('ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY,', @@sql_mode)]);
141 0           $_[1]->do(q[SET SQL_AUTO_IS_NULL = 0]);
142             }
143              
144             1;
145              
146             =encoding utf8
147              
148             =head1 NAME
149              
150             Mojo::mysql - Mojolicious and Async MySQL/MariaDB
151              
152             =head1 SYNOPSIS
153              
154             use Mojo::mysql;
155              
156             # Connect to a local database
157             my $mysql = Mojo::mysql->strict_mode('mysql://username@/test');
158              
159             # Connect to a remote database
160             my $mysql = Mojo::mysql->strict_mode('mysql://username:password@hostname/test');
161             # MySQL >= 8.0:
162             my $mysql = Mojo::mysql->strict_mode('mysql://username:password@hostname/test;mysql_ssl=1');
163              
164             # Use DBD::MariaDB instead of DBD::mysql
165             my $mysql = Mojo::mysql->strict_mode('mariadb://username@/test');
166              
167             # Create a table
168             $mysql->db->query(
169             'create table names (id integer auto_increment primary key, name text)');
170              
171             # Insert a few rows
172             my $db = $mysql->db;
173             $db->query('insert into names (name) values (?)', 'Sara');
174             $db->query('insert into names (name) values (?)', 'Stefan');
175              
176             # Insert more rows in a transaction
177             eval {
178             my $tx = $db->begin;
179             $db->query('insert into names (name) values (?)', 'Baerbel');
180             $db->query('insert into names (name) values (?)', 'Wolfgang');
181             $tx->commit;
182             };
183             say $@ if $@;
184              
185             # Insert another row and return the generated id
186             say $db->query('insert into names (name) values (?)', 'Daniel')
187             ->last_insert_id;
188              
189             # Use SQL::Abstract::mysql to generate queries for you
190             $db->insert('names', {name => 'Isabel'});
191             say $db->select('names', undef, {name => 'Isabel'})->hash->{id};
192             $db->update('names', {name => 'Bel'}, {name => 'Isabel'});
193             $db->delete('names', {name => 'Bel'});
194              
195             # Select one row at a time
196             my $results = $db->query('select * from names');
197             while (my $next = $results->hash) {
198             say $next->{name};
199             }
200              
201             # Select all rows blocking
202             $db->query('select * from names')
203             ->hashes->map(sub { $_->{name} })->join("\n")->say;
204              
205             # Select all rows non-blocking
206             $db->query('select * from names' => sub {
207             my ($db, $err, $results) = @_;
208             $results->hashes->map(sub { $_->{name} })->join("\n")->say;
209             });
210              
211             # Concurrent non-blocking queries (synchronized with promises)
212             my $now = $db->query_p('select now() as now');
213             my $names = $db->query_p('select * from names');
214             Mojo::Promise->all($now, $names)->then(sub {
215             my ($now, $names) = @_;
216             say $now->[0]->hash->{now};
217             say $_->{name} for $names->[0]->hashes->each;
218             })->catch(sub {
219             my $err = shift;
220             warn "Something went wrong: $err";
221             })->wait;
222              
223             Mojo::IOLoop->start unless Mojo::IOLoop->is_running;
224              
225             =head1 DESCRIPTION
226              
227             L is a tiny wrapper around L and L that
228             makes L and L a lot
229             of fun to use with the L real-time web
230             framework.
231              
232             The two DBD drivers are compatible with both MySQL and MariaDB, but they offer
233             different L. L should have better unicode support
234             though and might become the default in the future.
235              
236             Database and handles are cached automatically, so they can be reused
237             transparently to increase performance. And you can handle connection timeouts
238             gracefully by holding on to them only for short amounts of time.
239              
240             use Mojolicious::Lite;
241             use Mojo::mysql;
242              
243             helper mysql =>
244             sub { state $mysql = Mojo::mysql->strict_mode('mysql://sri:s3cret@localhost/db') };
245              
246             get '/' => sub {
247             my $c = shift;
248             my $db = $c->mysql->db;
249             $c->render(json => $db->query('select now() as time')->hash);
250             };
251              
252             app->start;
253              
254             While all I/O operations are performed blocking, you can wait for long running
255             queries asynchronously, allowing the L event loop to perform
256             other tasks in the meantime. Since database connections usually have a very low
257             latency, this often results in very good performance.
258              
259             Every database connection can only handle one active query at a time, this
260             includes asynchronous ones. So if you start more than one, they will be put on
261             a waiting list and performed sequentially. To perform multiple queries
262             concurrently, you have to use multiple connections.
263              
264             # Performed sequentially (10 seconds)
265             my $db = $mysql->db;
266             $db->query('select sleep(5)' => sub {...});
267             $db->query('select sleep(5)' => sub {...});
268              
269             # Performed concurrently (5 seconds)
270             $mysql->db->query('select sleep(5)' => sub {...});
271             $mysql->db->query('select sleep(5)' => sub {...});
272              
273             All cached database handles will be reset automatically if a new process has
274             been forked, this allows multiple processes to share the same L
275             object safely.
276              
277             =head1 EVENTS
278              
279             L inherits all events from L and can emit the
280             following new ones.
281              
282             =head2 connection
283              
284             $mysql->on(connection => sub {
285             my ($mysql, $dbh) = @_;
286             ...
287             });
288              
289             Emitted when a new database connection has been established.
290              
291             =head1 ATTRIBUTES
292              
293             L implements the following attributes.
294              
295             =head2 abstract
296              
297             $abstract = $mysql->abstract;
298             $mysql = $mysql->abstract(SQL::Abstract::mysql->new);
299              
300             L object used to generate CRUD queries for L.
301              
302             # Generate statements and bind values
303             my ($stmt, @bind) = $mysql->abstract->select('names');
304              
305             =head2 auto_migrate
306              
307             my $bool = $mysql->auto_migrate;
308             $mysql = $mysql->auto_migrate($bool);
309              
310             Automatically migrate to the latest database schema with L, as
311             soon as the first database connection has been established.
312              
313             Defaults to false.
314              
315             =head2 database_class
316              
317             $class = $mysql->database_class;
318             $mysql = $mysql->database_class("MyApp::Database");
319              
320             Class to be used by L, defaults to L. Note that this
321             class needs to have already been loaded before L is called.
322              
323             =head2 dsn
324              
325             my $dsn = $mysql->dsn;
326             $mysql = $mysql->dsn('dbi:mysql:dbname=foo');
327              
328             Data Source Name, defaults to C.
329              
330             =head2 max_connections
331              
332             my $max = $mysql->max_connections;
333             $mysql = $mysql->max_connections(3);
334              
335             Maximum number of idle database handles to cache for future use, defaults to
336             C<5>.
337              
338             =head2 migrations
339              
340             my $migrations = $mysql->migrations;
341             $mysql = $mysql->migrations(Mojo::mysql::Migrations->new);
342              
343             L object you can use to change your database schema more
344             easily.
345              
346             # Load migrations from file and migrate to latest version
347             $mysql->migrations->from_file('/Users/sri/migrations.sql')->migrate;
348              
349             MySQL and MariaDB does not support nested transactions and DDL transactions.
350             DDL statements cause implicit C. C will be called if any step
351             of migration script fails, but only DML statements after the last implicit or
352             explicit C can be reverted. Not all storage engines (like C)
353             support transactions.
354              
355             This means database will most likely be left in unknown state if migration script fails.
356             Use this feature with caution and remember to always backup your database.
357              
358             =head2 options
359              
360             my $options = $mysql->options;
361             $mysql = $mysql->options({mysql_use_result => 1});
362              
363             Options for database handles, defaults to activating C (only
364             for L), C, C as well as
365             C and deactivating C. C and C
366             are considered mandatory, so deactivating them would be very dangerous.
367              
368             C is never enabled, L takes care of dead connections.
369              
370             C cannot not be disabled, use $db->L to manage transactions.
371              
372             C is enabled for blocking and disabled in event loop for non-blocking queries.
373              
374             About C:
375              
376             The mysql_enable_utf8 sets the utf8 charset which only supports up to 3-byte
377             UTF-8 encodings. mysql_enable_utf8mb4 (as of DBD::mysql 4.032) properly
378             supports encoding unicode characters to up to 4 bytes, such as 𠜎. It means the
379             connection charset will be utf8mb4 (supported back to at least mysql 5.5) and
380             these unicode characters will be supported, but no other changes.
381              
382             See also L
383              
384             =head2 password
385              
386             my $password = $mysql->password;
387             $mysql = $mysql->password('s3cret');
388              
389             Database password, defaults to an empty string.
390              
391             =head2 pubsub
392              
393             my $pubsub = $mysql->pubsub;
394             $mysql = $mysql->pubsub(Mojo::mysql::PubSub->new);
395              
396             L should be considered an EXPIREMENT! See
397             L for more information.
398              
399             =head2 username
400              
401             my $username = $mysql->username;
402             $mysql = $mysql->username('batman');
403              
404             Database username, defaults to an empty string.
405              
406             =head1 METHODS
407              
408             L inherits all methods from L and implements the
409             following new ones.
410              
411             =head2 close_idle_connections
412              
413             $mysql = $mysql->close_idle_connections($keep);
414              
415             Close all connections that are not currently active, or limit the
416             number of idle connections to C<$keep>.
417              
418             =head2 db
419              
420             my $db = $mysql->db;
421              
422             Get L object for a cached or newly created database
423             handle. The database handle will be automatically cached again when that
424             object is destroyed, so you can handle connection timeouts gracefully by
425             holding on to it only for short amounts of time.
426              
427             =head2 from_string
428              
429             $mysql = $mysql->from_string('mysql://user@/test');
430              
431             Parse configuration from connection string.
432              
433             # Just a database
434             $mysql->from_string('mysql:///db1');
435              
436             # Username and database
437             $mysql->from_string('mysql://batman@/db2');
438              
439             # Username, password, host and database
440             $mysql->from_string('mysql://batman:s3cret@localhost/db3');
441              
442             # Username, domain socket and database
443             $mysql->from_string('mysql://batman@%2ftmp%2fmysql.sock/db4');
444              
445             # Username, database and additional options
446             $mysql->from_string('mysql://batman@/db5?PrintError=1&RaiseError=0');
447              
448             =head2 new
449              
450             my $mysql = Mojo::mysql->new;
451             my $mysql = Mojo::mysql->new(%attrs);
452             my $mysql = Mojo::mysql->new(\%attrs);
453             my $mysql = Mojo::mysql->new('mysql://user@/test');
454             my $mysql = Mojo::mysql->new('mariadb://user@/test');
455              
456             Construct a new L object either from L and or parse
457             connection string with L if necessary.
458              
459             Using the "mariadb" scheme requires the optional module L version
460             1.21 (or later) to be installed.
461              
462             =head2 strict_mode
463              
464             my $mysql = Mojo::mysql->strict_mode('mysql://user@/test');
465             my $mysql = $mysql->strict_mode($boolean);
466              
467             This method can act as both a constructor and a method. When called as a
468             constructor, it will be the same as:
469              
470             my $mysql = Mojo::mysql->new('mysql://user@/test')->strict_mode(1);
471              
472             Enabling strict mode will execute the following statement when a new connection
473             is created:
474              
475             SET SQL_MODE = CONCAT('ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY,', @@sql_mode)
476             SET SQL_AUTO_IS_NULL = 0
477              
478             The idea is to set up a connection that makes it harder for MySQL to allow
479             "invalid" data to be inserted.
480              
481             This method will not be removed, but the internal commands is subject to
482             change.
483              
484             =head1 DEBUGGING
485              
486             You can set the C environment variable to get some advanced
487             diagnostics information printed to C by L.
488              
489             DBI_TRACE=1
490             DBI_TRACE=15
491             DBI_TRACE=15=dbitrace.log
492             DBI_TRACE=SQL
493             DBI_PROFILE=2
494              
495             See also L and
496             L.
497              
498             =head1 REFERENCE
499              
500             This is the class hierarchy of the L distribution.
501              
502             =over 2
503              
504             =item * L
505              
506             =item * L
507              
508             =item * L
509              
510             =item * L
511              
512             =item * L
513              
514             =item * L
515              
516             =back
517              
518             =head1 AUTHOR
519              
520             Curt Hochwender - C
521              
522             Dan Book - C
523              
524             Jan Henning Thorsen - C
525              
526             Mike Magowan
527              
528             Rolf Stöckli - C
529              
530             This code started as a rip-off from Sebastian Riedel's L.
531              
532             =head1 COPYRIGHT AND LICENSE
533              
534             Copyright (C) 2014-2019, Jan Henning Thorsen.
535              
536             This program is free software, you can redistribute it and/or modify it under
537             the terms of the Artistic License version 2.0.
538              
539             =head1 SEE ALSO
540              
541             L,
542              
543             L Async Connector for PostgreSQL using L, L,
544              
545             L Pure-Perl non-blocking I/O MySQL Connector, L,
546              
547             L, L.
548              
549             =cut